PGXN

Alice is a global superuser. She works for Veil Corp (corp 1000). She authenticates using bcrypt. You can connect as Alice using this query:

vpd=# select *
vpd-#  from veil2.create_session('Alice', 'bcrypt', 4, 1000) c
vpd-# cross join veil2.open_connection(c.session_id, 1, 'passwd1');
	    

Because she has global superuser privilege, Alice can see all records.

To see which privileges she has, and how she has arrived at them, run the following query:

vpd=# select *
vpd-#  from veil2.privilege_assignments
vpd-# where accessor_id = 1080;
	    

Bob is a superuser in corp context for Secured Corp. He works for Secured Corp (corp 1010). He authenticates using plaintext. You can connect as Bob using this query:

vpd=# select *
vpd-#  from veil2.create_session('Bob', 'plaintext', 4, 1010) c
vpd-# cross join veil2.open_connection(c.session_id, 1, 'passwd2') o1;
	    

Because he has superuser privilege for Secured Corp, Bob can see all records associated with Secured Corp.

If you compare Bob's privilege_assignments with Alice's, you will see that Bob has all of the same privileges that Alice has, but assigned in different contexts. This is why Bob sees fewer records than Alice.

Carol is a superuser in corp context for Protected Corp. She works for Protected Corp (corp 1020). She authenticates using plaintext. You can connect as Carol using this query:

vpd=# select *
vpd-#  from veil2.create_session('Carol', 'plaintext', 4, 1020) c
vpd-# cross join veil2.open_connection(c.session_id, 1, 'passwd3') o1;
	    

Unlike Bob, Carol can see no projects. This is because Protected Corp has no projects.

Sue is a superuser in org context for Department S. She works for Department S (org 1050). She authenticates using plaintext. You can connect as Sue using this query:

vpd=# select *
  from veil2.create_session('Sue', 'plaintext', 4, 1050) c
 cross join veil2.open_connection(c.session_id, 1, 'passwd5') o1;
	    

Sue sees only those party, party_assignment and project records for Department S. Of particular interest are the project_assignment records for which Sue gets the appropriate privilege in a scope superior to the context of the project itself. Try executing these 2 queries:

vpd=# select veil2.i_have_priv_in_scope(26, 5, 1);
vpd=# select veil2.i_have_priv_in_superior_scope(26, 5, 1);
	    

Simon is a project manager for project S.1. He works for Department S (org 1050). He authenticates using plaintext. You can connect as Simon using this query:

vpd=# select *
vpd-#  from veil2.create_session('Simon', 'plaintext', 4, 1050) c
vpd-# cross join veil2.open_connection(c.session_id, 1, 'passwd7') o1;
	    

Simon's only assigned roles, are connect, assigned in global context in veil2.accessor_roles, and project manager assigned in the context of project 1 in demo.project_assignments.

This limits what Simon can see to data about project 1, and data about himself through privileges assigned in personal context. What is interesting, and cool, is that Simon is able to see the demo.parties record for the org that owns project 1:

vpd=# select * from demo.parties;
 party_id | party_type_id | corp_id | org_id |  party_name  |   password   
----------+---------------+---------+--------+--------------+--------------
     1050 |             2 |    1010 |   1030 | Department S | 
     1140 |             1 |    1010 |   1050 | Simon        | xxxxxxxxxxxx
(2 rows)
vpd=> 
	    

His access to the record for Department S arises from the project viewer role, which has been assigned the select orgs privilege. This privilege allows the privilege holder to select from parties that are organizations (not persons), and when assigned in a lesser context (like project context) promotes to apply in org scope. Simon has the project viewer role indirectly as it has been assigned to the project manager role.

Understanding how a user gains their privileges, and the contexts in which they apply, can be tricky. This is where the developer views, and general postgres expertise come in.

Let's begin by understanding what privileges could be giving us access to demo.parties. The psql \d command can be used to describe a relation:

vpd=# \d demo.parties
                   View "demo.parties"
    Column     |  Type   | Collation | Nullable | Default 
---------------+---------+-----------+----------+---------
 party_id      | integer |           |          | 
 party_type_id | integer |           |          | 
 corp_id       | integer |           |          | 
 org_id        | integer |           |          | 
 party_name    | text    |           |          | 
 password      | text    |           |          | 
	    

