create table veil2.scope_types ( scope_type_id integer not null, scope_type_name text not null, description text not null ); alter table veil2.scope_types enable row level security;
Identifies the types of security scope for your VPD. This can be thought of as the 'level' of a scope.
Insert one record into this table for each type of scope that you wish to implement. Veil2 comes with 2 built-in scope types: for global and personal scopes.
create table veil2.system_parameters (
parameter_name text not null,
parameter_value text not null,
user_defined boolean
);
alter table veil2.system_parameters add constraint system_parameter__pk
primary key(parameter_name);
alter table veil2.system_parameters enable row level security;
Provides values for various parameters.
Column user_defined: Whether this parameter value was modified by the user. This is
needed for exports using pg_dump.
create table veil2.role_types ( role_type_id integer not null, role_type_name text not null, description text ); alter table veil2.role_types add constraint role_type__pk primary key(role_type_id); alter table veil2.role_types add constraint role_type__name_uk unique(role_type_name); alter table veil2.role_types enable row level security;
A role type is used to classify roles so that they may be shown and used in different ways. This is mostly a VPD implementation choice.
For instance you may choose to distinguish between user and function-level roles so that you can prevent role assignments to user-level roles. In such a case you might add columns to this table to identify specific properties of specific role_types.
create table veil2.privileges ( privilege_id integer not null, privilege_name text not null, promotion_scope_type_id integer, description text ); alter table veil2.privileges add constraint privilege__pk primary key(privilege_id); alter table veil2.privileges add constraint privilege__promotion_scope_type_fk foreign key(promotion_scope_type_id) references veil2.scope_types(scope_type_id); alter table veil2.privileges enable row level security;
This provides all privileges used by our VPD. There should be no need for anyone other than administrators to have any access to this table.
A privilege is the lowest level of access control. It should be used to allow the holder of that privilege to do exactly one thing, for example 'select privileges' should be used to allow the privilege holder to select from the privileges table. It should not be used for any other purpose.
Note that the name of the privilege is only a clue to its usage. We use the privilege ids and not the names to manage access. It is the responsibility of the implementor to ensure that a privilege's name matches the purpose to which it is put.
Column privilege_id: Primary key for privilege. This is the integer that will be used as a
key into our privilege bitmaps. It is not generated from a sequence as
we want to have very tight control of the privilege_ids.
The range of privilege_ids in use should be kept as small as possible. If privileges become deprecated, you should (once you have ensured that the old privilege_id is not in use *anywhere*) try to re-use the old privilege_ids rather than extending the range of privilege_ids by allocating new ones.
This will keep your privilege bitmaps smaller, which should in turn improve performance.
Column privilege_name: A descriptive name for a privilege. This should generally be enough to
figure out the purpose of the privilege.
Column promotion_scope_type_id: Identfies a security scope type to which this privileges scope should
be promoted if possible. This allows roles which will be assigned in
a restricted security context to contain privileges which necessarily
must apply in a superior scope (ie as if they had been assigned in a
superior context).
For example a hypothetical 'select lookup' privilege may be assigned in a team context (via a hypothetical 'team member' role). But if the lookups table is not in any way team-specific it makes no sense to apply that privilege in that scope. Instead, we will promote that privilege to a scope where it does make sense. See the Veil2 docs for more on privilege promotion and on the use of the terms scope and context.
Column description: For any privilege whose purpose cannot easily be determined from the
name, a description of the privilege should appear here.
create table veil2.scopes ( scope_type_id integer not null, scope_id integer not null ); alter table veil2.scopes enable row level security;
A scope, or context, identifies a limit to access. It is a scope_type applied to a specific instance. For example, if access controls are placed in project scopes, there will be one scope record for each project that we wish to manage access to. So for three projects A, B and C, there would be 3 scopes with scope_types of project. This table as created by the Veil2 database creation scripts is incomplete. It needs additional columns to link itself with the scopes it is protecting.
Your implementation must link this scopes table to the tables in your database that provide your scopes. For instance a users table or a projects table.
The approved method for linking your tables to the veil2 scopes table is by defining your own veil2 table that inherits from scopes. Your inherited table will provide foreign key relationships back to your protected database. There are a number of ways to do this. Probably the simplest is to add nullable columns to this table for each type of relational context key and then add appropriate foreign key and check constraints.
For example to implement a corp context with a foreign key back to your corporations table:
create table veil2.scope_corps (
column corp_id integer
) inherits (veil2.scopes);
-- create pk and fks for the new table based on those for veil2.scopes
alter table veil2.scope_corps_link
add constraint scope_corps__corp_fk
foreign key (corp_id)
references my_schema.corporations(corp_id);
-- Ensure that for corp context types we have a corp_id
-- (assume corp_context has scope_type_id = 3)
alter table veil2.scope_corps
add constraint scope_corp__corp_chk
check ((scope_type_id != 3)
or ((scope_type_id = 3) and (corp_id is not null)));
You will, of course, also need to ensure that the corp_id field is populated.
Note that global scope uses scope_id 0. Ideally it would be null, since it does not relate directly to any other entity but that makes defining foreign key relationships (to this table) difficult. Using a reserved value of zero is just simpler (though suckier).
Column scope_type_id: Identifies the type of scope that we are describing.
Column scope_id: This, in conjunction with the scope_type_id, fully identifies a scope
or context. For global scope, this id is 0: ideally it would be null
but as it needs to be part of the primary key of this table, that is
not possible.
The scope_id provides a link back to the database we are protecting, and will usually be the key to some entity that can be said to 'own' data. This might be a party, or a project, or a department.
create table veil2.roles ( role_id integer not null, role_type_id integer not null default(1), role_name text not null, implicit boolean not null default false, immutable boolean not null default false, description text ); alter table veil2.roles add constraint role__pk primary key(role_id); alter table veil2.roles add constraint role__name_uk unique(role_name); alter table veil2.roles add constraint role__type_fk foreign key(role_type_id) references veil2.role_types(role_type_id); alter table veil2.roles enable row level security;
A role is a way of collecting privileges (and other roles) into groups for easier management.
Column role_id: Primary key for role.
Column role_name: A descriptive name for a role. This should generally be enough to
figure out the purpose of the role.
Column implicit: Whether this role is implicitly assigned to all accessors. Such roles
may not be explicitly assigned.
Column immutable: Whether this role is considered unmodifiable. Such roles may not be
the primary role in a role_role assignment, ie you cannot assign other
roles to them.
Column description: For any role whose purpose cannot easily be determined from the
name, a description of the role should appear here.
create table veil2.context_roles ( role_id integer not null, role_name text not null, context_type_id integer not null, context_id integer not null ); alter table veil2.context_roles add constraint context_role__pk primary key(role_id, context_type_id, context_id); alter table veil2.context_roles add constraint context_role__name_uk unique(role_name, context_type_id, context_id); alter table veil2.context_roles add constraint context_role__role_fk foreign key(role_id) references veil2.roles(role_id); alter table veil2.context_roles enable row level security;
This provides a context-based role-name for a role. The purpose of this is to allow certain security contexts to name their own roles. This, coupled with role_roles, allows for role definitions to be different in different contexts. It is primarily aimed at VPDs where there are completely independent sets of accessors. For example in a SaaS implementation where each corporate customer gets their virtual private database and no customer can see any data for any other customer. In such a case it is likely that roles will be different, will have different names, and different sets of roles will exist.
If this makes no sense to you, you probably have no need for it, so don't use it. If do choose to use, do so sparingly as it could lead to great confusion.
create table veil2.role_privileges ( role_id integer not null, privilege_id integer not null ); alter table veil2.role_privileges add constraint role_privilege__pk primary key(role_id, privilege_id); alter table veil2.role_privileges enable row level security;
Records the mapping of privileges to roles. Roles will be assigned to parties in various contexts; privileges are only assigned indirectly through roles. Note that role privileges should not be managed by anyone other than a developer or administrator that understands the requirements of system access controls. Getting this wrong is the best route to your system having poor database security. There should be no need for anyone other than administrators to have any access to this table.
User management of roles should be done through user visible role->role mappings. While this may seem an odd concept, the use of roles in databases provides a good model for how this can work.
Note that the assignment of role to role may be something that is done within a specific security context: consider that the database may be storing data for separate groups of parties (eg companies) and the role->role assignment may therefore need to be specific to those groups (eg a customer liaison role in one company may need different privileges from a similar role in another company).
create table veil2.accessors ( accessor_id integer not null, username text, notes text ); alter table veil2.accessors add constraint accessor__pk primary key(accessor_id); alter table veil2.accessors add constraint accessor__username_uk unique (username); alter table veil2.accessors enable row level security;
Identifies parties that may access our database. If this is a party that should have direct database access (ie they are a database user), we record their username here. This allows our security functions to associate the connected database user with their assigned privileges.
VPD Implementation Notes: You are likely to want to implement a foreign-key relationship back to your users table in your protected database (each accessor is a user). It is likely that your accessor_id can simply be the same as the user_id (or party_id, or person_id...). If this is not the case, you can add columns to this table as needed and define FKs as needed.
In the simple case you will do something like this:
alter table veil2.accessors add constraint accessor__user_fk foreign key(accessor_id) references my_schema.users(user_id);
In the event that you have multiple types of accessors, with overlapping ranges of keys, you may have to extend this table to add an accessor_type, and other columns to provide the actual foreign-key values. As accessor_id is heavily used by Veil2 you *must* ensure that this value is truly unique.
Column username: If this is provided, it should match a database username. This
allows a database user to be associated with the accessor_id, and for
their privileges to be determined.
Column accessor_id: The id of the database accessor. This is the id used throughout Veil2
for determining access rights. Ideally this will be the id of the user
from the protected database
create table veil2.accessor_roles ( accessor_id integer not null, role_id integer not null, context_type_id integer not null, context_id integer not null ); alter table veil2.accessor_roles add constraint accessor_role__pk primary key(accessor_id, role_id, context_type_id, context_id); alter table veil2.accessor_roles enable row level security;
This records the assignment of roles to accessors in various contexts. A role assigned to a party here, grants that accessor all of the privileges that that role has been assigned, whether directly or indirectly.
create unlogged table veil2.sessions (
session_id integer not null
default nextval('veil2.session_id_seq'),
accessor_id integer not null,
login_context_type_id integer not null,
login_context_id integer not null,
session_context_type_id integer not null,
session_context_id integer not null,
mapping_context_type_id integer not null,
mapping_context_id integer not null,
authent_type text not null,
expires timestamp with time zone,
token text not null,
has_authenticated boolean not null,
session_supplemental text,
nonces bitmap,
parent_session_id integer
);
alter table veil2.sessions add constraint session__pk
primary key(session_id);
alter table veil2.sessions add constraint session__accessor_fk
foreign key(accessor_id)
references veil2.accessors(accessor_id);
alter table veil2.sessions enable row level security;
Records active sessions. There should be a background task to delete expired sessions and keep this table vacuumed. Note that for performance reasons we may want to disable any foreign key constraints on this table.
Note that access to this table should not be granted to normal users. This table can be used to determine whether a create_session() call successfully created a session, and so can aid in username fishing.
Column login_context_type_id: This, along with the login_context_id column describes the context
used for authentication of this session. This allows users to log in
in specific contexts (eg for dept a, rather than dept b), within which
role mappings may differ. This context information allows the session
to determine which role mappings to apply.
Column login_context_id: See comment on veil2.sessions.login_context_type_id
Column mapping_context_type_id: This, along with the mapping_context_id column describes the context
used for role->role mapping by this session.
Column mapping_context_id: See comment on veil2.sessions.mapping_context_type_id
Column parent_session_id: Used by become-user sessions to record their parent session_id
create table veil2.authentication_types ( shortname text not null, enabled boolean not null, description text not null, authent_fn text not null, supplemental_fn text, user_defined boolean ); alter table veil2.authentication_types add constraint authentication_type__pk primary key(shortname); alter table veil2.authentication_types enable row level security;
Types of authentication supported by this VPD.
Column shortname: A short textual identifier for this type of authentication. This acts
as the primary key.
Column enabled: Whether this authentication type is currently enabled. If it is not,
you will not be able to authenticate using this method.
Column description: A description of this authentication type.
Column authent_fn: The name of a function that will determine whether a supplied
authentication token is correct.
The signature for this function is:
fn(accessor_id integer, token text) returns boolean;
It will return true if the supplied token is what is expected.
Column supplemental_fn: The name of a function that will return session_supplemental values
for create_session.
The signature for this function is:
fn(accessor_id in integer, session_token in out text, session_supplemental out text) returns record;
The provided session_token is a random value, that may be returned untouched or may be modified. The session_supplemental result is supplemental data for the chosen authentication protocol. This is where you might return the base and modulus selection for a Diffie-Hellman exchange, should you wish to implement such a thing.
Column user_defined: Whether this parameter value was modified by the user. This is
needed for exports using pg_dump.
create table veil2.role_roles ( primary_role_id integer not null, assigned_role_id integer not null, context_type_id integer not null, context_id integer not null ); alter table veil2.role_roles add constraint role_role__pk primary key(primary_role_id, assigned_role_id, context_type_id, context_id); alter table veil2.role_roles enable row level security;
This table shows the mapping of roles to roles in various contexts.
The purpose of context-specific role mappings is to enable custom role mappings in different situations. An example of when this may be useful is when creating a SaaS application for multiple corporate customers. Each corporation can have their own role mappings, unaffected and unseen by other corporations. This means that a CSR role at one corporation may have different privileges from a CSR at another.
create type veil2.session_context_t as ( accessor_id integer, session_id integer, login_context_type_id integer, login_context_id integer, session_context_type_id integer, session_context_id integer, mapping_context_type_id integer, mapping_context_id integer, parent_session_id integer );
Records context for the current session. This type is used for the generation of a veil2_session_context temporary table which is populated by Veil2's session management functions.
Column accessor_id: The id of the accessor whose session this is.
Column accessor_id: The id of the accessor whose access rights (mostly) are being used by
this session. If this is not the same as the accessor_id, then the
session_user has assumed the access rights of this accessor using the
become_user() function.
Column login_context_type_id: This is the context_type_id for the context within which our accessor
has authenticated. This will have been the context_type_id provided
to the create_session() or hello() function that began this session.
Column login_context_id: This is the context_id for the context within which our accessor
has authenticated. This will have been the context_id provided
to the create_session() or hello() function that began this session.
Column session_context_type_id: This is the context_type_id to be used for limiting our session's
assigned roles and from which is determined our
mapping_context_type_id. Ordinarily, this will be the same as our
login_context_type_id, but if create_session() has been provided with
session_context parameters, this will be different. Note that for an
accessor to create such a session they must have connect privilege in
both their login context and their requested session context.
create type veil2.session_privileges_t as ( scope_type_id integer, scope_id integer, roles bitmap, privs bitmap );
Records the privileges for active sessions in each assigned context.
This type is used for the generation of a veil2_session_privileges temporary table which is populated by Veil2's session management functions.
create table veil2.authentication_details ( accessor_id integer not null, authentication_type text not null, authent_token text not null ); alter table veil2.authentication_details enable row level security;
Types of authentication available for individual parties, along with whatever authentication tokens are needed for that form of authentication. Because this table stores authentication tables, access to it must be as thoroughly locked down as possible.
Column authentication_type: Identifies a specific authentication type. More than 1 authentication
type may be available to some parties.
Column authent_token: An authentication token for the party for the given authentication
type. If we were using plaintext passwords (do not do this), this would
be where the password would be stored.
create table veil2.deferred_install ( install_time timestamp with time zone not null); alter table veil2.deferred_install enable row level security;
This table is used solely to provide a hook for a trigger. By inserting into this table, a trigger is fired which will cause any user-provided veil2 objects to replace their equivalent system-provided ones.
The trigger on this table is:
create trigger deferred_install_trg after insert on veil2.deferred_install for each statement execute function veil2.deferred_install_fn();
This trigger exists to allow inserts into the deferred install table to cause user-provided functions and views to be installed after the current system-provided functions have completed running. This is to prevent the function that inserts into the table from being overwritten while it is still running. PostgreSQL may handle this well, I don't know - but I see no reason to stress the implementation any further than I must.
See function get_accessor() for an example of the use of this table.