v3-0002-dblink-Allow-user-mapping-to-override-use_scram_p.patch

From 61c8f1331d83db7804a62f7856dc904723cd2baf Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
Date: Thu, 14 May 2026 17:08:24 -0300
Subject: [PATCH v3 2/3] dblink: Allow user mapping to override
 use_scram_passthrough

Previously, use_scram_passthrough was checked on the foreign server
options first, which meant that if set on the server, the user mapping
option would be ignored. This changes the precedence to check the user
mapping option first, allowing users to override the server-level
setting on a per-user basis.

This is consistent with how postgres_fdw handles this option and how
other connection options like sslcert and sslkey work, where user
mapping settings take precedence over server settings.

Also add a test case to verify that setting use_scram_passthrough=false
on a user mapping correctly disables SCRAM passthrough even when the
server has it enabled.

Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://www.postgresql.org/message-id/CAHGQGwEJ8rZjmbOvCicyr4vbuLio082bNTde0WNoSWaWr9wVcg%40mail.gmail.com
---
 contrib/dblink/dblink.c            | 10 ++++++++--
 contrib/dblink/t/001_auth_scram.pl | 24 ++++++++++++++++++++++++
 doc/src/sgml/dblink.sgml           | 10 ++++++----
 3 files changed, 38 insertions(+), 6 deletions(-)

diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c
index d843eee7e97..bb6fcae4974 100644
--- a/contrib/dblink/dblink.c
+++ b/contrib/dblink/dblink.c
@@ -3230,12 +3230,18 @@ appendSCRAMKeysInfo(StringInfo buf)
 }
 
 
+/*
+ * Return whether SCRAM pass-through is enabled.
+ *
+ * If use_scram_passthrough is specified in both the foreign server
+ * and the user mapping, the user mapping setting takes precedence.
+ */
 static bool
 UseScramPassthrough(ForeignServer *foreign_server, UserMapping *user)
 {
 	ListCell   *cell;
 
-	foreach(cell, foreign_server->options)
+	foreach(cell, user->options)
 	{
 		DefElem    *def = lfirst(cell);
 
@@ -3243,7 +3249,7 @@ UseScramPassthrough(ForeignServer *foreign_server, UserMapping *user)
 			return defGetBoolean(def);
 	}
 
-	foreach(cell, user->options)
+	foreach(cell, foreign_server->options)
 	{
 		DefElem    *def = (DefElem *) lfirst(cell);
 
diff --git a/contrib/dblink/t/001_auth_scram.pl b/contrib/dblink/t/001_auth_scram.pl
index 9558ca83b7c..b087b38e5a5 100644
--- a/contrib/dblink/t/001_auth_scram.pl
+++ b/contrib/dblink/t/001_auth_scram.pl
@@ -24,6 +24,7 @@ my $db1 = "db1";                               # For node1
 my $db2 = "db2";                               # For node2
 my $fdw_server = "db1_fdw";
 my $fdw_server2 = "db2_fdw";
+my $fdw_server3 = "db1_fdw_override";
 my $fdw_invalid_server = "db2_fdw_invalid";    # For invalid fdw options
 my $fdw_invalid_server2 =
   "db2_fdw_invalid2";    # For invalid scram keys fdw options
@@ -55,10 +56,12 @@ setup_fdw_server($node1, $db0, $fdw_server, $node1, $db1);
 setup_fdw_server($node1, $db0, $fdw_server2, $node2, $db2);
 setup_invalid_fdw_server($node1, $db0, $fdw_invalid_server, $node2, $db2);
 setup_fdw_server($node1, $db0, $fdw_invalid_server2, $node2, $db2);
+setup_fdw_server($node1, $db0, $fdw_server3, $node1, $db1);
 
 setup_user_mapping($node1, $db0, $fdw_server);
 setup_user_mapping($node1, $db0, $fdw_server2);
 setup_user_mapping($node1, $db0, $fdw_invalid_server);
+setup_user_mapping($node1, $db0, $fdw_server3);
 
 # Make the user have the same SCRAM key on both servers. Forcing to have the
 # same iteration and salt.
@@ -96,6 +99,27 @@ test_fdw_auth($node1, $db0, "t2", $fdw_server2,
 
 test_fdw_auth_with_invalid_overwritten_require_auth($fdw_invalid_server);
 
+# Test that use_scram_passthrough=false on user mapping overrides server setting
+{
+	my $connstr = $node1->connstr($db0) . qq' user=$user';
+
+	$node1->safe_psql($db0,
+		qq'ALTER USER MAPPING FOR $user SERVER $fdw_server3 OPTIONS(add use_scram_passthrough \'false\')',
+		connstr => $connstr
+	);
+
+	my ($ret, $stdout, $stderr) = $node1->psql(
+		$db0,
+		"select * from dblink('$fdw_server3', 'select * from t') as t(a int, b int)",
+		connstr => $connstr);
+
+	is($ret, 3, 'SCRAM passthrough disabled on user mapping should fail');
+	like(
+		$stderr,
+		qr/password/i,
+		'expected password-related error when scram passthrough disabled on user mapping');
+}
+
 # Ensure that trust connections fail without superuser opt-in.
 unlink($node1->data_dir . '/pg_hba.conf');
 unlink($node2->data_dir . '/pg_hba.conf');
diff --git a/doc/src/sgml/dblink.sgml b/doc/src/sgml/dblink.sgml
index dd6778d22a8..fc496b74288 100644
--- a/doc/src/sgml/dblink.sgml
+++ b/doc/src/sgml/dblink.sgml
@@ -154,10 +154,12 @@ dblink_connect(text connname, text connstr) returns text
     The foreign-data wrapper <filename>dblink_fdw</filename> has an additional
     Boolean option <literal>use_scram_passthrough</literal> that controls
     whether <filename>dblink</filename> will use the SCRAM pass-through
-    authentication to connect to the remote database.  With SCRAM pass-through
-    authentication, <filename>dblink</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.
+    authentication to connect to the remote database.  It can be specified
+    for a foreign server or a user mapping.  A user mapping setting overrides
+    the foreign server setting.  With SCRAM pass-through authentication,
+    <filename>dblink</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.
     See the documentation of the equivalent <link
     linkend="postgres-fdw-option-use-scram-passthrough"><literal>use_scram_passthrough</literal></link>
     option of postgres_fdw for further details and restrictions.
-- 
2.53.0