Re: [RFC] Interface of Row Level Security

2012/6/4 Robert Haas <robertmhaas@gmail.com>:
> On Sat, Jun 2, 2012 at 12:58 AM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
>> 2012/6/1 Robert Haas <robertmhaas@gmail.com>:
>>> On Thu, May 31, 2012 at 3:52 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
>>>> It may be an option to separate the case into two; a situation to execute
>>>> the given query immediately just after optimization and never reused,
>>>> and others.
>>>
>>> Yes.  Simon suggested exactly this a while back, and I agree with him
>>> that we ought to have it.
>>>
>> It is good for me, also.
>>
>>>> Then, if so, we will be able to push the stuff corresponding to
>>>> RLSBYPASS into the query optimization, and works transparently
>>>> for users.
>>>
>>> You're still going to need a way to make sure that the cluster can be
>>> dumped properly.  RLSBYPASS accomplishes that; your scheme doesn't.
>>>
>> If something like "has_superuser_privilege() OR" is automatically
>> appended to user given clauses, it makes sure whole of the database
>> cluster is dumped.
>> That also means any permission checks are delayed to executor stage
>> (except for the case on simple query protocol, I mentioned above),
>> thus it simplifies the condition to invalidate prepared statement.
>>
>> One problem I noticed last night around RLSBYPASS approach is:
>> it can take much number of invalidation whenever current user-id is
>> switched. Not only SET AUTHORIZATION, but SECURITY DEFINER
>> function's invocation also. I'm not sure whether this invalidation
>> storm is reasonable level, or not.
>>
>> Is it unavailable to handle this type of implicit superuser checks
>> with existing EXECUTE statement?
>> I tried to run EXPLAIN with similar case.
>>
>> postgres=# PREPARE p1(int, bool) AS SELECT * FROM tbl WHERE y > $1 OR $2;
>> PREPARE
>> postgres=# EXPLAIN EXECUTE p1(10, current_user in ('kaigai','rhaas'));
>>                       QUERY PLAN
>> --------------------------------------------------------
>>  Seq Scan on tbl  (cost=0.00..21.60 rows=1160 width=40)
>> (1 row
>>
>> However,
>>
>> postgres=# PREPARE p2(int) AS SELECT * FROM tbl
>>                   WHERE y > $1 OR current_user in ('kaigai','rhaas');
>> PREPARE
>> postgres=# EXPLAIN EXECUTE p2(10);
>>                                 QUERY PLAN
>> -----------------------------------------------------------------------------
>>  Seq Scan on tbl  (cost=0.00..30.30 rows=394 width=40)
>>   Filter: ((y > 10) OR ("current_user"() = ANY ('{kaigai,rhaas}'::name[])))
>> (2 rows)
>>
>> Please assume the second condition something like superuser
>> checks in addition to the main security policy.
>> It implies an idea to replace a certain portion of clause that
>> consists of only constant values and stable / immutable functions
>> by shadow parameter to be calculated at executor stage, makes
>> sense to wipe out RLS policy for superusers. In addition, it requires
>> no new invalidation mechanism to prepared statements.
>
> I'm not sure how best to handle the invalidation issue... but I still
> think that relying on the query planner to use theorem-proving to
> simplify RLS conditions is going to be problematic.  You can certainly
> try it ad see how it comes out...
>
I think, the enhancement of planner can be handled independently
from RLS policy stuff, and we should avoid to contain many complex
stuff into one functionality.
At least, it will work correctly with idea to append implicit condition
("OR has_superuser_privilege()") without any invalidation mechanism,
although its performance penalty is not negligible; without planner-
enhancement.
How about your opinion?

I'm worry about future maintenance issues, once we have
RLSBYPASS permission or something user visible...

Thanks,
--
KaiGai Kohei <kaigai@kaigai.gr.jp>