Re: Questions about the new subscription parameter: password_required

On 9/21/23 20:29, Robert Haas wrote:
> Which one? I see 2 ALTER SUBSCRIPTION ... OWNER commands in
> password_required.log and 1 more in password_required2.log, but
> they're all performed by the superuser, who is entitled to do anything
> they want.

Thank you for taking the time to respond!

I expected the ALTER SUBSCRIPTION ... OWNER command in
password_required.log to fail because the end result of the command is a
non-superuser owning a subscription with password_required=true, but the
connection string has no password keyword, and the authentication scheme
used doesn't require one anyway.

The description of the password_required parameter doesn't clearly state
what will fail or when the configuration is enforced (during CREATE
SUBSCRIPTION and ALTER SUBSCRIPTION .. CONNECTION):

""" https://www.postgresql.org/docs/16/sql-createsubscription.html
Specifies whether connections to the publisher made as a result of this
subscription must use password authentication. This setting is ignored
when the subscription is owned by a superuser. The default is true. Only
superusers can set this value to false.
"""

The description of pg_subscription.subpasswordrequired doesn't either:

""" https://www.postgresql.org/docs/16/catalog-pg-subscription.html
If true, the subscription will be required to specify a password for
authentication
"""

Can we consider adding something like this to clarify?

"""
This parameter is enforced when the CREATE SUBSCRIPTION or ALTER
SUBSCRIPTION .. CONNECTION commands are executed. Therefore, it's
possible to alter the ownership of a subscription with
password_required=true to a non-superuser.
"""

Is the DROP SUBSCRIPTION failure in password_required.log expected for
both superuser and non-superuser?

Is the DROP SUBSCRIPTION success in password_required2.log expected?
(i.e., with password_require=false, the only action a non-superuser can
perform is dropping the subscription. Since they own it, it is
understandable).

--
Benoit Lobréau
Consultant
http://dalibo.com