0002-PATCH-v3-2-3-Add-regression-tests-for-new-security_i.patch

From a0c221a3e601e303d860fbacd5b4f02474d4093d Mon Sep 17 00:00:00 2001
From: Christoph Heiss <christoph.heiss@cybertec.at>
Date: Tue, 18 Jan 2022 15:34:13 +0100
Subject: [PATCH 2/3] [PATCH v3 2/3] Add regression tests for new
 security_invoker reloption on views

This expands on the current regressions tests for CREATE VIEW and
ROW LEVEL SECURITY-related matters.

Signed-off-by: Christoph Heiss <christoph.heiss@cybertec.at>
---
 src/test/regress/expected/create_view.out | 42 ++++++++++++++++++----
 src/test/regress/expected/rowsecurity.out | 43 ++++++++++++++++++++++-
 src/test/regress/sql/create_view.sql      | 26 +++++++++++---
 src/test/regress/sql/rowsecurity.sql      | 26 ++++++++++++++
 4 files changed, 125 insertions(+), 12 deletions(-)

diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index 509e930fc7..fea893569f 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -261,15 +261,26 @@ CREATE VIEW mysecview3 WITH (security_barrier=false)
        AS SELECT * FROM tbl1 WHERE a < 0;
 CREATE VIEW mysecview4 WITH (security_barrier)
        AS SELECT * FROM tbl1 WHERE a <> 0;
-CREATE VIEW mysecview5 WITH (security_barrier=100)	-- Error
+CREATE VIEW mysecview5 WITH (security_invoker=true)
+       AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview6 WITH (security_invoker=false)
        AS SELECT * FROM tbl1 WHERE a > 100;
+CREATE VIEW mysecview7 WITH (security_invoker)
+       AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview8 WITH (security_barrier=100)	-- Error
+       AS SELECT * FROM tbl1 WHERE a <> 100;
 ERROR:  invalid value for boolean option "security_barrier": 100
-CREATE VIEW mysecview6 WITH (invalid_option)		-- Error
+CREATE VIEW mysecview9 WITH (security_invoker=100)	-- Error
+       AS SELECT * FROM tbl1 WHERE a = 100;
+ERROR:  invalid value for boolean option "security_invoker": 100
+CREATE VIEW mysecview10 WITH (invalid_option)		-- Error
        AS SELECT * FROM tbl1 WHERE a < 100;
 ERROR:  unrecognized parameter "invalid_option"
 SELECT relname, relkind, reloptions FROM pg_class
        WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
-                     'mysecview3'::regclass, 'mysecview4'::regclass)
+                     'mysecview3'::regclass, 'mysecview4'::regclass,
+                     'mysecview5'::regclass, 'mysecview6'::regclass,
+                     'mysecview7'::regclass)
        ORDER BY relname;
   relname   | relkind |        reloptions        
 ------------+---------+--------------------------
@@ -277,7 +288,10 @@ SELECT relname, relkind, reloptions FROM pg_class
  mysecview2 | v       | {security_barrier=true}
  mysecview3 | v       | {security_barrier=false}
  mysecview4 | v       | {security_barrier=true}
-(4 rows)
+ mysecview5 | v       | {security_invoker=true}
+ mysecview6 | v       | {security_invoker=false}
+ mysecview7 | v       | {security_invoker=true}
+(7 rows)
 
 CREATE OR REPLACE VIEW mysecview1
        AS SELECT * FROM tbl1 WHERE a = 256;
@@ -287,9 +301,17 @@ CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true)
        AS SELECT * FROM tbl1 WHERE a < 256;
 CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false)
        AS SELECT * FROM tbl1 WHERE a <> 256;
+CREATE OR REPLACE VIEW mysecview5
+       AS SELECT * FROM tbl1 WHERE a > 256;
+CREATE OR REPLACE VIEW mysecview6 WITH (security_invoker=true)
+       AS SELECT * FROM tbl1 WHERE a < 256;
+CREATE OR REPLACE VIEW mysecview7 WITH (security_invoker=false)
+       AS SELECT * FROM tbl1 WHERE a <> 256;
 SELECT relname, relkind, reloptions FROM pg_class
        WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
-                     'mysecview3'::regclass, 'mysecview4'::regclass)
+                     'mysecview3'::regclass, 'mysecview4'::regclass,
+                     'mysecview5'::regclass, 'mysecview6'::regclass,
+                     'mysecview7'::regclass)
        ORDER BY relname;
   relname   | relkind |        reloptions        
 ------------+---------+--------------------------
@@ -297,7 +319,10 @@ SELECT relname, relkind, reloptions FROM pg_class
  mysecview2 | v       | 
  mysecview3 | v       | {security_barrier=true}
  mysecview4 | v       | {security_barrier=false}
