Re: dblink: Add SCRAM pass-through authentication

[Jacob Champion]

On Mon, Mar 10, 2025 at 11:25 AM Jacob Champion
<jacob.champ...@enterprisedb.com> wrote:
>
> 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.

In an offline discussion with Peter and Matheus, we figured out that
this is still not enough. The latest patch checks that a password was
used, but it doesn't ensure that the password material came from the
SCRAM keys. Attached is an updated test to illustrate.

Thanks,
--Jacob

commit 4a41754eaa41f2db285e68ff8140d6932c299358
Author: Jacob Champion <jacob.champ...@enterprisedb.com>
Date:   Mon Mar 10 11:18:27 2025 -0700

    WIP

diff --git a/contrib/postgres_fdw/t/001_auth_scram.pl 
b/contrib/postgres_fdw/t/001_auth_scram.pl
index 047840cc914..464492948b4 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   all             all                                     password
+});
+
+$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 password fails on a different cluster');
+like(
+       $stderr,
+       qr/password or GSSAPI delegated credentials required/,
+       'expected error from loopback password (different cluster)');
+
 # Helper functions
 
 sub test_auth