Re: RLS Design - Mailing list pgsql-hackers

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

Oracle VPD - Multiple Policies for Each Table, View, or Synonym
http://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvpoli.htm#i1008351

It says - Note that all policies applied to a table are enforced with AND syntax.

Not only Oracle VPD, it fits attitude of defense in depth.
Please assume a system that installs network firewall, unix permissions
and selinux. If somebody wants to reference an information asset within
a file, he has to connect the server from the network address being allowed
by the firewall configuration AND both of DAC and MAC has to allow his
access.
Usually, we have to pass all the access control to reference the target
information, not one of the access control stuffs being installed.


>     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.
> 
It seems to me a pain on database administration, if we have to pay attention
not to conflict each RLS-policy. I expect 90% of RLS-policy will be configured
to PUBLIC user, to apply everybody same rules on access. In this case, DBA
has to ensure the target table has no policy or existing policy does not
conflict with the new policy to be set.
I don't think it is a good idea to enforce DBA these checks.


>     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 don't think this assumption is reasonable.
Please expect two applications: app-X that is a database security product
to apply access control based on remote ip-address of the client for any
table accesses by any database roles. app-Y that is a usual enterprise
package for daily business data, with RLS-policy.
What is the expected behavior in this case?

App-X provides overall access control towards whole of the database.
So, it expects any client out of 192.168.0.0/16 should not reference
any credential information for example.
How does it interact with the RLS-policy by app-Y? If RLS-policies
are merged with OR'd form, it seems to me it invalidate control of
app-Y if client connected from inside of 192.168.0.0/16 or if client
connects with a particular app-Y's role from out of 192.168.0.0/16.

How to solve the situation above? 

Thanks,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>