SCRAM pass-through authentication for postgres_fdw

From 69f41167ab3cf42f5a262403145ae7b53c77a38c Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
Date: Tue, 19 Nov 2024 15:37:57 -0300
Subject: [PATCH v2 1/2] postgres_fdw: SCRAM authentication pass-through

This commit enable SCRAM authentication for postgres_fdw when connecting
to a fdw server without having to store a plain-text password on user
mapping options.

This is done by saving the SCRAM ClientKey and ServeryKey from the
client authentication and using those instead of the plain-text password
for the server-side SCRAM exchange.
---
 contrib/postgres_fdw/Makefile            |   1 +
 contrib/postgres_fdw/connection.c        |  67 ++++++++++-
 contrib/postgres_fdw/meson.build         |   5 +
 contrib/postgres_fdw/option.c            |   3 +
 contrib/postgres_fdw/t/001_auth_scram.pl | 137 +++++++++++++++++++++++
 src/backend/libpq/auth-scram.c           |  14 ++-
 src/include/libpq/libpq-be.h             |   9 ++
 src/interfaces/libpq/fe-auth-scram.c     |  29 ++++-
 src/interfaces/libpq/fe-auth.c           |   2 +-
 src/interfaces/libpq/fe-connect.c        |  31 +++++
 src/interfaces/libpq/libpq-int.h         |   6 +
 11 files changed, 291 insertions(+), 13 deletions(-)
 create mode 100644 contrib/postgres_fdw/t/001_auth_scram.pl

diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index 88fdce40d6..6c12c8e925 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -8,6 +8,7 @@ OBJS = \
 	option.o \
 	postgres_fdw.o \
 	shippable.o
+TAP_TESTS = 1
 PGFILEDESC = "postgres_fdw - foreign data wrapper for PostgreSQL"
 
 PG_CPPFLAGS = -I$(libpq_srcdir)
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 2326f391d3..e0e1ebe0d4 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -19,6 +19,7 @@
 #include "access/xact.h"
 #include "catalog/pg_user_mapping.h"
 #include "commands/defrem.h"
+#include "common/base64.h"
 #include "funcapi.h"
 #include "libpq/libpq-be.h"
 #include "libpq/libpq-be-fe-helpers.h"
@@ -168,6 +169,7 @@ static void pgfdw_finish_abort_cleanup(List *pending_entries,
 static void pgfdw_security_check(const char **keywords, const char **values,
 								 UserMapping *user, PGconn *conn);
 static bool UserMappingPasswordRequired(UserMapping *user);
+static bool UseScramPassthrough(ForeignServer *server, UserMapping *user);
 static bool disconnect_cached_connections(Oid serverid);
 static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
 												  enum pgfdwVersion api_version);
@@ -476,7 +478,7 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 		 * for application_name, fallback_application_name, client_encoding,
 		 * end marker.
 		 */
-		n = list_length(server->options) + list_length(user->options) + 4;
+		n = list_length(server->options) + list_length(user->options) + 4 + 2;
 		keywords = (const char **) palloc(n * sizeof(char *));
 		values = (const char **) palloc(n * sizeof(char *));
 
@@ -545,10 +547,37 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 		values[n] = GetDatabaseEncodingName();
 		n++;
 
+		if (MyProcPort->has_scram_keys && UseScramPassthrough(server, user))
+		{
+			int			len;
+
+			keywords[n] = "scram_client_key";
+			len = pg_b64_enc_len(sizeof(MyProcPort->scram_ClientKey));
+			/* don't forget the zero-terminator */
+			values[n] = palloc0(len+1);
+			pg_b64_encode((const char *) MyProcPort->scram_ClientKey,
+						  sizeof(MyProcPort->scram_ClientKey),
+						  (char *) values[n], len);
+			n++;
+
+			keywords[n] = "scram_server_key";
+			len = pg_b64_enc_len(sizeof(MyProcPort->scram_ServerKey));
+			/* don't forget the zero-terminator */
+			values[n] = palloc0(len+1);
+			pg_b64_encode((const char *) MyProcPort->scram_ServerKey,
+						  sizeof(MyProcPort->scram_ServerKey),
+						  (char *) values[n], len);
+			n++;
+		}
+
 		keywords[n] = values[n] = NULL;
 
-		/* verify the set of connection parameters */
-		check_conn_params(keywords, values, user);
+		/*
+		 * Verify the set of connection parameters only if scram pass-through
+		 * is not being used because the password is not necessary.
+		 */
+		if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
+			check_conn_params(keywords, values, user);
 
 		/* first time, allocate or get the custom wait event */
 		if (pgfdw_we_connect == 0)
@@ -566,8 +595,12 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 							server->servername),
 					 errdetail_internal("%s", pchomp(PQerrorMessage(conn)))));
 
-		/* Perform post-connection security checks */
-		pgfdw_security_check(keywords, values, user, conn);
+		/*
+		 * Perform post-connection security checks only if scram pass-through
+		 * is not being used because the password is not necessary.
+		 */
+		if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
+			pgfdw_security_check(keywords, values, user, conn);
 
 		/* Prepare new session for use */
 		configure_remote_session(conn);
@@ -620,6 +653,30 @@ UserMappingPasswordRequired(UserMapping *user)
 	return true;
 }
 
+static bool
+UseScramPassthrough(ForeignServer *server, UserMapping *user)
+{
+	ListCell   *cell;
+
+	foreach(cell, server->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(cell);
+
+		if (strcmp(def->defname, "use_scram_passthrough") == 0)
+			return defGetBoolean(def);
+	}
+
+	foreach(cell, user->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(cell);
+
+		if (strcmp(def->defname, "use_scram_passthrough") == 0)
+			return defGetBoolean(def);
+	}
+
+	return false;
+}
+
 /*
  * For non-superusers, insist that the connstr specify a password or that the
  * user provided their own GSSAPI delegated credentials.  This
diff --git a/contrib/postgres_fdw/meson.build b/contrib/postgres_fdw/meson.build
index 3014086ba6..27d07188fc 100644
--- a/contrib/postgres_fdw/meson.build
+++ b/contrib/postgres_fdw/meson.build
@@ -41,4 +41,9 @@ tests += {
     ],
     'regress_args': ['--dlpath', meson.build_root() / 'src/test/regress'],
   },
+  'tap': {
+      'tests': [
+        't/001_auth_scram.pl',
+      ],
+  },
 }
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 232d85354b..15abc64381 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -279,6 +279,9 @@ InitPgFdwOptions(void)
 		{"analyze_sampling", ForeignServerRelationId, false},
 		{"analyze_sampling", ForeignTableRelationId, false},
 
+		{"use_scram_passthrough", ForeignServerRelationId, false},
+		{"use_scram_passthrough", UserMappingRelationId, false},
+
 		/*
 		 * sslcert and sslkey are in fact libpq options, but we repeat them
 		 * here to allow them to appear in both foreign server context (when
diff --git a/contrib/postgres_fdw/t/001_auth_scram.pl b/contrib/postgres_fdw/t/001_auth_scram.pl
new file mode 100644
index 0000000000..8a6d6a05c6
--- /dev/null
+++ b/contrib/postgres_fdw/t/001_auth_scram.pl
@@ -0,0 +1,137 @@
+# Copyright (c) 2021-2024, PostgreSQL Global Development Group
+
+# Test SCRAM authentication when opening a new connection with a foreign
+# server.
+#
+# The test is executed by testing the SCRAM authentifcation on a looplback
+# connection on the same server and with different servers.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Utils;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+my $hostaddr = '127.0.0.1';
+my $user = "user01";
+
+my $db0 = "db0"; # For node1
+my $db1 = "db1"; # For node1
+my $db2 = "db2"; # For node2
+my $fdw_server = "db1_fdw";
+my $fdw_server2 = "db2_fdw";
+
+my $node1 = PostgreSQL::Test::Cluster->new('node1');
+my $node2 = PostgreSQL::Test::Cluster->new('node2');
+
+$node1->init;
+$node2->init;
+
+$node1->start;
+$node2->start;
+
+# Test setup
+
+$node1->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$node2->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$ENV{PGPASSWORD} = "pass";
+
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db0');
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db1');
+$node2->safe_psql('postgres', qq'CREATE DATABASE $db2');
+
+setup_table($node1, $db1, "t");
+setup_table($node2, $db2, "t2");
+
+$node1->safe_psql($db0, 'CREATE EXTENSION IF NOT EXISTS postgres_fdw');
+setup_fdw_server($node1, $db0, $fdw_server, $node1, $db1);
+setup_fdw_server($node1, $db0, $fdw_server2, $node2, $db2);
+
+setup_user_mapping($node1, $db0, $fdw_server);
+setup_user_mapping($node1, $db0, $fdw_server2);
+
+# Make the user have the same SCRAM key on both servers. Forcing to have the
+# same iteration and salt.
+my $rolpassword = $node1->safe_psql('postgres', qq"SELECT rolpassword FROM pg_authid WHERE rolname = '$user';");
+$node2->safe_psql('postgres', qq"ALTER ROLE $user PASSWORD '$rolpassword'");
+
+setup_pghba($node1);
+setup_pghba($node2);
+
+# End of test setup
+
+test_fdw_auth($node1, $db0, "t", $fdw_server, "SCRAM auth on the same database cluster must succeed");
+test_fdw_auth($node1, $db0, "t2", $fdw_server2, "SCRAM auth on a different database cluster must succeed");
+test_auth($node2, $db2, "t2",  "SCRAM auth directly on foreign server should still succeed");
+
+# Helper functions
+
+sub test_auth
+{
+	local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+	my ($node, $db, $tbl, $testname) = @_;
+	my $connstr = $node->connstr($db) . qq' user=$user';
+
+	my $ret = $node->safe_psql($db, qq'SELECT count(1) FROM $tbl',
+		connstr=>$connstr);
+
+	is($ret, '10', $testname);
+}
+
+sub test_fdw_auth
+{
+	local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+	my ($node, $db, $tbl, $fdw, $testname) = @_;
+	my $connstr = $node->connstr($db) . qq' user=$user';
+
+	$node->safe_psql($db, qq'IMPORT FOREIGN SCHEMA public LIMIT TO($tbl) FROM SERVER $fdw INTO public;',
+		connstr=>$connstr);
+
+	test_auth($node, $db, $tbl, $testname);
+}
+
+sub setup_pghba
+{
+	my ($node) = @_;
+
+	unlink($node->data_dir . '/pg_hba.conf');
+	$node->append_conf(
+		'pg_hba.conf', qq{
+	local   all             all                                     scram-sha-256
+	host    all             all             $hostaddr/32            scram-sha-256
+	});
+
+	$node->restart;
+}
+
+sub setup_user_mapping
+{
+	my ($node, $db, $fdw) = @_;
+
+	$node->safe_psql($db, qq'CREATE USER MAPPING FOR $user SERVER $fdw OPTIONS (user \'$user\');');
+	$node->safe_psql($db, qq'GRANT USAGE ON FOREIGN SERVER $fdw to $user;');
+	$node->safe_psql($db, qq'GRANT ALL ON SCHEMA public to $user');
+}
+
+sub setup_fdw_server
+{
+	my ($node, $db, $fdw, $fdw_node, $dbname) = @_;
+	my $host = $fdw_node->host;
+	my $port = $fdw_node->port;
+
+	$node->safe_psql($db, qq'CREATE SERVER $fdw FOREIGN DATA WRAPPER postgres_fdw options (
+		host \'$host\', port \'$port\', dbname \'$dbname\', use_scram_passthrough \'true\') ');
+}
+
+sub setup_table
+{
+	my ($node, $db, $tbl) = @_;
+
+	$node->safe_psql($db, qq'CREATE TABLE $tbl AS SELECT g,g+1 FROM generate_series(1,10) g(g)');
+	$node->safe_psql($db, qq'GRANT USAGE ON SCHEMA public to $user');
+	$node->safe_psql($db, qq'GRANT SELECT ON $tbl to $user');
+}
+
+done_testing();
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index 8c5b6d9c67..7711ed63e0 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -101,6 +101,7 @@
 #include "libpq/crypt.h"
 #include "libpq/sasl.h"
 #include "libpq/scram.h"
+#include "miscadmin.h"
 
 static void scram_get_mechanisms(Port *port, StringInfo buf);
 static void *scram_init(Port *port, const char *selected_mech,
@@ -144,6 +145,7 @@ typedef struct
 
 	int			iterations;
 	char	   *salt;			/* base64-encoded */
+	uint8		ClientKey[SCRAM_MAX_KEY_LEN];
 	uint8		StoredKey[SCRAM_MAX_KEY_LEN];
 	uint8		ServerKey[SCRAM_MAX_KEY_LEN];
 
@@ -462,6 +464,13 @@ scram_exchange(void *opaq, const char *input, int inputlen,
 	if (*output)
 		*outputlen = strlen(*output);
 
+	if (result == PG_SASL_EXCHANGE_SUCCESS && state->state == SCRAM_AUTH_FINISHED)
+	{
+		memcpy(MyProcPort->scram_ClientKey, state->ClientKey, sizeof(MyProcPort->scram_ClientKey));
+		memcpy(MyProcPort->scram_ServerKey, state->ServerKey, sizeof(MyProcPort->scram_ServerKey));
+		MyProcPort->has_scram_keys = true;
+	}
+
 	return result;
 }
 
@@ -1140,7 +1149,6 @@ static bool
 verify_client_proof(scram_state *state)
 {
 	uint8		ClientSignature[SCRAM_MAX_KEY_LEN];
-	uint8		ClientKey[SCRAM_MAX_KEY_LEN];
 	uint8		client_StoredKey[SCRAM_MAX_KEY_LEN];
 	pg_hmac_ctx *ctx = pg_hmac_create(state->hash_type);
 	int			i;
@@ -1173,10 +1181,10 @@ verify_client_proof(scram_state *state)
 
 	/* Extract the ClientKey that the client calculated from the proof */
 	for (i = 0; i < state->key_length; i++)
-		ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
+		state->ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
 
 	/* Hash it one more time, and compare with StoredKey */
-	if (scram_H(ClientKey, state->hash_type, state->key_length,
+	if (scram_H(state->ClientKey, state->hash_type, state->key_length,
 				client_StoredKey, &errstr) < 0)
 		elog(ERROR, "could not hash stored key: %s", errstr);
 
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 9109b2c334..4eb9e80523 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -18,6 +18,8 @@
 #ifndef LIBPQ_BE_H
 #define LIBPQ_BE_H
 
+#include "common/scram-common.h"
+
 #include <sys/time.h>
 #ifdef USE_OPENSSL
 #include <openssl/ssl.h>
@@ -181,6 +183,13 @@ typedef struct Port
 	int			keepalives_count;
 	int			tcp_user_timeout;
 
+	/*
+	 * SCRAM structures.
+	 */
+	uint8		scram_ClientKey[SCRAM_MAX_KEY_LEN];
+	uint8		scram_ServerKey[SCRAM_MAX_KEY_LEN];
+	bool		has_scram_keys; /* true if the above two are valid */
+
 	/*
 	 * GSSAPI structures.
 	 */
diff --git a/src/interfaces/libpq/fe-auth-scram.c b/src/interfaces/libpq/fe-auth-scram.c
index 0bb820e0d9..7beb5a9d31 100644
--- a/src/interfaces/libpq/fe-auth-scram.c
+++ b/src/interfaces/libpq/fe-auth-scram.c
@@ -119,6 +119,8 @@ scram_init(PGconn *conn,
 		return NULL;
 	}
 
+	if (password)
+	{
 	/* Normalize the password with SASLprep, if possible */
 	rc = pg_saslprep(password, &prep_password);
 	if (rc == SASLPREP_OOM)
@@ -138,6 +140,7 @@ scram_init(PGconn *conn,
 		}
 	}
 	state->password = prep_password;
+	}
 
 	return state;
 }
@@ -775,6 +778,12 @@ calculate_client_proof(fe_scram_state *state,
 		return false;
 	}
 
+	if (state->conn->scram_client_key_binary)
+	{
+		memcpy(ClientKey, state->conn->scram_client_key_binary, SCRAM_MAX_KEY_LEN);
+	}
+	else
+	{
 	/*
 	 * Calculate SaltedPassword, and store it in 'state' so that we can reuse
 	 * it later in verify_server_signature.
@@ -783,15 +792,20 @@ calculate_client_proof(fe_scram_state *state,
 							 state->key_length, state->salt, state->saltlen,
 							 state->iterations, state->SaltedPassword,
 							 errstr) < 0 ||
-		scram_ClientKey(state->SaltedPassword, state->hash_type,
-						state->key_length, ClientKey, errstr) < 0 ||
-		scram_H(ClientKey, state->hash_type, state->key_length,
-				StoredKey, errstr) < 0)
+			scram_ClientKey(state->SaltedPassword, state->hash_type,
+						state->key_length, ClientKey, errstr) < 0)
 	{
 		/* errstr is already filled here */
 		pg_hmac_free(ctx);
 		return false;
 	}
+	}
+
+	if (scram_H(ClientKey, state->hash_type, state->key_length, StoredKey, errstr)  < 0)
+	{
+		pg_hmac_free(ctx);
+		return false;
+	}
 
 	if (pg_hmac_init(ctx, StoredKey, state->key_length) < 0 ||
 		pg_hmac_update(ctx,
@@ -841,6 +855,12 @@ verify_server_signature(fe_scram_state *state, bool *match,
 		return false;
 	}
 
+	if (state->conn->scram_server_key_binary)
+	{
+		memcpy(ServerKey, state->conn->scram_server_key_binary, SCRAM_MAX_KEY_LEN);
+	}
+	else
+	{
 	if (scram_ServerKey(state->SaltedPassword, state->hash_type,
 						state->key_length, ServerKey, errstr) < 0)
 	{
@@ -848,6 +868,7 @@ verify_server_signature(fe_scram_state *state, bool *match,
 		pg_hmac_free(ctx);
 		return false;
 	}
+	}
 
 	/* calculate ServerSignature */
 	if (pg_hmac_init(ctx, ServerKey, state->key_length) < 0 ||
diff --git a/src/interfaces/libpq/fe-auth.c b/src/interfaces/libpq/fe-auth.c
index 20d3427e94..ef1c965cd5 100644
--- a/src/interfaces/libpq/fe-auth.c
+++ b/src/interfaces/libpq/fe-auth.c
@@ -559,7 +559,7 @@ pg_SASL_init(PGconn *conn, int payloadlen)
 	 * First, select the password to use for the exchange, complaining if
 	 * there isn't one and the selected SASL mechanism needs it.
 	 */
-	if (conn->password_needed)
+	if (conn->password_needed && !conn->scram_client_key_binary)
 	{
 		password = conn->connhost[conn->whichhost].password;
 		if (password == NULL)
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index aaf87e8e88..464cefd901 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -22,6 +22,7 @@
 #include <time.h>
 #include <unistd.h>
 
+#include "common/base64.h"
 #include "common/ip.h"
 #include "common/link-canary.h"
 #include "common/scram-common.h"
@@ -365,6 +366,12 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
 		"Load-Balance-Hosts", "", 8,	/* sizeof("disable") = 8 */
 	offsetof(struct pg_conn, load_balance_hosts)},
 
+	{"scram_client_key", NULL, NULL, NULL, "SCRAM-Client-Key", "D", SCRAM_MAX_KEY_LEN * 2,
+	offsetof(struct pg_conn, scram_client_key)},
+
+	{"scram_server_key", NULL, NULL, NULL, "SCRAM-Server-Key", "D", SCRAM_MAX_KEY_LEN * 2,
+	offsetof(struct pg_conn, scram_server_key)},
+
 	/* Terminating entry --- MUST BE LAST */
 	{NULL, NULL, NULL, NULL,
 	NULL, NULL, 0}
@@ -1792,6 +1799,28 @@ pqConnectOptions2(PGconn *conn)
 	else
 		conn->target_server_type = SERVER_TYPE_ANY;
 
+	if (conn->scram_client_key)
+	{
+		int			len;
+
+		len = pg_b64_dec_len(strlen(conn->scram_client_key));
+		conn->scram_client_key_len = len;
+		conn->scram_client_key_binary = malloc(len);
+		pg_b64_decode(conn->scram_client_key, strlen(conn->scram_client_key),
+					  conn->scram_client_key_binary, len);
+	}
+
+	if (conn->scram_server_key)
+	{
+		int			len;
+
+		len = pg_b64_dec_len(strlen(conn->scram_server_key));
+		conn->scram_server_key_len = len;
+		conn->scram_server_key_binary = malloc(len);
+		pg_b64_decode(conn->scram_server_key, strlen(conn->scram_server_key),
+					  conn->scram_server_key_binary, len);
+	}
+
 	/*
 	 * validate load_balance_hosts option, and set load_balance_type
 	 */
@@ -4703,6 +4732,8 @@ freePGconn(PGconn *conn)
 	free(conn->rowBuf);
 	free(conn->target_session_attrs);
 	free(conn->load_balance_hosts);
+	free(conn->scram_client_key);
+	free(conn->scram_server_key);
 	termPQExpBuffer(&conn->errorMessage);
 	termPQExpBuffer(&conn->workBuffer);
 
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 08cc391cbd..17b81c81f4 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -427,6 +427,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;
+	char	   *scram_server_key;
 
 	bool		cancelRequest;	/* true if this connection is used to send a
 								 * cancel request, instead of being a normal
@@ -517,6 +519,10 @@ struct pg_conn
 	AddrInfo   *addr;			/* the array of addresses for the currently
 								 * tried host */
 	bool		send_appname;	/* okay to send application_name? */
+	size_t		scram_client_key_len;
+	char	   *scram_client_key_binary;
+	size_t		scram_server_key_len;
+	char	   *scram_server_key_binary;
 
 	/* Miscellaneous stuff */
 	int			be_pid;			/* PID of backend --- needed for cancels */
-- 
2.39.3 (Apple Git-146)

From 7ec18a1553ddab0252d8b16262c5341014b7425c Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@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)

From e4bffb68a4a29993462777c44a4b5f41c406039a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 8 Jan 2025 11:36:56 +0100
Subject: [PATCH 1/3] Minor cosmetic improvements

---
 contrib/postgres_fdw/Makefile    | 2 +-
 contrib/postgres_fdw/meson.build | 6 +++---
 src/interfaces/libpq/libpq-int.h | 4 ++--
 3 files changed, 6 insertions(+), 6 deletions(-)

diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index 6c12c8e9251..adfbd2ef758 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -8,7 +8,6 @@ OBJS = \
 	option.o \
 	postgres_fdw.o \
 	shippable.o
-TAP_TESTS = 1
 PGFILEDESC = "postgres_fdw - foreign data wrapper for PostgreSQL"
 
 PG_CPPFLAGS = -I$(libpq_srcdir)
@@ -18,6 +17,7 @@ EXTENSION = postgres_fdw
 DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql postgres_fdw--1.1--1.2.sql
 
 REGRESS = postgres_fdw query_cancel
+TAP_TESTS = 1
 
 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/contrib/postgres_fdw/meson.build b/contrib/postgres_fdw/meson.build
index 61269934c18..8b29be24dee 100644
--- a/contrib/postgres_fdw/meson.build
+++ b/contrib/postgres_fdw/meson.build
@@ -42,8 +42,8 @@ tests += {
     'regress_args': ['--dlpath', meson.build_root() / 'src/test/regress'],
   },
   'tap': {
-      'tests': [
-        't/001_auth_scram.pl',
-      ],
+    'tests': [
+      't/001_auth_scram.pl',
+    ],
   },
 }
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 79097b63ec0..d29ef1cfb77 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -521,9 +521,9 @@ struct pg_conn
 								 * tried host */
 	bool		send_appname;	/* okay to send application_name? */
 	size_t		scram_client_key_len;
-	char	   *scram_client_key_binary;
+	void	   *scram_client_key_binary;
 	size_t		scram_server_key_len;
-	char	   *scram_server_key_binary;
+	void	   *scram_server_key_binary;
 
 	/* Miscellaneous stuff */
 	int			be_pid;			/* PID of backend --- needed for cancels */
-- 
2.47.1

From 1c33c6b1e011f47c1d7decd5da6e952fad21ed6d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 8 Jan 2025 11:37:17 +0100
Subject: [PATCH 2/3] Must check return value of malloc()

---
 src/interfaces/libpq/fe-connect.c | 4 ++++
 1 file changed, 4 insertions(+)

diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 49f36f0e3d9..ebe272617e0 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -1807,6 +1807,8 @@ pqConnectOptions2(PGconn *conn)
 		len = pg_b64_dec_len(strlen(conn->scram_client_key));
 		conn->scram_client_key_len = len;
 		conn->scram_client_key_binary = malloc(len);
+		if (!conn->scram_client_key_binary)
+			goto oom_error;
 		pg_b64_decode(conn->scram_client_key, strlen(conn->scram_client_key),
 					  conn->scram_client_key_binary, len);
 	}
@@ -1818,6 +1820,8 @@ pqConnectOptions2(PGconn *conn)
 		len = pg_b64_dec_len(strlen(conn->scram_server_key));
 		conn->scram_server_key_len = len;
 		conn->scram_server_key_binary = malloc(len);
+		if (!conn->scram_server_key_binary)
+			goto oom_error;
 		pg_b64_decode(conn->scram_server_key, strlen(conn->scram_server_key),
 					  conn->scram_server_key_binary, len);
 	}
-- 
2.47.1

From fd96accf772c78cb27a3821224f9986103f8ded5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 8 Jan 2025 11:37:35 +0100
Subject: [PATCH 3/3] Placeholders for more documentation

---
 doc/src/sgml/libpq.sgml | 18 ++++++++++++++++++
 1 file changed, 18 insertions(+)

diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 105b22b3171..68d61015a99 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2199,6 +2199,24 @@ <title>Parameter Key Words</title>
       </listitem>
      </varlistentry>
 
