Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit761c795

Browse files
peteremattheusv
andcommitted
postgres_fdw: SCRAM authentication pass-through
This enables SCRAM authentication for postgres_fdw when connecting toa foreign server without having to store a plain-text password on usermapping options.This is done by saving the SCRAM ClientKey and ServeryKey from theclient authentication and using those instead of the plain-textpassword for the server-side SCRAM exchange. The new foreign-serveror 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 parentb6463ea commit761c795

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
ifdefUSE_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
staticvoidpgfdw_security_check(constchar**keywords,constchar**values,
178179
UserMapping*user,PGconn*conn);
179180
staticboolUserMappingPasswordRequired(UserMapping*user);
181+
staticboolUseScramPassthrough(ForeignServer*server,UserMapping*user);
180182
staticbooldisconnect_cached_connections(Oidserverid);
181183
staticvoidpostgres_fdw_get_connections_internal(FunctionCallInfofcinfo,
182184
enumpgfdwVersionapi_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= (constchar**)palloc(n*sizeof(char*));
490492
values= (constchar**)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+
intlen;
562+
563+
keywords[n]="scram_client_key";
564+
len=pg_b64_enc_len(sizeof(MyProcPort->scram_ClientKey));
565+
/* don't forget the zero-terminator */
566+
values[n]=palloc0(len+1);
567+
pg_b64_encode((constchar*)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((constchar*)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+
staticbool
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+
returndefGetBoolean(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+
returndefGetBoolean(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 orprovide 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 orprovide 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 orprovide 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
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 warningsFATAL=>'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+
subtest_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+
subtest_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+
subsetup_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+
subsetup_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+
subsetup_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+
subsetup_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)
[8]ページ先頭
©2009-2025 Movatter.jp