Appendix B. Veil2 Views, Materialized Views and Caches

view veil2.accessor_contexts (
  accessor_id, context_type_id, context_id
) as
select accessor_id, 1, 0
  from veil2.accessors;

This view lists the allowed session (and login, where different) contexts for accessors. The system-provided version of this view may be overridden by providing an equivalent view called veil2.my_accessor_contexts.

When an accessor opens a session, they choose a session context. This session context determines which set of role to role mappings are in play. Typically, there will only be one such set, as provided by the default implementation of this view. If however, your application requires separate contexts to have different role to role mappings, you should modify this view to map your accessors with that context.

Typically this will be used in a situation where your application serves a number of different clients, each of which have their own role definitions. Each accessor will belong to one of those clients and this view should be modified to make that mapping apparent.

A typical view definition might be:

select party_id, 3, client_id
  from app_schema.parties
 union all 
select party_id, 1, 0
  from mycorp_schema.superusers;

which would allow those defined in the superusers table to connect in the global scope, and those defined in the parties table to connect in the context of the client that they work for.

Note that any change to the underlying data of this view (ie one that changes what the view will show) *must* cause a full refresh of all Veil2 materialized views and caches.

view veil2.superior_scopes (
  scope_type_id, scope_id,
  superior_scope_type_id, superior_scope_id
) as
select null::integer, null::integer,
       null::integer, null::integer
where false;

This view identifies superior scopes for determining the scope hierarchy. This is used for determing how to promote privileges when privilege promotion is needed, which happens when a role that is assigned in a restricted security context has privileges that must be applied in a less restricted scope. Note that promotion to global scope is always possible and is not managed through this view.

If you have restricted scopes which are descendant scopes of less restricted ones, and you need privileges assigned in the restricted context to be promoted to the less restricted one, you must override this view to show which scopes may be promoted to which other scopes. For example if you have a corp scope type and a dept scope type which is a sub-scope of it, and your departments table identifies the corp_id for each department, you would define your over-riding view something like this:

create or replace
view veil2.my_superior_scopes (
  scope_type_id, scope_id,
  superior_scope_type_id, superior_scope_id
) as
select 96,   -- dept scope type id
       department_id,
       95,   -- corp scope type id 
       corp_id
  from departments;

Multi-level context promotions (eg to grandparent or great-grandparent scopes) will be handled by veil2.all_superior_scopes which you should have no need to modify.

Note that any change to the underlying data of this view (ie one that changes what the view will show) *must* cause a full refresh of all Veil2 materialized views and caches.

view veil2.all_accessor_roles (
  accessor_id, role_id, context_type_id, context_id
) as
select accessor_id, role_id,
       context_type_id, context_id
  from veil2.accessor_roles;

Provides all of an accessor's explicit role assignments, ie it does not provide the personal context role.

If you have any explicitly assigned roles that are not granted through the veil2.accessor_role table, you must provide your own definition of this view (called veil2.my_all_accessor_roles). For example if you have a project context that is dependent on an accessor being assigned to a project you might redefine the view as follows:

create or replace
view veil2.my_all_accessor_roles (
  accessor_id, role_id, context_type_id, context_id
) as
select accessor_id, role_id,
       context_type_id, context_id
  from veil2.accessor_roles
 union all
select party_id, role_id,
       99,  -- id for project context_type
       project_id
  from project_parties;

Note that any change to the underlying data of this view (ie one that changes what the view will show) *must* cause a full or partial refresh of all Veil2 materialized views and caches.

view veil2.privilege_assignments as
select aar.accessor_id, rp.privilege_id,
       aar.context_type_id as ass_cntxt_type_id,
       aar.context_id as ass_cntxt_id,
       coalesce(p.promotion_scope_type_id,
                aar.context_type_id) as scope_type_id,
       coalesce(asp.superior_scope_id,
	        aar.context_id) as scope_id,
       rc.primary_role_id as ass_role_id,
       rc.assigned_role_id as priv_bearing_role_id,
       rc.id_chain as role_id_mapping,
       rc.name_chain as role_name_mapping,
       rc.context_type_id as map_cntxt_type_id,
       rc.context_id as map_cntxt_id
  from (
    select role_id, privilege_id
      from veil2.role_privileges
    union all
    select 1, privilege_id
      from veil2.privileges
       ) rp
 inner join veil2.privileges p
    on p.privilege_id = rp.privilege_id
 inner join veil2.role_chains rc
    on rc.assigned_role_id = rp.role_id
 inner join veil2.all_accessor_roles_plus aar
    on aar.role_id = rc.primary_role_id
  left outer join veil2.all_superior_scopes asp
    on asp.scope_type_id = aar.context_type_id
   and asp.scope_id = aar.context_id
   and asp.superior_scope_type_id = p.promotion_scope_type_id
   and asp.is_type_promotion;

