<html><head></head><body>I messed up the list! Thanks!<br><br><div class="gmail_quote">On July 4, 2016 7:39:56 PM EDT, Regina Obe <lr@pcorp.us> wrote:<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<pre class="k9mail">I think this question may be better asked on pg-general since your focus is more on RLS than working with PostGIS.<br /><br /><br /><br />-----Original Message-----<br />From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf Of James Keener<br />Sent: Saturday, July 02, 2016 1:51 AM<br />To: PostGIS Users Discussion <postgis-users@lists.osgeo.org><br />Subject: [postgis-users] GRANTable Row Permissions<br /><br />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.<br />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?<br /><br />So, aay I have<br /><br /> create table viz (<br /> viz_id bigserial primary key,<br /> name text<br />
);<br /><br /> create role group_a;<br /> create role group_b;<br /> create role user1;<br /> create role user2;<br /> create role user3;<br /><br /> grant group_a to user1;<br /> grant group_b to group_a;<br /><br /> insert into viz (name) values ('test 1'),('test 2'),('test 3');<br /><br /><br /><br />I am trying to find a way to essentially do the following:<br /><br /> revoke select on viz from public;<br /> grant select on viz to group_a where viz_id = 1;<br /> grant select on viz to user2 where viz_id = 2;<br /> grant select on viz to group_b where viz_id = 3;<br /><br />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.<br /><br /> create table viz_perm (<br /> viz_id bigint
references viz,<br /> role_name text,<br /> can_view boolean not null default false<br /> );<br /><br /> alter table viz enable row level security;<br /> alter table viz_perm enable row level security;<br /><br /> create policy viz_permissions on viz_perm for select using (<br /> (with recursive rec_roles(grantee,granted) as (<br /> select roless.rolname as grantee, groupss.rolname as granted<br /> from pg_roles roless<br /> inner join pg_auth_members<br /> on roless.oid = pg_auth_members.member<br /> inner join pg_roles groupss<br /> on groupss.oid = pg_auth_members.roleid<br /> union<br /> select rec_roles.grantee as grantee, groupss.rolname as granted<br /> from rec_roles<br /> inner join pg_roles roless on roless.rolname = rec_roles.granted<br /> inner join pg_auth_members<br /> on roless.oid = pg_auth_members.member<br /> inner join pg_roles
groupss<br /> on groupss.oid = pg_auth_members.roleid<br /> )<br /> select bool_or(true)<br /> from rec_roles<br /> where<br /> role_name = current_user<br /> or (grantee = current_user and granted = role_name))<br /> );<br /><br /> create policy viz_permissions on viz using (<br /> (select bool_or(can_view)<br /> from viz_perm<br /> where viz_perm.viz_id=viz.viz_id)<br /> );<br /><br /> insert into viz_perm (viz_id, role_name, can_view) values<br /> (1, 'group_a', true),<br /> (2, 'user2', true),<br /> (3, 'group_b', true);<br /><br /> grant select on viz to user1;<br /> grant select on viz_perm to user1;<br /> grant select on viz to user2;<br /> grant select on viz_perm to user2;<br /><br /><br /> set role user1;<br /> select * from viz;<br /> -- viz_id | name<br /> ----------+--------<br /> -- 1 | test 1<br /> -- 3 | test
3<br /> --(2 rows)<br /><br /> reset role;<br /> set role user2;<br /> select * from viz;<br /> -- viz_id | name<br /> ----------+--------<br /> -- 2 | test 2<br /> --(1 row)<br /><br /> reset role;<br /><br />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?<br /><br />Thanks,<br />Jim<br /><hr /><br />postgis-users mailing list<br />postgis-users@lists.osgeo.org<br /><a href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br /><br /><hr /><br />postgis-users mailing list<br />postgis-users@lists.osgeo.org<br /><a
href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre></blockquote></div><br>
-- <br>
Sent from my Android device with K-9 Mail. Please excuse my brevity.</body></html>