Chapter 16. Secure Your Tables (STEP 9)

At last we get to secure something.

Each of your user-facing relations should be secured. Even if you think that *all* users should have access to something, it does little harm to secure it. It makes for a more consistent implementation and it shows that the relation has not simply been forgotten. The overhead of doing this is small.

16.1. Doing It

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.

Note that veil2_demo only implements the select policy. For more complete policy implementations you can take a look at the veil2_minimal_demo extension.

16.2. The Veil2 tables

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.