Contents
-
Appendix C. Veil2 Functions and Triggers
-
- C.1. Functions for managing Referential Integrity
- C.2. Functions for Management of User-Provided Database Objects
- C.3. Authentication Functions
- C.4. Session Functions
- C.5. Privilege Testing Functions
- C.6. Utility and Administration Functions
- C.7. Materialized View Functions
- C.8. Functions For Checking Your Implementation
- C.9. Other Trigger Functions
- C.10. Built-in Triggers
-
Prev | Next | |
Appendix B. Veil2 Views, Materialized Views and Caches |
Home | Appendix D. Veil2 C Implementation |
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:
- session_ready();
- reset_session();
- reset_session_privs();
- get_accessor();
- new_session_context();
- have_accessor_context();
- create_accessor_session();
- create_session();
- check_nonce();
- update_nonces();
- filter_privs();
- load_ancestor_privs();
- filter_session_privs();
- save_privs_as_orig();
- all_accessor_roles();
- base_accessor_roleprivs();
- session_context();
- session_privileges();
- load_session_privs();
- save_session_privs();
- load_cached_privs();
- update_session();
- load_connection_privs();
- reload_session_context();
- reload_connection_privs();
- check_continuation();
- open_connection();
- close_connection();
- hello();
- check_become_user_priv();
- check_accessor_context();
- become_accessor();
- become_user().
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();