[Bug] pg_upgrade could fail for non-superuser subscriptions using foreign servers

From: "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>
To: "'pgsql-hackers(at)lists(dot)postgresql(dot)org'" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Jeff Davis <pgsql(at)j-davis(dot)com>
Subject: [Bug] pg_upgrade could fail for non-superuser subscriptions using foreign servers
Date: 2026-07-22 13:07:20
Message-ID: OS9PR01MB12149C3ED34272966B25DB173F5C12@OS9PR01MB12149.jpnprd01.prod.outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dear hackers,
(including Jeff, because 8185bb53 is written by you)

While working on another thread, I found that pg_upgrade could not upgrade nodes
if a subscription is created by non-superuser and it refers the foreign server.
This happened because GRANT ON FOREIGN SERVER can be executed after the ALTER
SUBSCRIPTION OWNER TO command. Please see the attached reproducer.

The upgrade would fail while restoring objects to the new node:

```
$ pg_upgrade ...
...
Performing Upgrade
------------------
...
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
postgres
*failure*

Consult the last few lines of "new/pg_upgrade_output.d/20260722T214937.529/log/pg_upgrade_dump_5.log" for
the probable cause of the failure.
Failure, exiting
```

And you can see that the restoring can happen after the ALTER SUBSCRIPTION.

```
$ pg_restore --file - new/pg_upgrade_output.d/20260722T214937.529/dump/pg_upgrade_dump_5.custom
...
CREATE SERVER "server" FOREIGN DATA WRAPPER "postgres_fdw" OPTIONS (
"dbname" 'postgres',
"port" '5432'
);
...
--
-- Name: sub; Type: SUBSCRIPTION; Schema: -; Owner: test
--

CREATE SUBSCRIPTION "sub" SERVER "server" PUBLICATION "pub" WITH (connect = false, slot_name = 'sub', streaming = parallel);

ALTER SUBSCRIPTION sub OWNER TO test;
...
GRANT ALL ON FOREIGN SERVER "server" TO "test";
```

IIUC, it happens because in pg_restore command, altering the owner is done in the
main pass but ACL commands is done afterward. Some special objects can be
restored after ACL commands, but subscription does not have such a treatment.

My primitive idea for fix is to introduce a new TOC entry to record the OWNER
command, in the pg_dump. Unlike the normal CREATE SUBSCRIPTION, this entry
can be handled as RESTORE_PASS_POST_ACL.
I'm locally working on the idea and will post tomorrow. But better ideas are also welcome.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Attachment Content-Type Size
repro.txt text/plain 1.1 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Christoph Berg 2026-07-22 13:12:26 Re: Allow pg_read_all_stats to see database size in \l+
Previous Message Nathan Bossart 2026-07-22 13:06:44 Re: convert various variables to atomics