Developer view that shows how accessors get privileges. It shows the roles that the user is assigned, and the context in which they are assigned, as well as the mappings from role to role to privilege which give that resulting privilege to the accessor.

If you are uncertain how accessor 999 has privilege 333, then simply run:

select * 
  from veil2.privilege_assignments 
 where accessor_id = 999 
   and privilege_id = 333;

view veil2.all_role_privileges_info as
select role_id, mapping_context_type_id,
       mapping_context_id, to_array(roles) as roles,
       to_array(privileges) as privileges
  from veil2.all_role_privileges_v;

Developer view on all_role_privileges showing roles and privileges as arrays of integers for easier comprehension.

view veil2.scope_tree (scope_tree) as
with recursive
top_scopes as
  (
    select distinct
           sp.superior_scope_id as root_scope_id,
	   sp.superior_scope_type_id as root_scope_type_id,
	   st.scope_type_name as root_scope_type_name,
	   st.scope_type_id || ' (' || st.scope_type_name || 
	     ').' || sp.superior_scope_id as root_full_name
      from veil2.superior_scopes sp
     inner join veil2.scope_types st
        on st.scope_type_id = sp.superior_scope_type_id
     where (sp.superior_scope_type_id, sp.superior_scope_id) not in (
        select sp2.scope_type_id, sp2.scope_id
	  from veil2.superior_scopes sp2)
  ),
recursive_part as
  (
    select 1 as depth,
           root_scope_id as scope_id,
	   root_scope_type_id as scope_type_id,
	   root_full_name as full_name,
	   '(' || root_scope_type_id || '.' || root_scope_id || ')' as path,
	   length(root_full_name) as path_length
      from top_scopes
     union all
    select rp.depth + 1,
           sp.scope_id,
	   sp.scope_type_id,
	   st.scope_type_id || ' (' ||
	       st.scope_type_name || ').' || sp.scope_id,
	   rp.path || '(' || sp.scope_type_id || '.' || sp.scope_id || ')',
	   length(st.scope_type_name || '.' || sp.scope_id) + path_length
      from recursive_part rp
     inner join veil2.superior_scopes sp
        on sp.superior_scope_id = rp.scope_id
       and sp.superior_scope_type_id = rp.scope_type_id
     inner join veil2.scope_types st
        on st.scope_type_id = sp.scope_type_id
  )
select format('%' || ((depth * 4) - 2) || 's', '+ ') ||
       full_name
from recursive_part
order by path;

Provides a simple ascii-formatted tree representation of our scope promotions tree. This is an aid to data visualisation for data designers and administrators and is not used elsewhere in Veil2.

create view veil2.promotable_privileges_info (
  scope_type_id, privilege_ids)
as
select scope_type_id, to_array(privilege_ids)
  from veil2.promotable_privileges;

As veil2.promotable_privileges with bitmaps shown as arrays. Info views are intended as developer-readable versions of the non-info views.

view veil2.session_privileges_info as
select *
  from veil2.session_privileges();

Provides a user-readable view of the veil2.session_privileges temporary table.

view veil2.role_chains as
with recursive role_chains
as
  (
    select rr.primary_role_id, rr.assigned_role_id,
    	   rr.primary_role_id::text || '->' ||
	       rr.assigned_role_id::text as id_chain,
	   r1.role_name || '->' || r2.role_name as name_chain,
	   rr.context_type_id,
	   rr.context_id,
	   bitmap(rr.primary_role_id) + rr.assigned_role_id as roles_bitmap
      from veil2.role_roles rr
     inner join veil2.roles r1
        on r1.role_id = rr.primary_role_id
     inner join veil2.roles r2
        on r2.role_id = rr.assigned_role_id
     union all
    select rc.primary_role_id, rr.assigned_role_id,
           rc.id_chain || '->' || rr.assigned_role_id::text,
	   rc.name_chain || '->' || r.role_name,
	   rc.context_type_id,
	   rc.context_id,
	   rc.roles_bitmap + rr.assigned_role_id
      from role_chains rc
     inner join veil2.role_roles rr
        on rr.primary_role_id = rc.assigned_role_id
       and rr.context_type_id = rc.context_type_id
       and rr.context_id = rc.context_id
     inner join veil2.roles r
        on r.role_id = rr.assigned_role_id
     where not rc.roles_bitmap ? rr.assigned_role_id
   ),
  all_contexts as
   (
     select distinct context_type_id, context_id
       from role_chains
   ),
  base_roles as
   (
     select r.role_id as primary_role_id, 
            r.role_id as assigned_role_id, 
            r.role_id::text as id_chain,
            r.role_name as name_chain,
            ac.context_type_id,
            ac.context_id
       from veil2.roles r
      cross join all_contexts ac
   )  