-(4 rows)
+ mysecview5 | v       | 
+ mysecview6 | v       | {security_invoker=true}
+ mysecview7 | v       | {security_invoker=false}
+(7 rows)
 
 -- Check that unknown literals are converted to "text" in CREATE VIEW,
 -- so that we don't end up with unknown-type columns.
@@ -2010,7 +2035,7 @@ drop cascades to view aliased_view_2
 drop cascades to view aliased_view_3
 drop cascades to view aliased_view_4
 DROP SCHEMA testviewschm2 CASCADE;
-NOTICE:  drop cascades to 74 other objects
+NOTICE:  drop cascades to 77 other objects
 DETAIL:  drop cascades to table t1
 drop cascades to view temporal1
 drop cascades to view temporal2
@@ -2031,6 +2056,9 @@ drop cascades to view mysecview1
 drop cascades to view mysecview2
 drop cascades to view mysecview3
 drop cascades to view mysecview4
+drop cascades to view mysecview5
+drop cascades to view mysecview6
+drop cascades to view mysecview7
 drop cascades to view unspecified_types
 drop cascades to table tt1
 drop cascades to table tx1
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 89397e41f0..d10ecaa272 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -8,9 +8,11 @@ DROP USER IF EXISTS regress_rls_alice;
 DROP USER IF EXISTS regress_rls_bob;
 DROP USER IF EXISTS regress_rls_carol;
 DROP USER IF EXISTS regress_rls_dave;
+DROP USER IF EXISTS regress_rls_grace;
 DROP USER IF EXISTS regress_rls_exempt_user;
 DROP ROLE IF EXISTS regress_rls_group1;
 DROP ROLE IF EXISTS regress_rls_group2;
+DROP ROLE IF EXISTS regress_rls_group3;
 DROP SCHEMA IF EXISTS regress_rls_schema CASCADE;
 RESET client_min_messages;
 -- initial setup
@@ -18,11 +20,14 @@ CREATE USER regress_rls_alice NOLOGIN;
 CREATE USER regress_rls_bob NOLOGIN;
 CREATE USER regress_rls_carol NOLOGIN;
 CREATE USER regress_rls_dave NOLOGIN;
+CREATE USER regress_rls_grace NOLOGIN;
 CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
 CREATE ROLE regress_rls_group1 NOLOGIN;
 CREATE ROLE regress_rls_group2 NOLOGIN;
+CREATE ROLE regress_rls_group3 NOLOGIN;
 GRANT regress_rls_group1 TO regress_rls_bob;
 GRANT regress_rls_group2 TO regress_rls_carol;
+GRANT regress_rls_group3 TO regress_rls_grace;
 CREATE SCHEMA regress_rls_schema;
 GRANT ALL ON SCHEMA regress_rls_schema to public;
 SET search_path = regress_rls_schema;
@@ -627,6 +632,39 @@ SELECT * FROM category;
   44 | manga
 (4 rows)
 
+-- Test views with security_invoker reloption set
+RESET SESSION AUTHORIZATION;
+SET row_security TO ON;
+CREATE POLICY p3 ON category FOR ALL TO regress_rls_group3 USING (cname = 'novel');
+CREATE VIEW v1 WITH (security_invoker=true) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION security_invoker_func() RETURNS SETOF category
+    AS 'SELECT * FROM category'
+    LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (security_invoker=true) AS
+SELECT * FROM security_invoker_func();
+GRANT SELECT ON category TO regress_rls_group3;
+GRANT SELECT ON v1 TO regress_rls_group3;
+GRANT SELECT ON v1f TO regress_rls_group3;
+SET SESSION AUTHORIZATION regress_rls_grace;
+SELECT * FROM category;
+ cid | cname 
+-----+-------
+  11 | novel
+(1 row)
+
+SELECT * FROM v1;
+ cid | cname 
+-----+-------
+  11 | novel
+(1 row)
+
+SELECT * FROM v1f;
+ cid | cname 
+-----+-------
+  11 | novel
+(1 row)
+
 --
 -- Table inheritance and RLS policy
 --
@@ -3987,11 +4025,14 @@ RESET SESSION AUTHORIZATION;
 --
 RESET SESSION AUTHORIZATION;
 DROP SCHEMA regress_rls_schema CASCADE;
-NOTICE:  drop cascades to 29 other objects
+NOTICE:  drop cascades to 32 other objects
 DETAIL:  drop cascades to function f_leak(text)
 drop cascades to table uaccount
 drop cascades to table category
 drop cascades to table document
+drop cascades to view v1
+drop cascades to function security_invoker_func()
+drop cascades to view v1f
 drop cascades to table part_document
 drop cascades to table dependent
 drop cascades to table rec1
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 82df4b7cac..290bf59c32 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -214,13 +214,23 @@ CREATE VIEW mysecview3 WITH (security_barrier=false)
        AS SELECT * FROM tbl1 WHERE a < 0;
 CREATE VIEW mysecview4 WITH (security_barrier)
        AS SELECT * FROM tbl1 WHERE a <> 0;
