Re: Questions about the new subscription parameter: password_required - Mailing list pgsql-hackers

On Fri, Sep 22, 2023 at 4:25 AM Benoit Lobréau
<benoit.lobreau@dalibo.com> wrote:
> 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.
> """

I'm not sure of the exact wording, but there was another recent thread
complaining about this being unclear, so it seems like some
clarification is needed.

[ adding Jeff Davis in case he wants to weigh in here ]

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

I haven't checked this, but I think what's happening here is that DROP
SUBSCRIPTION tries to drop the remote slot, which requires making a
connection, which can trigger the error. You might get different
results if you did ALTER SUBSCRIPTION ... SET (slot_name = none)
first.

--
Robert Haas
EDB: http://www.enterprisedb.com