postgres.git / summary / log / commit / refs
commit 97f6fc10fff0e42d08d78ce7f2a5b116e7f8d9b2
Author: Fujii Masao <fujii@postgresql.org>
Date: Mon May 25 15:46:31 2026 +0000
postgres_fdw: Give user mapping precedence for use_scram_passthrough
Previously, when use_scram_passthrough was specified on both a foreign server
and a user mapping, the server-level setting took precedence over the
user-mapping setting. This was inconsistent with the usual semantics of
postgres_fdw options, where foreign server options provide shared defaults
and user mapping options override them on a per-user basis.
This commit updates postgres_fdw so that the user-mapping setting takes
precedence when use_scram_passthrough is specified in both places. This
matches the behavior of other connection options such as sslcert and sslkey.
Backpatch to v18, where use_scram_passthrough was introduced. In v18,
this only affects limited configurations that specify conflicting values
at both the foreign server and user-mapping levels. In such cases, users
would naturally expect the user-mapping setting to override the server-level
setting, so changing the behavior should be minimally disruptive.
Also keeping v18 as the only branch with different semantics for
use_scram_passthrough would be unnecessarily confusing, so backpatch
this fix to v18.
Author: Matheus Alcantara <matheusssilv97@gmail.com>
Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwEJ8rZjmbOvCicyr4vbuLio082bNTde0WNoSWaWr9wVcg@mail.gmail.com
Backpatch-through: 18
contrib/postgres_fdw/connection.c | 10 ++++++++--
contrib/postgres_fdw/t/001_auth_scram.pl | 30 ++++++++++++++++++++++++++++++
doc/src/sgml/postgres-fdw.sgml | 4 +++-
3 files changed, 41 insertions(+), 3 deletions(-)
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index c3a1c5f46ca..346f6f1fbfe 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -716,12 +716,18 @@ UserMappingPasswordRequired(UserMapping *user)
return true;
}
+/*
+ * 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 *server, UserMapping *user)
{
ListCell *cell;
- foreach(cell, server->options)
+ foreach(cell, user->options)
{
DefElem *def = (DefElem *) lfirst(cell);
@@ -729,7 +735,7 @@ UseScramPassthrough(ForeignServer *server, UserMapping *user)
return defGetBoolean(def);
}
- foreach(cell, user->options)
+ foreach(cell, server->options)
{
DefElem *def = (DefElem *) lfirst(cell);
diff --git a/contrib/postgres_fdw/t/001_auth_scram.pl b/contrib/postgres_fdw/t/001_auth_scram.pl
index 6c18db4f2c8..c4b57cd81b3 100644
--- a/contrib/postgres_fdw/t/001_auth_scram.pl
+++ b/contrib/postgres_fdw/t/001_auth_scram.pl
@@ -20,6 +20,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 $node1 = PostgreSQL::Test::Cluster->new('node1');
my $node2 = PostgreSQL::Test::Cluster->new('node2');
@@ -46,9 +47,11 @@ 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_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_server3);
# Make the user have the same SCRAM key on both servers. Forcing to have the
# same iteration and salt.
@@ -68,6 +71,33 @@ test_fdw_auth($node1, $db0, "t2", $fdw_server2,
test_auth($node2, $db2, "t2",
"SCRAM auth directly on foreign server should still succeed");
+# 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
+ );
+
+ $node1->safe_psql(
+ $db0,
+ qq'CREATE FOREIGN TABLE override_t (g int, col2 int) SERVER $fdw_server3 OPTIONS (table_name \'t\');',
+ connstr => $connstr );
+ $node1->safe_psql($db0, qq'GRANT SELECT ON override_t TO $user;', connstr => $connstr);
+
+ my ($ret, $stdout, $stderr) = $node1->psql(
+ $db0,
+ qq'SELECT count(1) FROM override_t',
+ 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');
+}
+
SKIP:
{
skip "test requires Unix-domain sockets", 4 if !$use_unix_sockets;
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index b81f33732fb..b9e1b04463e 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -803,7 +803,9 @@ OPTIONS (ADD password_required 'false');
<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,
+ server. 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>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