- Chapter 21. The Veil2 Implementation: What You Need To Know
- 21.1. User Customizations
- 21.2. Authentication
- 21.3. Privileges
- 21.4. Roles
- 21.5. Authentication Contexts
- 21.6. Role Assignment and Contexts
- 21.7. Session Contexts
- 21.8. Role Mappings and Mapping Contexts
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
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
This section provides details of the underlying
Veil2 implementation, intended to help you with
your customization needs.
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
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
PostgreSQL allows tables in extensions to contain user-data.
Such tables must be registered with the extension mechanism
This allows a
where clause to be defined
which specifies how to identify user-provided entries.
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.
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
The mechanism provided by
Veil2 to allow
for user-provided functionality is to allow users to define
their own replacements for built-in
functions. These replacement functions are owned by you
and are not part of the
To define your own version of a
function, you simply create a new function, with the same
function signature, in the
with the function name prepended with
The following example is from the
veil2_demo extension and redefines the
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
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
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
view, you simply need to define a new view, in the
veil2 schema, with the prefix
my_. So, to define new functionality for
veil2.superior_scopes, you would
create a new view
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
Veil2 provides 5 functions for
low-level management of user-provided functions and views:
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
veil2schema 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.
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
Veil2extension, 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.
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.
Restores the original system-provided views from backups made by
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
Veil2 when the system-provided
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.
Veil2 provides barely adequate password-based
bcrypt, and the means to
create your own better authentication methods.
Veil2 allows for new authentication methods
to be defined by creating authentication functions that match
the call signature of
and recording them in
If your custom authentication mechanism requires 2 round-trips,
you can send supplemental data to the client from
by recording a value for
veil2.authentication_types entry. This
will be the name of a function which takes an
session_token as parameters and returns
another token which will be returned as the
session_supplemental value from
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.
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
To allow for expansion of
Veil2's core set of
privileges in future releases, you should start numbering your
own privileges from 20.
Connect is a special built-in privilege
with an important meaning. This privilege allows a user to
create a database session (either dedicated or
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.
Restricting the use of the
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
which will only succeed if you have the
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).
Veil2 is supplied with a small set of
built-in roles. To allow for
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:
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
Immutable roles are intended to be function-level roles only. They should not have other roles assigned to them.
connect role is provided by
Veil2 and is intended to be the only role
that provides the
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.
superuser role is system-provided, and
unmodifiable. It provides every non-implicit role and every
connect. This is built in
to the implementation (in
and are not modifiable through
This role is implicitly assigned to each accessor (via
when their session is established. As supplied it has no
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
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 “
context” it is.
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
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:
And the login pages are:
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
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:
select * from veil2.create_session(<username>, 4, 42);
select * from veil2.create_session(<username>, 4, 43);
select * from veil2.create_session(<username>, 4, 44);
The association between an accessor and their authentication
context is entirely user-defined.
relies on the
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
(following the text “
in the file
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
or implicit as is the case for the
One of the coolest things about
that you can create your own role assignment mechanisms. You
do this by redefining
This view returns all role assignments in all contexts other
than personal context which is handled by
You add new mechanisms by adding extra unions to the base
query against the
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
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;
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
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
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
connect privilege in
a scope equal or superior to the session context.
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
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.
As described in the concepts
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
Discounts role to the
Rep role, while the other would not.
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
mapping context target scope typesystem parameter;
This parameter (recorded in
veil2.system_parameters) specifies scope type that will apply to mapping contexts. By default this is
global scopewhich 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 Managerrole 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.