select primary_role_id, assigned_role_id,
       context_type_id,
       context_id, id_chain, name_chain
  from role_chains
 union all
select primary_role_id, assigned_role_id,
       context_type_id,
       context_id, id_chain, name_chain
  from base_roles
order by 3, 4, 1, 2;

This is a developer view. It is intended for development and debugging, and provides a way to view role mappings in a simple but complete way. Try it, it should immediately make sense.

view veil2.all_role_roles (
    primary_role_id, assigned_role_id,
    context_type_id, context_id) as
with recursive assigned_roles (
    primary_role_id, assigned_role_id,
    context_type_id, context_id) as
  (
    -- get all role->role assignments, both direct and indirect, in all contexts
    select primary_role_id, assigned_role_id,
           context_type_id, context_id,
	   bitmap(primary_role_id) + assigned_role_id as roles_encountered
      from veil2.role_roles
     union all
    select ar.primary_role_id, rr.assigned_role_id,
    	   ar.context_type_id, ar.context_id,
	   ar.roles_encountered + rr.assigned_role_id
      from assigned_roles ar
     inner join veil2.role_roles rr
        on rr.primary_role_id = ar.assigned_role_id
       and rr.context_type_id = ar.context_type_id
       and rr.context_id = ar.context_id
       and not ar.roles_encountered ? rr.assigned_role_id
       and rr.primary_role_id != 1 -- Superuser role is handled below
  ),
  superuser_roles (primary_role_id, assigned_role_id) as
  (
    select 1, role_id
      from veil2.roles
     where role_id not in (1, 0)  -- not connect and not superuser
       and not implicit           -- and not implicitly assigned roles
  )
select primary_role_id, assigned_role_id,
       context_type_id, context_id
  from assigned_roles
 union all
select primary_role_id, assigned_role_id,
       null, null
  from superuser_roles;

Show all role to role mappings in all contexts. If the context is null, the mapping applies in all contexts, taking into account role mappings that occur indirectly through other role mappings.

Indirect mappings occur through other mappings (ie mappings are transitive). Eg if a is assigned to b and b to c, then by transitivity a is assigned (indirectly) to c.

Note that the superuser role is implicitly assigned all non-implicit roles except connect.

view veil2.all_superior_scopes_v (
  scope_type_id, scope_id,
  superior_scope_type_id, superior_scope_id,
  is_type_promotion
) as
with recursive recursive_superior_scopes as
  (
    select scope_type_id, scope_id,
           superior_scope_type_id, superior_scope_id,
	   scope_type_id != superior_scope_type_id
      from veil2.superior_scopes
     union
    select rsp.scope_type_id, rsp.scope_id,
           sp.superior_scope_type_id, sp.superior_scope_id,
	   sp.scope_type_id != sp.superior_scope_type_id
      from recursive_superior_scopes rsp
     inner join veil2.superior_scopes sp
        on sp.scope_type_id = rsp.superior_scope_type_id
       and sp.scope_id = rsp.superior_scope_id
     where not (    sp.superior_scope_type_id = rsp.superior_scope_type_id
                and sp.superior_scope_id = rsp.superior_scope_id)
  )
select *
  from recursive_superior_scopes;

This takes the simple user-provided view veil2.superior_scopes and makes it recursive so that if context a contains scope b and scope b contains scope c, then this view will return rows for scope c promoting to both scope b and scope a.

You should not need to modify this view when creating your custom VPD implementation.

Note that for performance reasons a materialized version of this view, veil2.all_superior_scopes, has been created. Any change to the data underlying this view must result in the materialized view being refreshed.

view veil2.all_accessor_roles_plus as
select accessor_id, role_id,
       context_type_id, context_id
  from veil2.all_accessor_roles
 union all
select accessor_id, 2, 2, accessor_id
  from veil2.accessors;

As all_accessor_roles but also providing the implicitly assigned personal context role for each accessor.

