From: Tom Lane <tgl@sss.pgh.pa.us>
To: mittalhanumant@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18713: pg_upgrade fails when owner of an extension is changed or dropped
Date: Mon, 18 Nov 2024 10:49:36 -0500
Message-ID: <3305917.1731944976@sss.pgh.pa.us> (raw)
In-Reply-To: <18713-21afc479c25cce4b@postgresql.org>
References: <18713-21afc479c25cce4b@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> pg_upgrade from pg14 to pg15 fails with error of this kind:
> ```
> pg_restore: from TOC entry 3999; 0 0 ACL FUNCTION
> "pg_stat_statements_reset"("userid" "oid", "dbid" "oid", "queryid" bigint)
> newuser
> pg_restore: error: could not execute query: ERROR: role "16384" does not
> exist
> ```
> Condition that seems to be triggering this is if we create an extension with
> one user (e.g. olduser), reassigned to another user (e.g. newuser) and drop
> the olduser. Post that if we trigger pg_upgrade it fails.
Yeah. I believe this is an instance of the known problems around DROP
OWNED/REASSIGN OWNED not updating entries in pg_init_privs. See long
discussion here:
https://www.postgresql.org/message-id/flat/1745535.1712358659%40sss.pgh.pa.us
v17 has improved that situation (though I don't claim that pg_dump
is fully simpatico with reassigning an extension's owner even yet).
There's no chance of doing anything about it in older branches,
sadly, because they simply lack the catalog entries needed to detect
that a problem even exists.
Short-term advice is to not reassign the owner of an extension.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, mittalhanumant@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18713: pg_upgrade fails when owner of an extension is changed or dropped
In-Reply-To: <3305917.1731944976@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox