Re: [RFC] Interface of Row Level Security

On Jun5, 2012, at 11:43 , Kohei KaiGai wrote:
> 2012/6/5 Florian Pflug <fgp(at)phlo(dot)org>:
>> What's to be gained by that? Once there's *any* way to bypass a RLS
>> policy, you'll have to deal with the plan invalidation issues you
>> mentioned anyway. ISTM that complexity-wide, you don't save much by not
>> having RLSBYPASS (or something similar), but feature-wise you lose at
>> lot…

> All we need to change is selection of the function to be appended
> automatically. In case when superusers are allowed to bypass RLS
> policy, "OR has_superuser_privilege()" shall be appended to the
> user given clause, however, "OR has_table_privilege()" shall be
> appended instead in case of RLSBYPASS permission.
> (Note that has_table_privilege() always true for superusers.)
>
> I think it does not require to add a mechanism to invalidate
> prepared-statement, because all the checks are applied on
> executor stage. And these functions can be stable functions,
> so I believe some enhancements at planner will correctly wipe
> out prior to query execution at the next step.

That won't work, since the current role can change any time mid-query,
at least if you're using cursors. Here's an example

First, setup a table
> CREATE TABLE data AS SELECT id FROM generate_series(1,1000000) AS id;
> GRANT SELECT ON data TO PUBLIC;

Then create a cursor which doesn't materialize its result
> BEGIN;
> DECLARE mycursor NO SCROLL CURSOR WITHOUT HOLD FOR
SELECT id, current_user FROM data;
> SET ROLE=anybody;
> FETCH mycursor;
id | current_user
----+--------------
1 | anybody
(1 row)
> SET role=somebody;
> FETCH mycursor;
id | current_user
----+--------------
2 | somebody
(1 row)
> ROLLBACK;

And now, for comparison, a cursor which does materialize its result
> BEGIN;
> DECLARE mycursor NO SCROLL CURSOR WITHOUT HOLD FOR
SELECT id, current_user FROM data ORDER BY id;
> SET ROLE=anybody;
> FETCH mycursor;
id | current_user
----+--------------
1 | anybody
(1 row)

> SET role=somebody;
> FETCH mycursor;
id | current_user
----+--------------
2 | anybody
(1 row)

Imagine that the current_user function call was actually part of the
RLS policy. Then the example above shows that *which* role we check
access against (the one active at the first fetch, or currently active
one) depends on which plan the optimizer happens to pick. This, IMHO,
is not acceptable for a security feature like RLS.

best regards,
Florian Pflug