Re: Questions about the new subscription parameter: password_required

On 9/22/23 14:36, Robert Haas wrote:
> 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.

You're right, it comes from the connection to drop the slot.

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

1522 >~~~must_use_password = !superuser_arg(subowner) &&
form->subpasswordrequired;
...
1685 >~~~wrconn = walrcv_connect(conninfo, true, must_use_password,
1 >~~~>~~~>~~~>~~~>~~~>~~~>~~~subname, &err);
2 >~~~if (wrconn == NULL)
3 >~~~{
4 >~~~>~~~if (!slotname)
5 >~~~>~~~{
6 >~~~>~~~>~~~/* be tidy */
7 >~~~>~~~>~~~list_free(rstates);
8 >~~~>~~~>~~~table_close(rel, NoLock);
9 >~~~>~~~>~~~return;
10 >~~~>~~~}
11 >~~~>~~~else
12 >~~~>~~~{
13 >~~~>~~~>~~~ReportSlotConnectionError(rstates, subid, slotname,
err);
14 >~~~>~~~}
15 >~~~}

Reading the code, I think I understand why the postgres user cannot drop
the slot:

* the owner is sub_owner (not a superuser)
* and form->subpasswordrequired is true

Should there be a test to check if the user executing the query is
superuser? maybe it's handled differently? (I am not very familiar with
the code).

I dont understand (yet?) why I can do ALTER SUBSCRIPTIONs after changing
the ownership to an unpriviledged user (must_use_password should be true
also in that case).

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