[v9.2] Fix Leaky View Problem - Mailing list pgsql-hackers

From Kohei Kaigai
Subject [v9.2] Fix Leaky View Problem
Date
Msg-id D0C1A1F8BF513F469926E6C71461D9EC04C49D@EX10MBX02.EU.NEC.COM
Whole thread Raw
Responses Re: [v9.2] Fix Leaky View Problem
List pgsql-hackers
Hi,

The attached patches try to tackle our long-standing leaky view problem, and were revised according to the discussion
wehad in the commit-fest 1st. 

We already knew two scenarios to leak contents of invisible tuples using functions with side-effects; such as error
messagescontaining its arguments. 

The first one was the order of execution of qualifiers within a scan plan. Query optimizer shall pull-up simple
sub-queriesinto inner-join due to the performance gain, however, it possibly cause a problem that functions supplied at
outsideof the sub-query is launched earlier than functions come from inside of the sub-query; depending on the cost
estimation.In the result, it allows users to reference contents of invisible tuples (to be filtered by view), if they
providea function with side-effects as a part of WHERE clause. 

The second one was the distribution policy of qualifiers. In the case when a view (that intends row-level security)
containsJOIN clause, we hope the qualifiers supplied from outside of the view to be launched after the table join,
becausethe view may filter out some of tuples during checks of its join condition. However, the current query optimizer
willdistribute a qualifier that reference only one-side of the join into inside of the join-loop to minimize number of
tuplesto be joined. In the result, it also allows users to reference contents of invisible tuples. 

In the commit-fest 1st, we made a consensus that a part of views should perform as "security barrier" that enables to
preventunexpected push-down and execution order of qualifiers; being marked by creator of the view. 
And, we also made a consensus obviously secure functions should be allowed to push-down across security barrier; to
minimizeunnecessary performance damages. 

The part-1 patch implements SQL enhancement stuffs; (1) add reloption support on RELKIND_VIEW with "security_barrier"
boolvariable (2) add pg_proc.proleaky flag to show whether the function is possibly leaky, or not. 
The (2) is new stuff from the revision in commit-fest 1st. It enables to supply "NOLEAKY" option on CREATE FUNCTION
statement,then the function is allowed to distribute across security barrier. Only superuser can set this option. 

  Example)
    CREATE FUNCTION safe_func(text) RETURNS bool
        LANGUAGE 'C' NOLEAKY AS '$libdir/safe_lib', 'safe_func';
                     ^^^^^^^
A patch to add a new field into pg_proc always takes a large chunk, so the attached proctrans.php is the script I used
toappend a new field to the existing functions. Right now, I marked it true (= possibly leaky), because we need to have
adiscussion what shall be none-leaky functions in the default. 

The part-2 patch is same as we had discussed in the commit fest. Here is not updates except for rebasing to the latest
tree.It enables to remember the nest level of the qualifier being originally used, and utilize it to sort order of the
qualifiers.

The part-3 patch was a bit revised, although its basic idea has not been changed.
It enables to prevent qualifiers come from outside of security barrier being pushed down into inside of the security
barrier,even if it references only a part of relations within the sub-query expanded from a view with
"security_barrier"flag. 

Thanks,
--
NEC Europe Ltd, SAP Global Competence Center
KaiGai Kohei <kohei.kaigai@emea.nec.com>

Attachment

pgsql-hackers by date:

Previous
From: Gokulakannan Somasundaram
Date:
Subject: Re: the big picture for index-only scans
Next
From: Alexander Korotkov
Date:
Subject: Re: WIP: Fast GiST index build