GRANTable Row Permissions

From: James Keener <jim(at)jimkeener(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: GRANTable Row Permissions
Date: 2016-07-03 16:26:22
Message-ID: CAG8g3twHhU9PuEcOEjX2Jek5jD_mgN-1wk0qOZQr3fRKpEdmXQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm trying to work out how to grant permissions to rows in a table
without having to rebuild the pg auth mechanisms (see below). One option
is to have many tables (each representing a row), and grant normally.
The other is, like I build below, uses a table and a recursive CTE to
resolve the PG group membership and apply it to the table in question
using a RLS policy. Is any of this sane?

So, aay I have

create table viz (
viz_id bigserial primary key,
name text
);

create role group_a;
create role group_b;
create role user1;
create role user2;
create role user3;

grant group_a to user1;
grant group_b to group_a;

insert into viz (name) values ('test 1'),('test 2'),('test 3');

I am trying to find a way to essentially do the following:

revoke select on viz from public;
grant select on viz to group_a where viz_id = 1;
grant select on viz to user2 where viz_id = 2;
grant select on viz to group_b where viz_id = 3;

With RLS I can create a policy that can validate via an arbitrary sql
statement, but I can't think of a clean way to have row-level grants
that can be implemented without having to kludge the pg permission
system into a table. The following kind of gets at what I want, but
uses a table instead of being able to grant.

create table viz_perm (
viz_id bigint references viz,
role_name text,
can_view boolean not null default false
);

alter table viz enable row level security;
alter table viz_perm enable row level security;

create policy viz_permissions on viz_perm for select using (
(with recursive rec_roles(grantee,granted) as (
select roless.rolname as grantee, groupss.rolname as granted
from pg_roles roless
inner join pg_auth_members
on roless.oid = pg_auth_members.member
inner join pg_roles groupss
on groupss.oid = pg_auth_members.roleid
union
select rec_roles.grantee as grantee, groupss.rolname as granted
from rec_roles
inner join pg_roles roless on roless.rolname = rec_roles.granted
inner join pg_auth_members
on roless.oid = pg_auth_members.member
inner join pg_roles groupss
on groupss.oid = pg_auth_members.roleid
)
select bool_or(true)
from rec_roles
where
role_name = current_user
or (grantee = current_user and granted = role_name))
);

create policy viz_permissions on viz using (
(select bool_or(can_view)
from viz_perm
where viz_perm.viz_id=viz.viz_id)
);

insert into viz_perm (viz_id, role_name, can_view) values
(1, 'group_a', true),
(2, 'user2', true),
(3, 'group_b', true);

grant select on viz to user1;
grant select on viz_perm to user1;
grant select on viz to user2;
grant select on viz_perm to user2;

set role user1;
select * from viz;
-- viz_id | name
----------+--------
-- 1 | test 1
-- 3 | test 3
--(2 rows)

reset role;
set role user2;
select * from viz;
-- viz_id | name
----------+--------
-- 2 | test 2
--(1 row)

reset role;

While the above more-or-less works, it feels very wonky. Is there a
better way to do this? Would it be better to have a table for each viz,
necessitating each table having a single row, and using the standard
permission system. Is what I describe and build in this email an
acceptable way to go about doing what I want to do?

Thanks,
Jim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message James Keener 2016-07-03 16:33:17 Re: GRANTable Row Permissions
Previous Message Adrian Klaver 2016-07-03 16:11:13 Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements