Appendix C. Veil2 Functions and Triggers

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);
  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 bigint default null,
    session_id out bigint,
    mapping_context_type_id out integer,
    mapping_context_id out integer)
  returns record as
$$
  select currval('veil2.session_id_seq'), sc.mapping_context_type_id,
  	 sc.mapping_context_id
    from veil2.system_parameters sp
    left outer join veil2.all_superior_scopes asp
      on asp.scope_type_id = new_session_context.session_context_type_id
     and asp.scope_id = new_session_context.session_context_id
     and asp.superior_scope_type_id = sp.parameter_value::integer
     and asp.is_type_promotion
   cross join lateral veil2.session_context(
             accessor_id, nextval('veil2.session_id_seq'),
   	     new_session_context.login_context_type_id,
   	       new_session_context.login_context_id,
   	     new_session_context.session_context_type_id,
   	       new_session_context.session_context_id,
	     case when sp.parameter_value = '1' then 1
             else coalesce(asp.superior_scope_type_id,
	                   new_session_context.session_context_type_id) end,
               case when sp.parameter_value = '1' then 0
               else coalesce(asp.superior_scope_id,
	                     new_session_context.session_context_id) end,
	      parent_session_id) sc
   where sp.parameter_name = 'mapping context target scope type';
$$
language sql security definer volatile;

Create an in-memory 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 bigint,
    session_token out text,
    session_supplemental out text)
  returns record as
$$
declare
  _mapping_context_type_id integer;
  _mapping_context_id integer;
  supplemental_fn text;
begin
  execute veil2.reset_session();

  -- Regardless of validity of accessor_id, we create a
  -- veil2_session_context record.  This is to prevent fishing for
  -- valid accessor_ids.
  select *
    into session_id, _mapping_context_type_id,
	 _mapping_context_id
     from veil2.new_session_context(
  	     accessor_id,
	     login_context_type_id, login_context_id,
	     session_context_type_id, session_context_id) x;

  -- Figure out the session tokens.  This must succeed regardless of
  -- the validity of our parameters.
  select t.supplemental_fn
    into supplemental_fn
    from veil2.authentication_types t
   where shortname = authent_type;

  if supplemental_fn is not null then
    execute format('select * from %s(%s, %L)',
                   supplemental_fn, _accessor_id, session_token)
       into session_token, session_supplemental;
  else
    session_token := encode(digest(random()::text || now()::text, 'sha256'),
		            'base64');
  end if;

  if veil2.have_accessor_context(accessor_id, login_context_type_id,
     				 login_context_id)
  then
    insert
      into veil2.sessions
          (accessor_id, session_id,
	   login_context_type_id, login_context_id,
	   session_context_type_id, session_context_id,
	   mapping_context_type_id, mapping_context_id,
	   authent_type, has_authenticated,
	   session_supplemental, expires,
	   token)
    select create_accessor_session.accessor_id,
	     create_accessor_session.session_id, 
    	   create_accessor_session.login_context_type_id,
	     create_accessor_session.login_context_id,
	   create_accessor_session.session_context_type_id,
	     create_accessor_session.session_context_id,
    	   _mapping_context_type_id, _mapping_context_id,
	   authent_type, false,
	   session_supplemental, now() + sp.parameter_value::interval,
	   session_token
      from veil2.system_parameters sp
     where sp.parameter_name = 'shared session timeout';
  end if;
end;
$$
language plpgsql security definer volatile
set client_min_messages = 'error';

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 bigint,
    session_token out text,
    session_supplemental out text)
  returns record as
$$
declare
  _accessor_id integer;
begin
  -- Generate session_id and session_token and establish whether
  -- username was valid.
  _accessor_id := veil2.get_accessor(username, context_type_id, context_id);

  select cas.session_id, cas.session_token,
         cas.session_supplemental
    into create_session.session_id, create_session.session_token,
         create_session.session_supplemental
    from veil2.create_accessor_session(
             _accessor_id, authent_type,
	     context_type_id, context_id,
	     coalesce(session_context_type_id, context_type_id),
	     coalesce(session_context_id, context_id)) cas;