+     <varlistentry id="libpq-connect-scram-client-key" xreflabel="scram_client_key">
+      <term><literal>scram_client_key</literal></term>
+      <listitem>
+       <para>
+        TODO
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="libpq-connect-scram-server-key" xreflabel="scram_server_key">
+      <term><literal>scram_server_key</literal></term>
+      <listitem>
+       <para>
+        TODO
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="libpq-connect-service" xreflabel="service">
       <term><literal>service</literal></term>
       <listitem>
-- 
2.47.1

From fc0ec661535838cd5f26e8eb679de2fcbf9b8fad Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
Date: Tue, 19 Nov 2024 15:37:57 -0300
Subject: [PATCH v2] postgres_fdw: SCRAM authentication pass-through

This commit enable SCRAM authentication for postgres_fdw when connecting
to a fdw server without having to store a plain-text password on user
mapping options.

This is done by saving the SCRAM ClientKey and ServeryKey from the
client authentication and using those instead of the plain-text password
for the server-side SCRAM exchange.
---
 contrib/postgres_fdw/Makefile                 |   1 +
 contrib/postgres_fdw/connection.c             |  69 ++++++++-
 .../postgres_fdw/expected/postgres_fdw.out    |   4 +-
 contrib/postgres_fdw/meson.build              |   5 +
 contrib/postgres_fdw/option.c                 |   3 +
 contrib/postgres_fdw/t/001_auth_scram.pl      | 137 ++++++++++++++++++
 doc/src/sgml/libpq.sgml                       |  28 ++++
 doc/src/sgml/postgres-fdw.sgml                |  19 +++
 src/backend/libpq/auth-scram.c                |  14 +-
 src/include/libpq/libpq-be.h                  |   9 ++
 src/interfaces/libpq/fe-auth-scram.c          |  29 +++-
 src/interfaces/libpq/fe-auth.c                |   2 +-
 src/interfaces/libpq/fe-connect.c             |  47 ++++++
 src/interfaces/libpq/libpq-int.h              |   6 +
 14 files changed, 357 insertions(+), 16 deletions(-)
 create mode 100644 contrib/postgres_fdw/t/001_auth_scram.pl

diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index 88fdce40d6..adfbd2ef75 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -17,6 +17,7 @@ EXTENSION = postgres_fdw
 DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql postgres_fdw--1.1--1.2.sql
 
 REGRESS = postgres_fdw query_cancel
+TAP_TESTS = 1
 
 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 202e7e583b..06ec6201bb 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -19,6 +19,7 @@
 #include "access/xact.h"
 #include "catalog/pg_user_mapping.h"
 #include "commands/defrem.h"
+#include "common/base64.h"
 #include "funcapi.h"
 #include "libpq/libpq-be.h"
 #include "libpq/libpq-be-fe-helpers.h"
@@ -177,6 +178,7 @@ static void pgfdw_finish_abort_cleanup(List *pending_entries,
 static void pgfdw_security_check(const char **keywords, const char **values,
 								 UserMapping *user, PGconn *conn);
 static bool UserMappingPasswordRequired(UserMapping *user);
+static bool UseScramPassthrough(ForeignServer *server, UserMapping *user);
 static bool disconnect_cached_connections(Oid serverid);
 static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
 												  enum pgfdwVersion api_version);
@@ -485,7 +487,7 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 		 * for application_name, fallback_application_name, client_encoding,
 		 * end marker.
 		 */
-		n = list_length(server->options) + list_length(user->options) + 4;
+		n = list_length(server->options) + list_length(user->options) + 4 + 2;
 		keywords = (const char **) palloc(n * sizeof(char *));
 		values = (const char **) palloc(n * sizeof(char *));
 
@@ -554,10 +556,37 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 		values[n] = GetDatabaseEncodingName();
 		n++;
 
+		if (MyProcPort->has_scram_keys && UseScramPassthrough(server, user))
+		{
+			int			len;
+
+			keywords[n] = "scram_client_key";
+			len = pg_b64_enc_len(sizeof(MyProcPort->scram_ClientKey));
+			/* don't forget the zero-terminator */
+			values[n] = palloc0(len+1);
+			pg_b64_encode((const char *) MyProcPort->scram_ClientKey,
+						  sizeof(MyProcPort->scram_ClientKey),
+						  (char *) values[n], len);
+			n++;
+
+			keywords[n] = "scram_server_key";
+			len = pg_b64_enc_len(sizeof(MyProcPort->scram_ServerKey));
+			/* don't forget the zero-terminator */
+			values[n] = palloc0(len+1);
+			pg_b64_encode((const char *) MyProcPort->scram_ServerKey,
+						  sizeof(MyProcPort->scram_ServerKey),
+						  (char *) values[n], len);
+			n++;
+		}
+
 		keywords[n] = values[n] = NULL;
 
-		/* verify the set of connection parameters */
-		check_conn_params(keywords, values, user);
+		/*
+		 * Verify the set of connection parameters only if scram pass-through
+		 * is not being used because the password is not necessary.
+		 */
+		if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
+			check_conn_params(keywords, values, user);
 
 		/* first time, allocate or get the custom wait event */
 		if (pgfdw_we_connect == 0)
@@ -575,8 +604,12 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 							server->servername),
 					 errdetail_internal("%s", pchomp(PQerrorMessage(conn)))));
 
-		/* Perform post-connection security checks */
-		pgfdw_security_check(keywords, values, user, conn);
+		/*
+		 * Perform post-connection security checks only if scram pass-through
+		 * is not being used because the password is not necessary.
+		 */
+		if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
+			pgfdw_security_check(keywords, values, user, conn);
 
 		/* Prepare new session for use */
 		configure_remote_session(conn);
@@ -629,6 +662,30 @@ UserMappingPasswordRequired(UserMapping *user)
 	return true;
 }
 
+static bool
+UseScramPassthrough(ForeignServer *server, UserMapping *user)
+{
+	ListCell   *cell;
+
+	foreach(cell, server->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(cell);
+
+		if (strcmp(def->defname, "use_scram_passthrough") == 0)
+			return defGetBoolean(def);
+	}
+
+	foreach(cell, user->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(cell);
+
+		if (strcmp(def->defname, "use_scram_passthrough") == 0)
+			return defGetBoolean(def);
+	}
+
+	return false;
+}
+
 /*
  * For non-superusers, insist that the connstr specify a password or that the
  * user provided their own GSSAPI delegated credentials.  This
@@ -666,7 +723,7 @@ check_conn_params(const char **keywords, const char **values, UserMapping *user)
 	ereport(ERROR,
 			(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
 			 errmsg("password or GSSAPI delegated credentials required"),
-			 errdetail("Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.")));
+			 errdetail("Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.")));
 }
 
 /*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bf322198a2..64aa12ecc4 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10301,7 +10301,7 @@ CREATE FOREIGN TABLE pg_temp.ft1_nopw (
 ) SERVER loopback_nopw OPTIONS (schema_name 'public', table_name 'ft1');
 SELECT 1 FROM ft1_nopw LIMIT 1;
 ERROR:  password or GSSAPI delegated credentials required
-DETAIL:  Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.
+DETAIL:  Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.
 -- If we add a password to the connstr it'll fail, because we don't allow passwords
 -- in connstrs only in user mappings.
 ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw');
@@ -10351,7 +10351,7 @@ DROP USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
 -- lacks password_required=false
 SELECT 1 FROM ft1_nopw LIMIT 1;
 ERROR:  password or GSSAPI delegated credentials required
-DETAIL:  Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.
+DETAIL:  Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.
 RESET ROLE;
 -- The user mapping for public is passwordless and lacks the password_required=false
 -- mapping option, but will work because the current user is a superuser.
diff --git a/contrib/postgres_fdw/meson.build b/contrib/postgres_fdw/meson.build
index 3f19981cff..8b29be24de 100644
--- a/contrib/postgres_fdw/meson.build
+++ b/contrib/postgres_fdw/meson.build
@@ -41,4 +41,9 @@ tests += {
     ],
     'regress_args': ['--dlpath', meson.build_root() / 'src/test/regress'],
   },
+  'tap': {
+    'tests': [
+      't/001_auth_scram.pl',
+    ],
+  },
 }
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 12aed4054f..d0766f007d 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -279,6 +279,9 @@ InitPgFdwOptions(void)
 		{"analyze_sampling", ForeignServerRelationId, false},
 		{"analyze_sampling", ForeignTableRelationId, false},
 
+		{"use_scram_passthrough", ForeignServerRelationId, false},
+		{"use_scram_passthrough", UserMappingRelationId, false},
+
 		/*
 		 * sslcert and sslkey are in fact libpq options, but we repeat them
 		 * here to allow them to appear in both foreign server context (when
diff --git a/contrib/postgres_fdw/t/001_auth_scram.pl b/contrib/postgres_fdw/t/001_auth_scram.pl
new file mode 100644
index 0000000000..d5be6fc35c
--- /dev/null
+++ b/contrib/postgres_fdw/t/001_auth_scram.pl
@@ -0,0 +1,137 @@
+# Copyright (c) 2024-2025, PostgreSQL Global Development Group
+
+# Test SCRAM authentication when opening a new connection with a foreign
+# server.
+#
+# The test is executed by testing the SCRAM authentifcation on a looplback
+# connection on the same server and with different servers.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Utils;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+my $hostaddr = '127.0.0.1';
+my $user = "user01";
+
+my $db0 = "db0"; # For node1
+my $db1 = "db1"; # For node1
+my $db2 = "db2"; # For node2
+my $fdw_server = "db1_fdw";
+my $fdw_server2 = "db2_fdw";
+
+my $node1 = PostgreSQL::Test::Cluster->new('node1');
+my $node2 = PostgreSQL::Test::Cluster->new('node2');
+
+$node1->init;
+$node2->init;
+
+$node1->start;
+$node2->start;
+
+# Test setup
+
+$node1->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$node2->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$ENV{PGPASSWORD} = "pass";
+
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db0');
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db1');
+$node2->safe_psql('postgres', qq'CREATE DATABASE $db2');
+
+setup_table($node1, $db1, "t");
+setup_table($node2, $db2, "t2");
+
+$node1->safe_psql($db0, 'CREATE EXTENSION IF NOT EXISTS postgres_fdw');
+setup_fdw_server($node1, $db0, $fdw_server, $node1, $db1);
+setup_fdw_server($node1, $db0, $fdw_server2, $node2, $db2);
+
+setup_user_mapping($node1, $db0, $fdw_server);
+setup_user_mapping($node1, $db0, $fdw_server2);
+
+# Make the user have the same SCRAM key on both servers. Forcing to have the
+# same iteration and salt.
+my $rolpassword = $node1->safe_psql('postgres', qq"SELECT rolpassword FROM pg_authid WHERE rolname = '$user';");
+$node2->safe_psql('postgres', qq"ALTER ROLE $user PASSWORD '$rolpassword'");
+
+setup_pghba($node1);
+setup_pghba($node2);
+
+# End of test setup
+
+test_fdw_auth($node1, $db0, "t", $fdw_server, "SCRAM auth on the same database cluster must succeed");
+test_fdw_auth($node1, $db0, "t2", $fdw_server2, "SCRAM auth on a different database cluster must succeed");
+test_auth($node2, $db2, "t2",  "SCRAM auth directly on foreign server should still succeed");
+
+# Helper functions
+
+sub test_auth
+{
+	local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+	my ($node, $db, $tbl, $testname) = @_;
+	my $connstr = $node->connstr($db) . qq' user=$user';
+
+	my $ret = $node->safe_psql($db, qq'SELECT count(1) FROM $tbl',
+		connstr=>$connstr);
+
+	is($ret, '10', $testname);
+}
+
+sub test_fdw_auth
+{
+	local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+	my ($node, $db, $tbl, $fdw, $testname) = @_;
+	my $connstr = $node->connstr($db) . qq' user=$user';
+
+	$node->safe_psql($db, qq'IMPORT FOREIGN SCHEMA public LIMIT TO($tbl) FROM SERVER $fdw INTO public;',
+		connstr=>$connstr);
+
+	test_auth($node, $db, $tbl, $testname);
+}
+
+sub setup_pghba
+{
+	my ($node) = @_;
+
+	unlink($node->data_dir . '/pg_hba.conf');
+	$node->append_conf(
+		'pg_hba.conf', qq{
+	local   all             all                                     scram-sha-256
+	host    all             all             $hostaddr/32            scram-sha-256
+	});
+
+	$node->restart;
+}
+
+sub setup_user_mapping
+{
+	my ($node, $db, $fdw) = @_;
+
+	$node->safe_psql($db, qq'CREATE USER MAPPING FOR $user SERVER $fdw OPTIONS (user \'$user\');');
+	$node->safe_psql($db, qq'GRANT USAGE ON FOREIGN SERVER $fdw to $user;');
+	$node->safe_psql($db, qq'GRANT ALL ON SCHEMA public to $user');
+}
+
+sub setup_fdw_server
+{
+	my ($node, $db, $fdw, $fdw_node, $dbname) = @_;
+	my $host = $fdw_node->host;
+	my $port = $fdw_node->port;
+
+	$node->safe_psql($db, qq'CREATE SERVER $fdw FOREIGN DATA WRAPPER postgres_fdw options (
+		host \'$host\', port \'$port\', dbname \'$dbname\', use_scram_passthrough \'true\') ');
+}
+
+sub setup_table
+{
+	my ($node, $db, $tbl) = @_;
+
+	$node->safe_psql($db, qq'CREATE TABLE $tbl AS SELECT g,g+1 FROM generate_series(1,10) g(g)');
+	$node->safe_psql($db, qq'GRANT USAGE ON SCHEMA public to $user');
+	$node->safe_psql($db, qq'GRANT SELECT ON $tbl to $user');
+}
+
+done_testing();
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 105b22b317..090b983289 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2199,6 +2199,34 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
       </listitem>
      </varlistentry>
 
+     <varlistentry id="libpq-connect-scram-client-key" xreflabel="scram_client_key">
+      <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"/>
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="libpq-connect-scram-server-key" xreflabel="scram_server_key">
+      <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"/>
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="libpq-connect-service" xreflabel="service">
       <term><literal>service</literal></term>
       <listitem>
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>
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index 1514133acd..26dd241efa 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -101,6 +101,7 @@
 #include "libpq/crypt.h"
 #include "libpq/sasl.h"
 #include "libpq/scram.h"
+#include "miscadmin.h"
 
 static void scram_get_mechanisms(Port *port, StringInfo buf);
 static void *scram_init(Port *port, const char *selected_mech,
@@ -144,6 +145,7 @@ typedef struct
 
 	int			iterations;
 	char	   *salt;			/* base64-encoded */
