Re: [v9.2] Fix Leaky View Problem

The attached patches are fixes to the leaky-view problem; a
prerequisite to implement
row-level security; that consists of two portion.

Part-1)
It adds WITH(options...) clause on view definition, and disallow to
make sub-queries
flatten, if this sub-query is originated a particular view with
"security_barrier" reloption.
In addition, it also disallow to push-down qualifiers across
security-barrier, thus, we
will have a way to guarantee order to launch qualifiers; that has been
headache for us
to achieve row-level security using view (or possibly similar feature).

Part-2)
It adds "leakproof" attribute to functions; that means functions are
obviously leakproof
to the supplied arguments, and only superuser can set.
If a qualifier is consists of functions with "leakproof" only, the
query planner handles it
as an exception of the security-barrier. A typical case is WHERE x =
100; that shall
promote the given scan plan from sequential to index in many cases.
It requires the part-1 being applied prior to this patch, and
compressed by gzip due to
the size of patch (mostly pg_proc.h).

The following examples shows how these features works:

postgres=# CREATE VIEW v1 AS SELECT * FROM t1 WHERE a % 2 =0;
CREATE VIEW
postgres=# CREATE VIEW v1s WITH (security_barrier) AS SELECT * FROM t1
WHERE a % 2 =0;
CREATE VIEW
postgres=# CREATE VIEW v2 AS SELECT * FROM t1 JOIN t2 ON a = x WHERE a % 2 = 0;
CREATE VIEW
postgres=# CREATE VIEW v2s WITH (security_barrier) AS SELECT * FROM t1
JOIN t2 ON a = x W
HERE a % 2 = 0;
CREATE VIEW
postgres=# CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
    LANGUAGE plpgsql COST 0.0001
    AS 'BEGIN RAISE notice ''f_leak => %'', $1; RETURN true;
END';CREATE FUNCTION
Without security_barrier
------------------------------------
postgres=# SELECT * FROM v1 WHERE f_leak(b);NOTICE:  f_leak =>
aaaNOTICE:  f_leak => bbbNOTICE:  f_leak => cccNOTICE:  f_leak => ddd
a |  b---+----- 2 | bbb 4 | ddd(2 rows)
postgres=# EXPLAIN SELECT * FROM v1 WHERE f_leak(b);
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on t1  (cost=0.00..28.45 rows=2 width=36)
   Filter: (f_leak(b) AND ((a % 2) = 0))
(2 rows)


postgres=# SELECT * FROM v2 WHERE f_leak(y);
NOTICE:  f_leak => xxx
NOTICE:  f_leak => yyy
NOTICE:  f_leak => zzz
NOTICE:  f_leak => xyz
 a |  b  | x |  y
---+-----+---+-----
 2 | bbb | 2 | xxx
 4 | ddd | 4 | zzz
(2 rows)

postgres=# EXPLAIN SELECT * FROM v2 WHERE f_leak(y);
                           QUERY PLAN
----------------------------------------------------------------
 Hash Join  (cost=28.52..52.38 rows=2 width=72)
   Hash Cond: (t2.x = t1.a)
   ->  Seq Scan on t2  (cost=0.00..22.30 rows=410 width=36)
         Filter: f_leak(y)
   ->  Hash  (cost=28.45..28.45 rows=6 width=36)
         ->  Seq Scan on t1  (cost=0.00..28.45 rows=6 width=36)
               Filter: ((a % 2) = 0)
(7 rows)


With security_barrier
-------------------------------
postgres=# SELECT * FROM v1s WHERE f_leak(b);NOTICE:  f_leak =>
bbbNOTICE:  f_leak => ddd a |  b---+----- 2 | bbb 4 | ddd(2 rows)
postgres=# EXPLAIN SELECT * FROM v1s WHERE f_leak(b);
                        QUERY PLAN
----------------------------------------------------------
 Subquery Scan on v1s  (cost=0.00..28.51 rows=2 width=36)
   Filter: f_leak(v1s.b)
   ->  Seq Scan on t1  (cost=0.00..28.45 rows=6 width=36)
         Filter: ((a % 2) = 0)
(4 rows)

postgres=# SELECT * FROM v2s WHERE f_leak(y);
NOTICE:  f_leak => xxx
NOTICE:  f_leak => zzz
 a |  b  | x |  y
---+-----+---+-----
 2 | bbb | 2 | xxx
 4 | ddd | 4 | zzz
(2 rows)

postgres=# EXPLAIN SELECT * FROM v2s WHERE f_leak(y);
                              QUERY PLAN
----------------------------------------------------------------------
 Subquery Scan on v2s  (cost=28.52..55.56 rows=2 width=72)
   Filter: f_leak(v2s.y)
   ->  Hash Join  (cost=28.52..55.50 rows=6 width=72)
         Hash Cond: (t2.x = t1.a)
         ->  Seq Scan on t2  (cost=0.00..22.30 rows=1230 width=36)
         ->  Hash  (cost=28.45..28.45 rows=6 width=36)
               ->  Seq Scan on t1  (cost=0.00..28.45 rows=6 width=36)
                     Filter: ((a % 2) = 0)
(8 rows)


Leakproof function is exceptionally allowed to be pushed down
------------------------------------------------------------------------------------------

postgres=# SELECT * FROM v2s WHERE f_leak(y) AND a = 2;
NOTICE:  f_leak => xxx
 a |  b  | x |  y
---+-----+---+-----
 2 | bbb | 2 | xxx
(1 row)

(*) int4eq is set as a leakproof function in the default.

postgres=# EXPLAIN SELECT * FROM v2s WHERE f_leak(y) AND a = 2;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Subquery Scan on v2s  (cost=0.00..16.56 rows=1 width=72)
   Filter: f_leak(v2s.y)
   ->  Nested Loop  (cost=0.00..16.55 rows=1 width=72)
         ->  Index Scan using t1_pkey on t1  (cost=0.00..8.27 rows=1 width=36)
               Index Cond: (a = 2)
               Filter: ((a % 2) = 0)
         ->  Index Scan using t2_pkey on t2  (cost=0.00..8.27 rows=1 width=36)
               Index Cond: (x = 2)
(8 rows)

Thanks,

2011/10/21 Robert Haas <robertmhaas@gmail.com>:
> On Fri, Oct 21, 2011 at 10:36 AM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
>> So, I will split the patch into two parts as follows, in the next commit fest.
>>
>> Part-1) Views with security_barrier reloption
>>
>> The part-1 portion provides views "security_barrier" reloption; that enables
>> to keep sub-queries unflatten in the prepjoin.c stage.
>> In addition, these sub-queries (that originally come from views with
>> "security_barrier" option) don't allow to push down qualifiers from upper
>> level. It shall prevent both of the problematic scenarios.
>>
>> Part-2) Functions with leakproof attribute
>>
>> The part-2 portion provides functions "leakproof" attribute; that enables
>> to push down leakproof functions into sub-queries, even if it originally
>> come from security views.
>> It shall minimize performance damages when we use view for row-level
>> security purpose.
>
> Sounds reasonable.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>



--
KaiGai Kohei <kaigai@kaigai.gr.jp>