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

Commit6136e94

Browse files
committed
Superuser can permit passwordless connections on postgres_fdw
Currently postgres_fdw doesn't permit a non-superuser to connect to aforeign server without specifying a password, or to use anauthentication mechanism that doesn't use the password. This is to avoidusing the settings and identity of the user running Postgres.However, this doesn't make sense for all authentication methods. Wetherefore allow a superuser to set "password_required 'false'" for usermappings for the postgres_fdw. The superuser must ensure that theforeign server won't try to rely solely on the server identity (e.g.trust, peer, ident) or use an authentication mechanism that relies on thepassword settings (e.g. md5, scram-sha-256).This feature is a prelude to better support for sslcert and sslkeysettings in user mappings.Author: Craig Ringer.Discussion:https://postgr.es/m/075135da-545c-f958-fed0-5dcb462d6dae@2ndQuadrant.com
1 parent16a4e4a commit6136e94

File tree

5 files changed

+257
-8
lines changed

5 files changed

+257
-8
lines changed

‎contrib/postgres_fdw/connection.c

Lines changed: 34 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@
1515
#include"access/htup_details.h"
1616
#include"access/xact.h"
1717
#include"catalog/pg_user_mapping.h"
18+
#include"commands/defrem.h"
1819
#include"mb/pg_wchar.h"
1920
#include"miscadmin.h"
2021
#include"pgstat.h"
@@ -89,7 +90,7 @@ static bool pgfdw_exec_cleanup_query(PGconn *conn, const char *query,
8990
boolignore_errors);
9091
staticboolpgfdw_get_cleanup_result(PGconn*conn,TimestampTzendtime,
9192
PGresult**result);
92-
93+
staticboolUserMappingPasswordRequired(UserMapping*user);
9394

9495
/*
9596
* Get a PGconn which can be used to execute queries on the remote PostgreSQL
@@ -272,14 +273,16 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
272273
/*
273274
* Check that non-superuser has used password to establish connection;
274275
* otherwise, he's piggybacking on the postgres server's user
275-
* identity. See also dblink_security_check() in contrib/dblink.
276+
* identity. See also dblink_security_check() in contrib/dblink
277+
* and check_conn_params.
276278
*/
277-
if (!superuser_arg(user->userid)&& !PQconnectionUsedPassword(conn))
279+
if (!superuser_arg(user->userid)&&UserMappingPasswordRequired(user)&&
280+
!PQconnectionUsedPassword(conn))
278281
ereport(ERROR,
279282
(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
280283
errmsg("password is required"),
281284
errdetail("Non-superuser cannot connect if the server does not request a password."),
282-
errhint("Target server's authentication method must be changed.")));
285+
errhint("Target server's authentication method must be changed or password_required=false set in the user mapping attributes.")));
283286

284287
/* Prepare new session for use */
285288
configure_remote_session(conn);
@@ -312,12 +315,31 @@ disconnect_pg_server(ConnCacheEntry *entry)
312315
}
313316
}
314317

318+
/*
319+
* Return true if the password_required is defined and false for this user
320+
* mapping, otherwise false. The mapping has been pre-validated.
321+
*/
322+
staticbool
323+
UserMappingPasswordRequired(UserMapping*user)
324+
{
325+
ListCell*cell;
326+
327+
foreach(cell,user->options)
328+
{
329+
DefElem*def= (DefElem*)lfirst(cell);
330+
if (strcmp(def->defname,"password_required")==0)
331+
returndefGetBoolean(def);
332+
}
333+
334+
return true;
335+
}
336+
315337
/*
316338
* For non-superusers, insist that the connstr specify a password. This
317-
* prevents a password from being picked up from .pgpass, a service file,
318-
*theenvironment, etc. We don't want the postgres user's passwords
319-
* to be accessible to non-superusers. (See also dblink_connstr_check in
320-
* contrib/dblink.)
339+
* prevents a password from being picked up from .pgpass, a service file, the
340+
* environment, etc. We don't want the postgres user's passwords,
341+
*certificates, etcto be accessible to non-superusers. (See also
342+
*dblink_connstr_check incontrib/dblink.)
321343
*/
322344
staticvoid
323345
check_conn_params(constchar**keywords,constchar**values,UserMapping*user)
@@ -335,6 +357,10 @@ check_conn_params(const char **keywords, const char **values, UserMapping *user)
335357
return;
336358
}
337359