end;
$$
language plpgsql security definer volatile
set client_min_messages = 'error';

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
$$
declare
  _count integer;
begin
  -- We are going to update veil2_session_privileges to remove any
  -- roles and privileges that do not exist in
  -- veil2_ancestor_privileges.  This is part of the become user
  -- process, to ensure that become user cannot lead to privilege
  -- escalation.
  with updatable_privs as
    (
      select sp.scope_type_id, sp.scope_id
        from veil2.session_privileges() sp
       where scope_type_id != 2
    ),
  superior_scopes as
    (
      -- For each scope, other than personal, in
      -- veil2_session_privileges, identify all scopes that could give
      -- us equivalent privileges.
      select up.scope_type_id, up.scope_id,
      	     asp.scope_type_id as test_scope_type_id,
	       asp.scope_id as test_scope_id
        from updatable_privs up
       inner join veil2.all_superior_scopes asp
          on asp.scope_type_id = up.scope_type_id
         and asp.scope_id = up.scope_id
       union all
      select scope_type_id, scope_id, scope_type_id, scope_id
        from updatable_privs
       union all
      select scope_type_id, scope_id, 1, 0  -- global scope is superior
        from updatable_privs
    ),
  effective_ancestor_privs as
    (
      -- The set of privileges our ancestor effectively has at each
      -- scope level.  These privileges may be from global or other
      -- superior scopes.
      select ss.scope_type_id, ss.scope_id,
      	     union_of(coalesce(vap.roles, bitmap())) as roles,
	       union_of(coalesce(vap.privs, bitmap())) as privs
        from superior_scopes ss
        left outer join veil2_ancestor_privileges vap
          on vap.scope_type_id = ss.test_scope_type_id
	 and vap.scope_id = ss.test_scope_id
       group by ss.scope_type_id, ss.scope_id
    ),
  final_privs as
    (
      -- What the become-user session's roles and privs should be
      -- after filtering any that the ancestor session did not
      -- effectively have.
      select sp.scope_type_id, sp.scope_id,
             sp.roles * eap.roles as roles,
             sp.privs * eap.privs as privs
        from veil2.session_privileges() sp
       inner join effective_ancestor_privs eap
          on eap.scope_type_id = sp.scope_type_id
         and eap.scope_id = sp.scope_id
    ),
  updates as
    (
      select veil2.update_session_privileges(scope_type_id, scope_id,
					     roles, privs), 1
        from final_privs
    )
  select count(*)::integer
    into _count
    from updates;
end;
$$
language plpgsql security definer volatile;

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 bigint)
  returns void as
