Re: RLS Design

Sorry for my late responding, now I'm catching up the discussion.

> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> > On Tue, Jul 1, 2014 at 3:20 PM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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.
>
It seems to me direction of the constraints (RLS-policy) works to is reverse.

In case when we have no RLS-policy, 100% of rows are visible isn't it?
Addition of a constraint usually reduces the number of rows being visible,
or same number of rows at least. Constraint shall never work to the direction
to increase the number of rows being visible.

If multiple RLS-policies are connected with OR-operator, the first policy
works to the direction to reduce number of visible rows, but the second
policy works to the reverse direction.

If we would have OR'd RLS-policy, how does it merged with user given
qualifiers with?
For example, if RLS-policy of t1 is (t1.credential < get_user_credential)
and user's query is:
SELECT * FROM t1 WHERE t1.x = t1.x;
Do you think RLS-policy shall be merged with OR'd form?

> > 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.
>
Hmm... This approach starts from the empty permission then adds permission
to reference a particular range of the configured table. It's one attitude.

However, I think it has a dark side we cannot ignore. Usually, the purpose
of security mechanism is to ensure which is readable/writable according to
the rules. Once multiple RLS-policies are merged with OR'd form, its results
are unpredicatable.
Please assume here are two individual applications that use RLS on table-X.
Even if application-1 want only rows being "public" become visible, it may
expose "credential" or "secret" rows by interaction of orthogonal policy
configured by application-2 (that may configure the policy according to the
source ip-address). It seems to me application-2 partially invalidated the
RLS-policy configured by application-1.

I think, an important characteristic is things to be invisible is invisible
even though multiple rules are configured.

> Otherwise, I'm generally liking Dean's thoughts in
> http://www.postgresql.org/message-id/CAEZATCVftksFH=X+9mVmBNMZo5KsUP+R
> K0kb4oRO92JOfjO29g(at)mail(dot)gmail(dot)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,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>