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