PostgreSQL allows tables in extensions to contain user-data. Such tables must be registered with the extension mechanism using pg_catalog.pg_extension_config_dump(). This allows a where clause to be defined which specifies how to identify user-provided entries.

All Veil2 tables are managed in this way, so you can safely add your own data to them. What you cannot do is extend those tables in other ways such as by adding extra columns or constraints.

Although most Veil2 functions are not expected to be modified by the user, a small number must provide functionality that is specific to your database schema. This means that some functionality must be user-provided.

The mechanism provided by Veil2 to allow for user-provided functionality is to allow users to define their own replacements for built-in Veil2 functions. These replacement functions are owned by you and are not part of the Veil2 extension.

To define your own version of a Veil2 function, you simply create a new function, with the same function signature, in the veil2 schema, with the function name prepended with my_.

The following example is from the veil2_demo extension and redefines the veil2.get_accessor() function:

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).

Much of the heavy-lifting of managing users' privileges is done through views and materialized views. By redefining views, we can alter functionality and expand the range of data upon which we operate.

For instance, the veil2.superior_scopes view identifies the hierarchy of scopes for which privilege promotions are available. If we have a database which provides scopes for projects and for organizations, the scope promotion for a project might be to the organization which owns the project. A role assigned in a project context might contain the privilege to view basic organization data, and this privilege would need to be promoted to apply at the organization level. In order to perform this promotion, Veil2 needs to know which projects belong to which organizations. This is what the superior_scopes view is intended to provide, but can only do so if user-defined.

Just as with functions, views owned by the Veil2 extension cannot be safely redefined, but again, as with functions, we can create alternative views and have Veil2 manage them.

To override the built-in version of a Veil2 view, you simply need to define a new view, in the veil2 schema, with the prefix my_. So, to define new functionality for the view veil2.superior_scopes, you would create a new view veil2.my_superior_scopes.

As an example, here is the definition for veil2.my_superior_scopes from the demo:

create or replace
view veil2.my_superior_scopes (
  scope_type_id, scope_id,
  superior_scope_type_id, superior_scope_id
) as
select 4, party_id,  -- Promote org to corp scope
       3, corp_id
  from demo.parties_tbl -- No join needed to scopes as party_id == scope_id
 where party_type_id = 2
union all
select 4, party_id,  -- Promote root orgs within corps to corp scope
       3, party_id
  from demo.parties_tbl
 where party_type_id = 2
   and org_id = 100
   and party_id != 100
union all
select 4, party_id,  -- Promotion of org to higher org
       4, org_id
  from demo.parties_tbl
 where party_type_id = 2
   and party_id != org_id
union all
select 5, s.scope_id,   -- Project to corp promotions
       3, p.corp_id
  from demo.projects p
 inner join veil2.scopes s
    on s.project_id = p.project_id
union all
select 5, s.scope_id,   -- Project to org promotions
       4, p.org_id
  from demo.projects p
 inner join veil2.scopes s
    on s.project_id = p.project_id;
	

To make user-provided functions and views take effect, they must be installed to replace the original system-provided version. Veil2 provides 5 functions for low-level management of user-provided functions and views:

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.

This privilege allows an already authenticated user to effectively become another user, with that user's access rights.

This is primarily intended for testing and verifying functionality. If a user complains that they cannot access something, or that something does not work properly, then by becoming that user you can investigate the problem in a realistic manner without having to ask them to lend you their session or, worse, their authentication credentials.

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).

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.

The association between an accessor and their authentication context is entirely user-defined. Veil2 relies on the veil2.my_get_accessor() function to return an accessor_id for a given combination of username and authentication context. For an example of this, you should look at the veil2_demo (following the text “STEP 3” in the file veil2_demo--<version>.sql.

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.