Skip to content

Commit 3642df2

Browse files
peterepetere
committed
dblink: SCRAM authentication pass-through
This enables SCRAM authentication for dblink (using dblink_fdw) when connecting to a foreign server without having to store a plain-text password on user mapping options This uses the same approach as it was implemented for postgres_fdw in commit 761c795. (It also contains the equivalent of the subsequent fixes 76563f8 and d2028e9.) Author: Matheus Alcantara <mths.dev@pm.me> Reviewed-by: Jacob Champion <jacob.champion@enterprisedb.com> Discussion: https://www.postgresql.org/message-id/flat/CAFY6G8ercA1KES%3DE_0__R9QCTR805TTyYr1No8qF8ZxmMg8z2Q%40mail.gmail.com
1 parent a3b6dfd commit 3642df2

File tree

6 files changed

+457
-12
lines changed

6 files changed

+457
-12
lines changed

‎contrib/dblink/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ PGFILEDESC = "dblink - connect to other PostgreSQL databases"
1313

1414
REGRESS = dblink
1515
REGRESS_OPTS = --dlpath=$(top_builddir)/src/test/regress
16+
TAP_TESTS = 1
1617

1718
ifdef USE_PGXS
1819
PG_CONFIG = pg_config

‎contrib/dblink/dblink.c

Lines changed: 177 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,8 @@
4343
#include "catalog/pg_foreign_server.h"
4444
#include "catalog/pg_type.h"
4545
#include "catalog/pg_user_mapping.h"
46+
#include "commands/defrem.h"
47+
#include "common/base64.h"
4648
#include "executor/spi.h"
4749
#include "foreign/foreign.h"
4850
#include "funcapi.h"
@@ -126,6 +128,11 @@ static bool is_valid_dblink_option(const PQconninfoOption *options,
126128
const char *option, Oid context);
127129
static int applyRemoteGucs(PGconn *conn);
128130
static void restoreLocalGucs(int nestlevel);
131+
static bool UseScramPassthrough(ForeignServer *foreign_server, UserMapping *user);
132+
static void appendSCRAMKeysInfo(StringInfo buf);
133+
static bool is_valid_dblink_fdw_option(const PQconninfoOption *options, const char *option,
134+
Oid context);
135+
static bool dblink_connstr_has_required_scram_options(const char *connstr);
129136

