PGXN

create or replace
function veil2.my_get_accessor(
    username in text,
    context_type_id in integer,
    context_id in integer)
  returns integer as
$$
declare
  _result integer;
begin
  select party_id
    into _result
    from demo.parties_tbl p
   where p.party_name = username
     and p.org_id = context_id
     and context_type_id = 4;  -- Logins are in org context
   return _result;
end;
$$
language plpgsql security definer stable leakproof;
	

This provides an accessor_id for a user based on their username and the authentication context for which they are logging-in.

Note that in use, your user-provided function will replace the Veil2 system-provided function. This means that the name of the function when it is executed will not include the my_ prefix. You should not therefore use the function name as a prefix to a parameter name in order to disambiguate a reference (see this link for more details).

In addition to the manual mechanisms described above, all user-defined functions and views will be automatically installed by Veil2 when the system-provided veil2.get_accessor() function is first run.

This is provided primarily for the case when we restore from a pg_dump-based backup. In such a case, the first attempt by a user to log in will call the system-provided version of this function. The system-provided version of the function will only be in place during this initial call, and will have been replaced by the user-provided version on subsequent calls.

Connect is a special built-in privilege with an important meaning. This privilege allows a user to create a database session (either dedicated or shared) and load other privileges into the session tables. Without this privilege, a user will be able to load no other privileges and will therefore have no access rights at all.

The connect privilege is not intended to be provided by any roles other than the connect role, and is the only privilege not provided by the superuser role.

Restricting the use of the connect privilege in this way means that you can disable a user by simply revoking their connect role, and that you can re-allow it by re-assigning that role: you don't need to revoke all of a user's roles in order to disable their access. This is intended to make user and role management easier.

To become a different user you use the function veil2.become_user(), which will only succeed if you have the become user privilege in the scope of, or a superior scope to, their authentication context.

Note that you cannot use veil2.become_user() to gain access rights that you do not already have: your session's privileges will become those of the user that you become, minus any privileges that they have and you do not (this is the mathematical intersection of the sets of your privileges and theirs).

• implicit;

  Roles marked as implicit are intended to not be assigned to accessors and do not appear in the set of roles implicitly mapped to the superuser role.

• immutable;

  Immutable roles are intended to be function-level roles only. They should not have other roles assigned to them.

The connect role is provided by Veil2 and is intended to be the only role that provides the connect privilege. Users need this role in both their authentication and session contexts (or a superior one such as global context) in order to establish a session.

This role has no other privileges, as supplied, and should not be assigned any.

The superuser role is system-provided, and unmodifiable. It provides every non-implicit role and every privilege except connect. This is built in to the implementation (in veil2.all_role_roles) and are not modifiable through veil2.role_privileges and veil2.role_roles.

This role is implicitly assigned to each accessor (via veil2.all_accessor_roles_plus) when their session is established. As supplied it has no privileges.

It may initially seem odd that there is a role with the same name as a security context but here is the reasoning. The only privileges that apply in your personal scope are those that are assigned in personal context and this is done through a single, implied, role assignment. We could have named that role “role that is assigned only in personal context” but it isn't really a better name so “personal context” it is.

• Alicecorp;
• Bobcorp;
• Carolcorp.

• https://myveilprotectedsite/Alicecorp/login;
• https://myveilprotectedsite/Bobcorp/login;
• https://myveilprotectedsite/Carolcorp/login.

• https://myveilprotectedsite/Alicecorp/login;

  select * from veil2.create_session(<username>, 4, 42);	    
  	  

• https://myveilprotectedsite/Bobcorp/login;

  select * from veil2.create_session(<username>, 4, 43);	    
  	  

• https://myveilprotectedsite/Carolcorp/login;

  select * from veil2.create_session(<username>, 4, 44);	    
  	  

One of the coolest things about Veil2 is that you can create your own role assignment mechanisms. You do this by redefining veil2.all_accessor_roles. This view returns all role assignments in all contexts other than personal context which is handled by veil2.all_accessor_roles_plus). You add new mechanisms by adding extra unions to the base query against the veil2.accessor_roles table.

For instance, to add an implicit global context role assignment of role 67 to all accessors whose username begins with M (for reasons that make no sense whatsoever, but nicely illustrate the flexibility this provides) you would define your version of the view like this:

create or replace
view veil2.my_all_accessor_roles (
  accessor_id, role_id, context_type_id, context_id
) as
select accessor_id, role_id,
       context_type_id, context_id
  from veil2.accessor_roles
 union all
select accessor_id, role_id, 1, 0
  from veil2.accessors
 where lower(username) like 'm%';
	

As a more realistic example let's add, additionally, the project member role (which we'll give role_id 68) in the project context to all accessors who are recorded as team members in the project_members table:

create or replace
view veil2.my_all_accessor_roles (
  accessor_id, role_id, context_type_id, context_id
) as
select accessor_id, role_id,
       context_type_id, context_id
  from veil2.accessor_roles
 union all
select accessor_id, 67, 1, 0
  from veil2.accessors
 where lower(username) like 'm%'
 union all
select accessor_id, 68, 5, project_id -- project context type is 5
  from project_members; 
	

In order to let privileged users work in session contexts that are unrelated to their authentication contexts, Veil2 optionally allows the session context for a session to be provided explicitly to the veil2.create_session() and veil2.hello() functions.

The user must have connect privilege in the scope of both the accessor's authentication context and the chosen session context.

This is described in more detail in the Authentication Protocols appendix.

• the mapping context target scope type system parameter;

  This parameter (recorded in veil2.system_parameters) specifies scope type that will apply to mapping contexts. By default this is global scope which means that all accessors will see the same role->role mappings. If you don't need different mapping contexts, you should leave this setting alone.

• the context of the role assignment;

  Each role is assigned in a specific context. The mapping context that applies to a role in a given assignment context will be the context of the first superior scope that matches our mapping context target scope type.

  For example, imagine a scope hierarchy that goes from corporation, to division, to department, to project. If our mapping context is at the division level, then the Project Manager role assigned for a project within department S, division South, would have a mapping context of department S. This would mean that the Project Manager role in department S might have different sub-roles from the same Project Manager role in Department Y.

• the user's session context.