[postgis-users] GRANTable Row Permissions

[James Keener]

I messed up the list! Thanks!

On July 4, 2016 7:39:56 PM EDT, Regina Obe <lr at pcorp.us> wrote:
>I think this question may be better asked on pg-general since your
>focus is more on RLS than working with PostGIS.
>
>
>
>-----Original Message-----
>From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
>Behalf Of James Keener
>Sent: Saturday, July 02, 2016 1:51 AM
>To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
>Subject: [postgis-users] 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
>_______________________________________________
>postgis-users mailing list
>postgis-users at lists.osgeo.org
>http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at lists.osgeo.org
>http://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160704/8e871903/attachment.html>