Re: RLS Design

Kaigai,

On Thursday, July 3, 2014, Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com> wrote:

> Sorry for my late responding, now I'm catching up the discussion.
>
> > * Robert Haas (robertmhaas(at)gmail(dot)com <javascript:;>) wrote:
> > > On Tue, Jul 1, 2014 at 3:20 PM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com
> <javascript:;>>
> > 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?

No, as outlined later, the table would appear empty if no policies exist
and RLS is enabled for the table.

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

Can you clarify where this is coming from..? It sounds like you're
referring to an existing implementation and, if so, it'd be good to get
more information on how that works exactly.

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

This isn't accurate, as mentioned. Each policy stands alone to define what
is visible through it and if no policy exists then no rows are visible.

> If we would have OR'd RLS-policy, how does it merged with user given
> qualifiers with?

The RLS quals are all applied together with OR's and the result is AND'd
with any user quals provided. This is only when multiple policies are being
applied for a given query and seems pretty straight forward to me.

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

Only the RLS policies are OR'd together, not user provided quals. The above
would result in:

Where t1.x = t1.x and (t1.credential < get_user_credential)

If another policy also applies for this query, such as t1.cred2 <
get_user_credential then we would have:

Where t1.x = t1.x and (t1.credential < get_user_credential OR t1.cred2 <
get_user_credential)

This is similar to how roles work- your overall access includes all access
granted to any roles you are a member of. You don't need SELECT rights
granted to every role you are a member of to select from the table.
Additionally, if an admin wants to AND the quals together then they can
simply create a policy which does that rather than have 2 policies.

> > 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.
>
>
Right- just like how our grant system works.

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

I don't see how it's unpredictable at all.

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

You are suggesting instead that if application 2 sets up policies on the
table and then application 1 adds another policy that it should reduce what
application 2's users can see? That doesn't make any sense to me. I'd
actually expect these applications to at least use different roles anyway,
which means they could each have a single role specific policy which only
returns what that application is allowed to see.

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

This is addressed through the ability to associate roles to policies.

Thanks,

Stephen