From 1a00d821d5e0655b3d2496ed4d075c800ddba350 Mon Sep 17 00:00:00 2001 From: jian he Date: Wed, 26 Nov 2025 16:19:59 +0800 Subject: [PATCH v17 1/1] 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..362a9e2e2ba 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 success 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..325699fb86c 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 success 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.34.1