How to implement business logic permissions in PostgreSQL (or SQL in general)?

Question

Let's assume I have a table of items:

CREATE TABLE items
(
    item serial PRIMARY KEY,
    ...
);

Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking about database access permissions here, but business logic permissions for that item). Each item has default permissions and also per-user permissions that may override default permissions.

I tried to think of several ways to implement this and came up with the following solutions, but I'm not sure on which is the best and why:

1) The Boolean Solution

Use a boolean column for each permission:

CREATE TABLE items
(
    item serial PRIMARY KEY,

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),

    PRIMARY KEY(item, user),

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

Advantages: Each permission is named.

Disadvantages: There are dozens of permissions which increases the number of columns significantly and you have to define them twice (once in each table).

2) The Integer Solution

Use an integer and treat it as a bitfield (i.e. bit 0 is for can_change_description, bit 1 is for can_change_price, and so on, and use bitwise operations to set or read permissions).

CREATE DOMAIN permissions AS integer;

Advantages: very fast.

Disadvantages: You have to keep track of which bit stands for which permission in both the database and the front-end interface.

3) The Bitfield Solution

Same as 2), but use bit(n). Most likely the same advantages and disadvantages, maybe slightly slower.

4) The Enum Solution

Use an enum type for the permissions:

CREATE TYPE permission AS ENUM ('can_change_description', 'can_change_price', .....);

and then create an extra table for default permissions:

CREATE TABLE item_default_permissions
(
    item int NOT NULL REFERENCES items(item),
    perm permission NOT NULL,

    PRIMARY KEY(item, perm)
);

and change the per-user definition table to:

CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),
    perm permission NOT NULL,

    PRIMARY KEY(item, user, perm)    
);

Advantages: Easy to name individual permissions (you don't have to handle bit positions).

Disadvantages: Even when just retrieving the default permissions, it requires accessing two additional tables: first, the default permissions table, and second, the system catalog storing the enum values.

Especially because the default permissions must be retrieved for every single page view of that item, the performance impact of the last alternative might be significant.

5) The Enum Array Solution

Same as 4), but use an array to hold all the (default) permissions:

CREATE TYPE permission AS ENUM ('can_change_description', 'can_change_price', .....);

CREATE TABLE items
(
    item serial PRIMARY KEY,

    granted_permissions permission ARRAY,
    ...
);

Advantages: Easy to name individual permissions (you don't have to handle bit positions).

Disadvantages: Breaks the 1st normal form and is a bit ugly. Takes up a considerable number of bytes in a row if number of permissions is large (about 50).

Can you think of other alternatives?

Which approach should be taken and why?

Please note: this is a modified version of a question posted earlier on Stackoverflow.

Answer 1

I know that you are not asking about database security per se, but you can do what you want using database security. You can even use this in a web app. If you don't want to use database security, then the schemas still apply.

You want column-level security, row-level security, and probably hierarchical role management. Role-Based security is much easier to manage than User-Based security.

This example code is for PostgreSQL 9.4, which comes out soon. You can do it with 9.3, but there is more manual labour required.

You want everything to be indexable if you are concerned with performance †, which you should be. This means that bit-mask and array fields probably won't be a good idea.

In this example, we keep the main data tables in the data schema, and corresponding views in public.

create schema data; --main data tables
create schema security; --acls, security triggers, default privileges

create table data.thing (
  thing_id int primary key,
  subject text not null, --or whatever
  owner name not null
);

Put a trigger on data.thing for inserts and updates enforcing that the owner column is the current_user. Perhaps allow only the owner to delete his own records (another trigger).

Create a WITH CHECK OPTION view, which is what users will actually use. Try really hard to make it updatable, otherwise you'll need triggers/rules, which is more work.

create view public.thing with(security_barrier) as 
select
thing_id,
subject,
owner,
from data.thing
where
pg_has_role(owner, 'member') --only owner or roles "above" him can view his rows. 
WITH CHECK OPTION;

Next, create an access-control list table:

--privileges r=read, w=write

create table security.thing_acl (
  thing_id int,
  grantee name, --the role to whom your are granting the privilege
  privilege char(1) check (privilege in ('r','w') ),

  primary key (thing_id, grantee, privilege),

  foreign key (thing_id) references data.thing(thing_id) on delete cascade
);

Change your view to account for ACLs:

drop view public.thing;

create view public.thing with(security_barrier) as 
select
thing_id,
subject,
owner
from data.thing a
where
pg_has_role(owner, 'member')
or exists (select 1 from security.thing_acl b where b.thing_id = a.thing_id and pg_has_role(grantee, 'member') and privilege='r')
with check option;

Create a default row privileges table:

create table security.default_row_privileges (
  table_name name,
  role_name name,
  privilege char(1),

  primary key (table_name, role_name, privilege)
);

Put a trigger on insert on data.thing so that it copies default row privileges to security.thing_acl .

• Adjust table-level security appropriately (prevent inserts from unwanted users). No one should be able to read the data or security schemas.
• Adjust column-level security appropriately (prevent some users from seeing/editing some columns). You can use has_column_privilege() to check that a user can see a column.
• Probably want security definer tag on your view.
• Consider adding grantor and admin_option columns to acl tables to track who granted the privilege, and whether the grantee can manage privileges on that row.
• Test lots

† In this case pg_has_role is probably not indexable. You would have to get a list of all superior roles to current_user and compare to the owner/grantee value instead.

Answer 2

Have you considered using the Access Control List PostgreSQL extension?

It contains the native PostgreSQL data type ACE and a set of functions that allow you to check if a user has the permission to access data. It works either with the PostgreSQL roles system or with abstract numbers (or UUIDs) representing your application user/role IDs.

In your case, you just add an ACL column to your data tables and use one of the acl_check_access functions to check a user against an ACL.

CREATE TABLE items
(
    item serial PRIMARY KEY,
    acl ace[],
    ...
);

INSERT INTO items(acl, ...) VALUES ('{a//<user id>=r, a//<role id>=rwd, ...}');

SELECT * FROM items where acl_check_access(acl, 'r', <roles of the user>, false) = 'r'

Using ACLs is an extremely flexible way of dealing with business logic permissions. In addition, it is incredibly fast — the average overhead is only 25% of the time needed to read a record. The only limitation being that it supports a maximum of 16 custom permission per object type.

Answer 3

I can think of another possibility to encode this, the relational one

If you don't need the permission_per_item tableyou can skip it and connect Permissions and Items directly to the item_per_user_permissions table.

legend
diagram