From f2eac3772557bca650b4748d2dfc635ef17f724e Mon Sep 17 00:00:00 2001 From: Andreas Lind Date: Wed, 19 Jun 2024 23:08:37 +0200 Subject: [PATCH v1 3/4] Adjust existing tests --- src/test/regress/expected/rules.out | 3 +++ src/test/regress/expected/updatable_views.out | 13 ++++++++++++ src/test/regress/sql/updatable_views.sql | 20 +++++++++++++++++++ 3 files changed, 36 insertions(+) diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 6cf828ca8d0..63134e53495 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1509,6 +1509,7 @@ pg_roles| SELECT pg_authid.rolname, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolbypassrls, + pg_authid.rolbypassleakproof, s.setconfig AS rolconfig, pg_authid.oid FROM (pg_authid @@ -1746,6 +1747,7 @@ pg_shadow| SELECT pg_authid.rolname AS usename, pg_authid.rolsuper AS usesuper, pg_authid.rolreplication AS userepl, pg_authid.rolbypassrls AS usebypassrls, + pg_authid.rolbypassleakproof AS usebypassleakproof, pg_authid.rolpassword AS passwd, pg_authid.rolvaliduntil AS valuntil, s.setconfig AS useconfig @@ -2685,6 +2687,7 @@ pg_user| SELECT usename, usesuper, userepl, usebypassrls, + usebypassleakproof, '********'::text AS passwd, valuntil, useconfig diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 095df0a670c..e577c02cf81 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -2973,6 +2973,9 @@ SELECT table_name, column_name, is_updatable rw_view1 | person | YES (1 row) +CREATE USER regress_view_user4 NOBYPASSLEAKPROOF; +GRANT ALL ON rw_view1 TO regress_view_user4; +SET SESSION AUTHORIZATION regress_view_user4; SELECT * FROM rw_view1 WHERE snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Harry @@ -3033,6 +3036,7 @@ MERGE INTO rw_view1 t -> Values Scan on "*VALUES*" (7 rows) +RESET SESSION AUTHORIZATION; -- security barrier view on top of security barrier view CREATE VIEW rw_view2 WITH (security_barrier = true) AS SELECT * FROM rw_view1 WHERE snoop(person); @@ -3061,6 +3065,9 @@ SELECT table_name, column_name, is_updatable rw_view2 | person | YES (1 row) +CREATE USER regress_view_user5 NOBYPASSLEAKPROOF; +GRANT ALL ON rw_view2 TO regress_view_user5; +SET SESSION AUTHORIZATION regress_view_user5; SELECT * FROM rw_view2 WHERE snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Tom @@ -3130,6 +3137,7 @@ MERGE INTO rw_view2 t -> Values Scan on "*VALUES*" (6 rows) +RESET SESSION AUTHORIZATION; DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 @@ -3224,6 +3232,10 @@ CREATE VIEW v1 WITH (security_barrier=true) AS SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d FROM t1 WHERE a > 5 AND EXISTS(SELECT 1 FROM t12 WHERE t12.a = t1.a); +CREATE USER regress_view_user6 NOBYPASSLEAKPROOF; +GRANT ALL ON t1 TO regress_view_user6; +GRANT ALL ON v1 TO regress_view_user6; +SET SESSION AUTHORIZATION regress_view_user6; SELECT * FROM v1 WHERE a=3; -- should not see anything a | b | c | d ---+---+---+--- @@ -3381,6 +3393,7 @@ TABLE t1; -- verify all a<=5 are intact 5 | 5 | t111 (20 rows) +RESET SESSION AUTHORIZATION; DROP TABLE t1, t11, t12, t111 CASCADE; NOTICE: drop cascades to view v1 DROP FUNCTION snoop(anyelement); diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index c071fffc116..0ef3fe35913 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -1539,7 +1539,12 @@ SELECT table_name, column_name, is_updatable WHERE table_name = 'rw_view1' ORDER BY ordinal_position; +CREATE USER regress_view_user4 NOBYPASSLEAKPROOF; +GRANT ALL ON rw_view1 TO regress_view_user4; +SET SESSION AUTHORIZATION regress_view_user4; + SELECT * FROM rw_view1 WHERE snoop(person); + UPDATE rw_view1 SET person=person WHERE snoop(person); DELETE FROM rw_view1 WHERE NOT snoop(person); MERGE INTO rw_view1 t @@ -1553,6 +1558,7 @@ EXPLAIN (costs off) MERGE INTO rw_view1 t USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person; +RESET SESSION AUTHORIZATION; -- security barrier view on top of security barrier view @@ -1572,7 +1578,12 @@ SELECT table_name, column_name, is_updatable WHERE table_name = 'rw_view2' ORDER BY ordinal_position; +CREATE USER regress_view_user5 NOBYPASSLEAKPROOF; +GRANT ALL ON rw_view2 TO regress_view_user5; +SET SESSION AUTHORIZATION regress_view_user5; + SELECT * FROM rw_view2 WHERE snoop(person); + UPDATE rw_view2 SET person=person WHERE snoop(person); DELETE FROM rw_view2 WHERE NOT snoop(person); MERGE INTO rw_view2 t @@ -1587,6 +1598,8 @@ MERGE INTO rw_view2 t USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person; +RESET SESSION AUTHORIZATION; + DROP TABLE base_tbl CASCADE; -- security barrier view on top of table with rules @@ -1648,6 +1661,11 @@ SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d FROM t1 WHERE a > 5 AND EXISTS(SELECT 1 FROM t12 WHERE t12.a = t1.a); +CREATE USER regress_view_user6 NOBYPASSLEAKPROOF; +GRANT ALL ON t1 TO regress_view_user6; +GRANT ALL ON v1 TO regress_view_user6; +SET SESSION AUTHORIZATION regress_view_user6; + SELECT * FROM v1 WHERE a=3; -- should not see anything SELECT * FROM v1 WHERE a=8; @@ -1668,6 +1686,8 @@ DELETE FROM v1 WHERE snoop(a) AND leakproof(a); -- should not delete everything, TABLE t1; -- verify all a<=5 are intact +RESET SESSION AUTHORIZATION; + DROP TABLE t1, t11, t12, t111 CASCADE; DROP FUNCTION snoop(anyelement); DROP FUNCTION leakproof(anyelement); -- 2.39.2