Re: RLS Design - Mailing list pgsql-hackers

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Tue, Jul 1, 2014 at 3:20 PM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> > If RLS quals are instead regarded as constraints on access, and
> > multiple policies apply, then it seems that the quals should now be
> > combined with AND rather than OR, right?

I do feel that RLS quals are constraints on access, but I don't see how
it follows that multiple quals should be AND'd together because of that.
I view the RLS policies on each table as being independent and "standing
alone" regarding what can be seen.  If you have access to a table today
through policy A, and then later policy B is added, using AND would mean
that the set of rows returned is less than if only policy A existed.
That doesn't seem correct to me.

> Yeah, maybe.  I intuitively feel that OR would be more useful, so it
> would be nice to find a design where that makes sense.  But it depends
> a lot, in my view, on what syntax we end up with.  For example,
> suppose we add just one command:
>
> ALTER TABLE table_name FILTER [ role_name | PUBLIC ] USING qual;
>
> If the given role inherits from multiple roles that have different
> filters, I think the user will naturally expect all of the filters to
> be applied.

Agreed.

> But you could do it other ways.  For example:
>
> ALTER TABLE table_name [ NO ] ROW LEVEL SECURITY;
> ALTER TABLE table_name GRANT ROW ACCESS TO role_name USING qual;
>
> If a table is set to NO ROW LEVEL SECURITY then it behaves just like
> it does now: anyone who accesses it sees all the rows, restricted to
> those columns for which they have permission.  If the table is set to
> ROW LEVEL SECURITY then the default is to show no rows.  The second
> command then allows access to a subset of the rows for a give role
> name.  In this case, it is probably logical for access to be combined
> via OR.

I can see value is having a table-level option to indicate if RLS is
applied for that table or not, but I had been thinking we'd just
automatically manage that.  That is to say that once you define an RLS
policy for a table, we go look and see what policy should be applied in
each case.  With the user able to control that, what happens if they say
"row security" on the table and there are no policies?  All access would
show the table as empty?  What if policies exist and they decide to
'turn off' RLS for the table- suddenly everyone can see all the rows?

My answers to the above (which are making me like the idea more,
actually...) would be:

Yes, if they turn on RLS for the table and there aren't any policies,
then the table appears empty for anyone with normal SELECT rights (table
owner and superusers would still see everything).

If policies exist and the user asks to turn off RLS, I'd throw an ERROR
as there is a security risk there.  We could support a CASCADE option
which would go and drop the policies from the table first.

Otherwise, I'm generally liking Dean's thoughts in
http://www.postgresql.org/message-id/CAEZATCVftksFH=X+9mVmBNMZo5KsUP+RK0kb4oRO92JOfjO29g@mail.gmail.com
along with the table-level "enable RLS" option.

Are we getting to a point where there is sufficient agreement that it'd
be worthwhile to really start implementing this?  I'd suggest that we
either forgo or at least table the notion of per-column policy
definitions- RLS controls whole rows and so I don't feel that per-column
policies really make sense.
Thanks,
    Stephen