From 1a34324021bc6b30d519188cbe6f5d7a0f0e0859 Mon Sep 17 00:00:00 2001 From: jian he Date: Thu, 23 Oct 2025 16:20:43 +0800 Subject: [PATCH v2 1/1] refactor New RLS tests to test policies applied by command type. discussion: https://postgr.es/m/CAEZATCWqnfeChjK=n1V_dYZT4rt4mnq+ybf9c0qXDYTVMsy8pg@mail.gmail.com --- src/test/regress/expected/rowsecurity.out | 37 +++++++++-------------- src/test/regress/sql/rowsecurity.sql | 37 +++++++++-------------- 2 files changed, 30 insertions(+), 44 deletions(-) diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 52da80bcf3a..f21d457c1ec 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -43,35 +43,26 @@ CREATE FUNCTION rls_test_tgt_set_c() RETURNS trigger AS LANGUAGE plpgsql; CREATE TRIGGER rls_test_tgt_set_c BEFORE INSERT OR UPDATE ON rls_test_tgt FOR EACH ROW EXECUTE FUNCTION rls_test_tgt_set_c(); -CREATE FUNCTION sel_using_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'SELECT USING on %.%', $1, $2; RETURN true; END; $$ +CREATE FUNCTION policy_using_fn(text, text, record) RETURNS bool AS + $$ BEGIN RAISE NOTICE '% USING on %.%', $1, $2, $3; RETURN true; END; $$ LANGUAGE plpgsql; -CREATE FUNCTION ins_check_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'INSERT CHECK on %.%', $1, $2; RETURN true; END; $$ - LANGUAGE plpgsql; -CREATE FUNCTION upd_using_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'UPDATE USING on %.%', $1, $2; RETURN true; END; $$ - LANGUAGE plpgsql; -CREATE FUNCTION upd_check_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'UPDATE CHECK on %.%', $1, $2; RETURN true; END; $$ - LANGUAGE plpgsql; -CREATE FUNCTION del_using_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'DELETE USING on %.%', $1, $2; RETURN true; END; $$ +CREATE FUNCTION policy_check_fn(text, text, record) RETURNS bool AS + $$ BEGIN RAISE NOTICE '% CHECK on %.%', $1, $2, $3; RETURN true; END; $$ LANGUAGE plpgsql; CREATE POLICY sel_pol ON rls_test_src FOR SELECT - USING (sel_using_fn('rls_test_src', rls_test_src)); + USING (policy_using_fn('SELECT', 'rls_test_src', rls_test_src)); CREATE POLICY upd_pol ON rls_test_src FOR UPDATE - USING (upd_using_fn('rls_test_src', rls_test_src)) - WITH CHECK (upd_check_fn('rls_test_src', rls_test_src)); + USING (policy_using_fn('UPDATE', 'rls_test_src', rls_test_src)) + WITH CHECK (policy_check_fn('UPDATE', 'rls_test_src', rls_test_src)); CREATE POLICY sel_pol ON rls_test_tgt FOR SELECT - USING (sel_using_fn('rls_test_tgt', rls_test_tgt)); + USING (policy_using_fn('SELECT', 'rls_test_tgt', rls_test_tgt)); CREATE POLICY ins_pol ON rls_test_tgt FOR INSERT - WITH CHECK (ins_check_fn('rls_test_tgt', rls_test_tgt)); + WITH CHECK (policy_check_fn('INSERT', 'rls_test_tgt', rls_test_tgt)); CREATE POLICY upd_pol ON rls_test_tgt FOR UPDATE - USING (upd_using_fn('rls_test_tgt', rls_test_tgt)) - WITH CHECK (upd_check_fn('rls_test_tgt', rls_test_tgt)); + USING (policy_using_fn('UPDATE', 'rls_test_tgt', rls_test_tgt)) + WITH CHECK (policy_check_fn('UPDATE', 'rls_test_tgt', rls_test_tgt)); CREATE POLICY del_pol ON rls_test_tgt FOR DELETE - USING (del_using_fn('rls_test_tgt', rls_test_tgt)); + USING (policy_using_fn('DELETE', 'rls_test_tgt', rls_test_tgt)); GRANT SELECT, UPDATE ON rls_test_src TO public; GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON rls_test_tgt TO public; SET SESSION AUTHORIZATION regress_rls_bob; @@ -162,6 +153,8 @@ NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") INSERT INTO rls_test_tgt VALUES (1, 'tgt b') ON CONFLICT (a) DO NOTHING; NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt b","TGT B") NOTICE: SELECT USING on rls_test_tgt.(1,"tgt b","TGT B") +INSERT INTO rls_test_tgt VALUES (1, 'tgt b') ON CONFLICT DO NOTHING; +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt b","TGT B") BEGIN; INSERT INTO rls_test_tgt VALUES (2, 'tgt a') ON CONFLICT (a) DO UPDATE SET b = 'tgt b'; NOTICE: INSERT CHECK on rls_test_tgt.(2,"tgt a","TGT A") @@ -256,7 +249,7 @@ NOTICE: DELETE USING on rls_test_tgt.(1,"tgt c","TGT C") RESET SESSION AUTHORIZATION; DROP TABLE rls_test_src, rls_test_tgt; DROP FUNCTION rls_test_tgt_set_c; -DROP FUNCTION sel_using_fn, ins_check_fn, upd_using_fn, upd_check_fn, del_using_fn; +DROP FUNCTION policy_using_fn, policy_check_fn; -- BASIC Row-Level Security Scenario SET SESSION AUTHORIZATION regress_rls_alice; CREATE TABLE uaccount ( diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 92423187dd5..7dfdf00a62e 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -57,37 +57,29 @@ CREATE FUNCTION rls_test_tgt_set_c() RETURNS trigger AS CREATE TRIGGER rls_test_tgt_set_c BEFORE INSERT OR UPDATE ON rls_test_tgt FOR EACH ROW EXECUTE FUNCTION rls_test_tgt_set_c(); -CREATE FUNCTION sel_using_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'SELECT USING on %.%', $1, $2; RETURN true; END; $$ +CREATE FUNCTION policy_using_fn(text, text, record) RETURNS bool AS + $$ BEGIN RAISE NOTICE '% USING on %.%', $1, $2, $3; RETURN true; END; $$ LANGUAGE plpgsql; -CREATE FUNCTION ins_check_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'INSERT CHECK on %.%', $1, $2; RETURN true; END; $$ - LANGUAGE plpgsql; -CREATE FUNCTION upd_using_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'UPDATE USING on %.%', $1, $2; RETURN true; END; $$ - LANGUAGE plpgsql; -CREATE FUNCTION upd_check_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'UPDATE CHECK on %.%', $1, $2; RETURN true; END; $$ - LANGUAGE plpgsql; -CREATE FUNCTION del_using_fn(text, record) RETURNS bool AS - $$ BEGIN RAISE NOTICE 'DELETE USING on %.%', $1, $2; RETURN true; END; $$ + +CREATE FUNCTION policy_check_fn(text, text, record) RETURNS bool AS + $$ BEGIN RAISE NOTICE '% CHECK on %.%', $1, $2, $3; RETURN true; END; $$ LANGUAGE plpgsql; CREATE POLICY sel_pol ON rls_test_src FOR SELECT - USING (sel_using_fn('rls_test_src', rls_test_src)); + USING (policy_using_fn('SELECT', 'rls_test_src', rls_test_src)); CREATE POLICY upd_pol ON rls_test_src FOR UPDATE - USING (upd_using_fn('rls_test_src', rls_test_src)) - WITH CHECK (upd_check_fn('rls_test_src', rls_test_src)); + USING (policy_using_fn('UPDATE', 'rls_test_src', rls_test_src)) + WITH CHECK (policy_check_fn('UPDATE', 'rls_test_src', rls_test_src)); CREATE POLICY sel_pol ON rls_test_tgt FOR SELECT - USING (sel_using_fn('rls_test_tgt', rls_test_tgt)); + USING (policy_using_fn('SELECT', 'rls_test_tgt', rls_test_tgt)); CREATE POLICY ins_pol ON rls_test_tgt FOR INSERT - WITH CHECK (ins_check_fn('rls_test_tgt', rls_test_tgt)); + WITH CHECK (policy_check_fn('INSERT', 'rls_test_tgt', rls_test_tgt)); CREATE POLICY upd_pol ON rls_test_tgt FOR UPDATE - USING (upd_using_fn('rls_test_tgt', rls_test_tgt)) - WITH CHECK (upd_check_fn('rls_test_tgt', rls_test_tgt)); + USING (policy_using_fn('UPDATE', 'rls_test_tgt', rls_test_tgt)) + WITH CHECK (policy_check_fn('UPDATE', 'rls_test_tgt', rls_test_tgt)); CREATE POLICY del_pol ON rls_test_tgt FOR DELETE - USING (del_using_fn('rls_test_tgt', rls_test_tgt)); + USING (policy_using_fn('DELETE', 'rls_test_tgt', rls_test_tgt)); GRANT SELECT, UPDATE ON rls_test_src TO public; GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON rls_test_tgt TO public; @@ -114,6 +106,7 @@ DELETE FROM rls_test_tgt RETURNING *; INSERT INTO rls_test_tgt VALUES (1, 'tgt a') ON CONFLICT (a) DO NOTHING; INSERT INTO rls_test_tgt VALUES (1, 'tgt b') ON CONFLICT (a) DO NOTHING; +INSERT INTO rls_test_tgt VALUES (1, 'tgt b') ON CONFLICT DO NOTHING; BEGIN; INSERT INTO rls_test_tgt VALUES (2, 'tgt a') ON CONFLICT (a) DO UPDATE SET b = 'tgt b'; @@ -150,7 +143,7 @@ MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a RESET SESSION AUTHORIZATION; DROP TABLE rls_test_src, rls_test_tgt; DROP FUNCTION rls_test_tgt_set_c; -DROP FUNCTION sel_using_fn, ins_check_fn, upd_using_fn, upd_check_fn, del_using_fn; +DROP FUNCTION policy_using_fn, policy_check_fn; -- BASIC Row-Level Security Scenario -- 2.34.1