The Code
veil.veil_demo_init(performing_reset bool)
This function is called at the start of each session, and whenever veil_perform_reset() is called. The parameter, doing_reset, is false when called to initialise a session and true when called from veil_perform_reset(). It is registered with veil_init(doing_reset bool) through the veil.veil_demo_init_fns table which is created as an inherited table of veil.veil_init_fns. By registering the initialisation functions using a veil_demo-specific inherited table, when the veil_demo extension is dropped, so is the registration data for veil.veil_demo_init(performing_reset bool).
function veil.veil_demo_init(doing_reset bool) returns bool as '
declare
exists_privs_range bool;
exists_roles_range bool;
exists_role_privs bool;
exists_det_types_range bool;
exists_det_types_privs bool;
init_reqd bool;
dummy bool;
dummy2 bool;
dummyint int4;
_count int4;
begin
-- Declare all shared variables.
select into exists_privs_range, exists_roles_range,
exists_role_privs, exists_det_types_range,
exists_det_types_privs
veil.share(''privs_range''), veil.share(''roles_range''),
veil.share(''role_privs''), veil.share(''det_types_range''),
veil.share(''det_types_privs'');
init_reqd = not (exists_privs_range and exists_role_privs and
The first task of veil_init() is to declare a set of Veil shared variables. This is done by calling share(name text). This function returns true if the variable already exists, and creates the variable and returns false, if not.
These variables are defined as shared because they will be identical for each session. Making them shared means that only one session has to deal with the overhead of their initialisation.
init_reqd = not (exists_privs_range and exists_role_privs and
exists_role_privs and exists_det_types_range and
exists_det_types_privs);
if init_reqd or doing_reset then
-- Load ranges for privs and roles.
select into dummyint
veil.init_range(''roles_range'', min(role_id), max(role_id))
from hidden.roles;
select into dummyint
veil.init_range(''privs_range'', min(privilege_id),
max(privilege_id))
from hidden.privileges;
-- Load range for detail_types
select into dummyint
veil.init_range(''det_types_range'',
min(detail_type_id), max(detail_type_id))
from hidden.detail_types;
-- Initialise array of required privs for detail_types
select into dummy
veil.init_int4array(''det_types_privs'', ''det_types_range'');
select into _count
count(veil.int4array_set(''det_types_privs'',
detail_type_id, required_privilege_id))
from hidden.detail_types;
-- Initialise role_privs bitmap_array
select into dummy
veil.init_bitmap_array(''role_privs'', ''roles_range'',
''privs_range'');
-- Populate role_privs bitmap_array
select into _count
count(veil.bitmap_array_setbit(''role_privs'',
role_id, privilege_id))
from hidden.role_privileges;
end if;
We then check whether the shared variables must be initialised. We will initialise them if they have not already been initialised by another session, or if we are performing a reset (see veil_perform_reset()).
Each variable is initialised in its own way.
Ranges are set by a single call to init_range(name text, min int4, max int4). Ranges are used to create bitmap and array types of a suitable size.
Int4Arrays are used to record mappings of one integer to another. In the demo, they are used to record the mapping of detail_type_id to required_privilege_id. We use this variable so that we can look-up the privilege required to access a given project_detail or person_detail without having to explicitly fetch from attribute_detail_types.
Int4Arrays are initialised by a call to init_int4array(arrayname text, range text), and are populated by calling int4array_set(arrayname text, idx int4, value int4) for each value to be recorded. Note that rather than using a cursor to loop through each detail_type record, we use select count(). This requires less code and has the same effect.
We use a BitmapArray to record the set of privileges for each role. Its initialisation and population is handled in much the same way as described above for Int4Arrays, using the functions init_bitmap_array(bmarray text, array_range text, bitmap_range text) and bitmap_array_setbit(bmarray text, arr_idx int4, bitno int4).
-- Declare important session variables, so that we do not
-- get odd, undefined variable, error messages.
select into dummyint, dummy, dummy2
veil.int4_set(''person_id'', null),
veil.init_bitmap(''global_context'', ''privs_range''),
veil.init_bitmap_hash(''project_context'', ''privs_range'');
return true;
end;
' language plpgsql volatile security definer;
The final section of code defines and initialises a set of session variables. These are defined here to avoid getting undefined variable errors from any access function that may be called before an authenticated connection has been established.
Note that this and all Veil related functions are defined with security definer attributes. This means that the function will be executed with the privileges of the function's owner, rather than those of the invoker. This is absolutely critical as the invoker must have no privileges on the base objects, or on the raw Veil functions themselves. The only access to objects protected by Veil must be through user-defined functions and views.
connect_person(_person_id int4)
This function is used to establish a connection from a specific person. In a real application this function would be provided with some form of authentication token for the user. For the sake of simplicity the demo allows unauthenticated connection requests.
function connect_person(_person_id int4) returns bool as '
declare
dummy int4;
_connect bool;
proj_roles record;
last_proj int4;
first_rec bool;
begin
-- In reality this function would require some authentication token such
-- as a password. This is just a dumb demo version.
select into _connect disconnect_person();
-- Test whether provided person exists. This is where we would, in a
-- real version, do proper authentication.
select into dummy 1
from hidden.persons
where person_id = _person_id;
if found then
-- The person exists and passes authentication
-- From the persons roles set the global_context bitmap.
select into dummy
count(veil.union_from_bitmap_array(''global_context'',
''role_privs'', role_id))
from hidden.person_roles
where person_id = _person_id;
-- Check that user has can_connect privilege
select into _connect
veil.bitmap_testbit(''global_context'', 10100);
if not _connect then
select into _connect disconnect_person();
return false;
end if;
-- From the persons assignments set the project_context bitmap hash.
select into dummy
count(veil.union_into_bitmap_hash(''project_context'',
project_id::text,
veil.bitmap_from_array(''scratch_bitmap'',
''role_privs'', role_id)))
from hidden.assignments
where person_id = _person_id;
-- Finally, record the person_id for the connection.
select into dummy veil.int4_set(''person_id'', _person_id);
return true;
else
return false;
end if;
end;
' language plpgsql volatile security definer;
This function identifies the user, ensures that they have can_connect privilege. It initialises the global_context bitmap to contain the union of all privileges for each role the person is assigned through person_roles. It also sets up a bitmap hash containing a bitmap of privileges for each project to which the person is assigned.
i_have_global_priv(priv_id int4)
This function is used to determine whether a user has a specified privilege in the global context. It tests that the user is connected using int4_get(name text), and then checks whether the specified privilege is present in the global_context bitmap.
function i_have_global_priv(priv_id int4) returns bool as '
declare
connection_id int4;
result bool;
begin
select into connection_id, result
veil.int4_get(''person_id''),
veil.bitmap_testbit(''global_context'', priv_id);
if connection_id is null then
return false;
else
return result;
end if;
end;
' language plpgsql volatile security definer;
The following example shows this function in use by the secured view, privileges:
create view privileges(
privilege_id,
privilege_name) as
select privilege_id,
privilege_name
from hidden.privileges
where i_have_global_priv(10001);
create rule ii_privileges as
on insert to privileges
do instead
insert into hidden.privileges
(privilege_id, privilege_name)
select new.privilege_id, new.privilege_name
where i_have_global_priv(10002);
create rule iu_privileges as
on update to privileges
do instead
update hidden.privileges
set privilege_name = new.privilege_name,
privilege_id = new.privilege_id
where privilege_id = old.privilege_id
and i_have_global_priv(10003);
create rule id_privileges as
on delete to privileges
do instead
delete from hidden.privileges
where privilege_id = old.privilege_id
and i_have_global_priv(10004);
grant select, insert, update, delete on privileges to public;
The privileges used above are select_privileges (10001), insert_privileges (10002), update_privileges (10003), and delete_privileges (10004).
i_have_personal_priv(priv_id int4, person_id int4)
This function determines whether a user has a specified privilege to a specified user's data, in the global or personal contexts. It performs the same tests as for i_have_global_priv(priv_id int4). If the user does not have access in the global context, and the connected user is the same user as the owner of the data we are looking at, then we test whether the specified privilege exists in the role_privs bitmap array for the Personal Context role.
function i_have_personal_priv(priv_id int4, person_id int4) returns bool as '
declare
connection_id int4;
result bool;
begin
select into connection_id, result
veil.int4_get(''person_id''),
veil.bitmap_testbit(''global_context'', priv_id);
if connection_id is null then
-- No-one is connected
return false;
else
if result then
-- We have the required privilege in global context. No need
-- to check any further
return true;
else
if person_id = connection_id then
-- We are connected as the owner of this record. Check
-- whether we have the required privilege in personal
-- context.
select into result
veil.bitmap_array_testbit(''role_privs'',
11002, priv_id);
return result;
else
-- We have no personal context rights to this record
return false;
end if;
end if;
end if;
end;
' language plpgsql volatile security definer;
Here is an example of this function in use from the persons secured view:
create view persons(
person_id,
person_name) as
select person_id,
person_name
from hidden.persons
where i_have_personal_priv(10013, person_id);
create rule ii_persons as
on insert to persons
do instead
insert into hidden.persons
(person_id, person_name)
select new.person_id, new.person_name
where i_have_personal_priv(10014, new.person_id);
create rule iu_persons as
on update to persons
do instead
update hidden.persons
set person_id = new.person_id,
person_name = new.person_name
where person_id = old.person_id
and i_have_personal_priv(10015, old.person_id);
create rule id_persons as
on delete to persons
do instead
delete from hidden.persons
where person_id = old.person_id
and i_have_personal_priv(10016, old.person_id);
grant select, insert, update, delete on persons to public;
i_have_project_priv(priv_id int4, project_id int4)
This function determines whether a user has a specified privilege in the global or project contexts. If the user does not have the global privilege, we check whether they have the privilege defined in the project_context BitmapHash.
function i_have_project_priv(priv_id int4, project_id int4) returns bool as '
declare
connection_id int4;
result bool;
begin
select into connection_id, result
veil.int4_get(''person_id''),
veil.bitmap_testbit(''global_context'', priv_id);
if connection_id is null then
-- No-one is connected
return false;
else
if result then
-- We have the required privilege in global context. No need
-- to check any further
return true;
else
select into result
veil.bitmap_hash_testbit(''project_context'',
project_id::text, priv_id);
return result;
end if;
end if;
end;
' language plpgsql volatile security definer;
Here is an example of this function in use from the instead-of insert trigger for the projects secured view:
create rule ii_projects as
on insert to projects
do instead
insert into hidden.projects
(project_id, project_name)
select new.project_id, new.project_name
where i_have_project_priv(10018, new.project_id);
i_have_proj_or_pers_priv(priv_id int4, project_id int4, person_id int4)
This function checks all privileges. It starts with the cheapest check first, and short-circuits as soon as a privilege is found.
function i_have_proj_or_pers_priv(
priv_id int4, project_id int4, person_id int4) returns bool as '
declare
connection_id int4;
result bool;
begin
select into connection_id, result
veil.int4_get(''person_id''),
veil.bitmap_testbit(''global_context'', priv_id);
if connection_id is null then
-- No-one is connected
return false;
else
if result then
-- We have the required privilege in global context. No need
-- to check any further
return true;
else
if person_id = connection_id then
-- We are connected as the owner of this record. Check
-- whether we have the required privilege in personal
-- context.
select into result
veil.bitmap_array_testbit(''role_privs'',
11002, priv_id);
return result;
end if;
select into result
veil.bitmap_hash_testbit(''project_context'',
project_id::text, priv_id);
return result;
-- We have no personal context rights to this record
-- so check project context
return false;
end if;
end if;
end;
' language plpgsql volatile security definer;
Here is an example of this function in use from the instead-of update trigger for the assignments secured view:
create rule ii_assignments as
on insert to assignments
do instead
insert into hidden.assignments
(project_id, person_id, role_id)
select new.project_id, new.person_id, new.role_id
where i_have_proj_or_pers_priv(10026, new.project_id, new.person_id);
create rule iu_assignments as
on update to assignments
do instead
update hidden.assignments
set project_id = new.project_id,
person_id = new.person_id,
role_id = new.person_id
where project_id = old.project_id
and person_id = old.person_id
and i_have_proj_or_pers_priv(10027, old.project_id, old.person_id);
i_have_person_detail_priv(detail_id int4, person_id int4)
This function is used to determine which types of person details are accessible to each user. This provides distinct access controls to each attribute that may be recorded for a person.
function i_have_person_detail_priv(detail_id int4, person_id int4) returns bool as '
declare
result bool;
begin
select into result
i_have_personal_priv(veil.int4array_get(''det_types_privs'', detail_id),
person_id);
return result;
end;
' language plpgsql volatile security definer;
The function is shown in use, below, in the instead-of delete trigger for person_details. Note that two distinct access functions are being used here.
create rule id_person_details as
on delete to person_details
do instead
delete from hidden.person_details
where person_id = old.person_id
and detail_type_id = old.detail_type_id
and i_have_personal_priv(10040, old.person_id)
and i_have_person_detail_priv(old.detail_type_id, old.person_id);
Next: Removing The Demo Database
1.8.13