Обсуждение: GRANTable Row Permissions

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

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@jimkeener.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