+	uint8		ClientKey[SCRAM_MAX_KEY_LEN];
 	uint8		StoredKey[SCRAM_MAX_KEY_LEN];
 	uint8		ServerKey[SCRAM_MAX_KEY_LEN];
 
@@ -462,6 +464,13 @@ scram_exchange(void *opaq, const char *input, int inputlen,
 	if (*output)
 		*outputlen = strlen(*output);
 
+	if (result == PG_SASL_EXCHANGE_SUCCESS && state->state == SCRAM_AUTH_FINISHED)
+	{
+		memcpy(MyProcPort->scram_ClientKey, state->ClientKey, sizeof(MyProcPort->scram_ClientKey));
+		memcpy(MyProcPort->scram_ServerKey, state->ServerKey, sizeof(MyProcPort->scram_ServerKey));
+		MyProcPort->has_scram_keys = true;
+	}
+
 	return result;
 }
 
@@ -1140,7 +1149,6 @@ static bool
 verify_client_proof(scram_state *state)
 {
 	uint8		ClientSignature[SCRAM_MAX_KEY_LEN];
-	uint8		ClientKey[SCRAM_MAX_KEY_LEN];
 	uint8		client_StoredKey[SCRAM_MAX_KEY_LEN];
 	pg_hmac_ctx *ctx = pg_hmac_create(state->hash_type);
 	int			i;
@@ -1173,10 +1181,10 @@ verify_client_proof(scram_state *state)
 
 	/* Extract the ClientKey that the client calculated from the proof */
 	for (i = 0; i < state->key_length; i++)
-		ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
+		state->ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
 
 	/* Hash it one more time, and compare with StoredKey */
-	if (scram_H(ClientKey, state->hash_type, state->key_length,
+	if (scram_H(state->ClientKey, state->hash_type, state->key_length,
 				client_StoredKey, &errstr) < 0)
 		elog(ERROR, "could not hash stored key: %s", errstr);
 
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 16da6f89ef..2f6c29200b 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -18,6 +18,8 @@
 #ifndef LIBPQ_BE_H
 #define LIBPQ_BE_H
 
+#include "common/scram-common.h"
+
 #include <sys/time.h>
 #ifdef USE_OPENSSL
 #include <openssl/ssl.h>
@@ -181,6 +183,13 @@ typedef struct Port
 	int			keepalives_count;
 	int			tcp_user_timeout;
 
+	/*
+	 * SCRAM structures.
+	 */
+	uint8		scram_ClientKey[SCRAM_MAX_KEY_LEN];
+	uint8		scram_ServerKey[SCRAM_MAX_KEY_LEN];
+	bool		has_scram_keys; /* true if the above two are valid */
+
 	/*
 	 * GSSAPI structures.
 	 */
diff --git a/src/interfaces/libpq/fe-auth-scram.c b/src/interfaces/libpq/fe-auth-scram.c
index 59bf87d221..dda4308912 100644
--- a/src/interfaces/libpq/fe-auth-scram.c
+++ b/src/interfaces/libpq/fe-auth-scram.c
@@ -119,6 +119,8 @@ scram_init(PGconn *conn,
 		return NULL;
 	}
 
+	if (password)
+	{
 	/* Normalize the password with SASLprep, if possible */
 	rc = pg_saslprep(password, &prep_password);
 	if (rc == SASLPREP_OOM)
@@ -138,6 +140,7 @@ scram_init(PGconn *conn,
 		}
 	}
 	state->password = prep_password;
+	}
 
 	return state;
 }
@@ -775,6 +778,12 @@ calculate_client_proof(fe_scram_state *state,
 		return false;
 	}
 
+	if (state->conn->scram_client_key_binary)
+	{
+		memcpy(ClientKey, state->conn->scram_client_key_binary, SCRAM_MAX_KEY_LEN);
+	}
+	else
+	{
 	/*
 	 * Calculate SaltedPassword, and store it in 'state' so that we can reuse
 	 * it later in verify_server_signature.
@@ -783,15 +792,20 @@ calculate_client_proof(fe_scram_state *state,
 							 state->key_length, state->salt, state->saltlen,
 							 state->iterations, state->SaltedPassword,
 							 errstr) < 0 ||
-		scram_ClientKey(state->SaltedPassword, state->hash_type,
-						state->key_length, ClientKey, errstr) < 0 ||
-		scram_H(ClientKey, state->hash_type, state->key_length,
-				StoredKey, errstr) < 0)
+			scram_ClientKey(state->SaltedPassword, state->hash_type,
+						state->key_length, ClientKey, errstr) < 0)
 	{
 		/* errstr is already filled here */
 		pg_hmac_free(ctx);
 		return false;
 	}
+	}
+
+	if (scram_H(ClientKey, state->hash_type, state->key_length, StoredKey, errstr)  < 0)
+	{
+		pg_hmac_free(ctx);
+		return false;
+	}
 
 	if (pg_hmac_init(ctx, StoredKey, state->key_length) < 0 ||
 		pg_hmac_update(ctx,
@@ -841,6 +855,12 @@ verify_server_signature(fe_scram_state *state, bool *match,
 		return false;
 	}
 
+	if (state->conn->scram_server_key_binary)
+	{
+		memcpy(ServerKey, state->conn->scram_server_key_binary, SCRAM_MAX_KEY_LEN);
+	}
+	else
+	{
 	if (scram_ServerKey(state->SaltedPassword, state->hash_type,
 						state->key_length, ServerKey, errstr) < 0)
 	{
@@ -848,6 +868,7 @@ verify_server_signature(fe_scram_state *state, bool *match,
 		pg_hmac_free(ctx);
 		return false;
 	}
+	}
 
 	/* calculate ServerSignature */
 	if (pg_hmac_init(ctx, ServerKey, state->key_length) < 0 ||
diff --git a/src/interfaces/libpq/fe-auth.c b/src/interfaces/libpq/fe-auth.c
index 14a9a862f5..7e478489b7 100644
--- a/src/interfaces/libpq/fe-auth.c
+++ b/src/interfaces/libpq/fe-auth.c
@@ -559,7 +559,7 @@ pg_SASL_init(PGconn *conn, int payloadlen)
 	 * First, select the password to use for the exchange, complaining if
 	 * there isn't one and the selected SASL mechanism needs it.
 	 */
-	if (conn->password_needed)
+	if (conn->password_needed && !conn->scram_client_key_binary)
 	{
 		password = conn->connhost[conn->whichhost].password;
 		if (password == NULL)
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 8f211821eb..ac5af8f524 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -22,6 +22,7 @@
 #include <time.h>
 #include <unistd.h>
 
+#include "common/base64.h"
 #include "common/ip.h"
 #include "common/link-canary.h"
 #include "common/scram-common.h"
@@ -366,6 +367,12 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
 		"Load-Balance-Hosts", "", 8,	/* sizeof("disable") = 8 */
 	offsetof(struct pg_conn, load_balance_hosts)},
 
+	{"scram_client_key", NULL, NULL, NULL, "SCRAM-Client-Key", "D", SCRAM_MAX_KEY_LEN * 2,
+	offsetof(struct pg_conn, scram_client_key)},
+
+	{"scram_server_key", NULL, NULL, NULL, "SCRAM-Server-Key", "D", SCRAM_MAX_KEY_LEN * 2,
+	offsetof(struct pg_conn, scram_server_key)},
+
 	/* Terminating entry --- MUST BE LAST */
 	{NULL, NULL, NULL, NULL,
 	NULL, NULL, 0}
@@ -1793,6 +1800,44 @@ pqConnectOptions2(PGconn *conn)
 	else
 		conn->target_server_type = SERVER_TYPE_ANY;
 
+	if (conn->scram_client_key)
+	{
+		int			len;
+
+		len = pg_b64_dec_len(strlen(conn->scram_client_key));
+		/* Consider the zero-terminator */
+		if (len != SCRAM_MAX_KEY_LEN+1)
+		{
+			libpq_append_conn_error(conn, "invalid scram client key len: %d", len);
+			return false;
+		}
+		conn->scram_client_key_len = len;
+		conn->scram_client_key_binary = malloc(len);
+		if (!conn->scram_client_key_binary)
+			goto oom_error;
+		pg_b64_decode(conn->scram_client_key, strlen(conn->scram_client_key),
+					  conn->scram_client_key_binary, len);
+	}
+
+	if (conn->scram_server_key)
+	{
+		int			len;
+
+		len = pg_b64_dec_len(strlen(conn->scram_server_key));
+		/* Consider the zero-terminator */
+		if (len != SCRAM_MAX_KEY_LEN+1)
+		{
+			libpq_append_conn_error(conn, "invalid scram server key len: %d", len);
+			return false;
+		}
+		conn->scram_server_key_len = len;
+		conn->scram_server_key_binary = malloc(len);
+		if (!conn->scram_server_key_binary)
+			goto oom_error;
+		pg_b64_decode(conn->scram_server_key, strlen(conn->scram_server_key),
+					  conn->scram_server_key_binary, len);
+	}
+
 	/*
 	 * validate load_balance_hosts option, and set load_balance_type
 	 */
@@ -4704,6 +4749,8 @@ freePGconn(PGconn *conn)
 	free(conn->rowBuf);
 	free(conn->target_session_attrs);
 	free(conn->load_balance_hosts);
+	free(conn->scram_client_key);
+	free(conn->scram_server_key);
 	termPQExpBuffer(&conn->errorMessage);
 	termPQExpBuffer(&conn->workBuffer);
 
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 4a5a7c8b5e..b96630298e 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -428,6 +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 */
 
 	bool		cancelRequest;	/* true if this connection is used to send a
 								 * cancel request, instead of being a normal
@@ -518,6 +520,10 @@ struct pg_conn
 	AddrInfo   *addr;			/* the array of addresses for the currently
 								 * 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 */
+	size_t		scram_server_key_len;
+	void	   *scram_server_key_binary; /*base64 decoded scram server key */
 
 	/* Miscellaneous stuff */
 	int			be_pid;			/* PID of backend --- needed for cancels */
-- 
2.39.5 (Apple Git-154)

From 8c1ed745da53a299a92015c54e3f49739ffec005 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
Date: Tue, 19 Nov 2024 15:37:57 -0300
Subject: [PATCH v2.1 1/2] postgres_fdw: SCRAM authentication pass-through

This commit enable SCRAM authentication for postgres_fdw when connecting
to a fdw server without having to store a plain-text password on user
mapping options.

This is done by saving the SCRAM ClientKey and ServeryKey from the
client authentication and using those instead of the plain-text password
for the server-side SCRAM exchange.
---
 contrib/postgres_fdw/Makefile                 |   1 +
 contrib/postgres_fdw/connection.c             |  69 ++++++++-
 .../postgres_fdw/expected/postgres_fdw.out    |   4 +-
 contrib/postgres_fdw/meson.build              |   5 +
 contrib/postgres_fdw/option.c                 |   3 +
 contrib/postgres_fdw/t/001_auth_scram.pl      | 137 ++++++++++++++++++
 doc/src/sgml/libpq.sgml                       |  28 ++++
 doc/src/sgml/postgres-fdw.sgml                |  19 +++
 src/backend/libpq/auth-scram.c                |  14 +-
 src/include/libpq/libpq-be.h                  |   9 ++
 src/interfaces/libpq/fe-auth-scram.c          |  29 +++-
 src/interfaces/libpq/fe-auth.c                |   2 +-
 src/interfaces/libpq/fe-connect.c             |  47 ++++++
 src/interfaces/libpq/libpq-int.h              |   6 +
 14 files changed, 357 insertions(+), 16 deletions(-)
 create mode 100644 contrib/postgres_fdw/t/001_auth_scram.pl

diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index 88fdce40d6a..adfbd2ef758 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -17,6 +17,7 @@ EXTENSION = postgres_fdw
 DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql postgres_fdw--1.1--1.2.sql
 
 REGRESS = postgres_fdw query_cancel
+TAP_TESTS = 1
 
 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 202e7e583b3..06ec6201bb5 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -19,6 +19,7 @@
 #include "access/xact.h"
 #include "catalog/pg_user_mapping.h"
 #include "commands/defrem.h"
+#include "common/base64.h"
 #include "funcapi.h"
 #include "libpq/libpq-be.h"
 #include "libpq/libpq-be-fe-helpers.h"
@@ -177,6 +178,7 @@ static void pgfdw_finish_abort_cleanup(List *pending_entries,
 static void pgfdw_security_check(const char **keywords, const char **values,
 								 UserMapping *user, PGconn *conn);
 static bool UserMappingPasswordRequired(UserMapping *user);
+static bool UseScramPassthrough(ForeignServer *server, UserMapping *user);
 static bool disconnect_cached_connections(Oid serverid);
 static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
 												  enum pgfdwVersion api_version);
@@ -485,7 +487,7 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 		 * for application_name, fallback_application_name, client_encoding,
 		 * end marker.
 		 */
-		n = list_length(server->options) + list_length(user->options) + 4;
+		n = list_length(server->options) + list_length(user->options) + 4 + 2;
 		keywords = (const char **) palloc(n * sizeof(char *));
 		values = (const char **) palloc(n * sizeof(char *));
 
@@ -554,10 +556,37 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 		values[n] = GetDatabaseEncodingName();
 		n++;
 
+		if (MyProcPort->has_scram_keys && UseScramPassthrough(server, user))
+		{
+			int			len;
+
+			keywords[n] = "scram_client_key";
+			len = pg_b64_enc_len(sizeof(MyProcPort->scram_ClientKey));
+			/* don't forget the zero-terminator */
+			values[n] = palloc0(len+1);
+			pg_b64_encode((const char *) MyProcPort->scram_ClientKey,
+						  sizeof(MyProcPort->scram_ClientKey),
+						  (char *) values[n], len);
+			n++;
+
+			keywords[n] = "scram_server_key";
+			len = pg_b64_enc_len(sizeof(MyProcPort->scram_ServerKey));
+			/* don't forget the zero-terminator */
+			values[n] = palloc0(len+1);
+			pg_b64_encode((const char *) MyProcPort->scram_ServerKey,
+						  sizeof(MyProcPort->scram_ServerKey),
+						  (char *) values[n], len);
+			n++;
+		}
+
 		keywords[n] = values[n] = NULL;
 
-		/* verify the set of connection parameters */
-		check_conn_params(keywords, values, user);
+		/*
+		 * Verify the set of connection parameters only if scram pass-through
+		 * is not being used because the password is not necessary.
+		 */
+		if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
+			check_conn_params(keywords, values, user);
 
 		/* first time, allocate or get the custom wait event */
 		if (pgfdw_we_connect == 0)
@@ -575,8 +604,12 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 							server->servername),
 					 errdetail_internal("%s", pchomp(PQerrorMessage(conn)))));
 
-		/* Perform post-connection security checks */
-		pgfdw_security_check(keywords, values, user, conn);
+		/*
+		 * Perform post-connection security checks only if scram pass-through
+		 * is not being used because the password is not necessary.
+		 */
+		if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
+			pgfdw_security_check(keywords, values, user, conn);
 
 		/* Prepare new session for use */
 		configure_remote_session(conn);
@@ -629,6 +662,30 @@ UserMappingPasswordRequired(UserMapping *user)
 	return true;
 }
 
