On 09.01.25 16:22, Matheus Alcantara wrote:
Yeah, I also think that makes sense.
I've made all changes on the attached v2.
(This should probably have been v3, since you had already sent a v2
earlier.)
This all looks good to me.
Attached is a fixup patch where I have tried to expand the documentation
a bit in an attempt to clarify how to use this. Maybe check that what I
wrote is correct.
From 8c1ed745da53a299a92015c54e3f49739ffec005 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths....@pm.me>
Date: Tue, 19 Nov 2024 15:37:57 -0300
Subject: [PATCH v2.1 1/2] postgres_fdw: SCRAM authentication pass-through
This commit enable SCRAM authentication for postgres_fdw when connecting
to a fdw server without having to store a plain-text password on user
mapping options.
This is done by saving the SCRAM ClientKey and ServeryKey from the
client authentication and using those instead of the plain-text password
for the server-side SCRAM exchange.
---
contrib/postgres_fdw/Makefile | 1 +
contrib/postgres_fdw/connection.c | 69 ++++++++-
.../postgres_fdw/expected/postgres_fdw.out | 4 +-
contrib/postgres_fdw/meson.build | 5 +
contrib/postgres_fdw/option.c | 3 +
contrib/postgres_fdw/t/001_auth_scram.pl | 137 ++++++++++++++++++
doc/src/sgml/libpq.sgml | 28 ++++
doc/src/sgml/postgres-fdw.sgml | 19 +++
src/backend/libpq/auth-scram.c | 14 +-
src/include/libpq/libpq-be.h | 9 ++
src/interfaces/libpq/fe-auth-scram.c | 29 +++-
src/interfaces/libpq/fe-auth.c | 2 +-
src/interfaces/libpq/fe-connect.c | 47 ++++++
src/interfaces/libpq/libpq-int.h | 6 +
14 files changed, 357 insertions(+), 16 deletions(-)
create mode 100644 contrib/postgres_fdw/t/001_auth_scram.pl
diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index 88fdce40d6a..adfbd2ef758 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -17,6 +17,7 @@ EXTENSION = postgres_fdw
DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql
postgres_fdw--1.1--1.2.sql
REGRESS = postgres_fdw query_cancel
+TAP_TESTS = 1
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/postgres_fdw/connection.c
b/contrib/postgres_fdw/connection.c
index 202e7e583b3..06ec6201bb5 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -19,6 +19,7 @@
#include "access/xact.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "common/base64.h"
#include "funcapi.h"
#include "libpq/libpq-be.h"
#include "libpq/libpq-be-fe-helpers.h"
@@ -177,6 +178,7 @@ static void pgfdw_finish_abort_cleanup(List
*pending_entries,
static void pgfdw_security_check(const char **keywords, const char **values,
UserMapping
*user, PGconn *conn);
static bool UserMappingPasswordRequired(UserMapping *user);
+static bool UseScramPassthrough(ForeignServer *server, UserMapping *user);
static bool disconnect_cached_connections(Oid serverid);
static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
enum pgfdwVersion api_version);
@@ -485,7 +487,7 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
* for application_name, fallback_application_name,
client_encoding,
* end marker.
*/
- n = list_length(server->options) + list_length(user->options) +
4;
+ n = list_length(server->options) + list_length(user->options) +
4 + 2;
keywords = (const char **) palloc(n * sizeof(char *));
values = (const char **) palloc(n * sizeof(char *));
@@ -554,10 +556,37 @@ connect_pg_server(ForeignServer *server, UserMapping
*user)
values[n] = GetDatabaseEncodingName();
n++;
+ if (MyProcPort->has_scram_keys && UseScramPassthrough(server,
user))
+ {
+ int len;
+
+ keywords[n] = "scram_client_key";
+ len =
pg_b64_enc_len(sizeof(MyProcPort->scram_ClientKey));
+ /* don't forget the zero-terminator */
+ values[n] = palloc0(len+1);
+ pg_b64_encode((const char *)
MyProcPort->scram_ClientKey,
+
sizeof(MyProcPort->scram_ClientKey),
+ (char *) values[n], len);
+ n++;
+
+ keywords[n] = "scram_server_key";
+ len =
pg_b64_enc_len(sizeof(MyProcPort->scram_ServerKey));
+ /* don't forget the zero-terminator */
+ values[n] = palloc0(len+1);
+ pg_b64_encode((const char *)
MyProcPort->scram_ServerKey,
+
sizeof(MyProcPort->scram_ServerKey),
+ (char *) values[n], len);
+ n++;
+ }
+
keywords[n] = values[n] = NULL;
- /* verify the set of connection parameters */
- check_conn_params(keywords, values, user);
+ /*
+ * Verify the set of connection parameters only if scram
pass-through
+ * is not being used because the password is not necessary.
+ */
+ if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server,
user)))
+ check_conn_params(keywords, values, user);
/* first time, allocate or get the custom wait event */
if (pgfdw_we_connect == 0)
@@ -575,8 +604,12 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
server->servername),
errdetail_internal("%s",
pchomp(PQerrorMessage(conn)))));
- /* Perform post-connection security checks */
- pgfdw_security_check(keywords, values, user, conn);
+ /*
+ * Perform post-connection security checks only if scram
pass-through
+ * is not being used because the password is not necessary.
+ */
+ if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server,
user)))
+ pgfdw_security_check(keywords, values, user, conn);
/* Prepare new session for use */
configure_remote_session(conn);
@@ -629,6 +662,30 @@ UserMappingPasswordRequired(UserMapping *user)
return true;
}
+static bool
+UseScramPassthrough(ForeignServer *server, UserMapping *user)
+{
+ ListCell *cell;
+
+ foreach(cell, server->options)
+ {
+ DefElem *def = (DefElem *) lfirst(cell);
+
+ if (strcmp(def->defname, "use_scram_passthrough") == 0)
+ return defGetBoolean(def);
+ }
+
+ foreach(cell, user->options)
+ {
+ DefElem *def = (DefElem *) lfirst(cell);
+
+ if (strcmp(def->defname, "use_scram_passthrough") == 0)
+ return defGetBoolean(def);
+ }
+
+ return false;
+}
+
/*
* For non-superusers, insist that the connstr specify a password or that the
* user provided their own GSSAPI delegated credentials. This
@@ -666,7 +723,7 @@ check_conn_params(const char **keywords, const char
**values, UserMapping *user)
ereport(ERROR,
(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
errmsg("password or GSSAPI delegated credentials
required"),
- errdetail("Non-superusers must delegate GSSAPI
credentials or provide a password in the user mapping.")));
+ errdetail("Non-superusers must delegate GSSAPI
credentials, provide a password, or enable SCRAM pass-through in user
mapping.")));
}
/*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index bf322198a20..64aa12ecc48 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10301,7 +10301,7 @@ CREATE FOREIGN TABLE pg_temp.ft1_nopw (
) SERVER loopback_nopw OPTIONS (schema_name 'public', table_name 'ft1');
SELECT 1 FROM ft1_nopw LIMIT 1;
ERROR: password or GSSAPI delegated credentials required
-DETAIL: Non-superusers must delegate GSSAPI credentials or provide a password
in the user mapping.
+DETAIL: Non-superusers must delegate GSSAPI credentials, provide a password,
or enable SCRAM pass-through in user mapping.
-- If we add a password to the connstr it'll fail, because we don't allow
passwords
-- in connstrs only in user mappings.
ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw');
@@ -10351,7 +10351,7 @@ DROP USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
-- lacks password_required=false
SELECT 1 FROM ft1_nopw LIMIT 1;
ERROR: password or GSSAPI delegated credentials required
-DETAIL: Non-superusers must delegate GSSAPI credentials or provide a password
in the user mapping.
+DETAIL: Non-superusers must delegate GSSAPI credentials, provide a password,
or enable SCRAM pass-through in user mapping.
RESET ROLE;
-- The user mapping for public is passwordless and lacks the
password_required=false
-- mapping option, but will work because the current user is a superuser.
diff --git a/contrib/postgres_fdw/meson.build b/contrib/postgres_fdw/meson.build
index 3f19981cffc..8b29be24dee 100644
--- a/contrib/postgres_fdw/meson.build
+++ b/contrib/postgres_fdw/meson.build
@@ -41,4 +41,9 @@ tests += {
],
'regress_args': ['--dlpath', meson.build_root() / 'src/test/regress'],
},
+ 'tap': {
+ 'tests': [
+ 't/001_auth_scram.pl',
+ ],
+ },
}
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 12aed4054fa..d0766f007d2 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -279,6 +279,9 @@ InitPgFdwOptions(void)
{"analyze_sampling", ForeignServerRelationId, false},
{"analyze_sampling", ForeignTableRelationId, false},
+ {"use_scram_passthrough", ForeignServerRelationId, false},
+ {"use_scram_passthrough", UserMappingRelationId, false},
+
/*
* sslcert and sslkey are in fact libpq options, but we repeat
them
* here to allow them to appear in both foreign server context
(when
diff --git a/contrib/postgres_fdw/t/001_auth_scram.pl
b/contrib/postgres_fdw/t/001_auth_scram.pl
new file mode 100644
index 00000000000..d5be6fc35c5
--- /dev/null
+++ b/contrib/postgres_fdw/t/001_auth_scram.pl
@@ -0,0 +1,137 @@
+# Copyright (c) 2024-2025, PostgreSQL Global Development Group
+
+# Test SCRAM authentication when opening a new connection with a foreign
+# server.
+#
+# The test is executed by testing the SCRAM authentifcation on a looplback
+# connection on the same server and with different servers.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Utils;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+my $hostaddr = '127.0.0.1';
+my $user = "user01";
+
+my $db0 = "db0"; # For node1
+my $db1 = "db1"; # For node1
+my $db2 = "db2"; # For node2
+my $fdw_server = "db1_fdw";
+my $fdw_server2 = "db2_fdw";
+
+my $node1 = PostgreSQL::Test::Cluster->new('node1');
+my $node2 = PostgreSQL::Test::Cluster->new('node2');
+
+$node1->init;
+$node2->init;
+
+$node1->start;
+$node2->start;
+
+# Test setup
+
+$node1->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$node2->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
+$ENV{PGPASSWORD} = "pass";
+
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db0');
+$node1->safe_psql('postgres', qq'CREATE DATABASE $db1');
+$node2->safe_psql('postgres', qq'CREATE DATABASE $db2');
+
+setup_table($node1, $db1, "t");
+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_user_mapping($node1, $db0, $fdw_server);
+setup_user_mapping($node1, $db0, $fdw_server2);
+
+# Make the user have the same SCRAM key on both servers. Forcing to have the
+# same iteration and salt.
+my $rolpassword = $node1->safe_psql('postgres', qq"SELECT rolpassword FROM
pg_authid WHERE rolname = '$user';");
+$node2->safe_psql('postgres', qq"ALTER ROLE $user PASSWORD '$rolpassword'");
+
+setup_pghba($node1);
+setup_pghba($node2);
+
+# End of test setup
+
+test_fdw_auth($node1, $db0, "t", $fdw_server, "SCRAM auth on the same database
cluster must succeed");
+test_fdw_auth($node1, $db0, "t2", $fdw_server2, "SCRAM auth on a different
database cluster must succeed");
+test_auth($node2, $db2, "t2", "SCRAM auth directly on foreign server should
still succeed");
+
+# Helper functions
+
+sub test_auth
+{
+ local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+ my ($node, $db, $tbl, $testname) = @_;
+ my $connstr = $node->connstr($db) . qq' user=$user';
+
+ my $ret = $node->safe_psql($db, qq'SELECT count(1) FROM $tbl',
+ connstr=>$connstr);
+
+ is($ret, '10', $testname);
+}
+
+sub test_fdw_auth
+{
+ local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+ my ($node, $db, $tbl, $fdw, $testname) = @_;
+ my $connstr = $node->connstr($db) . qq' user=$user';
+
+ $node->safe_psql($db, qq'IMPORT FOREIGN SCHEMA public LIMIT TO($tbl)
FROM SERVER $fdw INTO public;',
+ connstr=>$connstr);
+
+ test_auth($node, $db, $tbl, $testname);
+}
+
+sub setup_pghba
+{
+ my ($node) = @_;
+
+ unlink($node->data_dir . '/pg_hba.conf');
+ $node->append_conf(
+ 'pg_hba.conf', qq{
+ local all all
scram-sha-256
+ host all all $hostaddr/32
scram-sha-256
+ });
+
+ $node->restart;
+}
+
+sub setup_user_mapping
+{
+ my ($node, $db, $fdw) = @_;
+
+ $node->safe_psql($db, qq'CREATE USER MAPPING FOR $user SERVER $fdw
OPTIONS (user \'$user\');');
+ $node->safe_psql($db, qq'GRANT USAGE ON FOREIGN SERVER $fdw to $user;');
+ $node->safe_psql($db, qq'GRANT ALL ON SCHEMA public to $user');
+}
+
+sub setup_fdw_server
+{
+ my ($node, $db, $fdw, $fdw_node, $dbname) = @_;
+ my $host = $fdw_node->host;
+ my $port = $fdw_node->port;
+
+ $node->safe_psql($db, qq'CREATE SERVER $fdw FOREIGN DATA WRAPPER
postgres_fdw options (
+ host \'$host\', port \'$port\', dbname \'$dbname\',
use_scram_passthrough \'true\') ');
+}
+
+sub setup_table
+{
+ my ($node, $db, $tbl) = @_;
+
+ $node->safe_psql($db, qq'CREATE TABLE $tbl AS SELECT g,g+1 FROM
generate_series(1,10) g(g)');
+ $node->safe_psql($db, qq'GRANT USAGE ON SCHEMA public to $user');
+ $node->safe_psql($db, qq'GRANT SELECT ON $tbl to $user');
+}
+
+done_testing();
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 105b22b3171..090b9832899 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2199,6 +2199,34 @@ <title>Parameter Key Words</title>
</listitem>
</varlistentry>
+ <varlistentry id="libpq-connect-scram-client-key"
xreflabel="scram_client_key">
+ <term><literal>scram_client_key</literal></term>
+ <listitem>
+ <para>
+ The SCRAM client key is used by FDW extensions to enable pass-through
+ SCRAM authentication. When <option>use_scram_passthrough</option> is
+ set to <literal>true</literal> and this parameter is specified, the
+ backend uses the provided key as the SCRAM client key during
+ authentication with the FDW server. See <xref
+ linkend="postgres-fdw-options-connection-management"/>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="libpq-connect-scram-server-key"
xreflabel="scram_server_key">
+ <term><literal>scram_server_key</literal></term>
+ <listitem>
+ <para>
+ The SCRAM server key is used by FDW extensions to enable pass-through
+ SCRAM authentication. When <option>use_scram_passthrough</option> is
+ set to <literal>true</literal> and this parameter is specified, the
+ backend uses the provided key as the SCRAM server key during
+ authentication with the FDW server. See <xref
+ linkend="postgres-fdw-options-connection-management"/>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="libpq-connect-service" xreflabel="service">
<term><literal>service</literal></term>
<listitem>
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 188e8f0b4d0..da04e14a044 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -770,6 +770,25 @@ <title>Connection Management Options</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>use_scram_passthrough</literal>
(<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ This option controls whether <filename>postgres_fdw</filename> will use
+ the SCRAM password authentication to connect into the foreign server.
+ SCRAM secrets can only be used for logging into the foreign server if
+ the client authentication also uses SCRAM.
+ </para>
+ <para>
+ SCRAM authentication into the foreign server can only be possible if
+ both servers have identical SCRAM secrets (encrypted password) for the
+ user being used on <filename>postgres_fdw</filename> to authenticate on
+ the foreign server, same salt and iterations, not merely the same
+ password.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect3>
</sect2>
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index 1514133acdc..26dd241efa9 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -101,6 +101,7 @@
#include "libpq/crypt.h"
#include "libpq/sasl.h"
#include "libpq/scram.h"
+#include "miscadmin.h"
static void scram_get_mechanisms(Port *port, StringInfo buf);
static void *scram_init(Port *port, const char *selected_mech,
@@ -144,6 +145,7 @@ typedef struct
int iterations;
char *salt; /* base64-encoded */
+ uint8 ClientKey[SCRAM_MAX_KEY_LEN];
uint8 StoredKey[SCRAM_MAX_KEY_LEN];
uint8 ServerKey[SCRAM_MAX_KEY_LEN];
@@ -462,6 +464,13 @@ scram_exchange(void *opaq, const char *input, int inputlen,
if (*output)
*outputlen = strlen(*output);
+ if (result == PG_SASL_EXCHANGE_SUCCESS && state->state ==
SCRAM_AUTH_FINISHED)
+ {
+ memcpy(MyProcPort->scram_ClientKey, state->ClientKey,
sizeof(MyProcPort->scram_ClientKey));
+ memcpy(MyProcPort->scram_ServerKey, state->ServerKey,
sizeof(MyProcPort->scram_ServerKey));
+ MyProcPort->has_scram_keys = true;
+ }
+
return result;
}
@@ -1140,7 +1149,6 @@ static bool
verify_client_proof(scram_state *state)
{
uint8 ClientSignature[SCRAM_MAX_KEY_LEN];
- uint8 ClientKey[SCRAM_MAX_KEY_LEN];
uint8 client_StoredKey[SCRAM_MAX_KEY_LEN];
pg_hmac_ctx *ctx = pg_hmac_create(state->hash_type);
int i;
@@ -1173,10 +1181,10 @@ verify_client_proof(scram_state *state)
/* Extract the ClientKey that the client calculated from the proof */
for (i = 0; i < state->key_length; i++)
- ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
+ state->ClientKey[i] = state->ClientProof[i] ^
ClientSignature[i];
/* Hash it one more time, and compare with StoredKey */
- if (scram_H(ClientKey, state->hash_type, state->key_length,
+ if (scram_H(state->ClientKey, state->hash_type, state->key_length,
client_StoredKey, &errstr) < 0)
elog(ERROR, "could not hash stored key: %s", errstr);
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 16da6f89ef1..2f6c29200ba 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -18,6 +18,8 @@
#ifndef LIBPQ_BE_H
#define LIBPQ_BE_H
+#include "common/scram-common.h"
+
#include <sys/time.h>
#ifdef USE_OPENSSL
#include <openssl/ssl.h>
@@ -181,6 +183,13 @@ typedef struct Port
int keepalives_count;
int tcp_user_timeout;
+ /*
+ * SCRAM structures.
+ */
+ uint8 scram_ClientKey[SCRAM_MAX_KEY_LEN];
+ uint8 scram_ServerKey[SCRAM_MAX_KEY_LEN];
+ bool has_scram_keys; /* true if the above two are valid */
+
/*
* GSSAPI structures.
*/
diff --git a/src/interfaces/libpq/fe-auth-scram.c
b/src/interfaces/libpq/fe-auth-scram.c
index 59bf87d2213..dda43089121 100644
--- a/src/interfaces/libpq/fe-auth-scram.c
+++ b/src/interfaces/libpq/fe-auth-scram.c
@@ -119,6 +119,8 @@ scram_init(PGconn *conn,
return NULL;
}
+ if (password)
+ {
/* Normalize the password with SASLprep, if possible */
rc = pg_saslprep(password, &prep_password);
if (rc == SASLPREP_OOM)
@@ -138,6 +140,7 @@ scram_init(PGconn *conn,
}
}
state->password = prep_password;
+ }
return state;
}
@@ -775,6 +778,12 @@ calculate_client_proof(fe_scram_state *state,
return false;
}
+ if (state->conn->scram_client_key_binary)
+ {
+ memcpy(ClientKey, state->conn->scram_client_key_binary,
SCRAM_MAX_KEY_LEN);
+ }
+ else
+ {
/*
* Calculate SaltedPassword, and store it in 'state' so that we can
reuse
* it later in verify_server_signature.
@@ -783,15 +792,20 @@ calculate_client_proof(fe_scram_state *state,
state->key_length,
state->salt, state->saltlen,
state->iterations,
state->SaltedPassword,
errstr) < 0 ||
- scram_ClientKey(state->SaltedPassword, state->hash_type,
- state->key_length, ClientKey,
errstr) < 0 ||
- scram_H(ClientKey, state->hash_type, state->key_length,
- StoredKey, errstr) < 0)
+ scram_ClientKey(state->SaltedPassword, state->hash_type,
+ state->key_length, ClientKey,
errstr) < 0)
{
/* errstr is already filled here */
pg_hmac_free(ctx);
return false;
}
+ }
+
+ if (scram_H(ClientKey, state->hash_type, state->key_length, StoredKey,
errstr) < 0)
+ {
+ pg_hmac_free(ctx);
+ return false;
+ }
if (pg_hmac_init(ctx, StoredKey, state->key_length) < 0 ||
pg_hmac_update(ctx,
@@ -841,6 +855,12 @@ verify_server_signature(fe_scram_state *state, bool *match,
return false;
}
+ if (state->conn->scram_server_key_binary)
+ {
+ memcpy(ServerKey, state->conn->scram_server_key_binary,
SCRAM_MAX_KEY_LEN);
+ }
+ else
+ {
if (scram_ServerKey(state->SaltedPassword, state->hash_type,
state->key_length, ServerKey,
errstr) < 0)
{
@@ -848,6 +868,7 @@ verify_server_signature(fe_scram_state *state, bool *match,
pg_hmac_free(ctx);
return false;
}
+ }
/* calculate ServerSignature */
if (pg_hmac_init(ctx, ServerKey, state->key_length) < 0 ||
diff --git a/src/interfaces/libpq/fe-auth.c b/src/interfaces/libpq/fe-auth.c
index 14a9a862f51..7e478489b71 100644
--- a/src/interfaces/libpq/fe-auth.c
+++ b/src/interfaces/libpq/fe-auth.c
@@ -559,7 +559,7 @@ pg_SASL_init(PGconn *conn, int payloadlen)
* First, select the password to use for the exchange, complaining if
* there isn't one and the selected SASL mechanism needs it.
*/
- if (conn->password_needed)
+ if (conn->password_needed && !conn->scram_client_key_binary)
{
password = conn->connhost[conn->whichhost].password;
if (password == NULL)
diff --git a/src/interfaces/libpq/fe-connect.c
b/src/interfaces/libpq/fe-connect.c
index 8f211821eb2..ac5af8f5240 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -22,6 +22,7 @@
#include <time.h>
#include <unistd.h>
+#include "common/base64.h"
#include "common/ip.h"
#include "common/link-canary.h"
#include "common/scram-common.h"
@@ -366,6 +367,12 @@ static const internalPQconninfoOption PQconninfoOptions[]
= {
"Load-Balance-Hosts", "", 8, /* sizeof("disable") = 8 */
offsetof(struct pg_conn, load_balance_hosts)},
+ {"scram_client_key", NULL, NULL, NULL, "SCRAM-Client-Key", "D",
SCRAM_MAX_KEY_LEN * 2,
+ offsetof(struct pg_conn, scram_client_key)},
+
+ {"scram_server_key", NULL, NULL, NULL, "SCRAM-Server-Key", "D",
SCRAM_MAX_KEY_LEN * 2,
+ offsetof(struct pg_conn, scram_server_key)},
+
/* Terminating entry --- MUST BE LAST */
{NULL, NULL, NULL, NULL,
NULL, NULL, 0}
@@ -1793,6 +1800,44 @@ pqConnectOptions2(PGconn *conn)
else
conn->target_server_type = SERVER_TYPE_ANY;
+ if (conn->scram_client_key)
+ {
+ int len;
+
+ len = pg_b64_dec_len(strlen(conn->scram_client_key));
+ /* Consider the zero-terminator */
+ if (len != SCRAM_MAX_KEY_LEN+1)
+ {
+ libpq_append_conn_error(conn, "invalid scram client key
len: %d", len);
+ return false;
+ }
+ conn->scram_client_key_len = len;
+ conn->scram_client_key_binary = malloc(len);
+ if (!conn->scram_client_key_binary)
+ goto oom_error;
+ pg_b64_decode(conn->scram_client_key,
strlen(conn->scram_client_key),
+ conn->scram_client_key_binary, len);
+ }
+
+ if (conn->scram_server_key)
+ {
+ int len;
+
+ len = pg_b64_dec_len(strlen(conn->scram_server_key));
+ /* Consider the zero-terminator */
+ if (len != SCRAM_MAX_KEY_LEN+1)
+ {
+ libpq_append_conn_error(conn, "invalid scram server key
len: %d", len);
+ return false;
+ }
+ conn->scram_server_key_len = len;
+ conn->scram_server_key_binary = malloc(len);
+ if (!conn->scram_server_key_binary)
+ goto oom_error;
+ pg_b64_decode(conn->scram_server_key,
strlen(conn->scram_server_key),
+ conn->scram_server_key_binary, len);
+ }
+
/*
* validate load_balance_hosts option, and set load_balance_type
*/
@@ -4704,6 +4749,8 @@ freePGconn(PGconn *conn)
free(conn->rowBuf);
free(conn->target_session_attrs);
free(conn->load_balance_hosts);
+ free(conn->scram_client_key);
+ free(conn->scram_server_key);
termPQExpBuffer(&conn->errorMessage);
termPQExpBuffer(&conn->workBuffer);
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 4a5a7c8b5e3..b96630298eb 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -428,6 +428,8 @@ struct pg_conn
char *target_session_attrs; /* desired session properties */
char *require_auth; /* name of the expected auth method */
char *load_balance_hosts; /* load balance over hosts */
+ char *scram_client_key; /* base64 encoded scram client key */
+ char *scram_server_key; /* base64 encoded scram server key */
bool cancelRequest; /* true if this connection is used to
send a
* cancel
request, instead of being a normal
@@ -518,6 +520,10 @@ struct pg_conn
AddrInfo *addr; /* the array of addresses for
the currently
* tried host */
bool send_appname; /* okay to send application_name? */
+ size_t scram_client_key_len;
+ void *scram_client_key_binary; /*base64 decoded scram client key
*/
+ size_t scram_server_key_len;
+ void *scram_server_key_binary; /*base64 decoded scram server key
*/
/* Miscellaneous stuff */
int be_pid; /* PID of backend ---
needed for cancels */
--
2.47.1
From fa20ffb5b3aa7760975dd8dcdd9e981f6b96520f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 14 Jan 2025 10:14:15 +0100
Subject: [PATCH v2.1 2/2] fixup! postgres_fdw: SCRAM authentication
pass-through
---
doc/src/sgml/libpq.sgml | 24 +++++-----
doc/src/sgml/postgres-fdw.sgml | 77 ++++++++++++++++++++++++++-----
src/interfaces/libpq/fe-connect.c | 4 +-
src/interfaces/libpq/libpq-int.h | 8 ++--
4 files changed, 83 insertions(+), 30 deletions(-)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 090b9832899..e04acf1c208 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2203,12 +2203,12 @@ <title>Parameter Key Words</title>
<term><literal>scram_client_key</literal></term>
<listitem>
<para>
- The SCRAM client key is used by FDW extensions to enable pass-through
- SCRAM authentication. When <option>use_scram_passthrough</option> is
- set to <literal>true</literal> and this parameter is specified, the
- backend uses the provided key as the SCRAM client key during
- authentication with the FDW server. See <xref
- linkend="postgres-fdw-options-connection-management"/>
+ The base64-encoded SCRAM client key. This can be used by foreign-data
+ wrappers or similar middleware to enable pass-through SCRAM
+ authentication. See <xref
+ linkend="postgres-fdw-options-connection-management"/> for one such
+ implementation. It is not meant to be specified directly by users or
+ client applications.
</para>
</listitem>
</varlistentry>
@@ -2217,12 +2217,12 @@ <title>Parameter Key Words</title>
<term><literal>scram_server_key</literal></term>
<listitem>
<para>
- The SCRAM server key is used by FDW extensions to enable pass-through
- SCRAM authentication. When <option>use_scram_passthrough</option> is
- set to <literal>true</literal> and this parameter is specified, the
- backend uses the provided key as the SCRAM server key during
- authentication with the FDW server. See <xref
- linkend="postgres-fdw-options-connection-management"/>
+ The base64-encoded SCRAM server key. This can be used by foreign-data
+ wrappers or similar middleware to enable pass-through SCRAM
+ authentication. See <xref
+ linkend="postgres-fdw-options-connection-management"/> for one such
+ implementation. It is not meant to be specified directly by users or
+ client applications.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index da04e14a044..d2998c13d5d 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -774,18 +774,71 @@ <title>Connection Management Options</title>
<term><literal>use_scram_passthrough</literal>
(<type>boolean</type>)</term>
<listitem>
<para>
- This option controls whether <filename>postgres_fdw</filename> will use
- the SCRAM password authentication to connect into the foreign server.
- SCRAM secrets can only be used for logging into the foreign server if
- the client authentication also uses SCRAM.
- </para>
- <para>
- SCRAM authentication into the foreign server can only be possible if
- both servers have identical SCRAM secrets (encrypted password) for the
- user being used on <filename>postgres_fdw</filename> to authenticate on
- the foreign server, same salt and iterations, not merely the same
- password.
- </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,
+ <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
+ catalogs.
+ </para>
+
+ <para>
+ To use SCRAM pass-through authentication:
+ <itemizedlist>
+ <listitem>
+ <para>
+ The remote server must request SCRAM authentication. (If desired,
+ enforce this on the client side (FDW side) with the option
+ <literal>require_auth</literal>.) If another authentication method
+ is requested by the server, then that one will be used normally.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The remote server can be of any PostgreSQL version that supports
+ SCRAM. Support for <literal>use_scram_passthrough</literal> is
+ only required on the client side (FDW side).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The user mapping password is not used. (It could be set to support
+ other authentication methods, but that would arguably violate the
+ point of this feature, which is to avoid storing plain-text
+ passwords.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The server running <filename>postgres_fdw</filename> and the remote
+ server must have identical SCRAM secrets (encrypted passwords) for
+ the user being used on <filename>postgres_fdw</filename> to
+ authenticate on the foreign server (same salt and iterations, not
+ merely the same password).
+ </para>
+
+ <para>
+ As a corollary, if FDW connections to multiple hosts are to be
+ made, for example for partitioned foreign tables/sharding, then all
+ hosts must have identical SCRAM secrets for the users involved.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The current session on the PostgreSQL instance that makes the
+ outgoing FDW connections also must also use SCRAM authentication
+ for its incoming client connection. (Hence
+ <quote>pass-through</quote>: SCRAM must be used going in and out.)
+ This is a technical requirement of the SCRAM protocol.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
</listitem>
</varlistentry>
diff --git a/src/interfaces/libpq/fe-connect.c
b/src/interfaces/libpq/fe-connect.c
index ac5af8f5240..4931c77a24f 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -1808,7 +1808,7 @@ pqConnectOptions2(PGconn *conn)
/* Consider the zero-terminator */
if (len != SCRAM_MAX_KEY_LEN+1)
{
- libpq_append_conn_error(conn, "invalid scram client key
len: %d", len);
+ libpq_append_conn_error(conn, "invalid SCRAM client key
length: %d", len);
return false;
}
conn->scram_client_key_len = len;
@@ -1827,7 +1827,7 @@ pqConnectOptions2(PGconn *conn)
/* Consider the zero-terminator */
if (len != SCRAM_MAX_KEY_LEN+1)
{
- libpq_append_conn_error(conn, "invalid scram server key
len: %d", len);
+ libpq_append_conn_error(conn, "invalid SCRAM server key
length: %d", len);
return false;
}
conn->scram_server_key_len = len;
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index b96630298eb..1f105718678 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -428,8 +428,8 @@ struct pg_conn
char *target_session_attrs; /* desired session properties */
char *require_auth; /* name of the expected auth method */
char *load_balance_hosts; /* load balance over hosts */
- char *scram_client_key; /* base64 encoded scram client key */
- char *scram_server_key; /* base64 encoded scram server key */
+ char *scram_client_key; /* base64-encoded SCRAM client key */
+ char *scram_server_key; /* base64-encoded SCRAM server key */
bool cancelRequest; /* true if this connection is used to
send a
* cancel
request, instead of being a normal
@@ -521,9 +521,9 @@ struct pg_conn
* tried host */
bool send_appname; /* okay to send application_name? */
size_t scram_client_key_len;
- void *scram_client_key_binary; /*base64 decoded scram client key
*/
+ void *scram_client_key_binary; /* binary (decoded) SCRAM client
key */
size_t scram_server_key_len;
- void *scram_server_key_binary; /*base64 decoded scram server key
*/
+ void *scram_server_key_binary; /* binary (decoded) SCRAM server
key */
/* Miscellaneous stuff */
int be_pid; /* PID of backend ---
needed for cancels */
--
2.47.1
