8000 postgres_fdw: SCRAM authentication pass-through · postgres/postgres@761c795 · GitHub
[go: up one dir, main page]
Skip to content

Commit 761c795

Browse files
peteremattheusv
petere
and
mattheusv
committed
postgres_fdw: SCRAM authentication pass-through
This enables SCRAM authentication for postgres_fdw when connecting to a foreign 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. The new foreign-server or user-mapping option "use_scram_passthrough" enables this. Co-authored-by: Matheus Alcantara <mths.dev@pm.me> Co-authored-by: Peter Eisentraut <peter@eisentraut.org> Discussion: https://www.postgresql.org/message-id/flat/27b29a35-9b96-46a9-bc1a-914140869dac@gmail.com
1 parent b6463ea commit 761c795

File tree

14 files changed

+451
-43
lines changed

14 files changed

+451
-43
lines changed

‎contrib/postgres_fdw/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@ EXTENSION = postgres_fdw
1717
DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql postgres_fdw--1.1--1.2.sql
1818

1919
REGRESS = postgres_fdw query_cancel
20+
TAP_TESTS = 1
2021

2122
ifdef USE_PGXS
2223
PG_CONFIG = pg_config

‎contrib/postgres_fdw/connection.c

Lines changed: 63 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@
1919
#include "access/xact.h"
2020
#include "catalog/pg_user_mapping.h"
2121
#include "commands/defrem.h"
22+
#include "common/base64.h"
2223
#include "funcapi.h"
2324
#include "libpq/libpq-be.h"
2425
#include "libpq/libpq-be-fe-helpers.h"
@@ -177,6 +178,7 @@ static void pgfdw_finish_abort_cleanup(List *pending_entries,
177178
static void pgfdw_security_check(const char **keywords, const char **values,
178179
UserMapping *user, PGconn *conn);
179180
static bool UserMappingPasswordRequired(UserMapping *user);
181+
static bool UseScramPassthrough(ForeignServer *server, UserMapping *user);
180182
static bool disconnect_cached_connections(Oid serverid);
181183
static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
182184
enum pgfdwVersion api_version);
@@ -485,7 +487,7 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
485487
* for application_name, fallback_application_name, client_encoding,
486488
* end marker.
487489
*/
488-
n = list_length(server->options) + list_length(user->options) + 4;
490+
n = list_length(server->options) + list_length(user->options) + 4 + 2;
489491
keywords = (const char **) palloc(n * sizeof(char *));
490492
values = (const char **) palloc(n * sizeof(char *));
491493

@@ -554,10 +556,37 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
554556
values[n] = GetDatabaseEncodingName();
555557
n++;
556558

559+
if (MyProcPort->has_scram_keys && UseScramPassthrough(server, user))
560+
{
561+
int len;
562+
563+
keywords[n] = "scram_client_key";
564+
len = pg_b64_enc_len(sizeof(MyProcPort->scram_ClientKey));
565+
/* don't forget the zero-terminator */
5 F438 66+
values[n] = palloc0(len + 1);
567+
pg_b64_encode((const char *) MyProcPort->scram_ClientKey,
568+
sizeof(MyProcPort->scram_ClientKey),
569+
(char *) values[n], len);
570+
n++;
571+
572+
keywords[n] = "scram_server_key";
573+
len = pg_b64_enc_len(sizeof(MyProcPort->scram_ServerKey));
574+
/* don't forget the zero-terminator */
575+
values[n] = palloc0(len + 1);
576+
pg_b64_encode((const char *) MyProcPort->scram_ServerKey,
577+
sizeof(MyProcPort->scram_ServerKey),
578+
(char *) values[n], len);
579+
n++;
580+
}
581+
557582
keywords[n] = values[n] = NULL;
558583

559-
/* verify the set of connection parameters */
560-
check_conn_params(keywords, values, user);
584+
/*
585+
* Verify the set of connection parameters only if scram pass-through
586+
* is not being used because the password is not necessary.
587+
*/
588+
if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
589+
check_conn_params(keywords, values, user);
561590

562591
/* first time, allocate or get the custom wait event */
563592
if (pgfdw_we_connect == 0)
@@ -575,8 +604,12 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
575604
server->servername),
576605
errdetail_internal("%s", pchomp(PQerrorMessage(conn)))));
577606

578-
/* Perform post-connection security checks */
579-
pgfdw_security_check(keywords, values, user, conn);
607+
/*
608+
* Perform post-connection security checks only if scram pass-through
609+
* is not being used because the password is not necessary.
610+
*/
611+
if (!(MyProcPort->has_scram_keys && UseScramPassthrough(server, user)))
612+
pgfdw_security_check(keywords, values, user, conn);
580613

581614
/* Prepare new session for use */
582615
configure_remote_session(conn);
@@ -629,6 +662,30 @@ UserMappingPasswordRequired(UserMapping *user)
629662
return true;
630663
}
631664

665+
static bool
666+
UseScramPassthrough(ForeignServer *server, UserMapping *user)
667+
{
668+
ListCell *cell;
669+
670+
foreach(cell, server->options)
671+
{
672+
DefElem *def = (DefElem *) lfirst(cell);
673+
674+
if (strcmp(def->defname, "use_scram_passthrough") == 0)
675+
return defGetBoolean(def);
676+
}
677+
678+
foreach(cell, user->options)
679+
{
680+
DefElem *def = (DefElem *) lfirst(cell);
681+
682+
if (strcmp(def->defname, "use_scram_passthrough") == 0)
683+
return defGetBoolean(def);
684+
}
685+
686+
return false;
687+
}
688+
632689
/*
633690
* For non-superusers, insist that the connstr specify a password or that the
634691
* user provided their own GSSAPI delegated credentials. This
@@ -666,7 +723,7 @@ check_conn_params(const char **keywords, const char **values, UserMapping *user)
666723
ereport(ERROR,
667724
(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
668725
errmsg("password or GSSAPI delegated credentials required"),
669-
errdetail("Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.")));
726+
errdetail("Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.")));
670727
}
671728

672729
/*

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10301,7 +10301,7 @@ CREATE FOREIGN TABLE pg_temp.ft1_nopw (
1030110301
) SERVER loopback_nopw OPTIONS (schema_name 'public', table_name 'ft1');
1030210302
SELECT 1 FROM ft1_nopw LIMIT 1;
1030310303
ERROR: password or GSSAPI delegated credentials required
10304-
DETAIL: Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.
10304+
DETAIL: Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.
1030510305
-- If we add a password to the connstr it'll fail, because we don't allow passwords
1030610306
-- in connstrs only in user mappings.
1030710307
ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw');
@@ -10351,7 +10351,7 @@ DROP USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
1035110351
-- lacks password_required=false
1035210352
SELECT 1 FROM ft1_nopw LIMIT 1;
1035310353
ERROR: password or GSSAPI delegated credentials required
10354-
DETAIL: Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping.
10354+
DETAIL: Non-superusers must delegate GSSAPI credentials, provide a password, or enable SCRAM pass-through in user mapping.
1035510355
RESET ROLE;
1035610356
-- The user mapping for public is passwordless and lacks the password_required=false
1035710357
-- mapping option, but will work because the current user is a superuser.

‎contrib/postgres_fdw/meson.build

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,4 +41,9 @@ tests += {
4141
],
4242
'regress_args': ['--dlpath', meson.build_root() / 'src/test/regress'],
4343
},
44+
'tap': {
45+
'tests': [
46+
't/001_auth_scram.pl',
47+
],
48+
},
4449
}

‎contrib/postgres_fdw/option.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -279,6 +279,9 @@ InitPgFdwOptions(void)
279279
{"analyze_sampling", ForeignServerRelationId, false},
280280
{"analyze_sampling", ForeignTableRelationId, false},
281281

282+
{"use_scram_passthrough", ForeignServerRelationId, false},
283+
{"use_scram_passthrough", UserMappingRelationId, false},
284+
282285
/*
283286
* sslcert and sslkey are in fact libpq options, but we repeat them
284287
* here to allow them to appear in both foreign server context (when

‎contrib/postgres_fdw/t/001_auth_scram.pl

Lines changed: 151 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,151 @@
1+
# Copyright (c) 2024-2025, PostgreSQL Global Development Group
2+
3+
# Test SCRAM authentication when opening a new connection with a foreign
4+
# server.
5+
#
6+
# The test is executed by testing the SCRAM authentifcation on a looplback
7+
# connection on the same server and with different servers.
8+
9+
use strict;
10+
use warnings FATAL => 'all';
11+
use PostgreSQL::Test::Utils;
12+
use PostgreSQL::Test::Cluster;
13+
use Test::More;
14+
15+
my $hostaddr = '127.0.0.1';
16+
my $user = "user01";
17+
18+
my $db0 = "db0"; # For node1
19+
my $db1 = "db1"; # For node1
20+
my $db2 = "db2"; # For node2
21+
my $fdw_server = "db1_fdw";
22+
my $fdw_server2 = "db2_fdw";
23+
24+
my $node1 = PostgreSQL::Test::Cluster->new('node1');
25+
my $node2 = PostgreSQL::Test::Cluster->new('node2');
26+
27+
$node1->init;
28+
$node2->init;
29+
30+
$node1->start;
31+
$node2->start;
32+
33+
# Test setup
34+
35+
$node1->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
36+
$node2->safe_psql('postgres', qq'CREATE USER $user WITH password \'pass\'');
37+
$ENV{PGPASSWORD} = "pass";
38+
39+
$node1->safe_psql('postgres', qq'CREATE DATABASE $db0');
40+
$node1->safe_psql('postgres', qq'CREATE DATABASE $db1');
41+
$node2->safe_psql('postgres', qq'CREATE DATABASE $db2');
42+
43+
setup_table($node1, $db1, "t");
44+
setup_table($node2, $db2, "t2");
45+
46+
$node1->safe_psql($db0, 'CREATE EXTENSION IF NOT EXISTS postgres_fdw');
47+
setup_fdw_server($node1, $db0, $fdw_server, $node1, $db1);
48+
setup_fdw_server($node1, $db0, $fdw_server2, $node2, $db2);
49+
50+
setup_user_mapping($node1, $db0, $fdw_server);
51+
setup_user_mapping($node1, $db0, $fdw_server2);
52+
53+
# Make the user have the same SCRAM key on both servers. Forcing to have the
54+
# same iteration and salt.
55+
my $rolpassword = $node1->safe_psql('postgres',
56+
qq"SELECT rolpassword FROM pg_authid WHERE rolname = '$user';");
57+
$node2->safe_psql('postgres', qq"ALTER ROLE $user PASSWORD '$rolpassword'");
58+
59+
setup_pghba($node1);
60+
setup_pghba($node2);
61+
62+
# End of test setup
63+
64+
test_fdw_auth($node1, $db0, "t", $fdw_server,
65+
"SCRAM auth on the same database cluster must succeed");
66+
test_fdw_auth($node1, $db0, "t2", $fdw_server2,
67+
"SCRAM auth on a different database cluster must succeed");
68+
test_auth($node2, $db2, "t2",
69+
"SCRAM auth directly on foreign server should still succeed");
70+
71+
# Helper functions
72+
73+
sub test_auth
74+
{
75+
local $Test::Builder::Level = $Test::Builder::Level + 1;
76+
77+
my ($node, $db, $tbl, $testname) = @_;
78+
my $connstr = $node->connstr($db) . qq' user=$user';
79+
80+
my $ret = $node->safe_psql(
81+
$db,
82+
qq'SELECT count(1) FROM $tbl',
83+
connstr => $connstr);
84+
85+
is($ret, '10', $testname);
86+
}
87+
88+
sub test_fdw_auth
89+
{
90+
local $Test::Builder::Level = $Test::Builder::Level + 1;
91+
92+
my ($node, $db, $tbl, $fdw, $testname) = @_;
93+
my $connstr = $node->connstr($db) . qq' user=$user';
94+
95+
$node->safe_psql(
96+
$db,
97+
qq'IMPORT FOREIGN SCHEMA public LIMIT TO ($tbl) FROM SERVER $fdw INTO public;',
98+
connstr => $connstr);
99+
100+
test_auth($node, $db, $tbl, $testname);
101+
}
102+
103+
sub setup_pghba
104+
{
105+
my ($node) = @_;
106+
107+
unlink($node->data_dir . '/pg_hba.conf');
108+
$node->append_conf(
109+
'pg_hba.conf', qq{
110+
local all all scram-sha-256
111+
host all all $hostaddr/32 scram-sha-256
112+
});
113+
114+
$node->restart;
115+
}
116+
117+
sub setup_user_mapping
118+
{
119+
my ($node, $db, $fdw) = @_;
120+
121+
$node->safe_psql($db,
122+
qq'CREATE USER MAPPING FOR $user SERVER $fdw OPTIONS (user \'$user\');'
123+
);
124+
$node->safe_psql($db, qq'GRANT USAGE ON FOREIGN SERVER $fdw TO $user;');
125+
$node->safe_psql($db, qq'GRANT ALL ON SCHEMA public TO $user');
126+
}
127+
128+
sub setup_fdw_server
129+
{
130+
my ($node, $db, $fdw, $fdw_node, $dbname) = @_;
131+
my $host = $fdw_node->host;
132+
my $port = $fdw_node->port;
133+
134+
$node->safe_psql(
135+
$db, qq'CREATE SERVER $fdw FOREIGN DATA WRAPPER postgres_fdw options (
136+
host \'$host\', port \'$port\', dbname \'$dbname\', use_scram_passthrough \'true\') '
137+
);
138+
}
139+
140+
sub setup_table
141+
{
142+
my ($node, $db, $tbl) = @_;
143+
144+
$node->safe_psql($db,
145+
qq'CREATE TABLE $tbl AS SELECT g, g + 1 FROM generate_series(1,10) g(g)'
146+
);
147+
$node->safe_psql($db, qq'GRANT USAGE ON SCHEMA public TO $user');
148+
$node->safe_psql($db, qq'GRANT SELECT ON $tbl TO $user');
149+
}
150+
151+
done_testing();

‎doc/src/sgml/libpq.sgml

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2199,6 +2199,34 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
21992199
</listitem>
22002200
</varlistentry>
22012201

2202+
<varlistentry id="libpq-connect-scram-client-key" xreflabel="scram_client_key">
2203+
<term><literal>scram_client_key</literal></term>
2204+
<listitem>
2205+
<para>
2206+
The base64-encoded SCRAM client key. This can be used by foreign-data
2207+
wrappers or similar middleware to enable pass-through SCRAM
2208+
authentication. See <xref
2209+
linkend="postgres-fdw-options-connection-management"/> for one such
2210+
implementation. It is not meant to be specified directly by users or
2211+
client applications.
2212+
</para>
2213+
</listitem>
2214+
</varlistentry>
2215+
2216+
<varlistentry id="libpq-connect-scram-server-key" xreflabel="scram_server_key">
2217+
<term><literal>scram_server_key</literal></term>
2218+
<listitem>
2219+
<para>
2220+
The base64-encoded SCRAM server key. This can be used by foreign-data
2221+
wrappers or similar middleware to enable pass-through SCRAM
2222+
authentication. See <xref
2223+
linkend="postgres-fdw-options-connection-management"/> for one such
2224+
implementation. It is not meant to be specified directly by users or
2225+
client applications.
2226+
</para>
2227+
</listitem>
2228+
</varlistentry>
2229+
22022230
<varlistentry id="libpq-connect-service" xreflabel="service">
22032231
<term><literal>service</literal></term>
22042232
<listitem>

0 commit comments

Comments
 (0)
0