On Thu, 23 Oct 2025 at 09:23, jian he <[email protected]> wrote: > > On Tue, Oct 21, 2025 at 12:01 AM Viktor Holmberg <[email protected]> wrote: > > > > So patch 0001, attached, adds a new set of regression tests, near the > > start of rowsecurity.sql, which specifically tests which policies are > > applied for each command variant. > > > hi. > I only applied the 0001. > > it would be better to add some comments to the regress tests, IMHO. > for example, for below: > +SELECT * FROM rls_test_src FOR UPDATE; > +SELECT * FROM rls_test_src FOR NO KEY UPDATE; > +SELECT * FROM rls_test_src FOR SHARE; > +SELECT * FROM rls_test_src FOR KEY SHARE; > > we could add a comment such as: > "Expect both UPDATE and the SELECT command policies to be invoked for > these four below query".
Thank you both for the reviews. Attached is a new version with more comments in the tests, focusing on what is expected from each test. > The 0001 regess tests define several functions: sel_using_fn, > ins_check_fn, upd_using_fn, > upd_check_fn, and del_using_fn. > IMHO, these could be simplified (we probably only need two functions). Good point. Actually it can be done with just one function, further reducing the amount of test code. A recent commit reminded me that COPY ... TO also applies RLS SELECT policies (and so does TABLE, though I doubt many people use that), so I think it's worth testing and documenting those too. Updated patches attached. Regards, Dean
From 076102e057dd301e23791eac0956193d6428ac3a Mon Sep 17 00:00:00 2001 From: Dean Rasheed <[email protected]> Date: Thu, 27 Mar 2025 14:08:09 +0000 Subject: [PATCH v2 1/2] New RLS tests to test policies applied by command type. The existing RLS tests focus on the outcomes of various testing scenarios, rather than the exact policies applied. These new tests list out the policies applied for each command type, including the different paths through INSERT ... ON CONFLICT and MERGE, making it easier to verify correct behaviour. Author: Dean Rasheed <[email protected]> Reviewed-by: Viktor Holmberg <[email protected]> Reviewed-by: Jian He <[email protected]> Discussion: https://postgr.es/m/CAEZATCWqnfeChjK=n1V_dYZT4rt4mnq+ybf9c0qXDYTVMsy8pg@mail.gmail.com --- src/test/regress/expected/rowsecurity.out | 259 ++++++++++++++++++++++ src/test/regress/sql/rowsecurity.sql | 139 ++++++++++++ 2 files changed, 398 insertions(+) diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 42b78a24603..c958ef4d70a 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -31,6 +31,265 @@ CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool COST 0.0000001 LANGUAGE plpgsql AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END'; GRANT EXECUTE ON FUNCTION f_leak(text) TO public; +-- +-- Test policies applied by command type +-- +SET SESSION AUTHORIZATION regress_rls_alice; +-- setup source table (for MERGE operations) +CREATE TABLE rls_test_src (a int PRIMARY KEY, b text); +ALTER TABLE rls_test_src ENABLE ROW LEVEL SECURITY; +GRANT SELECT, UPDATE ON rls_test_src TO public; +INSERT INTO rls_test_src VALUES (1, 'src a'); +-- setup target table with a column set by a BEFORE ROW trigger +-- (policies should always see values set by the trigger) +CREATE TABLE rls_test_tgt (a int PRIMARY KEY, b text, c text); +ALTER TABLE rls_test_tgt ENABLE ROW LEVEL SECURITY; +GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON rls_test_tgt TO public; +CREATE FUNCTION rls_test_tgt_set_c() RETURNS trigger AS + $$ BEGIN new.c = upper(new.b); RETURN new; END; $$ + 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(); +-- setup a complete set of policies that emit NOTICE messages when applied +CREATE FUNCTION rls_test_policy_fn(text, record) RETURNS bool AS + $$ BEGIN RAISE NOTICE '%.%', $1, $2; RETURN true; END; $$ + LANGUAGE plpgsql; +CREATE POLICY sel_pol ON rls_test_src FOR SELECT + USING (rls_test_policy_fn('SELECT USING on rls_test_src', rls_test_src)); +CREATE POLICY upd_pol ON rls_test_src FOR UPDATE + USING (rls_test_policy_fn('UPDATE USING on rls_test_src', rls_test_src)) + WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_src', rls_test_src)); +CREATE POLICY sel_pol ON rls_test_tgt FOR SELECT + USING (rls_test_policy_fn('SELECT USING on rls_test_tgt', rls_test_tgt)); +CREATE POLICY ins_pol ON rls_test_tgt FOR INSERT + WITH CHECK (rls_test_policy_fn('INSERT CHECK on rls_test_tgt', rls_test_tgt)); +CREATE POLICY upd_pol ON rls_test_tgt FOR UPDATE + USING (rls_test_policy_fn('UPDATE USING on rls_test_tgt', rls_test_tgt)) + WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_tgt', rls_test_tgt)); +CREATE POLICY del_pol ON rls_test_tgt FOR DELETE + USING (rls_test_policy_fn('DELETE USING on rls_test_tgt', rls_test_tgt)); +-- test policies applied to regress_rls_bob +SET SESSION AUTHORIZATION regress_rls_bob; +-- SELECT, COPY ... TO, and TABLE should only apply SELECT USING policy clause +SELECT * FROM rls_test_src; +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +COPY rls_test_src TO stdout; +NOTICE: SELECT USING on rls_test_src.(1,"src a") +1 src a +TABLE rls_test_src; +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +-- SELECT ... FOR UPDATE/SHARE should also apply UPDATE USING policy clause +SELECT * FROM rls_test_src FOR UPDATE; +NOTICE: UPDATE USING on rls_test_src.(1,"src a") +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +SELECT * FROM rls_test_src FOR NO KEY UPDATE; +NOTICE: UPDATE USING on rls_test_src.(1,"src a") +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +SELECT * FROM rls_test_src FOR SHARE; +NOTICE: UPDATE USING on rls_test_src.(1,"src a") +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +SELECT * FROM rls_test_src FOR KEY SHARE; +NOTICE: UPDATE USING on rls_test_src.(1,"src a") +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +-- plain INSERT should apply INSERT CHECK policy clause +INSERT INTO rls_test_tgt VALUES (1, 'tgt a'); +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +-- INSERT ... RETURNING should also apply SELECT USING policy clause +TRUNCATE rls_test_tgt; +INSERT INTO rls_test_tgt VALUES (1, 'tgt a') RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") + a | b | c +---+-------+------- + 1 | tgt a | TGT A +(1 row) + +-- UPDATE without WHERE or RETURNING should only apply UPDATE policy clauses +UPDATE rls_test_tgt SET b = 'tgt b'; +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt b","TGT B") +-- UPDATE with WHERE or RETURNING should also apply SELECT USING policy clause +-- (to both old and new values) +UPDATE rls_test_tgt SET b = 'tgt c' WHERE a = 1; +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") +UPDATE rls_test_tgt SET b = 'tgt d' RETURNING *; +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt d","TGT D") + a | b | c +---+-------+------- + 1 | tgt d | TGT D +(1 row) + +-- DELETE without WHERE or RETURNING should only apply DELETE USING policy clause +BEGIN; DELETE FROM rls_test_tgt; ROLLBACK; +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt d","TGT D") +-- DELETE with WHERE or RETURNING should also apply SELECT USING policy clause +BEGIN; DELETE FROM rls_test_tgt WHERE a = 1; ROLLBACK; +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt d","TGT D") +DELETE FROM rls_test_tgt RETURNING *; +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt d","TGT D") + a | b | c +---+-------+------- + 1 | tgt d | TGT D +(1 row) + +-- INSERT ... ON CONFLICT DO NOTHING should apply INSERT CHECK and SELECT USING +-- policy clauses (to new value, whether it conflicts or not) +INSERT INTO rls_test_tgt VALUES (1, 'tgt a') ON CONFLICT (a) DO NOTHING; +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +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 ... ON CONFLICT DO NOTHING without an arbiter clause only applies +-- INSERT CHECK policy clause +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") +-- INSERT ... ON CONFLICT DO UPDATE should apply INSERT CHECK and SELECT USING +-- policy clauses to values proposed for insert. In the event of a conflict it +-- should also apply UPDATE and SELECT policies to old and new values, like +-- UPDATE ... WHERE. +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") +NOTICE: SELECT USING on rls_test_tgt.(2,"tgt a","TGT A") +INSERT INTO rls_test_tgt VALUES (2, 'tgt c') ON CONFLICT (a) DO UPDATE SET b = 'tgt d'; +NOTICE: INSERT CHECK on rls_test_tgt.(2,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(2,"tgt c","TGT C") +NOTICE: UPDATE USING on rls_test_tgt.(2,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(2,"tgt a","TGT A") +NOTICE: UPDATE CHECK on rls_test_tgt.(2,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(2,"tgt d","TGT D") +INSERT INTO rls_test_tgt VALUES (3, 'tgt a') ON CONFLICT (a) DO UPDATE SET b = 'tgt b' RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(3,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(3,"tgt a","TGT A") + a | b | c +---+-------+------- + 3 | tgt a | TGT A +(1 row) + +INSERT INTO rls_test_tgt VALUES (3, 'tgt c') ON CONFLICT (a) DO UPDATE SET b = 'tgt d' RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(3,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(3,"tgt c","TGT C") +NOTICE: UPDATE USING on rls_test_tgt.(3,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(3,"tgt a","TGT A") +NOTICE: UPDATE CHECK on rls_test_tgt.(3,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(3,"tgt d","TGT D") + a | b | c +---+-------+------- + 3 | tgt d | TGT D +(1 row) + +ROLLBACK; +-- MERGE should always apply SELECT USING policy clauses to both source and +-- target rows +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN NOT MATCHED THEN DO NOTHING; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +-- MERGE ... INSERT should behave like INSERT on target table +-- (SELECT policy applied to target, if RETURNING is specified) +TRUNCATE rls_test_tgt; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN NOT MATCHED THEN INSERT VALUES (1, 'tgt a'); +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +TRUNCATE rls_test_tgt; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN NOT MATCHED THEN INSERT VALUES (1, 'tgt a') + RETURNING *; +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") + a | b | a | b | c +---+-------+---+-------+------- + 1 | src a | 1 | tgt a | TGT A +(1 row) + +-- MERGE ... UPDATE should behave like UPDATE ... WHERE on target table +-- (join clause is like WHERE, so SELECT policies are always applied) +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN UPDATE SET b = 'tgt b'; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt b","TGT B") +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN UPDATE SET b = 'tgt c' + RETURNING *; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") + a | b | a | b | c +---+-------+---+-------+------- + 1 | src a | 1 | tgt c | TGT C +(1 row) + +-- MERGE ... DELETE should behave like DELETE ... WHERE on target table +-- (join clause is like WHERE, so SELECT policies are always applied) +BEGIN; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN DELETE; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt c","TGT C") +ROLLBACK; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN DELETE + RETURNING *; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt c","TGT C") + a | b | a | b | c +---+-------+---+-------+------- + 1 | src a | 1 | tgt c | TGT C +(1 row) + +-- Tidy up +RESET SESSION AUTHORIZATION; +DROP TABLE rls_test_src, rls_test_tgt; +DROP FUNCTION rls_test_tgt_set_c; +DROP FUNCTION rls_test_policy_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 2d1be543391..5d923c5ca3b 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -41,6 +41,145 @@ CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END'; GRANT EXECUTE ON FUNCTION f_leak(text) TO public; +-- +-- Test policies applied by command type +-- +SET SESSION AUTHORIZATION regress_rls_alice; + +-- setup source table (for MERGE operations) +CREATE TABLE rls_test_src (a int PRIMARY KEY, b text); +ALTER TABLE rls_test_src ENABLE ROW LEVEL SECURITY; +GRANT SELECT, UPDATE ON rls_test_src TO public; +INSERT INTO rls_test_src VALUES (1, 'src a'); + +-- setup target table with a column set by a BEFORE ROW trigger +-- (policies should always see values set by the trigger) +CREATE TABLE rls_test_tgt (a int PRIMARY KEY, b text, c text); +ALTER TABLE rls_test_tgt ENABLE ROW LEVEL SECURITY; +GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON rls_test_tgt TO public; + +CREATE FUNCTION rls_test_tgt_set_c() RETURNS trigger AS + $$ BEGIN new.c = upper(new.b); RETURN new; END; $$ + 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(); + +-- setup a complete set of policies that emit NOTICE messages when applied +CREATE FUNCTION rls_test_policy_fn(text, record) RETURNS bool AS + $$ BEGIN RAISE NOTICE '%.%', $1, $2; RETURN true; END; $$ + LANGUAGE plpgsql; + +CREATE POLICY sel_pol ON rls_test_src FOR SELECT + USING (rls_test_policy_fn('SELECT USING on rls_test_src', rls_test_src)); +CREATE POLICY upd_pol ON rls_test_src FOR UPDATE + USING (rls_test_policy_fn('UPDATE USING on rls_test_src', rls_test_src)) + WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_src', rls_test_src)); + +CREATE POLICY sel_pol ON rls_test_tgt FOR SELECT + USING (rls_test_policy_fn('SELECT USING on rls_test_tgt', rls_test_tgt)); +CREATE POLICY ins_pol ON rls_test_tgt FOR INSERT + WITH CHECK (rls_test_policy_fn('INSERT CHECK on rls_test_tgt', rls_test_tgt)); +CREATE POLICY upd_pol ON rls_test_tgt FOR UPDATE + USING (rls_test_policy_fn('UPDATE USING on rls_test_tgt', rls_test_tgt)) + WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_tgt', rls_test_tgt)); +CREATE POLICY del_pol ON rls_test_tgt FOR DELETE + USING (rls_test_policy_fn('DELETE USING on rls_test_tgt', rls_test_tgt)); + +-- test policies applied to regress_rls_bob +SET SESSION AUTHORIZATION regress_rls_bob; + +-- SELECT, COPY ... TO, and TABLE should only apply SELECT USING policy clause +SELECT * FROM rls_test_src; +COPY rls_test_src TO stdout; +TABLE rls_test_src; + +-- SELECT ... FOR UPDATE/SHARE should also apply UPDATE USING policy clause +SELECT * FROM rls_test_src FOR UPDATE; +SELECT * FROM rls_test_src FOR NO KEY UPDATE; +SELECT * FROM rls_test_src FOR SHARE; +SELECT * FROM rls_test_src FOR KEY SHARE; + +-- plain INSERT should apply INSERT CHECK policy clause +INSERT INTO rls_test_tgt VALUES (1, 'tgt a'); + +-- INSERT ... RETURNING should also apply SELECT USING policy clause +TRUNCATE rls_test_tgt; +INSERT INTO rls_test_tgt VALUES (1, 'tgt a') RETURNING *; + +-- UPDATE without WHERE or RETURNING should only apply UPDATE policy clauses +UPDATE rls_test_tgt SET b = 'tgt b'; + +-- UPDATE with WHERE or RETURNING should also apply SELECT USING policy clause +-- (to both old and new values) +UPDATE rls_test_tgt SET b = 'tgt c' WHERE a = 1; +UPDATE rls_test_tgt SET b = 'tgt d' RETURNING *; + +-- DELETE without WHERE or RETURNING should only apply DELETE USING policy clause +BEGIN; DELETE FROM rls_test_tgt; ROLLBACK; + +-- DELETE with WHERE or RETURNING should also apply SELECT USING policy clause +BEGIN; DELETE FROM rls_test_tgt WHERE a = 1; ROLLBACK; +DELETE FROM rls_test_tgt RETURNING *; + +-- INSERT ... ON CONFLICT DO NOTHING should apply INSERT CHECK and SELECT USING +-- policy clauses (to new value, whether it conflicts or not) +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 ... ON CONFLICT DO NOTHING without an arbiter clause only applies +-- INSERT CHECK policy clause +INSERT INTO rls_test_tgt VALUES (1, 'tgt b') ON CONFLICT DO NOTHING; + +-- INSERT ... ON CONFLICT DO UPDATE should apply INSERT CHECK and SELECT USING +-- policy clauses to values proposed for insert. In the event of a conflict it +-- should also apply UPDATE and SELECT policies to old and new values, like +-- UPDATE ... WHERE. +BEGIN; +INSERT INTO rls_test_tgt VALUES (2, 'tgt a') ON CONFLICT (a) DO UPDATE SET b = 'tgt b'; +INSERT INTO rls_test_tgt VALUES (2, 'tgt c') ON CONFLICT (a) DO UPDATE SET b = 'tgt d'; +INSERT INTO rls_test_tgt VALUES (3, 'tgt a') ON CONFLICT (a) DO UPDATE SET b = 'tgt b' RETURNING *; +INSERT INTO rls_test_tgt VALUES (3, 'tgt c') ON CONFLICT (a) DO UPDATE SET b = 'tgt d' RETURNING *; +ROLLBACK; + +-- MERGE should always apply SELECT USING policy clauses to both source and +-- target rows +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN NOT MATCHED THEN DO NOTHING; + +-- MERGE ... INSERT should behave like INSERT on target table +-- (SELECT policy applied to target, if RETURNING is specified) +TRUNCATE rls_test_tgt; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN NOT MATCHED THEN INSERT VALUES (1, 'tgt a'); +TRUNCATE rls_test_tgt; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN NOT MATCHED THEN INSERT VALUES (1, 'tgt a') + RETURNING *; + +-- MERGE ... UPDATE should behave like UPDATE ... WHERE on target table +-- (join clause is like WHERE, so SELECT policies are always applied) +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN UPDATE SET b = 'tgt b'; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN UPDATE SET b = 'tgt c' + RETURNING *; + +-- MERGE ... DELETE should behave like DELETE ... WHERE on target table +-- (join clause is like WHERE, so SELECT policies are always applied) +BEGIN; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN DELETE; +ROLLBACK; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN DELETE + RETURNING *; + +-- Tidy up +RESET SESSION AUTHORIZATION; +DROP TABLE rls_test_src, rls_test_tgt; +DROP FUNCTION rls_test_tgt_set_c; +DROP FUNCTION rls_test_policy_fn; + -- BASIC Row-Level Security Scenario SET SESSION AUTHORIZATION regress_rls_alice; -- 2.51.0
From e5f3fe63011b9912f7297d8f18077308ac91c2be Mon Sep 17 00:00:00 2001 From: Dean Rasheed <[email protected]> Date: Thu, 27 Mar 2025 14:19:15 +0000 Subject: [PATCH v2 2/2] doc: Improve the "Policies Applied by Command Type" table. This table was missing MERGE ... THEN DELETE and some of the policies applied during INSERT ... ON CONFLICT and MERGE. Fix that, and try to improve readability by listing the various MERGE cases separately, rather than together with INSERT/UPDATE/DELETE. In addition, correct the paragraph above the table to note that an INSERT ... ON CONFLICT DO NOTHING/UPDATE checks all new rows against the INSERT policy expressions, regardless of whether the rows end up being inserted. Author: Dean Rasheed <[email protected]> Reviewed-by: Viktor Holmberg <[email protected]> Discussion: https://postgr.es/m/CAEZATCWqnfeChjK=n1V_dYZT4rt4mnq+ybf9c0qXDYTVMsy8pg@mail.gmail.com --- doc/src/sgml/ref/create_policy.sgml | 72 +++++++++++++++++++++++------ 1 file changed, 58 insertions(+), 14 deletions(-) diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index e76c342d3da..d6299a40844 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -293,9 +293,9 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable </para> <para> Note that <literal>INSERT</literal> with <literal>ON CONFLICT DO - UPDATE</literal> checks <literal>INSERT</literal> policies' - <literal>WITH CHECK</literal> expressions only for rows appended - to the relation by the <literal>INSERT</literal> path. + NOTHING/UPDATE</literal> checks <literal>INSERT</literal> policies' + <literal>WITH CHECK</literal> expressions for all rows, regardless + of whether they end up being inserted. </para> </listitem> </varlistentry> @@ -424,7 +424,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable </thead> <tbody> <row> - <entry><command>SELECT</command></entry> + <entry><command>SELECT</command> / <command>COPY ... TO</command> / <command>TABLE</command></entry> <entry>Existing row</entry> <entry>—</entry> <entry>—</entry> @@ -440,15 +440,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable <entry>—</entry> </row> <row> - <entry><command>INSERT</command> / <command>MERGE ... THEN INSERT</command></entry> - <entry>—</entry> - <entry>New row</entry> - <entry>—</entry> - <entry>—</entry> - <entry>—</entry> - </row> - <row> - <entry><command>INSERT ... RETURNING</command></entry> + <entry><command>INSERT</command></entry> <entry> New row <footnote id="rls-select-priv"> <para> @@ -464,7 +456,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable <entry>—</entry> </row> <row> - <entry><command>UPDATE</command> / <command>MERGE ... THEN UPDATE</command></entry> + <entry><command>UPDATE</command></entry> <entry> Existing & new rows <footnoteref linkend="rls-select-priv"/> </entry> @@ -483,14 +475,66 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable <entry>—</entry> <entry>Existing row</entry> </row> + <row> + <entry><command>INSERT ... ON CONFLICT</command></entry> + <entry>New row</entry> + <entry>New row</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + </row> <row> <entry><command>ON CONFLICT DO UPDATE</command></entry> + <entry> + Existing & new rows <footnote id="rls-on-conflict-update-priv"> + <para> + New row of the auxiliary <command>UPDATE</command> command, which + might be different from the new row of the original + <command>INSERT</command> command. + </para> + </footnote> + </entry> + <entry>—</entry> + <entry>Existing row</entry> + <entry> + New row <footnoteref linkend="rls-on-conflict-update-priv"/> + </entry> + <entry>—</entry> + </row> + <row> + <entry><command>MERGE</command></entry> + <entry>All candidate source & target rows</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + </row> + <row> + <entry><command>MERGE ... THEN INSERT</command></entry> + <entry> + New row <footnoteref linkend="rls-select-priv"/> + </entry> + <entry>New row</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + </row> + <row> + <entry><command>MERGE ... THEN UPDATE</command></entry> <entry>Existing & new rows</entry> <entry>—</entry> <entry>Existing row</entry> <entry>New row</entry> <entry>—</entry> </row> + <row> + <entry><command>MERGE ... THEN DELETE</command></entry> + <entry>Existing row</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + <entry>Existing row</entry> + </row> </tbody> </tgroup> </table> -- 2.51.0
