Re: RLS Design

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> 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.

Ok, I like that as it means that "normal" GRANTs, etc, remain the same
and are just constrained by RLS when there is an RLS policy on the
table, which I believe is really the right approach.

> 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.

Right, this makes sense also and is similar to what we were angling
towards initially. I'll think further on this and propose a catalog
structure and try to delve into the semantics of query operations, etc.

One issue that occurs to me is trying to think through how to address
the plancache invalidation, such that we are not invalidating constantly
but also setting the correct quals for the current query. We had gone
down a road where we saved a plan for each role under which a query was
run but then ripped that out because the RLS policy would handle the
per-role issues (modulo whether RLS should be enabled or not). This
approach means that we'd have to bring back the notion of per-role plan
cacheing. I'm not against that, just making note of it.

Thanks,

Stephen