Wednesday, December 30, 2015

UNIX-like row permissions in PostgreSQL

create table foo (
  foo_id int primary key,
  content text,
  owner name,      --username of the owner (or could be a key to another table)
  grp name,        --name of the group
  mode varchar(3)  --unix-style file permission mode (ex 777)

create extension pg_trgm;

--lets you use indexes with regex:
create index on foo using gin ( mode gin_trgm_ops );
create index on foo (owner);
create index on foo (grp);

insert into foo values
(1, 'bar', 'neil', 'managers', '764'); --764 being owner read/write/delete, group read/write, world read

create function world_read() returns text language sql strict immutable as $$
  select '[4567]$'::text

/* and similar functions world_write(), group_delete() etc */

from foo
    mode ~ world_read()
    or group in (?) and mode ~ group_read()
    or owner = ? and mode ~ owner_read();

from foo
    mode ~ world_delete()
    or group in (?) and mode ~ group_delete()
    or owner = ? and mode ~ owner_delete();

No comments: