On 2024-Feb-18, Alvaro Herrera wrote: > On 2024-Feb-18, Tom Lane wrote: > > > So I'd blame this on faulty handling of the zero-partitions case in > > the RTEPermissionInfo refactoring. (I didn't bisect to prove that > > a61b1f748 is exactly where it broke, but I do see that the query > > successfully does nothing in v15.) > > You're right, this is the commit that broke it. It's unclear to me if > Amit is available to look at it, so I'll give this a look tomorrow if he > isn't.
OK, so it turns out that the bug is older than that -- it was actually introduced with MERGE itself (7103ebb7aae8) and has nothing to do with partitioning or RTEPermissionInfo; commit a61b1f748 is only related because it added the Assert() that barfs when there are no privileges to check. The real problem is that a MERGE ... DO NOTHING action reports that no permissions need to be checked on the target relation, which is not a problem when there are other actions in the MERGE command since they add privs to check. When DO NOTHING is the only action, the added assert in a61b1f748 is triggered. So, this means we can fix this by simply requiring ACL_SELECT privileges on a DO NOTHING action. We don't need to request specific privileges on any particular column (perminfo->selectedCols continues to be the empty set) -- which means that any role that has privileges on *any* column would get a pass. If you're doing MERGE with any other action besides DO NOTHING, you already have privileges on at least some column, so adding ACL_SELECT breaks nothing. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
>From 5f2eb9992f40933aa58f8bb0e0bed7ebd99f2952 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera <alvhe...@alvh.no-ip.org> Date: Tue, 20 Feb 2024 18:30:37 +0100 Subject: [PATCH] MERGE ... DO NOTHING: require SELECT privileges Verify that a user running MERGE with a DO NOTHING clause has privileges to read the table, even if no columns are referenced. Such privileges were already required if the ON clause or any of the WHEN conditions referenced any column at all, so there's no functional change in practice. This change fixes an assertion failure in the case where no column is referenced by the command and the WHEN clauses are all DO NOTHING. Backpatch to 15, where MERGE was introduced. Reported-by: Alena Rybakina <a.rybak...@postgrespro.ru> Discussion: https://postgr.es/m/4d65a385-7efa-4436-a825-0869f89d9...@postgrespro.ru --- src/backend/parser/parse_merge.c | 7 ++++++- src/test/regress/expected/merge.out | 11 ++++++++++- src/test/regress/sql/merge.sql | 11 +++++++++++ 3 files changed, 27 insertions(+), 2 deletions(-) diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c index 5f6a683ab9..73f7a48b3c 100644 --- a/src/backend/parser/parse_merge.c +++ b/src/backend/parser/parse_merge.c @@ -133,7 +133,11 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) int when_type = (mergeWhenClause->matched ? 0 : 1); /* - * Collect action types so we can check target permissions + * Collect permissions to check, according to action types. We require + * SELECT privileges for DO NOTHING because it'd be irregular to have + * a target relation with zero privileges checked, in case DO NOTHING + * is the only action. There's no damage from that: any meaningful + * MERGE command requires at least some access to the table anyway. */ switch (mergeWhenClause->commandType) { @@ -147,6 +151,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) targetPerms |= ACL_DELETE; break; case CMD_NOTHING: + targetPerms |= ACL_SELECT; break; default: elog(ERROR, "unknown action in MERGE WHEN clause"); diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index f87905fabd..125d7b6bca 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -3,6 +3,7 @@ -- CREATE USER regress_merge_privs; CREATE USER regress_merge_no_privs; +CREATE USER regress_merge_none; DROP TABLE IF EXISTS target; NOTICE: table "target" does not exist, skipping DROP TABLE IF EXISTS source; @@ -159,12 +160,19 @@ ERROR: cannot execute MERGE on relation "mv" DETAIL: This operation is not supported for materialized views. DROP MATERIALIZED VIEW mv; -- permissions +SET SESSION AUTHORIZATION regress_merge_none; +MERGE INTO target +USING (SELECT 1) +ON true +WHEN MATCHED THEN + DO NOTHING; +ERROR: permission denied for table target +RESET SESSION AUTHORIZATION; MERGE INTO target USING source2 ON target.tid = source2.sid WHEN MATCHED THEN UPDATE SET balance = 0; -ERROR: permission denied for table source2 GRANT INSERT ON target TO regress_merge_no_privs; SET SESSION AUTHORIZATION regress_merge_no_privs; MERGE INTO target @@ -2248,3 +2256,4 @@ DROP TABLE source, source2; DROP FUNCTION merge_trigfunc(); DROP USER regress_merge_privs; DROP USER regress_merge_no_privs; +DROP USER regress_merge_none; diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 66cb75a756..b5bddfcf8d 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -4,6 +4,8 @@ CREATE USER regress_merge_privs; CREATE USER regress_merge_no_privs; +CREATE USER regress_merge_none; + DROP TABLE IF EXISTS target; DROP TABLE IF EXISTS source; CREATE TABLE target (tid integer, balance integer) @@ -118,6 +120,14 @@ DROP MATERIALIZED VIEW mv; -- permissions +SET SESSION AUTHORIZATION regress_merge_none; +MERGE INTO target +USING (SELECT 1) +ON true +WHEN MATCHED THEN + DO NOTHING; +RESET SESSION AUTHORIZATION; + MERGE INTO target USING source2 ON target.tid = source2.sid @@ -1471,3 +1481,4 @@ DROP TABLE source, source2; DROP FUNCTION merge_trigfunc(); DROP USER regress_merge_privs; DROP USER regress_merge_no_privs; +DROP USER regress_merge_none; -- 2.39.2
