Re: SCRAM pass-through authentication for postgres_fdw

[Matheus Alcantara]

On 04/12/24 20:05, Jelte Fennema-Nio wrote:
On Wed, 4 Dec 2024 at 23:11, Jacob Champion
<jacob.champ...@enterprisedb.com> wrote:
It makes me a little uneasy to give users a reason to copy identical
salts/verifiers around... But for e.g. a loopback connection, it seems
like there'd be no additional risk. Is that the target use case?

I don't think that necessarily has to be the usecase,
clustering/sharding setups could benefit from this too. PgBouncer
supports the same functionality[1]. I only see advantages over the
alternative, which is copying the plaintext password around. In case
of compromise of the server, only the salt+verifier has to be rotated,
not the actual user password.
The patch is very similar with what was implemented on PgBoucer[1]

Regarding the actual patch: This definitely needs a bunch of
documentation explaining how to use this and when not to use this.
I'm attaching a patch with a initial documentation, so that we can get 
initial thoughts (not sure if I should put the documentation on the 
same patch of code changes).

Thanks!

[1] 
https://github.com/pgbouncer/pgbouncer/commit/ba1abfe#diff-128a3f9ffa6a6f3863e843089ede6d07010215acf49c66b2d1f1d9baba2f49e7R1001

--
Matheus Alcantara
EDB: https://www.enterprisedb.comFrom 7ec18a1553ddab0252d8b16262c5341014b7425c Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths....@pm.me>
Date: Mon, 9 Dec 2024 14:48:07 -0300
Subject: [PATCH v2 2/2] postgres_fdw: Add documentation for SCRAM auth

---
 doc/src/sgml/postgres-fdw.sgml | 19 +++++++++++++++++++
 1 file changed, 19 insertions(+)

diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 188e8f0b4d..da04e14a04 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -770,6 +770,25 @@ OPTIONS (ADD password_required 'false');
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><literal>use_scram_passthrough</literal> 
(<type>boolean</type>)</term>
+      <listitem>
+       <para>
+        This option controls whether <filename>postgres_fdw</filename> will use
+        the SCRAM password authentication to connect into the foreign server.
+        SCRAM secrets can only be used for logging into the foreign server if
+        the client authentication also uses SCRAM.
+      </para>
+      <para>
+        SCRAM authentication into the foreign server can only be possible if
+        both servers have identical SCRAM secrets (encrypted password) for the
+        user being used on <filename>postgres_fdw</filename> to authenticate on
+        the foreign server, same salt and iterations, not merely the same
+        password.
+      </para>
+      </listitem>
+     </varlistentry>
+
     </variablelist>
    </sect3>
  </sect2>
-- 
2.39.3 (Apple Git-146)