Re: RLS - permissive vs restrictive

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Tue, Oct 7, 2014 at 6:44 AM, Thom Brown <thom@linux.com> wrote:
> > It appears that I'm not the only person who finds it somewhat
> > unintuitive for overlapping RLS policies to be permissive rather than
> > restrictive (OR vs AND) (at least 3 others seem to expect AND
> > behaviour), although I understand the reasoning behind
> > it.  And I've since discovered that the same feature in another
> > database system uses the latter rather than the former.
> >
> > I posted a brain coredump of my thoughts on the matter on Depesz's
> > blog (http://www.depesz.com/2014/10/02/waiting-for-9-5-row-level-security-policies-rls/#comment-187800)
> > and I was wondering if there's a future in allowing both systems.  The
> > syntax is less important than the functionality, where restrictive
> > policies would be AND'd, permissive policies would (like they
> > currently do) be OR'd, and a combination would involve all restrictive
> > plus at least one permissive (i.e. restr1 AND restr2 AND (perm3 OR
> > perm4)).
> >
> > I'm just interested to know what others' thoughts on the matter are.
>
> I think that could make sense.  I think the main thing to consider is
> the case where different policies apply to different users: what will
> be the combined effect for users who are subjected to any subset of
> those policies?   If the same policies applies to everyone, then you
> can just do it all as a single policy and put whatever Boolean logic
> you like inside of it.

Agreed- this, as I recall, was the crux of the concern when we were
discussing it back in March(?).  One option might be to have a flag on
the table which basically says "and" or "or", but that feels pretty
limiting to me.  Another option would be to allow the user to define the
specific and-vs-or for each policy like so:

ALTER TABLE t SET ROW SECURITY POLICY p1 OR (p2 AND p3);

... or you could just only have one policy on the table and do whatever
you'd like with it (which was the original idea, actually, though I've
found myself very much liking the ability to have multiple policies, and
to have them set for specific roles and commands, rather than having to
have one very complicated policy or having to use a function..).

As mentioned previously, I'm certainly not against any of these options,
but we need to figure out what we actually want first.  I do think 'OR'
is still the right answer if we're only going to do one thing as this
wouldn't make any sense to me:

CREATE ROLE r1;
CREATE ROLE r2;
CREATE ROLE r3;
GRANT r2, r3 TO r1;

CREATE TABLE t (a int);
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY p1 ON t TO r2 USING (a = 1);
CREATE POLICY p2 ON t TO r3 USING (a = 2);
GRANT SELECT ON t TO r1, r2, r3;

INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);

SET ROLE r1;
TABLE t;a
---
(0 rows)


SET ROLE r2;
TABLE t;a
---1
(1 row)

Given that r2 and r3 are granted to r1, using AND would mean that r1
can't actually see any rows (and this could end up happening at a later
point too- perhaps r1 only had r2 granted originally and was then
granted r3..).  We could try to come up with convoluted rules for
policies which have roles associated vs. policies which are for all
users, or try to define the policy overlap handling in some other way,
but I don't think we'd be doing our users a favor by doing so.
Thanks,
    Stephen