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

Commit9b8aff8

Browse files
committed
Add REPLICATION privilege for ROLEs
This privilege is required to do Streaming Replication, instead ofsuperuser, making it possible to set up a SR slave that doesn'thave write permissions on the master.Superuser privileges do NOT override this check, so in order touse the default superuser account for replication it must beexplicitly granted the REPLICATION permissions. This is backwardsincompatible change, in the interest of higher default security.
1 parentf2ba1e9 commit9b8aff8

File tree

19 files changed

+189
-32
lines changed

19 files changed

+189
-32
lines changed

‎doc/src/sgml/catalogs.sgml‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1235,6 +1235,17 @@
12351235
</entry>
12361236
</row>
12371237

1238+
<row>
1239+
<entry><structfield>rolreplication</structfield></entry>
1240+
<entry><type>bool</type></entry>
1241+
<entry>
1242+
Role is a replication role. That is, this role can initiate streaming
1243+
replication (see <xref linkend="streaming-replication">) and set/unset
1244+
the system backup mode using <function>pg_start_backup</> and
1245+
<function>pg_stop_backup</>.
1246+
</entry>
1247+
</row>
1248+
12381249
<row>
12391250
<entry><structfield>rolconnlimit</structfield></entry>
12401251
<entry><type>int4</type></entry>

‎doc/src/sgml/func.sgml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13969,14 +13969,14 @@ SELECT set_config('log_statement_stats', 'off', false);
1396913969
<literal><function>pg_start_backup(<parameter>label</> <type>text</> <optional>, <parameter>fast</> <type>boolean</> </optional>)</function></literal>
1397013970
</entry>
1397113971
<entry><type>text</type></entry>
13972-
<entry>Prepare for performing on-line backup (restricted to superusers)</entry>
13972+
<entry>Prepare for performing on-line backup (restricted to superusers or replication roles)</entry>
1397313973
</row>
1397413974
<row>
1397513975
<entry>
1397613976
<literal><function>pg_stop_backup()</function></literal>
1397713977
</entry>
1397813978
<entry><type>text</type></entry>
13979-
<entry>Finish performing on-line backup (restricted to superusers)</entry>
13979+
<entry>Finish performing on-line backup (restricted to superusers or replication roles)</entry>
1398013980
</row>
1398113981
<row>
1398213982
<entry>

‎doc/src/sgml/high-availability.sgml‎

Lines changed: 20 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -636,8 +636,8 @@ protocol to make nodes agree on a serializable transactional order.
636636
<para>
637637
If you want to use streaming replication, set up authentication on the
638638
primary server to allow replication connections from the standby
639-
server(s); that is, provide a suitable entry or entries in
640-
<filename>pg_hba.conf</> with the database field set to
639+
server(s); that is,create a role andprovide a suitable entry or
640+
entries in<filename>pg_hba.conf</> with the database field set to
641641
<literal>replication</>. Also ensure <varname>max_wal_senders</> is set
642642
to a sufficiently large value in the configuration file of the primary
643643
server.
@@ -796,15 +796,28 @@ archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'
796796
It is very important that the access privileges for replication be set up
797797
so that only trusted users can read the WAL stream, because it is
798798
easy to extract privileged information from it. Standby servers must
799-
authenticate to the primary as a superuser account.
800-
So a role with the <literal>SUPERUSER</> and <literal>LOGIN</>
801-
privileges needs to be created on the primary.
799+
authenticate to the primary as an account that has the
800+
<literal>REPLICATION</> privilege. So a role with the
801+
<literal>REPLICATION</> and <literal>LOGIN</> privileges needs to be
802+
created on the primary.
802803
</para>
804+
805+
<note>
806+
<para>
807+
It is recommended that a dedicated user account is used for replication.
808+
While it is possible to add the <literal>REPLICATION</> privilege to
809+
a superuser account for the purporses of replication, this is not
810+
recommended. While <literal>REPLICATION</> privilege gives very high
811+
permissions, it does not allow the user to modify any data on the
812+
primary system, which the <literal>SUPERUSER</> privilege does.
813+
</para>
814+
</note>
815+
803816
<para>
804817
Client authentication for replication is controlled by a
805818
<filename>pg_hba.conf</> record specifying <literal>replication</> in the
806819
<replaceable>database</> field. For example, if the standby is running on
807-
host IP <literal>192.168.1.100</> and thesuperuser's name for replication
820+
host IP <literal>192.168.1.100</> and theaccount name for replication
808821
is <literal>foo</>, the administrator can add the following line to the
809822
<filename>pg_hba.conf</> file on the primary:
810823

@@ -823,7 +836,7 @@ host replication foo 192.168.1.100/32 md5
823836
standby (specify <literal>replication</> in the <replaceable>database</>
824837
field).
825838
For example, if the primary is running on host IP <literal>192.168.1.50</>,
826-
port <literal>5432</literal>, thesuperuser's name for replication is
839+
port <literal>5432</literal>, theaccount name for replication is
827840
<literal>foo</>, and the password is <literal>foopass</>, the administrator
828841
can add the following line to the <filename>recovery.conf</> file on the
829842
standby:

‎doc/src/sgml/ref/alter_role.sgml‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replace
3131
| CREATEUSER | NOCREATEUSER
3232
| INHERIT | NOINHERIT
3333
| LOGIN | NOLOGIN
34+
| REPLICATION | NOREPLICATION
3435
| CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
3536
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
3637
| VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'
@@ -63,7 +64,7 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <repl
6364
Attributes not mentioned in the command retain their previous settings.
6465
Database superusers can change any of these settings for any role.
6566
Roles having <literal>CREATEROLE</> privilege can change any of these
66-
settings, but only for non-superuser roles.
67+
settings, but only for non-superuserand non-replicationroles.
6768
Ordinary roles can only change their own password.
6869
</para>
6970

@@ -127,6 +128,8 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <repl
127128
<term><literal>NOINHERIT</literal></term>
128129
<term><literal>LOGIN</literal></term>
129130
<term><literal>NOLOGIN</literal></term>
131+
<term><literal>REPLICATION</literal></term>
132+
<term><literal>NOREPLICATION</literal></term>
130133
<term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
131134
<term><literal>PASSWORD</> <replaceable class="parameter">password</replaceable></term>
132135
<term><literal>ENCRYPTED</></term>

‎doc/src/sgml/ref/alter_user.sgml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ ALTER USER <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replace
3131
| CREATEUSER | NOCREATEUSER
3232
| INHERIT | NOINHERIT
3333
| LOGIN | NOLOGIN
34+
| REPLICATION | NOREPLICATION
3435
| CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
3536
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
3637
| VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'

‎doc/src/sgml/ref/create_role.sgml‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
3131
| CREATEUSER | NOCREATEUSER
3232
| INHERIT | NOINHERIT
3333
| LOGIN | NOLOGIN
34+
| REPLICATION | NOREPLICATION
3435
| CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
3536
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
3637
| VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'
@@ -174,6 +175,21 @@ CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
174175
</listitem>
175176
</varlistentry>
176177

178+
<varlistentry>
179+
<term><literal>REPLICATION</literal></term>
180+
<term><literal>NOREPLICATION</literal></term>
181+
<listitem>
182+
<para>
183+
These clauses determine whether a role is allowed to initiate
184+
streaming replication or put the system in and out of backup mode.
185+
A role having the <literal>REPLICATION</> attribute is a very
186+
highly privileged role, and should only be used on roles actually
187+
used for replication. If not specified,
188+
<literal>NOREPLICATION</literal> is the default.
189+
</para>
190+
</listitem>
191+
</varlistentry>
192+
177193
<varlistentry>
178194
<term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
179195
<listitem>

‎doc/src/sgml/ref/create_user.sgml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ CREATE USER <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
3131
| CREATEUSER | NOCREATEUSER
3232
| INHERIT | NOINHERIT
3333
| LOGIN | NOLOGIN
34+
| REPLICATION | NOREPLICATION
3435
| CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
3536
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
3637
| VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'

‎src/backend/access/transam/xlog.c‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8301,10 +8301,10 @@ pg_start_backup(PG_FUNCTION_ARGS)
83018301
structstatstat_buf;
83028302
FILE*fp;
83038303

