Re: [RFC] Interface of Row Level Security

2012/6/5 Florian Pflug <fgp(at)phlo(dot)org>:
> 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.
>
Which is your expected behavior in this example?

In case when the query is internally materialized, the current_user function
is *already* executed prior to switch user-id, thus, the result set internally
stored in was already filtered out using older user-id.

It seems to me, caution of the problem is current_user is performing out
of the snapshot controls. According to the definition of stable function,
its result should not be changed during a particular scan.
At least, it is not a fundamental problem of RLS implementation, although
it needs to take an enhancement something like effective user-id per
snapshot basis.

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