On Fri, Mar 7, 2025 at 8:22 AM Peter Eisentraut <pe...@eisentraut.org> wrote:
> Right.  How about the attached?  It checks as an alternative to a
> password whether the SCRAM keys were provided.  That should get us back
> to the same level of checking?

Yes, I think so. Attached is a set of tests to illustrate, mirroring
the dblink tests added upthread; they fail without this patch.

I like that this solution addresses some of the concerns from my dblink review.

--

Not part of this patchset, but I think the errmsg in
pgfdw_security_check() is confusing:

    ERROR: password or GSSAPI delegated credentials required
    DETAIL: Non-superuser cannot connect if the server does not
request a password or...
    HINT: Target server's authentication method must be changed or...

For the user to have gotten past check_conn_params, they *have*
provided a password/credentials. But the server didn't ask for it (or
at least, not the right one). The detail and hint messages are correct
here, but I'd argue the error message itself is not.

Thanks!
--Jacob
diff --git a/contrib/postgres_fdw/t/001_auth_scram.pl 
b/contrib/postgres_fdw/t/001_auth_scram.pl
index 047840cc914..60d46ebc665 100644
--- a/contrib/postgres_fdw/t/001_auth_scram.pl
+++ b/contrib/postgres_fdw/t/001_auth_scram.pl
@@ -68,6 +68,45 @@ test_fdw_auth($node1, $db0, "t2", $fdw_server2,
 test_auth($node2, $db2, "t2",
        "SCRAM auth directly on foreign server should still succeed");
 
+# Ensure that trust connections fail without superuser opt-in.
+unlink($node1->data_dir . '/pg_hba.conf');
+unlink($node2->data_dir . '/pg_hba.conf');
+
+$node1->append_conf(
+       'pg_hba.conf', qq{
+local   db0             all                                     scram-sha-256
+local   db1             all                                     trust
+});
+$node2->append_conf(
+       'pg_hba.conf', qq{
+local   db2             all                                     trust
+});
+
+$node1->restart;
+$node2->restart;
+
+my ($ret, $stdout, $stderr) = $node1->psql(
+       $db0,
+       "SELECT count(1) FROM t",
+       connstr => $node1->connstr($db0) . " user=$user");
+
+is($ret, 3, 'loopback trust fails on the same cluster');
+like(
+       $stderr,
+       qr/password or GSSAPI delegated credentials required/,
+       'expected error from loopback trust (same cluster)');
+
+($ret, $stdout, $stderr) = $node1->psql(
+       $db0,
+       "SELECT count(1) FROM t2",
+       connstr => $node1->connstr($db0) . " user=$user");
+
+is($ret, 3, 'loopback trust fails on a different cluster');
+like(
+       $stderr,
+       qr/password or GSSAPI delegated credentials required/,
+       'expected error from loopback trust (different cluster)');
+
 # Helper functions
 
 sub test_auth

Reply via email to