On Fri May 15, 2026 at 1:29 AM -03, Fujii Masao wrote: >> The attached patches implement this change for postgres_fdw and dblink. > > Thanks for the patches! They look good to me. > > One minor comment: in UseScramPassthrough() in postgres_fdw/connection.c > and dblink.c, how about adding a comment like the following to clarify > the option precedence? > > /* > * 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. > */ >
Sounds good, fixed. > Also, while reviewing the use_scram_passthrough handling in dblink, > I found a separate issue: the option can be set via > ALTER FOREIGN DATA WRAPPER dblink_fdw, but that setting does not > seem to have any effect. Is this a bug? Like postgres_fdw, also in dblink, > we should disallow the option to be set at the foreign-data-wrapper level? > If yes, I think it would be better to address that as a separate patch. > Thought? > Yeah, good catch, this is from 3642df265d0. The problem is that dblink_fdw_validator() was changed to call is_valid_dblink_fdw_option() instead of is_valid_dblink_option() to make CRETE SERVER ... OPTIONS (use_scram_passthrough '...'); works but I miss the fact that it's also used by ALTER FOREIGN DATA WRAPPER. The new attached 0003 fix this by only checking the fdw options when the validator context is from foreign server or user mapping. Are you considering backporting these patches? I think that 0003 is good, not sure about 0001 and 0002. -- Matheus Alcantara EDB: https://www.enterprisedb.com
From 1340720aed1fd3ff45ef519f241d370b7b516718 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Thu, 14 May 2026 17:00:13 -0300 Subject: [PATCH v2 1/3] 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 | 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 3d2a8d0519d..d71769296b8 100644 --- a/contrib/postgres_fdw/connection.c +++ b/contrib/postgres_fdw/connection.c @@ -715,12 +715,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); @@ -728,7 +734,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 852004780cb4d59b45e2326c93b0832cd940acc7 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Thu, 14 May 2026 17:08:24 -0300 Subject: [PATCH v2 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. 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
From b49398cf84fe4f4c6e013aa4bd8786acb285f41e Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Fri, 15 May 2026 12:38:48 -0300 Subject: [PATCH v2 3/3] dblink: Reject use_scram_passthrough option on foreign data wrapper The use_scram_passthrough option only makes sense for foreign server and user mapping contexts, as it controls authentication behavior for specific connections. Previously, this option was incorrectly accepted when set via ALTER FOREIGN DATA WRAPPER OPTIONS, even though it had no effect at that level. Restrict the option validation to only accept use_scram_passthrough when the context is ForeignServerRelationId or UserMappingRelationId. Discussion: https://www.postgresql.org/message-id/CAHGQGwEJ8rZjmbOvCicyr4vbuLio082bNTde0WNoSWaWr9wVcg%40mail.gmail.com --- contrib/dblink/dblink.c | 8 ++++++-- contrib/dblink/t/001_auth_scram.pl | 16 ++++++++++++++++ 2 files changed, 22 insertions(+), 2 deletions(-) diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c index bb6fcae4974..451c3208afe 100644 --- a/contrib/dblink/dblink.c +++ b/contrib/dblink/dblink.c @@ -3115,8 +3115,12 @@ static bool is_valid_dblink_fdw_option(const PQconninfoOption *options, const char *option, Oid context) { - if (strcmp(option, "use_scram_passthrough") == 0) - return true; + /* These options are only valid for foreign server or user mapping contexts */ + if (context == ForeignServerRelationId || context == UserMappingRelationId) + { + if (strcmp(option, "use_scram_passthrough") == 0) + return true; + } return is_valid_dblink_option(options, option, context); } diff --git a/contrib/dblink/t/001_auth_scram.pl b/contrib/dblink/t/001_auth_scram.pl index b087b38e5a5..c4ecdac19f2 100644 --- a/contrib/dblink/t/001_auth_scram.pl +++ b/contrib/dblink/t/001_auth_scram.pl @@ -18,6 +18,7 @@ if (!$use_unix_sockets) } my $user = "user01"; +my $admin = "admin"; my $db0 = "db0"; # For node1 my $db1 = "db1"; # For node1 @@ -41,6 +42,7 @@ $node2->start; # Test setup $node1->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\''); +$node1->safe_psql('postgres', qq'CREATE USER $admin WITH password \'pass\' SUPERUSER'); $node2->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\''); $ENV{PGPASSWORD} = "pass"; @@ -89,6 +91,20 @@ $node2->restart; # End of test setup +# Test that adding use_scram_passthrough option on an foreign data wrapper is invalid +{ + my $connstr = $node1->connstr($db0) . qq' user=$admin'; + + my ($ret, $stdout, $stderr ) = $node1->psql($db0, + 'ALTER FOREIGN DATA WRAPPER dblink_fdw OPTIONS (add use_scram_passthrough \'true\')', + connstr => $connstr); + is($ret, 3, 'ALTER FOREIGN DATA WRAPPER should not be allowed'); + like( + $stderr, + qr\invalid option "use_scram_passthrough"\i, + 'expected ALTER FOREIGN DATA WRAPPER to fail'); +} + test_scram_keys_is_not_overwritten($node1, $db0, $fdw_invalid_server2); test_fdw_auth($node1, $db0, "t", $fdw_server, -- 2.53.0