360+
/* ok if the superuser explicitly said so at user mapping creation time */
361+
if (!UserMappingPasswordRequired(user))
362+
return;
363+
338364
ereport(ERROR,
339365
(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
340366
errmsg("password is required"),

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8809,6 +8809,100 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
88098809
-> Foreign Scan on fpagg_tab_p3 pagg_tab_2
88108810
(15 rows)
88118811

8812+
-- ===================================================================
8813+
-- access rights and superuser
8814+
-- ===================================================================
8815+
-- Non-superuser cannot create a FDW without a password in the connstr
8816+
CREATE ROLE nosuper NOSUPERUSER;
8817+
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO nosuper;
8818+
SET ROLE nosuper;
8819+
SHOW is_superuser;
8820+
is_superuser
8821+
--------------
8822+
off
8823+
(1 row)
8824+
8825+
-- This will be OK, we can create the FDW
8826+
DO $d$
8827+
BEGIN
8828+
EXECUTE $$CREATE SERVER loopback_nopw FOREIGN DATA WRAPPER postgres_fdw
8829+
OPTIONS (dbname '$$||current_database()||$$',
8830+
port '$$||current_setting('port')||$$'
8831+
)$$;
8832+
END;
8833+
$d$;
8834+
-- But creation of user mappings for non-superusers should fail
8835+
CREATE USER MAPPING FOR public SERVER loopback_nopw;
8836+
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
8837+
CREATE FOREIGN TABLE ft1_nopw (
8838+
c1 int NOT NULL,
8839+
c2 int NOT NULL,
8840+
c3 text,
8841+
c4 timestamptz,
8842+
c5 timestamp,
8843+
c6 varchar(10),
8844+
c7 char(10) default 'ft1',
8845+
c8 user_enum
8846+
) SERVER loopback_nopw OPTIONS (schema_name 'public', table_name 'ft1');
8847+
SELECT * FROM ft1_nopw LIMIT 1;
8848+
ERROR: password is required
8849+
DETAIL: Non-superusers must provide a password in the user mapping.
8850+
-- If we add a password to the connstr it'll fail, because we don't allow passwords
8851+
-- in connstrs only in user mappings.
8852+
DO $d$
8853+
BEGIN
8854+
EXECUTE $$ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw')$$;
8855+
END;
8856+
$d$;
8857+
ERROR: invalid option "password"
8858+
HINT: Valid options in this context are: service, passfile, channel_binding, connect_timeout, dbname, host, hostaddr, port, options, application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, tcp_user_timeout, sslmode, sslcompression, sslcert, sslkey, sslrootcert, sslcrl, requirepeer, gssencmode, krbsrvname, target_session_attrs, use_remote_estimate, fdw_startup_cost, fdw_tuple_cost, extensions, updatable, fetch_size
8859+
CONTEXT: SQL statement "ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw')"
8860+
PL/pgSQL function inline_code_block line 3 at EXECUTE
8861+
-- If we add a password for our user mapping instead, we should get a different
8862+
-- error because the password wasn't actually *used* when we run with trust auth.
8863+
--
8864+
-- This won't work with installcheck, but neither will most of the FDW checks.
8865+
ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD password 'dummypw');
8866+
SELECT * FROM ft1_nopw LIMIT 1;
8867+
ERROR: password is required
8868+
DETAIL: Non-superuser cannot connect if the server does not request a password.
8869+
HINT: Target server's authentication method must be changed or password_required=false set in the user mapping attributes.
8870+
-- Unpriv user cannot make the mapping passwordless
8871+
ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD password_required 'false');
8872+
ERROR: password_required=false is superuser-only
8873+
HINT: User mappings with the password_required option set to false may only be created or modified by the superuser
8874+
SELECT * FROM ft1_nopw LIMIT 1;
8875+
ERROR: password is required
8876+
DETAIL: Non-superuser cannot connect if the server does not request a password.
8877+
HINT: Target server's authentication method must be changed or password_required=false set in the user mapping attributes.
8878+
RESET ROLE;
8879+
-- But the superuser can
8880+
ALTER USER MAPPING FOR nosuper SERVER loopback_nopw OPTIONS (ADD password_required 'false');
8881+
SET ROLE nosuper;
8882+
-- Should finally work now
8883+
SELECT * FROM ft1_nopw LIMIT 1;
8884+
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
8885+
------+----+----+----+----+----+------------+----
8886+
1111 | 2 | | | | | ft1 |
8887+
(1 row)
8888+
8889+
-- We're done with the role named after a specific user and need to check the
8890+
-- changes to the public mapping.
8891+
DROP USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
8892+
-- This will fail again as it'll resolve the user mapping for public, which
8893+
-- lacks password_required=false
8894+
SELECT * FROM ft1_nopw LIMIT 1;
8895+
ERROR: password is required
8896+
DETAIL: Non-superusers must provide a password in the user mapping.
8897+
RESET ROLE;
8898+
-- The user mapping for public is passwordless and lacks the password_required=false
8899+
-- mapping option, but will work because the current user is a superuser.
8900+
SELECT * FROM ft1_nopw LIMIT 1;
8901+
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
8902+
------+----+----+----+----+----+------------+----
8903+
1111 | 2 | | | | | ft1 |
8904+
(1 row)
8905+
88128906
-- Clean-up
88138907
RESET enable_partitionwise_aggregate;
88148908
-- Two-phase transactions are not supported.

