Re: [RFC] A tackle to the leaky VIEWs for RLS - Mailing list pgsql-hackers

(2010/06/01 22:09), Robert Haas wrote:
> 2010/6/1 KaiGai Kohei<kaigai@ak.jp.nec.com>:
>> I have an idea that we add FuncExpr a new field (e.g nestlevel) to remember
>> where is originally put in the query, and prevent reordering over the nest
>> level of subqueries.
>> In above example, f_malicious() has nestlevel=0 because it is put on the top
>> level.
>> But f_policy() has nestlevel=1 because it is originally put on the second
>> level subquery. Then, the order_qual_clauses() will check nestlevel of the
>> scan filter prior to reorder them based on the cost estimation.
>> Even if we have multiple nestlevels, solution will be same. A FuncExpr with
>> larger nestlevel shall be invoked earlier than others.
> [...]
>> My idea is similar to what I proposed at [1]. It adds a new field into
>> RelOptInfo (or other structure?) to remember the original nestlevel of
>> the scan, then it will be compared to nestlevel of the FuncExpr.
>> If nestlevel of the FuncExpr is smaller than nestlevel of the RelOptInfo,
>> it prevents to distribute the FuncExpr onto the RelOptInfo, even if the
>> function depends on only the relation of RelOptInfo.
> 
> Keep in mind that users who are NOT using a view as a security barrier
> don't expect it to kill performance.  This approach, and particularly
> the second part, about preventing quals from being pushed through
> joins, has the potential to be a performance disaster.  So I think
> it's absolutely critical that we don't do that except when it's
> necessary to prevent a security issue.
> 
Yes, I agree. It is necessary to distinguish security conscious views
and others. In general, only creator of the view knows its intention
correctly, so I think it is reasonable suggestion to provide a hint
whether we should prevent a part of optimization, or not.

> On the technical side, I am pretty doubtful that the approach of
> adding a nestlevel to FuncExpr and RelOptInfo is the right way to go.
> I believe we have existing code (to handle left joins) that prevents
> quals from being pushed down too far by fudging the set of relations
> that are supposedly needed to evaluate the qual.  I suspect a similar
> approach would work here.
> 
> I think the steps here are:
> 
> 1. Decide whether the view is a security barrier (perhaps, check
> whether the user has sufficient privs to execute the underlying query;
> or we could add an explicit setting).  If not, stop.

I'm a fun of an explicit setting.
Queries are optimized prior to execution stage.

> 2. Decide whether each qual executes potentially untrusted code (algorithm?).

A simple idea is to assume all the FuncExpr being potentially untrusted
as a starting up of the fix.
(Can we trust all the built-in functions? It needs to ensure they don't
have any side-effects; in future versions also.)

> 3. Prevent any untrusted quals from being pushed down into view that
> is a security barrier.
> 
> We should have a design for each of these before we start coding.
> 

Thanks,
-- 
KaiGai Kohei <kaigai@ak.jp.nec.com>