Prev  Up  Next
Chapter 15. Integrate And/Or Create Roles (STEP 8)  Home  Chapter 17. Secure Your Views (STEP 10)

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.

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.

Prev  Up  Next
Chapter 15. Integrate And/Or Create Roles (STEP 8)  Home  Chapter 17. Secure Your Views (STEP 10)