postgres_fdw: SCRAM authentication pass-through

This enables SCRAM authentication for postgres_fdw when connecting to
a foreign 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.  The new foreign-server
or user-mapping option "use_scram_passthrough" enables this.

Co-authored-by: Matheus Alcantara <mths.dev@pm.me>
Co-authored-by: Peter Eisentraut <peter@eisentraut.org>
Discussion: https://www.postgresql.org/message-id/flat/27b29a35-9b96-46a9-bc1a-914140869dac@gmail.com

6 files changed, 225 insertions, 8 deletions

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..0274d6c253d 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..047840cc914
--- /dev/null
+++ b/contrib/postgres_fdw/t/001_auth_scram.pl
@@ -0,0 +1,151 @@
+# 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();