8304-
if (!superuser())
8304+
if (!superuser()&& !is_authenticated_user_replication_role())
83058305
ereport(ERROR,
83068306
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
8307-
errmsg("must be superuser to run a backup")));
8307+
errmsg("must be superuseror replication roleto run a backup")));
83088308

83098309
if (RecoveryInProgress())
83108310
ereport(ERROR,
@@ -8493,10 +8493,10 @@ pg_stop_backup(PG_FUNCTION_ARGS)
84938493
intwaits=0;
84948494
boolreported_waiting= false;
84958495

8496-
if (!superuser())
8496+
if (!superuser()&& !is_authenticated_user_replication_role())
84978497
ereport(ERROR,
84988498
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
8499-
(errmsg("must be superuser to run a backup"))));
8499+
(errmsg("must be superuseror replication roleto run a backup"))));
85008500

85018501
if (RecoveryInProgress())
85028502
ereport(ERROR,

‎src/backend/catalog/system_views.sql‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@ CREATE VIEW pg_roles AS
1515
rolcreatedb,
1616
rolcatupdate,
1717
rolcanlogin,
18+
rolreplication,
1819
rolconnlimit,
1920
'********'::textas rolpassword,
2021
rolvaliduntil,
@@ -30,6 +31,7 @@ CREATE VIEW pg_shadow AS
3031
rolcreatedbAS usecreatedb,
3132
rolsuperAS usesuper,
3233
rolcatupdateAS usecatupd,
34+
rolreplicationAS userepl,
3335
rolpasswordAS passwd,
3436
rolvaliduntil::abstimeAS valuntil,
3537
setconfigAS useconfig
@@ -54,6 +56,7 @@ CREATE VIEW pg_user AS
5456
usecreatedb,
5557
usesuper,
5658
usecatupd,
59+
userepl,
5760
'********'::textas passwd,
5861
valuntil,
5962
useconfig

‎src/backend/commands/user.c‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,6 +94,7 @@ CreateRole(CreateRoleStmt *stmt)
9494
boolcreaterole= false;/* Can this user create roles? */
9595
boolcreatedb= false;/* Can the user create databases? */
9696
boolcanlogin= false;/* Can this user login? */
97+
boolisreplication= false;/* Is this a replication role? */
9798
intconnlimit=-1;/* maximum connections allowed */
9899
List*addroleto=NIL;/* roles to make this a member of */
99100
List*rolemembers=NIL;/* roles to be members of this role */
@@ -107,6 +108,7 @@ CreateRole(CreateRoleStmt *stmt)
107108
DefElem*dcreaterole=NULL;
108109
DefElem*dcreatedb=NULL;
109110
DefElem*dcanlogin=NULL;
111+
DefElem*disreplication=NULL;
110112
DefElem*dconnlimit=NULL;
111113
DefElem*daddroleto=NULL;
112114
DefElem*drolemembers=NULL;
@@ -190,6 +192,14 @@ CreateRole(CreateRoleStmt *stmt)
190192
errmsg("conflicting or redundant options")));
191193
dcanlogin=defel;
192194
}
195+
elseif (strcmp(defel->defname,"isreplication")==0)
196+
{
197+
if (disreplication)
198+
ereport(ERROR,
199+
(errcode(ERRCODE_SYNTAX_ERROR),
200+
errmsg("conflicting or redundant options")));
201+
disreplication=defel;
202+
}
193203
elseif (strcmp(defel->defname,"connectionlimit")==0)
194204
{
195205
if (dconnlimit)
@@ -247,6 +257,8 @@ CreateRole(CreateRoleStmt *stmt)
247257
createdb=intVal(dcreatedb->arg)!=0;
248258
if (dcanlogin)
249259
canlogin=intVal(dcanlogin->arg)!=0;
260+
if (disreplication)
261+
isreplication=intVal(disreplication->arg)!=0;
250262
if (dconnlimit)
251263
{
252264
connlimit=intVal(dconnlimit->arg);
@@ -272,6 +284,13 @@ CreateRole(CreateRoleStmt *stmt)
272284
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
273285
errmsg("must be superuser to create superusers")));
274286
}
287+
elseif (isreplication)
288+
{
289+
if (!superuser())
290+
ereport(ERROR,
291+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
292+
errmsg("must be superuser to create replication users")));
293+
}
275294
else
276295
{
277296
if (!have_createrole_privilege())
@@ -341,6 +360,7 @@ CreateRole(CreateRoleStmt *stmt)
341360
/* superuser gets catupdate right by default */
342361
new_record[Anum_pg_authid_rolcatupdate-1]=BoolGetDatum(issuper);
343362
new_record[Anum_pg_authid_rolcanlogin-1]=BoolGetDatum(canlogin);
363+
new_record[Anum_pg_authid_rolreplication-1]=BoolGetDatum(isreplication);
344364
new_record[Anum_pg_authid_rolconnlimit-1]=Int32GetDatum(connlimit);
345365

346366
if (password)
@@ -439,6 +459,7 @@ AlterRole(AlterRoleStmt *stmt)
439459
intcreaterole=-1;/* Can this user create roles? */
440460
intcreatedb=-1;/* Can the user create databases? */
441461
intcanlogin=-1;/* Can this user login? */
462+
intisreplication=-1;/* Is this a replication role? */
442463
intconnlimit=-1;/* maximum connections allowed */
443464
List*rolemembers=NIL;/* roles to be added/removed */
444465
char*validUntil=NULL;/* time the login is valid until */
@@ -450,6 +471,7 @@ AlterRole(AlterRoleStmt *stmt)
450471
DefElem*dcreaterole=NULL;
451472
DefElem*dcreatedb=NULL;
452473
DefElem*dcanlogin=NULL;
474+
DefElem*disreplication=NULL;
453475
DefElem*dconnlimit=NULL;
454476
DefElem*drolemembers=NULL;
455477
DefElem*dvalidUntil=NULL;
@@ -514,6 +536,14 @@ AlterRole(AlterRoleStmt *stmt)
514536
errmsg("conflicting or redundant options")));
515537
dcanlogin=defel;
516538
}
539+
elseif (strcmp(defel->defname,"isreplication")==0)
540+
{
541+
if (disreplication)
542+
ereport(ERROR,
543+
(errcode(ERRCODE_SYNTAX_ERROR),
544+
errmsg("conflicting or redundant options")));
545+
disreplication=defel;
546+
}
517547
elseif (strcmp(defel->defname,"connectionlimit")==0)
518548
{
519549
if (dconnlimit)
@@ -556,6 +586,8 @@ AlterRole(AlterRoleStmt *stmt)
556586
createdb=intVal(dcreatedb->arg);
557587
if (dcanlogin)
558588
canlogin=intVal(dcanlogin->arg);
589+
if (disreplication)
590+
isreplication=intVal(disreplication->arg);
559591
if (dconnlimit)
560592
{
561593
connlimit=intVal(dconnlimit->arg);
@@ -594,12 +626,20 @@ AlterRole(AlterRoleStmt *stmt)
594626
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
595627
errmsg("must be superuser to alter superusers")));
596628
}
629+
elseif (((Form_pg_authid)GETSTRUCT(tuple))->rolreplication||isreplication >=0)
630+
{
631+
if (!superuser())
632+
ereport(ERROR,
633+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
634+
errmsg("must be superuser to alter replication users")));
635+
}
597636
elseif (!have_createrole_privilege())
598637
{
599638
if (!(inherit<0&&
600639
createrole<0&&
601640
createdb<0&&
602641
canlogin<0&&
642+
isreplication<0&&
603643
!dconnlimit&&
604644
!rolemembers&&
605645
!validUntil&&
@@ -685,6 +725,12 @@ AlterRole(AlterRoleStmt *stmt)
685725
new_record_repl[Anum_pg_authid_rolcanlogin-1]= true;
686726
}
687727

728+
if (isreplication >=0)
729+
{
730+
new_record[Anum_pg_authid_rolreplication-1]=BoolGetDatum(isreplication>0);
731+
new_record_repl[Anum_pg_authid_rolreplication-1]= true;
732+
}
733+
688734
if (dconnlimit)
689735
{
690736
new_record[Anum_pg_authid_rolconnlimit-1]=Int32GetDatum(connlimit);

0 commit comments

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