Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.
Obviously the patch needs docs and some system views updates, which I
will add later. But I wanted to post what I have so far for a quick
review to confirm whether I'm on the right track or not... How it
works should be rather obvious - adds a "WITH
REPLICATION/NOREPLICATION" to the create and alter role commands, and
then check this when a connection attempts to start the walsender.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
*** a/src/backend/commands/user.c
--- b/src/backend/commands/user.c
***************
*** 94,99 **** CreateRole(CreateRoleStmt *stmt)
--- 94,100 ----
bool createrole = false; /* Can this user create roles? */
bool createdb = false; /* Can the user create databases? */
bool canlogin = false; /* Can this user login? */
+ bool isreplication = false; /* Is this a replication role? */
int connlimit = -1; /* maximum connections allowed */
List *addroleto = NIL; /* roles to make this a member of */
List *rolemembers = NIL; /* roles to be members of this role */
***************
*** 107,112 **** CreateRole(CreateRoleStmt *stmt)
--- 108,114 ----
DefElem *dcreaterole = NULL;
DefElem *dcreatedb = NULL;
DefElem *dcanlogin = NULL;
+ DefElem *disreplication = NULL;
DefElem *dconnlimit = NULL;
DefElem *daddroleto = NULL;
DefElem *drolemembers = NULL;
***************
*** 190,195 **** CreateRole(CreateRoleStmt *stmt)
--- 192,205 ----
errmsg("conflicting or redundant options")));
dcanlogin = defel;
}
+ else if (strcmp(defel->defname, "isreplication") == 0)
+ {
+ if (disreplication)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ disreplication = defel;
+ }
else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
***************
*** 247,252 **** CreateRole(CreateRoleStmt *stmt)
--- 257,264 ----
createdb = intVal(dcreatedb->arg) != 0;
if (dcanlogin)
canlogin = intVal(dcanlogin->arg) != 0;
+ if (disreplication)
+ isreplication = intVal(disreplication->arg) != 0;
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 341,346 **** CreateRole(CreateRoleStmt *stmt)
--- 353,359 ----
/* superuser gets catupdate right by default */
new_record[Anum_pg_authid_rolcatupdate - 1] = BoolGetDatum(issuper);
new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
+ new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
if (password)
***************
*** 439,444 **** AlterRole(AlterRoleStmt *stmt)
--- 452,458 ----
int createrole = -1; /* Can this user create roles? */
int createdb = -1; /* Can the user create databases? */
int canlogin = -1; /* Can this user login? */
+ int isreplication = -1; /* Is this a replication role? */
int connlimit = -1; /* maximum connections allowed */
List *rolemembers = NIL; /* roles to be added/removed */
char *validUntil = NULL; /* time the login is valid until */
***************
*** 450,455 **** AlterRole(AlterRoleStmt *stmt)
--- 464,470 ----
DefElem *dcreaterole = NULL;
DefElem *dcreatedb = NULL;
DefElem *dcanlogin = NULL;
+ DefElem *disreplication = NULL;
DefElem *dconnlimit = NULL;
DefElem *drolemembers = NULL;
DefElem *dvalidUntil = NULL;
***************
*** 514,519 **** AlterRole(AlterRoleStmt *stmt)
--- 529,542 ----
errmsg("conflicting or redundant options")));
dcanlogin = defel;
}
+ else if (strcmp(defel->defname, "isreplication") == 0)
+ {
+ if (disreplication)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ disreplication = defel;
+ }
else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
***************
*** 556,561 **** AlterRole(AlterRoleStmt *stmt)
--- 579,586 ----
createdb = intVal(dcreatedb->arg);
if (dcanlogin)
canlogin = intVal(dcanlogin->arg);
+ if (disreplication)
+ isreplication = intVal(disreplication->arg);
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 600,605 **** AlterRole(AlterRoleStmt *stmt)
--- 625,631 ----
createrole < 0 &&
createdb < 0 &&
canlogin < 0 &&
+ isreplication < 0 &&
!dconnlimit &&
!rolemembers &&
!validUntil &&
***************
*** 685,690 **** AlterRole(AlterRoleStmt *stmt)
--- 711,722 ----
new_record_repl[Anum_pg_authid_rolcanlogin - 1] = true;
}
+ if (isreplication >= 0)
+ {
+ new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication > 0);
+ new_record_repl[Anum_pg_authid_rolreplication - 1] = true;
+ }
+
if (dconnlimit)
{
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 510,517 **** static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
! NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER
! NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
--- 510,518 ----
MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
! NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOREPLICATION_P
! NOSUPERUSER NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
! NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
***************
*** 523,530 **** static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
QUOTE
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
! RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
! RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
--- 524,532 ----
QUOTE
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
! RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA REPLICATION_P
! RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK
! ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
***************
*** 864,869 **** AlterOptRoleElem:
--- 866,879 ----
{
$$ = makeDefElem("canlogin", (Node *)makeInteger(FALSE));
}
+ | REPLICATION_P
+ {
+ $$ = makeDefElem("isreplication", (Node *)makeInteger(TRUE));
+ }
+ | NOREPLICATION_P
+ {
+ $$ = makeDefElem("isreplication", (Node *)makeInteger(FALSE));
+ }
| CONNECTION LIMIT SignedIconst
{
$$ = makeDefElem("connectionlimit", (Node *)makeInteger($3));
***************
*** 11288,11293 **** unreserved_keyword:
--- 11298,11304 ----
| NOCREATEUSER
| NOINHERIT
| NOLOGIN_P
+ | NOREPLICATION_P
| NOSUPERUSER
| NOTHING
| NOTIFY
***************
*** 11330,11335 **** unreserved_keyword:
--- 11341,11347 ----
| REPEATABLE
| REPLACE
| REPLICA
+ | REPLICATION_P
| RESET
| RESTART
| RESTRICT
*** a/src/backend/utils/init/postinit.c
--- b/src/backend/utils/init/postinit.c
***************
*** 60,65 ****
--- 60,66 ----
static HeapTuple GetDatabaseTuple(const char *dbname);
static HeapTuple GetDatabaseTupleByOid(Oid dboid);
+ static bool is_replication_role(void);
static void PerformAuthentication(Port *port);
static void CheckMyDatabase(const char *name, bool am_superuser);
static void InitCommunication(void);
***************
*** 166,171 **** GetDatabaseTupleByOid(Oid dboid)
--- 167,189 ----
return tuple;
}
+ /*
+ * is_replication_role -- check if the role is a replication role
+ */
+ static bool
+ is_replication_role(void)
+ {
+ bool result = false;
+ HeapTuple utup;
+
+ utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(GetUserId()));
+ if (HeapTupleIsValid(utup))
+ {
+ result = ((Form_pg_authid) GETSTRUCT(utup))->rolreplication;
+ ReleaseSysCache(utup);
+ }
+ return result;
+ }
/*
* PerformAuthentication -- authenticate a remote client
***************
*** 658,668 **** InitPostgres(const char *in_dbname, Oid dboid, const char *username,
{
Assert(!bootstrap);
! /* must have authenticated as a superuser */
! if (!am_superuser)
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser to start walsender")));
/* process any options passed in the startup packet */
if (MyProcPort != NULL)
--- 676,686 ----
{
Assert(!bootstrap);
! /* must have authenticated as a replication role */
! if (!is_replication_role())
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be replication role to start walsender")));
/* process any options passed in the startup packet */
if (MyProcPort != NULL)
*** a/src/include/catalog/pg_authid.h
--- b/src/include/catalog/pg_authid.h
***************
*** 51,56 **** CATALOG(pg_authid,1260) BKI_SHARED_RELATION BKI_ROWTYPE_OID(2842) BKI_SCHEMA_MAC
--- 51,57 ----
bool rolcreatedb; /* allowed to create databases? */
bool rolcatupdate; /* allowed to alter catalogs manually? */
bool rolcanlogin; /* allowed to log in as session user? */
+ bool rolreplication; /* role used for streaming replication */
int4 rolconnlimit; /* max connections allowed (-1=no limit) */
/* remaining fields may be null; use heap_getattr to read them! */
***************
*** 72,78 **** typedef FormData_pg_authid *Form_pg_authid;
* compiler constants for pg_authid
* ----------------
*/
! #define Natts_pg_authid 10
#define Anum_pg_authid_rolname 1
#define Anum_pg_authid_rolsuper 2
#define Anum_pg_authid_rolinherit 3
--- 73,79 ----
* compiler constants for pg_authid
* ----------------
*/
! #define Natts_pg_authid 11
#define Anum_pg_authid_rolname 1
#define Anum_pg_authid_rolsuper 2
#define Anum_pg_authid_rolinherit 3
***************
*** 80,88 **** typedef FormData_pg_authid *Form_pg_authid;
#define Anum_pg_authid_rolcreatedb 5
#define Anum_pg_authid_rolcatupdate 6
#define Anum_pg_authid_rolcanlogin 7
! #define Anum_pg_authid_rolconnlimit 8
! #define Anum_pg_authid_rolpassword 9
! #define Anum_pg_authid_rolvaliduntil 10
/* ----------------
* initial contents of pg_authid
--- 81,90 ----
#define Anum_pg_authid_rolcreatedb 5
#define Anum_pg_authid_rolcatupdate 6
#define Anum_pg_authid_rolcanlogin 7
! #define Anum_pg_authid_rolreplication 8
! #define Anum_pg_authid_rolconnlimit 9
! #define Anum_pg_authid_rolpassword 10
! #define Anum_pg_authid_rolvaliduntil 11
/* ----------------
* initial contents of pg_authid
***************
*** 91,97 **** typedef FormData_pg_authid *Form_pg_authid;
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
--- 93,99 ----
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t f -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 250,255 **** PG_KEYWORD("nocreateuser", NOCREATEUSER, UNRESERVED_KEYWORD)
--- 250,256 ----
PG_KEYWORD("noinherit", NOINHERIT, UNRESERVED_KEYWORD)
PG_KEYWORD("nologin", NOLOGIN_P, UNRESERVED_KEYWORD)
PG_KEYWORD("none", NONE, COL_NAME_KEYWORD)
+ PG_KEYWORD("noreplication", NOREPLICATION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("nosuperuser", NOSUPERUSER, UNRESERVED_KEYWORD)
PG_KEYWORD("not", NOT, RESERVED_KEYWORD)
PG_KEYWORD("nothing", NOTHING, UNRESERVED_KEYWORD)
***************
*** 313,318 **** PG_KEYWORD("rename", RENAME, UNRESERVED_KEYWORD)
--- 314,320 ----
PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD)
PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD)
+ PG_KEYWORD("replication", REPLICATION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
