🌐 AI搜索 & 代理 主页
Skip to content

Commit 9b8aff8

Browse files
mhagandermhagander
committed
Add REPLICATION privilege for ROLEs
This privilege is required to do Streaming Replication, instead of superuser, making it possible to set up a SR slave that doesn't have write permissions on the master. Superuser privileges do NOT override this check, so in order to use the default superuser account for replication it must be explicitly granted the REPLICATION permissions. This is backwards incompatible change, in the interest of higher default security.
1 parent f2ba1e9 commit 9b8aff8

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 and provide 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 the superuser's name for replication
820+
host IP <literal>192.168.1.100</> and the account 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>, the superuser's name for replication is
839+
port <literal>5432</literal>, the account 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-superuser and non-replication roles.
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
struct stat stat_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 superuser or replication role to run a backup")));
83088308

83098309
if (RecoveryInProgress())
83108310
ereport(ERROR,
@@ -8493,10 +8493,10 @@ pg_stop_backup(PG_FUNCTION_ARGS)
84938493
int waits = 0;
84948494
bool reported_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 superuser or replication role to 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
'********'::text as rolpassword,
2021
rolvaliduntil,
@@ -30,6 +31,7 @@ CREATE VIEW pg_shadow AS
3031
rolcreatedb AS usecreatedb,
3132
rolsuper AS usesuper,
3233
rolcatupdate AS usecatupd,
34+
rolreplication AS userepl,
3335
rolpassword AS passwd,
3436
rolvaliduntil::abstime AS valuntil,
3537
setconfig AS useconfig
@@ -54,6 +56,7 @@ CREATE VIEW pg_user AS
5456
usecreatedb,
5557
usesuper,
5658
usecatupd,
59+
userepl,
5760
'********'::text as 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
bool createrole = false; /* Can this user create roles? */
9595
bool createdb = false; /* Can the user create databases? */
9696
bool canlogin = false; /* Can this user login? */
97+
bool isreplication = false; /* Is this a replication role? */
9798
int connlimit = -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+
else if (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
else if (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+
else if (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
int createrole = -1; /* Can this user create roles? */
440460
int createdb = -1; /* Can the user create databases? */
441461
int canlogin = -1; /* Can this user login? */
462+
int isreplication = -1; /* Is this a replication role? */
442463
int connlimit = -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+
else if (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
else if (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+
else if (((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
else if (!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)