130137
/* Global */
131138
static remoteConn *pconn = NULL;
@@ -1964,7 +1971,7 @@ dblink_fdw_validator(PG_FUNCTION_ARGS)
19641971
{
19651972
DefElem *def = (DefElem *) lfirst(cell);
19661973

1967-
if (!is_valid_dblink_option(options, def->defname, context))
1974+
if (!is_valid_dblink_fdw_option(options, def->defname, context))
19681975
{
19691976
/*
19701977
* Unknown option, or invalid option for the context specified, so
@@ -2596,6 +2603,67 @@ deleteConnection(const char *name)
25962603
errmsg("undefined connection name")));
25972604
}
25982605

2606+
/*
2607+
* Ensure that require_auth and SCRAM keys are correctly set on connstr.
2608+
* SCRAM keys used to pass-through are coming from the initial connection
2609+
* from the client with the server.
2610+
*
2611+
* All required SCRAM options are set by dblink, so we just need to ensure
2612+
* that these options are not overwritten by the user.
2613+
*
2614+
* See appendSCRAMKeysInfo and its usage for more.
2615+
*/
2616+
bool
2617+
dblink_connstr_has_required_scram_options(const char *connstr)
2618+
{
2619+
PQconninfoOption *options;
2620+
bool has_scram_server_key = false;
2621+
bool has_scram_client_key = false;
2622+
bool has_require_auth = false;
2623+
bool has_scram_keys = false;
2624+
2625+
options = PQconninfoParse(connstr, NULL);
2626+
if (options)
2627+
{
2628+
/*
2629+
* Continue iterating even if we found the keys that we need to
2630+
* validate to make sure that there is no other declaration of these
2631+
* keys that can overwrite the first.
2632+
*/
2633+
for (PQconninfoOption *option = options; option->keyword != NULL; option++)
2634+
{
2635+
if (strcmp(option->keyword, "require_auth") == 0)
2636+
{
2637+
if (option->val != NULL && strcmp(option->val, "scram-sha-256") == 0)
2638+
has_require_auth = true;
2639+
else
2640+
has_require_auth = false;
2641+
}
2642+
2643+
if (strcmp(option->keyword, "scram_client_key") == 0)
2644+
{
2645+
if (option->val != NULL && option->val[0] != '\0')
2646+
has_scram_client_key = true;
2647+
else
2648+
has_scram_client_key = false;
2649+
}
2650+
2651+
if (strcmp(option->keyword, "scram_server_key") == 0)
2652+
{
2653+
if (option->val != NULL && option->val[0] != '\0')
2654+
has_scram_server_key = true;
2655+
else
2656+
has_scram_server_key = false;
2657+
}
2658+
}
2659+
PQconninfoFree(options);
2660+
}
2661+
2662+
has_scram_keys = has_scram_client_key && has_scram_server_key && MyProcPort->has_scram_keys;
2663+
2664+
return (has_scram_keys && has_require_auth);
2665+
}
2666+
25992667
/*
26002668
* We need to make sure that the connection made used credentials
26012669
* which were provided by the user, so check what credentials were
@@ -2612,6 +2680,18 @@ dblink_security_check(PGconn *conn, remoteConn *rconn, const char *connstr)
26122680
if (PQconnectionUsedPassword(conn) && dblink_connstr_has_pw(connstr))
26132681
return;
26142682

2683+
/*
2684+
* Password was not used to connect, check if SCRAM pass-through is in
2685+
* use.
2686+
*
2687+
* If dblink_connstr_has_required_scram_options is true we assume that
2688+
* UseScramPassthrough is also true because the required SCRAM keys are
2689+
* only added if UseScramPassthrough is set, and the user is not allowed
2690+
* to add the SCRAM keys on fdw and user mapping options.
2691+
*/
2692+
if (MyProcPort->has_scram_keys && dblink_connstr_has_required_scram_options(connstr))
2693+
return;
2694+
26152695
#ifdef ENABLE_GSS
26162696
/* If GSSAPI creds used to connect, make sure it was one delegated */
26172697
if (PQconnectionUsedGSSAPI(conn) && be_gssapi_get_delegation(MyProcPort))
@@ -2664,12 +2744,14 @@ dblink_connstr_has_pw(const char *connstr)
26642744
}
26652745

26662746
/*
2667-
* For non-superusers, insist that the connstr specify a password, except
2668-
* if GSSAPI credentials have been delegated (and we check that they are used
2669-
* for the connection in dblink_security_check later). This prevents a
2670-
* password or GSSAPI credentials from being picked up from .pgpass, a
2671-
* service file, the environment, etc. We don't want the postgres user's
2672-
* passwords or Kerberos credentials to be accessible to non-superusers.
2747+
* For non-superusers, insist that the connstr specify a password, except if
2748+
* GSSAPI credentials have been delegated (and we check that they are used for
2749+
* the connection in dblink_security_check later) or if SCRAM pass-through is
2750+
* being used. This prevents a password or GSSAPI credentials from being
2751+
* picked up from .pgpass, a service file, the environment, etc. We don't want
2752+
* the postgres user's passwords or Kerberos credentials to be accessible to
2753+
* non-superusers. In case of SCRAM pass-through insist that the connstr
2754+
* has the required SCRAM pass-through options.
26732755
*/
26742756
static void
26752757
dblink_connstr_check(const char *connstr)
@@ -2680,6 +2762,9 @@ dblink_connstr_check(const char *connstr)
26802762
if (dblink_connstr_has_pw(connstr))
26812763
return;
26822764

2765+
if (MyProcPort->has_scram_keys && dblink_connstr_has_required_scram_options(connstr))
2766+
return;
2767+
26832768
#ifdef ENABLE_GSS
26842769
if (be_gssapi_get_delegation(MyProcPort))
26852770
return;
@@ -2832,6 +2917,14 @@ get_connect_string(const char *servername)
28322917
if (aclresult != ACLCHECK_OK)
28332918
aclcheck_error(aclresult, OBJECT_FOREIGN_SERVER, foreign_server->servername);
28342919

2920+
/*
2921+
* First append hardcoded options needed for SCRAM pass-through, so if
2922+
* the user overwrites these options we can ereport on
2923+
* dblink_connstr_check and dblink_security_check.
2924+
*/
2925+
if (MyProcPort->has_scram_keys && UseScramPassthrough(foreign_server, user_mapping))
2926+
appendSCRAMKeysInfo(&buf);
2927+
28352928
foreach(cell, fdw->options)
28362929
{
28372930
DefElem *def = lfirst(cell);
@@ -3016,6 +3109,20 @@ is_valid_dblink_option(const PQconninfoOption *options, const char *option,
30163109
return true;
30173110
}
30183111

3112+
/*
3113+
* Same as is_valid_dblink_option but also check for only dblink_fdw specific
3114+
* options.
3115+
*/
3116+
static bool
3117+
is_valid_dblink_fdw_option(const PQconninfoOption *options, const char *option,
3118+
Oid context)
3119+
{
3120+
if (strcmp(option, "use_scram_passthrough") == 0)
3121+
return true;
3122+
3123+
return is_valid_dblink_option(options, option, context);
3124+
}
3125+
30193126
/*
30203127
* Copy the remote session's values of GUCs that affect datatype I/O
30213128
* and apply them locally in a new GUC nesting level. Returns the new
@@ -3085,3 +3192,66 @@ restoreLocalGucs(int nestlevel)
30853192
if (nestlevel > 0)
30863193
AtEOXact_GUC(true, nestlevel);
30873194
}
3195+
3196+
/*
3197+
* Append SCRAM client key and server key information from the global
3198+
* MyProcPort into the given StringInfo buffer.
3199+
*/
3200+
static void
3201+
appendSCRAMKeysInfo(StringInfo buf)
3202+
{
3203+
int len;
3204+
int encoded_len;
3205+
char *client_key;
3206+
char *server_key;
3207+
3208+
len = pg_b64_enc_len(sizeof(MyProcPort->scram_ClientKey));
3209+
/* don't forget the zero-terminator */
3210+
client_key = palloc0(len + 1);
3211+
encoded_len = pg_b64_encode((const char *) MyProcPort->scram_ClientKey,
3212+
sizeof(MyProcPort->scram_ClientKey),
3213+
client_key, len);
3214+
if (encoded_len < 0)
3215+
elog(ERROR, "could not encode SCRAM client key");
3216+
3217+
len = pg_b64_enc_len(sizeof(MyProcPort->scram_ServerKey));
3218+
/* don't forget the zero-terminator */
3219+
server_key = palloc0(len + 1);
3220+
encoded_len = pg_b64_encode((const char *) MyProcPort->scram_ServerKey,
3221+
sizeof(MyProcPort->scram_ServerKey),
3222+
server_key, len);
3223+
if (encoded_len < 0)
3224+
elog(ERROR, "could not encode SCRAM server key");
3225+
3226+
appendStringInfo(buf, "scram_client_key='%s' ", client_key);
3227+
appendStringInfo(buf, "scram_server_key='%s' ", server_key);
3228+
appendStringInfo(buf, "require_auth='scram-sha-256' ");
3229+
3230+
pfree(client_key);
3231+
pfree(server_key);
3232+
}
3233+
3234+
3235+
static bool
3236+
UseScramPassthrough(ForeignServer *foreign_server, UserMapping *user)
3237+
{
3238+
ListCell *cell;
3239+
3240+
foreach(cell, foreign_server->options)
3241+
{
3242+
DefElem *def = lfirst(cell);
3243+
3244+
if (strcmp(def->defname, "use_scram_passthrough") == 0)
3245+
return defGetBoolean(def);
3246+
}
3247+
3248+
foreach(cell, user->options)
3249+
{
3250+
DefElem *def = (DefElem *) lfirst(cell);
3251+
3252+
if (strcmp(def->defname, "use_scram_passthrough") == 0)
3253+
return defGetBoolean(def);
3254+
}
3255+
3256+
return false;
3257+
}

‎contrib/dblink/meson.build

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,4 +36,9 @@ tests += {
3636
],
3737
'regress_args': ['--dlpath', meson.build_root() / 'src/test/regress'],
3838
},
39+
'tap': {
40+
'tests': [
41+
't/001_auth_scram.pl',
42+
],
43+
},
3944
}

0 commit comments

Comments
 (0)