PGXN

Securing a table is pretty simple. First you enable row level security for that table, and then you define policies on the table for select, insert, update and delete. The following is the definition for the select policy on projects from the veil2_demo extension. See STEP 9 in file demo/veil2_demo--0.9.1 (beta).sql.

alter table demo.projects enable row level security;

create policy parties_tbl__select
    on demo.parties_tbl
   for select
 using (   veil2.i_have_global_priv(17)
        or veil2.i_have_priv_in_scope(17, 3, corp_id)
        or veil2.i_have_priv_in_scope(17, 4, org_id)
        or veil2.i_have_priv_in_scope(17, 4, party_id) -- View the org itself
        or veil2.i_have_personal_priv(17, party_id)
	or (    party_type_id = 2    -- View an org that owns a project
	    and veil2.i_have_priv_in_scope(23, 4, party_id)));
      

Note that we check for select projects privilege in many different ways. This is because there are multiple ways that we could have been assigned the privilege. The most interesting test is the last one, which tests for select orgs rather than select parties. This is to allow someone with that privilege to view the party record for the org that is the owner of some other record. In this case, the org will be the owner of a project, and a user assigned select orgs in the context of that project will have that privilege automatically promoted to org scope allowing them to see the party that owns the project, but no other parties.

Each Veil2 table is protected with a select privilege. It is generally assumed that no-one will be granted direct access to Veil2 tables, though this will not be the case if you use Veil2's role implementation as the demo does. In this case you should add whatever extra security policies and privileges you need.

DO NOT GRANT ANY ACCESS OTHER THAN SELECT TO A VEIL2 TABLE WITHOUT FIRST ADDING SUITABLE PRIVILEGES AND SECURITY POLICIES.