Establishing whether an accessor has a given privilege in a given scope is a complex operation. Privileges are assigned via roles, which may be assigned by other roles. Furthermore the contexts in which those role assignments occur, and the context of the role to role mappings are largely user-defined. To deal with both the complexity, and the need to allow user-customization, we determine an accessor's privileges through views.
For performance reasons, some of these views are materialized. Where materialized views alone are not enough we use cache tables which are similar in nature to materialized views but:
- are implemented manually;
- can be refreshed incrementally;
- can be indexed.
The (clickable) diagram above shows the relationship of views
with each other and with
Veil2, and other,
tables and functions.
The bottom-most view in the diagram,
provides the data that determines an accessors privileges. This
view is used directly by the function
to load the set of applicable roles and privileges, in all
applicable contexts for an accessor's session. The performance
of this view is critical, and has been carefully developed and
optimised. Even so,
management functions will usually load this data from the
A few of the views shown do not contribute to
session_privileges_v. These are developer
views, aimed at providing data for development and debugging.
Although all of
Veil2's views may be
replaced by user-supplied versions, the following views are
expected to be. These views are expected to directly query
user-provided tables which will be part of the set of data
being protected by
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.
These views do not directly contribute to
Veil2 functionality. They exist to help
developers in visuallizing and debugging data.
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.
These views are fundamental to the proper operation of
Veil2. You may provide your own versions
of these views if needed, but you should be aware that future
Veil2 may redefine and
redevelop these views. Although your user-supplied views will
take precedence, you run the risk of missing important
improvements to performance and functionality, and more
importantly, if the view semantics change, you risk
breaking assumptions made by the
developers, which could have unforeseen consequences.
That said, if you need to create a user-supplied version of
one of these views, you are free to do so. If so, you should
carefully review any revisions to
before applying the extension upgrade. You are also advised
to discuss your needs with the
developers. Contact links can be found here.
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 select login_context_type_id as context_type_id, login_context_id as context_id from veil2.session_context() sc union select session_context_type_id as context_type_id, session_context_id as context_id from veil2.session_context() sc union select ass.superior_scope_type_id, ass.superior_scope_id from veil2.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 veil2.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 veil2.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.
Materized views and caches are used to improve query performance. The trade-off for improved performance is that they need to be managed: whenever the data on which they depend is modified, they must be refreshed.
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 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.