Re: CREATE SUBSCRIPTION ... SERVER vs. pg_dump, etc.

On Tue, 2026-07-28 at 09:10 -0700, Jeff Davis wrote:
> * During restore, we simply want it to recreate the right catalog
> state, and it uses multiple commands to do so (CREATE SUBSCRIPTION,
> ALTER SUBSCRIPTION OWNER TO, etc.). It should never connect to the
> publisher, and validation is mostly counterproductive for the
> intermediate states.
>
> * When we no longer need a slot (due to ALTER/DROP), we would like to
> drop it from the publisher, but for various reasons a connection to
> the
> publisher may be impossible. In that case, the user may still want
> the
> ALTER/DROP to succeed.
>
> * Validation at DDL-time is useful for interactive purposes, but
> limited. Whatever is validated may change before connection time
> (e.g.
> privileges on the server may be revoked), so connection-time
> validation
> is the authoritative one.
>
> To reconcile these goals, we need to weaken DDL-time validation a
> bit,
> be more precise about when we try to generate a conninfo, and then be
> sure that restore doesn't do anything that would cause a conninfo to
> be
> generated or a connection to happen.

Summary of which commands require a connection (and therefore cannot be
used during restore):

CREATE SUBSCRIPTION iff connect=true
ALTER SUBSCRIPTION SET (failover|twophase) iff slot_name
ALTER SUBSCRIPTION SET|ADD|DROP PUBLICATION iff refresh
ALTER SUBSCRIPTION REFRESH
DROP SUBSCRIPTION iff slot_name or tablesync slots

Proposal:

* Form a conninfo if and only if a connection is immediately
required. That is, it's one of the DDL commands above, or a
logical worker.

* Check USAGE on the server when a connection is formed or when
ALTER sets the server or when the subscription owner changes
(unless superuser changes the owner, in which case it may be part
of a multi-command DDL sequence during restore).

* Check that the server's FDW supports a connection function when
DDL sets the subscription's server.

* Check that a user mapping exists during DDL when the server or
owner changes, but demote the message to a WARNING, because it
may be part of a multi-command DDL sequence during restore.
- CREATE SUBSCRIPTION already issues WARNINGS during restore.

* Ensure that none of the commands during restore need a connection.
- check_pub_rdt should happen at connection time, and only
opportunistically at DDL time if already forming a connection

* Check walrcv_check_conninfo() before connecting, or during
CREATE/ALTER SUBSCRIPTION ... CONNECTION.
- If a connection is not needed for DDL, and it's a server-based
subscription, walrcv_check_conninfo() will be called only by
the logical worker when a connection is needed.
- That loses some convenience for interactive DDL, but avoids
false positive failures during restore.

If we reordered the commands during restore, as in Hayato Kuroda's
second patch[1], we could tighten the checks. But I'm not sure we want
to do that for v19.

Regards,
Jeff Davis

[1]
https://www.postgresql.org/message-id/OS9PR01MB121493DA4C1A7748B11A646D8F5C02%40OS9PR01MB12149.jpnprd01.prod.outlook.com