‎contrib/postgres_fdw/option.c

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -51,6 +51,7 @@ static void InitPgFdwOptions(void);
5151
staticboolis_valid_option(constchar*keyword,Oidcontext);
5252
staticboolis_libpq_option(constchar*keyword);
5353

54+
#include"miscadmin.h"
5455

5556
/*
5657
* Validate the generic options given to a FOREIGN DATA WRAPPER, SERVER,
@@ -141,6 +142,23 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
141142
errmsg("%s requires a non-negative integer value",
142143
def->defname)));
143144
}
145+
elseif (strcmp(def->defname,"password_required")==0)
146+
{
147+
boolpw_required=defGetBoolean(def);
148+
149+
/*
150+
* Only the superuser may set this option on a user mapping, or
151+
* alter a user mapping on which this option is set. We allow a
152+
* user to clear this option if it's set - in fact, we don't have a
153+
* choice since we can't see the old mapping when validating an
154+
* alter.
155+
*/
156+
if (!superuser()&& !pw_required)
157+
ereport(ERROR,
158+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
159+
errmsg("password_required=false is superuser-only"),
160+
errhint("User mappings with the password_required option set to false may only be created or modified by the superuser")));
161+
}
144162
}
145163

146164
PG_RETURN_VOID();
@@ -175,6 +193,7 @@ InitPgFdwOptions(void)
175193
/* fetch_size is available on both server and table */
176194
{"fetch_size",ForeignServerRelationId, false},
177195
{"fetch_size",ForeignTableRelationId, false},
196+
{"password_required",UserMappingRelationId, false},
178197
{NULL,InvalidOid, false}
179198
};
180199

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2493,6 +2493,92 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
24932493
EXPLAIN (COSTS OFF)
24942494
SELECT b,avg(a),max(a),count(*)FROM pagg_tabGROUP BY bHAVINGsum(a)<700ORDER BY1;
24952495

