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:

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.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.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.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.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.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.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.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.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.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.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.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.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.

Prev     Next
Appendix B. Veil2 Views, Materialized Views and Caches  Home  Appendix D. Veil2 C Implementation