Thread

1. BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

   PG Bug reporting form <noreply@postgresql.org> — 2026-05-18T05:06:24Z

   The following bug has been logged on the website:
   
   Bug reference:      19483
   Logged by:          Hüseyin Demir
   Email address:      huseyin.d3r@gmail.com
   PostgreSQL version: 18.3
   Operating system:   CentOS 9
   Description:        
   
   Hi,
   
   I encountered a problem during the pg_upgrade.
   
   pg_upgrade --check passes all consistency checks but then fails during
   the actual upgrade when pg_restore attempts to GRANT privileges to a
   role OID (e.g. "16424") that no longer exists. The root cause is orphan
   records in pg_init_privs whose ACL entries reference a dropped role by
   OID. pg_upgrade does not detect these dangling references during its
   pre-flight checks.
   
   Steps to Reproduce
   
   1. Create a role and database with that role as owner:
   
      CREATE ROLE benchmark_owner SUPERUSER;
      CREATE DATABASE my_db OWNER benchmark_owner;
   
   2. Connect to the database and install pg_wait_sampling as that role:
   
      \c my_db
      SET ROLE benchmark_owner;
      CREATE EXTENSION pg_wait_sampling;
      RESET ROLE;
   
   3. Verify pg_init_privs now contains rows with benchmark_owner in the
      ACL (privtype = 'e', OIDs 16429, 16434, 16439, 16443, etc.)
   
   4. Reassign owned objects and drop the role:
   
      \c postgres
      SELECT pg_terminate_backend(pid)
        FROM pg_stat_activity WHERE datname = 'my_db';
      ALTER DATABASE my_db RENAME TO my_db_v2;
      \c my_db_v2
      REASSIGN OWNED BY benchmark_owner TO postgres;
      DROP OWNED BY benchmark_owner;
      \c postgres
      DROP ROLE benchmark_owner;
   
   5. Verify orphan records remain in pg_init_privs:
   
      SELECT pip.objoid
      FROM pg_init_privs pip
      CROSS JOIN LATERAL aclexplode(pip.initprivs) ace
      LEFT JOIN pg_authid a ON a.oid = ace.grantee
      WHERE a.oid IS NULL AND ace.grantee <> 0;
   
      -- Returns 22 rows referencing OID 16461 (the dropped role)
   
   6. Run pg_upgrade --check:
   
      /usr/pgsql-18/bin/pg_upgrade \
        --old-datadir=/var/lib/pgsql/14/data \
        --new-datadir=/var/lib/pgsql/18/data \
        --old-bindir=/usr/pgsql-14/bin/ \
        --new-bindir=/usr/pgsql-18/bin/ \
        --check --jobs=4
   
      Result: "Clusters are compatible" -- all checks pass.
   
   [postgres@localhost data]$ /usr/pgsql-18/bin/pg_upgrade
   --old-datadir=/var/lib/pgsql/14/data --new-datadir=/var/lib/pgsql/18/data
   --old-bindir=/usr/pgsql-14/bin/ --new-bindir=/usr/pgsql-18/bin/ --check
   --jobs=4
   Performing Consistency Checks on Old Live Server
   ------------------------------------------------
   Checking cluster versions                                     ok
   Checking database connection settings                         ok
   Checking database user is the install user                    ok
   Checking for prepared transactions                            ok
   Checking for contrib/isn with bigint-passing mismatch         ok
   Checking data type usage                                      ok
   Checking for not-null constraint inconsistencies              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*
   
   7. Run pg_upgrade (without --check):
   
   [postgres@localhost data]$ /usr/pgsql-18/bin/pg_upgrade
   --old-datadir=/var/lib/pgsql/14/data --new-datadir=/var/lib/pgsql/18/data
   --old-bindir=/usr/pgsql-14/bin/ --new-bindir=/usr/pgsql-18/bin/ --jobs=4
   Performing Consistency Checks
   -----------------------------
   Checking cluster versions                                     ok
   Checking database connection settings                         ok
   Checking database user is the install user                    ok
   Checking for prepared transactions                            ok
   Checking for contrib/isn with bigint-passing mismatch         ok
   Checking data type usage                                      ok
   Checking for not-null constraint inconsistencies              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
   ------------------
   Setting locale and encoding for new cluster                   ok
   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 the default char signedness for new cluster           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
     my_db_v2
   *failure*
   
   Consult the last few lines of
   "/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16425.log"
   for
   the probable cause of the failure.
   Failure, exiting
   
   *failure*
   
   Consult the last few lines of
   "/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16384.log"
   for
   the probable cause of the failure.
   Failure, exiting
   
   child process exited abnormally: status 256
   Failure, exiting
   [postgres@localhost data]$
   
   The pg_upgrade log as follow
   [postgres@localhost data]$ cat
   /var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16425.log
   command: "/usr/pgsql-18/bin/pg_dump" --host /var/lib/pgsql/18/data --port
   50432 --username postgres --no-data --sequence-data  --quote-all-identifiers
   --binary-upgrade --format=custom --statistics --no-sync
   --file="/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/dump/pg_upgrade_dump_16425.custom"
   'dbname=my_db_v2' >>
   "/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16425.log"
   2>&1
   
   
   command: "/usr/pgsql-18/bin/pg_restore" --host /var/lib/pgsql/18/data --port
   50432 --username postgres --create --exit-on-error --verbose
   --transaction-size=250 --dbname template1
   "/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/dump/pg_upgrade_dump_16425.custom"
   >>
   "/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16425.log"
   2>&1
   pg_restore: connecting to database for restore
   pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
   false);
   pg_restore: creating DATABASE "my_db_v2"
   pg_restore: connecting to new database "my_db_v2"
   pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
   false);
   pg_restore: creating DATABASE PROPERTIES "my_db_v2"
   pg_restore: connecting to new database "my_db_v2"
   pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
   false);
   pg_restore: creating pg_largeobject "pg_largeobject"
   pg_restore: creating SCHEMA "public"
   pg_restore: creating COMMENT "SCHEMA "public""
   pg_restore: creating EXTENSION "pg_wait_sampling"
   pg_restore: creating COMMENT "EXTENSION "pg_wait_sampling""
   pg_restore: creating FUNCTION "public.pg_wait_sampling_get_current(integer)"
   pg_restore: creating FUNCTION "public.pg_wait_sampling_get_history()"
   pg_restore: creating FUNCTION "public.pg_wait_sampling_get_profile()"
   pg_restore: creating FUNCTION "public.pg_wait_sampling_reset_profile()"
   pg_restore: creating VIEW "public.pg_wait_sampling_current"
   pg_restore: creating VIEW "public.pg_wait_sampling_history"
   pg_restore: creating VIEW "public.pg_wait_sampling_profile"
   pg_restore: creating ACL "SCHEMA "public""
   pg_restore: creating ACL "public.FUNCTION
   "pg_wait_sampling_reset_profile"()"
   pg_restore: while PROCESSING TOC:
   pg_restore: from TOC entry 4338; 0 0 ACL FUNCTION
   "pg_wait_sampling_reset_profile"() postgres
   pg_restore: error: could not execute query: ERROR:  role "16424" does not
   exist
   Command was: SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);
   REVOKE ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() FROM
   PUBLIC;
   REVOKE ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() FROM
   "postgres";
   SET SESSION AUTHORIZATION "16424";
   GRANT ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() TO
   "16424";
   RESET SESSION AUTHORIZATION;
   SELECT pg_catalog.binary_upgrade_set_record_init_privs(false);
   REVOKE ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() FROM
   "16424";
   GRANT ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() TO
   "postgres";
   
   Root Cause
   
   When a role is dropped, DROP OWNED BY and REASSIGN OWNED BY do NOT
   cascade to pg_init_privs. This leaves ACL entries in pg_init_privs
   referencing the dropped role by its numeric OID.
   
   pg_dump (run internally by pg_upgrade) faithfully dumps these orphan
   ACL entries and emits SET SESSION AUTHORIZATION "<OID>" commands.
   pg_restore then fails because the numeric OID is not a valid role name
   in the new cluster.
   
   pg_upgrade's --check phase does not include a check for orphan role
   references inside pg_init_privs ACLs, so the problem is not caught
   before the destructive upgrade steps begin.
   
   Expected Behavior
   
   pg_upgrade --check should detect orphan ACL entries in pg_init_privs
   (where ace.grantee does not match any pg_authid.oid and is not 0) and
   report an error before declaring the clusters compatible.
   
   Possible Fix
   
   Add a new pre-flight check in pg_upgrade (src/bin/pg_upgrade/check.c)
   that runs the following query against each user database in the old
   cluster:
   
      SELECT DISTINCT pip.objoid
      FROM pg_init_privs pip
      CROSS JOIN LATERAL aclexplode(pip.initprivs) ace
      LEFT JOIN pg_authid a ON a.oid = ace.grantee
      WHERE a.oid IS NULL AND ace.grantee <> 0;
   
   If any rows are returned, pg_upgrade should abort with a descriptive
   error message telling the user to either drop or clean up the orphan
   ACL entries before upgrading.
   
   Workaround
   
   Before running pg_upgrade, manually clean up orphan pg_init_privs
   entries in all user databases:
   
      -- Identify affected objects
      SELECT pip.objoid
      FROM pg_init_privs pip
      CROSS JOIN LATERAL aclexplode(pip.initprivs) ace
      LEFT JOIN pg_authid a ON a.oid = ace.grantee
      WHERE a.oid IS NULL AND ace.grantee <> 0;
   
      -- Remove the orphan rows (adjust WHERE clause as needed)
      DELETE FROM pg_init_privs
      WHERE objoid IN (
        SELECT pip.objoid
        FROM pg_init_privs pip
        CROSS JOIN LATERAL aclexplode(pip.initprivs) ace
        LEFT JOIN pg_authid a ON a.oid = ace.grantee
        WHERE a.oid IS NULL AND ace.grantee <> 0
      );
   
   Note: Direct modification of system catalogs requires
   allow_system_table_mods = on.
   
   
   How should we proceed or solve this case ?
   
   
   
   
   
   

2. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

   Greg Sabino Mullane <htamfids@gmail.com> — 2026-05-20T13:06:58Z

   >
   > PostgreSQL version: 18.3
   > ...
   
   5. Verify orphan records remain in pg_init_privs:erprise Postgres Software
   > Products & Tech Support
   >
   
   Thanks for providing a failing use case. I ran this on a 18.3 server and
   found no orphaned rows - but I used the pg_stat_statements extension
   instead of pg_wait_sampling. Could you try your experiment using
   pg_stat_statements? And could you also show us the contents of the errant
   rows in pg_init_privs for the failing case?
   

3. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

   Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-07T10:53:07Z

   Hi, sorry for the late reply. There was a problem in my mailbox.
   
   I was able to reproduce the same issue with the pg_stat_statements
   extension. The problem is valid for orphan pg_init_privs records.
   Therefore, I created a simple patch to introduce a new check to pg_upgrade
   binary.
   
   PS: I'm working on PostgreSQL 14 (on different minor versions)
   
   Please see [1]
   <https://www.postgresql.org/message-id/flat/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com>
   to see and review the problem I try to solve.
   
   ```
   my_db_v2=# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
   e.extname
   FROM pg_init_privs pip
   JOIN pg_depend d ON d.objid = pip.objoid
   JOIN pg_extension e ON e.oid = d.refobjid
   WHERE e.extname = 'pg_stat_statements'
   AND pip.privtype = 'e';
   objoid | classoid | privtype | initprivs | extname
   --------+----------+----------+--------------------------------+--------------------
   16458 | 1255 | e | {16449=X/16449} | pg_stat_statements
   16466 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
   16471 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
   (3 rows)
   
   ```
   
   I applied the following steps.
   
   1. Create the role and database on postgres database.
   
   ```
   CREATE ROLE benchmark_owner SUPERUSER;
   CREATE DATABASE my_db OWNER benchmark_owner;
   ```
   
   2. Connect to the my_db and execute the following commands.
   
   ```
   SET ROLE benchmark_owner;
   create extension pg_stat_statements;
   ```
   
   Afterwards, I see the records in pg_init_privs
   
   my_db=# reset role;
   RESET
   my_db=# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
   e.extname
   FROM pg_init_privs pip
   JOIN pg_depend d ON d.objid = pip.objoid
   JOIN pg_extension e ON e.oid = d.refobjid
   WHERE e.extname = 'pg_stat_statements'
   AND pip.privtype = 'e';
   objoid | classoid | privtype | initprivs | extname
   --------+----------+----------+--------------------------------------------------------------+--------------------
   16458 | 1255 | e | {benchmark_owner=X/benchmark_owner} | pg_stat_statements
   16466 | 1259 | e |
   {benchmark_owner=arwdDxt/benchmark_owner,=r/benchmark_owner} |
   pg_stat_statements
   16471 | 1259 | e |
   {benchmark_owner=arwdDxt/benchmark_owner,=r/benchmark_owner} |
   pg_stat_statements
   (3 rows)
   
   3. Connect to postgres database and execute the following ones.
   
   ```
   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE datname = 'my_db';ALTER DATABASE my_db RENAME TO my_db_v2;
   ```
   
   4. Connect to my_db_2 and execute the following ones.
   
   ```
   REASSIGN OWNED BY benchmark_owner TO postgres;
   DROP OWNED BY benchmark_owner;
   ```
   
   5. Connect to postgres database and drop the role we created the extension
   
   ```
   DROP ROLE benchmark_owner;
   ```
   
   6. Connect to my_db_2 and check the dangling/orphan records.
   
   ```
   my_db_v2=# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
   e.extname
   FROM pg_init_privs pip
   JOIN pg_depend d ON d.objid = pip.objoid
   JOIN pg_extension e ON e.oid = d.refobjid
   WHERE e.extname = 'pg_stat_statements'
   AND pip.privtype = 'e';
   objoid | classoid | privtype | initprivs | extname
   --------+----------+----------+--------------------------------+--------------------
   16458 | 1255 | e | {16449=X/16449} | pg_stat_statements
   16466 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
   16471 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
   (3 rows)
   ```
   
   [1]
   https://www.postgresql.org/message-id/flat/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com
   
   
   Greg Sabino Mullane <htamfids@gmail.com>, 20 May 2026 Çar, 15:07 tarihinde
   şunu yazdı:
   
   > PostgreSQL version: 18.3
   >> ...
   >
   > 5. Verify orphan records remain in pg_init_privs:erprise Postgres Software
   >> Products & Tech Support
   >>
   >
   > Thanks for providing a failing use case. I ran this on a 18.3 server and
   > found no orphaned rows - but I used the pg_stat_statements extension
   > instead of pg_wait_sampling. Could you try your experiment using
   > pg_stat_statements? And could you also show us the contents of the errant
   > rows in pg_init_privs for the failing case?
   >
   >
   >
   >
   

4. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

   Tom Lane <tgl@sss.pgh.pa.us> — 2026-06-07T15:51:59Z

   Greg Sabino Mullane <htamfids@gmail.com> writes:
   >> 5. Verify orphan records remain in pg_init_privs:
   
   > Thanks for providing a failing use case. I ran this on a 18.3 server and
   > found no orphaned rows - but I used the pg_stat_statements extension
   > instead of pg_wait_sampling. Could you try your experiment using
   > pg_stat_statements? And could you also show us the contents of the errant
   > rows in pg_init_privs for the failing case?
   
   The orphaned-rows problem shouldn't exist in v17 and later (see
   534287403, 35dd40d34, and related commits).  The OP is apparently
   complaining about an upgrade from v14, where such rows could exist.
   
   I don't especially care for the proposed fix of making pg_upgrade
   refuse to run.  Manually correcting such situations would be tedious
   and error-prone.  Plus, it's inconsistent with what we did about
   related issues with role GRANTs (see 29d75b25b and 74b4438a7).
   I wonder if it'd be sane for pg_dump to just skip dangling role
   references in pg_init_privs.
   
   			regards, tom lane
   
   
   
   

5. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

   Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-11T05:49:05Z

   > The orphaned-rows problem shouldn't exist in v17 and later (see
   > 534287403, 35dd40d34, and related commits).  The OP is apparently
   > complaining about an upgrade from v14, where such rows could exist.
   
   Yes I was working on upgrading the PostgreSQL version from v14 to v18
   and was able to solve the problem by removing the danling records from
   pg_init_privs.
   
   > I wonder if it'd be sane for pg_dump to just skip dangling role
   > references in pg_init_privs.
   
   It will change the behavior of pg_dump and it's a general purpose tool
   because when we instruct pg_dump to filter orphan records it will
   change the content in the system catalogs.
   
   For now I suppose we have two options: either pg_upgrade or pg_dump.
   
   Regards.
   
   
   Tom Lane <tgl@sss.pgh.pa.us>, 7 Haz 2026 Paz, 17:52 tarihinde şunu yazdı:
   >
   > Greg Sabino Mullane <htamfids@gmail.com> writes:
   > >> 5. Verify orphan records remain in pg_init_privs:
   >
   > > Thanks for providing a failing use case. I ran this on a 18.3 server and
   > > found no orphaned rows - but I used the pg_stat_statements extension
   > > instead of pg_wait_sampling. Could you try your experiment using
   > > pg_stat_statements? And could you also show us the contents of the errant
   > > rows in pg_init_privs for the failing case?
   >
   > The orphaned-rows problem shouldn't exist in v17 and later (see
   > 534287403, 35dd40d34, and related commits).  The OP is apparently
   > complaining about an upgrade from v14, where such rows could exist.
   >
   > I don't especially care for the proposed fix of making pg_upgrade
   > refuse to run.  Manually correcting such situations would be tedious
   > and error-prone.  Plus, it's inconsistent with what we did about
   > related issues with role GRANTs (see 29d75b25b and 74b4438a7).
   > I wonder if it'd be sane for pg_dump to just skip dangling role
   > references in pg_init_privs.
   >
   >                         regards, tom lane
   
   
   
   

6. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

   Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-12T16:22:03Z

   Hi,
   
   I worked on pg_dump and discussed it with Laurenz Albe. Created the
   attached patch.
   
   The fix filters dangling grantees out of each initprivs array at query
   time, using NULLIF/ARRAY/NOT EXISTS against pg_authid. Entries for
   grantee = 0 (PUBLIC) are never filtered. If all entries for an object
   are dangling, NULL is returned and no ACL statement is emitted. Since
   we cannot restore grants to non-existent roles. correct outcome,
   
   The patch includes a TAP test (008_pg_dump_dangling_initprivs.pl) that
   reproduces the scenario using allow_system_table_mods to create a
   dangling pg_init_privs entry, then verifies pg_dump exits cleanly and
   emits no invalid GRANT.
   
   I have not prepared backpatch branches yet.
   
   Regards.
   
   Hüseyin Demir <huseyin.d3r@gmail.com>, 11 Haz 2026 Per, 07:49
   tarihinde şunu yazdı:
   >
   > > The orphaned-rows problem shouldn't exist in v17 and later (see
   > > 534287403, 35dd40d34, and related commits).  The OP is apparently
   > > complaining about an upgrade from v14, where such rows could exist.
   >
   > Yes I was working on upgrading the PostgreSQL version from v14 to v18
   > and was able to solve the problem by removing the danling records from
   > pg_init_privs.
   >
   > > I wonder if it'd be sane for pg_dump to just skip dangling role
   > > references in pg_init_privs.
   >
   > It will change the behavior of pg_dump and it's a general purpose tool
   > because when we instruct pg_dump to filter orphan records it will
   > change the content in the system catalogs.
   >
   > For now I suppose we have two options: either pg_upgrade or pg_dump.
   >
   > Regards.
   >
   >
   > Tom Lane <tgl@sss.pgh.pa.us>, 7 Haz 2026 Paz, 17:52 tarihinde şunu yazdı:
   > >
   > > Greg Sabino Mullane <htamfids@gmail.com> writes:
   > > >> 5. Verify orphan records remain in pg_init_privs:
   > >
   > > > Thanks for providing a failing use case. I ran this on a 18.3 server and
   > > > found no orphaned rows - but I used the pg_stat_statements extension
   > > > instead of pg_wait_sampling. Could you try your experiment using
   > > > pg_stat_statements? And could you also show us the contents of the errant
   > > > rows in pg_init_privs for the failing case?
   > >
   > > The orphaned-rows problem shouldn't exist in v17 and later (see
   > > 534287403, 35dd40d34, and related commits).  The OP is apparently
   > > complaining about an upgrade from v14, where such rows could exist.
   > >
   > > I don't especially care for the proposed fix of making pg_upgrade
   > > refuse to run.  Manually correcting such situations would be tedious
   > > and error-prone.  Plus, it's inconsistent with what we did about
   > > related issues with role GRANTs (see 29d75b25b and 74b4438a7).
   > > I wonder if it'd be sane for pg_dump to just skip dangling role
   > > references in pg_init_privs.
   > >
   > >                         regards, tom lane
   

7. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

   Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-19T06:25:47Z

   I simplified the patch and only changed the SQL query.
   
   
   The v2 patch correctly filters `pg_init_privs` entries whose grantee OID
   has no corresponding row in `pg_authid`, without affecting valid entries.
   
   Regards,
   Demir.
   
   Hüseyin Demir <huseyin.d3r@gmail.com>, 12 Haz 2026 Cum, 18:22 tarihinde
   şunu yazdı:
   
   > Hi,
   >
   > I worked on pg_dump and discussed it with Laurenz Albe. Created the
   > attached patch.
   >
   > The fix filters dangling grantees out of each initprivs array at query
   > time, using NULLIF/ARRAY/NOT EXISTS against pg_authid. Entries for
   > grantee = 0 (PUBLIC) are never filtered. If all entries for an object
   > are dangling, NULL is returned and no ACL statement is emitted. Since
   > we cannot restore grants to non-existent roles. correct outcome,
   >
   > The patch includes a TAP test (008_pg_dump_dangling_initprivs.pl) that
   > reproduces the scenario using allow_system_table_mods to create a
   > dangling pg_init_privs entry, then verifies pg_dump exits cleanly and
   > emits no invalid GRANT.
   >
   > I have not prepared backpatch branches yet.
   >
   > Regards.
   >
   > Hüseyin Demir <huseyin.d3r@gmail.com>, 11 Haz 2026 Per, 07:49
   > tarihinde şunu yazdı:
   > >
   > > > The orphaned-rows problem shouldn't exist in v17 and later (see
   > > > 534287403, 35dd40d34, and related commits).  The OP is apparently
   > > > complaining about an upgrade from v14, where such rows could exist.
   > >
   > > Yes I was working on upgrading the PostgreSQL version from v14 to v18
   > > and was able to solve the problem by removing the danling records from
   > > pg_init_privs.
   > >
   > > > I wonder if it'd be sane for pg_dump to just skip dangling role
   > > > references in pg_init_privs.
   > >
   > > It will change the behavior of pg_dump and it's a general purpose tool
   > > because when we instruct pg_dump to filter orphan records it will
   > > change the content in the system catalogs.
   > >
   > > For now I suppose we have two options: either pg_upgrade or pg_dump.
   > >
   > > Regards.
   > >
   > >
   > > Tom Lane <tgl@sss.pgh.pa.us>, 7 Haz 2026 Paz, 17:52 tarihinde şunu
   > yazdı:
   > > >
   > > > Greg Sabino Mullane <htamfids@gmail.com> writes:
   > > > >> 5. Verify orphan records remain in pg_init_privs:
   > > >
   > > > > Thanks for providing a failing use case. I ran this on a 18.3 server
   > and
   > > > > found no orphaned rows - but I used the pg_stat_statements extension
   > > > > instead of pg_wait_sampling. Could you try your experiment using
   > > > > pg_stat_statements? And could you also show us the contents of the
   > errant
   > > > > rows in pg_init_privs for the failing case?
   > > >
   > > > The orphaned-rows problem shouldn't exist in v17 and later (see
   > > > 534287403, 35dd40d34, and related commits).  The OP is apparently
   > > > complaining about an upgrade from v14, where such rows could exist.
   > > >
   > > > I don't especially care for the proposed fix of making pg_upgrade
   > > > refuse to run.  Manually correcting such situations would be tedious
   > > > and error-prone.  Plus, it's inconsistent with what we did about
   > > > related issues with role GRANTs (see 29d75b25b and 74b4438a7).
   > > > I wonder if it'd be sane for pg_dump to just skip dangling role
   > > > references in pg_init_privs.
   > > >
   > > >                         regards, tom lane
   >
   

8. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

   Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-19T07:40:47Z

   Found a problem and fixed it quickly.
   
   v2 introduced a regression: the new query in getAdditionalACLs() joined
   pg_catalog.pg_authid to check whether a grantee OID still exists. pg_authid
   is restricted to superusers because it stores password hashes. That caused
   pg_dump to fail with "permission denied for table pg_authid" whenever it
   ran as a non-superuser role
   
   v3 fixes this by joining pg_catalog.pg_roles instead. pg_roles is a view
   defined directly on top of pg_authid. Since we only need to check whether a
   row with a given OID exists, pg_roles is sufficient and correct here.
   
   
   
   Hüseyin Demir <huseyin.d3r@gmail.com>, 19 Haz 2026 Cum, 08:25 tarihinde
   şunu yazdı:
   
   > I simplified the patch and only changed the SQL query.
   >
   >
   > The v2 patch correctly filters `pg_init_privs` entries whose grantee OID
   > has no corresponding row in `pg_authid`, without affecting valid entries.
   >
   > Regards,
   > Demir.
   >
   > Hüseyin Demir <huseyin.d3r@gmail.com>, 12 Haz 2026 Cum, 18:22 tarihinde
   > şunu yazdı:
   >
   >> Hi,
   >>
   >> I worked on pg_dump and discussed it with Laurenz Albe. Created the
   >> attached patch.
   >>
   >> The fix filters dangling grantees out of each initprivs array at query
   >> time, using NULLIF/ARRAY/NOT EXISTS against pg_authid. Entries for
   >> grantee = 0 (PUBLIC) are never filtered. If all entries for an object
   >> are dangling, NULL is returned and no ACL statement is emitted. Since
   >> we cannot restore grants to non-existent roles. correct outcome,
   >>
   >> The patch includes a TAP test (008_pg_dump_dangling_initprivs.pl) that
   >> reproduces the scenario using allow_system_table_mods to create a
   >> dangling pg_init_privs entry, then verifies pg_dump exits cleanly and
   >> emits no invalid GRANT.
   >>
   >> I have not prepared backpatch branches yet.
   >>
   >> Regards.
   >>
   >> Hüseyin Demir <huseyin.d3r@gmail.com>, 11 Haz 2026 Per, 07:49
   >> tarihinde şunu yazdı:
   >> >
   >> > > The orphaned-rows problem shouldn't exist in v17 and later (see
   >> > > 534287403, 35dd40d34, and related commits).  The OP is apparently
   >> > > complaining about an upgrade from v14, where such rows could exist.
   >> >
   >> > Yes I was working on upgrading the PostgreSQL version from v14 to v18
   >> > and was able to solve the problem by removing the danling records from
   >> > pg_init_privs.
   >> >
   >> > > I wonder if it'd be sane for pg_dump to just skip dangling role
   >> > > references in pg_init_privs.
   >> >
   >> > It will change the behavior of pg_dump and it's a general purpose tool
   >> > because when we instruct pg_dump to filter orphan records it will
   >> > change the content in the system catalogs.
   >> >
   >> > For now I suppose we have two options: either pg_upgrade or pg_dump.
   >> >
   >> > Regards.
   >> >
   >> >
   >> > Tom Lane <tgl@sss.pgh.pa.us>, 7 Haz 2026 Paz, 17:52 tarihinde şunu
   >> yazdı:
   >> > >
   >> > > Greg Sabino Mullane <htamfids@gmail.com> writes:
   >> > > >> 5. Verify orphan records remain in pg_init_privs:
   >> > >
   >> > > > Thanks for providing a failing use case. I ran this on a 18.3
   >> server and
   >> > > > found no orphaned rows - but I used the pg_stat_statements extension
   >> > > > instead of pg_wait_sampling. Could you try your experiment using
   >> > > > pg_stat_statements? And could you also show us the contents of the
   >> errant
   >> > > > rows in pg_init_privs for the failing case?
   >> > >
   >> > > The orphaned-rows problem shouldn't exist in v17 and later (see
   >> > > 534287403, 35dd40d34, and related commits).  The OP is apparently
   >> > > complaining about an upgrade from v14, where such rows could exist.
   >> > >
   >> > > I don't especially care for the proposed fix of making pg_upgrade
   >> > > refuse to run.  Manually correcting such situations would be tedious
   >> > > and error-prone.  Plus, it's inconsistent with what we did about
   >> > > related issues with role GRANTs (see 29d75b25b and 74b4438a7).
   >> > > I wonder if it'd be sane for pg_dump to just skip dangling role
   >> > > references in pg_init_privs.
   >> > >
   >> > >                         regards, tom lane
   >>
   >
   

9. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-06-20T21:53:01Z

   On Fri, 2026-06-19 at 09:40 +0200, Hüseyin Demir wrote:
   > Found a problem and fixed it quickly.
   
   I think that we need something like this fix, because a failing
   upgrade is a bug.  For the same reason I think that the fix
   should be backpatched.
   
   I looked at your patch and found that the query you added doesn't
   cover the important case where the grantor is a non-existing role
   (that is, the original extension owner was dropped).
   The grantor appears in SET SESSION AUTHORIZATION commands in the
   dump, which make the restore and consequently the upgrade fail.
   
   I suggest a query like this one:
   
   SELECT pip.objoid, pip.classoid, pip.objsubid, pip.privtype,
     NULLIF(
       ARRAY(
         SELECT elt FROM pg_catalog.unnest(pip.initprivs) AS elt
         /* that is valid, that is, there is not ... */
         WHERE NOT EXISTS (
           /* ... a non-existing grantor ... */
           SELECT 1 FROM pg_catalog.aclexplode(ARRAY[elt]) ace
           WHERE NOT EXISTS (
             SELECT 1 FROM pg_catalog.pg_roles AS r1
             WHERE r1.oid = ace.grantor
           )
           /* ... or a non-existing grantee that isn't 0 */
           OR ace.grantee <> 0
             AND NOT EXISTS (
               SELECT 1 FROM pg_catalog.pg_roles AS r2
               WHERE r2.oid = ace.grantee
             )
         )
       ),
       ARRAY[]::pg_catalog.aclitem[]
     ) AS initprivs
   FROM pg_catalog.pg_init_privs pip;
   
   You see that I added some comments, because the query is almost
   incomprehensible.  I couldn't think of a more elegant solution.
   
   I think that you also should add an extensive code comment that
   explains why this hack is needed.
   
   I am undecided if the regression test with the artificially created
   broken initial privileges is a good idea or not.  After all, we are
   not testing the real thing here (for example, the test didn't catch
   the omission described above).
   
   I am attaching a test extension that I installed in a v14 database
   to test your patch; perhaps you'll find it useful.  It creates all
   kinds of objects that have an ACL.  Dropping the role that created
   the extension leaves various junk entries in pg_init_privs that you
   can use to test your patch.
   
   Yours,
   Laurenz Albe
   

10. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-06-21T05:47:33Z

    On Sat, 2026-06-20 at 23:53 +0200, I wrote:
    > I think that we need something like this fix, because a failing
    > upgrade is a bug.  For the same reason I think that the fix
    > should be backpatched.
    > 
    > I looked at your patch and found that the query you added doesn't
    > cover the important case where the grantor is a non-existing role
    > 
    > I suggest a query like this one: [...]
    
    Further testing shows that changing this query isn't enough.
    There are three more places where pg_dump queries pg_init_privs
    (in getAggregates, getFuncs and dumpTable).
    
    So we'd have to use a similarly ugly query in all these places,
    which doesn't seem particularly attractive and introduces
    considerable code duplication.
    
    One approach I can think of is to have a macro SAFE_INITPRIVS
    that contains the ugly subquery and is used in all these places.
    
    The other idea is to do some post-processing of the aclitems
    found, but they are in string form and would need to get parsed
    again, which doesn't look attractive either.
    
    Yours,
    Laurenz Albe
    
    
    
    

11. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-22T05:44:45Z

    Hi,
    
    > Further testing shows that changing this query isn't enough.
    > There are three more places where pg_dump queries pg_init_privs
    > (in getAggregates, getFuncs and dumpTable).
    >
    > So we'd have to use a similarly ugly query in all these places,
    > which doesn't seem particularly attractive and introduces
    > considerable code duplication.
    >
    > One approach I can think of is to have a macro SAFE_INITPRIVS
    > that contains the ugly subquery and is used in all these places.
    >
    > The other idea is to do some post-processing of the aclitems
    > found, but they are in string form and would need to get parsed
    > again, which doesn't look attractive either.
    
    Thanks for the feedback.
    I created a simple v4 patch to cover your feedback
    
    v4 applies the filter at all four sites where pg_dump queries pg_init_privs:
    - getAggregates() — WHERE clause comparison
    - getFuncs() — WHERE clause comparison
    - getAdditionalACLs() — SELECT expression (object-level initprivs)
    - PREPQUERY_GETCOLUMNACLS — SELECT expression (column-level initprivs,
    objsubid != 0)
    
    Secondly to avoid duplicating the multi-line subquery at every call
    site, I introduced a SAFE_INITPRIVS(col) macro.
    
    I tried to add more clean and detailed comments for future commits but
    please let me know if you have additional feedback on it.
    
    Regards,
    Demir.
    

12. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-22T06:07:16Z

    >
    > > Further testing shows that changing this query isn't enough.
    > > There are three more places where pg_dump queries pg_init_privs
    > > (in getAggregates, getFuncs and dumpTable).
    > >
    > > So we'd have to use a similarly ugly query in all these places,
    > > which doesn't seem particularly attractive and introduces
    > > considerable code duplication.
    > >
    > > One approach I can think of is to have a macro SAFE_INITPRIVS
    > > that contains the ugly subquery and is used in all these places.
    > >
    > > The other idea is to do some post-processing of the aclitems
    > > found, but they are in string form and would need to get parsed
    > > again, which doesn't look attractive either.
    >
    >
    > v4 applies the filter at all four sites where pg_dump queries pg_init_privs:
    > - getAggregates() — WHERE clause comparison
    > - getFuncs() — WHERE clause comparison
    > - getAdditionalACLs() — SELECT expression (object-level initprivs)
    > - PREPQUERY_GETCOLUMNACLS — SELECT expression (column-level initprivs,
    > objsubid != 0)
    >
    > Secondly to avoid duplicating the multi-line subquery at every call
    > site, I introduced a SAFE_INITPRIVS(col) macro.
    
    One question from my side: can't we use function instead of macro ?
    Would it be more accurate for future readers ?
    
    Wanted to ask your opinion besides the v4 patch.
    
    Regards,
    Demir.
    
    
    
    

13. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-06-22T17:23:38Z

    On Mon, 2026-06-22 at 08:07 +0200, Hüseyin Demir wrote:
    > > v4 applies the filter at all four sites where pg_dump queries pg_init_privs:
    > > - getAggregates() — WHERE clause comparison
    > > - getFuncs() — WHERE clause comparison
    > > - getAdditionalACLs() — SELECT expression (object-level initprivs)
    > > - PREPQUERY_GETCOLUMNACLS — SELECT expression (column-level initprivs,
    > > objsubid != 0)
    > > 
    > > Secondly to avoid duplicating the multi-line subquery at every call
    > > site, I introduced a SAFE_INITPRIVS(col) macro.
    
    Great, that's just what I had in mind.
    
    > One question from my side: can't we use function instead of macro ?
    > Would it be more accurate for future readers ?
    
    That would work too, but if you do it in C rather than with the
    preprocessor, you have to deal with string manipulation, which
    will makes the patch more complicated.  I think it is better the
    way it is now.
    
    I'll mark the patch "ready for committer".
    
    Since there have been very few reports of this problem, the question
    remains if we need this patch at all, or of it should be backpatched.
    My opinion is that it should; every upgrade or restore failure is
    one too many.
    
    Yours,
    Laurenz Albe
    
    
    
    

14. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-06-22T18:30:54Z

    Laurenz Albe <laurenz.albe@cybertec.at> writes:
    > Since there have been very few reports of this problem, the question
    > remains if we need this patch at all, or of it should be backpatched.
    > My opinion is that it should; every upgrade or restore failure is
    > one too many.
    
    I have a more pressing concern: has any performance testing been
    done on this?  It looks like it'd be absolutely catastrophic for
    pg_dump performance on databases with lots of objects.
    
    The implementation direction I'd been vaguely imagining was for
    pg_dump's buildACLCommands() to drop any AclItems that contain
    dangling role references (ie, numeric OIDs where a role name
    should be).  If the given role name contains any non-digit
    characters then it's certainly not dangling, so most of the time
    this'd be a very cheap check.  However, if somebody does
    
    	CREATE USER "007";
    	GRANT ALL ON TABLE mi6_operations TO "007";
    
    we mustn't get fooled by that.  The backend is doing us no favors by
    not making numeric OIDs visibly different from all-digit role names
    in AclItems.  In HEAD I'd advocate fixing that on the server side
    (as attached), but we can't assume that a back-branch server has such
    a fix.  What we could do with an old server is issue a query (once per
    pg_dump run) to collect all the valid all-digit role names, which
    should surely be a short list in most databases, and then filter
    against that within buildACLCommands().
    
    			regards, tom lane
    
    

15. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-23T08:32:34Z

    > I have a more pressing concern: has any performance testing been
    > done on this?  It looks like it'd be absolutely catastrophic for
    > pg_dump performance on databases with lots of objects.
    >
    > The implementation direction I'd been vaguely imagining was for
    > pg_dump's buildACLCommands() to drop any AclItems that contain
    > dangling role references (ie, numeric OIDs where a role name
    > should be).  If the given role name contains any non-digit
    > characters then it's certainly not dangling, so most of the time
    > this'd be a very cheap check.  However, if somebody does
    >
    >         CREATE USER "007";
    >         GRANT ALL ON TABLE mi6_operations TO "007";
    >
    > we mustn't get fooled by that.  The backend is doing us no favors by
    > not making numeric OIDs visibly different from all-digit role names
    > in AclItems.  In HEAD I'd advocate fixing that on the server side
    > (as attached), but we can't assume that a back-branch server has such
    > a fix.  What we could do with an old server is issue a query (once per
    > pg_dump run) to collect all the valid all-digit role names, which
    > should surely be a short list in most databases, and then filter
    > against that within buildACLCommands().
    
    I see the approach and it's a valid concern. I can create a v5 to
    comply with your suggestion and can create a new patch to be applied
    to the current HEAD.
    
    After preparing the v5 I'm going to create a new CF to be patched to
    HEAD since this fix will be backpatched.
    
    Regards,
    Demir.
    
    
    
    

16. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-24T06:14:53Z

    Hi,
    
    You can see the attached v5 patch. Basically, v5 solves the problem by
    adding dangling-role filtering directly inside buildACLCommands(). A
    role name that consists entirely of digits is potentially a dangling
    OID reference and it resolves by querying pg_authid once at the start
    of the dump for any legitimate all-digit role names. The sorted list
    is passed as new digitRoles/nDigitRoles parameters to
    buildACLCommands(), which skips any REVOKE or GRANT item whose grantee
    or grantor matches a dangling reference.
    
    I defined a static called is_dangling_role_ref in dumputils.c and
    introduced two new parameters to buildACLCommands to eliminate the
    dangling grantee or grantor objects.
    
    When it comes to patching the HEAD I'm going to create and submit
    another small patch.
    
    Regards,
    Demir.
    
    Hüseyin Demir <huseyin.d3r@gmail.com>, 23 Haz 2026 Sal, 10:32
    tarihinde şunu yazdı:
    >
    > > I have a more pressing concern: has any performance testing been
    > > done on this?  It looks like it'd be absolutely catastrophic for
    > > pg_dump performance on databases with lots of objects.
    > >
    > > The implementation direction I'd been vaguely imagining was for
    > > pg_dump's buildACLCommands() to drop any AclItems that contain
    > > dangling role references (ie, numeric OIDs where a role name
    > > should be).  If the given role name contains any non-digit
    > > characters then it's certainly not dangling, so most of the time
    > > this'd be a very cheap check.  However, if somebody does
    > >
    > >         CREATE USER "007";
    > >         GRANT ALL ON TABLE mi6_operations TO "007";
    > >
    > > we mustn't get fooled by that.  The backend is doing us no favors by
    > > not making numeric OIDs visibly different from all-digit role names
    > > in AclItems.  In HEAD I'd advocate fixing that on the server side
    > > (as attached), but we can't assume that a back-branch server has such
    > > a fix.  What we could do with an old server is issue a query (once per
    > > pg_dump run) to collect all the valid all-digit role names, which
    > > should surely be a short list in most databases, and then filter
    > > against that within buildACLCommands().
    >
    > I see the approach and it's a valid concern. I can create a v5 to
    > comply with your suggestion and can create a new patch to be applied
    > to the current HEAD.
    >
    > After preparing the v5 I'm going to create a new CF to be patched to
    > HEAD since this fix will be backpatched.
    >
    > Regards,
    > Demir.
    

17. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-06-24T09:56:58Z

    On Wed, 2026-06-24 at 08:14 +0200, Hüseyin Demir wrote:
    > You can see the attached v5 patch. Basically, v5 solves the problem by
    > adding dangling-role filtering directly inside buildACLCommands(). A
    > role name that consists entirely of digits is potentially a dangling
    > OID reference and it resolves by querying pg_authid once at the start
    > of the dump for any legitimate all-digit role names.
    
    The patch looks good and passes my tests.
    
    It fails the regression tests on my system with
    
      pg_dump: error: query failed: ERROR:  permission denied for table pg_authid
    
    I think you should use pg_roles rather than pg_authid, so that it
    remains possible to use pg_dump with a non-superuser.
    
    While the technique of fetching the all-numeric role names in advance
    is certainly much cheaper than running a complicated subquery for
    every object dumped, I have one remaining doubt:
    
    What if there is a dangling role OID 65432 in pg_init_privs *and*
    a valid role with the same name (but a different OID)?  Then the patch
    would tacitly restore the dangling reference to the latter role.
    Apart from the result being wrong, I wonder if that could be used for
    a privilege escalation attack: you detect that there are dangling
    pg_init_privs entries that grant high privileges.  Then you abuse your
    CREATEROLE to create a role with the same name as the dangling OID.
    After a dump and restore, your role has been assigned those privileges.
    
    Perhaps it would be a better approach to fetch the data from
    pg_init_privs once at the beginning of the dump, ignoring the entries
    with dangling OIDs?
    
    Yours,
    Laurenz Albe
    
    
    
    

18. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-24T12:19:47Z

    >
    > While the technique of fetching the all-numeric role names in advance
    > is certainly much cheaper than running a complicated subquery for
    > every object dumped, I have one remaining doubt:
    >
    > What if there is a dangling role OID 65432 in pg_init_privs *and*
    > a valid role with the same name (but a different OID)?  Then the patch
    > would tacitly restore the dangling reference to the latter role.
    > Apart from the result being wrong, I wonder if that could be used for
    > a privilege escalation attack: you detect that there are dangling
    > pg_init_privs entries that grant high privileges.  Then you abuse your
    > CREATEROLE to create a role with the same name as the dangling OID.
    > After a dump and restore, your role has been assigned those privileges.
    >
    > Perhaps it would be a better approach to fetch the data from
    > pg_init_privs once at the beginning of the dump, ignoring the entries
    > with dangling OIDs?
    >
    > Yours,
    > Laurenz Albe
    
    Thank you for the reviews.
    
    This v6 patch adds a SAFE_INITPRIVS macro that filters aclitem[]
    arrays server-side by checking that each entry's grantor and grantee
    OID still exists in pg_roles. It is applied in exactly two queries:
    
    1. getAdditionalACLs() -- the one-time fetch of pg_init_privs at startup
    2. dumpTable() column ACL prepared statement -- per-table column initprivs
    
    Crucially, the WHERE clauses in getAggregates()/getFuncs() are NOT
    modified. Those queries use raw pip.initprivs only for object
    selection (not output), and any spuriously-selected objects produce
    zero output because the stored initprivs (from getAdditionalACLs) is
    already filtered.
    
    pg_roles is used instead of pg_authid to support non-superuser pg_dump.
    
    So this patch covers the security and performance concerns together I
    believe. In addition to this, I tried to introduce different tests to
    verify it.
    
    When it comes to performance results I tried to draw a conclusion.
    Performance testing shows the overhead is ~1-2ms on the one-time
    pg_init_privs fetch (249-749 rows) and zero measurable impact on a
    database with 10,000 functions + 500 aggregates.
    
    The filtering adds ~1ms (249 rows) to ~2ms (749 rows) to the one-time
    getAdditionalACLs() query that runs at pg_dump startup. This is a
    fixed cost that does NOT scale with the number of functions,
    aggregates, or other objects in the database.
    
    Let me know if you have additional feedback and concerns.
    
    Regards,
    Demir
    

19. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-06-24T14:57:58Z

    Laurenz Albe <laurenz.albe@cybertec.at> writes:
    > What if there is a dangling role OID 65432 in pg_init_privs *and*
    > a valid role with the same name (but a different OID)?  Then the patch
    > would tacitly restore the dangling reference to the latter role.
    
    That is a good point, but I would put the blame on aclitemout: in such
    a case it's entirely impossible for pg_dump to distinguish whether an
    apparently all-numeric role name in an ACL item is the valid role or a
    dangling OID.
    
    I was tempted yesterday to propose a simpler solution in which
    we back-patch the putid() fix I showed earlier, and just change
    dumputils.c to drop ACLs that have unquoted all-numeric grantees.
    (If the grantor part is a dangling OID, we could omit GRANTED BY, as
    we did recently for role grants.)  Now the problem with this is that
    if you have a case like my "007" example, you're going to lose some
    grants if you dump with an updated pg_dump from a not-updated server.
    That cure is very likely worse than the disease.
    
    So what I'm thinking today is we apply the putid() fix only in HEAD,
    and make dumputils.c ignore unquoted all-numeric roles only if
    server version >= 19.  This means we don't have a fix for the actually
    known problems with old server versions, which is kind of sad, but
    given Laurenz's point I don't think a reliable fix is possible with
    an unpatched server.
    
    An alternative answer is to back-patch the putid() fix and teach
    dumputils.c to consider the server minor version when deciding whether
    to reject unquoted all-numeric roles.  We don't typically make pg_dump
    pay attention to minor versions, but it would provide a pathway for
    users to deal with this problem: if you've got dangling grants then
    update the old server before dumping.
    
    			regards, tom lane
    
    
    
    

20. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-25T07:55:59Z

    Hi,
    
    > So what I'm thinking today is we apply the putid() fix only in HEAD,
    > and make dumputils.c ignore unquoted all-numeric roles only if
    > server version >= 19.  This means we don't have a fix for the actually
    > known problems with old server versions, which is kind of sad, but
    > given Laurenz's point I don't think a reliable fix is possible with
    > an unpatched server.
    > An alternative answer is to back-patch the putid() fix and teach
    > dumputils.c to consider the server minor version when deciding whether
    > to reject unquoted all-numeric roles.  We don't typically make pg_dump
    > pay attention to minor versions, but it would provide a pathway for
    > users to deal with this problem: if you've got dangling grants then
    > update the old server before dumping.
    
    It will require an almost mandatory minor-version update prior to the
    upgrade, which adds extra friction and a more complex upgrade story
    for our users since both sides need to be updated.
    Another point is that quoting all-digit roles alters the aclitem::text
    representation across psql, extensions, and client apps giving it a
    much broader behavioral impact than just pg_dump.
    
    Finally, it introduces a bit of a maintenance headache for committers,
    who will have to adjust the macro on HEAD after back-patching to v18.
    
    Happy to hear other ideas.
    
    Regards,
    Demir.
    
    
    
    

21. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Rui Zhao <zhaorui126@gmail.com> — 2026-06-25T10:44:38Z

    Hi Hüseyin,
    
    I reviewed and tested v6. The filtering logic is correct and applied in the
    right place: doing it in the source queries (getAdditionalACLs and the
    column-level ACL query) means the dangling entries never reach the
    binary-upgrade "SET SESSION AUTHORIZATION <oid>" path, which is where the
    upgrade actually broke. Using pg_roles instead of pg_authid for the
    existence check is also right for non-superuser pg_dump. With the test
    running, all of its assertions pass.
    
    Two things on the test:
    
    1. The TAP test doesn't run for me at all -- it dies in setup with
       "role \"rui\" does not exist". The cause is that the aclitem literals
       are built by concatenating current_user unquoted, e.g.
    
           ARRAY[('ghost_grantee=X/' || current_user)::aclitem]
    
       My bootstrap superuser is "rui.zhao", so this becomes
       'ghost_grantee=X/rui.zhao', and aclitemin parses the grantor only up to
       the dot:
    
           =# SELECT ('g=X/' || 'a.b')::aclitem;
           ERROR:  role "a" does not exist
    
       So the test fails before any assertion runs on any cluster whose
       superuser name needs quoting (a dot, uppercase, etc.). Wrapping it as
       quote_ident(current_user) in the four aclitem literals fixes it (the
       test then passes 12/12 here). A bit ironic given the patch is about
       handling odd role names.
    
    2. The PUBLIC case (grantee = 0) isn't covered. The "ace.grantee <> 0"
       branch is what keeps PUBLIC grants from being filtered, but there's no
       test for either direction: a valid PUBLIC grant ("=r/validgrantor")
       being kept, or a PUBLIC grant whose grantor is dangling ("=r/ghost")
       being dropped. Worth a case or two.
    
    Thanks,
    Rui
    
    
    
    

22. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-06-25T13:53:25Z

    =?UTF-8?Q?H=C3=BCseyin_Demir?= <huseyin.d3r@gmail.com> writes:
    >> An alternative answer is to back-patch the putid() fix and teach
    >> dumputils.c to consider the server minor version when deciding whether
    >> to reject unquoted all-numeric roles.  We don't typically make pg_dump
    >> pay attention to minor versions, but it would provide a pathway for
    >> users to deal with this problem: if you've got dangling grants then
    >> update the old server before dumping.
    
    > It will require an almost mandatory minor-version update prior to the
    > upgrade, which adds extra friction and a more complex upgrade story
    > for our users since both sides need to be updated.
    
    Only if you actually have dangling ACL references, which I think
    is the case for a vanishingly small set of users --- otherwise
    we'd have heard more complaints and been motivated to fix this
    long ago.  Let's not optimize for the broken case.
    
    			regards, tom lane
    
    
    
    

23. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-06-26T05:18:28Z

    On Wed, 2026-06-24 at 14:19 +0200, Hüseyin Demir wrote:
    > 
    > This v6 patch adds a SAFE_INITPRIVS macro that filters aclitem[]
    > arrays server-side by checking that each entry's grantor and grantee
    > OID still exists in pg_roles. It is applied in exactly two queries:
    > 
    > 1. getAdditionalACLs() -- the one-time fetch of pg_init_privs at startup
    > 2. dumpTable() column ACL prepared statement -- per-table column initprivs
    
    No, that's not good.  If you are running the complicated subquery for
    every table dumped, you are re-introducing the performance regression
    from the v4 patch that Tom justly complained about.
    
    On the other hand, I agree with you that Tom's idea to make this fix
    depend on a minor update of the source server that fixes the string
    representation of aclitems is not so great.  Few people undergo the
    hassle of applying the latest minor update to a server they are about
    to update (and I am not even speaking about the users who keep running
    on the 14.3 they went into production with).  Yes, the problem that
    the present patch is trying to address is a rare one, and we should
    keep the maintenance and performance burden incurred moderate.
    But what good is a fix that won't work for a good percentage of the
    affected cases, even if they are few?
    
    Here is my latest idea (hold your noses):
    Instead of having pg_dump query "FROM pg_catalog.pg_init_privs pip",
    how about writing "(FROM (VALUES (...), (...), ...) AS pip", where the
    VALUES clause is composed from a query against pg_init_privs run once
    at the beginning of pg_dump that excludes the bad entries?
    Critizism I forsee is that a) this is ugly and b) very long VALUES
    statements might also constitute a performance regression.
    However, I have yet to see an extension that produces a hundred
    initial privilege entries.
    
    Yours,
    Laurenz Albe
    
    
    
    

24. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Hüseyin Demir <huseyin.d3r@gmail.com> — 2026-06-26T06:56:00Z

    > > This v6 patch adds a SAFE_INITPRIVS macro that filters aclitem[]
    > > arrays server-side by checking that each entry's grantor and grantee
    > > OID still exists in pg_roles. It is applied in exactly two queries:
    > >
    > > 1. getAdditionalACLs() -- the one-time fetch of pg_init_privs at startup
    > > 2. dumpTable() column ACL prepared statement -- per-table column initprivs
    >
    > No, that's not good.  If you are running the complicated subquery for
    > every table dumped, you are re-introducing the performance regression
    > from the v4 patch that Tom justly complained about.
    
    The correlated subquery in getAdditionalACLs adds overhead
    proportional to pg_init_privs row count × aclitems per row, with each
    check being an indexed OID lookup. For a database with 5,000
    pg_init_privs rows this is probably a few extra milliseconds. I added
    performance test results with v6. I think it's a sustainable operation
    but If I'm missing sth. let me know.
    
    > On the other hand, I agree with you that Tom's idea to make this fix
    > depend on a minor update of the source server that fixes the string
    > representation of aclitems is not so great.  Few people undergo the
    > hassle of applying the latest minor update to a server they are about
    > to update (and I am not even speaking about the users who keep running
    > on the 14.3 they went into production with).  Yes, the problem that
    > the present patch is trying to address is a rare one, and we should
    > keep the maintenance and performance burden incurred moderate.
    > But what good is a fix that won't work for a good percentage of the
    > affected cases, even if they are few?
    >
    > Here is my latest idea (hold your noses):
    > Instead of having pg_dump query "FROM pg_catalog.pg_init_privs pip",
    > how about writing "(FROM (VALUES (...), (...), ...) AS pip", where the
    > VALUES clause is composed from a query against pg_init_privs run once
    > at the beginning of pg_dump that excludes the bad entries?
    > Critizism I forsee is that a) this is ugly and b) very long VALUES
    > statements might also constitute a performance regression.
    > However, I have yet to see an extension that produces a hundred
    > initial privilege entries.
    
    I'm not against this approach but the tradeoff is the same with v6 I
    suppose. Since the overhead of processing the full VALUES clause on
    every EXECUTE is unnecessary. v6 goes directly to pg_init_privs
    indexed by objoid = $1.
    
    But definitely we can prepare a new patch to cover these expectations.
    
    With that in mind, I would like to propose splitting the work into two
    separate patches:
    
    1. pg_dump fix (backpatch to 14): the v6 SQL OID-level filter. Works
    on any server >= 9.6 without requiring a source-side update. Fixes the
    known breakage for users upgrading from pre-17 clusters that
    accumulated dangling entries via DROP ROLE.
    
    2. putid() fix (HEAD only): improve aclitemout so that a dangling OID
    is rendered in a form that is unambiguously distinct from a valid role
    name.  This is the right long-term fix for the text representation and
    would benefit any future tool that processes aclitem output.  It is
    independent of the pg_dump fix and does not need to be backpatched for
    patch 1 to be correct.
    
    New dangling entries will not live on modern servers.  However, users
    upgrading from pre-17 clusters may have years of accumulated dangling
    entries that pg_upgrade will carry forward silently.  The pg_dump fix
    is a one-time for that migration window.
    Does this split make sense to the reviewers?  If so, I will prepare v7
    of the pg_dump patch (incorporating any further feedback on v6) and a
    separate patch for the putid() fix in HEAD.
    
    Regards,
    Demir.
    
    
    
    

25. Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

    Laurenz Albe <laur@aon.at> — 2026-06-26T08:30:28Z

    On Fri, 2026-06-26 at 08:56 +0200, Hüseyin Demir wrote:
    > > > This v6 patch adds a SAFE_INITPRIVS macro that filters aclitem[]
    > > > arrays server-side by checking that each entry's grantor and grantee
    > > > OID still exists in pg_roles. It is applied in exactly two queries:
    > > > 
    > > > 1. getAdditionalACLs() -- the one-time fetch of pg_init_privs at startup
    > > > 2. dumpTable() column ACL prepared statement -- per-table column initprivs
    > > 
    > > No, that's not good.  If you are running the complicated subquery for
    > > every table dumped, you are re-introducing the performance regression
    > > from the v4 patch that Tom justly complained about.
    > 
    > The correlated subquery in getAdditionalACLs adds overhead
    > proportional to pg_init_privs row count × aclitems per row, with each
    > check being an indexed OID lookup. For a database with 5,000
    > pg_init_privs rows this is probably a few extra milliseconds. I added
    > performance test results with v6. I think it's a sustainable operation
    > but If I'm missing sth. let me know.
    
    I have no problem with the subquery being in getAdditionalACLs().
    But dumpTable() is executed once per table that gets dumped, right?
    So the complicated subquery will run once per table.
    
    > > Here is my latest idea (hold your noses):
    > > Instead of having pg_dump query "FROM pg_catalog.pg_init_privs pip",
    > > how about writing "(FROM (VALUES (...), (...), ...) AS pip", where the
    > > VALUES clause is composed from a query against pg_init_privs run once
    > > at the beginning of pg_dump that excludes the bad entries?
    > > Critizism I forsee is that a) this is ugly and b) very long VALUES
    > > statements might also constitute a performance regression.
    > > However, I have yet to see an extension that produces a hundred
    > > initial privilege entries.
    > 
    > I'm not against this approach but the tradeoff is the same with v6 I
    > suppose. Since the overhead of processing the full VALUES clause on
    > every EXECUTE is unnecessary. v6 goes directly to pg_init_privs
    > indexed by objoid = $1.
    
    Ah, so you are saying that the effort per table will be much less.
    
    Could you measure the difference with a database with - say - 10000 tables?
    
    > But definitely we can prepare a new patch to cover these expectations.
    
    Let's agree on the proper approach before you go to the effort of writing
    another patch.
    
    > With that in mind, I would like to propose splitting the work into two
    > separate patches:
    > 
    > 1. pg_dump fix (backpatch to 14): the v6 SQL OID-level filter. Works
    > on any server >= 9.6 without requiring a source-side update. Fixes the
    > known breakage for users upgrading from pre-17 clusters that
    > accumulated dangling entries via DROP ROLE.
    > 
    > 2. putid() fix (HEAD only): improve aclitemout so that a dangling OID
    > is rendered in a form that is unambiguously distinct from a valid role
    > name.  This is the right long-term fix for the text representation and
    > would benefit any future tool that processes aclitem output.  It is
    > independent of the pg_dump fix and does not need to be backpatched for
    > patch 1 to be correct.
    
    I think that we all agree on that.
    
    > New dangling entries will not live on modern servers.  However, users
    > upgrading from pre-17 clusters may have years of accumulated dangling
    > entries that pg_upgrade will carry forward silently.
    
    ... or rather fail doing so ...
    
    >                                                       The pg_dump fix
    > is a one-time for that migration window.
    
    Right.
    
    > Does this split make sense to the reviewers?  If so, I will prepare v7
    > of the pg_dump patch (incorporating any further feedback on v6) and a
    > separate patch for the putid() fix in HEAD.
    
    It makes sense to me.
    
    I'd say that your v6 patch is fine if the performance impact can be
    shown to be small.
    
    Yours,
    Laurenz Albe