Re: [RFC] Interface of Row Level Security

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

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

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

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

best regards,
Florian Pflug