Chapter 21. The Veil2 Implementation: What You Need To Know

Although Veil2 is built to be customized and modified by you, it comes with a number of built-in features, functionality and assumptions. These exist to provide a sane starting point for your implementation.

For most database implementations only a relatively small number of customizations will be needed. These are primarily in order to integrate Veil2 with your database implementation. For those systems that have more complex requirements, almost all of Veil2 is tweakable by providing your own overriding views and functions for those provided by Veil2.

This section provides details of the underlying Veil2 implementation, intended to help you with your customization needs.

21.1. User Customizations

As a PostgreSQL extension, allowing customization of Veil2 is something of a challenge. The issue is that Veil2's own database objects are expected by PostgreSQL to not be user-modifiable. This is so that extensions can be upgraded in place using standard extension mechanisms; and so that backups taken using pg_dump can allow a database to be restored by simply re-installing the extension. What this means is that any modifications you make to database objects that are part of the Veil2 extension will be lost if you restore from a pg_dump backup, or if you upgrade the Veil2 extension.

It is therefore vital that any user modifications are to objects that are not owned by the extension. To deal with this, we limit the ways in which customizations to Veil2 are made:

21.1.1. Customized Data

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.

21.1.2. Customized Functions

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

21.1.3. Customized Views

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;
	

21.1.4. Managing User-Provided Functionality

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:

  • veil2.install_user_functions()

    This function copies all user-provided functions over their corresponding system-provided counterparts. Before doing this, it ensures that a backup copy of the original system-provided function exists. Any function defined in the veil2 schema with a name beginning with my_ that matches a function without that prefix will be copied.

    This function should be called after any modification of your user-provided functions. It is safe to call this function any number of times.

  • veil2.restore_system_functions()

    Should you need to restore original functions from their backup copies you should call this function. This function exists primarily for use when upgrading the Veil2 extension, but if you have mistakenly overwritten system-provided functionality with a buggy my_xxxx() function you should use this to restore the original functionality before dropping or fixing your version.

  • veil2.install_user_views()

    This installs user-provided views in the same way that veil2.install_user_functions() installs user-provided functions.

    This function should be called after any modification of your user-provided views. It is safe to call this function any number of times.

  • veil2.restore_system_views()

    Restores the original system-provided views from backups made by veil2.install_user_views().

  • veil2.init()

    This installs all user-provided views and functions and also refreshes all materialized views. Call this function any time that you modify one of your user-provided views or functions. This will install your latest versions and ensure that everything is ready to go. This function can safely be called any time that you may doubt whether you have the latest versions of your views and functions in use.

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.

21.2. Authentication

Veil2 provides barely adequate password-based authentication using bcrypt, and the means to create your own better authentication methods.

Authentication is performed by the veil2.authenticate() function which is called from veil2.open_connection(), one of the functions used in Shared Session Authentication.

Veil2 allows for new authentication methods to be defined by creating authentication functions that match the call signature of veil2.authenticate_bcrypt() and recording them in veil2.authentication_types.

If your custom authentication mechanism requires 2 round-trips, you can send supplemental data to the client from veil2.create_session() by recording a value for supplemental_fn in the veil2.authentication_types entry. This will be the name of a function which takes an accessor_id and session_token as parameters and returns another token which will be returned as the session_supplemental value from veil2.create_session().

It should be possible to implement most authentication methods using these 2 functions. If you need anything more complex than this, you will have to create your own versions of the session management functions.

21.3. Privileges

Veil2 is supplied with a base set of privileges. These are to manage access to core functionality, and to the built-in Veil2 tables (which are themselves secured by Veil2).

To allow for expansion of Veil2's core set of privileges in future releases, you should start numbering your own privileges from 20.

21.3.1. Connect Privilege

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.

21.3.2. Become User Privilege

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

21.4. Roles

Veil2 is supplied with a small set of built-in roles. To allow for expansion of Veil2's built-in roles in future releases, you should start numbering your own roles from 5.

Roles have 2 attributes that require some explanation:

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

21.4.1. The Connect Role

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.

21.4.2. The Superuser Role

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.

21.4.3. The Personal Context Role

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.

21.5. Authentication Contexts

As described in the concepts section, having distinct authentication contexts means that you can have distinct sets of usernames for different groupings of users. Typically this would be for different organizations based on some sort of organization context type.

If you don't need this functionality you can skip this section and create the simplest possible my_get_accessor() function, something like this:

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 user_id
    into _result
    from my_users
   where user_name = username;
   -- We could choose to ensure that context_type_id = 1 and
   -- context_id = 0, but instead we just ignore those parameters.
  return _result;
end;
$$
language plpgsql security definer stable leakproof;
	  
      

If your application serves a number of distinct customers, each effectively having their own web site, then the authentication context will be provided by the login page for that web site. For example, if your customers are:

  • Alicecorp;
  • Bobcorp;
  • Carolcorp.

And the login pages are:

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

Then each login page will provide different context data for the authentication calls. Assuming that we have defined a corp context with a context_type_id of 3, and that the accessor_ids for AliceCorp, BobCorp, and CarolCorp are 42, 43, and 44 respectively, then our veil2.create_session() calls for each of the login pages will be as follows:

  • 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);	    
    	  

21.5.1. Associating Accessors With Their Authentication Context

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.

21.6. Role Assignment and Contexts

Each role assignment in Veil2 happens within a specific assignment context. The assignment may be explicit, as it is when roles are assigned to accessors in veil2.accessor_roles or implicit as is the case for the personal context role.

21.6.1. Creating New Role Assignment Mechanisms

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; 
	

21.7. Session Contexts

As described in session contexts in the concepts section, a user's session context need not be the same as their authentication context. The user-provided function my_get_accessor() is used to determine the accessor_id for a user based on a username and a context. This context, ordinarily - see Separating Login and Session Contexts below), will be the session context. Your my_get_accessor() function may use the context parameters as it sees fit: if you require a global authentication context, the parameters can be ignored; or it can require the supplied context to be the authentication context; or it can require that the supplied context is related to the authentication context (eg a child context of the authentication context). What it chooses to do is entirely up to your implementation.

Note that in order to successfully authenticate, the user must have connect privilege in a scope equal or superior to the session context.

21.7.1. Separating Login and Session Contexts

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.

21.8. Role Mappings and Mapping Contexts

As described in the concepts section, Veil2 allows roles to be mapped to other roles, and for those role mappings to be different for different users.

The purpose of this is to allow different groups of users to be able to separately manage their own role->role mappings. For instance, if one company allows Customer Service Reps to provide financial incentives to their customers and another does not, the first company might assign the Manage Discounts role to the Customer Service Rep role, while the other would not.

If your Veil2 protected system is providing distinct virtual databases for a number of independent clients, this is an important feature.

Veil2 allows this by providing different mapping contexts to apply to different groups of users. The mapping context that applies for a given role assignment depends on:

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