Using views for access control in PostgreSQL

Question

I have a schema of tables whose contents basically boil down to:

• A set of users
• A set of object groups
• An access control list (acl) indicating what users have access to what groups
• A set of objects, each of which belongs to exactly one group.

I want to create a simple application that supports access control. I'm thinking views would be a good approach here.

Suppose I have the following database initialization:

/* Database definition */

BEGIN;

CREATE SCHEMA foo;

CREATE TABLE foo.users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE foo.groups (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE foo.acl (
    user_ INT REFERENCES foo.users,
    group_ INT REFERENCES foo.groups
);

CREATE TABLE foo.objects (
    id SERIAL PRIMARY KEY,
    group_ INT REFERENCES foo.groups,
    name TEXT,
    data TEXT
);

/* Sample data */

-- Create groups A and B
INSERT INTO foo.groups VALUES (1, 'A');
INSERT INTO foo.groups VALUES (2, 'B');

-- Create objects belonging to group A
INSERT INTO foo.objects VALUES (1, 1, 'object in A', 'apples');
INSERT INTO foo.objects VALUES (2, 1, 'another object in A', 'asparagus');

-- Create objects belonging to group B
INSERT INTO foo.objects VALUES (3, 2, 'object in B', 'bananas');
INSERT INTO foo.objects VALUES (4, 2, 'object in B', 'blueberries');

-- Create users
INSERT INTO foo.users VALUES (1, 'alice');
INSERT INTO foo.users VALUES (2, 'amy');
INSERT INTO foo.users VALUES (3, 'billy');
INSERT INTO foo.users VALUES (4, 'bob');
INSERT INTO foo.users VALUES (5, 'caitlin');
INSERT INTO foo.users VALUES (6, 'charlie');

-- alice and amy can access group A
INSERT INTO foo.acl VALUES (1, 1);
INSERT INTO foo.acl VALUES (2, 1);

-- billy and bob can access group B
INSERT INTO foo.acl VALUES (3, 2);
INSERT INTO foo.acl VALUES (4, 2);

-- caitlin and charlie can access groups A and B
INSERT INTO foo.acl VALUES (5, 1);
INSERT INTO foo.acl VALUES (5, 2);
INSERT INTO foo.acl VALUES (6, 1);
INSERT INTO foo.acl VALUES (6, 2);

COMMIT;

My idea is to use views that mirror the database, but restrict content to only that which the current user (ascertained by my PHP script) may access (here I'll just use the user 'bob'). Suppose I run this at the beginning of every PostgreSQL session (meaning every time someone accesses a page on my site):

BEGIN;

CREATE TEMPORARY VIEW users AS
SELECT * FROM foo.users
WHERE name='bob';

CREATE TEMPORARY VIEW acl AS
SELECT acl.* FROM foo.acl, users
WHERE acl.user_=users.id;

CREATE TEMPORARY VIEW groups AS
SELECT groups.* FROM foo.groups, acl
WHERE groups.id=acl.group_;

CREATE TEMPORARY VIEW objects AS
SELECT objects.* FROM foo.objects, groups
WHERE objects.group_=groups.id;

COMMIT;

My question is, is this a good approach? Do these CREATE TEMPORARY VIEW statements produce significant overhead, especially compared to a couple simple queries?

Also, is there a way to make these views permanent in my database definition, then bind a value to the user name per session? This way, it doesn't have to create all these views every time a user loads a page.

Answer 1

Several problems with this approach:

1. One user web session is not the same thing as one database session. Multiple users with with sort of setup would fail instantly.

2. Management overhead creating/destroying the views.

Instead, I would recommend something like the following view:

CREATE VIEW AllowedObjects
SELECT objects.*, users.name AS alloweduser
FROM objects
   INNER JOIN groups ON groups.id = objects.group_
   INNER JOIN acl ON acl.group_ = groups.id
   INNER JOIN users ON users.id = acl.user_

Then, everywhere you select objects:

SELECT * FROM AllowedObjects
WHERE alloweduser='Bob'

This assumes Bob can only have one ACL joining him to a particular group, otherwise a DISTINCT would be necessary.

This could be abstracted to a slightly less complex view that could be used to make it easier to check permissions for UPDATE and DELETE:

CREATE VIEW AllowedUserGroup
SELECT groups.id AS allowedgroup, users.name AS alloweduser
FROM groups
   INNER JOIN acl ON acl.group_ = groups.id
   INNER JOIN users ON users.id = acl.user_

This provides a flattened view of which users are in which groups, which you can check against the objects table during an UPDATE/DELETE:

UPDATE objects SET foo='bar' WHERE id=42 AND EXISTS
(SELECT NULL FROM AllowedUserGroup 
 WHERE alloweduser='Bob' AND allowedgroup = objects.group_)