From 2afa90b624ecc97807fa4b3b2c470c4dbcc7011e Mon Sep 17 00:00:00 2001 From: jian he 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