Re: GRANTable Row Permissions

Of course I think of something as soon as I send it. Policies can be
granted to a specific role! So

create policy xxxxxx on table_1 for select to role_1 using (row_id = 1234);

Jim

On Sun, Jul 3, 2016 at 12:26 PM, James Keener <jim(at)jimkeener(dot)com> wrote:
> 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