pgsql-v9.2-fix-leaky-view-part-4.v3.patch

 doc/src/sgml/rules.sgml                      |   95 +++++++++++++++++
 src/test/regress/expected/create_view.out    |   34 ++++++-
 src/test/regress/expected/select_views_1.out |  141 ++++++++++++++++++++++++++
 src/test/regress/sql/create_view.sql         |   17 +++
 src/test/regress/sql/select_views.sql        |   65 ++++++++++++
 5 files changed, 351 insertions(+), 1 deletions(-)

diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 1b06519..4ccb076 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -1856,6 +1856,101 @@ SELECT * FROM phone_number WHERE tricky(person, phone);
 </para>
 
 <para>
+    In addition, another scenarios is known to leak invisible tuples, even if
+    these should be filtered out by view.
+<programlisting>
+CREATE VIEW your_credit AS
+    SELECT a.rolname, c.number, c.expire
+    FROM pg_authid a JOIN credit_cards c ON a.oid = c.id
+    WHERE a.rolname = getpgusername();
+</programlisting>
+    This view may also look secure, because all the references to the
+    <literal>your_credit</literal> are restricted to the tuples which
+    satisfies <literal>a.rolname = getpgusername()</literal>.
+    Unfortunately, this assumption is incorrect.
+</para>
+<para>
+    Note that this view contains a join loop. If a user provides
+    <literal>WHERE</literal> clause a function that references only
+    one-side of the join loop, the query planner distributes this
+    qualifier inside of the loop to minimize the number of tuples to
+    be joined.
+
+    It is an example to break the row-level security implemented with
+    <literal>your_credit</literal> view:
+<programlisting>
+postgres=> SELECT * FROM your_credit WHERE tricky(number, expire);
+NOTICE:  1111-2222-3333-4444 => Jan-01
+NOTICE:  5555-6666-7777-8888 => Feb-02
+NOTICE:  1234-5678-9012-3456 => Mar-03
+ rolname |       number        | expire
+---------+---------------------+--------
+ alice   | 5555-6666-7777-8888 | Feb-02
+(1 row)
+</programlisting>
+</para>
+<para>
+    The output of <command>EXPLAIN</command> shows us obvious reason
+    of this unpreferable result.
+<programlisting>
+postgres=> EXPLAIN SELECT * FROM your_credit WHERE tricky(number, expire);
+                               QUERY PLAN
+------------------------------------------------------------------------
+ Hash Join  (cost=1.03..20.38 rows=1 width=128)
+   Hash Cond: (c.id = a.oid)
+   ->  Seq Scan on credit_cards c  (cost=0.00..18.30 rows=277 width=68)
+         Filter: tricky(number, expire)
+   ->  Hash  (cost=1.01..1.01 rows=1 width=68)
+         ->  Seq Scan on pg_authid a  (cost=0.00..1.01 rows=1 width=68)
+               Filter: (rolname = getpgusername())
+(7 rows)
+</programlisting>
+    The supplied <function>tricky</function> only references
+    <literal>number</literal> and <literal>expire</literal> columns
+    of <literal>credit_cards</literal> relation, however, the qualifier
+    to restrict invisible tuples performs at another side of the join
+    loop.
+    In the result, the supplied <function>tricky</function> was launched
+    during the executor scans <literal>credit_cards</literal> table, then
+    it raised messages including given arguments that contains information
+    to be invisible.
+</para>
+<para>
+    <productname>PostgreSQL</productname> provides a countermeasure for
+    these scenarios. <command>CREATE SECURITY VIEW</command> allows to
+    define viewa with a hint that informs the query planner this view is
+    intended to filter out invisible tuples; in other word, it shall be
+    used to row-level security purpose, instead of a bit performance
+    trade-off.
+</para>
+<para>
+    If the query planner found a view with this hint, it never launches
+    qualifiers provided at outside of the view earlier than qualifiers
+    used inside of the view, independent from its cost estimation,
+    even if these qualifiers are chained to same scan plan togetger.
+    In addition, the query planner never push down the qualifiers
+    across the view with this hint, even if it references one-side of
+    the join loop only, except for <literal>LEAKPROOF</litaral> functions.
+</para>
+<para>
+    No need to say, it has performance trade-off, because it restain
+    a part of query optimization to be done in regular cases.
+    Especially, it was expected that performance trade-off is unacceptable
+    in the case when index-scan was degraded to sequential-scan due to
+    prevention of qualifier distribution to optimal scan plan.
+    So, we also provided a way to provide the query optimizer a hint that
+    shows the function is obviously leakproof, so no need to prevent
+    push down the qualifiers into join-loops.
+    It is <literal>LEAKPROOF</literal> option of
+    <command>CREATE FUNCTION</command>; that allows only superusers to
+    define functions with this flag.
+</para>
+<para>
+    These options allows to find out the most suitable combination of
+    security and performance. DBA should set up views of row-level
+    security using these options, with understanding to the background.
+</para>
+<para>
     Similar considerations apply to update rules. In the examples of
     the previous section, the owner of the tables in the example
     database could grant the privileges <literal>SELECT</>,
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f9490a3..9e92173 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -239,6 +239,36 @@ And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
      1
 (1 row)
 
+-- the view shall be marked as 'security view'
+CREATE SECURITY VIEW mysecview1
+       AS SELECT * FROM tbl1 WHERE a > 0;
+SELECT relname, relkind, relissecbarrier FROM pg_class
+       WHERE oid = 'mysecview1'::regclass;
+  relname   | relkind | relissecbarrier 
+------------+---------+-----------------
+ mysecview1 | v       | t
+(1 row)
+
+-- 'security view' flag shall be preserved
+CREATE OR REPLACE VIEW mysecview1
+       AS SELECT * FROM tbl1 WHERE a < 0;
+SELECT relname, relkind, relissecbarrier FROM pg_class
+       WHERE oid = 'mysecview1'::regclass;
+  relname   | relkind | relissecbarrier 
+------------+---------+-----------------
+ mysecview1 | v       | t
+(1 row)
+
+-- the default of 'security view' is false
+CREATE OR REPLACE VIEW mysecview2
+       AS SELECT * FROM tbl1 WHERE a = 0;
+SELECT relname, relkind, relissecbarrier FROM pg_class
+       WHERE oid = 'mysecview2'::regclass;
+  relname   | relkind | relissecbarrier 
+------------+---------+-----------------
+ mysecview2 | v       | f
+(1 row)
+
 DROP SCHEMA temp_view_test CASCADE;
 NOTICE:  drop cascades to 22 other objects
 DETAIL:  drop cascades to table temp_view_test.base_table
@@ -264,7 +294,7 @@ drop cascades to view temp_view_test.v8
 drop cascades to sequence temp_view_test.seq1
 drop cascades to view temp_view_test.v9
 DROP SCHEMA testviewschm2 CASCADE;
-NOTICE:  drop cascades to 16 other objects
+NOTICE:  drop cascades to 18 other objects
 DETAIL:  drop cascades to table t1
 drop cascades to view temporal1
 drop cascades to view temporal2
@@ -281,4 +311,6 @@ drop cascades to table tbl3
 drop cascades to table tbl4
 drop cascades to view mytempview
 drop cascades to view pubview
+drop cascades to view mysecview1
+drop cascades to view mysecview2
 SET search_path to public;
diff --git a/src/test/regress/expected/select_views_1.out b/src/test/regress/expected/select_views_1.out
index 9a972cf..1d1f9e9 100644
--- a/src/test/regress/expected/select_views_1.out
+++ b/src/test/regress/expected/select_views_1.out
@@ -1247,3 +1247,144 @@ SELECT * FROM toyemp WHERE name = 'sharon';
  sharon |  25 | (15,12)  |     12000
 (1 row)
 
