On Mon, Dec 27, 2010 at 16:40, Magnus Hagander <mag...@hagander.net> wrote:
> On Mon, Dec 27, 2010 at 16:33, Tom Lane <t...@sss.pgh.pa.us> wrote:
>> Magnus Hagander <mag...@hagander.net> writes:
>>> On Mon, Dec 27, 2010 at 10:53, Magnus Hagander <mag...@hagander.net> wrote:
>>>> We could quite easily make a replication role *never* be able to
>>>> connect to a non-walsender backend. That would mean that if you set
>>>> your role to WITH REPLICATION, it can *only* be used for replication
>>>> and nothing else (well, you could still SET ROLE to it, but given that
>>>> it's not a superuser (anymore), that doesn't have any security
>>>> implications.
>>
>>> Actually, having implemented that and tested it, I realize that's a
>>> pretty bad idea.
>>
>> OK, so if we're not going to recommend that REPLICATION roles be
>> NOLOGIN, we're back to the original question: should the REPLICATION
>> bit give any other special privileges? I can see the point of allowing
>> such a user to issue pg_start_backup and pg_stop_backup.
>
> Yes, those would definitely be useful.
Updated patch, still pending docs, but otherwise updated: allow
start/stop backup, make sure only superuser can turn on/off the flag,
include in system views, show properly in psql.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***************
*** 8301,8310 **** pg_start_backup(PG_FUNCTION_ARGS)
struct stat stat_buf;
FILE *fp;
! if (!superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser to run a backup")));
if (RecoveryInProgress())
ereport(ERROR,
--- 8301,8310 ----
struct stat stat_buf;
FILE *fp;
! if (!superuser() && !is_authenticated_user_replication_role())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser or replication role to run a backup")));
if (RecoveryInProgress())
ereport(ERROR,
***************
*** 8493,8502 **** pg_stop_backup(PG_FUNCTION_ARGS)
int waits = 0;
bool reported_waiting = false;
! if (!superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser to run a backup"))));
if (RecoveryInProgress())
ereport(ERROR,
--- 8493,8502 ----
int waits = 0;
bool reported_waiting = false;
! if (!superuser() && !is_authenticated_user_replication_role())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser or replication role to run a backup"))));
if (RecoveryInProgress())
ereport(ERROR,
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 15,20 **** CREATE VIEW pg_roles AS
--- 15,21 ----
rolcreatedb,
rolcatupdate,
rolcanlogin,
+ rolreplication,
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
***************
*** 30,35 **** CREATE VIEW pg_shadow AS
--- 31,37 ----
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolcatupdate AS usecatupd,
+ rolreplication AS userepl,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
setconfig AS useconfig
***************
*** 54,59 **** CREATE VIEW pg_user AS
--- 56,62 ----
usecreatedb,
usesuper,
usecatupd,
+ userepl,
'********'::text as passwd,
valuntil,
useconfig
*** 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);
***************
*** 272,277 **** CreateRole(CreateRoleStmt *stmt)
--- 284,296 ----
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to create superusers")));
}
+ else if (isreplication)
+ {
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create replication users")));
+ }
else
{
if (!have_createrole_privilege())
***************
*** 341,346 **** CreateRole(CreateRoleStmt *stmt)
--- 360,366 ----
/* 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)
--- 459,465 ----
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)
--- 471,477 ----
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)
--- 536,549 ----
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)
--- 586,593 ----
createdb = intVal(dcreatedb->arg);
if (dcanlogin)
canlogin = intVal(dcanlogin->arg);
+ if (disreplication)
+ isreplication = intVal(disreplication->arg);
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 594,605 **** AlterRole(AlterRoleStmt *stmt)
--- 626,645 ----
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to alter superusers")));
}
+ else if (((Form_pg_authid) GETSTRUCT(tuple))->rolreplication || isreplication >= 0)
+ {
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to alter replication users")));
+ }
else if (!have_createrole_privilege())
{
if (!(inherit < 0 &&
createrole < 0 &&
createdb < 0 &&
canlogin < 0 &&
+ isreplication < 0 &&
!dconnlimit &&
!rolemembers &&
!validUntil &&
***************
*** 685,690 **** AlterRole(AlterRoleStmt *stmt)
--- 725,736 ----
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/miscinit.c
--- b/src/backend/utils/init/miscinit.c
***************
*** 231,236 **** static int SecurityRestrictionContext = 0;
--- 231,240 ----
static bool SetRoleIsActive = false;
+ /* Remember if the authenticated user is a replication role */
+ static bool AuthenticatedUserIsReplicationRole = false;
+
+
/*
* GetUserId - get the current effective user ID.
*
***************
*** 389,394 **** SetUserIdAndContext(Oid userid, bool sec_def_context)
--- 393,407 ----
/*
+ * Check if the authenticated user is a replication role
+ */
+ bool
+ is_authenticated_user_replication_role(void)
+ {
+ return AuthenticatedUserIsReplicationRole;
+ }
+
+ /*
* Initialize user identity during normal backend startup
*/
void
***************
*** 418,423 **** InitializeSessionUserId(const char *rolename)
--- 431,437 ----
AuthenticatedUserId = roleid;
AuthenticatedUserIsSuperuser = rform->rolsuper;
+ AuthenticatedUserIsReplicationRole = rform->rolreplication;
/* This sets OuterUserId/CurrentUserId too */
SetSessionUserId(roleid, AuthenticatedUserIsSuperuser);
*** a/src/backend/utils/init/postinit.c
--- b/src/backend/utils/init/postinit.c
***************
*** 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)
--- 658,668 ----
{
Assert(!bootstrap);
! /* must have authenticated as a replication role */
! if (!is_authenticated_user_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/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***************
*** 2195,2200 **** describeRoles(const char *pattern, bool verbose)
--- 2195,2204 ----
appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
ncols++;
}
+ if (pset.sversion >= 90100)
+ {
+ appendPQExpBufferStr(&buf,"\n, r.rolreplication");
+ }
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
***************
*** 2254,2259 **** describeRoles(const char *pattern, bool verbose)
--- 2258,2267 ----
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
+ if (pset.sversion >= 90100)
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
+ add_role_attribute(&buf, _("Replication"));
+
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
{
*** 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/miscadmin.h
--- b/src/include/miscadmin.h
***************
*** 357,362 **** extern void ValidatePgVersion(const char *path);
--- 357,363 ----
extern void process_shared_preload_libraries(void);
extern void process_local_preload_libraries(void);
extern void pg_bindtextdomain(const char *domain);
+ extern bool is_authenticated_user_replication_role(void);
/* in access/transam/xlog.c */
extern bool BackupInProgress(void);
*** 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
