Re: [RFC] Interface of Row Level Security

2012/5/29 Florian Pflug <fgp(at)phlo(dot)org>:
> On May29, 2012, at 13:37 , Kohei KaiGai wrote:
>> 2012/5/27 Alastair Turner <bell(at)ctrlf5(dot)co(dot)za>:
>>> Excerpts from Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote on Fri, May 25,
>>> 2012 at 11:08 PM:
>>>> If we assume RLS is applied when user has
>>>> no privileges on tables, the current ExecCheckRTEPerms()
>>>> always raises an error towards unprivileged users, prior to
>>>> execution of queries.
>>>> Isn't it preferable behavior to allow unprivileged users to
>>>> reference a table (or columns) when it has RLS policy?
>
> I don't think so. Per-table and per-column permission checks should still
> apply independent from any RLS policy. You can always grant SELECT access
> to PUBLIC if want to rely solely on the RLS policy...
>
Frankly, I have same opinion. Existing per-table and column permission
checks should perform independently from RLS. What I wanted to explain
is the combination of them makes confusion for us.

>>> Rather than assuming the the RLS checks will be applied when there are
>>> no privileges it would make sense to regard RLS as a limitation on the
>>> scope of a particular privilege. This makes RLS a property of a
>>> particular grant of a privilege rather than of the table. Viewed this
>>> way it is possible to control which users are subject to restrictions
>>> imposed by the RLS function, which will avoid RLS overhead for
>>> operations which don't require it while allowing checks for those that
>>> do, provide a mechanism exempting object owners from RLS checks and
>>> make it possible to avoid pg_dump calling user defined code.
>>>
>>> This suggests an alternative declaration syntax, to use the salesman example:
>>>
>>> GRANT SELECT ON TABLE client_detail TO super_sales_group;
>>> GRANT SELECT TABLE ON client_detail TO limited_sales_group WITH
>>> (QUALIFICATION FUNCTION sales_view_check);
>>>
>>> and since more easily usable security features will be used more
>>> effectively, a possible future inlining of the condition:
>>>
>>> GRANT SELECT ON TABLE client_detail TO limited_sales_group WHERE
>>> sales_rep = my_sales_rep();
>>>
>> It seems to me an interesting proposition, because I didn't thought such kind of
>> statement to provide row-level policies.
>
> Note, though, that due to role inheritance, multiple such QUALIFICATION FUNCTIONS
> could be "in scope" for a single table. In that case, I guess you'd have to
> OR them together, i.e. hide a row only if all of them return false.
>
Nice catch! Indeed, it makes RLS policy to be applied complex, and hard to apply
index-scan.

>> We have a common issue for the idea that check privileges of underlying tables;
>> when we should check the privileges and make a decision whether RLS policy is
>> appended, or not.
>> Due to query optimization reason, the RLS policy should be appended prior to
>> the query optimization. At least, we want to utilize RLS policy for index scans,
>> rather than sequential scan.
>
> Agreed. With the current design, the RLS policy has to be applied before planning,
> not during execution.
>
>> One other issue is whether we should allow any users with enough privileges
>> to bypass RLS, or only superusers. I'm still not sure how the existing checks
>> perform with RLS.
>
> Every user who has the power to disable the RLS policy should also at least be
> able to circumvent it temporarily, I think. This includes superusers, the table
> owner (since he's presumably allowed to do ALTER TABLE .. RESET ROW POLICY),
> and maybe the database owner.
>
I'm not inclined this criteria, because existing privilege system
allows to restrict
owner's behavior, even though it can be invalidated by owner itself.
In other words, the privilege mechanism performs according to the current
setting unless it is not disabled by owner itself.
On the other hand, superuser is allowed to "ignore" the security setting.
In RLS situation, it is corresponding to run the query without RLS policy.

>> If and when Alice has SELECT permission on column X and Y of TBL with RLS,
>> but her query tries to reference X,Y and Z. In this case, existing privilege
>> mechanism shall raise an error, but the criteria with underlying table allows to
>> run this query. It seems to me it is a straightforward criteria to
>> limit superusers
>> to bypass RLS…
>
> I'm not sure I understand. Do you mean the Alice references only columns X
> and Y in her query, but the RLS policy adds the reference to column Z?
>
No, in this scenario, Alice tries to reference X,Y and Z, then it
should be violated.
I wanted to show is; If RLS performs only when current user has no table/column
level privilege, it makes strange behavior.
Here is no matter if RLS works independently from per table / column
permissions.

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>