0002-Add-regression-tests-for-new-security-reloption-for-.patch

From ea0771bdd38f9aa12fd97f0c824736dee02f55c1 Mon Sep 17 00:00:00 2001
From: Christoph Heiss <christoph.heiss@cybertec.at>
Date: Thu, 16 Dec 2021 19:25:24 +0100
Subject: [PATCH 2/3] Add regression tests for new 'security' reloption for
 views

This expands on the current regressions tests for CREATE VIEW and ROW LEVEL
SECURITY-related matters.
---
 src/test/regress/expected/create_view.out | 31 +++++++++++-----
 src/test/regress/expected/rowsecurity.out | 43 ++++++++++++++++++++++-
 src/test/regress/sql/create_view.sql      | 16 ++++++---
 src/test/regress/sql/rowsecurity.sql      | 26 ++++++++++++++
 4 files changed, 102 insertions(+), 14 deletions(-)

diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f50ef76685..4274bdcc15 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -261,23 +261,31 @@ 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=relation_permissions)
+       AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview6 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 mysecview7 WITH (security=invalid)		-- Error
+       AS SELECT * FROM tbl1 WHERE a <> 100;
+ERROR:  invalid value for enum option "security": invalid
+DETAIL:  Only valid value is "relation_permissions".
+CREATE VIEW mysecview8 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)
        ORDER BY relname;
-  relname   | relkind |        reloptions        
-------------+---------+--------------------------
+  relname   | relkind |           reloptions            
+------------+---------+---------------------------------
  mysecview1 | v       | 
  mysecview2 | v       | {security_barrier=true}
  mysecview3 | v       | {security_barrier=false}
  mysecview4 | v       | {security_barrier=true}
-(4 rows)
+ mysecview5 | v       | {security=relation_permissions}
+(5 rows)
 
 CREATE OR REPLACE VIEW mysecview1
        AS SELECT * FROM tbl1 WHERE a = 256;
@@ -287,9 +295,12 @@ 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;
 SELECT relname, relkind, reloptions FROM pg_class
        WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
-                     'mysecview3'::regclass, 'mysecview4'::regclass)
+                     'mysecview3'::regclass, 'mysecview4'::regclass,
+                     'mysecview5'::regclass)
        ORDER BY relname;
   relname   | relkind |        reloptions        
 ------------+---------+--------------------------
@@ -297,7 +308,8 @@ SELECT relname, relkind, reloptions FROM pg_class
  mysecview2 | v       | 
  mysecview3 | v       | {security_barrier=true}
  mysecview4 | v       | {security_barrier=false}
-(4 rows)
+ mysecview5 | v       | 
+(5 rows)
 
 -- Check that unknown literals are converted to "text" in CREATE VIEW,
 -- so that we don't end up with unknown-type columns.
@@ -1994,7 +2006,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 73 other objects
+NOTICE:  drop cascades to 74 other objects
 DETAIL:  drop cascades to table t1
 drop cascades to view temporal1
 drop cascades to view temporal2
@@ -2015,6 +2027,7 @@ 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 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..99ce4ce2e0 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=relation_permissions 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=relation_permissions) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION relation_permissions_func() RETURNS SETOF category
+    AS 'SELECT * FROM category'
+    LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (security=relation_permissions) AS
+SELECT * FROM relation_permissions_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 relation_permissions_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 bdda56e8de..9e8e768b3d 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -214,13 +214,18 @@ 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=relation_permissions)
+       AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview6 WITH (security_barrier=100)	-- Error
        AS SELECT * FROM tbl1 WHERE a > 100;
-CREATE VIEW mysecview6 WITH (invalid_option)		-- Error
+CREATE VIEW mysecview7 WITH (security=invalid)		-- Error
+       AS SELECT * FROM tbl1 WHERE a <> 100;
+CREATE VIEW mysecview8 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)
        ORDER BY relname;
 
 CREATE OR REPLACE VIEW mysecview1
@@ -231,9 +236,12 @@ 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;
 SELECT relname, relkind, reloptions FROM pg_class
        WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
-                     'mysecview3'::regclass, 'mysecview4'::regclass)
+                     'mysecview3'::regclass, 'mysecview4'::regclass,
+                     'mysecview5'::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..f30c08b795 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=relation_permissions 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=relation_permissions) AS
+SELECT * FROM category;
+CREATE OR REPLACE FUNCTION relation_permissions_func() RETURNS SETOF category
+    AS 'SELECT * FROM category'
+    LANGUAGE SQL STABLE STRICT;
+CREATE VIEW v1f WITH (security=relation_permissions) AS
+SELECT * FROM relation_permissions_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