$$
begin
  delete
    from veil2_ancestor_privileges;
  with recursive ancestors as
    (
      select *
        from veil2.sessions
       where session_id = load_ancestor_privs.parent_session_id
       union all
      select s.*
        from ancestors a
       inner join veil2.sessions s
          on s.session_id = a.parent_session_id
    ),
   base_accessor_privs as
    (
      select barp.accessor_id, barp.role_id,
      	     barp.assignment_context_type_id,
                 barp.assignment_context_id,
             barp.mapping_context_type_id, barp.mapping_context_id,
	     barp.roles, barp.privileges
        from ancestors a
       -- Making the following an inner join (rather than cross-join)
       -- even though the condition is redundant improves performance
       -- (Postgres 12.5).
       inner join veil2.base_accessor_roleprivs(
       	     	            a.accessor_id, a.session_context_type_id,
			    a.session_context_id, a.mapping_context_type_id,
			    a.mapping_context_id) barp
          on barp.accessor_id = a.accessor_id
       where assignment_context_type_id != 2 -- ignore personal context
    ),
  promoted_privs as
    (
      select bap.accessor_id, bap.role_id,
      	     bap.mapping_context_type_id, bap.mapping_context_id,
	     pp.scope_type_id, ss.superior_scope_id as scope_id,
  	     bap.privileges * pp.privilege_ids as privileges
        from base_accessor_privs bap
       inner join veil2.promotable_privileges pp
          on not is_empty(bap.privileges * pp.privilege_ids)
         and pp.scope_type_id != 1
       inner join veil2.all_superior_scopes ss
          on ss.scope_type_id = bap.assignment_context_type_id
         and ss.scope_id = bap.assignment_context_id
         and ss.superior_scope_type_id = pp.scope_type_id
	 and ss.is_type_promotion
    ),
  global_privs as
    (
      select bap.accessor_id, bap.role_id,
      	     bap.mapping_context_type_id, bap.mapping_context_id,
	     pp.scope_type_id, 0 as scope_id,
  	     bap.privileges * pp.privilege_ids as privileges
        from base_accessor_privs bap
       inner join veil2.promotable_privileges pp
          on not is_empty(bap.privileges * pp.privilege_ids)
         and pp.scope_type_id = 1
    ),  
  all_role_privs as
    (
      select accessor_id, 
      	     mapping_context_type_id, mapping_context_id,
  	     assignment_context_type_id as scope_type_id,
             assignment_context_id as scope_id,
             roles + role_id as roles,  privileges
        from base_accessor_privs
       union all
      select accessor_id, 
             mapping_context_type_id, mapping_context_id,
  	     scope_type_id, scope_id,
             bitmap() as roles, privileges
        from promoted_privs
       union all
      select accessor_id, 
             mapping_context_type_id, mapping_context_id,
  	     scope_type_id, scope_id,
             bitmap() as roles, privileges
        from global_privs
    ),
  grouped_role_privs as
    (
      select scope_type_id, scope_id,
             union_of(roles) as roles, union_of(privileges) as privs
        from all_role_privs
       group by scope_type_id, scope_id
    )
  insert
    into veil2_ancestor_privileges
        (scope_type_id, scope_id, roles, privs)
  select scope_type_id, scope_id, roles, privs
    from grouped_role_privs;
end;
$$
language plpgsql security definer volatile;

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 bigint)
  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             in out integer default null,
    session_id              in out bigint  default null,
    login_context_type_id   in out integer default null,
    login_context_id        in out integer default null,
    session_context_type_id in out integer default null,
    session_context_id      in out integer default null,
    mapping_context_type_id in out integer default null,
    mapping_context_id      in out integer default null,
    parent_session_id       in out bigint  default null) returns record
     as '$libdir/veil2', 'veil2_session_context'
     language C volatile;

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.

The Doxygen documentation for this can be found here.

function veil2.session_privileges(
    scope_type_id   out integer,
    scope_id        out integer,
    roles	    out bitmap,
    privs	    out bitmap)
  returns setof record 
     as '$libdir/veil2', 'veil2_session_privileges'
     language C stable;

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.

The Doxygen documentation for this can be found here.

function veil2.add_session_privileges(
    scope_type_id integer,
    scope_id integer,
    roles bitmap,
    privs bitmap)
  returns void
     as '$libdir/veil2', 'veil2_add_session_privileges'
     language C volatile;
function veil2.add_session_privileges(
    integer, integer, bitmap, bitmap) from public;

comment on function veil2.add_session_privileges(
    integer, integer, bitmap, bitmap) is
'Record in-memory copies of session privileges for a given scope for
the session.  Note that it is imperative that this is called in
scope_type_id, scope_id order so that the in-memory array can be
bsearched.';


\echo ......update_session_privileges()...
create or replace
function veil2.update_session_privileges(
    scope_type_id integer,
    scope_id integer,
    roles bitmap,
    privs bitmap)
  returns void
     as '$libdir/veil2', 'veil2_update_session_privileges'
     language C volatile;

Record in-memory copies of session privileges for a given scope for the session. Note that it is imperative that this is called in scope_type_id, scope_id order so that the in-memory array can be bsearched.

The Doxygen documentation for this can be found here.

