Re: SCRAM pass-through authentication for postgres_fdw

On 26.06.25 17:10, Matheus Alcantara wrote:
> On Wed Jun 25, 2025 at 3:07 PM -03, Alexander Pyhalov wrote:
>> Matheus Alcantara писал(а) 2025-06-25 14:36:
>>> Hi, thanks for testing and reporting the issue!
>>>
>>> On 25/06/25 11:37, Alexander Pyhalov wrote:
>>>> Hi.
>>>> I've started to look at this feature and found an issue - MyProcPort
>>>> can be not set if connection is initiated
>>>> by some bgworker. (Internally we use one for statistics collection.)
>>>> In other places (for example, in be_gssapi_get_delegation())
>>>> there are checks that port is not NULL. Likely postgres_fdw and dblink
>>>> should do something similar.
>>>>
>>>
>>> In this case the bgworker is used to collect statistics for the fdw
>>> tables? If that's the case, since we don't have the MyProcPort and the
>>> scram keys, will it use the user and password configured on user
>>> mapping
>>> properties? If that's also the case I think that we may have a problem
>>> because the goal of this feature is to avoid storing the password on
>>> user mapping.
>>>
>>> Do you have steps to reproduce the issue?
>>
>> Hi. I've created a simple extension to reproduce an issue. Just put
>> attached files to contrib and run make check.
>> You'll see bgworker crash.
>>
>
> Thanks! I was able to reproduce the issue.
>
> I've also made some other tests and your patch looks good, so +1.

I have committed Alexander's patch.

> I've also made some tests by using the use_scram_passthrough option on
> foreign server and if a bgworker try to use a foreign table that has
> this option associated with the foreign server the connection will fail
> because we don't have the MyProcPort and the password. To make it work
> the password is required on USER MAPPING options. I think that this
> limitation should be documented, see patch attached.

The fact that SCRAM pass-through doesn't work in a background worker is
arguably implied by the existing paragraph that says that you need to
use SCRAM on the client side. But I think there is opportunity to
clarify that further. The documentation currently doesn't say what
happens if the client doesn't use SCRAM. The code then just ignores the
use_scram_passthrough setting, and your documentation proposal also
suggests that it would fall back to the password provided in the user
mapping. But this could be documented more explicitly, I think.