From b12d83248047ea4e5b01c07392f2ffbc1347b41c Mon Sep 17 00:00:00 2001 From: Christoph Heiss Date: Wed, 2 Feb 2022 17:33:00 +0100 Subject: [PATCH v4 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 --- src/test/regress/expected/create_view.out | 42 ++++++++++++--- src/test/regress/expected/rowsecurity.out | 65 ++++++++++++++++++++++- src/test/regress/sql/create_view.sql | 26 +++++++-- src/test/regress/sql/rowsecurity.sql | 44 +++++++++++++++ 4 files changed, 165 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..856c25c085 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,59 @@ 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) + +RESET SESSION AUTHORIZATION; +CREATE TABLE sivt1 (x int); +CREATE VIEW v1t WITH (security_invoker=true) AS +SELECT * FROM sivt1; +ALTER VIEW v1t OWNER TO regress_rls_group1; +GRANT INSERT, UPDATE, DELETE ON sivt1 TO regress_rls_group1; +GRANT SELECT ON sivt1 TO regress_rls_group2; +GRANT SELECT, INSERT, UPDATE, DELETE ON v1t TO regress_rls_group2; +SET SESSION AUTHORIZATION regress_rls_carol; +SELECT * FROM v1t; + x +--- +(0 rows) + +INSERT INTO v1t values (1); +ERROR: permission denied for table sivt1 +UPDATE v1t SET x = 2; +ERROR: permission denied for table sivt1 +DELETE FROM v1t; +ERROR: permission denied for table sivt1 -- -- Table inheritance and RLS policy -- @@ -3987,11 +4045,16 @@ RESET SESSION AUTHORIZATION; -- RESET SESSION AUTHORIZATION; DROP SCHEMA regress_rls_schema CASCADE; -NOTICE: drop cascades to 29 other objects +NOTICE: drop cascades to 34 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 sivt1 +drop cascades to view v1t 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..fc005af98f 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,45 @@ 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; + +RESET SESSION AUTHORIZATION; + +CREATE TABLE sivt1 (x int); +CREATE VIEW v1t WITH (security_invoker=true) AS +SELECT * FROM sivt1; +ALTER VIEW v1t OWNER TO regress_rls_group1; + +GRANT INSERT, UPDATE, DELETE ON sivt1 TO regress_rls_group1; +GRANT SELECT ON sivt1 TO regress_rls_group2; +GRANT SELECT, INSERT, UPDATE, DELETE ON v1t TO regress_rls_group2; + +SET SESSION AUTHORIZATION regress_rls_carol; + +SELECT * FROM v1t; +INSERT INTO v1t values (1); +UPDATE v1t SET x = 2; +DELETE FROM v1t; + -- -- Table inheritance and RLS policy -- -- 2.35.1