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
