Row-level security

Summary

Use PostgreSQL to construct a user/team system allowing fine-grained access control to entities.

User/team management is built on top of the PostgreSQL roles system.

To simplify the example, entities are just a name (string).

Knowledge tested

To solve this problem, you will use:

• temporary tables
• COPY command
• plpgsql
• declaring types to match tables types
• FOR ... LOOP
• FOUND variable
• IF ... END IF
• EXECUTE and exception handling
• CREATE ROLE
• GRANT
• DDL
• SERIAL type
• PRIMARY KEY
• constraints and REFERENCES
• NOT NULL
• anonymous function blocks
• testing using pgtap
• customized options

Overview

Entities/Type

• Every entity has a type
• Types are also entities with a type "Type"
• Every entity has a string name

Security:

• there are three parts: subject (role), object (entity) and operations (query)
• operations are:
• query: can retrieve the document
• operations can be: true (allow), false (disallow) or unspecified (null)
• We want to be able to specify down to the entity / user level
• User-specified rights override teams rights
• Any user allow overrides other user disallow
• Any team allow overrides other teams disallow
• if no rights are specified for the entity, we default to the entity type rights
• if no rights record are found, we deny access

See rights.pdf for the expected result.

Example data

Look at the data/ directory for example input data.

entities:

• entity name
• entity type name

relationships:

• source entity name (detail)
• relationship description
• target entity name (master)

rights:

• role name
• entity or type name
• query allowed or disallowed (boolean)

roles:

• role name

teams:

• role name
• comma-separated list of roles

TODO

• clone this repository to your computer
• In your postgresql.conf, setup a new customized option var.role_name = 'unknown'
• sql/ddl.sql: DDL for normalized data with the following tables:
• Entity: contains all entities (including types which are also entities)
• RelationshipDescription: contains the description text
• RelationshipInstance: links the source, target entity and relationship description
• Right: refers to the entity id, the role and value for the query operation
• sql/ddl_drop.sql: reverse operation from ddl.sql in the correct order
• should NOT use CASCADE
• sql/import.sql: import script
• create import data tables matching inputs
• use the appropriate COPY command to process the data
• insert bootstrap Type record into the Entity table
• anonymous plpgsql script to turn all loaded data into entities
• sql/view.sql
• EntitySecure: view based on Entity that restricts access to data based on the user role set in var.role_name (a customized setting)
• any other views of the data you need
• pgtap/*.sql: add your own tests
• zip up and send your local git repo by email with a detailed report on how much time it took

Rules and evaluation criteria

• must use database roles (users and teams)
• The test pgtap/complete.sql must pass as-is, without changes to the test.
• The DDL must be normalized and enforce referential constraints and checks
• Use double-quoted names for database entities to allow case-sensitivity
• Table names must be CamelCase
• property/column names must use_underscore
• naming, indentation, documentation, consistency and comments are part of the evaluation
• BONUS: large dataset test / benchmarking / performance optimization

pgtap

Running the test requires pgTAP.

Install pgTAP and pgprove:

sudo pgxn install pgtap
sudo cpan TAP::Parser::SourceHandler::pgTAP

...or on Ubuntu:

sudo apt-get install libtap-parser-sourcehandler-pgtap-perl pgtap

You can then run the test:

$ pg_prove pgtap/complete.sql
pgtap/complete.sql .. ok
All tests successful.
Files=1, Tests=4,  1 wallclock secs ( 0.04 usr  0.00 sys +  0.06 cusr  0.01 csys =  0.11 CPU)
Result: PASS

Hints

For importing, use the:

COPY "destination" FROM filename DELIMITERS ',' CSV

For processing the entities, relationships, rights from imported data to normalized data use a:

FOR variable_of_imported_rowtype IN
	-- select from the imported data
LOOP
	-- see if the type already exist
	-- if it does not exist, create it
	-- insert the updated Entity, RelationshipInstance or Right
END LOOP;

when importing data, also make sure you use TRIM to clean up the inputs.

For roles and teams, use string concatenation to build the appropriate CREATE ROLE and GRANT ... TO.

For EntitySecure, suggest using LEFT OUTER JOIN to other tables.

The list of teams a role is linked to can be obtained with:

	SELECT rolname::text AS name
		FROM pg_auth_members,pg_roles
		WHERE roleid = oid
		AND member IN (
			SELECT oid
				FROM pg_roles
				WHERE rolname = current_setting('var.role_name')
			)

Customized options values can be fetched as follow:

current_setting('var.role_name')