+--
+-- Test for Leaky view scenario
+--
+CREATE USER alice;
+CREATE FUNCTION f_leak (text)
+       RETURNS bool LANGUAGE 'plpgsql'
+       COST 0.0000001
+       AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
+CREATE FUNCTION f_leakproof (text)
+       RETURNS bool LANGUAGE 'plpgsql' LEAKPROOF
+       COST 0.0000001
+       AS 'BEGIN RAISE NOTICE ''f_leakproof => %'', $1; RETURN true; END';
+CREATE TABLE customer (
+       cid	int primary key,
+       name	text,
+       addr	text,
+       passwd	text
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer"
+CREATE TABLE credit_cards (
+       cid	int references customer(cid),
+       cardno	text,
+       expired	text
+);
+INSERT INTO customer
+       VALUES (101, 'alice', 'Japan',   'passwd123'),
+              (102, 'bob',   'USA',     'beafsteak'),
+              (103, 'eve',   'Germany', 'hamburger');
+INSERT INTO credit_cards
+       VALUES (101, '1111-2222-3333-4444', 'Aug-2012'),
+       	      (102, '5555-6666-7777-8888', 'Nov-2016'),
+	      (103, '9801-2345-6789-0123', 'Jan-2018');
+CREATE VIEW your_property_normal AS
+       SELECT * FROM customer WHERE name = getpgusername();
+CREATE SECURITY VIEW your_property_secure AS
+       SELECT * FROM customer WHERE name = getpgusername();
+CREATE VIEW your_credit_card_normal AS
+       SELECT l.name, r.* FROM customer l NATURAL JOIN credit_cards r
+       WHERE name = getpgusername();
+CREATE SECURITY VIEW your_credit_card_secure AS
+       SELECT l.name, r.* FROM customer l NATURAL JOIN credit_cards r
+       WHERE name = getpgusername();
+GRANT SELECT ON your_property_normal TO public;
+GRANT SELECT ON your_property_secure TO public;
+GRANT SELECT ON your_credit_card_normal TO public;
+GRANT SELECT ON your_credit_card_secure TO public;
+---
+--- Run leaky view scenarios
+---
+SET SESSION AUTHORIZATION alice;
+SELECT * FROM your_property_normal WHERE f_leak(passwd);
+NOTICE:  f_leak => passwd123
+NOTICE:  f_leak => beafsteak
+NOTICE:  f_leak => hamburger
+ cid | name  | addr  |  passwd   
+-----+-------+-------+-----------
+ 101 | alice | Japan | passwd123
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM your_property_normal WHERE f_leak(passwd);
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Seq Scan on customer
+   Filter: (f_leak(passwd) AND (name = (getpgusername())::text))
+(2 rows)
+
+SELECT * FROM your_property_secure WHERE f_leak(passwd);
+NOTICE:  f_leak => passwd123
+ cid | name  | addr  |  passwd   
+-----+-------+-------+-----------
+ 101 | alice | Japan | passwd123
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM your_property_secure WHERE f_leak(passwd);
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Seq Scan on customer
+   Filter: ((name = (getpgusername())::text) AND f_leak(passwd))
+(2 rows)
+
+SELECT * FROM your_credit_card_normal WHERE f_leak(cardno);
+NOTICE:  f_leak => 1111-2222-3333-4444
+NOTICE:  f_leak => 5555-6666-7777-8888
+NOTICE:  f_leak => 9801-2345-6789-0123
+ name  | cid |       cardno        | expired  
+-------+-----+---------------------+----------
+ alice | 101 | 1111-2222-3333-4444 | Aug-2012
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM your_credit_card_normal WHERE f_leak(cardno);
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Hash Join
+   Hash Cond: (r.cid = l.cid)
+   ->  Seq Scan on credit_cards r
+         Filter: f_leak(cardno)
+   ->  Hash
+         ->  Seq Scan on customer l
+               Filter: (name = (getpgusername())::text)
+(7 rows)
+
+SELECT * FROM your_credit_card_secure WHERE f_leak(cardno);
+NOTICE:  f_leak => 1111-2222-3333-4444
+ name  | cid |       cardno        | expired  
+-------+-----+---------------------+----------
+ alice | 101 | 1111-2222-3333-4444 | Aug-2012
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM your_credit_card_secure WHERE f_leak(cardno);
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Hash Join
+   Hash Cond: (r.cid = l.cid)
+   Join Filter: f_leak(r.cardno)
+   ->  Seq Scan on credit_cards r
+   ->  Hash
+         ->  Seq Scan on customer l
+               Filter: (name = (getpgusername())::text)
+(7 rows)
+
+SELECT * FROM your_credit_card_secure WHERE f_leakproof(cardno);
+NOTICE:  f_leakproof => 1111-2222-3333-4444
+NOTICE:  f_leakproof => 5555-6666-7777-8888
+NOTICE:  f_leakproof => 9801-2345-6789-0123
+ name  | cid |       cardno        | expired  
+-------+-----+---------------------+----------
+ alice | 101 | 1111-2222-3333-4444 | Aug-2012
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM your_credit_card_secure WHERE f_leakproof(cardno);
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Hash Join
+   Hash Cond: (r.cid = l.cid)
+   ->  Seq Scan on credit_cards r
+         Filter: f_leakproof(cardno)
+   ->  Hash
+         ->  Seq Scan on customer l
+               Filter: (name = (getpgusername())::text)
+(7 rows)
+
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 86cfc51..43cf622 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -191,6 +191,23 @@ AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
 SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
 And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
 
+-- the view shall be marked as 'security view'
+CREATE SECURITY VIEW mysecview1
+       AS SELECT * FROM tbl1 WHERE a > 0;
+SELECT relname, relkind, relissecbarrier FROM pg_class
+       WHERE oid = 'mysecview1'::regclass;
+
+-- 'security view' flag shall be preserved
+CREATE OR REPLACE VIEW mysecview1
+       AS SELECT * FROM tbl1 WHERE a < 0;
+SELECT relname, relkind, relissecbarrier FROM pg_class
+       WHERE oid = 'mysecview1'::regclass;
+-- the default of 'security view' is false
+CREATE OR REPLACE VIEW mysecview2
+       AS SELECT * FROM tbl1 WHERE a = 0;
+SELECT relname, relkind, relissecbarrier FROM pg_class
+       WHERE oid = 'mysecview2'::regclass;
+
 DROP SCHEMA temp_view_test CASCADE;
 DROP SCHEMA testviewschm2 CASCADE;
 
diff --git a/src/test/regress/sql/select_views.sql b/src/test/regress/sql/select_views.sql
index 14f1be8..d1ac965 100644
--- a/src/test/regress/sql/select_views.sql
+++ b/src/test/regress/sql/select_views.sql
@@ -8,3 +8,68 @@ SELECT * FROM street;
 SELECT name, #thepath FROM iexit ORDER BY 1, 2;
 
 SELECT * FROM toyemp WHERE name = 'sharon';
+
+--
+-- Test for Leaky view scenario
+--
+CREATE USER alice;
+CREATE FUNCTION f_leak (text)
+       RETURNS bool LANGUAGE 'plpgsql'
+       COST 0.0000001
+       AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
+CREATE FUNCTION f_leakproof (text)
+       RETURNS bool LANGUAGE 'plpgsql' LEAKPROOF
+       COST 0.0000001
+       AS 'BEGIN RAISE NOTICE ''f_leakproof => %'', $1; RETURN true; END';
+CREATE TABLE customer (
+       cid	int primary key,
+       name	text,
+       addr	text,
+       passwd	text
+);
+CREATE TABLE credit_cards (
+       cid	int references customer(cid),
+       cardno	text,
+       expired	text
+);
+INSERT INTO customer
+       VALUES (101, 'alice', 'Japan',   'passwd123'),
+              (102, 'bob',   'USA',     'beafsteak'),
+              (103, 'eve',   'Germany', 'hamburger');
+INSERT INTO credit_cards
+       VALUES (101, '1111-2222-3333-4444', 'Aug-2012'),
+       	      (102, '5555-6666-7777-8888', 'Nov-2016'),
+	      (103, '9801-2345-6789-0123', 'Jan-2018');
+
+CREATE VIEW your_property_normal AS
+       SELECT * FROM customer WHERE name = getpgusername();
+CREATE SECURITY VIEW your_property_secure AS
+       SELECT * FROM customer WHERE name = getpgusername();
+CREATE VIEW your_credit_card_normal AS
+       SELECT l.name, r.* FROM customer l NATURAL JOIN credit_cards r
+       WHERE name = getpgusername();
+CREATE SECURITY VIEW your_credit_card_secure AS
+       SELECT l.name, r.* FROM customer l NATURAL JOIN credit_cards r
+       WHERE name = getpgusername();
+
+GRANT SELECT ON your_property_normal TO public;
+GRANT SELECT ON your_property_secure TO public;
+GRANT SELECT ON your_credit_card_normal TO public;
+GRANT SELECT ON your_credit_card_secure TO public;
+---
+--- Run leaky view scenarios
+---
+SET SESSION AUTHORIZATION alice;
+
+SELECT * FROM your_property_normal WHERE f_leak(passwd);
+EXPLAIN (COSTS OFF) SELECT * FROM your_property_normal WHERE f_leak(passwd);
+
+SELECT * FROM your_property_secure WHERE f_leak(passwd);
+EXPLAIN (COSTS OFF) SELECT * FROM your_property_secure WHERE f_leak(passwd);
+
+SELECT * FROM your_credit_card_normal WHERE f_leak(cardno);
+EXPLAIN (COSTS OFF) SELECT * FROM your_credit_card_normal WHERE f_leak(cardno);
+SELECT * FROM your_credit_card_secure WHERE f_leak(cardno);
+EXPLAIN (COSTS OFF) SELECT * FROM your_credit_card_secure WHERE f_leak(cardno);
+SELECT * FROM your_credit_card_secure WHERE f_leakproof(cardno);
+EXPLAIN (COSTS OFF) SELECT * FROM your_credit_card_secure WHERE f_leakproof(cardno);