/* ----------
* veil2--0.9.2.sql
*
* Create the veil2 extension for Veil2 version 0.9.2
*
* Copyright (c) 2020 Marc Munro
* Author: Marc Munro
* License: GPL V3
*
* ----------
*/
-- Although technically it is evil to create roles within extensions,
-- the veil_user role is so intrinsic to veil2 that it is a necessity.
-- Please don't hate me for it.
do
$$
declare
_result integer;
begin
select 1 into _result from pg_roles where rolname = 'veil_user';
if not found then
execute 'create role veil_user';
end if;
end;
$$;
comment on role veil_user is
'This role will have read access to all veil2 tables. That is not to
say that an assignee of the role will be able to see the data in those
tables, but that they will be allowed to try.';
\echo ...veil2 schema...
create schema if not exists veil2;
comment on schema veil2 is
'Schema containing veil2 database objects.';
revoke all on schema veil2 from public;
grant usage on schema veil2 to veil_user;
-- Create the VEIL2 schema tables
\echo ......scope_types...
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
add constraint scope_type__pk
primary key(scope_type_id);
alter table veil2.scope_types
add constraint scope__name_uq
unique(scope_type_name);
comment on table veil2.scope_types is
'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.';
revoke all on veil2.scope_types from public;
grant select on veil2.scope_types to veil_user;
\echo ......scopes...
create table veil2.scopes (
scope_type_id integer not null,
scope_id integer not null
);
alter table veil2.scopes
add constraint scope__pk
primary key(scope_type_id, scope_id);
alter table veil2.scopes
add constraint scope__type_fk
foreign key(scope_type_id)
references veil2.scope_types(scope_type_id);
comment on table veil2.scopes is
'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).';
comment on column veil2.scopes.scope_type_id is
'Identifies the type of scope that we are describing.';
comment on column veil2.scopes.scope_id is
'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.';
revoke all on veil2.scopes from public;
grant select on veil2.scopes to veil_user;
\echo ......context_exists_chk() (function)...
create or replace
function veil2.context_exists_chk()
returns trigger as
$$
begin
if not exists (
select null
from veil2.scopes a
where a.scope_type_id = new.context_type_id
and a.scope_id = new.context_id)
then
-- Pseudo Integrity Constraint Violation
raise exception using
message = TG_OP || ' on table "' ||
TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME ||
'" violates foreign key constraint "' ||
coalesce(TG_ARGV[0], 'null') || '"',
detail = 'Key (scope_type_id, scope_id)=(' ||
coalesce(new.context_type_id::text, 'null') || ', ' ||
coalesce(new.context_id::text, 'null') ||
') is not present in table "veil2.scopes"',
errcode = '23503';
end if;
return new;
end;
$$
language plpgsql security definer stable;
revoke all on function veil2.context_exists_chk() from public;
comment on function veil2.context_exists_chk() is
'Trigger to be used instead of FK-constraints against the scopes
table. This is because we expect to use inheritence to extend the
scopes table to contain references to user-provided tables, and
inheritence does not work well with foreign-key constraints.';
\echo ......privileges...
create table veil2.privileges (
privilege_id integer not null,
privilege_name text not null,
promotion_scope_type_id integer,
description text
);
comment on table veil2.privileges is
'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.';
comment on column veil2.privileges.privilege_id is
'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.';
comment on column veil2.privileges.privilege_name is
'A descriptive name for a privilege. This should generally be enough to
figure out the purpose of the privilege.';
comment on column veil2.privileges.promotion_scope_type_id is
'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.';
comment on column veil2.privileges.description is
'For any privilege whose purpose cannot easily be determined from the
name, a description of the privilege should appear here.';
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);
revoke all on veil2.privileges from public;
grant select on veil2.privileges to veil_user;
\echo ......role_types...
create table veil2.role_types (
role_type_id integer not null,
role_type_name text not null,
description text
);
comment on table veil2.role_types is
'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.';
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);
revoke all on veil2.role_types from public;
grant select on veil2.role_types to veil_user;
\echo ......roles...
create sequence veil2.role_id_seq;
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
);
comment on table veil2.roles is
'A role is a way of collecting privileges (and other roles) into groups
for easier management.';
comment on column veil2.roles.role_id is
'Primary key for role.';
comment on column veil2.roles.role_name is
'A descriptive name for a role. This should generally be enough to
figure out the purpose of the role.';
comment on column veil2.roles.implicit is
'Whether this role is implicitly assigned to all accessors. Such roles
may not be explicitly assigned.';
comment on column veil2.roles.immutable is
'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.';
comment on column veil2.roles.description is
'For any role whose purpose cannot easily be determined from the
name, a description of the role should appear here.';
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);
revoke all on veil2.roles from public;
grant select on veil2.roles to veil_user;
\echo ......context_roles...
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
);
comment on table veil2.context_roles is
'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.';
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);
create trigger context_role__context_fk
before insert or update on veil2.context_roles
for each row execute function
veil2.context_exists_chk('context_role__context_fk');
comment on trigger context_role__context_fk on veil2.context_roles is
'This trigger is in place of a foreign-key constraint against the
scopes table. We use this rather than the FK as we expect the scopes
table to be extended through inheritence which does not play nicely
with FK-constraints.';
revoke all on veil2.context_roles from public;
grant select on veil2.context_roles to veil_user;
\echo ......role_privileges...
create table veil2.role_privileges (
role_id integer not null,
privilege_id integer not null
);
comment on table veil2.role_privileges is
'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).';
alter table veil2.role_privileges add constraint role_privilege__pk
primary key(role_id, privilege_id);
alter table veil2.role_privileges
add constraint role_privilege__role_fk
foreign key(role_id)
references veil2.roles(role_id);
alter table veil2.role_privileges
add constraint role_privilege__privilege_fk
foreign key(privilege_id)
references veil2.privileges(privilege_id);
revoke all on veil2.role_privileges from public;
grant select on veil2.role_privileges to veil_user;
\echo ......role_roles...
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
);
comment on table veil2.role_roles is
'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.';
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
add constraint role_role__primary_role_fk
foreign key(primary_role_id)
references veil2.roles(role_id);
alter table veil2.role_roles
add constraint role_role__assigned_role_fk
foreign key(assigned_role_id)
references veil2.roles(role_id);
create trigger role_role__context_fk
before insert or update on veil2.role_roles
for each row execute function
veil2.context_exists_chk('role_role__context_fk');
comment on trigger role_role__context_fk on veil2.role_roles is
'This trigger is in place of a foreign-key constraint against the
scopes table. We use this rather than the FK as we expect the scopes
table to be extended through inheritence which does not play nicely
with FK-constraints.';
revoke all on veil2.role_roles from public;
grant select on veil2.role_roles to veil_user;
\echo .....accessors...
create table veil2.accessors (
accessor_id integer not null,
username text,
notes text
);
comment on table veil2.accessors is
'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.';
comment on column veil2.accessors.username is
'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.';
comment on column veil2.accessors.accessor_id is
'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';
alter table veil2.accessors add constraint accessor__pk
primary key(accessor_id);
alter table veil2.accessors add constraint accessor__username_uk
unique (username);
revoke all on veil2.accessors from public;
grant select on veil2.accessors to veil_user;
\echo ......authentication_types...
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
);
comment on table veil2.authentication_types is
'Types of authentication supported by this VPD.';
comment on column veil2.authentication_types.shortname is
'A short textual identifier for this type of authentication. This acts
as the primary key.';
comment on column veil2.authentication_types.enabled is
'Whether this authentication type is currently enabled. If it is not,
you will not be able to authenticate using this method.';
comment on column veil2.authentication_types.description is
'A description of this authentication type.';
comment on column veil2.authentication_types.authent_fn is
'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.';
comment on column veil2.authentication_types.supplemental_fn is
'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.';
comment on column veil2.authentication_types.user_defined is
'Whether this parameter value was modified by the user. This is
needed for exports using pg_dump.';
alter table veil2.authentication_types add constraint authentication_type__pk
primary key(shortname);
revoke all on veil2.authentication_types from public;
grant select on veil2.authentication_types to veil_user;
\echo ......authentication_details...
create table veil2.authentication_details (
accessor_id integer not null,
authentication_type text not null,
authent_token text not null
);
comment on table veil2.authentication_details is
'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.';
comment on column veil2.authentication_details.authentication_type is
'Identifies a specific authentication type. More than 1 authentication
type may be available to some parties.';
comment on column veil2.authentication_details.authent_token is
'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.';
alter table veil2.authentication_details
add constraint authentication_detail__pk
primary key(accessor_id, authentication_type);
alter table veil2.authentication_details
add constraint authentication_detail__authent_type_fk
foreign key(authentication_type)
references veil2.authentication_types(shortname);
alter table veil2.authentication_details
add constraint authentication_detail__accessor_fk
foreign key(accessor_id)
references veil2.accessors(accessor_id)
on delete cascade on update cascade;
comment on constraint authentication_detail__accessor_fk
on veil2.authentication_details is
'Since accessors may be updated or deleted as a result of transactions
in our secured database, we must allow such updates or deletions to
cascade to this table as well. The point of this is that the
application need not know about fk relationships that are internal to
Veil2.';
revoke all on veil2.authentication_details from public;
grant select on veil2.authentication_details to veil_user;
\echo ......accessor_roles...
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
);
comment on table veil2.accessor_roles is
'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.';
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
add constraint accessor_role__accessor_fk
foreign key(accessor_id)
references veil2.accessors(accessor_id)
on delete cascade on update cascade;
comment on constraint accessor_role__accessor_fk
on veil2.accessor_roles is
'Since accessors may be updated or deleted as a result of transactions
in our secured database, we must allow such updates or deletions to
cascade to this table as well. The point of this is that the
application need not know about fk relationships that are internal to
Veil2.';
create trigger accessor_role__context_fk
before insert or update on veil2.accessor_roles
for each row execute function
veil2.context_exists_chk('accessor_role__context_fk');
comment on trigger accessor_role__context_fk on veil2.accessor_roles is
'This trigger is in place of a foreign-key constraint against the
scopes table. We use this rather than the FK as we expect the scopes
table to be extended through inheritence which does not play nicely
with FK-constraints.';
revoke all on veil2.accessor_roles from public;
grant select on veil2.accessor_roles to veil_user;
\echo ......sessions...
create sequence veil2.session_id_seq;
create unlogged table veil2.sessions (
session_id bigint 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 bigint
);
comment on table veil2.sessions is
'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.';
comment on column veil2.sessions.login_context_type_id is
'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.';
comment on column veil2.sessions.login_context_id is
'See comment on veil2.sessions.login_context_type_id';
comment on column veil2.sessions.mapping_context_type_id is
'This, along with the mapping_context_id column describes the context
used for role->role mapping by this session.';
comment on column veil2.sessions.mapping_context_id is
'See comment on veil2.sessions.mapping_context_type_id';
comment on column veil2.sessions.parent_session_id is
'Used by become-user sessions to record their parent session_id';
alter table veil2.sessions add constraint session__pk
primary key(session_id);
/*
* For performance reasons we will not create FK constraints on this
* table.
alter table veil2.sessions add constraint session__accessor_fk
foreign key(accessor_id)
references veil2.accessors(accessor_id);
*/
revoke all on veil2.sessions from public;
grant select on veil2.sessions to veil_user;
\echo ......system_parameters...
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);
comment on table veil2.system_parameters is
'Provides values for various parameters.';
comment on column veil2.system_parameters.user_defined is
'Whether this parameter value was modified by the user. This is
needed for exports using pg_dump.';
revoke all on veil2.system_parameters from public;
grant select on veil2.system_parameters to veil_user;
\echo ......deferred_install...
create table veil2.deferred_install (
install_time timestamp with time zone not null);
comment on table veil2.deferred_install is
'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.';
revoke all on veil2.deferred_install from public;
grant select on veil2.deferred_install to veil_user;
\echo ......session_privileges_t...
create type veil2.session_privileges_t as (
scope_type_id integer,
scope_id integer,
roles bitmap,
privs bitmap
);
comment on type veil2.session_privileges_t is
'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.';
\echo ......session_context_t(type)...
create type veil2.session_context_t as (
accessor_id integer,
session_id bigint,
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 bigint
);
comment on type veil2.session_context_t is
'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.';
comment on column veil2.session_context_t.accessor_id is
'The id of the accessor whose session this is.';
comment on column veil2.session_context_t.accessor_id is
'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.';
comment on column veil2.session_context_t.login_context_type_id is
'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.';
comment on column veil2.session_context_t.login_context_id is
'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.';
comment on column veil2.session_context_t.session_context_type_id is
'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.';
\echo ......accessor_privileges_cache
create unlogged table veil2.accessor_privileges_cache (
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,
scope_type_id integer not null,
scope_id integer not null,
roles bitmap not null,
privs bitmap not null
);
comment on table veil2.accessor_privileges_cache is
'Table used to cache accessor_privileges returned by
veil2.session_privileges_v.
This is automatically populated by the Veil2 session management
functions for any combination of accessor and session context for
which it contains no data.
It should be truncated whenever any underlying role, privilege or
context data is updated, and records for individual accessors should
be deleted whenever their role assignments are updated.';
create index accessor_privileges_cache__accessor_idx
on veil2.accessor_privileges_cache(accessor_id);
comment on index veil2.accessor_privileges_cache__accessor_idx is
'There is no need for a PK on this cache table. The way this table is
used is:
- retrieve the scopes, roles and privs for a given accessor and
login context: extending the index to include login_context will
have very little impact on performance;
- truncating the whole thing when roles, role_roles or privileges
are modified;
- removing entries for a single accessor when an accessor''s roles
have changed: an index solely on accessor_id is perfect for this.';
-- Create the VEIL2 schema views, including matviews
--
\echo ......all_role_roles...
create or replace
view veil2.all_role_roles (
primary_role_id, assigned_role_id,
context_type_id, context_id) as
with recursive assigned_roles (
primary_role_id, assigned_role_id,
context_type_id, context_id) as
(
-- get all role->role assignments, both direct and indirect, in all contexts
select primary_role_id, assigned_role_id,
context_type_id, context_id,
bitmap(primary_role_id) + assigned_role_id as roles_encountered
from veil2.role_roles
union all
select ar.primary_role_id, rr.assigned_role_id,
ar.context_type_id, ar.context_id,
ar.roles_encountered + rr.assigned_role_id
from assigned_roles ar
inner join veil2.role_roles rr
on rr.primary_role_id = ar.assigned_role_id
and rr.context_type_id = ar.context_type_id
and rr.context_id = ar.context_id
and not ar.roles_encountered ? rr.assigned_role_id
and rr.primary_role_id != 1 -- Superuser role is handled below
),
superuser_roles (primary_role_id, assigned_role_id) as
(
select 1, role_id
from veil2.roles
where role_id not in (1, 0) -- not connect and not superuser
and not implicit -- and not implicitly assigned roles
)
select primary_role_id, assigned_role_id,
context_type_id, context_id
from assigned_roles
union all
select primary_role_id, assigned_role_id,
null, null
from superuser_roles;
comment on view veil2.all_role_roles is
'Show all role to role mappings in all contexts. If the context is
null, the mapping applies in all contexts, taking into account
role mappings that occur indirectly through other role mappings.
Indirect mappings occur through other mappings (ie mappings are
transitive). Eg if a is assigned to b and b to c, then by transitivity
a is assigned (indirectly) to c.
Note that the superuser role is implicitly assigned all non-implicit
roles except connect.';
revoke all on veil2.all_role_roles from public;
grant select on veil2.all_role_roles to veil_user;
\echo ......all_role_privileges...
create or replace
view veil2.all_role_privileges_v as
with superuser_privs as
(
-- Superuser role has implied assignments of all privileges except
-- connect.
select bitmap_of(privilege_id) as privileges
from veil2.privileges
where privilege_id != 0
)
select r.role_id as role_id,
rr.context_type_id as mapping_context_type_id,
rr.context_id as mapping_context_id,
coalesce(bitmap_of(rr.assigned_role_id) + r.role_id,
bitmap(r.role_id)) as roles,
case
when r.role_id = 1 then (select privileges from superuser_privs)
else coalesce(bitmap_of(rp.privilege_id),
bitmap())
end as privileges
from veil2.roles r
left outer join veil2.all_role_roles rr
on rr.primary_role_id = r.role_id
left join veil2.role_privileges rp
on rp.role_id = r.role_id
or rp.role_id = rr.assigned_role_id
group by r.role_id, rr.context_type_id,
rr.context_id;
comment on view veil2.all_role_privileges_v is
'Provides all role to role mappings, with their resulting privileges
in all mapping contexts. If the mapping context is null, the mapping
applies in all mapping contexts.
For performance reasons a materialized view veil2.all_role_privileges,
has been created. This must be refreshed whenever data underlying
this view is updated.';
create or replace
view veil2.all_role_privileges_info as
select role_id, mapping_context_type_id,
mapping_context_id, to_array(roles) as roles,
to_array(privileges) as privileges
from veil2.all_role_privileges_v;
comment on view veil2.all_role_privileges_info is
'Developer view on all_role_privileges showing roles and privileges as
arrays of integers for easier comprehension.';
create materialized view veil2.all_role_privileges as
select * from veil2.all_role_privileges_v;
comment on materialized view veil2.all_role_privileges is
'Materialized view on veil2.all_role_privileges_v. This exists to
improve the performance of veil2.session_privileges_v.
Any time that the data underlying all_role_privileges_v is modified,
this materialized view should be refreshed.';
revoke all on veil2.all_role_privileges_v from public;
grant select on veil2.all_role_privileges_v to veil_user;
revoke all on veil2.all_role_privileges from public;
grant select on veil2.all_role_privileges to veil_user;
revoke all on veil2.all_role_privileges_info from public;
grant select on veil2.all_role_privileges_info to veil_user;
\echo ......accessor_contexts...
create or replace
view veil2.accessor_contexts (
accessor_id, context_type_id, context_id
) as
select accessor_id, 1, 0
from veil2.accessors;
comment on view veil2.accessor_contexts is
'This view lists the allowed session (and login, where different)
contexts for accessors. The system-provided version of this view may
be overridden by providing an equivalent view called
veil2.my_accessor_contexts.
When an accessor opens a session, they choose a session context. This
session context determines which set of role to role mappings are in
play. Typically, there will only be one such set, as provided by the
default implementation of this view. If however, your application
requires separate contexts to have different role to role mappings,
you should modify this view to map your accessors with that context.
Typically this will be used in a situation where your application
serves a number of different clients, each of which have their own
role definitions. Each accessor will belong to one of those clients
and this view should be modified to make that mapping apparent.
A typical view definition might be:
select party_id, 3, client_id
from app_schema.parties
union all
select party_id, 1, 0
from mycorp_schema.superusers;
which would allow those defined in the superusers table to connect in
the global scope, and those defined in the parties table to connect
in the context of the client that they work for.
Note that any change to the underlying data of this view (ie one that
changes what the view will show) *must* cause a full refresh of all
Veil2 materialized views and caches.';
\echo ......superior_scopes...
create or replace
view veil2.superior_scopes (
scope_type_id, scope_id,
superior_scope_type_id, superior_scope_id
) as
select null::integer, null::integer,
null::integer, null::integer
where false;
comment on view veil2.superior_scopes is
'This view identifies superior scopes for determining the scope
hierarchy. This is used for determing how to promote privileges when
privilege promotion is needed, which happens when a role that is
assigned in a restricted security context has privileges that must be
applied in a less restricted scope. Note that promotion to global
scope is always possible and is not managed through this view.
If you have restricted scopes which are descendant scopes of less
restricted ones, and you need privileges assigned in the restricted
context to be promoted to the less restricted one, you must override
this view to show which scopes may be promoted to which other scopes.
For example if you have a corp scope type and a dept scope type which
is a sub-scope of it, and your departments table identifies the
corp_id for each department, you would define your over-riding view
something like this:
create or replace
view veil2.my_superior_scopes (
scope_type_id, scope_id,
superior_scope_type_id, superior_scope_id
) as
select 96, -- dept scope type id
department_id,
95, -- corp scope type id
corp_id
from departments;
Multi-level context promotions (eg to grandparent or great-grandparent
scopes) will be handled by veil2.all_superior_scopes which you should
have no need to modify.
Note that any change to the underlying data of this view (ie one that
changes what the view will show) *must* cause a full refresh of all
Veil2 materialized views and caches.';
revoke all on veil2.superior_scopes from public;
grant select on veil2.superior_scopes to veil_user;
\echo ......all_superior_scopes...
create or replace
view veil2.all_superior_scopes_v (
scope_type_id, scope_id,
superior_scope_type_id, superior_scope_id,
is_type_promotion
) as
with recursive recursive_superior_scopes as
(
select scope_type_id, scope_id,
superior_scope_type_id, superior_scope_id,
scope_type_id != superior_scope_type_id
from veil2.superior_scopes
union
select rsp.scope_type_id, rsp.scope_id,
sp.superior_scope_type_id, sp.superior_scope_id,
sp.scope_type_id != sp.superior_scope_type_id
from recursive_superior_scopes rsp
inner join veil2.superior_scopes sp
on sp.scope_type_id = rsp.superior_scope_type_id
and sp.scope_id = rsp.superior_scope_id
where not ( sp.superior_scope_type_id = rsp.superior_scope_type_id
and sp.superior_scope_id = rsp.superior_scope_id)
)
select *
from recursive_superior_scopes;
comment on view veil2.all_superior_scopes_v is
'This takes the simple user-provided view veil2.superior_scopes and
makes it recursive so that if context a contains scope b and scope b
contains scope c, then this view will return rows for scope c
promoting to both scope b and scope a.
You should not need to modify this view when creating your custom VPD
implementation.
Note that for performance reasons a materialized version of this view,
veil2.all_superior_scopes, has been created. Any change to the data
underlying this view must result in the materialized view being
refreshed.';
create
materialized view veil2.all_superior_scopes
as select * from veil2.all_superior_scopes_v;
comment on materialized view veil2.all_superior_scopes is
'This is a materialized view on veil2.all_superior_scopes_v. It
exists in order to improve the performance of
veil2.session_privileges_v.
It must be fully refreshed whenever the underlying data for
veil2.superior_scopes is updated.';
revoke all on veil2.all_superior_scopes from public;
grant select on veil2.all_superior_scopes to veil_user;
revoke all on veil2.all_superior_scopes_v from public;
grant select on veil2.all_superior_scopes_v to veil_user;
\echo ......scope_tree...
create or replace
view veil2.scope_tree (scope_tree) as
with recursive
top_scopes as
(
select distinct
sp.superior_scope_id as root_scope_id,
sp.superior_scope_type_id as root_scope_type_id,
st.scope_type_name as root_scope_type_name,
st.scope_type_id || ' (' || st.scope_type_name ||
').' || sp.superior_scope_id as root_full_name
from veil2.superior_scopes sp
inner join veil2.scope_types st
on st.scope_type_id = sp.superior_scope_type_id
where (sp.superior_scope_type_id, sp.superior_scope_id) not in (
select sp2.scope_type_id, sp2.scope_id
from veil2.superior_scopes sp2)
),
recursive_part as
(
select 1 as depth,
root_scope_id as scope_id,
root_scope_type_id as scope_type_id,
root_full_name as full_name,
'(' || root_scope_type_id || '.' || root_scope_id || ')' as path,
length(root_full_name) as path_length
from top_scopes
union all
select rp.depth + 1,
sp.scope_id,
sp.scope_type_id,
st.scope_type_id || ' (' ||
st.scope_type_name || ').' || sp.scope_id,
rp.path || '(' || sp.scope_type_id || '.' || sp.scope_id || ')',
length(st.scope_type_name || '.' || sp.scope_id) + path_length
from recursive_part rp
inner join veil2.superior_scopes sp
on sp.superior_scope_id = rp.scope_id
and sp.superior_scope_type_id = rp.scope_type_id
inner join veil2.scope_types st
on st.scope_type_id = sp.scope_type_id
)
select format('%' || ((depth * 4) - 2) || 's', '+ ') ||
full_name
from recursive_part
order by path;
comment on view veil2.scope_tree is
'Provides a simple ascii-formatted tree representation of our scope
promotions tree. This is an aid to data visualisation for data
designers and administrators and is not used elsewhere in Veil2.';
revoke all on veil2.scope_tree from public;
grant select on veil2.scope_tree to veil_user;
\echo ......promotable_privileges...
create view veil2.promotable_privileges (
scope_type_id, privilege_ids)
as
select p.promotion_scope_type_id, bitmap_of(p.privilege_id)
from veil2.privileges p
where p.promotion_scope_type_id is not null
group by p.promotion_scope_type_id;
comment on view veil2.promotable_privileges is
'Provide bitmaps of those privileges that may be promoted, mapped to the
context types to which they should promote.';
create view veil2.promotable_privileges_info (
scope_type_id, privilege_ids)
as
select scope_type_id, to_array(privilege_ids)
from veil2.promotable_privileges;
comment on view veil2.promotable_privileges_info is
'As veil2.promotable_privileges with bitmaps shown as arrays. Info
views are intended as developer-readable versions of the non-info
views.';
revoke all on veil2.promotable_privileges from public;
grant select on veil2.promotable_privileges to veil_user;
revoke all on veil2.promotable_privileges_info from public;
grant select on veil2.promotable_privileges_info to veil_user;
\echo ......all_accessor_roles...
create or replace
view veil2.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;
comment on view veil2.all_accessor_roles is
'Provides all of an accessor''s explicit role assignments, ie it does
not provide the personal context role.
If you have any explicitly assigned roles that are not granted through
the veil2.accessor_role table, you must provide your own definition of
this view (called veil2.my_all_accessor_roles). For example if you
have a project context that is dependent on an accessor being assigned
to a project you might redefine the view as follows:
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 party_id, role_id,
99, -- id for project context_type
project_id
from project_parties;
Note that any change to the underlying data of this view (ie one that
changes what the view will show) *must* cause a full or partial
refresh of all Veil2 materialized views and caches.';
revoke all on veil2.all_accessor_roles from public;
grant select on veil2.all_accessor_roles to veil_user;
\echo ......all_accessor_roles_plus...
create or replace
view veil2.all_accessor_roles_plus as
select accessor_id, role_id,
context_type_id, context_id
from veil2.all_accessor_roles
union all
select accessor_id, 2, 2, accessor_id
from veil2.accessors;
comment on view veil2.all_accessor_roles_plus is
'As all_accessor_roles but also providing the implicitly assigned
personal context role for each accessor. ';
revoke all on veil2.all_accessor_roles_plus from public;
grant select on veil2.all_accessor_roles_plus to veil_user;
\echo ......role_chains...
create or replace
view veil2.role_chains as
with recursive role_chains
as
(
select rr.primary_role_id, rr.assigned_role_id,
rr.primary_role_id::text || '->' ||
rr.assigned_role_id::text as id_chain,
r1.role_name || '->' || r2.role_name as name_chain,
rr.context_type_id,
rr.context_id,
bitmap(rr.primary_role_id) + rr.assigned_role_id as roles_bitmap
from veil2.role_roles rr
inner join veil2.roles r1
on r1.role_id = rr.primary_role_id
inner join veil2.roles r2
on r2.role_id = rr.assigned_role_id
union all
select rc.primary_role_id, rr.assigned_role_id,
rc.id_chain || '->' || rr.assigned_role_id::text,
rc.name_chain || '->' || r.role_name,
rc.context_type_id,
rc.context_id,
rc.roles_bitmap + rr.assigned_role_id
from role_chains rc
inner join veil2.role_roles rr
on rr.primary_role_id = rc.assigned_role_id
and rr.context_type_id = rc.context_type_id
and rr.context_id = rc.context_id
inner join veil2.roles r
on r.role_id = rr.assigned_role_id
where not rc.roles_bitmap ? rr.assigned_role_id
),
all_contexts as
(
select distinct context_type_id, context_id
from role_chains
),
base_roles as
(
select r.role_id as primary_role_id,
r.role_id as assigned_role_id,
r.role_id::text as id_chain,
r.role_name as name_chain,
ac.context_type_id,
ac.context_id
from veil2.roles r
cross join all_contexts ac
)
select primary_role_id, assigned_role_id,
context_type_id,
context_id, id_chain, name_chain
from role_chains
union all
select primary_role_id, assigned_role_id,
context_type_id,
context_id, id_chain, name_chain
from base_roles
order by 3, 4, 1, 2;
comment on view veil2.role_chains is
'This is a developer view. It is intended for development and
debugging, and provides a way to view role mappings in a simple but
complete way. Try it, it should immediately make sense.';
revoke all on veil2.role_chains from public;
grant select on veil2.role_chains to veil_user;
\echo ......privilege_assignments...
create or replace
view veil2.privilege_assignments as
select aar.accessor_id, rp.privilege_id,
aar.context_type_id as ass_cntxt_type_id,
aar.context_id as ass_cntxt_id,
coalesce(p.promotion_scope_type_id,
aar.context_type_id) as scope_type_id,
coalesce(asp.superior_scope_id,
aar.context_id) as scope_id,
rc.primary_role_id as ass_role_id,
rc.assigned_role_id as priv_bearing_role_id,
rc.id_chain as role_id_mapping,
rc.name_chain as role_name_mapping,
rc.context_type_id as map_cntxt_type_id,
rc.context_id as map_cntxt_id
from (
select role_id, privilege_id
from veil2.role_privileges
union all
select 1, privilege_id
from veil2.privileges
) rp
inner join veil2.privileges p
on p.privilege_id = rp.privilege_id
inner join veil2.role_chains rc
on rc.assigned_role_id = rp.role_id
inner join veil2.all_accessor_roles_plus aar
on aar.role_id = rc.primary_role_id
left outer join veil2.all_superior_scopes asp
on asp.scope_type_id = aar.context_type_id
and asp.scope_id = aar.context_id
and asp.superior_scope_type_id = p.promotion_scope_type_id
and asp.is_type_promotion;
comment on view veil2.privilege_assignments is
'Developer view that shows how accessors get privileges. It shows the
roles that the user is assigned, and the context in which they are
assigned, as well as the mappings from role to role to privilege which
give that resulting privilege to the accessor.
If you are uncertain how accessor 999 has privilege 333, then simply
run:
select *
from veil2.privilege_assignments
where accessor_id = 999
and privilege_id = 333;';
revoke all on veil2.privilege_assignments from public;
grant select on veil2.privilege_assignments to veil_user;
\echo ......session_context()...
create or replace
function veil2.session_context(
accessor_id in out integer default null,
session_id in out bigint default null,
login_context_type_id in out integer default null,
login_context_id in out integer default null,
session_context_type_id in out integer default null,
session_context_id in out integer default null,
mapping_context_type_id in out integer default null,
mapping_context_id in out integer default null,
parent_session_id in out bigint default null) returns record
as '$libdir/veil2', 'veil2_session_context'
language C volatile;
revoke all on function veil2.session_context(
integer, bigint, integer, integer,
integer, integer, integer, integer, bigint) from public;
comment on function veil2.session_context(
integer, bigint, integer, integer,
integer, integer, integer, integer, bigint) is
'Safe function to return the context of the current session. If no
session exists, returns nulls. We use a function in this context
because we cannot create a view on the veil2_session_context table as it
is a temporary table and does not always exist.';
\echo ......session_privileges()...
create or replace
function veil2.session_privileges(
scope_type_id out integer,
scope_id out integer,
roles out bitmap,
privs out bitmap)
returns setof record
as '$libdir/veil2', 'veil2_session_privileges'
language C stable;
revoke all on function veil2.session_privileges() from public;
grant execute on function veil2.session_privileges() to veil_user;
comment on function veil2.session_privileges() is
'Safe function to return a user-readable version of the privileges for
the current session. If no session exists, returns nulls. We use a
function in this context because we cannot create a view on the
veil2_session_privileges table as it is a temporary table and does not
always exist.';
\echo ......add_session_privileges()...
create or replace
function veil2.add_session_privileges(
scope_type_id integer,
scope_id integer,
roles bitmap,
privs bitmap)
returns void
as '$libdir/veil2', 'veil2_add_session_privileges'
language C volatile;
revoke all on
function veil2.add_session_privileges(
integer, integer, bitmap, bitmap) from public;
comment on function veil2.add_session_privileges(
integer, integer, bitmap, bitmap) is
'Record in-memory copies of session privileges for a given scope for
the session. Note that it is imperative that this is called in
scope_type_id, scope_id order so that the in-memory array can be
bsearched.';
\echo ......update_session_privileges()...
create or replace
function veil2.update_session_privileges(
scope_type_id integer,
scope_id integer,
roles bitmap,
privs bitmap)
returns void
as '$libdir/veil2', 'veil2_update_session_privileges'
language C volatile;
revoke all on
function veil2.update_session_privileges(
integer, integer, bitmap, bitmap) from public;
comment on function veil2.update_session_privileges(
integer, integer, bitmap, bitmap) is
'Update the in-memory roles and privileges bitmap for a given scope.';
\echo ......session_assignment_contexts...
create or replace
view veil2.session_assignment_contexts as
with session_context as
(
select *
from veil2.session_context()
)
select login_context_type_id as context_type_id,
login_context_id as context_id
from session_context sc
union
select session_context_type_id as context_type_id,
session_context_id as context_id
from session_context sc
union
select ass.superior_scope_type_id,
ass.superior_scope_id
from session_context sc
inner join veil2.all_superior_scopes ass
on ( ass.scope_type_id = sc.login_context_type_id
and ass.scope_id = sc.login_context_id)
or ( ass.scope_type_id = sc.session_context_type_id
and ass.scope_id = sc.session_context_id)
union
select ass.scope_type_id,
ass.scope_id
from session_context sc
inner join veil2.all_superior_scopes ass
on ( ass.superior_scope_type_id = sc.login_context_type_id
and ass.superior_scope_id = sc.login_context_id)
or ( ass.superior_scope_type_id = sc.session_context_type_id
and ass.superior_scope_id = sc.session_context_id)
union
select 1, 0
union
select 2, accessor_id
from session_context;
comment on view veil2.session_assignment_contexts is
'Provides the set of security contexts which are valid for role
assignments within the current session. The purpose of this is to
filter out any role assignments which should not apply to the current
session, as those roles could contain privileges which could be
promoted to global_scope.
The situation this prevents is for users that are allowed to login in
different contexts with different roles in those contexts. We do not
want the roles provided in one context to provide privileges that
have not been assigned when we are logged-in in a different context.';
revoke all on veil2.session_assignment_contexts from public;
grant select on veil2.session_assignment_contexts to veil_user;
\echo ......all_accessor_roles(function)...
create or replace
function veil2.all_accessor_roles(
accessor_id in out integer,
session_context_type_id in integer,
session_context_id in integer,
role_id out integer,
context_type_id out integer,
context_id out integer)
returns setof record as
$$
select -- All roles without filtering if our session context is global
-- context.
aar.accessor_id, aar.role_id,
aar.context_type_id, aar.context_id
from veil2.all_accessor_roles_plus aar
where aar.accessor_id = all_accessor_roles.accessor_id
and all_accessor_roles.session_context_type_id = 1
union all
select -- Globally assigned roles, if our session context is non-global
-- context.
aar.accessor_id, aar.role_id,
aar.context_type_id, aar.context_id
from veil2.all_accessor_roles_plus aar
inner join veil2.session_assignment_contexts sac
on -- Matching login context and assignment context
-- Also session_context and assignment context
aar.context_type_id = 1
or ( sac.context_type_id = aar.context_type_id
and sac.context_id = aar.context_id
and aar.context_type_id != 1)
where aar.accessor_id = all_accessor_roles.accessor_id
and all_accessor_roles.session_context_type_id != 1
union all
select accessor_id, 2, 2, accessor_id;
$$
language sql security definer stable;
revoke all on function veil2.all_accessor_roles(
integer, integer, integer)
from public;
comment on function veil2.all_accessor_roles(integer, integer, integer) is
'Return all roles for the given accessor in the given session context.';
\echo ......base_accessor_roleprivs(function)...
create or replace
function veil2.base_accessor_roleprivs(
accessor_id in out integer,
session_context_type_id in integer,
session_context_id in integer,
mapping_context_type_id in out integer,
mapping_context_id in out integer,
assignment_context_type_id out integer,
assignment_context_id out integer,
role_id out integer,
roles out bitmap,
privileges out bitmap)
returns setof record as
$$
select aar.accessor_id, arp.mapping_context_type_id,
arp.mapping_context_id, aar.context_type_id,
aar.context_id, aar.role_id,
coalesce(arp.roles, bitmap(aar.role_id)),
coalesce(arp.privileges, bitmap())
from veil2.all_accessor_roles(
base_accessor_roleprivs.accessor_id,
base_accessor_roleprivs.session_context_type_id,
base_accessor_roleprivs.session_context_id) aar
left outer join veil2.all_role_privileges arp
on arp.role_id = aar.role_id
and ( ( arp.mapping_context_type_id =
base_accessor_roleprivs.mapping_context_type_id
and arp.mapping_context_id =
base_accessor_roleprivs.mapping_context_id)
or ( arp.mapping_context_type_id = 1
and arp.mapping_context_id = 0)
or ( arp.mapping_context_type_id is null
and arp.mapping_context_id is null));
$$
language sql security definer stable;
revoke all on function veil2.base_accessor_roleprivs(
integer, integer, integer, integer, integer)
from public;
comment on function veil2.base_accessor_roleprivs(
integer, integer, integer, integer, integer) is
'Give the set of base (ignoring privilege promotion) roles and
privileges that apply to a given accessor in given mapping and session
contexts.';
\echo ......session_privileges_v...
create or replace
view veil2.session_privileges_v as
with session_context as
(
select *
from veil2.session_context() sc
),
base_accessor_privs as
(
select barp.accessor_id, barp.role_id,
barp.assignment_context_type_id,
barp.assignment_context_id,
barp.mapping_context_type_id, barp.mapping_context_id,
barp.roles, barp.privileges
from session_context sc
-- Making the following an inner join (rather than cross-join)
-- even though the condition is redundant improves performance
-- (Postgres 12.5).
inner join veil2.base_accessor_roleprivs(
sc.accessor_id, sc.session_context_type_id,
sc.session_context_id, sc.mapping_context_type_id,
sc.mapping_context_id) barp
on barp.accessor_id = sc.accessor_id
),
promoted_privs as
(
select bap.accessor_id, bap.role_id,
bap.mapping_context_type_id, bap.mapping_context_id,
pp.scope_type_id, ss.superior_scope_id as scope_id,
bap.privileges * pp.privilege_ids as privileges
from base_accessor_privs bap
inner join veil2.promotable_privileges pp
on not is_empty(bap.privileges * pp.privilege_ids)
and pp.scope_type_id != 1
inner join veil2.all_superior_scopes ss
on ss.scope_type_id = bap.assignment_context_type_id
and ss.scope_id = bap.assignment_context_id
and ss.superior_scope_type_id = pp.scope_type_id
and ss.is_type_promotion
),
global_privs as
(
select bap.accessor_id, bap.role_id,
bap.mapping_context_type_id, bap.mapping_context_id,
pp.scope_type_id, 0 as scope_id,
bap.privileges * pp.privilege_ids as privileges
from base_accessor_privs bap
inner join veil2.promotable_privileges pp
on not is_empty(bap.privileges * pp.privilege_ids)
and pp.scope_type_id = 1
),
all_role_privs as
(
select accessor_id,
mapping_context_type_id, mapping_context_id,
assignment_context_type_id as scope_type_id,
assignment_context_id as scope_id,
roles + role_id as roles, privileges
from base_accessor_privs
union all
select accessor_id,
mapping_context_type_id, mapping_context_id,
scope_type_id, scope_id,
bitmap() as roles, privileges
from promoted_privs
union all
select accessor_id,
mapping_context_type_id, mapping_context_id,
scope_type_id, scope_id,
bitmap() as roles, privileges
from global_privs
),
grouped_role_privs as
(
select accessor_id,
scope_type_id, scope_id,
union_of(roles) as roles, union_of(privileges) as privileges
from all_role_privs
group by accessor_id,
scope_type_id, scope_id
),
have_global_connect as
(
select exists (
select null
from grouped_role_privs
where scope_type_id = 1
and scope_id = 0
and privileges ? 0) as have_global_connect
),
have_session_connect as
(
select exists (
select null
from session_context sc
cross join grouped_role_privs grp
where grp.privileges ? 0
and ( ( grp.scope_type_id = sc.session_context_type_id
and grp.scope_id = sc.session_context_id)
or (grp.scope_type_id, grp.scope_id) in (
select ass.superior_scope_type_id, ass.superior_scope_id
from veil2.all_superior_scopes ass
where ass.scope_type_id = sc.session_context_type_id
and ass.scope_id = sc.session_context_id)))
as have_session_connect
),
have_login_connect as
(
select exists (
select null
from session_context sc
cross join grouped_role_privs grp
where grp.privileges ? 0
and ( ( grp.scope_type_id = sc.login_context_type_id
and grp.scope_id = sc.login_context_id)
or (grp.scope_type_id, grp.scope_id) in (
select ass.superior_scope_type_id, ass.superior_scope_id
from veil2.all_superior_scopes ass
where ass.scope_type_id = sc.login_context_type_id
and ass.scope_id = sc.login_context_id)))
as have_login_connect
),
have_connect as
(
select true as have_connect
from have_global_connect
where have_global_connect
union
select have_login_connect and have_session_connect
from have_global_connect
cross join have_login_connect
cross join have_session_connect
where not have_global_connect
)
select scope_type_id, scope_id,
roles, privileges
from grouped_role_privs
where exists (select null from have_connect where have_connect);
comment on view veil2.session_privileges_v is
'View used to dynamically figure out the roles and privileges in all
contexts for the current session. If the accessor for the session
does not have connect privilege in both the authentication and login
contexts, then no rows are returned.
This view is used as the basis for loading session privileges into the
veil2_session_privileges temporary table and into the
veil2.accessor_privileges_cache table. Note that the cache table is
used in preference to querying from this view if it has records for
the session''s accessor and session context.';
revoke all on veil2.session_privileges_v from public;
\echo ...creating materialized view refresh functions...
\echo ......refresh_all_matviews()...
create or replace
function veil2.refresh_all_matviews()
returns void as
$$
refresh materialized view veil2.all_superior_scopes;
refresh materialized view veil2.all_role_privileges;
truncate table veil2.accessor_privileges_cache;
$$
language sql security definer volatile;
revoke all on function veil2.refresh_all_matviews() from public;
comment on function veil2.refresh_all_matviews() is
'Clear all matviews and caches unconditionally.';
\echo ......refresh_scopes_matviews()...
create or replace
function veil2.refresh_scopes_matviews()
returns trigger
as
$$
begin
perform veil2.refresh_all_matviews();
return new;
end;
$$
language plpgsql security definer;
revoke all on function veil2.refresh_scopes_matviews() from public;
comment on function veil2.refresh_scopes_matviews() is
'Trigger function to refresh all materialized views and caches that
depend on the scopes hierarchy.';
\echo ......refresh_privs_matviews()...
create or replace
function veil2.refresh_privs_matviews()
returns trigger
as
$$
begin
refresh materialized view veil2.all_role_privileges;
truncate table veil2.accessor_privileges_cache;
return new;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.refresh_privs_matviews() from public;
comment on function veil2.refresh_privs_matviews() is
'Trigger function to refresh all materialized views and caches that
depend on privileges.';
\echo ......refresh_roles_matviews()...
create or replace
function veil2.refresh_roles_matviews()
returns trigger
as
$$
begin
refresh materialized view veil2.all_role_privileges;
truncate table veil2.accessor_privileges_cache;
return new;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.refresh_roles_matviews() from public;
comment on function veil2.refresh_roles_matviews() is
'Trigger function to refresh all materialized views and caches that
depend on roles.';
\echo ......clear_accessor_privs_cache()...
create or replace
function veil2.clear_accessor_privs_cache()
returns trigger as
$$
begin
truncate table veil2.accessor_privileges_cache;
return new;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.clear_accessor_privs_cache() from public;
comment on function veil2.clear_accessor_privs_cache() is
'Clear cached role and privileges information for all accessors.';
\echo ......clear_accessor_privs_cache_entry()...
create or replace
function veil2.clear_accessor_privs_cache_entry()
returns trigger as
$$
begin
if tg_op = 'INSERT' or tg_op = 'UPDATE' then
delete
from veil2.accessor_privileges_cache
where accessor_id = new.accessor_id;
if (tg_op = 'UPDATE') and
(old.accessor_id != new.accessor_id) then
delete
from veil2.accessor_privileges_cache
where accessor_id = old.accessor_id;
end if;
return new;
elsif tg_op = 'DELETE' then
delete
from veil2.accessor_privileges_cache
where accessor_id = old.accessor_id;
return old;
end if;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.clear_accessor_privs_cache_entry() from public;
comment on function veil2.clear_accessor_privs_cache_entry() is
'Clear the cached role and privileges information for the affected
accessor.';
\echo ...creating materialized view refresh triggers...
\echo ......on scopes...
create trigger scopes__aiudt
after insert or update or delete or truncate
on veil2.scopes
for each statement
execute procedure veil2.refresh_scopes_matviews();
comment on trigger scopes__aiudt on veil2.scopes is
'Refresh materialized views and clear caches that are constructed from
the scopes table.
VPD Implementation Notes:
Although we expect that scopes will be modified relatively
infrequently, this may not be the case in your application. If the
overhead of this trigger proves to be too significant it should be
dropped, and other mechanisms used to refresh the affected materialized
views. Note that this will mean that the materialized views will not
always be up to date, so this is a trade-off that must be evaluated.';
\echo ......on privileges...
create trigger privileges__aiudt
after insert or update or delete or truncate
on veil2.privileges
for each statement
execute procedure veil2.refresh_privs_matviews();
comment on trigger privileges__aiudt on veil2.privileges is
'Refresh materialized views that are constructed from the
privileges table.';
\echo ......on roles...
create trigger roles__aiudt
after insert or update or delete or truncate
on veil2.roles
for each statement
execute procedure veil2.refresh_roles_matviews();
comment on trigger roles__aiudt on veil2.roles is
'Refresh materialized views that are constructed from the
roles table.';
\echo ......on role_roles...
create trigger role_roles__aiudt
after insert or update or delete or truncate
on veil2.role_roles
for each statement
execute procedure veil2.refresh_roles_matviews();
comment on trigger role_roles__aiudt on veil2.role_roles is
'Refresh materialized views that are constructed from the
role_roles table.';
\echo ......on role_privileges...
create trigger role_privileges__aiudt
after insert or update or delete or truncate
on veil2.role_privileges
for each statement
execute procedure veil2.refresh_roles_matviews();
comment on trigger role_privileges__aiudt on veil2.role_privileges is
'Refresh materialized views that are constructed from the
role_privileges table.';
\echo ......on accessor_roles...
create trigger accessor_roles__at
after truncate
on veil2.accessor_roles
for each statement
execute procedure veil2.clear_accessor_privs_cache_entry();
comment on trigger accessor_roles__at on veil2.accessor_roles is
'Clear all cached accessor role and privilege data.';
create trigger accessor_roles__aiud
after insert or update or delete
on veil2.accessor_roles
for each row
execute procedure veil2.clear_accessor_privs_cache_entry();
comment on trigger accessor_roles__aiud on veil2.accessor_roles is
'Clear cached accessor role and privilege data for a given accessor.';
\echo ...creating veil2 user-provided object handling functions...
\echo ......docpath()...
create or replace
function veil2.docpath() returns text
as '$libdir/veil2', 'veil2_docpath'
language C stable strict;
revoke all on function veil2.docpath() from public;
grant execute on function veil2.docpath() to veil_user;
comment on function veil2.docpath() is
'Return the path to the directory under which Veil2 documents will be
installed.';
\echo ......datapath()...
create or replace
function veil2.datapath() returns text
as '$libdir/veil2', 'veil2_datapath'
language C stable strict;
revoke all on function veil2.datapath() from public;
grant execute on function veil2.datapath() to veil_user;
comment on function veil2.datapath() is
'Return the path to the directory under which Veil2 scripts will be
installed.';
\echo ......version()...
create or replace
function veil2.version() returns text
as '$libdir/veil2', 'veil2_version'
language C stable strict;
grant execute on function veil2.version() to veil_user;
comment on function veil2.version() is
'Return the version of the currently installed veil.';
\echo ......function_definition()...
create or replace
function veil2.function_definition(fn_name text, fn_oid oid)
returns text as
$$
declare
rec record;
_result text;
begin
-- Query (modified) from \df+ in psql
select
pg_catalog.pg_get_function_arguments(p.oid) as arg_types,
pg_catalog.pg_get_function_result(p.oid) as result_type,
p.prosrc as source,
l.lanname as language,
case when prosecdef then 'definer' else 'invoker' end as security,
case
when p.provolatile = 'i' then 'immutable'
when p.provolatile = 's' then 'stable'
when p.provolatile = 'v' then 'volatile'
end as volatility,
case
when p.proparallel = 'r' then 'restricted'
when p.proparallel = 's' then 'safe'
when p.proparallel = 'u' then 'unsafe'
end as parallel
into rec
from pg_catalog.pg_proc p
left join pg_catalog.pg_language l on l.oid = p.prolang
where p.oid = fn_oid;
if found then
_result := 'create or replace function veil2.' || fn_name ||
'(' || rec.arg_types || ') returns ' ||
rec.result_type || ' as $xyzzy$' ||
rec.source || '$xyzzy$ language ''' ||
rec.language || ''' security ' ||
rec.security || ' ' || rec.volatility ||
' parallel ' || rec.parallel;
end if;
return _result;
end;
$$
language plpgsql security definer stable;
revoke execute on function veil2.function_definition(text, oid) from public;
comment on function veil2.function_definition(text, oid) is
'Returns the text to create a function named
fn_name, based on the function definition provided
by fn_oid. This is used by
veil2.install_user_functions() and veil2.restore_system_functions()';
\echo ......replace_function()...
create or replace
function veil2.replace_function(fn_name text, from_fn oid)
returns void as
$$
declare
fn_defn text;
begin
fn_defn := veil2.function_definition(fn_name, from_fn);
execute fn_defn;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.replace_function(text, oid) from public;
comment on function veil2.replace_function(text, oid) is
'Create or replace the function named fn_name based
on the definition given in fn_oid. Used for
installing user-provided functions in place of the veil2
system-provided ones.';
\echo ......restore_system_functions()...
create or replace
function veil2.restore_system_functions()
returns void as
$$
declare
rec record;
begin
for rec in
select po.proname, pb.oid backup_oid, po.oid as old_oid
from pg_catalog.pg_namespace n
inner join pg_catalog.pg_proc pb -- backup proc
on pb.pronamespace = n.oid
and pb.proname like 'backup_%'
inner join pg_catalog.pg_proc po -- original proc
on po.pronamespace = n.oid
and pb.proname = 'backup_' || po.proname
where n.nspname = 'veil2'
loop
perform veil2.replace_function(rec.proname, rec.backup_oid);
end loop;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.restore_system_functions() from public;
comment on function veil2.restore_system_functions() is
'Restore system-provided functions that have been replaced by
user-provided ones. The originals for the system-provided functions
will have been saved as backups by veil2.install_user_functions()';
\echo ......install_user_functions()...
create or replace
function veil2.install_user_functions()
returns void as
$$
declare
rec record;
begin
for rec in
select po.proname, p.oid new_oid, po.oid as old_oid,
case when pb.proname is null then false
else true end as have_backup
from pg_catalog.pg_namespace n
inner join pg_catalog.pg_proc p -- replacement proc
on p.pronamespace = n.oid
and p.proname like 'my%'
inner join pg_catalog.pg_proc po -- original proc
on po.pronamespace = n.oid
and p.proname = 'my_' || po.proname
left outer join pg_catalog.pg_proc pb -- backup of original proc
on pb.pronamespace = n.oid
and pb.proname = 'backup_' || po.proname
where n.nspname = 'veil2'
loop
if not rec.have_backup then
perform veil2.replace_function('backup_' || rec.proname,
rec.old_oid);
end if;
perform veil2.replace_function(rec.proname, rec.new_oid);
end loop;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.install_user_functions() from public;
comment on function veil2.install_user_functions() is
'Install any user-provided functions that are to replace
system-provided ones. The original versions of the system-provided
functions will be saved as backups.';
\echo ......function_exists()...
create or replace
function veil2.function_exists(fn_name text)
returns boolean as
$$
declare
_result boolean;
begin
select true
into _result
from pg_catalog.pg_namespace n
inner join pg_catalog.pg_proc pn -- new proc
on pn.pronamespace = n.oid
and pn.proname = fn_name
where n.nspname = 'veil2';
return found;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.function_exists(text) from public;
comment on function veil2.function_exists(text) is
'Predicate returning true if a function named
fn_name exists in the veil2 schema.';
\echo ......view_exists()...
create or replace
function veil2.view_exists(vw_name text)
returns boolean as
$$
declare
_result boolean;
begin
select true
into _result
from pg_catalog.pg_namespace n
inner join pg_catalog.pg_class rn -- new reln
on rn.relnamespace = n.oid
and rn.relname = vw_name
and rn.relkind = 'v'
where n.nspname = 'veil2';
return found;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.view_exists(text) from public;
comment on function veil2.view_exists(text) is
'Predicate returning true if a view named
vw_name exists in the veil2 schema.';
\echo ......replace_view()...
create or replace
function veil2.replace_view(view_name text, from_view oid)
returns void as
$$
declare
view_defn text;
begin
view_defn := 'create or replace view veil2.' ||
view_name || ' as ' ||
pg_catalog.pg_get_viewdef(from_view, true);
--raise warning '%', view_defn;
execute view_defn;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.replace_view(text, oid) from public;
comment on function veil2.replace_view(text, oid) is
'Create or replace the view named view_name based
on the definition given in view_oid. Used for
installing user-provided views in place of the veil2
system-provided ones.';
\echo ......restore_system_views()...
create or replace
function veil2.restore_system_views()
returns void as
$$
declare
rec record;
begin
for rec in
select vo.relname, vb.oid backup_oid, vo.oid as old_oid
from pg_catalog.pg_namespace n
inner join pg_catalog.pg_class vb -- backup view
on vb.relnamespace = n.oid
and vb.relkind = 'v'
and vb.relname like 'backup_%'
inner join pg_catalog.pg_class vo -- original view
on vo.relnamespace = n.oid
and vo.relkind = 'v'
and vb.relname = 'backup_' || vo.relname
where n.nspname = 'veil2'
loop
perform veil2.replace_view(rec.relname, rec.backup_oid);
end loop;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.restore_system_views() from public;
comment on function veil2.restore_system_views() is
'Restore system-provided views that have been replaced by
user-provided ones. The originals for the system-provided view
will have been saved as backups by veil2.install_user_views()';
\echo ......install_user_views()...
create or replace
function veil2.install_user_views()
returns void as
$$
declare
rec record;
begin
for rec in
select vo.relname, v.oid new_oid, vo.oid as old_oid,
case when vb.relname is null then false
else true end as have_backup
from pg_catalog.pg_namespace n
inner join pg_catalog.pg_class v -- Replacement view
on v.relnamespace = n.oid
and v.relkind = 'v'
inner join pg_catalog.pg_class vo -- original view
on vo.relnamespace = n.oid
and vo.relkind = 'v'
and v.relname = 'my_' || vo.relname
left outer join pg_catalog.pg_class vb -- backup of original view
on vb.relnamespace = n.oid
and vb.relkind = 'v'
and vb.relname = 'backup_' || vo.relname
where n.nspname = 'veil2'
loop
if not rec.have_backup then
perform veil2.replace_view('backup_' || rec.relname,
rec.old_oid);
end if;
perform veil2.replace_view(rec.relname, rec.new_oid);
end loop;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.install_user_views() from public;
comment on function veil2.install_user_views() is
'Install any user-provided views that are to replace
system-provided ones. The original versions of the system-provided
views will be saved as backups.';
\echo ......init()...
create or replace
function veil2.init() returns void as
$$
begin
perform veil2.install_user_functions();
perform veil2.install_user_views();
refresh materialized view veil2.all_superior_scopes;
refresh materialized view veil2.all_role_privileges;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.init() from public;
comment on function veil2.init() is
'Perform some basic setup and reset tasks. This creates
user-modifiable views that are not already defined and refreshes all
materialized views. You should call it any time you have unexpected
results. If it fixes your problem then you have a problem with the
automatic refresh of one of the materialized views. If not, no harm
will have been done.';
\echo ......deferred_install()...
create or replace
function veil2.deferred_install_fn() returns trigger as
$$
begin
perform veil2.init();
return new;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.deferred_install_fn() from public;
comment on function veil2.deferred_install_fn() is
'Install user-provided functions and views. This is called from an
after statement trigger so that we do not install the new versions of
functions until the current versions, which we may be replacing, have
completed. This may be overly prudent but it does no harm.';
create trigger deferred_install_trg
after insert
on veil2.deferred_install
for each statement
execute function veil2.deferred_install_fn();
comment on trigger deferred_install_trg on veil2.deferred_install is
'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.';
\echo ...creating veil2 authentication functions...
\echo ......authenticate_false()...
create or replace
function veil2.authenticate_false(
accessor_id integer,
token text)
returns boolean as
$$
select false;
$$
language sql security definer stable;
revoke all on function veil2.authenticate_false(integer, text)
from public;
comment on function veil2.authenticate_false(integer, text) is
'Authentication predicate for unimplemented or disabled authentication
types. This function always returns false, causing authentication to
fail.';
\echo ......authenticate_plaintext()...
create or replace
function veil2.authenticate_plaintext(
accessor_id integer,
token text)
returns boolean as
$$
select coalesce(
(select authent_token = authenticate_plaintext.token
from veil2.authentication_details
where accessor_id = authenticate_plaintext.accessor_id
and authentication_type = 'plaintext'),
false);
$$
language sql security definer stable;
revoke all on function veil2.authenticate_plaintext(integer, text)
from public;
comment on function veil2.authenticate_plaintext(integer, text) is
'Authentication predicate for plaintext authentication. Return true if
the supplied token matches the stored authentication token for the
accessor. This authentication mechanism exists primarily for demo
purposes. DO NOT USE IT IN A REAL APPLICATION!';
\echo ......authenticate_bcrypt()...
create or replace
function veil2.authenticate_bcrypt(
accessor_id integer,
token text)
returns boolean as
$$
select coalesce(
(select authent_token = crypt(token, authent_token)
from veil2.authentication_details
where accessor_id = authenticate_bcrypt.accessor_id
and authentication_type = 'bcrypt'),
false);
$$
language sql security definer stable;
revoke all on function veil2.authenticate_bcrypt(integer, text)
from public;
comment on function veil2.authenticate_bcrypt(integer, text) is
'Authentication predicate for bcrypt authentication. Return true if
running bcrypt on the supplied token, using the salt from the
stored authentication token for the accessor, matches that stored
authentication token.
Bcrypt is generally considered a step up from traditional hash-based
password authentication, though it is essentially the same thing. In
a hash-based authentication system, a user''s password is stored as a,
possibly salted, hash on the plaintext. Since hashes are one-way
algorithms it is impossible to retrieve the original password from the
hash. However, as computers have become more powerful, brute-force
approaches have become more feasible. With a simple hash, it is is
now possible to try every possible password until one matches the
hash, whether salted or not in a matter of hours. Bcrypt makes
brute-forcing difficult by using a compuatationally inefficient hash
algorithm, which makes brute force attacks a very expensive
proposition. Note that in attacks on hash-based passwords it is
assumed that the hashed password has been compromised. Whether this
is likely in database protected by Veil2 is moot, however there may be
more likely avenues for attack as the hashed passwords can be pretty
well locked down.
The current bcrypt implementation''s biggest down-side, in common with
traditional hash-based approaches, is that the user''s password is
sent to the server in plaintext before it is tested by bcrypting it.
A better authentication method would avoid this.';
\echo ......authenticate()...
create or replace
function veil2.authenticate(
accessor_id integer,
authent_type text,
token text)
returns boolean as
$$
declare
success boolean;
authent_fn text;
enabled boolean;
begin
select t.enabled, t.authent_fn
into enabled, authent_fn
from veil2.authentication_types t
where t.shortname = authent_type;
if found and enabled then
execute format('select * from %s(%s, %L)',
authent_fn, accessor_id, token)
into success;
return success;
end if;
return false;
end;
$$
language plpgsql security definer stable;
revoke all on function veil2.authenticate(integer, text, text)
from public;
comment on function veil2.authenticate(integer, text, text) is
'For the given accessor_id and authentication_type check whether token
is an appropriate authentication.';
\echo ...creating veil2 session functions...
\echo ...session_ready()...
create or replace
function veil2.session_ready() returns boolean
as '$libdir/veil2', 'veil2_session_ready'
language C stable strict;
revoke all on function veil2.session_ready() from public;
comment on function veil2.session_ready() is
'Predicate to indicate whether veil2.reset_session() has been
successfully called for this session. If not, none of the
C language i_have_privilege functions will return true.
This exists as a stand-alone function so that it may be used by
user-defined functions.';
\echo ......reset_session()...
create or replace
function veil2.reset_session() returns void
as '$libdir/veil2', 'veil2_reset_session'
language C volatile strict security definer;
revoke all on function veil2.reset_session() from public;
comment on function veil2.reset_session() is
'Ensure our temp tables exist, are of the expected type (temporary
tables); that the session user has no unexpected access rights on
them; and clear them.';
\echo ......close_connection()...
create or replace
function veil2.close_connection() returns void
as '$libdir/veil2', 'veil2_reset_session'
language C volatile strict security definer;
revoke all on function veil2.close_connection() from public;
grant execute on function veil2.close_connection() to veil_user;
comment on function veil2.close_connection() is
'Close the current session. We use this to ensure that a shared
database connection cannot be used with our privileges once we have
finished with it. There is no authentication or verification done to
ensure that the session owner is the one doing this, because there is
no perceived need. If this is a problem then, given that you can
achieve the same thing by deliberately failing a veil2.open() call,
there are other, more complex areas of the session management protocol
that will need to be reconsidered.';
\echo ......reset_session_privs()...
create or replace
function veil2.reset_session_privs() returns void
as '$libdir/veil2', 'veil2_reset_session_privs'
language C volatile strict security definer;
revoke all on function veil2.reset_session_privs() from public;
comment on function veil2.reset_session_privs() is
'Safely clear the contents of the veil2_session_privileges temporary
table.';
\echo ......get_accessor()...
create or replace
function veil2.get_accessor(
username in text,
context_type_id in integer,
context_id in integer)
returns integer as
$$
begin
-- Cause any user-provided versions of veil2 functions to be
-- installed before the current statement completes.
insert into veil2.deferred_install values (now());
if veil2.function_exists('my_get_accessor') then
-- If we have a user-provided version of this function, we call it
-- now. The next time we are called, this, the system-provided
-- version of the function, will have been replaced by the
-- user-provided copy. This version can be restored by calling
-- veil2.restore_base_system().
return veil2.my_get_accessor(username, context_type_id, context_id);
end if;
return 0;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.get_accessor(text, integer, integer)
from public;
comment on function veil2.get_accessor(text, integer, integer) is
'Retrieve accessor_id based on username and context. A user-provided
version of this, named my_get_accessor() should be created
specifically for your application. It will be automatically installed
when it is first needed. If you modify your version, you can update
the system version by calling veil2.init().';
\echo ......new_session_context()...
create or replace
function veil2.new_session_context(
accessor_id in integer,
login_context_type_id in integer,
login_context_id in integer,
session_context_type_id in integer,
session_context_id in integer,
parent_session_id in bigint default null,
session_id out bigint,
mapping_context_type_id out integer,
mapping_context_id out integer)
returns record as
$$
select currval('veil2.session_id_seq'), sc.mapping_context_type_id,
sc.mapping_context_id
from veil2.system_parameters sp
left outer join veil2.all_superior_scopes asp
on asp.scope_type_id = new_session_context.session_context_type_id
and asp.scope_id = new_session_context.session_context_id
and asp.superior_scope_type_id = sp.parameter_value::integer
and asp.is_type_promotion
cross join lateral veil2.session_context(
accessor_id, nextval('veil2.session_id_seq'),
new_session_context.login_context_type_id,
new_session_context.login_context_id,
new_session_context.session_context_type_id,
new_session_context.session_context_id,
case when sp.parameter_value = '1' then 1
else coalesce(asp.superior_scope_type_id,
new_session_context.session_context_type_id) end,
case when sp.parameter_value = '1' then 0
else coalesce(asp.superior_scope_id,
new_session_context.session_context_id) end,
parent_session_id) sc
where sp.parameter_name = 'mapping context target scope type';
$$
language sql security definer volatile;
revoke all on function veil2.new_session_context(
integer, integer, integer, integer, integer, bigint)
from public;
comment on function veil2.new_session_context(
integer, integer, integer, integer, integer, bigint) is
'Create an in-memory session_context record for the given parameters,
returning session_id and mapping context.';
\echo ......have_accessor_context()...
create or replace
function veil2.have_accessor_context(
_accessor_id integer,
_context_type_id integer,
_context_id integer)
returns boolean as
$$
declare
result boolean;
begin
-- Whether the combination of accessor_id and context is valid.
-- Generate session_supplemental if authentication method supports it.
select exists (
into result
select null
from veil2.accessor_contexts ac
where ac.accessor_id = _accessor_id
and ac.context_type_id = _context_type_id
and ac.context_id = _context_id);
return result;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.have_accessor_context(
integer, integer, integer) from public;
comment on function veil2.have_accessor_context(
integer, integer, integer) is
'Predicate to determine whether an accessor has the right to use the
given session context.';
\echo ......create_accessor_session()...
create or replace
function veil2.create_accessor_session(
accessor_id in integer,
authent_type in text,
login_context_type_id in integer,
login_context_id in integer,
session_context_type_id in integer,
session_context_id in integer,
session_id out bigint,
session_token out text,
session_supplemental out text)
returns record as
$$
declare
_mapping_context_type_id integer;
_mapping_context_id integer;
supplemental_fn text;
begin
execute veil2.reset_session();
-- Regardless of validity of accessor_id, we create a
-- veil2_session_context record. This is to prevent fishing for
-- valid accessor_ids.
select *
into session_id, _mapping_context_type_id,
_mapping_context_id
from veil2.new_session_context(
accessor_id,
login_context_type_id, login_context_id,
session_context_type_id, session_context_id) x;
-- Figure out the session tokens. This must succeed regardless of
-- the validity of our parameters.
select t.supplemental_fn
into supplemental_fn
from veil2.authentication_types t
where shortname = authent_type;
if supplemental_fn is not null then
execute format('select * from %s(%s, %L)',
supplemental_fn, _accessor_id, session_token)
into session_token, session_supplemental;
else
session_token := encode(digest(random()::text || now()::text, 'sha256'),
'base64');
end if;
if veil2.have_accessor_context(accessor_id, login_context_type_id,
login_context_id)
then
insert
into veil2.sessions
(accessor_id, session_id,
login_context_type_id, login_context_id,
session_context_type_id, session_context_id,
mapping_context_type_id, mapping_context_id,
authent_type, has_authenticated,
session_supplemental, expires,
token)
select create_accessor_session.accessor_id,
create_accessor_session.session_id,
create_accessor_session.login_context_type_id,
create_accessor_session.login_context_id,
create_accessor_session.session_context_type_id,
create_accessor_session.session_context_id,
_mapping_context_type_id, _mapping_context_id,
authent_type, false,
session_supplemental, now() + sp.parameter_value::interval,
session_token
from veil2.system_parameters sp
where sp.parameter_name = 'shared session timeout';
end if;
end;
$$
language plpgsql security definer volatile
set client_min_messages = 'error';
revoke all on function veil2.create_accessor_session(
integer, text, integer, integer, integer, integer) from public;
comment on function veil2.create_accessor_session(
integer, text, integer, integer, integer, integer) is
'Create a new session based on an accessor_id rather than username.
This is an internal function to veil2. It does the hard work for
create_session().';
\echo ......create_session()...
create or replace
function veil2.create_session(
username in text,
authent_type in text,
context_type_id in integer default 1,
context_id in integer default 0,
session_context_type_id in integer default null,
session_context_id in integer default null,
session_id out bigint,
session_token out text,
session_supplemental out text)
returns record as
$$
declare
_accessor_id integer;
begin
-- Generate session_id and session_token and establish whether
-- username was valid.
_accessor_id := veil2.get_accessor(username, context_type_id, context_id);
select cas.session_id, cas.session_token,
cas.session_supplemental
into create_session.session_id, create_session.session_token,
create_session.session_supplemental
from veil2.create_accessor_session(
_accessor_id, authent_type,
context_type_id, context_id,
coalesce(session_context_type_id, context_type_id),
coalesce(session_context_id, context_id)) cas;
end;
$$
language plpgsql security definer volatile
set client_min_messages = 'error';
revoke all on function veil2.create_session(
text, text, integer, integer, integer, integer)
from public;
grant execute on function veil2.create_session(
text, text, integer, integer, integer, integer)
to veil_user;
comment on function veil2.create_session(
text, text, integer, integer, integer, integer) is
'Get session credentials for a new session.
Returns session_id, authent_token and session_supplemental.
session_id is used to uniquely identify this user''s session. It will
be needed for subsequent open_connection() calls.
session_token is randomly generated. Depending on the authentication
method chosen, the client may need to use this when generating their
authentication token for the subsequent open_connection() call.
session_supplemental is an authentication method specific set of
data. Depending upon the authentication method, the client may need
to use this in generating subsequent authentication tokens,
If username is not valid the function will appear to work but
subsequent attempts to open the session will fail and no privileges
will be loaded. This makes it harder to fish for valid usernames.
The authent_type parameter identifies what type of authentication will
be used, and therefore determines the authentication protocol. All
authentication types will make use of a session_id and session_token,
some may also require additional fields. These will be provided in
session_supplemental. For example, if we were to define a
Diffie-Hellman key exchange protocol, the session_supplemental field
would provide modulus, base and public transport values.';
\echo ......check_nonce()...
create or replace
function veil2.check_nonce(
nonce integer,
nonces bitmap)
returns boolean as
$$
select case
when nonces is null then true
when (nonces ? nonce) then false
when nonce < bitmin(nonces) then false
when nonce > (bitmax(nonces) + 64) then false
else true end;
$$
language sql security definer stable;
revoke all on function veil2.check_nonce(integer, bitmap) from public;
comment on function veil2.check_nonce(integer, bitmap) is
'Check that nonce has not already been used and is within the range of
acceptable values, returning true if all is well.';
\echo ......update_nonces()...
create or replace
function veil2.update_nonces(
nonce integer,
nonces bitmap)
returns bitmap as
$$
declare
reslt bitmap;
i integer;
target_bitmin integer;
begin
reslt := coalesce(nonces, bitmap()) + nonce;
if (bitmax(reslt) - bitmin(reslt)) > 192 then
-- If there are 3 64-bit groups in the bitmap, let's lose the
-- lowest one. We keep 2 groups active, allowing for some slop in
-- the arrival of consecutive integers without allowing the bitmaps
-- to become unreasonably large. I don't see any attack vector
-- here as it should be impossible to get past the checks in
-- check_nonce() by attempting to re-use (in a replay attack) a
-- nonce from a group that we have dropped.
target_bitmin = (bitmin(reslt) + 64) & ~63;
reslt := bitmap_setmin(reslt, target_bitmin);
end if;
return reslt;
end;
$$
language plpgsql security definer stable;
revoke all on function veil2.update_nonces(integer, bitmap) from public;
comment on function veil2.update_nonces(integer, bitmap) is
'Add nonce to the list of used nonces, slimming the bitmap down when it
gets too large.';
\echo ......filter_privs()...
create or replace
function veil2.filter_privs()
returns void as
$$
declare
_count integer;
begin
-- We are going to update veil2_session_privileges to remove any
-- roles and privileges that do not exist in
-- veil2_ancestor_privileges. This is part of the become user
-- process, to ensure that become user cannot lead to privilege
-- escalation.
with updatable_privs as
(
select sp.scope_type_id, sp.scope_id
from veil2.session_privileges() sp
where scope_type_id != 2
),
superior_scopes as
(
-- For each scope, other than personal, in
-- veil2_session_privileges, identify all scopes that could give
-- us equivalent privileges.
select up.scope_type_id, up.scope_id,
asp.scope_type_id as test_scope_type_id,
asp.scope_id as test_scope_id
from updatable_privs up
inner join veil2.all_superior_scopes asp
on asp.scope_type_id = up.scope_type_id
and asp.scope_id = up.scope_id
union all
select scope_type_id, scope_id, scope_type_id, scope_id
from updatable_privs
union all
select scope_type_id, scope_id, 1, 0 -- global scope is superior
from updatable_privs
),
effective_ancestor_privs as
(
-- The set of privileges our ancestor effectively has at each
-- scope level. These privileges may be from global or other
-- superior scopes.
select ss.scope_type_id, ss.scope_id,
union_of(coalesce(vap.roles, bitmap())) as roles,
union_of(coalesce(vap.privs, bitmap())) as privs
from superior_scopes ss
left outer join veil2_ancestor_privileges vap
on vap.scope_type_id = ss.test_scope_type_id
and vap.scope_id = ss.test_scope_id
group by ss.scope_type_id, ss.scope_id
),
final_privs as
(
-- What the become-user session's roles and privs should be
-- after filtering any that the ancestor session did not
-- effectively have.
select sp.scope_type_id, sp.scope_id,
sp.roles * eap.roles as roles,
sp.privs * eap.privs as privs
from veil2.session_privileges() sp
inner join effective_ancestor_privs eap
on eap.scope_type_id = sp.scope_type_id
and eap.scope_id = sp.scope_id
),
updates as
(
select veil2.update_session_privileges(scope_type_id, scope_id,
roles, privs), 1
from final_privs
)
select count(*)::integer
into _count
from updates;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.filter_privs() from public;
comment on function veil2.filter_privs() is
'Remove any privileges from veil2_session_privileges that would not be
provided by veil2_ancestor_privileges. This is part of the become user
functionality. We perform this filtering in order to ensure that a
user cannot increase their privileges using become user.';
\echo ......load_ancestor_privs(parent_session_id)...
create or replace
function veil2.load_ancestor_privs(parent_session_id bigint)
returns void as
$$
begin
delete
from veil2_ancestor_privileges;
with recursive ancestors as
(
select *
from veil2.sessions
where session_id = load_ancestor_privs.parent_session_id
union all
select s.*
from ancestors a
inner join veil2.sessions s
on s.session_id = a.parent_session_id
),
base_accessor_privs as
(
select barp.accessor_id, barp.role_id,
barp.assignment_context_type_id,
barp.assignment_context_id,
barp.mapping_context_type_id, barp.mapping_context_id,
barp.roles, barp.privileges
from ancestors a
-- Making the following an inner join (rather than cross-join)
-- even though the condition is redundant improves performance
-- (Postgres 12.5).
inner join veil2.base_accessor_roleprivs(
a.accessor_id, a.session_context_type_id,
a.session_context_id, a.mapping_context_type_id,
a.mapping_context_id) barp
on barp.accessor_id = a.accessor_id
where assignment_context_type_id != 2 -- ignore personal context
),
promoted_privs as
(
select bap.accessor_id, bap.role_id,
bap.mapping_context_type_id, bap.mapping_context_id,
pp.scope_type_id, ss.superior_scope_id as scope_id,
bap.privileges * pp.privilege_ids as privileges
from base_accessor_privs bap
inner join veil2.promotable_privileges pp
on not is_empty(bap.privileges * pp.privilege_ids)
and pp.scope_type_id != 1
inner join veil2.all_superior_scopes ss
on ss.scope_type_id = bap.assignment_context_type_id
and ss.scope_id = bap.assignment_context_id
and ss.superior_scope_type_id = pp.scope_type_id
and ss.is_type_promotion
),
global_privs as
(
select bap.accessor_id, bap.role_id,
bap.mapping_context_type_id, bap.mapping_context_id,
pp.scope_type_id, 0 as scope_id,
bap.privileges * pp.privilege_ids as privileges
from base_accessor_privs bap
inner join veil2.promotable_privileges pp
on not is_empty(bap.privileges * pp.privilege_ids)
and pp.scope_type_id = 1
),
all_role_privs as
(
select accessor_id,
mapping_context_type_id, mapping_context_id,
assignment_context_type_id as scope_type_id,
assignment_context_id as scope_id,
roles + role_id as roles, privileges
from base_accessor_privs
union all
select accessor_id,
mapping_context_type_id, mapping_context_id,
scope_type_id, scope_id,
bitmap() as roles, privileges
from promoted_privs
union all
select accessor_id,
mapping_context_type_id, mapping_context_id,
scope_type_id, scope_id,
bitmap() as roles, privileges
from global_privs
),
grouped_role_privs as
(
select scope_type_id, scope_id,
union_of(roles) as roles, union_of(privileges) as privs
from all_role_privs
group by scope_type_id, scope_id
)
insert
into veil2_ancestor_privileges
(scope_type_id, scope_id, roles, privs)
select scope_type_id, scope_id, roles, privs
from grouped_role_privs;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.load_ancestor_privs(bigint) from public;
comment on function veil2.load_ancestor_privs(bigint) is
'Load the privileges for all ancestor sessions into
veil2_ancestor_privileges. We will use this to ensure that a
become_user() session does not gain privileges that the parent session
did not have (ie we aim to stop it from being used as a mechanism for
privilege escalation).';
\echo ......filter_session_privs(parent_session_id)...
create or replace
function veil2.filter_session_privs(parent_session_id bigint)
returns void as
$$
begin
perform veil2.load_ancestor_privs(parent_session_id);
perform veil2.filter_privs();
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.filter_session_privs(bigint) from public;
comment on function veil2.filter_session_privs(bigint) is
'Remove from veil2_session_privileges any roles and privileges not
held by the ancestor session(s).';
\echo ......session_privileges_info (view)...
create or replace
view veil2.session_privileges_info as
select *
from veil2.session_privileges();
comment on view veil2.session_privileges_info is
'Provides a user-readable view of the veil2.session_privileges
temporary table.';
grant select on veil2.session_privileges_info to veil_user;
\echo ......load_and_cache_session_privs()...
create or replace
function veil2.load_and_cache_session_privs()
returns boolean as
$$
begin
insert
into veil2.accessor_privileges_cache
(accessor_id, login_context_type_id,
login_context_id, session_context_type_id,
session_context_id, mapping_context_type_id,
mapping_context_id, scope_type_id,
scope_id, roles,
privs)
select sc.accessor_id, sc.login_context_type_id,
sc.login_context_id, sc.session_context_type_id,
sc.session_context_id, sc.mapping_context_type_id,
sc.mapping_context_id, p.scope_type_id,
p.scope_id, p.roles,
p.privileges
from veil2.session_context() sc
cross join (
select *
from veil2.session_privileges_v
order by scope_type_id, scope_id) p
cross join lateral (
select veil2.add_session_privileges(p.scope_type_id, p.scope_id,
p.roles, p.privileges)) asp;
return found;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.load_and_cache_session_privs() from public;
comment on function veil2.load_and_cache_session_privs() is
'Load the in-memory copy of session privileges from
veil2.session_privileges_v and also cache them in
veil2.accessor_privileges_cache.';
\echo ......check_continuation()...
create or replace
function veil2.check_continuation(
nonce integer,
session_token text,
authent_token text)
returns boolean as
$$
select encode(digest(session_token || to_hex(nonce), 'sha1'),
'base64') = authent_token;
$$
language sql security definer stable;
revoke all on function veil2.check_continuation(integer, text, text)
from public;
comment on function veil2.check_continuation(integer, text, text) is
'Checks whether the combination of nonce, session_token and
authent_token is valid. This is used to continue sessions that have
already been authenticated. It ensures that new tokens are used on
each call, and that the caller has access to the session_token
returned from the original (subsequently authenticated) create
session() call.';
\echo ......load_cached_privs()...
create or replace
function veil2.load_cached_privs()
returns boolean as
$$
declare
_count integer;
begin
with privs as
(
select apc.scope_type_id, apc.scope_id,
apc.roles, apc.privs
from veil2.session_context() sc
inner join veil2.accessor_privileges_cache apc
on apc.accessor_id = sc.accessor_id
and apc.login_context_type_id = sc.login_context_type_id
and apc.login_context_id = sc.login_context_id
and apc.session_context_type_id = sc.session_context_type_id
and apc.session_context_id = sc.session_context_id
and apc.mapping_context_type_id = sc.mapping_context_type_id
and apc.mapping_context_id = sc.mapping_context_id
),
ins as
(
select veil2.add_session_privileges(scope_type_id, scope_id,
roles, privs)
from privs
)
select count(*)::integer
into _count
from ins;
return _count > 0;
end;
$$
language 'plpgsql' security definer volatile;
revoke all on function veil2.load_cached_privs() from public;
comment on function veil2.load_cached_privs() is
'Reload cached session privileges for the session''s accessor into our
current session.';
\echo ......update_session()...
create or replace
function veil2.update_session(
_session_id bigint,
_nonces bitmap,
_authenticated boolean)
returns void as
$$
with recursive sessions as
(
select parent_session_id
from upd_cur_session
union all
select s2.parent_session_id
from sessions s1
inner join veil2.sessions s2
on s1.parent_session_id is not null
and s2.session_id = s1.parent_session_id
and s2.parent_session_id is not null
),
timeout as
(
select parameter_value::interval as increment
from veil2.system_parameters
where parameter_name = 'shared session timeout'
),
upd_cur_session as
(
update veil2.sessions s
set expires =
case when _authenticated
then now() + t.increment
else s.expires
end,
nonces = coalesce(_nonces, nonces),
has_authenticated = has_authenticated or _authenticated
from timeout t
where s.session_id = _session_id
returning parent_session_id
)
update veil2.sessions s
set expires =
case when _authenticated
then now() + t.increment
else s.expires
end
from timeout t
where s.session_id in (select parent_session_id from sessions);
$$
language sql security definer volatile;
revoke all on function veil2.update_session(
bigint, bitmap, boolean) from public;
comment on function veil2.update_session(bigint, bitmap, boolean) is
'Update the veil2.sessions records associated with the current session
to show a new expiry date, record new nonces, and ongoing session
validity. This updates the identified sessions record with nonces,
validity and expiry, and also updates any ancestor sessions with
expiry. Although this might be more clearly expressed in plpgsql,
being able to do it in pure sql is cool and has the potential to be
faster as it requires only a single statement.';
\echo ......load_connection_privs()...
create or replace
function veil2.load_connection_privs(
parent_session_id bigint)
returns boolean as
$$
begin
if not veil2.load_cached_privs() then
if not veil2.load_and_cache_session_privs() then
return false;
end if;
end if;
if parent_session_id is not null then
perform veil2.filter_session_privs(parent_session_id);
end if;
return true;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.load_connection_privs(bigint) from public;
comment on function veil2.load_connection_privs(bigint) is
'Load veil_session_privileges temp table for a session, filtering the
privileges if neccesary for a become_user() session.';
\echo ......reload_session_context()...
create or replace
function veil2.reload_session_context(_session_id bigint)
returns bigint as
$$
select s.parent_session_id
from veil2.sessions s
cross join lateral veil2.session_context(
s.accessor_id, s.session_id,
s.login_context_type_id, s.login_context_id,
s.session_context_type_id, s.session_context_id,
s.mapping_context_type_id, s.mapping_context_id,
s.parent_session_id)
where s.session_id = _session_id;
$$
language sql security definer volatile;
revoke all on function veil2.reload_session_context(bigint) from public;
comment on function veil2.reload_session_context(bigint) is
'(Re)load veil2_session_context for a given session_id.';
\echo ......reload_connection_privs()...
create or replace
function veil2.reload_connection_privs()
returns boolean as
$$
declare
_parent_session_id bigint;
begin
perform veil2.reset_session_privs();
select parent_session_id
into _parent_session_id
from veil2.session_context();
return veil2.load_connection_privs(_parent_session_id);
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.reload_connection_privs() from public;
grant execute on function veil2.reload_connection_privs() to veil_user;
comment on function veil2.reload_connection_privs() is
'Reload a session''s privileges. This would be done in the event that
cached privileges need to be reloaded.';
\echo ......open_connection()...
create or replace
function veil2.open_connection(
session_id in bigint,
nonce in integer,
authent_token in text,
success out boolean,
errmsg out text)
returns record as
$$
declare
_accessor_id integer;
_nonces bitmap;
nonce_ok boolean;
_has_authenticated boolean;
_session_token text;
_context_type_id integer;
authent_type text;
expired boolean;
parent_session_id integer;
begin
perform veil2.reset_session();
success := false;
select s.accessor_id, s.expires < now(),
veil2.check_nonce(nonce, s.nonces), s.nonces,
s.authent_type,
ac.context_type_id, s.has_authenticated,
s.token
into _accessor_id, expired,
nonce_ok, _nonces,
authent_type, _context_type_id,
_has_authenticated, _session_token
from veil2.sessions s
left outer join veil2.accessor_contexts ac
on ac.accessor_id = s.accessor_id
and ac.context_type_id = s.login_context_type_id
and ac.context_id = s.login_context_id
where s.session_id = open_connection.session_id;
if not found then
raise warning 'SECURITY: Connection attempt with no session: %',
session_id;
errmsg := 'AUTHFAIL';
elsif _context_type_id is null then
raise warning 'SECURITY: Connection attempt for invalid context';
errmsg := 'AUTHFAIL';
elsif expired then
errmsg := 'EXPIRED';
elsif not nonce_ok then
-- Since this could be the result of an attempt to replay a past
-- authentication token, we log this failure
raise warning 'SECURITY: Nonce failure. Nonce %, Nonces %',
nonce, to_array(_nonces);
errmsg := 'NONCEFAIL';
else
success := true;
if _has_authenticated then
-- The session has already been opened. From here on we
-- use different authentication tokens for each open_connection()
-- call in order to avoid replay attacks.
if not veil2.check_continuation(nonce, _session_token,
authent_token) then
raise warning 'SECURITY: incorrect continuation token for %, %',
_accessor_id, session_id;
errmsg := 'AUTHFAIL';
success := false;
end if;
else
if not veil2.authenticate(_accessor_id, authent_type,
authent_token) then
raise warning 'SECURITY: incorrect % authentication token for %, %',
authent_type, _accessor_id, session_id;
errmsg := 'AUTHFAIL';
success := false;
end if;
end if;
end if;
if success then
-- Reload session context
parent_session_id := veil2.reload_session_context(session_id);
if not veil2.load_connection_privs(parent_session_id) then
raise warning 'SECURITY: Accessor % has no connect privilege.',
_accessor_id;
errmsg := 'AUTHFAIL';
success := false;
end if;
end if;
-- Regardless of the success of the preceding checks we record the
-- use of the latest nonce. If all validations succeeded, we
-- extend the expiry time of the session.
perform veil2.update_session(session_id,
veil2.update_nonces(nonce, _nonces),
success);
end;
$$
language plpgsql security definer volatile
set client_min_messages = 'error';
revoke all on function veil2.open_connection(bigint, integer, text)
from public;
grant execute on function veil2.open_connection(bigint, integer, text)
to veil_user;
comment on function veil2.open_connection(bigint, integer, text) is
'Attempt to open or re-open a session. This is used to authenticate
or re-authenticate a connection, and until this is done a session
cannot be used.
Failures may be for several reasons with errmsg as described below:
- non-existence of session [errmsg: ''AUTHFAIL''];
- expiry of session (while session record still exists - has not been
cleaned away) [errmsg: ''EXPIRED''];
- incorrect credentials being used [errmsg: ''AUTHFAIL''];
- invalid nonce being provided [errmsg: ''NONCEFAIL''];
- the user has no connect privilege [errmsg: ''AUTHFAIL''].
The _nonce is a number that may only be used once per session, and is
used to prevent replay attacks. Each open_connection() call should
provide a new nonce ascending in value from the last. As connections
may be asynchronous, we do not require a strictly ascending order but
nonces may not be out of sequence by a value of more than 64. This
allows us to keep track of used nonces without excess overhead while
still allowing an application to have multiple database connections
per user session.
The value of _authent_token depends upon the authentication method
chosen. See the authentication function for your session''s
authentication method (identified in table veil2.authentication_types)
for details.
Note that warning messages will be sent to the log but not to the
client, even if client_min_messages is modified for the session. This
is deliberate, for security reasons.';
\echo ......hello()...
create or replace
function veil2.hello(
context_type_id in integer default 1,
context_id in integer default 0)
returns boolean as
$$
declare
_accessor_id integer;
_session_id bigint;
success boolean;
begin
success := false;
execute veil2.reset_session();
select accessor_id
into _accessor_id
from veil2.accessors
where username = session_user;
if found then
select cas.session_id
into _session_id
from veil2.create_accessor_session(
_accessor_id, 'dedicated',
context_type_id, context_id,
context_type_id, context_id) cas;
if veil2.load_cached_privs() then
success := true;
else
success := veil2.load_and_cache_session_privs();
end if;
if not success then
raise exception 'SECURITY: user % has no connect privilege.',
session_user;
else
-- Update the permanent session record to show that we have
-- authenticated and give a reasonable expiry time.
update veil2.sessions
set expires = now() + '1 day'::interval,
has_authenticated = true
where session_id = _session_id;
end if;
end if;
return success;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.hello(integer, integer) from public;
grant execute on function veil2.hello(integer, integer) to veil_user;
comment on function veil2.hello(integer, integer) is
'This is used to begin a veil2 session for a database user, ie someone
who can directly access the database.';
\echo ......check_become_user_priv()...
create or replace
function veil2.check_become_user_priv(
label text,
accessor_id integer,
context_type_id integer,
context_id integer)
returns text as
$$
begin
if veil2.i_have_priv_in_scope_or_superior_or_global(
1, context_type_id, context_id)
then
return null;
else
raise warning 'SECURITY: become_user() (%): no privilege for % in '
'context %,%', label, accessor_id,
context_type_id, context_id;
return 'NOPRIV';
end if;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.check_become_user_priv(
text, integer, integer, integer) from public;
comment on function veil2.check_become_user_priv(
text, integer, integer, integer) is
'Determine whether the accessor has become_user privilege for the
supplied context (including superior and global). Return ''NOPRIV''
to the caller if not.';
\echo ......check_accessor_context()...
create or replace
function veil2.check_accessor_context(
label text,
accessor_id integer,
context_type_id integer,
context_id integer)
returns text as
$$
begin
if veil2.have_accessor_context(accessor_id, context_type_id,
context_id)
then
return null;
else
raise warning 'SECURITY: become_user() (%): invalid context for %'
' - %,%', label, accessor_id, context_type_id, context_id;
return 'AUTHFAIL';
end if;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.check_accessor_context(
text, integer, integer, integer) from public;
comment on function veil2.check_accessor_context(
text, integer, integer, integer) is
'Determine whether the geven accessor has rights to the given session
context. Return ''AUTHFAIL'' if not.';
\echo ......become_accessor()...
create or replace
function veil2.become_accessor(
accessor_id in integer,
login_context_type_id in integer,
login_context_id in integer,
session_context_type_id in integer,
session_context_id in integer,
session_id out bigint,
session_token out text,
success out boolean,
errmsg out text)
returns record as
$$
declare
orig_session_id bigint;
orig_accessor_id integer;
_mapping_context_type_id integer;
_mapping_context_id integer;
begin
select sc.session_id, sc.accessor_id
into orig_session_id, orig_accessor_id
from veil2.session_context() sc;
-- We must check that login context is valid for both the current
-- and target accessors.
errmsg := veil2.check_become_user_priv('AAA', orig_accessor_id,
login_context_type_id, login_context_id);
if errmsg is null then
errmsg := veil2.check_accessor_context('BBB', orig_accessor_id,
login_context_type_id, login_context_id);
end if;
if errmsg is null then
errmsg := veil2.check_accessor_context('CCC', accessor_id,
login_context_type_id, login_context_id);
end if;
if errmsg is null then
-- Create new session_context record
perform veil2.reset_session();
select *
into session_id, _mapping_context_type_id,
_mapping_context_id
from veil2.new_session_context(accessor_id,
login_context_type_id, login_context_id,
session_context_type_id, session_context_id,
orig_session_id) x;
-- Create new session record.
insert
into veil2.sessions
(accessor_id, session_id,
login_context_type_id, login_context_id,
session_context_type_id, session_context_id,
mapping_context_type_id, mapping_context_id,
authent_type, has_authenticated,
session_supplemental, expires,
token, parent_session_id)
select become_accessor.accessor_id, become_accessor.session_id,
become_accessor.login_context_type_id,
become_accessor.login_context_id,
become_accessor.session_context_type_id,
become_accessor.session_context_id,
_mapping_context_type_id,
_mapping_context_id,
'become', true,
null, now() + sp.parameter_value::interval,
encode(digest(random()::text || now()::text, 'sha256'),
'base64'),
orig_session_id
from veil2.system_parameters sp
where sp.parameter_name = 'shared session timeout'
returning token into session_token;
-- Update expiry of parent session.
perform veil2.update_session(orig_session_id, null::bitmap, true);
if not veil2.load_cached_privs() then
if not veil2.load_and_cache_session_privs() then
raise warning 'SECURITY: Accessor % has no connect privilege.',
_accessor_id;
errmsg := 'AUTHFAIL';
end if;
end if;
end if;
if errmsg is null then
perform veil2.filter_session_privs(orig_session_id);
success := true;
else
success := false;
end if;
end;
$$
language plpgsql security definer volatile
set client_min_messages = 'error';
revoke all on function veil2.become_accessor(
integer, integer, integer, integer, integer) from public;
comment on function veil2.become_accessor(
integer, integer, integer, integer, integer) is
'Create a new opened session for the given accessor_id and context.
This allows a suitably privileged accessor to emulate another user.
The intended use-case for this is in testing and debugging access
rights. Note that the new session will not give the connected user
more privileges than they already have, so the usage of this should
probably be confined to superusers. Any other user is likely to get a
set of privileges that may be less than the user they have become
would normally get.';
\echo ......become_user()...
create or replace
function veil2.become_user(
username in text,
login_context_type_id in integer,
login_context_id in integer,
session_context_type_id in integer default null,
session_context_id in integer default null,
session_id out bigint,
session_token out text,
success out boolean,
errmsg out text)
returns record as
$$
declare
_accessor_id integer;
begin
_accessor_id := veil2.get_accessor(username, login_context_type_id,
login_context_id);
select ba.session_id, ba.session_token,
ba.success, ba.errmsg
into become_user.session_id, become_user.session_token,
become_user.success, become_user.errmsg
from veil2.become_accessor(
_accessor_id,
login_context_type_id, login_context_id,
coalesce(session_context_type_id, login_context_type_id),
coalesce(session_context_id, login_context_id)) ba;
end;
$$
language plpgsql security definer volatile;
revoke all on function veil2.become_user(
text, integer, integer, integer, integer) from public;
grant execute on function veil2.become_user(
text, integer, integer, integer, integer) to veil_user;
comment on function veil2.become_user(
text, integer, integer, integer, integer) is
'See comments for become_accessor(). This is the same but takes a
username rather than accessor_id.';
\echo ...creating veil2 privilege testing functions...
-- Ensure the veil2_session_context and session _privileges temp tables
-- exist as they are needed in order to compile the following functions.
select veil2.reset_session();
\echo ......true()...
create or replace
function veil2.always_true(integer) returns boolean
as '$libdir/veil2', 'veil2_true'
language C security definer stable leakproof;
comment on function veil2.always_true(integer) is
'Performance testing function - always returns true. Used to
establish the minimum overhead of security policies for tables.';
\echo ......i_have_global_priv()...
create or replace
function veil2.i_have_global_priv(integer) returns boolean
as '$libdir/veil2', 'veil2_i_have_global_priv'
language C security definer stable leakproof;
comment on function veil2.i_have_global_priv(integer) is
'Predicate to determine whether the connected user has the given
privilege in the global scope.';
\echo ......i_have_personal_priv()...
create or replace
function veil2.i_have_personal_priv(integer, integer) returns boolean
as '$libdir/veil2', 'veil2_i_have_personal_priv'
language C security definer stable leakproof;
comment on function veil2.i_have_personal_priv(integer, integer) is
'Predicate to determine whether the connected user has the given
privilege in the personal scope.';
\echo ......i_have_priv_in_scope()...
create or replace
function veil2.i_have_priv_in_scope(integer, integer, integer) returns boolean
as '$libdir/veil2', 'veil2_i_have_priv_in_scope'
language C security definer stable leakproof;
comment on function veil2.i_have_priv_in_scope(integer, integer, integer) is
'Predicate to determine whether the connected user has the given
privilege in the given scope.';
\echo ......i_have_priv_in_scope_or_global()...
create or replace
function veil2.i_have_priv_in_scope_or_global(
integer, integer, integer) returns boolean
as '$libdir/veil2', 'veil2_i_have_priv_in_scope_or_global'
language C security definer stable leakproof;
comment on function veil2.i_have_priv_in_scope(integer, integer, integer) is
'Predicate to determine whether the connected user has the given
privilege in the given scope, or in global scope.';
\echo ......i_have_priv_in_superior_scope()...
create or replace
function veil2.i_have_priv_in_superior_scope(integer, integer, integer)
returns boolean
as '$libdir/veil2', 'veil2_i_have_priv_in_superior_scope'
language C security definer stable leakproof;
comment on function veil2.i_have_priv_in_superior_scope(
integer, integer, integer) is
'Predicate to determine whether the connected user has the given
privilege in a scope that is superior to the given scope. This does not
check for the privilege in a global scope as it is assumed that such a
test will have already been performed. Note that due to the join on
all_superior_scopes this function may incur some small measurable
overhead.';
\echo ......i_have_priv_in_scope_or_superior()...
create or replace
function veil2.i_have_priv_in_scope_or_superior(integer, integer, integer)
returns boolean
as '$libdir/veil2', 'veil2_i_have_priv_in_scope_or_superior'
language C security definer stable leakproof;
comment on function veil2.i_have_priv_in_scope_or_superior(
integer, integer, integer) is
'Predicate to determine whether the connected user has the given
privilege in a scope that is, or is superior to the given scope. This
does not check for the privilege in a global scope as it is assumed
that such a test will have already been performed. Note that due to
the join on all_superior_scopes this function may incur some small
measurable overhead.';
\echo ......i_have_priv_in_scope_or_superior_or_global()...
create or replace
function veil2.i_have_priv_in_scope_or_superior_or_global(
integer, integer, integer)
returns boolean
as '$libdir/veil2', 'veil2_i_have_priv_in_scope_or_superior_or_global'
language C security definer stable leakproof;
comment on function veil2.i_have_priv_in_scope_or_superior_or_global(
integer, integer, integer) is
'Predicate to determine whether the connected user has the given
privilege in a scope that is, or is superior to the given scope, or in
the global scope. This does not check for the privilege in a global
scope as it is assumed that such a test will have already been
performed. Note that due to the join on all_superior_scopes this
function may incur some small measurable overhead.';
\echo ......result_counts()...
create or replace
function veil2.result_counts(false_count out integer, true_count out integer)
returns record
as '$libdir/veil2', 'veil2_result_counts'
language C security definer stable leakproof;
comment on function veil2.result_counts() is
'Return record of how many false and how many true results have been
returned by the i_have_privi_xxx() functions in this session';
revoke all on function veil2.result_counts() from public;
\echo ...creating veil2 admin and helper functions...
\echo ......delete_expired_sessions()...
create or replace
function veil2.delete_expired_sessions() returns void as
$$
delete
from veil2.sessions s
where expires <= now();
$$
language 'sql' security definer volatile;
comment on function veil2.delete_expired_sessions() is
'Utility function to clean-up session data. This should be
run periodically from a batch job.';
\echo ......bcrypt()...
create or replace
function veil2.bcrypt(passwd text) returns text as
$$
select crypt(passwd, gen_salt('bf'));
$$
language 'sql' security definer volatile;
comment on function veil2.bcrypt(text) is
'Create a bcrypted password from plaintext. It creates a value that can
be stored in veil2.authentication_details for use by the
authenticate_bcrypt() function.';
-- Create base meta-data for veil2 schema
insert into veil2.scope_types
(scope_type_id, scope_type_name, description)
values (1, 'global scope',
'Assignments made in the global context apply globally (in ' ||
'global scope): that is there are no limitions based on data ' ||
'ownership applied to these assignments'),
(2, 'personal scope',
'Privileges assigned in personal context apply to the personal ' ||
'data of the user. If they have the ''select_parties'' ' ||
'privilege assigned only in personal context, they will be ' ||
'able to see only their own party record. All parties are ' ||
'expected to have the same rights to their own data, so we ' ||
'do not explicitly assign rights in personal context, instead ' ||
'we assume that the ''personal_context'' role has been ' ||
'assigned to every party. This role is special in that it ' ||
'should not be assigned in any other context, and so ' ||
'is defined as not enabled.');
insert into veil2.scopes
(scope_type_id, scope_id)
values (1, 0);
insert into veil2.authentication_types
(shortname, enabled,
description, authent_fn)
values ('bcrypt', true,
'Bcrypted password from the user.', 'veil2.authenticate_bcrypt'),
('plaintext', false,
'Plaintext password - enable for development only',
'veil2.authenticate_plaintext'),
('dedicated', false,
'Dedicated Session. Authentication by database session username',
'veil2.authenticate_false'),
('become', false,
'From become_user. Session supplemental identifies the originating session',
'veil2.authenticate_false'),
('oath2', false, -- Placeholder. An exercise for the reader
'Openid authentication.', 'veil2.authenticate_false');
insert into veil2.privileges
(privilege_id, privilege_name,
promotion_scope_type_id, description)
values (0, 'connect', null,
'May connect to the database to execute queries.'),
(1, 'become user', null,
'May execute the become_user function. This should only ' ||
'be available to superusers'),
(2, 'select scope_types', 1,
'May select from scope_types.'),
(3, 'select scopes', null,
'May select from scopes.'),
(4, 'select privileges', 1,
'May select from privileges.'),
(5, 'select role_types', 1,
'May select from role_types.'),
(6, 'select roles', 1,
'May select from roles.'),
(7, 'select context_roles', null,
'May select from context_roles.'),
(8, 'select role_privileges', 1,
'May select from role_privileges.'),
(9, 'select role_roles', null,
'May select from role_roles.'),
(10, 'select accessors', null,
'May select from accessors.'),
(11, 'select authentication_types', 1,
'May select from authentication_types.'),
(12, 'select authentication_details', null,
'May select from authentication_details.'),
(13, 'select accessor_roles', null,
'May select from accessor_roles.'),
(14, 'select sessions', null,
'May select from sessions.'),
(15, 'select system_parameters', null,
'May select from system_parameters.');
insert
into veil2.role_types
(role_type_id, role_type_name, description)
values (1, 'default', 'A general-purpose, unspecific role type'),
(2, 'veil2',
'A Veil2-specific role type, used for access to veil2 data');
insert into veil2.roles
(role_id, role_name, implicit, immutable, description)
values (0, 'connect', false, true, 'Allow minimal access to the system.'),
(1, 'superuser', false, true, 'An all-encompassing role.'),
(2, 'personal context', true, true,
'An implicitly assigned, to all users, role that allows ' ||
'access to a user''s own information');
-- Veil-specific roles
insert
into veil2.roles
(role_id, role_type_id, role_name, implicit, immutable, description)
values (3, 2, 'veil2_viewer', false, true,
'Allow read-access to veil data');
-- Set up basic access rights.
insert into veil2.role_privileges
(role_id, privilege_id)
values (0, 0),
(2, 10) -- personal_scope gives select to accessors table
;
-- Set up veil2_viewer rights
insert into veil2.role_privileges
(role_id, privilege_id)
values (3, 2),
(3, 3),
(3, 4),
(3, 5),
(3, 6),
(3, 7),
(3, 8),
(3, 9),
(3, 10),
(3, 11),
(3, 12),
(3, 13),
(3, 14),
(3, 15);
-- system parameters
insert into veil2.system_parameters
(parameter_name, parameter_value)
values ('shared session timeout', '20 mins'),
('mapping context target scope type', '1'),
('error on uninitialized session', true);
-- Create security for vpd tables.
-- This consists of enabling row-level security and only allowing
-- select access to users with the approrpiate veil privileges.
\echo ......scope_types...
alter table veil2.scope_types enable row level security;
-- Prevent modifications to scope_types - the database owner
-- should be the only user doing this.
create policy scope_type__select
on veil2.scope_types
for select
using (veil2.i_have_global_priv(2));
\echo ......scopes...
alter table veil2.scopes enable row level security;
create policy scope__select
on veil2.scopes
for select
using ( veil2.i_have_global_priv(3)
or veil2.i_have_priv_in_scope(3, scope_type_id, scope_id));
comment on policy scope__select on veil2.scopes is
'Require privilege ''select scopes'' in global scope
(assigned in global scope), in order to see the data in this table.';
\echo ......privileges...
alter table veil2.privileges enable row level security;
create policy privilege__select
on veil2.privileges
for select
using (veil2.i_have_global_priv(4));
comment on policy privilege__select on veil2.privileges is
'Require privilege ''select privilege'' in global scope
(assigned in global scope), in order to see the data in this table.';
\echo ......role_types...
alter table veil2.role_types enable row level security;
create policy role_type__select
on veil2.role_types
for select
using (veil2.i_have_global_priv(5));
comment on policy role_type__select on veil2.role_types is
'Require privilege ''select role_type'' in global scope
(assigned in global scope), in order to see the data in this table.';
\echo ......roles...
alter table veil2.roles enable row level security;
create policy role__select
on veil2.roles
for select
using (veil2.i_have_global_priv(6));
comment on policy role__select on veil2.roles is
'Require privilege ''select roles'' in global scope
(assigned in global scope), in order to see the data in this table.';
\echo ......context_roles...
alter table veil2.context_roles enable row level security;
-- We should be able to view this if we have select_context_role
-- privilege in a suitable scope.
create policy context_role__select
on veil2.context_roles
for select
using ( veil2.i_have_global_priv(7)
or veil2.i_have_priv_in_scope(7, context_type_id, context_id)
or veil2.i_have_priv_in_superior_scope(7, context_type_id, context_id)
);
comment on policy context_role__select on veil2.context_roles is
'Require privilege ''select context_roles'' in an appropriate scope in
order to see the data in this table.';
\echo ......role_privileges...
alter table veil2.role_privileges enable row level security;
-- We should be able to view this if we have select_role_privilege
-- privilege in a suitable scope.
create policy role_privilege__select
on veil2.role_privileges
for select
using (veil2.i_have_global_priv(8));
comment on policy role_privilege__select on veil2.role_privileges is
'Require privilege ''select role_privileges'' in global scope
(assigned in global scope), in order to see the data in this table.';
\echo ......role_roles...
alter table veil2.role_roles enable row level security;
-- We should be able to view this if we have select_role_role
-- privilege in a suitable scope.
create policy role_role__select
on veil2.role_roles
using ( veil2.i_have_global_priv(9)
or veil2.i_have_priv_in_scope(9, context_type_id, context_id)
or veil2.i_have_priv_in_superior_scope(9, context_type_id, context_id)
);
comment on policy role_role__select on veil2.role_roles is
'Require privilege ''select role_roles'' in an appropriate scope in
order to see the data in this table.';
\echo ......accessors...
alter table veil2.accessors enable row level security;
-- We should be able to view this if we have select_accessor
-- privilege in a suitable scope.
create policy accessor__select
on veil2.accessors
for select
using ( veil2.i_have_global_priv(10)
or veil2.i_have_personal_priv(10, accessor_id));
comment on policy accessor__select on veil2.accessors is
'Require privilege ''select accessors'' in global scope
(assigned in global scope) or personal scope, in order to see the data
in this table.';
\echo ......authentication_types...
alter table veil2.authentication_types enable row level security;
-- We should be able to view this if we have select_authentication_type
-- privilege in a suitable scope.
create policy authentication_type__select
on veil2.authentication_types
for select
using (veil2.i_have_global_priv(11));
comment on policy authentication_type__select on veil2.authentication_types is
'Require privilege ''select authentication_types'' in global scope
(assigned in global scope) in order to see the data in this table.';
\echo ......authentication_details...
alter table veil2.authentication_details enable row level security;
-- We should be able to view this if we have select_authentication_detail
-- privilege in a suitable scope.
create policy authentication_detail__select
on veil2.authentication_details
for select
using (veil2.i_have_global_priv(12));
comment on policy authentication_detail__select
on veil2.authentication_details is
'Require privilege ''select authentication_details'' in global scope
(assigned in global scope) in order to see the data in this table.';
\echo ......accessor_roles...
alter table veil2.accessor_roles enable row level security;
-- We should be able to view this if we have select_accessor_role
-- privilege in a suitable scope.
create policy accessor_role__select
on veil2.accessor_roles
for select
using (veil2.i_have_global_priv(13)
or veil2.i_have_priv_in_scope(13, context_type_id, context_id));
comment on policy accessor_role__select on veil2.accessor_roles is
'Require privilege ''select accessor_roles'' in global scope
(assigned in global scope) in order to see the data in this table.';
\echo ......sessions...
alter table veil2.sessions enable row level security;
-- We should be able to view this if we have select_session
-- privilege in a suitable scope.
create policy session__select
on veil2.sessions
for select
using (veil2.i_have_global_priv(14));
comment on policy session__select on veil2.sessions is
'Require privilege ''select sessions'' in global scope
(assigned in global scope) or personal scope, in order to see the data
in this table.';
\echo ......system_parameters...
alter table veil2.system_parameters enable row level security;
-- We should be able to view this if we have select_system_parameter
-- privilege in a suitable scope.
create policy system_parameter__select
on veil2.system_parameters
for select
using (veil2.i_have_global_priv(15));
comment on policy system_parameter__select on veil2.system_parameters is
'Require privilege ''select system_parameters'' in global scope
(assigned in global scope) in order to see the data in this table.';
\echo ......accessor_privileges_cache...
alter table veil2.accessor_privileges_cache enable row level security;
create policy accessor_privileges_cache__all
on veil2.accessor_privileges_cache;
comment on policy accessor_privileges_cache__all
on veil2.accessor_privileges_cache is
'No access to this table should be given to normal users.';
revoke all on veil2.accessor_privileges_cache from public;
\echo ......deferred_install...
alter table veil2.deferred_install enable row level security;
create policy deferred_install__all
on veil2.deferred_install;
comment on policy deferred_install__all on veil2.deferred_install is
'No access to this table should be given to normal users';
revoke all on veil2.deferred_install from public;
-- Deal with tables that implementors and administrators are expected
-- to update.
\echo ...handling for user-defined data in pg_dump...
\echo ......scope_types...
select pg_catalog.pg_extension_config_dump(
'veil2.scope_types',
'where not scope_type_id in (1,2)');
\echo ......system_parameters...
create or replace
function veil2.system_parameters_check()
returns trigger
as
$$
begin
if tg_op = 'INSERT' then
-- Check that the insert will not result in a key collision. If
-- it will, do an update instead. The insert may come from a
-- backup from pg_dump which is why we have to handle it like
-- this.
if exists (
select null
from veil2.system_parameters
where parameter_name = new.parameter_name)
then
update veil2.system_parameters
set parameter_value = new.parameter_value
where parameter_name = new.parameter_name;
return null;
end if;
end if;
new.user_defined := true;
return new;
end;
$$
language 'plpgsql' security definer volatile leakproof;
comment on function veil2.system_parameters_check() is
'Trigger function to allow pg_dump to dump and restore user-defined
system parameters, and to ensure all inserted and updated rows are
identfied as user_defined.';
create trigger system_parameters_biu before insert or update
on veil2.system_parameters
for each row execute function veil2.system_parameters_check();
select pg_catalog.pg_extension_config_dump(
'veil2.system_parameters',
'where user_defined');
\echo ......authentication_types...
create or replace
function veil2.make_user_defined()
returns trigger
as
$$
begin
if tg_op = 'INSERT' then
-- Check that the insert will not result in a key collision. If
-- it will, do an update instead. The insert may come from a
-- backup from pg_dump which is why we have to handle it like
-- this.
if exists (
select null
from veil2.authentication_types
where shortname = new.shortname)
then
update veil2.authentication_types
set enabled = new.enabled,
description = new.description,
authent_fn = new.authent_fn,
supplemental_fn = new.supplemental_fn
where shortname = new.shortname;
return null;
end if;
end if;
new.user_defined := true;
return new;
end;
$$
language 'plpgsql' security definer volatile leakproof;
create trigger authentication_types_biu before insert or update
on veil2.authentication_types
for each row execute function veil2.make_user_defined();
select pg_catalog.pg_extension_config_dump(
'veil2.authentication_types',
'where user_defined');
\echo ......privileges...
select pg_catalog.pg_extension_config_dump(
'veil2.privileges',
'where privilege_id >= 20
or privilege_id < 0');
\echo ......roles...
select pg_catalog.pg_extension_config_dump(
'veil2.roles',
'where role_id > 4
or role_id < 0');
select pg_catalog.pg_extension_config_dump(
'veil2.role_types',
'where role_type_id not in (1, 2)');
\echo ......role_privileges...
select pg_catalog.pg_extension_config_dump(
'veil2.role_privileges',
'where role_id > 4
or role_id < 0');
\echo ......role_roles...
select pg_catalog.pg_extension_config_dump(
'veil2.role_roles', '');
\echo ......scopes...
select pg_catalog.pg_extension_config_dump(
'veil2.scopes',
'where scope_type_id != 1
or scope_id != 0');
\echo ......accessors...
select pg_catalog.pg_extension_config_dump(
'veil2.accessors', '');
\echo ......authentication_details...
select pg_catalog.pg_extension_config_dump(
'veil2.authentication_details', '');
-- Functions for checking implementation status. These are to help
-- security model implementors.
\echo ...Functions for checking implementation status...
\echo ......have_user_scope_types()...
create or replace
function veil2.have_user_scope_types()
returns boolean as
$$
-- Have we defined new scope_types:
select exists (
select null
from veil2.scope_types
where scope_type_id not in (1, 2));
$$
language sql security definer stable;
comment on function veil2.have_user_scope_types() is
'Predicate used to determine whether user-defined scope_types have been
added to the implementation.';
\echo ......have_user_user_privileges()...
create or replace
function veil2.have_user_privileges()
returns boolean as
$$
select exists (
select null
from veil2.privileges
where privilege_id > 15
or privilege_id < 0);
$$
language sql security definer volatile;
comment on function veil2.have_user_privileges() is
'Predicate used to determine whether any user-defined privileges have
been created.';
\echo ......have_user_user_roles()...
create or replace
function veil2.have_user_roles()
returns boolean as
$$
select exists (
select null
from veil2.roles
where role_id > 4
or role_id < 0);
$$
language sql security definer volatile;
comment on function veil2.have_user_roles() is
'Predicate used to determine whether any user-defined roles have
been created.';
\echo ......have_role_privileges()...
create or replace
function veil2.have_role_privileges()
returns boolean as
$$
select exists (
select null
from veil2.role_privileges
where role_id < 0
or role_id > 4);
$$
language sql security definer volatile;
comment on function veil2.have_role_privileges() is
'Predicate used to determine whether any user-defined role_privileges
have been created.';
\echo ......have_role_roles()...
create or replace
function veil2.have_role_roles()
returns boolean as
$$
select exists (
select null
from veil2.role_roles);
$$
language sql security definer volatile;
comment on function veil2.have_role_roles() is
'Predicate used to determine whether any user-defined role_roles (role
to role mappings) have been created.';
\echo ......have_accessors()...
create or replace
function veil2.have_accessors()
returns boolean as
$$
select exists (
select null
from veil2.accessors);
$$
language sql security definer volatile;
comment on function veil2.have_accessors() is
'Predicate used to determine whether any accessors have been defined.';
\echo ......have_user_scopes()...
create or replace
function veil2.have_user_scopes ()
returns boolean as
$$
select exists (
select null
from veil2.scopes
where scope_type_id != 1
and scope_id != 0);
$$
language sql security definer volatile;
comment on function veil2.have_user_scopes() is
'Predicate used to determine whether any user-defined scopes have been
created.';
\echo ......check_table_security()...
create or replace
function veil2.check_table_security()
returns setof text as
$$
declare
tbl text;
header_returned boolean := false;
begin
for tbl in
select n.nspname || '.' || c.relname
from pg_catalog.pg_class c
inner join pg_catalog.pg_namespace n
on n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname not in ('pg_catalog', 'information_schema')
and c.relpersistence = 'p'
and not relrowsecurity
loop
if not header_returned then
header_returned := true;
return next 'The following tables have no security policies:';
end if;
return next ' - ' || tbl;
end loop;
if not header_returned then
return next 'All tables appear to have security policies.';
end if;
end;
$$
language plpgsql security definer stable;
comment on function veil2.check_table_security() is
'Predicate used to determine whether all user-defined tables have
security policies in place.';
\echo ......implementation_status()...
create or replace
function veil2.implementation_status()
returns setof text as
$$
declare
ok boolean := true;
line text;
begin
perform veil2.init();
if not veil2.have_user_scope_types() then
ok := false;
return next 'You need to define some scope types (step 2)';
end if;
if not veil2.view_exists('my_accessor_contexts') then
ok := false;
return next 'You need to redefine the accessor_contexts view (step 3)';
end if;
if not veil2.function_exists('my_get_accessor') then
ok := false;
return next 'You need to define a get_accessor() function (step 3)';
end if;
if not veil2.have_accessors() then
ok := false;
return next 'You need to create accessors (and maybe FK links) (step 4)';
end if;
if not veil2.have_user_scopes() then
ok := false;
return next 'You need to create user scopes (step 5)';
end if;
if not veil2.view_exists('my_superior_scopes') then
ok := false;
return next 'You need to redefine the superior_scopes view (step 6)';
else
execute('refresh materialized view veil2.all_superior_scopes');
end if;
if not veil2.have_user_privileges() then
ok := false;
return next 'You need to define some privileges (step 7)';
end if;
if not veil2.have_user_roles() then
ok := false;
return next 'You need to define some roles (step 8)';
end if;
if not veil2.have_role_privileges() then
ok := false;
return next 'You need to create entries in role_privileges (step 8)';
end if;
if not veil2.have_role_roles() then
ok := false;
return next 'You need to create entries in role_roles (step 8)';
end if;
if ok then
return next 'Your Veil2 basic implemementation seems to be complete.';
end if;
for line in select * from veil2.check_table_security()
loop
return next line;
end loop;
if ok then
return next 'Have you secured your views (I have no way of knowing)?';
end if;
end;
$$
language plpgsql security definer volatile;
comment on function veil2.implementation_status() is
'Set returning function that identifies incomplete
user-implementations.
Call this using select * from veil2.implementation_status();
and it will return a list of things to implement or consider implementing.';
create or replace
view veil2.docs(file, purpose) as
values (veil2.docpath() || '/html/index.html',
'Complete html documentation for Veil2');
comment on view veil2.docs is
'Show where local Veil2 documentation can be found.';
create or replace
view veil2.sql_files(file, purpose) as
values (veil2.datapath() || '/demo.sql',
'Install demo and run test'),
(veil2.datapath() || '/demo_test.sql',
'Run simple tests against demo'),
(veil2.datapath() || '/demo_bulk_data.sql',
'Install some bulk role and priv data'),
(veil2.datapath() || '/perf.sql',
'Run session management performance check'),
(veil2.datapath() || '/veil2_demo--0.9.1.sql',
'Veil2 demo creation script'),
(veil2.datapath() || '/veil2_minimal_demo.sql',
'Veil2 minimal-demo creation script'),
(veil2.datapath() || '/veil2_template.sql',
'Veil2 implementation template.'),
(veil2.datapath() || '/veil2--0.9.1.sql',
'Veil2 extension creation script.');
comment on view veil2.sql_files is
'Show where copies of useful Veil2 sql files can be found.';