v2-0001-refactor-New-RLS-tests-to-test-policies-applied-by-command-typ.no-cfbot

From 1a34324021bc6b30d519188cbe6f5d7a0f0e0859 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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