Re: [RFC] Interface of Row Level Security

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

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.

On the other hand, all the permission checks are currently applied at executor
stage, not planner stage. Table / column-level privileges are also applied at
head of the executor. It is headache, if we go ahead with an idea to integrate
RLS and existing privilege checks.
One exception is simplify_function() that tries to expand enough simple SQL
functions if current user has ACL_EXECUTE privilege at planner stage.

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

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