Validate SERVER subscription conninfo during ALTER SUBSCRIPTION ... OWNER TO

On Tue, Jul 21, 2026 at 3:23 PM Fujii Masao <masao.fujii@gmail.com> wrote:

Hi,

While tryng the new v19 feature CREATE/ALTER SUBSCRIPTION ... SERVER,
I found that ALTER SUBSCRIPTION ... OWNER TO can change the effective
conninfo for a SERVER subscription, but does not validate the new owner's
generated conninfo with walrcv_check_conninfo().

As a result, the owner of a SERVER subscription with password_required=true
can be changed to a non-superuser whose generated connection string does
not
contain a password. The ownership change succeeds, but the subscription is
left in a state that will fail later when a worker or another command
tries to
connect.

Isn't this problematic?

The attached patch fixes this by making ALTER SUBSCRIPTION ... OWNER TO
validate the new owner's generated conninfo with walrcv_check_conninfo().
It also updates the ALTER SUBSCRIPTION documentation to mention
the SERVER-specific requirements for changing the owner.

Thought?

Regards,

--
Fujii Masao

Sounds correct to me to do the conninfo validation.

A small comment on the documentation change:

+   for the new owner or for <literal>PUBLIC</literal> must exist, and the
+   connection string generated for the new owner must satisfy the
subscription's
+   <literal>password_required</literal> setting.

From the implementation, only when a new owner is not superuser, it needs
to satisfy the password_required setting.

Regards,
Yuanchao Zhang

Dear Fujii-san,

While tryng the new v19 feature CREATE/ALTER SUBSCRIPTION ... SERVER,
I found that ALTER SUBSCRIPTION ... OWNER TO can change the effective
conninfo for a SERVER subscription, but does not validate the new owner's
generated conninfo with walrcv_check_conninfo().
As a result, the owner of a SERVER subscription with password_required=true
can be changed to a non-superuser whose generated connection string does not
contain a password.

Not sure, but can we also meet the similar situation via ALTER USER MAPPING
... (DROP password) command? I.e., the command would succeed but the worker
will fail.
If so, should we also fix the case? Or it's something like "Don't do that" situation?

Best regards,
Hayato Kuroda
FUJITSU LIMITED

On Tue, Jul 21, 2026 at 4:37 PM yuanchao zhang <145zhangyc@gmail.com> wrote:

Sounds correct to me to do the conninfo validation.

Thanks for the review!

A small comment on the documentation change:

+   for the new owner or for <literal>PUBLIC</literal> must exist, and the
+   connection string generated for the new owner must satisfy the subscription's
+   <literal>password_required</literal> setting.

From the implementation, only when a new owner is not superuser, it needs to satisfy the password_required setting.

Agreed. I've updated the patch as suggested. The updated version is attached.

-   connection string generated for the new owner must satisfy the
subscription's
-   <literal>password_required</literal> setting.
+   connection string generated for the new owner must be valid.  If the new
+   owner is not a superuser and the subscription has
+   <literal>password_required=true</literal>, the generated connection string
+   must include a password.

Regards,

--
Fujii Masao

On Tue, Jul 21, 2026 at 6:45 PM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:

Not sure, but can we also meet the similar situation via ALTER USER MAPPING
... (DROP password) command? I.e., the command would succeed but the worker
will fail.
If so, should we also fix the case? Or it's something like "Don't do that" situation?

Yes, a similar situation can probably be created also by changing
the underlying user mapping.

But, I think that's part of a broader dependency invalidation problem.
Similar issues could also arise from commands such as DROP USER MAPPING,
ALTER SERVER, and ALTER FOREIGN DATA WRAPPER ... CONNECTION/NO CONNECTION.

If we decide to address the ALTER USER MAPPING case, we should probably
consider those related cases as well. Since that's a broader topic, I think
it's better to discuss that separately.

Regards,

--
Fujii Masao

On Wed, Jul 22, 2026 at 8:34 AM Fujii Masao <masao.fujii@gmail.com> wrote:

On Tue, Jul 21, 2026 at 4:37 PM yuanchao zhang <145zhangyc@gmail.com>
wrote:

Sounds correct to me to do the conninfo validation.

Thanks for the review!

A small comment on the documentation change:

+ for the new owner or for <literal>PUBLIC</literal> must exist, and

the

+ connection string generated for the new owner must satisfy the

subscription's

+ <literal>password_required</literal> setting.

From the implementation, only when a new owner is not superuser, it

needs to satisfy the password_required setting.

Agreed. I've updated the patch as suggested. The updated version is
attached.

-   connection string generated for the new owner must satisfy the
subscription's
-   <literal>password_required</literal> setting.
+   connection string generated for the new owner must be valid.  If the
new
+   owner is not a superuser and the subscription has
+   <literal>password_required=true</literal>, the generated connection
string
+   must include a password.

Regards,

--
Fujii Masao

Thanks for updating the patch, v2 LGTM.

Regards,
Yuanchao Zhang

On Wed, Jul 22, 2026 at 10:45 AM yuanchao zhang <145zhangyc@gmail.com> wrote:

Thanks for updating the patch, v2 LGTM.

Thanks for the review! I've pushed the patch.

Regards,

--
Fujii Masao