v19-0003-rowsecurity-tests-for-ON-CONFLICT-DO-SELECT-FOR-.patch

application/octet-stream

Filename: v19-0003-rowsecurity-tests-for-ON-CONFLICT-DO-SELECT-FOR-.patch

Type: application/octet-stream

Part: 2

Message: Re: ON CONFLICT DO SELECT (take 3)

Patch

Same data as JSON: GET /api/v1/attachments/:id/patch the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes. API reference →

Format: format-patch

Series: patch v19-0003

Subject: rowsecurity tests for ON CONFLICT DO SELECT FOR UPDATE

File	+	−
src/test/regress/expected/rowsecurity.out	6	2
src/test/regress/sql/rowsecurity.sql	6	2

From 5790726b3e2e40f16eeed95aeb810e9cc5fdaef0 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 26 Nov 2025 16:19:59 +0800
Subject: [PATCH v19 3/4] rowsecurity tests for ON CONFLICT DO SELECT FOR
 UPDATE

discussion: https://postgr.es/m/
---
 src/test/regress/expected/rowsecurity.out | 8 ++++++--
 src/test/regress/sql/rowsecurity.sql      | 8 ++++++--
 2 files changed, 12 insertions(+), 4 deletions(-)

diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index e45031f7391..a3df861f828 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2445,7 +2445,7 @@ CREATE POLICY p1_select_novels ON document FOR SELECT
 CREATE POLICY p2_insert_own ON document FOR INSERT
   WITH CHECK (dauthor = current_user);
 CREATE POLICY p3_update_novels ON document FOR UPDATE
-  USING (cid = (SELECT cid from category WHERE cname = 'novel'))
+  USING (cid = (SELECT cid from category WHERE cname = 'novel') AND dlevel = 1)
   WITH CHECK (dauthor = current_user);
 SET SESSION AUTHORIZATION regress_rls_bob;
 -- DO SELECT requires SELECT rights, should succeed for novel
@@ -2468,7 +2468,7 @@ INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel')
    1 | regress_rls_bob | my first novel
 (1 row)
 
--- DO SELECT FOR UPDATE requires both SELECT and UPDATE rights, should succeed for novel
+-- DO SELECT FOR UPDATE requires both SELECT and UPDATE rights, should succeed for novel and dlevel = 1
 INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
     ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
  did |     dauthor     |     dtitle     
@@ -2476,6 +2476,10 @@ INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel')
    1 | regress_rls_bob | my first novel
 (1 row)
 
+-- should fail because existing row does not ok with UPDATE USING policy
+INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
+    ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
+ERROR:  new row violates row-level security policy (USING expression) for table "document"
 -- DO SELECT FOR UPDATE requires UPDATE rights, should fail for non-novel
 INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'science fiction'), 1, 'regress_rls_bob', 'another sci-fi')
     ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index b3e282c19d3..3c47d8fcc9a 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -977,7 +977,7 @@ CREATE POLICY p1_select_novels ON document FOR SELECT
 CREATE POLICY p2_insert_own ON document FOR INSERT
   WITH CHECK (dauthor = current_user);
 CREATE POLICY p3_update_novels ON document FOR UPDATE
-  USING (cid = (SELECT cid from category WHERE cname = 'novel'))
+  USING (cid = (SELECT cid from category WHERE cname = 'novel') AND dlevel = 1)
   WITH CHECK (dauthor = current_user);
 
 SET SESSION AUTHORIZATION regress_rls_bob;
@@ -994,10 +994,14 @@ INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'scienc
 INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
     ON CONFLICT (did) DO SELECT WHERE excluded.dlevel = 1 RETURNING did, dauthor, dtitle;
 
--- DO SELECT FOR UPDATE requires both SELECT and UPDATE rights, should succeed for novel
+-- DO SELECT FOR UPDATE requires both SELECT and UPDATE rights, should succeed for novel and dlevel = 1
 INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
     ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
 
+-- should fail because existing row does not ok with UPDATE USING policy
+INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
+    ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
+
 -- DO SELECT FOR UPDATE requires UPDATE rights, should fail for non-novel
 INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'science fiction'), 1, 'regress_rls_bob', 'another sci-fi')
     ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
-- 
2.48.1