While looking at the INSERT ... ON CONFLICT DO SELECT patch, I noticed
that the "Policies Applied by Command Type" table on the CREATE POLICY
page doesn't fully or accurately describe all the policies that are
actually checked in all cases:
* INSERT ON CONFLICT checks the new row from the INSERT against SELECT
policy expressions, regardless of what ON CONFLICT action is
performed.
* If an ON CONFLICT DO UPDATE is executed, the new row from the
auxiliary UPDATE command is also checked against SELECT policy
expressions.
* MERGE always checks all candidate source and target rows against
SELECT policy expressions, even if no action is performed.
* MERGE ... THEN INSERT checks the new row against SELECT policy
expressions, if there is a RETURNING clause.
* MERGE ... THEN UPDATE always checks the new and existing rows
against SELECT policy expressions, even if there is no RETURNING
clause.
* MERGE ... THEN DELETE isn't mentioned at all. It always checks the
existing row against SELECT policy expressions.
I think having MERGE use the same row in the doc table as other
commands makes it harder to read, and it would be better to just list
each of the MERGE cases separately, even if that does involve some
repetition.
In addition, a paragraph above the table for INSERT policies says:
"""
Note that INSERT with ON CONFLICT DO UPDATE checks INSERT policies'
WITH CHECK expressions only for rows appended to the relation by the
INSERT path.
"""
Maybe that was once true, but it isn't true now, in any supported PG
version. The WITH CHECK expressions from INSERT policies are always
checked, regardless of which path it ends up taking.
I think it would be good to have regression tests specifically
covering all these cases. Yes, there are a lot of existing RLS
regression tests, but they tend to cover more complex scenarios, and
focus on whether the result of the command was what was expected,
rather than precisely which policies were checked in the process.
Thus, it's not obvious whether they provide complete coverage.
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.
Patch 0002 updates the doc table to try to be clearer and more
accurate, and consistent with the test results from 0001, and fixes
the paragraph mentioned above.
Regards,
Dean
From c2c49cd10f001a5ee7a2d52083b2fcd3232fc53e Mon Sep 17 00:00:00 2001
From: Dean Rasheed <dean.a.rash...@gmail.com>
Date: Thu, 27 Mar 2025 14:08:09 +0000
Subject: [PATCH v1 1/2] New RLS tests to test policies applied by command
type.
The existing RLS tests focus on the outcome 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.
---
src/test/regress/expected/rowsecurity.out | 226 ++++++++++++++++++++++
src/test/regress/sql/rowsecurity.sql | 111 +++++++++++
2 files changed, 337 insertions(+)
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 87929191d06..ce80cbde938 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -31,6 +31,232 @@ 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;
+CREATE TABLE rls_test_src (a int PRIMARY KEY, b text);
+ALTER TABLE rls_test_src ENABLE ROW LEVEL SECURITY;
+INSERT INTO rls_test_src VALUES (1, 'src a');
+CREATE TABLE rls_test_tgt (a int PRIMARY KEY, b text, c text);
+ALTER TABLE rls_test_tgt ENABLE ROW LEVEL SECURITY;
+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();
+CREATE FUNCTION sel_using_fn(text, record) RETURNS bool AS
+ $$ BEGIN RAISE NOTICE 'SELECT USING on %.%', $1, $2; 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; $$
+ LANGUAGE plpgsql;
+CREATE POLICY sel_pol ON rls_test_src FOR SELECT
+ USING (sel_using_fn('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));
+CREATE POLICY sel_pol ON rls_test_tgt FOR SELECT
+ USING (sel_using_fn('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));
+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));
+CREATE POLICY del_pol ON rls_test_tgt FOR DELETE
+ USING (del_using_fn('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;
+SELECT * FROM rls_test_src;
+NOTICE: SELECT USING on rls_test_src.(1,"src a")
+ a | b
+---+-------
+ 1 | src a
+(1 row)
+
+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)
+
+INSERT INTO rls_test_tgt VALUES (1, 'tgt a');
+NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A")
+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 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 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)
+
+BEGIN; DELETE FROM rls_test_tgt; ROLLBACK;
+NOTICE: DELETE USING on rls_test_tgt.(1,"tgt d","TGT D")
+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 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")
+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 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")
+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 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)
+
+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)
+
+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;
-- 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 f61dbbf9581..4bec2672a4f 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -41,6 +41,117 @@ 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;
+
+CREATE TABLE rls_test_src (a int PRIMARY KEY, b text);
+ALTER TABLE rls_test_src ENABLE ROW LEVEL SECURITY;
+INSERT INTO rls_test_src VALUES (1, 'src a');
+
+CREATE TABLE rls_test_tgt (a int PRIMARY KEY, b text, c text);
+ALTER TABLE rls_test_tgt ENABLE ROW LEVEL SECURITY;
+
+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();
+
+CREATE FUNCTION sel_using_fn(text, record) RETURNS bool AS
+ $$ BEGIN RAISE NOTICE 'SELECT USING on %.%', $1, $2; 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; $$
+ LANGUAGE plpgsql;
+
+CREATE POLICY sel_pol ON rls_test_src FOR SELECT
+ USING (sel_using_fn('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));
+
+CREATE POLICY sel_pol ON rls_test_tgt FOR SELECT
+ USING (sel_using_fn('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));
+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));
+CREATE POLICY del_pol ON rls_test_tgt FOR DELETE
+ USING (del_using_fn('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;
+
+SELECT * FROM rls_test_src;
+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;
+
+INSERT INTO rls_test_tgt VALUES (1, 'tgt a');
+TRUNCATE rls_test_tgt;
+INSERT INTO rls_test_tgt VALUES (1, 'tgt a') RETURNING *;
+
+UPDATE rls_test_tgt SET b = 'tgt b';
+UPDATE rls_test_tgt SET b = 'tgt c' WHERE a = 1;
+UPDATE rls_test_tgt SET b = 'tgt d' RETURNING *;
+
+BEGIN; DELETE FROM rls_test_tgt; ROLLBACK;
+BEGIN; DELETE FROM rls_test_tgt WHERE a = 1; ROLLBACK;
+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;
+
+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 INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a
+ WHEN NOT MATCHED THEN DO NOTHING;
+
+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 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 *;
+
+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 *;
+
+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;
+
-- BASIC Row-Level Security Scenario
SET SESSION AUTHORIZATION regress_rls_alice;
--
2.43.0
From 441f55244b19b7ffdadf77dc0c9bcd311ec56d00 Mon Sep 17 00:00:00 2001
From: Dean Rasheed <dean.a.rash...@gmail.com>
Date: Thu, 27 Mar 2025 14:19:15 +0000
Subject: [PATCH v1 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.
---
doc/src/sgml/ref/create_policy.sgml | 70 +++++++++++++++++++++++------
1 file changed, 57 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index e76c342d3da..2196fbb1174 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>
@@ -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.43.0
