v12-0002-permission-tests-for-ON-CONFLICT-DO-SELECT.no-cfbot

From 2afa90b624ecc97807fa4b3b2c470c4dbcc7011e Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 20 Nov 2025 14:09:23 +0800
Subject: [PATCH v12 2/2] permission tests for ON CONFLICT DO SELECT

discussion: https://postgr.es/m/d631b406-13b7-433e-8c0b-c6040c4b4663@Spark
---
 src/test/regress/expected/insert_conflict.out | 65 +++++++++++++++++++
 src/test/regress/sql/insert_conflict.sql      | 35 ++++++++++
 2 files changed, 100 insertions(+)

diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 5d76014c3eb..92d2f38aa08 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -249,6 +249,71 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+----- INSERT ON CONFLICT DO SELECT PERMISSION TESTS ---
+create table conflictselect_perv(key int4, fruit text);
+create unique index x_idx on conflictselect_perv(key);
+create role regress_conflict_alice;
+grant all on schema public to regress_conflict_alice;
+grant insert on conflictselect_perv to regress_conflict_alice;
+grant select(key) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.key = 1 returning 1; --ok
+ ?column? 
+----------
+        1
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+reset role;
+grant select(fruit) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning 1; --ok
+ ?column? 
+----------
+        1
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for no key update where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for share where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for key share where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+reset role;
+grant update (fruit) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning *;
+ key | fruit 
+-----+-------
+   1 | Apple
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for no key update where i.fruit = 'Apple' returning *;
+ key | fruit 
+-----+-------
+   1 | Apple
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for share where i.fruit = 'Apple' returning *;
+ key | fruit 
+-----+-------
+   1 | Apple
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for key share where i.fruit = 'Apple' returning *;
+ key | fruit 
+-----+-------
+   1 | Apple
+(1 row)
+
+reset role;
+drop table conflictselect_perv;
+revoke all on schema public from regress_conflict_alice;
+drop role regress_conflict_alice;
+------- END OF PERMISSION TESTS ------------
 -- DO SELECT
 delete from insertconflicttest where fruit = 'Apple';
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do select; -- fails
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index d5bb706acfd..495c193a763 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -101,6 +101,41 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+----- INSERT ON CONFLICT DO SELECT PERMISSION TESTS ---
+create table conflictselect_perv(key int4, fruit text);
+create unique index x_idx on conflictselect_perv(key);
+create role regress_conflict_alice;
+grant all on schema public to regress_conflict_alice;
+grant insert on conflictselect_perv to regress_conflict_alice;
+grant select(key) on conflictselect_perv to regress_conflict_alice;
+
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.key = 1 returning 1; --ok
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning 1; --fail
+
+reset role;
+grant select(fruit) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning 1; --ok
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning 1; --fail
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for no key update where i.fruit = 'Apple' returning 1; --fail
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for share where i.fruit = 'Apple' returning 1; --fail
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for key share where i.fruit = 'Apple' returning 1; --fail
+
+reset role;
+grant update (fruit) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning *;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for no key update where i.fruit = 'Apple' returning *;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for share where i.fruit = 'Apple' returning *;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for key share where i.fruit = 'Apple' returning *;
+
+reset role;
+drop table conflictselect_perv;
+revoke all on schema public from regress_conflict_alice;
+drop role regress_conflict_alice;
+------- END OF PERMISSION TESTS ------------
+
 -- DO SELECT
 delete from insertconflicttest where fruit = 'Apple';
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do select; -- fails
-- 
2.34.1