| 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 |
| 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 |