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