function veil2.update_session_privileges(
    scope_type_id integer,
    scope_id integer,
    roles bitmap,
    privs bitmap)
  returns void
     as '$libdir/veil2', 'veil2_update_session_privileges'
     language C volatile;
function veil2.update_session_privileges(
    integer, integer, bitmap, bitmap) from public;

comment on function veil2.update_session_privileges(
    integer, integer, bitmap, bitmap) is
'Update the in-memory roles and privileges bitmap for a given scope.';


\echo ......session_assignment_contexts...
create or replace
view veil2.session_assignment_contexts as
with session_context as
  (
    select *
      from veil2.session_context()
  )
select login_context_type_id as context_type_id,
       login_context_id as context_id
  from session_context sc
 union
select session_context_type_id as context_type_id,
       session_context_id as context_id
  from session_context sc
 union
select ass.superior_scope_type_id,
       ass.superior_scope_id
  from session_context sc
 inner join veil2.all_superior_scopes ass
    on (    ass.scope_type_id = sc.login_context_type_id
        and ass.scope_id = sc.login_context_id)
    or (    ass.scope_type_id = sc.session_context_type_id
        and ass.scope_id = sc.session_context_id)
 union
select ass.scope_type_id,
       ass.scope_id
  from session_context sc
 inner join veil2.all_superior_scopes ass
    on (    ass.superior_scope_type_id = sc.login_context_type_id
        and ass.superior_scope_id = sc.login_context_id)
    or (    ass.superior_scope_type_id = sc.session_context_type_id
        and ass.superior_scope_id = sc.session_context_id)
 union
select 1, 0
 union
select 2, accessor_id
  from session_context;

comment on view veil2.session_assignment_contexts is
'Provides the set of security contexts which are valid for role
assignments within the current session.  The purpose of this is to
filter out any role assignments which should not apply to the current
session, as those roles could contain privileges which could be
promoted to global_scope.

The situation this prevents is for users that are allowed to login in
different contexts with different roles in those contexts.  We do not
want the roles provided in one context to provide privileges that
have not been assigned when we are logged-in in a different context.';

revoke all on veil2.session_assignment_contexts from public;
grant select on veil2.session_assignment_contexts to veil_user;


\echo ......all_accessor_roles(function)...
create or replace
function veil2.all_accessor_roles(
   accessor_id in out integer,
   session_context_type_id in integer,
   session_context_id in integer,
   role_id out integer,
   context_type_id out integer,
   context_id out integer)
  returns setof record as
$$
  select -- All roles without filtering if our session context is global
         -- context.
         aar.accessor_id, aar.role_id,
	 aar.context_type_id, aar.context_id
    from veil2.all_accessor_roles_plus aar
   where aar.accessor_id = all_accessor_roles.accessor_id
     and all_accessor_roles.session_context_type_id = 1
   union all
  select -- Globally assigned roles, if our session context is non-global
         -- context.
         aar.accessor_id, aar.role_id,
	 aar.context_type_id, aar.context_id
    from veil2.all_accessor_roles_plus aar
   inner join veil2.session_assignment_contexts sac
      on -- Matching login context and assignment context
         -- Also session_context and assignment context
         aar.context_type_id = 1
      or (    sac.context_type_id = aar.context_type_id
          and sac.context_id = aar.context_id
 	  and aar.context_type_id != 1)
   where aar.accessor_id = all_accessor_roles.accessor_id
     and all_accessor_roles.session_context_type_id != 1
   union all
  select accessor_id, 2, 2, accessor_id;
$$
language sql security definer stable;

Update the in-memory roles and privileges bitmap for a given scope.

The Doxygen documentation for this can be found here.

function veil2.load_and_cache_session_privs()
  returns boolean as
