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, session_privileges_v
provides the data that determines an accessors privileges. This
view is used directly by the function load_session_privs
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, Veil2's session
management functions will usually load this data from the accessor_privileges_cache
table.
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 Veil2.
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
versions of 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 Veil2
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 Veil2
before applying the extension upgrade. You are also advised
to discuss your needs with the Veil2
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.