[postgis-users] GRANTable Row Permissions

[James Keener]

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