Hmmm demo.parties is a view, not a table. It appears, on inspection using \d+, to not have any security restrictions (calls to Veil2 privilege testing functions) on it, so let's take a look at the underlying table: demo.parties_tbl:

vpd=# \d demo.parties_tbl
                 Table "demo.parties_tbl"
    Column     |  Type   | Collation | Nullable | Default 
---------------+---------+-----------+----------+---------
 party_id      | integer |           | not null | 
 party_type_id | integer |           | not null | 
 corp_id       | integer |           | not null | 
 org_id        | integer |           | not null | 
 party_name    | text    |           | not null | 
 password      | text    |           |          | 
Indexes:
    "parties_tbl_pkey" PRIMARY KEY, btree (party_id)
Foreign-key constraints:
    "parties_tbl_corp_id_fkey" FOREIGN KEY (corp_id) REFERENCES demo.parties_tbl(party_id)
    "parties_tbl_org_id_fkey" FOREIGN KEY (org_id) REFERENCES demo.parties_tbl(party_id)
    "parties_tbl_party_type_id_fkey" FOREIGN KEY (party_type_id) REFERENCES demo.party_types(party_type_id)
Referenced by:
    TABLE "veil2.accessor_party_map" CONSTRAINT "accessor_party_map__party_fk" FOREIGN KEY (accessor_id) REFERENCES demo.parties_tbl(party_id)
    TABLE "demo.parties_tbl" CONSTRAINT "parties_tbl_corp_id_fkey" FOREIGN KEY (corp_id) REFERENCES demo.parties_tbl(party_id)
    TABLE "demo.parties_tbl" CONSTRAINT "parties_tbl_org_id_fkey" FOREIGN KEY (org_id) REFERENCES demo.parties_tbl(party_id)
    TABLE "demo.project_assignments" CONSTRAINT "project_assignments_party_id_fkey" FOREIGN KEY (party_id) REFERENCES demo.parties_tbl(party_id)
    TABLE "demo.projects" CONSTRAINT "projects_corp_id_fkey" FOREIGN KEY (corp_id) REFERENCES demo.parties_tbl(party_id)
    TABLE "demo.projects" CONSTRAINT "projects_org_id_fkey" FOREIGN KEY (org_id) REFERENCES demo.parties_tbl(party_id)
    TABLE "veil2.scope_links" CONSTRAINT "scope_link__party_fk" FOREIGN KEY (party_id) REFERENCES demo.parties_tbl(party_id) ON DELETE CASCADE
Policies:
    POLICY "parties_tbl__select" FOR SELECT
      USING ((veil2.i_have_priv_in_scope_or_global(21, 3, corp_id) OR veil2.i_have_priv_in_scope(21, 4, org_id) OR veil2.i_have_priv_in_scope(21, 4, party_id) OR veil2.i_have_personal_priv(21, party_id) OR ((party_type_id = 2) AND veil2.i_have_priv_in_scope(27, 4, party_id))))
Triggers:
    parties_tbl_ait AFTER INSERT ON demo.parties_tbl FOR EACH ROW EXECUTE FUNCTION demo.parties_tbl_ai()
    parties_tbl_aut AFTER UPDATE ON demo.parties_tbl FOR EACH ROW WHEN (new.password <> old.password) EXECUTE FUNCTION demo.parties_tbl_au()
	    

The part of interest to us, is the POLICY stuff. We see that we can be given select access via privilege 21 in various scopes, or 27 in org scope for the party. If we look at the privileges table we can see that these privileges are select parties and select orgs. Note that to view the contents of the veil2.privileges table you will need to connect to a more privileged user as demouser has no select privilege on that table.

So, does Simon have privileges 21 and/or 27? Let's try running the privilege testing functions directly. We can see party records 1140 and 1050. It seems likely that we can see record 1140 because its our record (we are being Simon at this point). And maybe we can see record 1050 because its the owner or project 1, for which we have the project manager role.

vpd=# select veil2.i_have_personal_priv(21, 1140);
 i_have_personal_priv 
----------------------
 t
(1 row)

vpd=# select veil2.i_have_priv_in_scope(27, 4, 1050);
 i_have_priv_in_scope 
----------------------
 t
(1 row)
	    

Well, that appears to explain it but it required a few assumptions about which scopes might apply. Let's try do it again, without those assumptions.