[Bug] pg_upgrade could fail for non-superuser subscriptions using foreign servers
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
Attachments:
repro.txttext/plain; name=repro.txtDownload
Hi hackers,
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.
So here is a patch. While coding, I found another issue that TOC entry for
"SUBSCRIPTION TABLE" may not be skipped in case of --no-subscriptions.
This can be an issue when:
1) there are tuples in pg_subscription_rel,
2) pg_dump is done with --binary-upgrade then
3) pg_restore is done with --no-subscription.
I think it seldom happens, but I could not find reasons to retain.
How do you feel?
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
0001-ignore-SUBSCRIPTION-TABLE-in-case-of-no-subscription.patchapplication/octet-stream; name=0001-ignore-SUBSCRIPTION-TABLE-in-case-of-no-subscription.patchDownload+3-2
0002-Fix-pg_upgrade-failure-with-subscriptions-using-fore.patchapplication/octet-stream; name=0002-Fix-pg_upgrade-failure-with-subscriptions-using-fore.patchDownload+50-3
On Thu, 23 Jul 2026 at 08:30, Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:
Hi hackers,
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.So here is a patch. While coding, I found another issue that TOC entry for
"SUBSCRIPTION TABLE" may not be skipped in case of --no-subscriptions.
This can be an issue when:1) there are tuples in pg_subscription_rel,
2) pg_dump is done with --binary-upgrade then
3) pg_restore is done with --no-subscription.I think it seldom happens, but I could not find reasons to retain.
How do you feel?
Hi Kuroda-san,
I tried to reproduce the issue. To reproduce this issue, pg_restore
must be run against a server started in binary upgrade mode.
Also I noticed when we try to use pg_restore with --no-subscription,
in this case we get an error:
pg_restore: error: could not execute query: ERROR: subscription
"sub1" does not exist
Command was:
-- For binary upgrade, must preserve the subscriber table.
SELECT pg_catalog.binary_upgrade_add_sub_rel_state('sub1', 16384, 'r',
'0/01750B90');
I have verified that the fix in 0001 addresses this issue.
Although this combination of options is uncommon, it seems more
consistent for --no-subscriptions to skip SUBSCRIPTION TABLE entries
as well to avoid the above error. I think we should keep this fix.
I am also able to reproduce the issue in [1]/messages/by-id/OS9PR01MB12149C3ED34272966B25DB173F5C12@OS9PR01MB12149.jpnprd01.prod.outlook.com with the steps provided
and I am still reviewing the fix in 0002 patch.
[1]: /messages/by-id/OS9PR01MB12149C3ED34272966B25DB173F5C12@OS9PR01MB12149.jpnprd01.prod.outlook.com
Thanks,
Shlok Kyal
On Thu, 23 Jul 2026 at 08:30, Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:
Hi hackers,
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.So here is a patch. While coding, I found another issue that TOC entry for
"SUBSCRIPTION TABLE" may not be skipped in case of --no-subscriptions.
This can be an issue when:1) there are tuples in pg_subscription_rel,
2) pg_dump is done with --binary-upgrade then
3) pg_restore is done with --no-subscription.I think it seldom happens, but I could not find reasons to retain.
How do you feel?
I reviewed the 0002 patch and tested it. I resolved the mentioned issue.
I have some minor comments.
1. Should we change the comment below?
+ /*
+ * ALTER SUBSCRIPTION OWNER TO command must be RESTORE_PASS_POST_ACL. The
+ * subscription may not be owned by the superuser and may depend on the
+ * foreign server. GRANT statement for the foreign server must be executed
+ * before the alternation.
+ */
To something like:
/*
* The ALTER SUBSCRIPTION ... OWNER TO command must be deferred to
* RESTORE_PASS_POST_ACL. The subscription may not be owned by a
* superuser and may depend on privileges on a foreign server. The
* GRANT on that foreign server must be executed before applying the
* ownership change.
*/2. Should we change some comments as describe below?
+ /*
+ * If the subscription refers to a foreign server, we must execute the
+ * ALTER SUBSCRIPTION OWNER TO command separately. If a non-superuser
+ * owns the subscription and the user does not own the foreign server,
+ * ALTER SUBSCRIPTION OWNER TO will be executed before the GRANT ... ON
+ * FOREIGN SERVER; this causes a failure in the restore phase. To
+ * address the issue, the ALTER SUBSCRIPTION command can be handled as
+ * a separate TOC entry and restored after ACL commands.
+ */
2.a. Should we change "If a non-superuser owns the subscription and
the user does not own the foreign server" to
"If a non-superuser owns the subscription and does not own the foreign server"?2.b. Should we change "address the issue, the ALTER SUBSCRIPTION command" to
"address the issue, the ALTER SUBSCRIPTION OWNER TO command"?
Thanks,
Shlok Kyal
On Thu, 2026-07-23 at 03:00 +0000, Hayato Kuroda (Fujitsu) wrote:
Hi hackers,
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.So here is a patch. While coding, I found another issue that TOC
entry for
"SUBSCRIPTION TABLE" may not be skipped in case of --no-
subscriptions.
This can be an issue when:1) there are tuples in pg_subscription_rel,
2) pg_dump is done with --binary-upgrade then
3) pg_restore is done with --no-subscription.
Thank you! There's a related discussion here:
/messages/by-id/e96efe16fb47fcca4ae0cd0157fb6bc662470712.camel@j-davis.com
Regards,
Jeff Davis
On Thu, 2026-07-23 at 03:00 +0000, Hayato Kuroda (Fujitsu) wrote:
While coding, I found another issue that TOC entry for
"SUBSCRIPTION TABLE" may not be skipped in case of --no-
subscriptions.
Thank you. The patch looks straightforward and independent, introduced
in 9a17be1e24. Amit, would you please look at 0001?
Regards,
Jeff Davis
On Wed, Jul 29, 2026 at 2:39 AM Jeff Davis <pgsql@j-davis.com> wrote:
On Thu, 2026-07-23 at 03:00 +0000, Hayato Kuroda (Fujitsu) wrote:
While coding, I found another issue that TOC entry for
"SUBSCRIPTION TABLE" may not be skipped in case of --no-
subscriptions.Thank you. The patch looks straightforward and independent, introduced
in 9a17be1e24. Amit, would you please look at 0001?
Yes, I'll look into it. Thanks!
--
With Regards,
Amit Kapila.
On Wed, Jul 29, 2026 at 9:06 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Wed, Jul 29, 2026 at 2:39 AM Jeff Davis <pgsql@j-davis.com> wrote:
On Thu, 2026-07-23 at 03:00 +0000, Hayato Kuroda (Fujitsu) wrote:
While coding, I found another issue that TOC entry for
"SUBSCRIPTION TABLE" may not be skipped in case of --no-
subscriptions.Thank you. The patch looks straightforward and independent, introduced
in 9a17be1e24. Amit, would you please look at 0001?Yes, I'll look into it. Thanks!
Pushed.
--
With Regards,
Amit Kapila.