Re: [RFC] Interface of Row Level Security

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

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company