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

1 files changed, 151 insertions, 0 deletions

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();