+static bool
+UseScramPassthrough(ForeignServer *server, UserMapping *user)
+{
+	ListCell   *cell;
+
+	foreach(cell, server->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(cell);
+
+		if (strcmp(def->defname, "use_scram_passthrough") == 0)
+			return defGetBoolean(def);
+	}
+
+	foreach(cell, user->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(cell);
+
+		if (strcmp(def->defname, "use_scram_passthrough") == 0)
+			return defGetBoolean(def);
+	}
+
+	return false;
+}
+
 /*
  * For non-superusers, insist that the connstr specify a password or that the
  * user provided their own GSSAPI delegated credentials.  This
@@ -666,7 +723,7 @@ check_conn_params(const char **keywords, const char **values, UserMapping *user)
 	ereport(ERROR,
 			(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
 			 errmsg("password or GSSAPI delegated credentials required"),
-			 errdetail("Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.")));
+			 errdetail("Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.")));
 }
 
 /*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bf322198a20..64aa12ecc48 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10301,7 +10301,7 @@ CREATE FOREIGN TABLE pg_temp.ft1_nopw (
 ) SERVER loopback_nopw OPTIONS (schema_name 'public', table_name 'ft1');
 SELECT 1 FROM ft1_nopw LIMIT 1;
 ERROR:  password or GSSAPI delegated credentials required
-DETAIL:  Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.
+DETAIL:  Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.
 -- If we add a password to the connstr it'll fail, because we don't allow passwords
 -- in connstrs only in user mappings.
 ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw');
@@ -10351,7 +10351,7 @@ DROP USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
 -- lacks password_required=false
 SELECT 1 FROM ft1_nopw LIMIT 1;
 ERROR:  password or GSSAPI delegated credentials required
-DETAIL:  Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.
+DETAIL:  Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.
 RESET ROLE;
 -- The user mapping for public is passwordless and lacks the password_required=false
 -- mapping option, but will work because the current user is a superuser.
diff --git a/contrib/postgres_fdw/meson.build b/contrib/postgres_fdw/meson.build
index 3f19981cffc..8b29be24dee 100644
--- a/contrib/postgres_fdw/meson.build
+++ b/contrib/postgres_fdw/meson.build
@@ -41,4 +41,9 @@ tests += {
     ],
     'regress_args': ['--dlpath', meson.build_root() / 'src/test/regress'],
   },
+  'tap': {
+    'tests': [
+      't/001_auth_scram.pl',
+    ],
+  },
 }
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 12aed4054fa..d0766f007d2 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -279,6 +279,9 @@ InitPgFdwOptions(void)
 		{"analyze_sampling", ForeignServerRelationId, false},
 		{"analyze_sampling", ForeignTableRelationId, false},
 
+		{"use_scram_passthrough", ForeignServerRelationId, false},
+		{"use_scram_passthrough", UserMappingRelationId, false},
+
 		/*
 		 * sslcert and sslkey are in fact libpq options, but we repeat them
 		 * here to allow them to appear in both foreign server context (when
diff --git a/contrib/postgres_fdw/t/001_auth_scram.pl b/contrib/postgres_fdw/t/001_auth_scram.pl
new file mode 100644
index 00000000000..d5be6fc35c5
--- /dev/null
+++ b/contrib/postgres_fdw/t/001_auth_scram.pl
@@ -0,0 +1,137 @@
+# Copyright (c) 2024-2025, PostgreSQL Global Development Group
+
+# Test SCRAM authentication when opening a new connection with a foreign
+# server.
+#
+# The test is executed by testing the SCRAM authentifcation on a looplback
+# connection on the same server and with different servers.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Utils;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+my $hostaddr = '127.0.0.1';
+my $user = "user01";
+
+my $db0 = "db0"; # For node1
+my $db1 = "db1"; # For node1
+my $db2 = "db2"; # For node2
+my $fdw_server = "db1_fdw";
+my $fdw_server2 = "db2_fdw";
+
+my $node1 = PostgreSQL::Test::Cluster->new('node1');
+my $node2 = PostgreSQL::Test::Cluster->new('node2');
+
+$node1->init;
+$node2->init;
+
+$node1->start;
+$node2->start;
+
+# Test setup
+
+$node1->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$node2->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$ENV{PGPASSWORD} = "pass";
+
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db0');
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db1');
+$node2->safe_psql('postgres', qq'CREATE DATABASE $db2');
+
+setup_table($node1, $db1, "t");
+setup_table($node2, $db2, "t2");
+
+$node1->safe_psql($db0, 'CREATE EXTENSION IF NOT EXISTS postgres_fdw');
+setup_fdw_server($node1, $db0, $fdw_server, $node1, $db1);
+setup_fdw_server($node1, $db0, $fdw_server2, $node2, $db2);
+
+setup_user_mapping($node1, $db0, $fdw_server);
+setup_user_mapping($node1, $db0, $fdw_server2);
+
+# Make the user have the same SCRAM key on both servers. Forcing to have the
+# same iteration and salt.
+my $rolpassword = $node1->safe_psql('postgres', qq"SELECT rolpassword FROM pg_authid WHERE rolname = '$user';");
+$node2->safe_psql('postgres', qq"ALTER ROLE $user PASSWORD '$rolpassword'");
+
+setup_pghba($node1);
+setup_pghba($node2);
+
+# End of test setup
+
+test_fdw_auth($node1, $db0, "t", $fdw_server, "SCRAM auth on the same database cluster must succeed");
+test_fdw_auth($node1, $db0, "t2", $fdw_server2, "SCRAM auth on a different database cluster must succeed");
+test_auth($node2, $db2, "t2",  "SCRAM auth directly on foreign server should still succeed");
+
+# Helper functions
+
+sub test_auth
+{
+	local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+	my ($node, $db, $tbl, $testname) = @_;
+	my $connstr = $node->connstr($db) . qq' user=$user';
+
+	my $ret = $node->safe_psql($db, qq'SELECT count(1) FROM $tbl',
+		connstr=>$connstr);
+
+	is($ret, '10', $testname);
+}
+
+sub test_fdw_auth
+{
+	local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+	my ($node, $db, $tbl, $fdw, $testname) = @_;
+	my $connstr = $node->connstr($db) . qq' user=$user';
+
+	$node->safe_psql($db, qq'IMPORT FOREIGN SCHEMA public LIMIT TO($tbl) FROM SERVER $fdw INTO public;',
+		connstr=>$connstr);
+
+	test_auth($node, $db, $tbl, $testname);
+}
+
+sub setup_pghba
+{
+	my ($node) = @_;
+
+	unlink($node->data_dir . '/pg_hba.conf');
+	$node->append_conf(
+		'pg_hba.conf', qq{
+	local   all             all                                     scram-sha-256
+	host    all             all             $hostaddr/32            scram-sha-256
+	});
+
+	$node->restart;
+}
+
+sub setup_user_mapping
+{
+	my ($node, $db, $fdw) = @_;
+
+	$node->safe_psql($db, qq'CREATE USER MAPPING FOR $user SERVER $fdw OPTIONS (user \'$user\');');
+	$node->safe_psql($db, qq'GRANT USAGE ON FOREIGN SERVER $fdw to $user;');
+	$node->safe_psql($db, qq'GRANT ALL ON SCHEMA public to $user');
+}
+
+sub setup_fdw_server
+{
+	my ($node, $db, $fdw, $fdw_node, $dbname) = @_;
+	my $host = $fdw_node->host;
+	my $port = $fdw_node->port;
+
+	$node->safe_psql($db, qq'CREATE SERVER $fdw FOREIGN DATA WRAPPER postgres_fdw options (
+		host \'$host\', port \'$port\', dbname \'$dbname\', use_scram_passthrough \'true\') ');
+}
+
+sub setup_table
+{
+	my ($node, $db, $tbl) = @_;
+
+	$node->safe_psql($db, qq'CREATE TABLE $tbl AS SELECT g,g+1 FROM generate_series(1,10) g(g)');
+	$node->safe_psql($db, qq'GRANT USAGE ON SCHEMA public to $user');
+	$node->safe_psql($db, qq'GRANT SELECT ON $tbl to $user');
+}
+
+done_testing();
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 105b22b3171..090b9832899 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2199,6 +2199,34 @@ <title>Parameter Key Words</title>
       </listitem>
      </varlistentry>
 
+     <varlistentry id="libpq-connect-scram-client-key" xreflabel="scram_client_key">
+      <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"/>
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="libpq-connect-scram-server-key" xreflabel="scram_server_key">
+      <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"/>
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="libpq-connect-service" xreflabel="service">
       <term><literal>service</literal></term>
       <listitem>
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 188e8f0b4d0..da04e14a044 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -770,6 +770,25 @@ <title>Connection Management Options</title>
       </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>
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index 1514133acdc..26dd241efa9 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -101,6 +101,7 @@
 #include "libpq/crypt.h"
 #include "libpq/sasl.h"
 #include "libpq/scram.h"
+#include "miscadmin.h"
 
 static void scram_get_mechanisms(Port *port, StringInfo buf);
 static void *scram_init(Port *port, const char *selected_mech,
@@ -144,6 +145,7 @@ typedef struct
 
 	int			iterations;
 	char	   *salt;			/* base64-encoded */
+	uint8		ClientKey[SCRAM_MAX_KEY_LEN];
 	uint8		StoredKey[SCRAM_MAX_KEY_LEN];
 	uint8		ServerKey[SCRAM_MAX_KEY_LEN];
 