2496+
-- ===================================================================
2497+
-- access rights and superuser
2498+
-- ===================================================================
2499+
2500+
-- Non-superuser cannot create a FDW without a password in the connstr
2501+
CREATE ROLE nosuper NOSUPERUSER;
2502+
2503+
GRANT USAGEON FOREIGN DATA WRAPPER postgres_fdw TO nosuper;
2504+
2505+
SET ROLE nosuper;
2506+
2507+
SHOW is_superuser;
2508+
2509+
-- This will be OK, we can create the FDW
2510+
DO $d$
2511+
BEGIN
2512+
EXECUTE $$CREATE SERVER loopback_nopw FOREIGN DATA WRAPPER postgres_fdw
2513+
OPTIONS (dbname'$$||current_database()||$$',
2514+
port'$$||current_setting('port')||$$'
2515+
)$$;
2516+
END;
2517+
$d$;
2518+
2519+
-- But creation of user mappings for non-superusers should fail
2520+
CREATEUSERMAPPING FOR public SERVER loopback_nopw;
2521+
CREATEUSERMAPPING FORCURRENT_USER SERVER loopback_nopw;
2522+
2523+
CREATE FOREIGN TABLE ft1_nopw (
2524+
c1intNOT NULL,
2525+
c2intNOT NULL,
2526+
c3text,
2527+
c4timestamptz,
2528+
c5timestamp,
2529+
c6varchar(10),
2530+
c7char(10) default'ft1',
2531+
c8 user_enum
2532+
) SERVER loopback_nopw OPTIONS (schema_name'public', table_name'ft1');
2533+
2534+
SELECT*FROM ft1_nopwLIMIT1;
2535+
2536+
-- If we add a password to the connstr it'll fail, because we don't allow passwords
2537+
-- in connstrs only in user mappings.
2538+
2539+
DO $d$
2540+
BEGIN
2541+
EXECUTE $$ALTER SERVER loopback_nopw OPTIONS (ADD password'dummypw')$$;
2542+
END;
2543+
$d$;
2544+
2545+
-- If we add a password for our user mapping instead, we should get a different
2546+
-- error because the password wasn't actually *used* when we run with trust auth.
2547+
--
2548+
-- This won't work with installcheck, but neither will most of the FDW checks.
2549+
2550+
ALTERUSER MAPPING FORCURRENT_USER SERVER loopback_nopw OPTIONS (ADD password'dummypw');
2551+
2552+
SELECT*FROM ft1_nopwLIMIT1;
2553+
2554+
-- Unpriv user cannot make the mapping passwordless
2555+
ALTERUSER MAPPING FORCURRENT_USER SERVER loopback_nopw OPTIONS (ADD password_required'false');
2556+
2557+
SELECT*FROM ft1_nopwLIMIT1;
2558+
2559+
RESET ROLE;
2560+
2561+
-- But the superuser can
2562+
ALTERUSER MAPPING FOR nosuper SERVER loopback_nopw OPTIONS (ADD password_required'false');
2563+
2564+
SET ROLE nosuper;
2565+
2566+
-- Should finally work now
2567+
SELECT*FROM ft1_nopwLIMIT1;
2568+
2569+
-- We're done with the role named after a specific user and need to check the
2570+
-- changes to the public mapping.
2571+
DROPUSER MAPPING FORCURRENT_USER SERVER loopback_nopw;
2572+
2573+
-- This will fail again as it'll resolve the user mapping for public, which
2574+
-- lacks password_required=false
2575+
SELECT*FROM ft1_nopwLIMIT1;
2576+
2577+
RESET ROLE;
2578+
2579+
-- The user mapping for public is passwordless and lacks the password_required=false
2580+
-- mapping option, but will work because the current user is a superuser.
2581+
SELECT*FROM ft1_nopwLIMIT1;
24962582

24972583
-- Clean-up
24982584
RESET enable_partitionwise_aggregate;

‎doc/src/sgml/postgres-fdw.sgml

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -136,6 +136,30 @@
136136
authentication, so always specify the <literal>password</literal> option
137137
for user mappings belonging to non-superusers.
138138
</para>
139+
<para>
140+
A superuser may override this check on a per-user-mapping basis by setting
141+
the user mapping option <literal>password_required 'false'</literal>, e.g.
142+
<programlisting>
143+
ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
144+
OPTIONS (ADD password_required 'false');
145+
</programlisting>
146+
To prevent unprivileged users from exploiting the authentication rights
147+
of the unix user the postgres server is running as to escalate to superuser
148+
rights, only the superuser may set this option on a user mapping.
149+
</para>
150+
<para>
151+
Care is required to ensure that this does not allow the mapped
152+
user the ability to connect as superuser to the mapped database per
153+
CVE-2007-3278 and CVE-2007-6601. Don't set
154+
<literal>password_required=false</literal>
155+
on the <literal>public</literal> role. Keep in mind that the mapped
156+
user can potentially use any client certificates,
157+
<filename>.pgpass</filename>,
158+
<filename>.pg_service.conf</filename> etc in the unix home directory of the
159+
system user the postgres server runs as. They can also use any trust
160+
relationship granted by authentication modes like <literal>peer</literal>
161+
or <literal>ident</literal> authentication.
162+
</para>
139163
</sect3>
140164

141165
<sect3>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp