Re: RLS Design

On Tue, Jun 24, 2014 at 8:49 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Let's try to outline what this would look like then.
>
> Taking your approach, we'd have:
>
> CREATE POLICY p1;
> CREATE POLICY p2;
>
> ALTER TABLE t1 SET POLICY p1 TO t1_p1_quals;
> ALTER TABLE t1 SET POLICY p2 TO t1_p2_quals;

This seems like a very nice, flexible framework.

> GRANT SELECT ON TABLE t1 TO role1 USING p1;
> GRANT SELECT ON TABLE t1 TO role2 USING p2;

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.

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.

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