@@ -462,6 +464,13 @@ scram_exchange(void *opaq, const char *input, int inputlen,
 	if (*output)
 		*outputlen = strlen(*output);
 
+	if (result == PG_SASL_EXCHANGE_SUCCESS && state->state == SCRAM_AUTH_FINISHED)
+	{
+		memcpy(MyProcPort->scram_ClientKey, state->ClientKey, sizeof(MyProcPort->scram_ClientKey));
+		memcpy(MyProcPort->scram_ServerKey, state->ServerKey, sizeof(MyProcPort->scram_ServerKey));
+		MyProcPort->has_scram_keys = true;
+	}
+
 	return result;
 }
 
@@ -1140,7 +1149,6 @@ static bool
 verify_client_proof(scram_state *state)
 {
 	uint8		ClientSignature[SCRAM_MAX_KEY_LEN];
-	uint8		ClientKey[SCRAM_MAX_KEY_LEN];
 	uint8		client_StoredKey[SCRAM_MAX_KEY_LEN];
 	pg_hmac_ctx *ctx = pg_hmac_create(state->hash_type);
 	int			i;
@@ -1173,10 +1181,10 @@ verify_client_proof(scram_state *state)
 
 	/* Extract the ClientKey that the client calculated from the proof */
 	for (i = 0; i < state->key_length; i++)
-		ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
+		state->ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
 
 	/* Hash it one more time, and compare with StoredKey */
-	if (scram_H(ClientKey, state->hash_type, state->key_length,
+	if (scram_H(state->ClientKey, state->hash_type, state->key_length,
 				client_StoredKey, &errstr) < 0)
 		elog(ERROR, "could not hash stored key: %s", errstr);
 
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 16da6f89ef1..2f6c29200ba 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -18,6 +18,8 @@
 #ifndef LIBPQ_BE_H
 #define LIBPQ_BE_H
 
+#include "common/scram-common.h"
+
 #include <sys/time.h>
 #ifdef USE_OPENSSL
 #include <openssl/ssl.h>
@@ -181,6 +183,13 @@ typedef struct Port
 	int			keepalives_count;
 	int			tcp_user_timeout;
 
+	/*
+	 * SCRAM structures.
+	 */
+	uint8		scram_ClientKey[SCRAM_MAX_KEY_LEN];
+	uint8		scram_ServerKey[SCRAM_MAX_KEY_LEN];
+	bool		has_scram_keys; /* true if the above two are valid */
+
 	/*
 	 * GSSAPI structures.
 	 */
diff --git a/src/interfaces/libpq/fe-auth-scram.c b/src/interfaces/libpq/fe-auth-scram.c
index 59bf87d2213..dda43089121 100644
--- a/src/interfaces/libpq/fe-auth-scram.c
+++ b/src/interfaces/libpq/fe-auth-scram.c
@@ -119,6 +119,8 @@ scram_init(PGconn *conn,
 		return NULL;
 	}
 
+	if (password)
+	{
 	/* Normalize the password with SASLprep, if possible */
 	rc = pg_saslprep(password, &prep_password);
 	if (rc == SASLPREP_OOM)
@@ -138,6 +140,7 @@ scram_init(PGconn *conn,
 		}
 	}
 	state->password = prep_password;
+	}
 
 	return state;
 }
@@ -775,6 +778,12 @@ calculate_client_proof(fe_scram_state *state,
 		return false;
 	}
 
+	if (state->conn->scram_client_key_binary)
+	{
+		memcpy(ClientKey, state->conn->scram_client_key_binary, SCRAM_MAX_KEY_LEN);
+	}
+	else
+	{
 	/*
 	 * Calculate SaltedPassword, and store it in 'state' so that we can reuse
 	 * it later in verify_server_signature.
@@ -783,15 +792,20 @@ calculate_client_proof(fe_scram_state *state,
 							 state->key_length, state->salt, state->saltlen,
 							 state->iterations, state->SaltedPassword,
 							 errstr) < 0 ||
-		scram_ClientKey(state->SaltedPassword, state->hash_type,
-						state->key_length, ClientKey, errstr) < 0 ||
-		scram_H(ClientKey, state->hash_type, state->key_length,
-				StoredKey, errstr) < 0)
+			scram_ClientKey(state->SaltedPassword, state->hash_type,
+						state->key_length, ClientKey, errstr) < 0)
 	{
 		/* errstr is already filled here */
 		pg_hmac_free(ctx);
 		return false;
 	}
+	}
+
+	if (scram_H(ClientKey, state->hash_type, state->key_length, StoredKey, errstr)  < 0)
+	{
+		pg_hmac_free(ctx);
+		return false;
+	}
 
 	if (pg_hmac_init(ctx, StoredKey, state->key_length) < 0 ||
 		pg_hmac_update(ctx,
@@ -841,6 +855,12 @@ verify_server_signature(fe_scram_state *state, bool *match,
 		return false;
 	}
 
+	if (state->conn->scram_server_key_binary)
+	{
+		memcpy(ServerKey, state->conn->scram_server_key_binary, SCRAM_MAX_KEY_LEN);
+	}
+	else
+	{
 	if (scram_ServerKey(state->SaltedPassword, state->hash_type,
 						state->key_length, ServerKey, errstr) < 0)
 	{
@@ -848,6 +868,7 @@ verify_server_signature(fe_scram_state *state, bool *match,
 		pg_hmac_free(ctx);
 		return false;
 	}
+	}
 
 	/* calculate ServerSignature */
 	if (pg_hmac_init(ctx, ServerKey, state->key_length) < 0 ||
diff --git a/src/interfaces/libpq/fe-auth.c b/src/interfaces/libpq/fe-auth.c
index 14a9a862f51..7e478489b71 100644
--- a/src/interfaces/libpq/fe-auth.c
+++ b/src/interfaces/libpq/fe-auth.c
@@ -559,7 +559,7 @@ pg_SASL_init(PGconn *conn, int payloadlen)
 	 * First, select the password to use for the exchange, complaining if
 	 * there isn't one and the selected SASL mechanism needs it.
 	 */
-	if (conn->password_needed)
+	if (conn->password_needed && !conn->scram_client_key_binary)
 	{
 		password = conn->connhost[conn->whichhost].password;
 		if (password == NULL)
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 8f211821eb2..ac5af8f5240 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -22,6 +22,7 @@
 #include <time.h>
 #include <unistd.h>
 
+#include "common/base64.h"
 #include "common/ip.h"
 #include "common/link-canary.h"
 #include "common/scram-common.h"
@@ -366,6 +367,12 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
 		"Load-Balance-Hosts", "", 8,	/* sizeof("disable") = 8 */
 	offsetof(struct pg_conn, load_balance_hosts)},
 
+	{"scram_client_key", NULL, NULL, NULL, "SCRAM-Client-Key", "D", SCRAM_MAX_KEY_LEN * 2,
+	offsetof(struct pg_conn, scram_client_key)},
+
+	{"scram_server_key", NULL, NULL, NULL, "SCRAM-Server-Key", "D", SCRAM_MAX_KEY_LEN * 2,
+	offsetof(struct pg_conn, scram_server_key)},
+
 	/* Terminating entry --- MUST BE LAST */
 	{NULL, NULL, NULL, NULL,
 	NULL, NULL, 0}
@@ -1793,6 +1800,44 @@ pqConnectOptions2(PGconn *conn)
 	else
 		conn->target_server_type = SERVER_TYPE_ANY;
 
+	if (conn->scram_client_key)
+	{
+		int			len;
+
+		len = pg_b64_dec_len(strlen(conn->scram_client_key));
+		/* Consider the zero-terminator */
+		if (len != SCRAM_MAX_KEY_LEN+1)
+		{
+			libpq_append_conn_error(conn, "invalid scram client key len: %d", len);
+			return false;
+		}
+		conn->scram_client_key_len = len;
+		conn->scram_client_key_binary = malloc(len);
+		if (!conn->scram_client_key_binary)
+			goto oom_error;
+		pg_b64_decode(conn->scram_client_key, strlen(conn->scram_client_key),
+					  conn->scram_client_key_binary, len);
+	}
+
+	if (conn->scram_server_key)
+	{
+		int			len;
+
+		len = pg_b64_dec_len(strlen(conn->scram_server_key));
+		/* Consider the zero-terminator */
+		if (len != SCRAM_MAX_KEY_LEN+1)
+		{
+			libpq_append_conn_error(conn, "invalid scram server key len: %d", len);
+			return false;
+		}
+		conn->scram_server_key_len = len;
+		conn->scram_server_key_binary = malloc(len);
+		if (!conn->scram_server_key_binary)
+			goto oom_error;
+		pg_b64_decode(conn->scram_server_key, strlen(conn->scram_server_key),
+					  conn->scram_server_key_binary, len);
+	}
+
 	/*
 	 * validate load_balance_hosts option, and set load_balance_type
 	 */
@@ -4704,6 +4749,8 @@ freePGconn(PGconn *conn)
 	free(conn->rowBuf);
 	free(conn->target_session_attrs);
 	free(conn->load_balance_hosts);
+	free(conn->scram_client_key);
+	free(conn->scram_server_key);
 	termPQExpBuffer(&conn->errorMessage);
 	termPQExpBuffer(&conn->workBuffer);
 
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 4a5a7c8b5e3..b96630298eb 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -428,6 +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 */
 
 	bool		cancelRequest;	/* true if this connection is used to send a
 								 * cancel request, instead of being a normal
@@ -518,6 +520,10 @@ struct pg_conn
 	AddrInfo   *addr;			/* the array of addresses for the currently
 								 * 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 */
+	size_t		scram_server_key_len;
+	void	   *scram_server_key_binary; /*base64 decoded scram server key */
 
 	/* Miscellaneous stuff */
 	int			be_pid;			/* PID of backend --- needed for cancels */
-- 
2.47.1

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

From 161b47ed18e54d3a18615dc60c1b972106190ed5 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
Date: Tue, 19 Nov 2024 15:37:57 -0300
Subject: [PATCH v4] postgres_fdw: SCRAM authentication pass-through

This commit enable SCRAM authentication for postgres_fdw when connecting
to a fdw server without having to store a plain-text password on user
mapping options.

This is done by saving the SCRAM ClientKey and ServeryKey from the
client authentication and using those instead of the plain-text password
for the server-side SCRAM exchange.
---
 contrib/postgres_fdw/Makefile                 |   1 +
 contrib/postgres_fdw/connection.c             |  69 ++++++++-
 .../postgres_fdw/expected/postgres_fdw.out    |   4 +-
 contrib/postgres_fdw/meson.build              |   5 +
 contrib/postgres_fdw/option.c                 |   3 +
 contrib/postgres_fdw/t/001_auth_scram.pl      | 137 ++++++++++++++++++
 doc/src/sgml/libpq.sgml                       |  28 ++++
 doc/src/sgml/postgres-fdw.sgml                |  72 +++++++++
 src/backend/libpq/auth-scram.c                |  14 +-
 src/include/libpq/libpq-be.h                  |   9 ++
 src/interfaces/libpq/fe-auth-scram.c          |  29 +++-
 src/interfaces/libpq/fe-auth.c                |   2 +-
 src/interfaces/libpq/fe-connect.c             |  47 ++++++
 src/interfaces/libpq/libpq-int.h              |   6 +
 14 files changed, 410 insertions(+), 16 deletions(-)
 create mode 100644 contrib/postgres_fdw/t/001_auth_scram.pl

diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index 88fdce40d6..adfbd2ef75 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -17,6 +17,7 @@ EXTENSION = postgres_fdw
 DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql postgres_fdw--1.1--1.2.sql
 
 REGRESS = postgres_fdw query_cancel
+TAP_TESTS = 1
 
 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 202e7e583b..06ec6201bb 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -19,6 +19,7 @@
 #include "access/xact.h"
 #include "catalog/pg_user_mapping.h"
 #include "commands/defrem.h"
+#include "common/base64.h"
 #include "funcapi.h"
 #include "libpq/libpq-be.h"
 #include "libpq/libpq-be-fe-helpers.h"
@@ -177,6 +178,7 @@ static void pgfdw_finish_abort_cleanup(List *pending_entries,
 static void pgfdw_security_check(const char **keywords, const char **values,
 								 UserMapping *user, PGconn *conn);
 static bool UserMappingPasswordRequired(UserMapping *user);
+static bool UseScramPassthrough(ForeignServer *server, UserMapping *user);
 static bool disconnect_cached_connections(Oid serverid);
 static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
 												  enum pgfdwVersion api_version);
@@ -485,7 +487,7 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 		 * for application_name, fallback_application_name, client_encoding,
 		 * end marker.
 		 */
-		n = list_length(server->options) + list_length(user->options) + 4;
+		n = list_length(server->options) + list_length(user->options) + 4 + 2;
 		keywords = (const char **) palloc(n * sizeof(char *));
 		values = (const char **) palloc(n * sizeof(char *));
 
@@ -554,10 +556,37 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 		values[n] = GetDatabaseEncodingName();
 		n++;
 
+		if (MyProcPort->has_scram_keys && UseScramPassthrough(server, user))
+		{
+			int			len;
+
+			keywords[n] = "scram_client_key";
+			len = pg_b64_enc_len(sizeof(MyProcPort->scram_ClientKey));
+			/* don't forget the zero-terminator */
+			values[n] = palloc0(len+1);
+			pg_b64_encode((const char *) MyProcPort->scram_ClientKey,
+						  sizeof(MyProcPort->scram_ClientKey),
+						  (char *) values[n], len);
+			n++;
+
+			keywords[n] = "scram_server_key";
+			len = pg_b64_enc_len(sizeof(MyProcPort->scram_ServerKey));
+			/* don't forget the zero-terminator */
+			values[n] = palloc0(len+1);
+			pg_b64_encode((const char *) MyProcPort->scram_ServerKey,
+						  sizeof(MyProcPort->scram_ServerKey),
+						  (char *) values[n], len);
+			n++;
+		}
+
 		keywords[n] = values[n] = NULL;
 
-		/* verify the set of connection parameters */
-		check_conn_params(keywords, values, user);
+		/*
+		 * Verify the set of connection parameters only if scram pass-through
+		 * is not being used because the password is not necessary.
+		 */
+		if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
+			check_conn_params(keywords, values, user);
 
 		/* first time, allocate or get the custom wait event */
 		if (pgfdw_we_connect == 0)
@@ -575,8 +604,12 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 							server->servername),
 					 errdetail_internal("%s", pchomp(PQerrorMessage(conn)))));
 
-		/* Perform post-connection security checks */
-		pgfdw_security_check(keywords, values, user, conn);
+		/*
+		 * Perform post-connection security checks only if scram pass-through
+		 * is not being used because the password is not necessary.
+		 */
+		if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
+			pgfdw_security_check(keywords, values, user, conn);
 
 		/* Prepare new session for use */
 		configure_remote_session(conn);
@@ -629,6 +662,30 @@ UserMappingPasswordRequired(UserMapping *user)
 	return true;
 }
 
