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

Commit5c31669

Browse files
committed
Re-validate connection string in libpqrcv_connect().
A superuser may create a subscription with password_required=true, butwhich uses a connection string without a password.Previously, if the owner of such a subscription was changed to anon-superuser, the non-superuser was able to utilize a password fromanother source (like a password file or the PGPASSWORD environmentvariable), which should not have been allowed.This commit adds a step to re-validate the connection string beforeconnecting.Reported-by: Jeff DavisAuthor: Vignesh CReviewed-by: Peter Smith, Robert Haas, Amit KapilaDiscussion:https://www.postgresql.org/message-id/flat/e5892973ae2a80a1a3e0266806640dae3c428100.camel%40j-davis.comBackpatch-through: 16
1 parenta160423 commit5c31669

File tree

3 files changed

+95
-5
lines changed

3 files changed

+95
-5
lines changed

‎doc/src/sgml/ref/create_subscription.sgml

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -357,11 +357,12 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
357357
<term><literal>password_required</literal> (<type>boolean</type>)</term>
358358
<listitem>
359359
<para>
360-
Specifies whether connections to the publisher made as a result
361-
of this subscription must use password authentication. This setting
362-
is ignored when the subscription is owned by a superuser.
363-
The default is <literal>true</literal>. Only superusers can set
364-
this value to <literal>false</literal>.
360+
If set to <literal>true</literal>, connections to the publisher made
361+
as a result of this subscription must use password authentication
362+
and the password must be specified as a part of the connection
363+
string. This setting is ignored when the subscription is owned by a
364+
superuser. The default is <literal>true</literal>. Only superusers
365+
can set this value to <literal>false</literal>.
365366
</para>
366367
</listitem>
367368
</varlistentry>

‎src/backend/replication/libpqwalreceiver/libpqwalreceiver.c

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -137,6 +137,15 @@ libpqrcv_connect(const char *conninfo, bool logical, bool must_use_password,
137137
constchar*vals[6];
138138
inti=0;
139139

140+
/*
141+
* Re-validate connection string. The validation already happened at DDL
142+
* time, but the subscription owner may have changed. If we don't recheck
143+
* with the correct must_use_password, it's possible that the connection
144+
* will obtain the password from a different source, such as PGPASSFILE or
145+
* PGPASSWORD.
146+
*/
147+
libpqrcv_check_conninfo(conninfo,must_use_password);
148+
140149
/*
141150
* We use the expand_dbname parameter to process the connection string (or
142151
* URI), and pass some extra options.

‎src/test/subscription/t/027_nosuperuser.pl

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -327,4 +327,84 @@ sub grant_superuser
327327
qr/LOG: ( [A-Z0-9]+:)? logical replication worker for subscription\"regression_sub\" will restart because the subscription owner's superuser privileges have been revoked/,
328328
$offset);
329329

330+
# If the subscription connection requires a password ('password_required'
331+
# is true) then a non-superuser must specify that password in the connection
332+
# string.
333+
$ENV{"PGPASSWORD"} ='secret';
334+
335+
my$node_publisher1 = PostgreSQL::Test::Cluster->new('publisher1');
336+
my$node_subscriber1 = PostgreSQL::Test::Cluster->new('subscriber1');
337+
$node_publisher1->init(allows_streaming=>'logical');
338+
$node_subscriber1->init;
339+
$node_publisher1->start;
340+
$node_subscriber1->start;
341+
my$publisher_connstr1 =
342+
$node_publisher1->connstr .' user=regress_test_user dbname=postgres';
343+
my$publisher_connstr2 =
344+
$node_publisher1->connstr
345+
.' user=regress_test_user dbname=postgres password=secret';
346+
347+
formy$node ($node_publisher1,$node_subscriber1)
348+
{
349+
$node->safe_psql(
350+
'postgres',qq(
351+
CREATE ROLE regress_test_user PASSWORD 'secret' LOGIN REPLICATION;
352+
GRANT CREATE ON DATABASE postgres TO regress_test_user;
353+
GRANT PG_CREATE_SUBSCRIPTION TO regress_test_user;
354+
));
355+
}
356+
357+
$node_publisher1->safe_psql(
358+
'postgres',qq(
359+
SET SESSION AUTHORIZATION regress_test_user;
360+
CREATE PUBLICATION regress_test_pub;
361+
));
362+
$node_subscriber1->safe_psql(
363+
'postgres',qq(
364+
CREATE SUBSCRIPTION regress_test_sub CONNECTION '$publisher_connstr1' PUBLICATION regress_test_pub;
365+
));
366+
367+
# Wait for initial sync to finish
368+
$node_subscriber1->wait_for_subscription_sync($node_publisher1,
369+
'regress_test_sub');
370+
371+
# Setup pg_hba configuration so that logical replication connection without
372+
# password is not allowed.
373+
unlink($node_publisher1->data_dir .'/pg_hba.conf');
374+
$node_publisher1->append_conf('pg_hba.conf',
375+
qq{local all regress_test_user md5});
376+
$node_publisher1->reload;
377+
378+
# Change the subscription owner to a non-superuser
379+
$node_subscriber1->safe_psql(
380+
'postgres',qq(
381+
ALTER SUBSCRIPTION regress_test_sub OWNER TO regress_test_user;
382+
));
383+
384+
# Non-superuser must specify password in the connection string
385+
my ($ret,$stdout,$stderr) =$node_subscriber1->psql(
386+
'postgres',qq(
387+
SET SESSION AUTHORIZATION regress_test_user;
388+
ALTER SUBSCRIPTION regress_test_sub REFRESH PUBLICATION;
389+
));
390+
isnt($ret, 0,
391+
"non zero exit for subscription whose owner is a non-superuser must specify password parameter of the connection string"
392+
);
393+
ok($stderr =~m/DETAIL: Non-superusers must provide a password in the connection string./,
394+
'subscription whose owner is a non-superuser must specify password parameter of the connection string'
395+
);
396+
397+
delete$ENV{"PGPASSWORD"};
398+
399+
# It should succeed after including the password parameter of the connection
400+
# string.
401+
($ret,$stdout,$stderr) =$node_subscriber1->psql(
402+
'postgres',qq(
403+
SET SESSION AUTHORIZATION regress_test_user;
404+
ALTER SUBSCRIPTION regress_test_sub CONNECTION '$publisher_connstr2';
405+
ALTER SUBSCRIPTION regress_test_sub REFRESH PUBLICATION;
406+
));
407+
is($ret, 0,
408+
"Non-superuser will be able to refresh the publication after specifying the password parameter of the connection string"
409+
);
330410
done_testing();

0 commit comments

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