v2.1-0002-fixup-postgres_fdw-SCRAM-authentication-pass-th.patch

From fa20ffb5b3aa7760975dd8dcdd9e981f6b96520f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Tue, 14 Jan 2025 10:14:15 +0100
Subject: [PATCH v2.1 2/2] fixup! postgres_fdw: SCRAM authentication
 pass-through

---
 doc/src/sgml/libpq.sgml           | 24 +++++-----
 doc/src/sgml/postgres-fdw.sgml    | 77 ++++++++++++++++++++++++++-----
 src/interfaces/libpq/fe-connect.c |  4 +-
 src/interfaces/libpq/libpq-int.h  |  8 ++--
 4 files changed, 83 insertions(+), 30 deletions(-)

diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 090b9832899..e04acf1c208 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2203,12 +2203,12 @@ <title>Parameter Key Words</title>
       <term><literal>scram_client_key</literal></term>
       <listitem>
        <para>
-        The SCRAM client key is used by FDW extensions to enable pass-through
-        SCRAM authentication. When <option>use_scram_passthrough</option> is
-        set to <literal>true</literal> and this parameter is specified, the
-        backend uses the provided key as the SCRAM client key during
-        authentication with the FDW server. See <xref
-        linkend="postgres-fdw-options-connection-management"/>
+        The base64-encoded SCRAM client key.  This can be used by foreign-data
+        wrappers or similar middleware to enable pass-through SCRAM
+        authentication. See <xref
+        linkend="postgres-fdw-options-connection-management"/> for one such
+        implementation.  It is not meant to be specified directly by users or
+        client applications.
        </para>
       </listitem>
      </varlistentry>
@@ -2217,12 +2217,12 @@ <title>Parameter Key Words</title>
       <term><literal>scram_server_key</literal></term>
       <listitem>
        <para>
-        The SCRAM server key is used by FDW extensions to enable pass-through
-        SCRAM authentication. When <option>use_scram_passthrough</option> is
-        set to <literal>true</literal> and this parameter is specified, the
-        backend uses the provided key as the SCRAM server key during
-        authentication with the FDW server. See <xref
-        linkend="postgres-fdw-options-connection-management"/>
+        The base64-encoded SCRAM server key.  This can be used by foreign-data
+        wrappers or similar middleware to enable pass-through SCRAM
+        authentication. See <xref
+        linkend="postgres-fdw-options-connection-management"/> for one such
+        implementation.  It is not meant to be specified directly by users or
+        client applications.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index da04e14a044..d2998c13d5d 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -774,18 +774,71 @@ <title>Connection Management Options</title>
       <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>
+        This option controls whether <filename>postgres_fdw</filename> will
+        use the SCRAM pass-through authentication to connect to the foreign
+        server.  With SCRAM pass-through authentication,
+        <filename>postgres_fdw</filename> uses SCRAM-hashed secrets instead of
+        plain-text user passwords to connect to the remote server.  This
+        avoids storing plain-text user passwords in PostgreSQL system
+        catalogs.
+       </para>
+
+       <para>
+        To use SCRAM pass-through authentication:
+        <itemizedlist>
+         <listitem>
+          <para>
+           The remote server must request SCRAM authentication.  (If desired,
+           enforce this on the client side (FDW side) with the option
+           <literal>require_auth</literal>.)  If another authentication method
+           is requested by the server, then that one will be used normally.
+          </para>
+         </listitem>
+
+         <listitem>
+          <para>
+           The remote server can be of any PostgreSQL version that supports
+           SCRAM.  Support for <literal>use_scram_passthrough</literal> is
+           only required on the client side (FDW side).
+          </para>
+         </listitem>
+
+         <listitem>
+          <para>
+           The user mapping password is not used.  (It could be set to support
+           other authentication methods, but that would arguably violate the
+           point of this feature, which is to avoid storing plain-text
+           passwords.)
+          </para>
+         </listitem>
+
+         <listitem>
+          <para>
+           The server running <filename>postgres_fdw</filename> and the remote
+           server must have identical SCRAM secrets (encrypted passwords) 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>
+
+          <para>
+           As a corollary, if FDW connections to multiple hosts are to be
+           made, for example for partitioned foreign tables/sharding, then all
+           hosts must have identical SCRAM secrets for the users involved.
+          </para>
+         </listitem>
+
+         <listitem>
+          <para>
+           The current session on the PostgreSQL instance that makes the
+           outgoing FDW connections also must also use SCRAM authentication
+           for its incoming client connection.  (Hence
+           <quote>pass-through</quote>: SCRAM must be used going in and out.)
+           This is a technical requirement of the SCRAM protocol.
+          </para>
+         </listitem>
+        </itemizedlist>
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index ac5af8f5240..4931c77a24f 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -1808,7 +1808,7 @@ pqConnectOptions2(PGconn *conn)
 		/* Consider the zero-terminator */
 		if (len != SCRAM_MAX_KEY_LEN+1)
 		{
-			libpq_append_conn_error(conn, "invalid scram client key len: %d", len);
+			libpq_append_conn_error(conn, "invalid SCRAM client key length: %d", len);
 			return false;
 		}
 		conn->scram_client_key_len = len;
@@ -1827,7 +1827,7 @@ pqConnectOptions2(PGconn *conn)
 		/* Consider the zero-terminator */
 		if (len != SCRAM_MAX_KEY_LEN+1)
 		{
-			libpq_append_conn_error(conn, "invalid scram server key len: %d", len);
+			libpq_append_conn_error(conn, "invalid SCRAM server key length: %d", len);
 			return false;
 		}
 		conn->scram_server_key_len = len;
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index b96630298eb..1f105718678 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -428,8 +428,8 @@ struct pg_conn
 	char	   *target_session_attrs;	/* desired session properties */
 	char	   *require_auth;	/* name of the expected auth method */
 	char	   *load_balance_hosts; /* load balance over hosts */
-	char	   *scram_client_key; /* base64 encoded scram client key */
-	char	   *scram_server_key; /* base64 encoded scram server key */
+	char	   *scram_client_key; /* base64-encoded SCRAM client key */
+	char	   *scram_server_key; /* base64-encoded SCRAM server key */
 
 	bool		cancelRequest;	/* true if this connection is used to send a
 								 * cancel request, instead of being a normal
@@ -521,9 +521,9 @@ struct pg_conn
 								 * tried host */
 	bool		send_appname;	/* okay to send application_name? */
 	size_t		scram_client_key_len;
-	void	   *scram_client_key_binary; /*base64 decoded scram client key */
+	void	   *scram_client_key_binary; /* binary (decoded) SCRAM client key */
 	size_t		scram_server_key_len;
-	void	   *scram_server_key_binary; /*base64 decoded scram server key */
+	void	   *scram_server_key_binary; /* binary (decoded) SCRAM server key */
 
 	/* Miscellaneous stuff */
 	int			be_pid;			/* PID of backend --- needed for cancels */
-- 
2.47.1