$$
begin
  insert
    into veil2.accessor_privileges_cache
        (accessor_id, login_context_type_id,
         login_context_id, session_context_type_id,
         session_context_id, mapping_context_type_id,
         mapping_context_id, scope_type_id,
         scope_id, roles,
         privs)
  select sc.accessor_id, sc.login_context_type_id,
         sc.login_context_id, sc.session_context_type_id,
         sc.session_context_id, sc.mapping_context_type_id,
         sc.mapping_context_id, p.scope_type_id,
         p.scope_id, p.roles,
         p.privileges
    from veil2.session_context() sc
   cross join (
      select *
        from veil2.session_privileges_v
       order by scope_type_id, scope_id) p
   cross join lateral (
      select veil2.add_session_privileges(p.scope_type_id, p.scope_id,
                                          p.roles, p.privileges)) asp;
  return found;
end;
$$
language plpgsql security definer volatile;

Load the in-memory copy of session privileges from veil2.session_privileges_v and also cache them in veil2.accessor_privileges_cache.

function veil2.load_cached_privs()
  returns boolean as
$$
declare
  _count integer;
begin
  with privs as
    (
      select apc.scope_type_id, apc.scope_id,
      	 apc.roles, apc.privs
        from veil2.session_context() sc
       inner join veil2.accessor_privileges_cache apc
          on apc.accessor_id = sc.accessor_id
         and apc.login_context_type_id = sc.login_context_type_id
         and apc.login_context_id = sc.login_context_id
         and apc.session_context_type_id = sc.session_context_type_id
         and apc.session_context_id = sc.session_context_id
         and apc.mapping_context_type_id = sc.mapping_context_type_id
         and apc.mapping_context_id = sc.mapping_context_id
    ),
  ins as
    (
      select veil2.add_session_privileges(scope_type_id, scope_id,
    	 			          roles, privs)
        from privs
    )
  select count(*)::integer
    into _count
    from ins;
  return _count > 0;
end;
$$
language 'plpgsql' security definer volatile;

Reload cached session privileges for the session's accessor into our current session.

function veil2.update_session(
    _session_id bigint,
    _nonces bitmap,
    _authenticated boolean)
  returns void as
$$
  with recursive sessions as
    (
      select parent_session_id
        from upd_cur_session
       union all
      select s2.parent_session_id
        from sessions s1
       inner join veil2.sessions s2
          on s1.parent_session_id is not null
	 and s2.session_id = s1.parent_session_id
	 and s2.parent_session_id is not null
    ),
  timeout as
    (
      select parameter_value::interval as increment
        from veil2.system_parameters
       where parameter_name = 'shared session timeout'
    ),
  upd_cur_session as
    (
      update veil2.sessions s
	 set expires =
	     case when _authenticated
	       then now() + t.increment
	       else s.expires
	       end,
	     nonces = coalesce(_nonces, nonces),
	     has_authenticated = has_authenticated or _authenticated
        from timeout t
       where s.session_id = _session_id
      returning parent_session_id
    )
  update veil2.sessions s
     set expires =
	   case when _authenticated
	     then now() + t.increment
	     else s.expires
	     end
   from timeout t
  where s.session_id in (select parent_session_id from sessions);
$$
language sql security definer volatile;

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 bigint)
  returns boolean as
$$
begin
  if not veil2.load_cached_privs() then
    if not veil2.load_and_cache_session_privs() then
      return false;
    end if;
  end if;

  if parent_session_id is not null then
    perform veil2.filter_session_privs(parent_session_id);
  end if;
  return true;
end;
$$
language plpgsql security definer volatile;

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 bigint)
  returns bigint as
$$
  select s.parent_session_id
    from veil2.sessions s
   cross join lateral veil2.session_context(
              s.accessor_id, s.session_id,
              s.login_context_type_id, s.login_context_id,
              s.session_context_type_id, s.session_context_id,
              s.mapping_context_type_id, s.mapping_context_id,
              s.parent_session_id)
   where s.session_id = _session_id;
$$
language sql security definer volatile;

(Re)load veil2_session_context for a given session_id.

function veil2.reload_connection_privs()
  returns boolean as
$$
declare
  _parent_session_id bigint;
