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.
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.
2. Decide whether each qual executes potentially untrusted code (algorithm?).
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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company