Thread: BUG #18713: pg_upgrade fails when owner of an extension is changed or dropped

The following bug has been logged on the website:

Bug reference:      18713
Logged by:          Hanumant Mittal
Email address:      mittalhanumant@gmail.com
PostgreSQL version: 15.4
Operating system:   Debian GNU/Linux rodete
Description:

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.

Steps to reproduce:
# Setup pg14
/usr/lib/postgresql/14/bin/initdb --locale=C.UTF-8 /tmp/data-14
/usr/lib/postgresql/14/bin/pg_ctl -D /tmp/data-14 -l logfile start


# Steps on psql
postgres=# select version();
                                                     version
                                     
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 (Debian 14.5-3+build1) on x86_64-pc-linux-gnu, compiled by
gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

postgres=# CREATE USER olduser WITH PASSWORD 'old_password';
ALTER USER olduser WITH SUPERUSER;
CREATE USER newuser WITH PASSWORD 'new_password';
ALTER USER newuser WITH SUPERUSER;
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
postgres=# \q
postgres@hanumant:~$ /usr/lib/postgresql/14/bin/psql -U olduser -d
postgres
psql (14.5 (Debian 14.5-3+build1))
Type "help" for help.

postgres=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION
postgres=# \q
postgres@hanumant:~$ /usr/lib/postgresql/14/bin/psql -U postgres -d
postgres^C
postgres@hanumant:~$ /usr/lib/postgresql/14/bin/psql -U postgres -d
postgres
psql (14.5 (Debian 14.5-3+build1))
Type "help" for help.

postgres=# REASSIGN OWNED by olduser to newuser;
DROP USER olduser;
\q
REASSIGN OWNED
DROP ROLE
postgres@hanumant:~$ 

# do pg_upgrade
## Setup pg15

/usr/lib/postgresql/15/bin/initdb --locale=C.UTF-8 /tmp/data-15

## Trigger upgrade
 /usr/lib/postgresql/15/bin/pg_upgrade -b /usr/lib/postgresql/14/bin -B
/usr/lib/postgresql/15/bin -d /tmp/data-14 -D /tmp/data-15 -r -v

Fails with:
executing: ALTER DATABASE "template0" ALLOW_CONNECTIONS = false
ok
Restoring global objects in the new cluster
"/usr/lib/postgresql/15/bin/psql" --echo-queries --set ON_ERROR_STOP=on
--no-psqlrc --dbname=template1 --host /var/lib/postgresql --port 50432
--username postgres -f
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/dump/pg_upgrade_dump_globals.sql"
>>
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/log/pg_upgrade_utility.log"
2>&1
ok
Restoring database schemas in the new cluster               
"/usr/lib/postgresql/15/bin/pg_restore" --host /var/lib/postgresql --port
50432 --username postgres --clean --create --exit-on-error --verbose
--dbname postgres
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/dump/pg_upgrade_dump_1.custom"
>>
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/log/pg_upgrade_dump_1.log"
2>&1
"/usr/lib/postgresql/15/bin/pg_restore" --host /var/lib/postgresql --port
50432 --username postgres --clean --create --exit-on-error --verbose
--dbname template1
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/dump/pg_upgrade_dump_14426.custom"
>>
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/log/pg_upgrade_dump_14426.log"
2>&1

*failure*
There were problems executing ""/usr/lib/postgresql/15/bin/pg_restore"
--host /var/lib/postgresql --port 50432 --username postgres --clean --create
--exit-on-error --verbose --dbname template1
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/dump/pg_upgrade_dump_14426.custom"
>>
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/log/pg_upgrade_dump_14426.log"
2>&1"

Consult the last few lines of
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/log/pg_upgrade_dump_14426.log"
for
the probable cause of the failure.
Failure, exiting
"/usr/lib/postgresql/15/bin/pg_ctl" -w -D "/tmp/data-15" -o "" -m fast stop
>>
"/tmp/data-15/pg_upgrade_output.d/20241118T132722.992/log/pg_upgrade_server.log"
2>&1

## Checking logs:
pg_restore: creating ACL "public.FUNCTION
"pg_stat_statements_reset"("userid" "oid", "dbid" "oid", "queryid"
bigint)"
pg_restore: while PROCESSING TOC:
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
Command was: SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);
REVOKE ALL ON FUNCTION "public"."pg_stat_statements_reset"("userid" "oid",
"dbid" "oid", "queryid" bigint) FROM PUBLIC;
REVOKE ALL ON FUNCTION "public"."pg_stat_statements_reset"("userid" "oid",
"dbid" "oid", "queryid" bigint) FROM "newuser";
SET SESSION AUTHORIZATION "16384";
GRANT ALL ON FUNCTION "public"."pg_stat_statements_reset"("userid" "oid",
"dbid" "oid", "queryid" bigint) TO "16384";
RESET SESSION AUTHORIZATION;
SELECT pg_catalog.binary_upgrade_set_record_init_privs(false);
REVOKE ALL ON FUNCTION "public"."pg_stat_statements_reset"("userid" "oid",
"dbid" "oid", "queryid" bigint) FROM "16384";
GRANT ALL ON FUNCTION "public"."pg_stat_statements_reset"("userid" "oid",
"dbid" "oid", "queryid" bigint) TO "newuser";

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