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.

Eve is a superuser in corp context for both Secure Corp and Protected Corp. She works for Veil Corp (corp 1000). She authenticates using plaintext. She has the connect role for Veil Corp and Secured Corp. This means she authenticates within Veil Corp but also has the right to connect to Secured Corp.

You can connect as Eve in Veil Corp using this query:

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

Connected to Veil Corp, Eve can only see her own party record.

To connect Eve in the context of Secured Corp you cab do this:

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

Eve now sees all parties in corp 1010 (plus herself). She sees all projects and project assignments within corp 1010.

Eve cannot log in to Protected Corp, even though she has the superuser role because she has not been given the connect role in that context. If you wish, you can verify this using:

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

If you give Eve the connect role for Protected Corp she will then be able to log in and see all parties for Protected Corp:

vpd=# select *
vpd-#  from veil2.create_session('Eve', 'plaintext', 4, 1000, 4, 1020) c
vpd-# cross join veil2.open_connection(c.session_id, 1, 'passwd4') o1;
 session_id |                session_token                 | session_supplemental | success |  errmsg  
------------+----------------------------------------------+----------------------+---------+----------
         28 | v3AvfSi6KQ0h4zhR0Yoha3XZ6JT8WHgVRUqGQAC+4R8= |                      | f       | AUTHFAIL
(1 row)

vpd=# select * from demo.parties;
 party_id | party_type_id | corp_id | org_id | party_name | password 
----------+---------------+---------+--------+------------+----------
(0 rows)

vpd=# insert
vpd-#   into veil2.accessor_roles
vpd-#        (accessor_id, role_id, context_type_id, context_id)
vpd-# values (1110, 0, 4, 1020);
INSERT 0 1
vpd=# select *                   
  from veil2.create_session('Eve', 'plaintext', 4, 1000, 4, 1020) c
 cross join veil2.open_connection(c.session_id, 1, 'passwd4') o1;
 session_id |                session_token                 | session_supplemental | success | errmsg 
------------+----------------------------------------------+----------------------+---------+--------
         29 | fA3tsbnh0nhC9j6KTXSKOEQCgiZNU7//McP7IcxZPVU= |                      | t       | 
(1 row)

vpd=# select * from demo.parties;
 party_id | party_type_id | corp_id | org_id |   party_name   |   password   
----------+---------------+---------+--------+----------------+--------------
     1020 |             2 |    1020 |   1020 | Protected Corp | 
     1100 |             1 |    1020 |   1020 | Carol          | xxxxxxxxxxxx
     1110 |             1 |    1000 |   1000 | Eve            | xxxxxxxxxxxx
     1180 |             2 |    1020 |   1020 | Department P   | 
     1190 |             2 |    1020 |   1020 | Department P2  | 
     1200 |             1 |    1020 |   1020 | Paul           | xxxxxxxxxxxx
     1210 |             1 |    1020 |   1020 | Pippa          | xxxxxxxxxxxx
     1220 |             1 |    1020 |   1020 | Phil           | xxxxxxxxxxxx
     1230 |             1 |    1020 |   1020 | Pete           | xxxxxxxxxxxx
     1240 |             1 |    1020 |   1020 | Pam            | xxxxxxxxxxxx
(10 rows)

vpd=# 
	    

What this shows is that Eve can see all users in both corps, but only for one corp at a time, depending on how she has logged in. Pretty cool, eh?

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.

We can discover the full set of Simon's privileges by selecting from session_privileges. However, this contains bitmaps, which are difficult to read, so it is better to use the helper view session_privileges_info.

vpd=# select * from veil2.session_privileges_info;
scope_type_id | scope_id |   roles    |     privs     
---------------+----------+------------+---------------
             3 |     1010 | {13,14,16} | {}
             2 |     1140 | {2}        | {10,13,21,26}
             1 |        0 | {0}        | {0,20}
             5 |        1 | {10,11,12} | {20,25,26,27}
             4 |     1050 | {}         | {27}
(5 rows)
	    

This shows us that we have:

This tells us which privileges we have in which scopes, as well as which roles we have been assigned, directly or indirectly, in which contexts. What it doesn't tell us is which roles gave us which privileges, and whether roles were assigned directly or indirectly.

We can get more detailed information about privilege assignments from the veil2.privilege_assignments view:

vpd=# select distinct privilege_id as priv_id, ass_cntxt_type_id as cntxt_type,
vpd-#        ass_cntxt_id as cntxt_id, scope_type_id as scope_type,
vpd-#        scope_id, ass_role_id as role_id,
vpd-#        priv_bearing_role_id as priv_role_id,
vpd-#        role_id_mapping
vpd-#   from veil2.privilege_assignments
vpd-#  where accessor_id = 1140 and privilege_id in (21, 27);
 priv_id | cntxt_type | cntxt_id | scope_type | scope_id | role_id | priv_role_id | role_id_mapping 
---------+------------+----------+------------+----------+---------+--------------+-----------------
      21 |          2 |     1140 |          2 |     1140 |       2 |            2 | 2
      27 |          5 |        1 |          4 |     1050 |      10 |           11 | 10->11
(2 rows)
	    

This gives us pretty much the whole story. We get privilege 21 in personal scope from role 2 (personal context assigned in personal context. And privilege 27 in org scope for org 1050, from role 11, mapped from role 10 assigned to us in project scope of project 1.

You are encouraged to become familiar with the developer views. There is more there than this quick tour shows, and they can provide invaluable information to help you debug and understand your security model.