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

On Thu, 23 Jul 2026 at 08:30, Hayato Kuroda (Fujitsu)
<kuroda(dot)hayato(at)fujitsu(dot)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