Re: Questions about the new subscription parameter: password_required

On 9/26/23 19:00, Jeff Davis wrote:
> + If the ownership of a subscription with
> <literal>password_required=true</literal>
> + is transferred to a non-superuser, they will gain full control
> over the subscription
> + but will not be able to modify it's connection string.
>
> I think you mean false, right?

No, but I was wrong. At the beginning of the thread, I was surprised
was even possible to change the ownership to a non-superuser because It
shouldn't work and commands like ENABLE didn't complain in the terminal.
Then Robert Haas explained to me that it's ok because the superuser can
do whatever he wants. I came back to it later and somehow convinced
myself it was working. Sorry.

> + If the ownership of a subscription with
> <literal>password_required=true</literal>
> + has been transferred to a non-superuser, it must be reverted to a
> superuser for
> + the DROP operation to succeed.
>
> That's only needed if the superuser transfers a subscription with
> password_required=true to a non-superuser and the connection string
> does not contain a password. In that case, the subscription is already
> in a failing state, not just for DROP. Ideally we'd have some other
> warning in the docs not to do that -- maybe in CREATE and ALTER.

Yes, I forgot the connection string bit.

> Also, if the subscription is in that kind of failing state, there are
> other ways to get out of it as well, like disabling it and setting
> connection=none, then dropping i
The code in for DropSubscription in
src/backend/commands/subscriptioncmds.c tries to connect before testing
if the slot is NONE / NULL. So it doesn't work to DISABLE the
subscription and set the slot to NONE.

Robert Haas proposed something in the following message but I am a
little out of my depth here ...

https://www.postgresql.org/message-id/af9435ae-18df-6a9e-2374-2de23009518c%40dalibo.com

> The whole thing is fairly delicate. As soon as you work slightly
> outside of the intended use, password_required starts causing
> unexpected things to happen.
>
> As I said earlier, I think the best thing to do is to just have a
> section that describes when to use password_required, what specific
> things you should do to satisfy that case, and what caveats you should
> avoid. Something like:
>
> "If you want to have a subscription using a connection string without
> a password managed by a non-superuser, then: [ insert SQL steps here ].
> Warning: if the connection string doesn't contain a password, make sure
> to set password_required=false before transferring ownership, otherwise
> it will start failing."

Ok, I will do it that way. Would you prefer this section to be in the
ALTER SUBSCRIPTION on the CREATE SUBSCIPTION doc ?

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