Hi, On Thu May 14, 2026 at 2:57 PM -03, Fujii Masao wrote: > use_scram_passthrough can be specified both on a foreign server and > on a user mapping. I found that when both are specified, currently > the server-level setting overrides the user mapping setting. Is this > precedence intentional? > > That seems inconsistent with the usual expectation that foreign server > options act as shared defaults, while user mapping options provide > per-user overrides. If the same option is specified in both places, > I would expect the user mapping setting to take precedence. Thoughts? > > If the current behavior is intentional, I'd like to document the precedence > when use_scram_passthrough is specified in both a foreign server and > a user mapping. Otherwise, I'd like to change postgres_fdw to prefer > the user mapping setting and update the documentation accordingly. >
I agree that that user mapping setting should take precedence. The current behavior seems not correct since we are not able to enable/disable the scram passthrough to a specific user mapping. dblink also implement the same behavior, so I think that it should also be changed as postgres_fdw. The attached patches implement this change for postgres_fdw and dblink. -- Matheus Alcantara EDB: https://www.enterprisedb.com
From 29709e3106bb00bedb69ef8ddfcfad5ad5f627a2 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Thu, 14 May 2026 17:00:13 -0300 Subject: [PATCH v1 1/2] postgres_fdw: 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 other connection options like sslcert and sslkey are handled, 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. Discussion: https://www.postgresql.org/message-id/CAHGQGwEJ8rZjmbOvCicyr4vbuLio082bNTde0WNoSWaWr9wVcg%40mail.gmail.com --- contrib/postgres_fdw/connection.c | 4 ++-- contrib/postgres_fdw/t/001_auth_scram.pl | 30 ++++++++++++++++++++++++ doc/src/sgml/postgres-fdw.sgml | 4 +++- 3 files changed, 35 insertions(+), 3 deletions(-) diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c index 3d2a8d0519d..1a6311de41b 100644 --- a/contrib/postgres_fdw/connection.c +++ b/contrib/postgres_fdw/connection.c @@ -720,7 +720,7 @@ UseScramPassthrough(ForeignServer *server, UserMapping *user) { ListCell *cell; - foreach(cell, server->options) + foreach(cell, user->options) { DefElem *def = (DefElem *) lfirst(cell); @@ -728,7 +728,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 -- 2.53.0
From 7bbb36ef312f1d5de780268b3ccb16e50e98d2ce Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Thu, 14 May 2026 17:08:24 -0300 Subject: [PATCH v1 2/2] 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. Discussion: https://www.postgresql.org/message-id/CAHGQGwEJ8rZjmbOvCicyr4vbuLio082bNTde0WNoSWaWr9wVcg%40mail.gmail.com --- contrib/dblink/dblink.c | 4 ++-- contrib/dblink/t/001_auth_scram.pl | 24 ++++++++++++++++++++++++ doc/src/sgml/dblink.sgml | 10 ++++++---- 3 files changed, 32 insertions(+), 6 deletions(-) diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c index d843eee7e97..01894e7a556 100644 --- a/contrib/dblink/dblink.c +++ b/contrib/dblink/dblink.c @@ -3235,7 +3235,7 @@ UseScramPassthrough(ForeignServer *foreign_server, UserMapping *user) { ListCell *cell; - foreach(cell, foreign_server->options) + foreach(cell, user->options) { DefElem *def = lfirst(cell); @@ -3243,7 +3243,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