+static bool
+UseScramPassthrough(ForeignServer *server, UserMapping *user)
+{
+	ListCell   *cell;
+
+	foreach(cell, server->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(cell);
+
+		if (strcmp(def->defname, "use_scram_passthrough") == 0)
+			return defGetBoolean(def);
+	}
+
+	foreach(cell, user->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(cell);
+
+		if (strcmp(def->defname, "use_scram_passthrough") == 0)
+			return defGetBoolean(def);
+	}
+
+	return false;
+}
+
 /*
  * For non-superusers, insist that the connstr specify a password or that the
  * user provided their own GSSAPI delegated credentials.  This
@@ -666,7 +723,7 @@ check_conn_params(const char **keywords, const char **values, UserMapping *user)
 	ereport(ERROR,
 			(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
 			 errmsg("password or GSSAPI delegated credentials required"),
-			 errdetail("Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.")));
+			 errdetail("Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.")));
 }
 
 /*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bf322198a2..64aa12ecc4 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10301,7 +10301,7 @@ CREATE FOREIGN TABLE pg_temp.ft1_nopw (
 ) SERVER loopback_nopw OPTIONS (schema_name 'public', table_name 'ft1');
 SELECT 1 FROM ft1_nopw LIMIT 1;
 ERROR:  password or GSSAPI delegated credentials required
-DETAIL:  Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.
+DETAIL:  Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.
 -- If we add a password to the connstr it'll fail, because we don't allow passwords
 -- in connstrs only in user mappings.
 ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw');
@@ -10351,7 +10351,7 @@ DROP USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
 -- lacks password_required=false
 SELECT 1 FROM ft1_nopw LIMIT 1;
 ERROR:  password or GSSAPI delegated credentials required
-DETAIL:  Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.
+DETAIL:  Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.
 RESET ROLE;
 -- The user mapping for public is passwordless and lacks the password_required=false
 -- mapping option, but will work because the current user is a superuser.
diff --git a/contrib/postgres_fdw/meson.build b/contrib/postgres_fdw/meson.build
index 3f19981cff..8b29be24de 100644
--- a/contrib/postgres_fdw/meson.build
+++ b/contrib/postgres_fdw/meson.build
@@ -41,4 +41,9 @@ tests += {
     ],
     'regress_args': ['--dlpath', meson.build_root() / 'src/test/regress'],
   },
+  'tap': {
+    'tests': [
+      't/001_auth_scram.pl',
+    ],
+  },
 }
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 12aed4054f..d0766f007d 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -279,6 +279,9 @@ InitPgFdwOptions(void)
 		{"analyze_sampling", ForeignServerRelationId, false},
 		{"analyze_sampling", ForeignTableRelationId, false},
 
+		{"use_scram_passthrough", ForeignServerRelationId, false},
+		{"use_scram_passthrough", UserMappingRelationId, false},
+
 		/*
 		 * sslcert and sslkey are in fact libpq options, but we repeat them
 		 * here to allow them to appear in both foreign server context (when
diff --git a/contrib/postgres_fdw/t/001_auth_scram.pl b/contrib/postgres_fdw/t/001_auth_scram.pl
new file mode 100644
index 0000000000..d5be6fc35c
--- /dev/null
+++ b/contrib/postgres_fdw/t/001_auth_scram.pl
@@ -0,0 +1,137 @@
+# Copyright (c) 2024-2025, PostgreSQL Global Development Group
+
+# Test SCRAM authentication when opening a new connection with a foreign
+# server.
+#
+# The test is executed by testing the SCRAM authentifcation on a looplback
+# connection on the same server and with different servers.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Utils;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+my $hostaddr = '127.0.0.1';
+my $user = "user01";
+
+my $db0 = "db0"; # For node1
+my $db1 = "db1"; # For node1
+my $db2 = "db2"; # For node2
+my $fdw_server = "db1_fdw";
+my $fdw_server2 = "db2_fdw";
+
+my $node1 = PostgreSQL::Test::Cluster->new('node1');
+my $node2 = PostgreSQL::Test::Cluster->new('node2');
+
+$node1->init;
+$node2->init;
+
+$node1->start;
+$node2->start;
+
+# Test setup
+
+$node1->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$node2->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$ENV{PGPASSWORD} = "pass";
+
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db0');
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db1');
+$node2->safe_psql('postgres', qq'CREATE DATABASE $db2');
+
+setup_table($node1, $db1, "t");
+setup_table($node2, $db2, "t2");
+
+$node1->safe_psql($db0, 'CREATE EXTENSION IF NOT EXISTS postgres_fdw');
+setup_fdw_server($node1, $db0, $fdw_server, $node1, $db1);
+setup_fdw_server($node1, $db0, $fdw_server2, $node2, $db2);
+
+setup_user_mapping($node1, $db0, $fdw_server);
+setup_user_mapping($node1, $db0, $fdw_server2);
+
+# Make the user have the same SCRAM key on both servers. Forcing to have the
+# same iteration and salt.
+my $rolpassword = $node1->safe_psql('postgres', qq"SELECT rolpassword FROM pg_authid WHERE rolname = '$user';");
+$node2->safe_psql('postgres', qq"ALTER ROLE $user PASSWORD '$rolpassword'");
+
+setup_pghba($node1);
+setup_pghba($node2);
+
+# End of test setup
+
+test_fdw_auth($node1, $db0, "t", $fdw_server, "SCRAM auth on the same database cluster must succeed");
+test_fdw_auth($node1, $db0, "t2", $fdw_server2, "SCRAM auth on a different database cluster must succeed");
+test_auth($node2, $db2, "t2",  "SCRAM auth directly on foreign server should still succeed");
+
+# Helper functions
+
+sub test_auth
+{
+	local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+	my ($node, $db, $tbl, $testname) = @_;
+	my $connstr = $node->connstr($db) . qq' user=$user';
+
+	my $ret = $node->safe_psql($db, qq'SELECT count(1) FROM $tbl',
+		connstr=>$connstr);
+
+	is($ret, '10', $testname);
+}
+
+sub test_fdw_auth
+{
+	local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+	my ($node, $db, $tbl, $fdw, $testname) = @_;
+	my $connstr = $node->connstr($db) . qq' user=$user';
+
+	$node->safe_psql($db, qq'IMPORT FOREIGN SCHEMA public LIMIT TO($tbl) FROM SERVER $fdw INTO public;',
+		connstr=>$connstr);
+
+	test_auth($node, $db, $tbl, $testname);
+}
+
+sub setup_pghba
+{
+	my ($node) = @_;
+
+	unlink($node->data_dir . '/pg_hba.conf');
+	$node->append_conf(
+		'pg_hba.conf', qq{
+	local   all             all                                     scram-sha-256
+	host    all             all             $hostaddr/32            scram-sha-256
+	});
+
+	$node->restart;
+}
+
+sub setup_user_mapping
+{
+	my ($node, $db, $fdw) = @_;
+
+	$node->safe_psql($db, qq'CREATE USER MAPPING FOR $user SERVER $fdw OPTIONS (user \'$user\');');
+	$node->safe_psql($db, qq'GRANT USAGE ON FOREIGN SERVER $fdw to $user;');
+	$node->safe_psql($db, qq'GRANT ALL ON SCHEMA public to $user');
+}
+
+sub setup_fdw_server
+{
+	my ($node, $db, $fdw, $fdw_node, $dbname) = @_;
+	my $host = $fdw_node->host;
+	my $port = $fdw_node->port;
+
+	$node->safe_psql($db, qq'CREATE SERVER $fdw FOREIGN DATA WRAPPER postgres_fdw options (
+		host \'$host\', port \'$port\', dbname \'$dbname\', use_scram_passthrough \'true\') ');
+}
+
+sub setup_table
+{
+	my ($node, $db, $tbl) = @_;
+
+	$node->safe_psql($db, qq'CREATE TABLE $tbl AS SELECT g,g+1 FROM generate_series(1,10) g(g)');
+	$node->safe_psql($db, qq'GRANT USAGE ON SCHEMA public to $user');
+	$node->safe_psql($db, qq'GRANT SELECT ON $tbl to $user');
+}
+
+done_testing();
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 105b22b317..e04acf1c20 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2199,6 +2199,34 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
       </listitem>
      </varlistentry>
 
+     <varlistentry id="libpq-connect-scram-client-key" xreflabel="scram_client_key">
+      <term><literal>scram_client_key</literal></term>
+      <listitem>
+       <para>
+        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>
+
+     <varlistentry id="libpq-connect-scram-server-key" xreflabel="scram_server_key">
+      <term><literal>scram_server_key</literal></term>
+      <listitem>
+       <para>
+        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>
+
      <varlistentry id="libpq-connect-service" xreflabel="service">
       <term><literal>service</literal></term>
       <listitem>
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 188e8f0b4d..d2998c13d5 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -770,6 +770,78 @@ 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 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>
+
     </variablelist>
    </sect3>
  </sect2>
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index 1514133acd..26dd241efa 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -101,6 +101,7 @@
 #include "libpq/crypt.h"
 #include "libpq/sasl.h"
 #include "libpq/scram.h"
+#include "miscadmin.h"
 
 static void scram_get_mechanisms(Port *port, StringInfo buf);
 static void *scram_init(Port *port, const char *selected_mech,
@@ -144,6 +145,7 @@ typedef struct
 
 	int			iterations;
 	char	   *salt;			/* base64-encoded */
+	uint8		ClientKey[SCRAM_MAX_KEY_LEN];
 	uint8		StoredKey[SCRAM_MAX_KEY_LEN];
 	uint8		ServerKey[SCRAM_MAX_KEY_LEN];
 
@@ -462,6 +464,13 @@ scram_exchange(void *opaq, const char *input, int inputlen,
 	if (*output)
 		*outputlen = strlen(*output);
 
+	if (result == PG_SASL_EXCHANGE_SUCCESS && state->state == SCRAM_AUTH_FINISHED)
+	{
+		memcpy(MyProcPort->scram_ClientKey, state->ClientKey, sizeof(MyProcPort->scram_ClientKey));
+		memcpy(MyProcPort->scram_ServerKey, state->ServerKey, sizeof(MyProcPort->scram_ServerKey));
+		MyProcPort->has_scram_keys = true;
+	}
+
 	return result;
 }
 