begin
  perform veil2.reset_session_privs();
  select parent_session_id
    into _parent_session_id
    from veil2.session_context();
  return veil2.load_connection_privs(_parent_session_id);
end;
$$
language plpgsql security definer volatile;

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 bigint,
    nonce in integer,
    authent_token in text,
    success out boolean,
    errmsg out text)
  returns record as
$$
declare
  _accessor_id integer;
  _nonces bitmap;
  nonce_ok boolean;
  _has_authenticated boolean;
  _session_token text;
  _context_type_id integer;
  authent_type text;
  expired boolean;
  parent_session_id integer;
begin
  perform veil2.reset_session();
  success := false;
  select s.accessor_id, s.expires < now(),
         veil2.check_nonce(nonce, s.nonces), s.nonces,
	 s.authent_type,
	 ac.context_type_id, s.has_authenticated,
	 s.token
    into _accessor_id, expired,
         nonce_ok, _nonces,
	 authent_type, _context_type_id, 
	 _has_authenticated, _session_token
    from veil2.sessions s
    left outer join veil2.accessor_contexts ac
      on ac.accessor_id = s.accessor_id
     and ac.context_type_id = s.login_context_type_id
     and ac.context_id = s.login_context_id
   where s.session_id = open_connection.session_id;

  if not found then
    raise warning 'SECURITY: Connection attempt with no session: %',
        session_id;
    errmsg := 'AUTHFAIL';
  elsif _context_type_id is null then
    raise warning 'SECURITY: Connection attempt for invalid context';
    errmsg := 'AUTHFAIL';
  elsif expired then
    errmsg := 'EXPIRED';
  elsif not nonce_ok then
    -- Since this could be the result of an attempt to replay a past
    -- authentication token, we log this failure
    raise warning 'SECURITY: Nonce failure.  Nonce %, Nonces %',
                   nonce, to_array(_nonces);
    errmsg := 'NONCEFAIL';
  else
    success := true;
      
    if _has_authenticated then
      -- The session has already been opened.  From here on we 
      -- use different authentication tokens for each open_connection()
      -- call in order to avoid replay attacks.
      if not veil2.check_continuation(nonce, _session_token,
	       			      authent_token) then
        raise warning 'SECURITY: incorrect continuation token for %, %',
                     _accessor_id, session_id;
        errmsg := 'AUTHFAIL';
	success := false;
      end if;
    else 
      if not veil2.authenticate(_accessor_id, authent_type,
				authent_token) then
        raise warning 'SECURITY: incorrect % authentication token for %, %',
                      authent_type, _accessor_id, session_id;
        errmsg := 'AUTHFAIL';
	success := false;
      end if;
    end if;
  end if;
  
  if success then
    -- Reload session context
    parent_session_id := veil2.reload_session_context(session_id);

    if not veil2.load_connection_privs(parent_session_id) then
      raise warning 'SECURITY: Accessor % has no connect privilege.',
                    _accessor_id;
      errmsg := 'AUTHFAIL';
      success := false;
    end if;
  end if;

  -- Regardless of the success of the preceding checks we record the
  -- use of the latest nonce.  If all validations succeeded, we
  -- extend the expiry time of the session.
  perform veil2.update_session(session_id,
			       veil2.update_nonces(nonce, _nonces),
			       success);
end;
$$
language plpgsql security definer volatile
set client_min_messages = 'error';

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 void
     as '$libdir/veil2', 'veil2_reset_session'
     language C volatile strict security definer;

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 bigint;
  success boolean;
begin
  success := false;
  execute veil2.reset_session();
  
  select accessor_id
    into _accessor_id
    from veil2.accessors
   where username = session_user;
  if found then

    select cas.session_id
      into _session_id
    from veil2.create_accessor_session(
             _accessor_id, 'dedicated',
	     context_type_id, context_id,
	     context_type_id, context_id) cas;

    if veil2.load_cached_privs() then
      success := true;
    else
      success := veil2.load_and_cache_session_privs();
    end if;

    if not success then
      raise exception 'SECURITY: user % has no connect privilege.',
      	    	      session_user;
    else
      -- Update the permanent session record to show that we have
      -- authenticated and give a reasonable expiry time.
      update veil2.sessions
         set expires = now() + '1 day'::interval,
 	    has_authenticated = true
       where session_id = _session_id;
    end if;
  end if;
  return success;
