The following functions, defined in
sql/veil2--0.9.1.sql, are for
managing Referential Integrity:
These functions are for managing user-provided database objects (functions and views):
These functions are for authentication handling:
These functions are for session handling:
These functions are for testing session privileges:
Utility and administration functions:
Functions for refreshing materialized views:
Functions for checking the status of your Veil2
secured database implementation:
Other trigger functions:
And these triggers exist:
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;
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.
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;
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()
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); --raise warning '%', fn_defn; execute fn_defn; end; $$ language plpgsql security definer volatile;
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.
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;
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()
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;
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.
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;
Predicate returning true if a function named
fn_name exists in the veil2 schema.
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;
Predicate returning true if a view named
vw_name exists in the veil2 schema.
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;
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.
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;
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()
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;
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.
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;
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.
function veil2.deferred_install_fn() returns trigger as $$ begin perform veil2.init(); return new; end; $$ language plpgsql security definer volatile;
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.
function veil2.authenticate_false(
accessor_id integer,
token text)
returns boolean as
$$
select false;
$$
language sql security definer stable;
Authentication predicate for unimplemented or disabled authentication types. This function always returns false, causing authentication to fail.
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;
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!
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;
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.
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;
For the given accessor_id and authentication_type check whether token is an appropriate authentication.
function veil2.session_ready() returns boolean
as '$libdir/veil2', 'veil2_session_ready'
language C stable strict;
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.
The Doxygen documentation for this can be found here.
function veil2.reset_session() returns void
as '$libdir/veil2', 'veil2_reset_session'
language C volatile strict security definer;
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.
The Doxygen documentation for this can be found here.
function veil2.reset_session_privs() returns void
as '$libdir/veil2', 'veil2_reset_session_privs'
language C volatile strict security definer;
Safely clear the contents of the veil2_session_privileges temporary table.
The Doxygen documentation for this can be found here.
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;
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().
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 integer default null,
session_id out integer,
mapping_context_type_id out integer,
mapping_context_id out integer)
returns record as
$$
insert
into veil2_session_context
(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,
parent_session_id)
select new_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,
new_session_context.parent_session_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
where sp.parameter_name = 'mapping context target scope type'
returning veil2_session_context.session_id,
veil2_session_context.mapping_context_type_id,
veil2_session_context.mapping_context_id;
$$
language sql security definer volatile;
Create a veil2_session_context record for the given parameters, returning session_id and mapping context.
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;
Predicate to determine whether an accessor has the right to use the given session context.
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 integer,
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';
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().
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 integer,
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';
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.
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;
Check that nonce has not already been used and is within the range of acceptable values, returning true if all is well.
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;
Add nonce to the list of used nonces, slimming the bitmap down when it gets too large.
function veil2.filter_privs()
returns void as
$$
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
)
update veil2_session_privileges sp
set roles = fp.roles,
privs = fp.privs
from final_privs fp
where sp.scope_type_id = fp.scope_type_id
and sp.scope_id = fp.scope_id;
end;
$$
language plpgsql security definer volatile;
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.
function veil2.load_ancestor_privs(parent_session_id integer)
returns void as
$$
begin
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;
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).
function veil2.filter_session_privs(parent_session_id integer) returns void as $$ begin perform veil2.load_ancestor_privs(parent_session_id); perform veil2.filter_privs(); end; $$ language plpgsql security definer volatile;
Remove from veil2_session_privileges any roles and privileges not held by the ancestor session(s).
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;
Return all roles for the given accessor in the given session context.
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;
Give the set of base (ignoring privilege promotion) roles and privileges that apply to a given accessor in given mapping and session contexts.
function veil2.session_context(
accessor_id out integer,
session_id out integer,
login_context_type_id out integer,
login_context_id out integer,
session_context_type_id out integer,
session_context_id out integer,
mapping_context_type_id out integer,
mapping_context_id out integer
)
returns record as
$$
begin
select sc.accessor_id, sc.session_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
into session_context.accessor_id, session_context.session_id,
session_context.login_context_type_id,
session_context.login_context_id,
session_context.session_context_type_id,
session_context.session_context_id,
session_context.mapping_context_type_id,
session_context.mapping_context_id
from veil2_session_context sc;
exception
when sqlstate '42P01' then
-- Temp table does not exist
return;
when others then
raise;
end;
$$
language plpgsql security definer volatile;
Column accessor_id: The id of the accessor whose session this is.
Column accessor_id: The id of the accessor whose access rights (mostly) are being used by
this session. If this is not the same as the accessor_id, then the
session_user has assumed the access rights of this accessor using the
become_user() function.
Column login_context_type_id: This is the context_type_id for the context within which our accessor
has authenticated. This will have been the context_type_id provided
to the create_session() or hello() function that began this session.
Column login_context_id: This is the context_id for the context within which our accessor
has authenticated. This will have been the context_id provided
to the create_session() or hello() function that began this session.
Column session_context_type_id: This is the context_type_id to be used for limiting our session's
assigned roles and from which is determined our
mapping_context_type_id. Ordinarily, this will be the same as our
login_context_type_id, but if create_session() has been provided with
session_context parameters, this will be different. Note that for an
accessor to create such a session they must have connect privilege in
both their login context and their requested session context.
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.
function veil2.session_privileges(
scope_type_id out integer,
scope_id out integer,
roles out integer[],
privs out integer[]
)
returns setof record as
$$
begin
for session_privileges.scope_type_id,
session_privileges.scope_id,
session_privileges.roles,
session_privileges.privs
in select sp.scope_type_id, sp.scope_id,
to_array(sp.roles), to_array(sp.privs)
from veil2_session_privileges sp
loop
return next;
end loop;
exception
when sqlstate '42P01' then
return;
when others then
raise;
end;
$$
language plpgsql security definer volatile;
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.
function veil2.load_session_privs()
returns boolean as
$$
begin
insert
into veil2_session_privileges
(scope_type_id, scope_id,
roles, privs)
select scope_type_id, scope_id,
roles, privileges
from veil2.session_privileges_v;
if found then
perform veil2.save_session_privs();
return true;
else
return false;
end if;
end;
$$
language plpgsql security definer volatile;
Load the temporary table veil2_session_privileges for session_id, with the privileges for the current session. The temporary table is queried by security functions in order to determine what access rights the connected user has.
function veil2.save_session_privs()
returns void as
$$
delete
from veil2.accessor_privileges_cache
where (accessor_id, login_context_type_id,
login_context_id) = (
select accessor_id, login_context_type_id,
login_context_id
from veil2_session_context);
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, sp.scope_type_id,
sp.scope_id, sp.roles,
sp.privs
from veil2_session_context sc
cross join veil2_session_privileges sp;
$$
language 'sql' security definer volatile;
Save the current contents of the veil2_session_privileges temporary table into veil2.session_privileges after ensuring that there is no existing data present for the session. This saves our session_privileges data for future use in the session.
function veil2.load_cached_privs()
returns boolean as
$$
begin
insert
into veil2_session_privileges
(scope_type_id, scope_id,
roles, privs)
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;
return found;
end;
$$
language 'plpgsql' security definer volatile;
Reload cached session privileges for the session's accessor into our current session.
function veil2.update_session(
_session_id integer,
_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;
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.
function veil2.load_connection_privs(
parent_session_id integer)
returns boolean as
$$
begin
if not veil2.load_cached_privs() then
if not veil2.load_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;
Load veil_session_privileges temp table for a session, filtering the privileges if neccesary for a become_user() session.
function veil2.reload_session_context(_session_id integer)
returns integer as
$$
insert
into veil2_session_context
(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,
parent_session_id)
select 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
from veil2.sessions s
where s.session_id = _session_id
returning parent_session_id;
$$
language sql security definer volatile;
(Re)load veil2_session_context for a given session_id.
function veil2.reload_connection_privs()
returns boolean as
$$
declare
_parent_session_id integer;
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;
Reload a session's privileges. This would be done in the event that cached privileges need to be reloaded.
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;
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.
function veil2.open_connection(
session_id in integer,
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
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
perform veil2.reset_session();
-- 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;
if not success then
perform veil2.reset_session();
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';
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.
function veil2.close_connection() returns boolean as $$ begin perform veil2.reset_session(); delete from veil2_session_privileges; return true; end; $$ language 'plpgsql' security definer volatile;
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.
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 integer;
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;
-- TODO: CHECK IF REFACTORING IS NEEDED HERE - MAYBE UNUSED VARS?
success := veil2.load_session_privs();
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;
This is used to begin a veil2 session for a database user, ie someone who can directly access the database.
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;
Determine whether the accessor has become_user privilege for the supplied context (including superior and global). Return 'NOPRIV' to the caller if not.
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;
Determine whether the geven accessor has rights to the given session context. Return 'AUTHFAIL' if not.
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 integer,
session_token out text,
success out boolean,
errmsg out text)
returns record as
$$
declare
orig_session_id integer;
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_session_privs() then
raise warning 'SECURITY: Accessor % has no connect privilege.',
_accessor_id;
errmsg := 'AUTHFAIL';
else
raise warning 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
perform veil2.filter_session_privs(orig_session_id);
end if;
end if;
if errmsg is null then
success := true;
else
success := false;
end if;
end;
$$
language plpgsql security definer volatile
set client_min_messages = 'error';
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.
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 integer,
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;
See comments for become_accessor(). This is the same but takes a username rather than accessor_id.
function veil2.always_true(integer) returns boolean
as '$libdir/veil2', 'veil2_true'
language C security definer stable leakproof;
Performance testing function - always returns true. Used to establish the minimum overhead of security policies for tables.
The Doxygen documentation for this can be found here.
function veil2.i_have_global_priv(integer) returns boolean
as '$libdir/veil2', 'veil2_i_have_global_priv'
language C security definer stable leakproof;
Predicate to determine whether the connected user has the given privilege in the global scope.
The Doxygen documentation for this can be found here.
function veil2.i_have_personal_priv(integer, integer) returns boolean
as '$libdir/veil2', 'veil2_i_have_personal_priv'
language C security definer stable leakproof;
Predicate to determine whether the connected user has the given privilege in the personal scope.
The Doxygen documentation for this can be found here.
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;
Predicate to determine whether the connected user has the given privilege in the given scope.
Predicate to determine whether the connected user has the given privilege in the given scope, or in global scope.
The Doxygen documentation for this can be found here.
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;
The Doxygen documentation for this can be found here.
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;
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.
The Doxygen documentation for this can be found here.
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;
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.
The Doxygen documentation for this can be found here.
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;
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.
The Doxygen documentation for this can be found here.
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;
Return record of how many false and how many true results have been returned by the i_have_privi_xxx() functions in this session
The Doxygen documentation for this can be found here.
function veil2.delete_expired_sessions() returns void as
$$
delete
from veil2.sessions s
where expires <= now();
$$
language 'sql' security definer volatile;
Utility function to clean-up session data. This should be run periodically from a batch job.
function veil2.docpath() returns text
as '$libdir/veil2', 'veil2_docpath'
language C stable strict;
Return the path to the directory under which Veil2 documents will be installed.
function veil2.datapath() returns text
as '$libdir/veil2', 'veil2_datapath'
language C stable strict;
Return the path to the directory under which Veil2 scripts will be installed.
function veil2.bcrypt(passwd text) returns text as
$$
select crypt(passwd, gen_salt('bf'));
$$
language 'sql' security definer volatile;
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.
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;
Clear all matviews and caches unconditionally.
function veil2.refresh_scopes_matviews() returns trigger as $$ begin perform veil2.refresh_all_matviews(); return new; end; $$ language plpgsql security definer;
Trigger function to refresh all materialized views and caches that depend on the scopes hierarchy.
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;
Trigger function to refresh all materialized views and caches that depend on roles.
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;
Trigger function to refresh all materialized views and caches that depend on privileges.
function veil2.clear_accessor_privs_cache() returns trigger as $$ begin truncate table veil2.accessor_privileges_cache; return new; end; $$ language plpgsql security definer volatile;
Clear cached role and privileges information for all accessors.
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;
Clear the cached role and privileges information for the affected accessor.
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;
Predicate used to determine whether user-defined scope_types have been added to the implementation.
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;
Predicate used to determine whether any user-defined privileges have been created.
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;
Predicate used to determine whether any user-defined roles have been created.
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;
Predicate used to determine whether any user-defined role_privileges have been created.
function veil2.have_role_roles()
returns boolean as
$$
select exists (
select null
from veil2.role_roles);
$$
language sql security definer volatile;
Predicate used to determine whether any user-defined role_roles (role to role mappings) have been created.
function veil2.have_accessors()
returns boolean as
$$
select exists (
select null
from veil2.accessors);
$$
language sql security definer volatile;
Predicate used to determine whether any accessors have been defined.
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;
Predicate used to determine whether any user-defined scopes have been created.
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;
Predicate used to determine whether all user-defined tables have security policies in place.
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;
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.
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;
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.
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 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');
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.
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');
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.
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');
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.
create trigger scopes__aiudt after insert or update or delete or truncate on veil2.scopes for each statement execute procedure veil2.refresh_scopes_matviews();
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.
create trigger deferred_install_trg after insert on veil2.deferred_install for each statement execute function veil2.deferred_install_fn();
This trigger exists to allow inserts into the deferred install table to cause user-provided functions and views to be installed after the current system-provided functions have completed running. This is to prevent the function that inserts into the table from being overwritten while it is still running. PostgreSQL may handle this well, I don't know - but I see no reason to stress the implementation any further than I must.
create trigger system_parameters_biu before insert or update on veil2.system_parameters for each row execute function veil2.system_parameters_check();