Chapter 20. Exploring The Veil2 Demos

Veil2 is supplied with 2 demo applications, packaged as extensions. These are:

20.1. The veil2_demo Extension

The Veil2 demo exists to demonstrate a Relational Security System, show its capabilities, and provide an example of Veil2 in use. It is a trivial, simple and incomplete example but provides enough of a foundation to explore and understand what Veil2 is capable of.

You are encouraged to dissect, play with, and hack on, the demo. There is much that has not been implemented. Feel free to implement the missing parts.

20.1.1. Installation

The demo can be most easily installed as a package from within postgres:

vpd=# create extension veil2_demo cascade;
CREATE EXTENSION
vpd=# 
	

As an alternative you can run the demo.sql script which can be found as described here, eg

marc:veil2$ psql -d vpd -f /usr/share/postgresql/12/veil2/demo.sql
	

This installs the veil2_demo and runs a simple set of tests on it. Note that in order to run the tests as supplied, the postgres user demouser must be given access as described below

As a final option, if you have cloned the Veil2 source from github you can use make:

marc:veil2$ make demo
	

20.1.1.1. Allowing Access For The Demo User

The Veil2 test script demo/demo_test.sql accesses the test database using demo user demouser, which it assumes is a trusted user (ie no password is needed). Note that this is not a good security practice and is done simply for simplicity and expediency.

If this doesn't work for you, you can create a modified version of the test script to use a more secure authentication method that conforms with your site's policies and requirements. However if you want to simply try the demo in a test or development environment where security is not a concern, you can enable trust-based authentication solely for the demouser role, solely for your demo database.

To allow demouser to access the demo database without needing to provide authentication you will need to add a trust authentication definition to your pg_hba.conf file. This line should look something like this:

local   whateverdbnameyoulike demouser                          trust
          

This defines that local (ie non-networked) connections to the whateverdbnameyoulike database by the demouser user are to be trusted. This line must appear before any local definition for all databases or all users.

20.1.2. Data Model Overview

The diagram above provides a logical view of the Veil2_demo database. The privilege, role, role_privilege and role_role entities are from Veil2, while the rest are created specifically for the application.

Parties may be persons, corporations or organizations, where organizations are subdivisions of corporations. Every party belongs to both an organization and a corporation.

All users are persons. Their access rights are determined by their party_roles, their project_assignments and by their (implied) personal_context role (this relationship is shown in the diagram with an asterisk). These tables and relationships provide the entire relational security model for the veil2_demo application.

Note that role_role has a mapping context relationship back to corporation. This allows each corporation to manage its own independent set of role to role mappings.

20.1.3. The Security Contexts

The demo provides 3 relational contexts:

  • corp context;
  • org context;
  • project context

20.1.3.1. Corp Context

The demo uses the parties table to identify organizations and persons. There is a tree structured organizational hierarchy of organizations. The top-level of each organizational tree is called a corp, or corporation. The corp context applies to all data owned by the corp, and by all organizations within it. Many tables contain a corp_id field as a data denormalization to make privilege testing in corp context easier and faster.

20.1.3.2. Org Context

Within each corp there is a tree of organizational parts, or orgs. Privileges applied in org contexts apply in all subordinate orgs. Technically, there is little to distinguish between corp context and a top-level org context, but corp context provides faster privilege checks in this implementation.

20.1.3.3. Project Context

Projects have their own contexts. Assignment to a project uses a different mechanism from the more explicit role assignments made in the other contexts.

20.1.4. The Organizational Hierarchy

See the above descriptions of corp and org contexts for an introduction to this. Persons work for specific orgs in the org hierarchy. The company they work for provides their login context. There are comments in the demo/demo.sql file that should help it all make sense. Take a look at them.

20.1.5. Exploring It

You can create the demo database by simply creating the extension veil2_demo.

The extension creates an unprivileged demouser (demo user) account, which you should use to explore the demo. Using a privileged account will bypass the security which would be pointless and uninteresting.

20.1.5.1. Users

This section describes a number of the users defined for the demo. These users are mostly assigned the same roles, but they each see different subsets of data. This should be a pretty convincing demonstration of the power of relational security contexts. Note that although the users are connecting in a number of different login contexts, their role mapping contexts are all the same (the global context).

The code that follows uses a shortcut single query authentication process. You should probably not do this in real life. The overhead of an extra database round-trip will, in most cases, be so small that it will not be noticeable, and the ability to subsequently use the two-step authentication protocol as a means to further improve your authentication security is something that you may not want to lose. For more on this see the authentication protocols appendix.

Alice

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

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

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

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

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

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:

  • roles 10, 11, 12 and privileges 20, 25, 26 and 27 in project scope for project 1;
  • role 0 and privileges 0 and 20 in global scope;
  • privilege 27 in org scope for org 1050;
  • role 2 and privileges 10, 13, 21 and 26 in personal scope for accessor 1140 (Simon);
  • roles 13, 14, 16 (with no privileges) in org scope for Secured Corp.

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.

20.2. The veil2_minimal_demo Extension

This aims to be a more complete but less extensive demonstration of Veil2's abilities. You are encouraged to explore this on your own.

20.2.1. Installation

The demo can be most easily installed as a package from within postgres:

vpd=# create extension veil2_minimal_demo cascade;
CREATE EXTENSION
vpd=# 
	

As an alternative you can run the minimal_demo.sql script which can be found as described here, eg

marc:veil2$ psql -d vpd -f /usr/share/postgresql/12/veil2/minimal_demo.sql
	

This installs the veil2_demo and runs a simple set of tests on it. Note that in order to run the tests as supplied, the postgres user demouser must be given access as described below

As a final option, if you have cloned the Veil2 source from github you can use make:

marc:veil2$ make mindemo