@@ -1140,7 +1149,6 @@ static bool
 verify_client_proof(scram_state *state)
 {
 	uint8		ClientSignature[SCRAM_MAX_KEY_LEN];
-	uint8		ClientKey[SCRAM_MAX_KEY_LEN];
 	uint8		client_StoredKey[SCRAM_MAX_KEY_LEN];
 	pg_hmac_ctx *ctx = pg_hmac_create(state->hash_type);
 	int			i;
@@ -1173,10 +1181,10 @@ verify_client_proof(scram_state *state)
 
 	/* Extract the ClientKey that the client calculated from the proof */
 	for (i = 0; i < state->key_length; i++)
-		ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
+		state->ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
 
 	/* Hash it one more time, and compare with StoredKey */
-	if (scram_H(ClientKey, state->hash_type, state->key_length,
+	if (scram_H(state->ClientKey, state->hash_type, state->key_length,
 				client_StoredKey, &errstr) < 0)
 		elog(ERROR, "could not hash stored key: %s", errstr);
 
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 16da6f89ef..2f6c29200b 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -18,6 +18,8 @@
 #ifndef LIBPQ_BE_H
 #define LIBPQ_BE_H
 
+#include "common/scram-common.h"
+
 #include <sys/time.h>
 #ifdef USE_OPENSSL
 #include <openssl/ssl.h>
@@ -181,6 +183,13 @@ typedef struct Port
 	int			keepalives_count;
 	int			tcp_user_timeout;
 
+	/*
+	 * SCRAM structures.
+	 */
+	uint8		scram_ClientKey[SCRAM_MAX_KEY_LEN];
+	uint8		scram_ServerKey[SCRAM_MAX_KEY_LEN];
+	bool		has_scram_keys; /* true if the above two are valid */
+
 	/*
 	 * GSSAPI structures.
 	 */
diff --git a/src/interfaces/libpq/fe-auth-scram.c b/src/interfaces/libpq/fe-auth-scram.c
index 59bf87d221..dda4308912 100644
--- a/src/interfaces/libpq/fe-auth-scram.c
+++ b/src/interfaces/libpq/fe-auth-scram.c
@@ -119,6 +119,8 @@ scram_init(PGconn *conn,
 		return NULL;
 	}
 
+	if (password)
+	{
 	/* Normalize the password with SASLprep, if possible */
 	rc = pg_saslprep(password, &prep_password);
 	if (rc == SASLPREP_OOM)
@@ -138,6 +140,7 @@ scram_init(PGconn *conn,
 		}
 	}
 	state->password = prep_password;
+	}
 
 	return state;
 }
@@ -775,6 +778,12 @@ calculate_client_proof(fe_scram_state *state,
 		return false;
 	}
 
+	if (state->conn->scram_client_key_binary)
+	{
+		memcpy(ClientKey, state->conn->scram_client_key_binary, SCRAM_MAX_KEY_LEN);
+	}
+	else
+	{
 	/*
 	 * Calculate SaltedPassword, and store it in 'state' so that we can reuse
 	 * it later in verify_server_signature.
@@ -783,15 +792,20 @@ calculate_client_proof(fe_scram_state *state,
 							 state->key_length, state->salt, state->saltlen,
 							 state->iterations, state->SaltedPassword,
 							 errstr) < 0 ||
-		scram_ClientKey(state->SaltedPassword, state->hash_type,
-						state->key_length, ClientKey, errstr) < 0 ||
-		scram_H(ClientKey, state->hash_type, state->key_length,
-				StoredKey, errstr) < 0)
+			scram_ClientKey(state->SaltedPassword, state->hash_type,
+						state->key_length, ClientKey, errstr) < 0)
 	{
 		/* errstr is already filled here */
 		pg_hmac_free(ctx);
 		return false;
 	}
+	}
+
+	if (scram_H(ClientKey, state->hash_type, state->key_length, StoredKey, errstr)  < 0)
+	{
+		pg_hmac_free(ctx);
+		return false;
+	}
 
 	if (pg_hmac_init(ctx, StoredKey, state->key_length) < 0 ||
 		pg_hmac_update(ctx,
@@ -841,6 +855,12 @@ verify_server_signature(fe_scram_state *state, bool *match,
 		return false;
 	}
 
+	if (state->conn->scram_server_key_binary)
+	{
+		memcpy(ServerKey, state->conn->scram_server_key_binary, SCRAM_MAX_KEY_LEN);
+	}
+	else
+	{
 	if (scram_ServerKey(state->SaltedPassword, state->hash_type,
 						state->key_length, ServerKey, errstr) < 0)
 	{
@@ -848,6 +868,7 @@ verify_server_signature(fe_scram_state *state, bool *match,
 		pg_hmac_free(ctx);
 		return false;
 	}
+	}
 
 	/* calculate ServerSignature */
 	if (pg_hmac_init(ctx, ServerKey, state->key_length) < 0 ||
diff --git a/src/interfaces/libpq/fe-auth.c b/src/interfaces/libpq/fe-auth.c
index 14a9a862f5..7e478489b7 100644
--- a/src/interfaces/libpq/fe-auth.c
+++ b/src/interfaces/libpq/fe-auth.c
@@ -559,7 +559,7 @@ pg_SASL_init(PGconn *conn, int payloadlen)
 	 * First, select the password to use for the exchange, complaining if
 	 * there isn't one and the selected SASL mechanism needs it.
 	 */
-	if (conn->password_needed)
+	if (conn->password_needed && !conn->scram_client_key_binary)
 	{
 		password = conn->connhost[conn->whichhost].password;
 		if (password == NULL)
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 8f211821eb..4931c77a24 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -22,6 +22,7 @@
 #include <time.h>
 #include <unistd.h>
 
+#include "common/base64.h"
 #include "common/ip.h"
 #include "common/link-canary.h"
 #include "common/scram-common.h"
@@ -366,6 +367,12 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
 		"Load-Balance-Hosts", "", 8,	/* sizeof("disable") = 8 */
 	offsetof(struct pg_conn, load_balance_hosts)},
 
+	{"scram_client_key", NULL, NULL, NULL, "SCRAM-Client-Key", "D", SCRAM_MAX_KEY_LEN * 2,
+	offsetof(struct pg_conn, scram_client_key)},
+
+	{"scram_server_key", NULL, NULL, NULL, "SCRAM-Server-Key", "D", SCRAM_MAX_KEY_LEN * 2,
+	offsetof(struct pg_conn, scram_server_key)},
+
 	/* Terminating entry --- MUST BE LAST */
 	{NULL, NULL, NULL, NULL,
 	NULL, NULL, 0}
@@ -1793,6 +1800,44 @@ pqConnectOptions2(PGconn *conn)
 	else
 		conn->target_server_type = SERVER_TYPE_ANY;
 
+	if (conn->scram_client_key)
+	{
+		int			len;
+
+		len = pg_b64_dec_len(strlen(conn->scram_client_key));
+		/* Consider the zero-terminator */
+		if (len != SCRAM_MAX_KEY_LEN+1)
+		{
+			libpq_append_conn_error(conn, "invalid SCRAM client key length: %d", len);
+			return false;
+		}
+		conn->scram_client_key_len = len;
+		conn->scram_client_key_binary = malloc(len);
+		if (!conn->scram_client_key_binary)
+			goto oom_error;
+		pg_b64_decode(conn->scram_client_key, strlen(conn->scram_client_key),
+					  conn->scram_client_key_binary, len);
+	}
+
+	if (conn->scram_server_key)
+	{
+		int			len;
+
+		len = pg_b64_dec_len(strlen(conn->scram_server_key));
+		/* Consider the zero-terminator */
+		if (len != SCRAM_MAX_KEY_LEN+1)
+		{
+			libpq_append_conn_error(conn, "invalid SCRAM server key length: %d", len);
+			return false;
+		}
+		conn->scram_server_key_len = len;
+		conn->scram_server_key_binary = malloc(len);
+		if (!conn->scram_server_key_binary)
+			goto oom_error;
+		pg_b64_decode(conn->scram_server_key, strlen(conn->scram_server_key),
+					  conn->scram_server_key_binary, len);
+	}
+
 	/*
 	 * validate load_balance_hosts option, and set load_balance_type
 	 */
@@ -4704,6 +4749,8 @@ freePGconn(PGconn *conn)
 	free(conn->rowBuf);
 	free(conn->target_session_attrs);
 	free(conn->load_balance_hosts);
+	free(conn->scram_client_key);
+	free(conn->scram_server_key);
 	termPQExpBuffer(&conn->errorMessage);
 	termPQExpBuffer(&conn->workBuffer);
 
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 4a5a7c8b5e..1f10571867 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -428,6 +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 */
 
 	bool		cancelRequest;	/* true if this connection is used to send a
 								 * cancel request, instead of being a normal
@@ -518,6 +520,10 @@ struct pg_conn
 	AddrInfo   *addr;			/* the array of addresses for the currently
 								 * tried host */
 	bool		send_appname;	/* okay to send application_name? */
+	size_t		scram_client_key_len;
+	void	   *scram_client_key_binary; /* binary (decoded) SCRAM client key */
+	size_t		scram_server_key_len;
+	void	   *scram_server_key_binary; /* binary (decoded) SCRAM server key */
 
 	/* Miscellaneous stuff */
 	int			be_pid;			/* PID of backend --- needed for cancels */
-- 
2.39.5 (Apple Git-154)

From 58536182f301ab218d57fd40f298666359ec5757 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
Date: Wed, 25 Jun 2025 12:05:53 +0300
Subject: [PATCH] postgres_fdw and dblink should check if backend has
 MyProcPort

---
 contrib/dblink/dblink.c           | 8 ++++----
 contrib/postgres_fdw/connection.c | 8 ++++----
 2 files changed, 8 insertions(+), 8 deletions(-)

diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c
index 8a0b112a7ff..ec0c832e921 100644
--- a/contrib/dblink/dblink.c
+++ b/contrib/dblink/dblink.c
@@ -2665,7 +2665,7 @@ dblink_connstr_has_required_scram_options(const char *connstr)
 		PQconninfoFree(options);
 	}
 
-	has_scram_keys = has_scram_client_key && has_scram_server_key && MyProcPort->has_scram_keys;
+	has_scram_keys = has_scram_client_key && has_scram_server_key && MyProcPort != NULL && MyProcPort->has_scram_keys;
 
 	return (has_scram_keys && has_require_auth);
 }
@@ -2698,7 +2698,7 @@ dblink_security_check(PGconn *conn, const char *connname, const char *connstr)
 	 * only added if UseScramPassthrough is set, and the user is not allowed
 	 * to add the SCRAM keys on fdw and user mapping options.
 	 */
-	if (MyProcPort->has_scram_keys && dblink_connstr_has_required_scram_options(connstr))
+	if (MyProcPort != NULL && MyProcPort->has_scram_keys && dblink_connstr_has_required_scram_options(connstr))
 		return;
 
 #ifdef ENABLE_GSS
@@ -2771,7 +2771,7 @@ dblink_connstr_check(const char *connstr)
 	if (dblink_connstr_has_pw(connstr))
 		return;
 
-	if (MyProcPort->has_scram_keys && dblink_connstr_has_required_scram_options(connstr))
+	if (MyProcPort != NULL && MyProcPort->has_scram_keys && dblink_connstr_has_required_scram_options(connstr))
 		return;
 
 #ifdef ENABLE_GSS
@@ -2931,7 +2931,7 @@ get_connect_string(const char *servername)
 		 * the user overwrites these options we can ereport on
 		 * dblink_connstr_check and dblink_security_check.
 		 */
-		if (MyProcPort->has_scram_keys && UseScramPassthrough(foreign_server, user_mapping))
+		if (MyProcPort != NULL && MyProcPort->has_scram_keys && UseScramPassthrough(foreign_server, user_mapping))
 			appendSCRAMKeysInfo(&buf);
 
 		foreach(cell, fdw->options)
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 304f3c20f83..eb255e74d15 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -462,7 +462,7 @@ pgfdw_security_check(const char **keywords, const char **values, UserMapping *us
 	 * assume that UseScramPassthrough is also true since SCRAM options are
 	 * only set when UseScramPassthrough is enabled.
 	 */
-	if (MyProcPort->has_scram_keys && pgfdw_has_required_scram_options(keywords, values))
+	if (MyProcPort != NULL && MyProcPort->has_scram_keys && pgfdw_has_required_scram_options(keywords, values))
 		return;
 
 	ereport(ERROR,
@@ -568,7 +568,7 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 		n++;
 
 		/* Add required SCRAM pass-through connection options if it's enabled. */
-		if (MyProcPort->has_scram_keys && UseScramPassthrough(server, user))
+		if (MyProcPort != NULL && MyProcPort->has_scram_keys && UseScramPassthrough(server, user))
 		{
 			int			len;
 			int			encoded_len;
@@ -743,7 +743,7 @@ check_conn_params(const char **keywords, const char **values, UserMapping *user)
 	 * assume that UseScramPassthrough is also true since SCRAM options are
 	 * only set when UseScramPassthrough is enabled.
 	 */
-	if (MyProcPort->has_scram_keys && pgfdw_has_required_scram_options(keywords, values))
+	if (MyProcPort != NULL && MyProcPort->has_scram_keys && pgfdw_has_required_scram_options(keywords, values))
 		return;
 
 	ereport(ERROR,
@@ -2557,7 +2557,7 @@ pgfdw_has_required_scram_options(const char **keywords, const char **values)
 		}
 	}
 
-	has_scram_keys = has_scram_client_key && has_scram_server_key && MyProcPort->has_scram_keys;
+	has_scram_keys = has_scram_client_key && has_scram_server_key && MyProcPort != NULL && MyProcPort->has_scram_keys;
 
 	return (has_scram_keys && has_require_auth);
 }
-- 
2.43.0

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)

From d466c99bbe9bd87db57a4d3da062d812515b898c Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
Date: Mon, 11 Aug 2025 17:38:20 -0300
Subject: [PATCH v2] docs: add note of fdw connections using SCRAM

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

diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 781a01067f7..ef052101abd 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -834,6 +834,13 @@ OPTIONS (ADD password_required 'false');
          </listitem>
         </itemizedlist>
        </para>
+
+      <para>
+       If the incoming connection to the FDW instance does not use SCRAM,
+       <literal>use_scram_passthrough</literal> is ignored and authentication
+       will instead use the password from the user mapping, if one is provided.
+      </para>
+
       </listitem>
      </varlistentry>
 
-- 
2.39.5 (Apple Git-154)