end;
$$
language plpgsql security definer volatile;

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 bigint,
    session_token out text,
    success out boolean,
    errmsg out text)
  returns record as
$$
declare
  orig_session_id bigint;
  orig_accessor_id integer;
  _mapping_context_type_id integer;
  _mapping_context_id integer;
begin
  select sc.session_id, sc.accessor_id
    into orig_session_id, orig_accessor_id
    from veil2.session_context() sc;

  -- We must check that login context is valid for both the current
  -- and target accessors.
  errmsg := veil2.check_become_user_priv('AAA', orig_accessor_id, 
  	        login_context_type_id, login_context_id);

  if errmsg is null then
    errmsg := veil2.check_accessor_context('BBB', orig_accessor_id, 
  	          login_context_type_id, login_context_id);
  end if;

  if errmsg is null then
    errmsg := veil2.check_accessor_context('CCC', accessor_id, 
  	          login_context_type_id, login_context_id);
  end if;
  
  if errmsg is null then
    -- Create new session_context record
    perform veil2.reset_session();

    select *
      into session_id, _mapping_context_type_id,
	   _mapping_context_id
      from veil2.new_session_context(accessor_id,
		     login_context_type_id, login_context_id,
	             session_context_type_id, session_context_id,
		     orig_session_id) x;

    -- Create new session record.
    insert
      into veil2.sessions
          (accessor_id, session_id,
	   login_context_type_id, login_context_id,
	   session_context_type_id, session_context_id,
	   mapping_context_type_id, mapping_context_id,
	   authent_type, has_authenticated,
	   session_supplemental, expires,
	   token, parent_session_id)
    select become_accessor.accessor_id, become_accessor.session_id,
	   become_accessor.login_context_type_id,
	     become_accessor.login_context_id,
	   become_accessor.session_context_type_id,
	     become_accessor.session_context_id,
    	   _mapping_context_type_id,
	     _mapping_context_id,
	   'become', true,
	   null, now() + sp.parameter_value::interval,
	   encode(digest(random()::text || now()::text, 'sha256'),
		  'base64'),
	     orig_session_id
      from veil2.system_parameters sp
     where sp.parameter_name = 'shared session timeout'
    returning token into session_token;

    -- Update expiry of parent session.
    perform veil2.update_session(orig_session_id, null::bitmap, true);

    if not veil2.load_cached_privs() then
      if not veil2.load_and_cache_session_privs() then
        raise warning 'SECURITY: Accessor % has no connect privilege.',
                       _accessor_id;
        errmsg := 'AUTHFAIL';
      end if;
    end if;
  end if;

  if errmsg is null then
    perform veil2.filter_session_privs(orig_session_id);
    success := true;
  else
    success := false;
  end if;
end;
$$
language plpgsql security definer volatile
set client_min_messages = 'error';

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 bigint,
    session_token out text,
    success out boolean,
    errmsg out text)
  returns record as
$$
declare
  _accessor_id integer;
begin
  _accessor_id := veil2.get_accessor(username, login_context_type_id,
				     login_context_id);

  select ba.session_id, ba.session_token,
  	 ba.success, ba.errmsg
    into become_user.session_id, become_user.session_token,
         become_user.success, become_user.errmsg
    from veil2.become_accessor(
             _accessor_id,
	     login_context_type_id, login_context_id,
	     coalesce(session_context_type_id, login_context_type_id), 
	     coalesce(session_context_id, login_context_id)) ba;
end;
$$
language plpgsql security definer volatile;

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();

create trigger authentication_types_biu before insert or update
  on veil2.authentication_types
  for each row execute function veil2.make_user_defined();