From ea0771bdd38f9aa12fd97f0c824736dee02f55c1 Mon Sep 17 00:00:00 2001 From: Christoph Heiss 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