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

On Sun, 2026-07-19 at 15:32 -0700, Jeff Davis wrote:
> Generating and validating the connection requires the subscription
> owner to be set correctly, the foreign server ACLs to be set, and the
> user mapping to exist. The checks at DDL time are were a convenient
> way
> to catch errors, but end up being too strict because those things can
> change before the connection is actually needed. In particular,
> pg_dump
> does the DDL in parts (first creating the subscription, then changing
> the owner), and we need the first part to succeed.

There are two other threads discussing closely-related problems:

https://www.postgresql.org/message-id/CAHGQGwFGa6+wWVgUmZPFwN=fBY59mYPkMK3=TxT=Pv5C1mNNRQ@mail.gmail.com
https://www.postgresql.org/message-id/OS9PR01MB12149C3ED34272966B25DB173F5C12@OS9PR01MB12149.jpnprd01.prod.outlook.com

I'd like to step back and discuss where the complexity comes from:

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

We can preserve the most useful kinds of validation by putting it
behind an if (!superuser()) guard, so it doesn't interfere with
restores.

Thoughts?

Regards,
Jeff Davis