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(at)gmail(dot)com>:
> On Fri, Oct 21, 2011 at 10:36 AM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)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(at)kaigai(dot)gr(dot)jp>