Invalid dump file after drop of role that previously created extension containing a table.

Hello,

The bug is reproducible on PG16, but we have detected it during
pg_upgrade 14 -> 15.
Testcase is provided (Pg 14, 15, 16, Ubuntu 22.04).

The upgrade failure and the test case description will follow:

Check run was OK:
=================
-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade -d /pgsql/cluster/14/data/ -D
/pgsql/cluster/15/data/ -b /usr/pgsql-14/bin -B /usr/pgsql-15/bin
--link --check
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok

*Clusters are compatible*

The upgrade itself fails (--retain was used to allow the next analysis steps):
==============================================================================
-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade -d /pgsql/cluster/14/data/ -D
/pgsql/cluster/15/data/ -b /usr/pgsql-14/bin -B /usr/pgsql-15/bin
--link --retain
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
betsys
*failure*

Consult the last few lines of
"/pgsql/cluster/15/data/pg_upgrade_output.d/20230921T191028.441/log/pg_upgrade_dump_16451.log"
for
the probable cause of the failure.
Failure, exiting

The error message in the log:
=============================
pg_restore: creating ACL "cron.SEQUENCE "jobid_seq""
pg_restore: creating ACL "cron.TABLE "job""
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6678; 0 0 ACL TABLE "job" sazky
pg_restore: error: could not execute query: ERROR: role "16441" does not exist
Command was: SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);

Get schema-only SQL script from the dump file whose import has failed
during pg_upgrade:
========================================================================================
/usr/pgsql-15/bin/pg_restore -s -f
/pgsql/cluster/15/data/pg_upgrade_output.d/20230921T191028.441/dump/pg_upgrade_dump_16451.schema_only.sql
/pgsql/cluster/15/data/pg_upgrade_output.d/20230921T191028.441/dump/pg_upgrade_dump_16451.custom

Check for the "16441" string in the SQL file produced from the dump
created by pg_upgrade:
==========================================================================================
-bash-4.2$ grep "16441"
/pgsql/cluster/15/data/pg_upgrade_output.d/20230921T191028.441/dump/pg_upgrade_dump_16451.schema_only.sql
GRANT ALL ON TABLE "cron"."job" TO "16441";
REVOKE ALL ON TABLE "cron"."job" FROM "16441";

Observation:
============
The pg_dump beyond other tables reads data from the
pg_catalog.pg_init_privs table to construct the above-listed commands
to preserve ACLs from the database being upgraded (actually, these are
part of ordinary pg_dump anyway).

In our case, I've realized that 16441 is an OID value for a previously
dropped login role (database user).

Testcase description:
====================
1) An extension (I've used pg_cron as an example because it contains a
table) is created by a database user (login role), and the initial
privileges at extension creation are stored for the extension object
(table in my test case) in the pg_catalog.pg_init_privs table.
2) Change the database user objects ownership from step 1 to another
database user -> this step keeps the pg_catalog.pg_init_privs table
content for the extension table from step 1 untouched.
3) Drop the database user used in step 1 and as its entry is deleted
from the catalog, all that remains is the OID of the deleted database
user in the pg_catalog.pg_init_privs table, later used by pg_dump.

I was able to reproduce the issue on several PostgreSQL versions:
=================================================================

PostgreSQL 16.0 (Ubuntu 16.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
PostgreSQL 15.4 (Ubuntu 15.4-2.pgdg22.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg22.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

Result from the test case:
==========================
--------------------------------------------------------------------------------
Print DUMP differences before and after drop of role user1
--------------------------------------------------------------------------------
80c80
< REVOKE ALL ON FUNCTION cron.alter_job(job_id bigint, schedule text,
command text, database text, username text, active boolean) FROM
user1;
---
> REVOKE ALL ON FUNCTION cron.alter_job(job_id bigint, schedule text, command text, database text, username text, active boolean) FROM "16384";
88c88
< REVOKE ALL ON FUNCTION cron.schedule_in_database(job_name text,
schedule text, command text, database text, username text, active
boolean) FROM user1;
---
> REVOKE ALL ON FUNCTION cron.schedule_in_database(job_name text, schedule text, command text, database text, username text, active boolean) FROM "16384";
96c96
< REVOKE ALL ON SEQUENCE cron.jobid_seq FROM user1;
---
> REVOKE ALL ON SEQUENCE cron.jobid_seq FROM "16384";
106c106
< REVOKE ALL ON TABLE cron.job FROM user1;
---
> REVOKE ALL ON TABLE cron.job FROM "16384";
116c116
< REVOKE ALL ON SEQUENCE cron.runid_seq FROM user1;
---
> REVOKE ALL ON SEQUENCE cron.runid_seq FROM "16384";
126c126
< REVOKE ALL ON TABLE cron.job_run_details FROM user1;
---
> REVOKE ALL ON TABLE cron.job_run_details FROM "16384";

The test case script and log files are attached.

Kind regards Ales Zeleny