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'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.
--
Matheus Alcantara
From bf566fdffb1471c99b6f3c9c1833f0399aca2313 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
Date: Thu, 26 Jun 2025 12:05:28 -0300
Subject: [PATCH v1] docs: add note of SCRAM pass-through for bgworkers
---
doc/src/sgml/postgres-fdw.sgml | 8 ++++++++
1 file changed, 8 insertions(+)
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 781a01067f7..8d36056f72d 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -834,6 +834,14 @@ OPTIONS (ADD password_required 'false');
</listitem>
</itemizedlist>
</para>
+
+ <note>
+ <para>
+ For extension developers, background workers that use foreign tables is
+ unable to scram pass-through, so for these scenarios, the password is
+ required on USER MAPPING options.
+ </para>
+ </note>
</listitem>
</varlistentry>
--
2.39.5 (Apple Git-154)
