Re: RLS Design - Mailing list pgsql-hackers

On 26 June 2014 18:04, Robert Haas <robertmhaas@gmail.com> wrote:
>> ALTER TABLE t1 SET POLICY p1 ON SELECT TO t1_p1_sel_quals;
>> GRANT SELECT ON TABLE t1 TO role1 USING p1;
>
> As I see it, the downside of this is that it gets a lot more complex.
> We have to revise the ACL representation, which is already pretty darn
> complicated, to keep track not only of the grantee, grantor, and
> permissions, but also the policies qualifying those permissions.  The
> changes to GRANT will need to propagate into GRANT ON ALL TABLES IN
> SCHEMA and AFTER DEFAULT PRIVILEGES.

No, it can be done without any changes to the permissions code by
storing the ACLs on the catalog entries where the RLS quals are held,
rather than modifying the ACL items on the table. I.e., instead of
thinking of "USING polname" as a modifier to the grant, think of it as
as an additional qualifier on the thing being granted.

That means the syntax I proposed earlier is wrong/misleading. Instead of

GRANT SELECT ON TABLE tbl TO role USING polname;

it should really be

GRANT SELECT USING polname ON TABLE tbl TO role;


> There is administrative
> complexity as well, because if you want to policy-protect an
> additional table, you've got to add the table to the policy and then
> update all the grants as well.  I think what will happen in practice
> is that people will grant to PUBLIC all rights on the policy, and then
> do all the access control through the GRANT statements.
>

If you assume that most users will only have one policy through which
they can access any given table, then there is no more administrative
overhead than we have right now. Right now you have to grant each user
permissions on each table you define. The only difference is that now
you throw in a "USING polname". We could also simplify administration
by supporting

GRANT SELECT USING polname ON ALL TABLES IN SCHEMA sch TO role;

The important distinction is that this is only granting permissions on
tables that exist now, not on tables that might be created later.


> An interesting question we haven't much considered is: who can set up
> policies and add then to users?  Maybe we should flip this around, and
> instead of adding users to policies, we should exempt users from
> policies.
>
> CREATE POLICY p1;
>
> And then, if they own p1 and t1, they can do:
>
> ALTER TABLE t1 SET POLICY p1 TO t1_p1_quals;
> (or maybe we should associate it to the policy instead of the table:
> ALTER POLICY p1 SET TABLE t1 TO t1_p1_quals)
>
> And then the policy applies to everyone who doesn't have the grantable
> EXEMPT privilege on the policy.  The policy owner and superuser have
> that privilege by default and it can be handed out to others like
> this:
>
> GRANT EXEMPT ON POLICY p1 TO snowden;
>
> Then users who have row_level_security=on will bypass RLS if possible,
> and otherwise it will be applied.  Users who have
> row_level_security=off will bypass RLS if possible, and otherwise
> error.  And users who have row_level_security=force will apply RLS
> even if they are entitled to bypass it.
>

That's interesting. I need to think some more about what that means.

Regards,
Dean