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
In-reply-to: <18713-21afc479c25cce4b@postgresql.org>
References: <18713-21afc479c25cce4b@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Mon, 18 Nov 2024 13:38:30 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3305916.1731944976.1@sss.pgh.pa.us>
Date: Mon, 18 Nov 2024 10:49:36 -0500
Message-ID: <3305917.1731944976@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3305917.1731944976%40sss.pgh.pa.us>
Precedence: bulk

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