view veil2.all_role_privileges_v as
with superuser_privs as
  (
    -- Superuser role has implied assignments of all privileges except
    -- connect.
    select bitmap_of(privilege_id) as privileges
      from veil2.privileges
     where privilege_id != 0  
  )
select r.role_id as role_id,
       rr.context_type_id as mapping_context_type_id,
       rr.context_id as mapping_context_id,
       coalesce(bitmap_of(rr.assigned_role_id) + r.role_id,
                bitmap(r.role_id)) as roles,
       case
       when r.role_id = 1 then (select privileges from superuser_privs)
       else coalesce(bitmap_of(rp.privilege_id),
		     bitmap())
       end as privileges
  from veil2.roles r
  left outer join veil2.all_role_roles rr
    on rr.primary_role_id = r.role_id 
  left join veil2.role_privileges rp
    on rp.role_id = r.role_id
    or rp.role_id = rr.assigned_role_id
 group by r.role_id, rr.context_type_id,
       rr.context_id;

Provides all role to role mappings, with their resulting privileges in all mapping contexts. If the mapping context is null, the mapping applies in all mapping contexts.

For performance reasons a materialized view veil2.all_role_privileges, has been created. This must be refreshed whenever data underlying this view is updated.

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;

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.

create view veil2.promotable_privileges (
  scope_type_id, privilege_ids)
as
select p.promotion_scope_type_id, bitmap_of(p.privilege_id)
  from veil2.privileges p
 where p.promotion_scope_type_id is not null
group by p.promotion_scope_type_id;

Provide bitmaps of those privileges that may be promoted, mapped to the context types to which they should promote.

view veil2.session_privileges_v as
  with session_context as
    (
      select *
        from veil2.session_context() sc
    ),
  base_accessor_privs as
    (
      select barp.accessor_id, barp.role_id,
      	     barp.assignment_context_type_id,
                 barp.assignment_context_id,
             barp.mapping_context_type_id, barp.mapping_context_id,
	     barp.roles, barp.privileges
        from session_context sc
       -- Making the following an inner join (rather than cross-join)
       -- even though the condition is redundant improves performance
       -- (Postgres 12.5).
       inner join veil2.base_accessor_roleprivs(
       	     	            sc.accessor_id, sc.session_context_type_id,
			    sc.session_context_id, sc.mapping_context_type_id,
			    sc.mapping_context_id) barp
          on barp.accessor_id = sc.accessor_id
    ),
  promoted_privs as
    (
      select bap.accessor_id, bap.role_id,
      	     bap.mapping_context_type_id, bap.mapping_context_id,
	     pp.scope_type_id, ss.superior_scope_id as scope_id,
  	     bap.privileges * pp.privilege_ids as privileges
        from base_accessor_privs bap
       inner join veil2.promotable_privileges pp
          on not is_empty(bap.privileges * pp.privilege_ids)
         and pp.scope_type_id != 1
       inner join veil2.all_superior_scopes ss
          on ss.scope_type_id = bap.assignment_context_type_id
         and ss.scope_id = bap.assignment_context_id
         and ss.superior_scope_type_id = pp.scope_type_id
	 and ss.is_type_promotion
    ),
  global_privs as
    (
      select bap.accessor_id, bap.role_id,
      	     bap.mapping_context_type_id, bap.mapping_context_id,
	     pp.scope_type_id, 0 as scope_id,
  	     bap.privileges * pp.privilege_ids as privileges
        from base_accessor_privs bap
       inner join veil2.promotable_privileges pp
          on not is_empty(bap.privileges * pp.privilege_ids)
         and pp.scope_type_id = 1
    ),  
  all_role_privs as
    (
      select accessor_id, 
      	     mapping_context_type_id, mapping_context_id,
  	     assignment_context_type_id as scope_type_id,
             assignment_context_id as scope_id,
             roles + role_id as roles,  privileges
        from base_accessor_privs
       union all
      select accessor_id, 
             mapping_context_type_id, mapping_context_id,
  	     scope_type_id, scope_id,
             bitmap() as roles, privileges
        from promoted_privs
       union all
      select accessor_id, 
             mapping_context_type_id, mapping_context_id,
  	     scope_type_id, scope_id,
             bitmap() as roles, privileges
        from global_privs
    ),
  grouped_role_privs as
    (
      select accessor_id,
             scope_type_id, scope_id,
             union_of(roles) as roles, union_of(privileges) as privileges
        from all_role_privs
       group by accessor_id,
                scope_type_id, scope_id
    ),
  have_global_connect as
    (
      select exists (
          select null
            from grouped_role_privs
           where scope_type_id = 1
             and scope_id = 0
             and privileges ? 0) as have_global_connect
    ),
  have_session_connect as
    (
      select exists (
    	  select null
    	    from session_context sc
    	   cross join grouped_role_privs grp
    	   where grp.privileges ? 0
	     and (   (    grp.scope_type_id = sc.session_context_type_id
    	              and grp.scope_id = sc.session_context_id)
    	          or (grp.scope_type_id, grp.scope_id) in (
    	      select ass.superior_scope_type_id, ass.superior_scope_id
    		from veil2.all_superior_scopes ass
    	       where ass.scope_type_id = sc.session_context_type_id
    	         and ass.scope_id = sc.session_context_id)))
             as have_session_connect
    ),
  have_login_connect as
    (
      select exists (
    	  select null
    	    from session_context sc
    	   cross join grouped_role_privs grp
    	   where grp.privileges ? 0
	     and (   (    grp.scope_type_id = sc.login_context_type_id
    	              and grp.scope_id = sc.login_context_id)
    	          or (grp.scope_type_id, grp.scope_id) in (
    	      select ass.superior_scope_type_id, ass.superior_scope_id
    		from veil2.all_superior_scopes ass
    	       where ass.scope_type_id = sc.login_context_type_id
    	         and ass.scope_id = sc.login_context_id)))
             as have_login_connect
    ),
  have_connect as
    (
      select true as have_connect
        from have_global_connect
       where have_global_connect
       union
      select have_login_connect and have_session_connect
        from have_global_connect
       cross join have_login_connect
       cross join have_session_connect
       where not have_global_connect
    )
  select scope_type_id, scope_id,
         roles, privileges
    from grouped_role_privs
   where exists (select null from have_connect where have_connect);

View used to dynamically figure out the roles and privileges in all contexts for the current session. If the accessor for the session does not have connect privilege in both the authentication and login contexts, then no rows are returned.

This view is used as the basis for loading session privileges into the veil2_session_privileges temporary table and into the veil2.accessor_privileges_cache table. Note that the cache table is used in preference to querying from this view if it has records for the session's accessor and session context.

materialized view veil2.all_superior_scopes
as select * from veil2.all_superior_scopes_v;

This is a materialized view on veil2.all_superior_scopes_v. It exists in order to improve the performance of veil2.session_privileges_v.

It must be fully refreshed whenever the underlying data for veil2.superior_scopes is updated.

Materialized view on veil2.all_role_privileges_v. This exists to improve the performance of veil2.session_privileges_v.

Any time that the data underlying all_role_privileges_v is modified, this materialized view should be refreshed.

create unlogged table veil2.accessor_privileges_cache (
  accessor_id			integer not null,
  login_context_type_id		integer not null,
  login_context_id		integer not null,
  session_context_type_id       integer not null,
  session_context_id		integer not null,
  mapping_context_type_id	integer not null,
  mapping_context_id		integer not null,
  scope_type_id			integer not null,
  scope_id			integer not null,
  roles                         bitmap not null,
  privs				bitmap not null
);

alter table veil2.accessor_privileges_cache enable row level security;

Table used to cache accessor_privileges returned by veil2.session_privileges_v.

This is automatically populated by the Veil2 session management functions for any combination of accessor and session context for which it contains no data.

It should be truncated whenever any underlying role, privilege or context data is updated, and records for individual accessors should be deleted whenever their role assignments are updated.

view veil2.docs(file, purpose) as
values (veil2.docpath() || '/html/index.html',
        'Complete html documentation for Veil2');

Show where local Veil2 documentation can be found.

view veil2.sql_files(file, purpose) as
values (veil2.datapath() || '/demo.sql',
       'Install demo and run test'),
       (veil2.datapath() || '/demo_test.sql',
       'Run simple tests against demo'),
       (veil2.datapath() || '/demo_bulk_data.sql',
       'Install some bulk role and priv data'),
       (veil2.datapath() || '/perf.sql',
       'Run session management performance check'),
       (veil2.datapath() || '/veil2_demo--0.9.1.sql',
       'Veil2 demo creation script'),
       (veil2.datapath() || '/veil2_minimal_demo.sql',
       'Veil2 minimal-demo creation script'),
       (veil2.datapath() || '/veil2_template.sql',
       'Veil2 implementation template.'),
       (veil2.datapath() || '/veil2--0.9.1.sql',
       'Veil2 extension creation script.');

Show where copies of useful Veil2 sql files can be found.