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=394width=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.
Thanks,
--
KaiGai Kohei <kaigai@kaigai.gr.jp>