-CREATE VIEW mysecview5 WITH (security_barrier=100)	-- Error
+CREATE VIEW mysecview5 WITH (security_invoker=true)
+       AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview6 WITH (security_invoker=false)
        AS SELECT * FROM tbl1 WHERE a > 100;
-CREATE VIEW mysecview6 WITH (invalid_option)		-- Error
+CREATE VIEW mysecview7 WITH (security_invoker)
+       AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview8 WITH (security_barrier=100)	-- Error
+       AS SELECT * FROM tbl1 WHERE a <> 100;
+CREATE VIEW mysecview9 WITH (security_invoker=100)	-- Error
+       AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview10 WITH (invalid_option)		-- Error
        AS SELECT * FROM tbl1 WHERE a < 100;
 SELECT relname, relkind, reloptions FROM pg_class
        WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
-                     'mysecview3'::regclass, 'mysecview4'::regclass)
+                     'mysecview3'::regclass, 'mysecview4'::regclass,
+                     'mysecview5'::regclass, 'mysecview6'::regclass,
+                     'mysecview7'::regclass)
        ORDER BY relname;
 
 CREATE OR REPLACE VIEW mysecview1
@@ -231,9 +241,17 @@ CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true)
        AS SELECT * FROM tbl1 WHERE a < 256;
 CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false)
        AS SELECT * FROM tbl1 WHERE a <> 256;
+CREATE OR REPLACE VIEW mysecview5
+       AS SELECT * FROM tbl1 WHERE a > 256;
+CREATE OR REPLACE VIEW mysecview6 WITH (security_invoker=true)
+       AS SELECT * FROM tbl1 WHERE a < 256;
+CREATE OR REPLACE VIEW mysecview7 WITH (security_invoker=false)
+       AS SELECT * FROM tbl1 WHERE a <> 256;
 SELECT relname, relkind, reloptions FROM pg_class
        WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
-                     'mysecview3'::regclass, 'mysecview4'::regclass)
+                     'mysecview3'::regclass, 'mysecview4'::regclass,
+                     'mysecview5'::regclass, 'mysecview6'::regclass,
+                     'mysecview7'::regclass)
        ORDER BY relname;
 
 -- Check that unknown literals are converted to "text" in CREATE VIEW,
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 44deb42bad..239a15e3d1 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -11,9 +11,11 @@ DROP USER IF EXISTS regress_rls_alice;
 DROP USER IF EXISTS regress_rls_bob;
 DROP USER IF EXISTS regress_rls_carol;
 DROP USER IF EXISTS regress_rls_dave;
+DROP USER IF EXISTS regress_rls_grace;
 DROP USER IF EXISTS regress_rls_exempt_user;
 DROP ROLE IF EXISTS regress_rls_group1;
 DROP ROLE IF EXISTS regress_rls_group2;
+DROP ROLE IF EXISTS regress_rls_group3;
 
 DROP SCHEMA IF EXISTS regress_rls_schema CASCADE;
 
@@ -24,12 +26,15 @@ CREATE USER regress_rls_alice NOLOGIN;
 CREATE USER regress_rls_bob NOLOGIN;
 CREATE USER regress_rls_carol NOLOGIN;
 CREATE USER regress_rls_dave NOLOGIN;
+CREATE USER regress_rls_grace NOLOGIN;
 CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
 CREATE ROLE regress_rls_group1 NOLOGIN;
 CREATE ROLE regress_rls_group2 NOLOGIN;
+CREATE ROLE regress_rls_group3 NOLOGIN;
 
 GRANT regress_rls_group1 TO regress_rls_bob;
 GRANT regress_rls_group2 TO regress_rls_carol;
+GRANT regress_rls_group3 TO regress_rls_grace;
 
 CREATE SCHEMA regress_rls_schema;
 GRANT ALL ON SCHEMA regress_rls_schema to public;
@@ -225,6 +230,27 @@ SET row_security TO OFF;
 SELECT * FROM document;
 SELECT * FROM category;
 
+-- Test views with security_invoker reloption set
+RESET SESSION AUTHORIZATION;
+SET row_security TO ON;
+CREATE POLICY p3 ON category FOR ALL TO regress_rls_group3 USING (cname = 'novel');
+CREATE VIEW v1 WITH (security_invoker=true) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION security_invoker_func() RETURNS SETOF category
+    AS 'SELECT * FROM category'
+    LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (security_invoker=true) AS
+SELECT * FROM security_invoker_func();
+
+GRANT SELECT ON category TO regress_rls_group3;
+GRANT SELECT ON v1 TO regress_rls_group3;
+GRANT SELECT ON v1f TO regress_rls_group3;
+
+SET SESSION AUTHORIZATION regress_rls_grace;
+SELECT * FROM category;
+SELECT * FROM v1;
+SELECT * FROM v1f;
+
 --
 -- Table inheritance and RLS policy
 --
-- 
2.34.1