Re: RLS Design - Mailing list pgsql-hackers

On Wed, Jun 25, 2014 at 4:48 PM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>> Instead of doing it this way, we could instead do:
>>
>> ALTER ROLE role1 ADD POLICY p1;
>> ALTER ROLE role2 ADD POLICY p2;
>>
>> We could possibly allow multiple policies to be set for the same user,
>> but given an error (or OR the quals together) if there are conflicting
>> policies for the same table.  A user with no policies would see
>> everything to which they've been granted access.
>>
> I'm a bit uneasy about allowing overlapping policies like this,
> because I think it is more likely to lead to unintended consequences
> than solve real use cases. For example, suppose you define policies p1
> and p2 and set them up on table t1, and you grant role1 permissions on
> t1 and allow role1 the use of policy p1. Then you set up policy p2 on
> another table t2, and decide you want to allow role1 access to t2
> using this policy. The only way to do it is to add p2 to role1, but
> doing so also then gives role1 access to t1 using p2, which might not
> have been what you intended.

I guess that's true but it just seems like a configuration error.  I
have it in mind that most people will define policies for
non-overlapping sets of tables and then apply those policies as
appropriate to each user.  Whether that's true or not, I don't see it
as being materially different from granting membership in a role - you
could easily give the user permission to do stuff they shouldn't be
able to do, but if you don't carefully examine the bundle of
privileges that come with that GRANT before executing on it, that's
your fault, not the system's.

>> To support different policies on different operations, you could have
>> something like:
>>
>> ALTER TABLE t1 SET POLICY p1 ON INSERT TO t1_p1_quals;
>>
>> Without the ON clause, it would establish the given policy for all operations.
>
> Yes, that makes sense. But as I was arguing above, I think the ACLs
> should be attached to the specific RLS policy identified uniquely by
> (table, policy, command). So, for example, if you did
>
> ALTER TABLE t1 SET POLICY p1 ON SELECT TO t1_p1_sel_quals;
> ALTER TABLE t1 SET POLICY p1 ON UPDATE TO t1_p1_upd_quals;
>
> you could also do
>
> GRANT SELECT ON TABLE t1 TO role1 USING p1;
> GRANT UPDATE ON TABLE t1 TO role1 USING p1;
>
> but it would be an error to do
>
> GRANT DELETE 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.  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.

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company