All, Attached is a patch that proposes the following additional role attributes for review:
* ONLINE_BACKUP - allows role to perform backup operations - originally proposed as BACKUP - due to concern for the use of that term in relation to other potential backup related permissions this form is in line with the documentation as it describes the affected backup operations as being 'online backups'. - applies only to the originally proposed backup functions. * XLOG_REPLAY - allows role to perform pause and resume on xlog_replay operations ('pg_xlog_replay_pause' and 'pg_xlog_replay_resume') - following the recommendation from Stephen and Magnus. * LOG - allows role to rotate log files - remains broad enough to consider future log related operations * MONITOR - allows role to view pg_stat_* details (as originally proposed) * SIGNAL - allows role to signal backend processes (as originally proposed) The documentation still needs to be updated. If this these attributes and the capabilities they provide are acceptable, then I'll begin moving forward on making those updates as well. Regarding the discussion on a DUMP/READONLY permission. I believe that topic needs much further discussion and decided it is probably best to keep it as a separate patch/effort. I'd certainly be willing to continue that discussion and assist in moving any related effort forward, therefore, please let me know if there is anything I can do to help. Thanks, Adam -- Adam Brightwell - adam.brightw...@crunchydatasolutions.com Database Engineer - www.crunchydatasolutions.com
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c new file mode 100644 index 2179bf7..aaf13c1 *** a/src/backend/access/transam/xlogfuncs.c --- b/src/backend/access/transam/xlogfuncs.c *************** *** 27,32 **** --- 27,33 ---- #include "miscadmin.h" #include "replication/walreceiver.h" #include "storage/smgr.h" + #include "utils/acl.h" #include "utils/builtins.h" #include "utils/numeric.h" #include "utils/guc.h" *************** pg_start_backup(PG_FUNCTION_ARGS) *** 54,63 **** backupidstr = text_to_cstring(backupid); ! if (!superuser() && !has_rolreplication(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("must be superuser or replication role to run a backup"))); startpoint = do_pg_start_backup(backupidstr, fast, NULL, NULL); --- 55,65 ---- backupidstr = text_to_cstring(backupid); ! if (!has_replication_privilege(GetUserId()) ! && !has_online_backup_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("must be superuser, replication role or online backup role to run a backup"))); startpoint = do_pg_start_backup(backupidstr, fast, NULL, NULL); *************** pg_stop_backup(PG_FUNCTION_ARGS) *** 82,91 **** { XLogRecPtr stoppoint; ! if (!superuser() && !has_rolreplication(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! (errmsg("must be superuser or replication role to run a backup")))); stoppoint = do_pg_stop_backup(NULL, true, NULL); --- 84,94 ---- { XLogRecPtr stoppoint; ! if (!has_replication_privilege(GetUserId()) ! && !has_online_backup_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("must be superuser, replication role or online backup role to run a backup"))); stoppoint = do_pg_stop_backup(NULL, true, NULL); *************** pg_switch_xlog(PG_FUNCTION_ARGS) *** 100,109 **** { XLogRecPtr switchpoint; ! if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! (errmsg("must be superuser to switch transaction log files")))); if (RecoveryInProgress()) ereport(ERROR, --- 103,112 ---- { XLogRecPtr switchpoint; ! if (!has_online_backup_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("must be superuser or online backup role to switch transaction log files"))); if (RecoveryInProgress()) ereport(ERROR, *************** pg_create_restore_point(PG_FUNCTION_ARGS *** 129,138 **** char *restore_name_str; XLogRecPtr restorepoint; ! if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! (errmsg("must be superuser to create a restore point")))); if (RecoveryInProgress()) ereport(ERROR, --- 132,141 ---- char *restore_name_str; XLogRecPtr restorepoint; ! if (!has_online_backup_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! (errmsg("must be superuser or online backup role to create a restore point")))); if (RecoveryInProgress()) ereport(ERROR, *************** pg_xlogfile_name(PG_FUNCTION_ARGS) *** 338,347 **** Datum pg_xlog_replay_pause(PG_FUNCTION_ARGS) { ! if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! (errmsg("must be superuser to control recovery")))); if (!RecoveryInProgress()) ereport(ERROR, --- 341,350 ---- Datum pg_xlog_replay_pause(PG_FUNCTION_ARGS) { ! if (!has_xlog_replay_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! (errmsg("must be superuser or xlog replay role to control recovery")))); if (!RecoveryInProgress()) ereport(ERROR, *************** pg_xlog_replay_pause(PG_FUNCTION_ARGS) *** 360,369 **** Datum pg_xlog_replay_resume(PG_FUNCTION_ARGS) { ! if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! (errmsg("must be superuser to control recovery")))); if (!RecoveryInProgress()) ereport(ERROR, --- 363,372 ---- Datum pg_xlog_replay_resume(PG_FUNCTION_ARGS) { ! if (!has_xlog_replay_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! (errmsg("must be superuser or xlog replay role to control recovery")))); if (!RecoveryInProgress()) ereport(ERROR, diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c new file mode 100644 index 1e3888e..6ad8294 *** a/src/backend/catalog/aclchk.c --- b/src/backend/catalog/aclchk.c *************** has_createrole_privilege(Oid roleid) *** 5080,5085 **** --- 5080,5110 ---- return result; } + /* + * Check whether specified role has REPLICATION privilege (or is a superuser) + */ + bool + has_replication_privilege(Oid roleid) + { + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolreplication; + ReleaseSysCache(utup); + } + return result; + } + + /* + * Check whether specified role has BYPASSRLS privilege (or is a superuser) + */ bool has_bypassrls_privilege(Oid roleid) { *************** has_bypassrls_privilege(Oid roleid) *** 5097,5102 **** --- 5122,5239 ---- ReleaseSysCache(utup); } return result; + } + + /* + * Check whether specified role has BACKUP privilege (or is a superuser) + */ + bool + has_online_backup_privilege(Oid roleid) + { + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolonlinebackup; + ReleaseSysCache(utup); + } + + return result; + } + + /* + * Check whether specified role has XLOGREPLAY privilege (or is a superuser) + */ + bool + has_xlog_replay_privilege(Oid roleid) + { + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolxlogreplay; + ReleaseSysCache(utup); + } + + return result; + } + + /* + * Check whether specified role has LOG privilege (or is a superuser) + */ + bool + has_log_privilege(Oid roleid) + { + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rollog; + ReleaseSysCache(utup); + } + return result; + } + + /* + * Check whether specified role has MONITOR privilege (or is a superuser) + */ + bool + has_monitor_privilege(Oid roleid) + { + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolmonitor; + ReleaseSysCache(utup); + } + return result; + } + + /* + * Check whether specified role has SIGNAL privilege (or is a superuser) + */ + bool + has_signal_privilege(Oid roleid) + { + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolsignal; + ReleaseSysCache(utup); + } + return result; } /* diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c new file mode 100644 index 2210eed..e067516 *** a/src/backend/commands/user.c --- b/src/backend/commands/user.c *************** static void DelRoleMems(const char *role *** 55,69 **** List *memberNames, List *memberIds, bool admin_opt); - - /* Check if current user has createrole privileges */ - static bool - have_createrole_privilege(void) - { - return has_createrole_privilege(GetUserId()); - } - - /* * CREATE ROLE */ --- 55,60 ---- *************** CreateRole(CreateRoleStmt *stmt) *** 88,93 **** --- 79,89 ---- bool canlogin = false; /* Can this user login? */ bool isreplication = false; /* Is this a replication role? */ bool bypassrls = false; /* Is this a row security enabled role? */ + bool onlinebackup = false; + bool xlogreplay = false; + bool log = false; + bool monitor = false; + bool signal = false; 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 */ *************** CreateRole(CreateRoleStmt *stmt) *** 108,113 **** --- 104,114 ---- DefElem *dadminmembers = NULL; DefElem *dvalidUntil = NULL; DefElem *dbypassRLS = NULL; + DefElem *donlinebackup = NULL; + DefElem *dxlogreplay = NULL; + DefElem *dlog = NULL; + DefElem *dmonitor = NULL; + DefElem *dsignal = NULL; /* The defaults can vary depending on the original statement type */ switch (stmt->stmt_type) *************** CreateRole(CreateRoleStmt *stmt) *** 242,247 **** --- 243,288 ---- errmsg("conflicting or redundant options"))); dbypassRLS = defel; } + else if (strcmp(defel->defname, "onlinebackup") == 0) + { + if (donlinebackup) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + donlinebackup = defel; + } + else if (strcmp(defel->defname, "xlogreplay") == 0) + { + if (dxlogreplay) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dxlogreplay = defel; + } + else if (strcmp(defel->defname, "log") == 0) + { + if (dlog) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dlog = defel; + } + else if (strcmp(defel->defname, "monitor") == 0) + { + if (dmonitor) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dmonitor = defel; + } + else if (strcmp(defel->defname, "signal") == 0) + { + if (dsignal) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dsignal = defel; + } else elog(ERROR, "option \"%s\" not recognized", defel->defname); *************** CreateRole(CreateRoleStmt *stmt) *** 279,284 **** --- 320,335 ---- validUntil = strVal(dvalidUntil->arg); if (dbypassRLS) bypassrls = intVal(dbypassRLS->arg) != 0; + if (donlinebackup) + onlinebackup = intVal(donlinebackup->arg) != 0; + if (dxlogreplay) + xlogreplay = intVal(dxlogreplay->arg) != 0; + if (dlog) + log = intVal(dlog->arg) != 0; + if (dmonitor) + monitor = intVal(dmonitor->arg) != 0; + if (dsignal) + signal = intVal(dsignal->arg) != 0; /* Check some permissions first */ if (issuper) *************** CreateRole(CreateRoleStmt *stmt) *** 304,310 **** } else { ! if (!have_createrole_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to create role"))); --- 355,361 ---- } else { ! if (!has_createrole_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to create role"))); *************** CreateRole(CreateRoleStmt *stmt) *** 395,400 **** --- 446,456 ---- new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null; new_record[Anum_pg_authid_rolbypassrls - 1] = BoolGetDatum(bypassrls); + new_record[Anum_pg_authid_rolonlinebackup - 1] = BoolGetDatum(onlinebackup); + new_record[Anum_pg_authid_rolxlogreplay - 1] = BoolGetDatum(xlogreplay); + new_record[Anum_pg_authid_rollog - 1] = BoolGetDatum(log); + new_record[Anum_pg_authid_rolmonitor - 1] = BoolGetDatum(monitor); + new_record[Anum_pg_authid_rolsignal - 1] = BoolGetDatum(signal); tuple = heap_form_tuple(pg_authid_dsc, new_record, new_record_nulls); *************** AlterRole(AlterRoleStmt *stmt) *** 496,501 **** --- 552,562 ---- Datum validUntil_datum; /* same, as timestamptz Datum */ bool validUntil_null; bool bypassrls = -1; + bool onlinebackup = -1; + bool xlogreplay = -1; + bool log = -1; + bool monitor = -1; + bool signal = -1; DefElem *dpassword = NULL; DefElem *dissuper = NULL; DefElem *dinherit = NULL; *************** AlterRole(AlterRoleStmt *stmt) *** 507,512 **** --- 568,578 ---- DefElem *drolemembers = NULL; DefElem *dvalidUntil = NULL; DefElem *dbypassRLS = NULL; + DefElem *donlinebackup = NULL; + DefElem *dxlogreplay = NULL; + DefElem *dlog = NULL; + DefElem *dmonitor = NULL; + DefElem *dsignal = NULL; Oid roleid; /* Extract options from the statement node tree */ *************** AlterRole(AlterRoleStmt *stmt) *** 609,614 **** --- 675,720 ---- errmsg("conflicting or redundant options"))); dbypassRLS = defel; } + else if (strcmp(defel->defname, "onlinebackup") == 0) + { + if (donlinebackup) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + donlinebackup = defel; + } + else if (strcmp(defel->defname, "xlogreplay") == 0) + { + if (dxlogreplay) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dxlogreplay = defel; + } + else if (strcmp(defel->defname, "log") == 0) + { + if (dlog) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dlog = defel; + } + else if (strcmp(defel->defname, "monitor") == 0) + { + if (dmonitor) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dmonitor = defel; + } + else if (strcmp(defel->defname, "signal") == 0) + { + if (dsignal) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dsignal = defel; + } else elog(ERROR, "option \"%s\" not recognized", defel->defname); *************** AlterRole(AlterRoleStmt *stmt) *** 642,647 **** --- 748,763 ---- validUntil = strVal(dvalidUntil->arg); if (dbypassRLS) bypassrls = intVal(dbypassRLS->arg); + if (donlinebackup) + onlinebackup = intVal(donlinebackup->arg); + if (dxlogreplay) + xlogreplay = intVal(dxlogreplay->arg); + if (dlog) + log = intVal(dlog->arg); + if (dmonitor) + monitor = intVal(dmonitor->arg); + if (dsignal) + signal = intVal(dsignal->arg); /* * Scan the pg_authid relation to be certain the user exists. *************** AlterRole(AlterRoleStmt *stmt) *** 682,694 **** (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to change bypassrls attribute"))); } ! else if (!have_createrole_privilege()) { if (!(inherit < 0 && createrole < 0 && createdb < 0 && canlogin < 0 && isreplication < 0 && !dconnlimit && !rolemembers && !validUntil && --- 798,815 ---- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to change bypassrls attribute"))); } ! else if (!has_createrole_privilege(GetUserId())) { if (!(inherit < 0 && createrole < 0 && createdb < 0 && canlogin < 0 && isreplication < 0 && + onlinebackup < 0 && + xlogreplay < 0 && + log < 0 && + monitor < 0 && + signal < 0 && !dconnlimit && !rolemembers && !validUntil && *************** AlterRole(AlterRoleStmt *stmt) *** 821,826 **** --- 942,977 ---- new_record_repl[Anum_pg_authid_rolbypassrls - 1] = true; } + if (onlinebackup >= 0) + { + new_record[Anum_pg_authid_rolonlinebackup - 1] = BoolGetDatum(onlinebackup > 0); + new_record_repl[Anum_pg_authid_rolonlinebackup - 1] = true; + } + + if (xlogreplay >= 0) + { + new_record[Anum_pg_authid_rolxlogreplay - 1] = BoolGetDatum(xlogreplay > 0); + new_record_repl[Anum_pg_authid_rolxlogreplay - 1] = true; + } + + if (log >= 0) + { + new_record[Anum_pg_authid_rollog - 1] = BoolGetDatum(log > 0); + new_record_repl[Anum_pg_authid_rollog - 1] = true; + } + + if (monitor >= 0) + { + new_record[Anum_pg_authid_rolmonitor - 1] = BoolGetDatum(monitor > 0); + new_record_repl[Anum_pg_authid_rolmonitor - 1] = true; + } + + if (signal >= 0) + { + new_record[Anum_pg_authid_rolsignal - 1] = BoolGetDatum(signal > 0); + new_record_repl[Anum_pg_authid_rolsignal - 1] = true; + } + new_tuple = heap_modify_tuple(tuple, pg_authid_dsc, new_record, new_record_nulls, new_record_repl); simple_heap_update(pg_authid_rel, &tuple->t_self, new_tuple); *************** AlterRoleSet(AlterRoleSetStmt *stmt) *** 898,904 **** } else { ! if (!have_createrole_privilege() && HeapTupleGetOid(roletuple) != GetUserId()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), --- 1049,1055 ---- } else { ! if (!has_createrole_privilege(GetUserId()) && HeapTupleGetOid(roletuple) != GetUserId()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), *************** DropRole(DropRoleStmt *stmt) *** 951,957 **** pg_auth_members_rel; ListCell *item; ! if (!have_createrole_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to drop role"))); --- 1102,1108 ---- pg_auth_members_rel; ListCell *item; ! if (!has_createrole_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to drop role"))); *************** RenameRole(const char *oldname, const ch *** 1182,1188 **** } else { ! if (!have_createrole_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to rename role"))); --- 1333,1339 ---- } else { ! if (!has_createrole_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to rename role"))); *************** AddRoleMems(const char *rolename, Oid ro *** 1409,1415 **** } else { ! if (!have_createrole_privilege() && !is_admin_of_role(grantorId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), --- 1560,1566 ---- } else { ! if (!has_createrole_privilege(GetUserId()) && !is_admin_of_role(grantorId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), *************** DelRoleMems(const char *rolename, Oid ro *** 1555,1561 **** } else { ! if (!have_createrole_privilege() && !is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), --- 1706,1712 ---- } else { ! if (!has_createrole_privilege(GetUserId()) && !is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y new file mode 100644 index 36dac29..ba35d61 *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** AlterOptRoleElem: *** 977,982 **** --- 977,1002 ---- */ $$ = makeDefElem("inherit", (Node *)makeInteger(FALSE)); } + else if (strcmp($1, "online_backup") == 0) + $$ = makeDefElem("onlinebackup", (Node *)makeInteger(TRUE)); + else if (strcmp($1, "noonline_backup") == 0) + $$ = makeDefElem("onlinebackup", (Node *)makeInteger(FALSE)); + else if (strcmp($1, "xlog_replay") == 0) + $$ = makeDefElem("xlogreplay", (Node *)makeInteger(TRUE)); + else if (strcmp($1, "noxlog_replay") == 0) + $$ = makeDefElem("xlogreplay", (Node *)makeInteger(FALSE)); + else if (strcmp($1, "log") == 0) + $$ = makeDefElem("log", (Node *)makeInteger(TRUE)); + else if (strcmp($1, "nolog") == 0) + $$ = makeDefElem("log", (Node *)makeInteger(FALSE)); + else if (strcmp($1, "monitor") == 0) + $$ = makeDefElem("monitor", (Node *)makeInteger(TRUE)); + else if (strcmp($1, "nomonitor") == 0) + $$ = makeDefElem("monitor", (Node *)makeInteger(FALSE)); + else if (strcmp($1, "signal") == 0) + $$ = makeDefElem("signal", (Node *)makeInteger(TRUE)); + else if (strcmp($1, "nosignal") == 0) + $$ = makeDefElem("signal", (Node *)makeInteger(FALSE)); else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c new file mode 100644 index 3be5263..b7e7947 *** a/src/backend/replication/logical/logicalfuncs.c --- b/src/backend/replication/logical/logicalfuncs.c *************** *** 29,34 **** --- 29,35 ---- #include "mb/pg_wchar.h" + #include "utils/acl.h" #include "utils/array.h" #include "utils/builtins.h" #include "utils/inval.h" *************** XLogRead(char *buf, TimeLineID tli, XLog *** 202,216 **** } } - static void - check_permissions(void) - { - if (!superuser() && !has_rolreplication(GetUserId())) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - (errmsg("must be superuser or replication role to use replication slots")))); - } - /* * read_page callback for logical decoding contexts. * --- 203,208 ---- *************** pg_logical_slot_get_changes_guts(Functio *** 324,330 **** if (get_call_result_type(fcinfo, NULL, &p->tupdesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); ! check_permissions(); CheckLogicalDecodingRequirements(); --- 316,325 ---- if (get_call_result_type(fcinfo, NULL, &p->tupdesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); ! if (!has_replication_privilege(GetUserId())) ! ereport(ERROR, ! (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("must be superuser or replication role to use replication slots"))); CheckLogicalDecodingRequirements(); diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c new file mode 100644 index f31925d..35e1c11 *** a/src/backend/replication/slotfuncs.c --- b/src/backend/replication/slotfuncs.c *************** *** 20,37 **** #include "replication/slot.h" #include "replication/logical.h" #include "replication/logicalfuncs.h" #include "utils/builtins.h" #include "utils/pg_lsn.h" - static void - check_permissions(void) - { - if (!superuser() && !has_rolreplication(GetUserId())) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - (errmsg("must be superuser or replication role to use replication slots")))); - } - /* * SQL function for creating a new physical (streaming replication) * replication slot. --- 20,29 ---- #include "replication/slot.h" #include "replication/logical.h" #include "replication/logicalfuncs.h" + #include "utils/acl.h" #include "utils/builtins.h" #include "utils/pg_lsn.h" /* * SQL function for creating a new physical (streaming replication) * replication slot. *************** pg_create_physical_replication_slot(PG_F *** 51,57 **** if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); ! check_permissions(); CheckSlotRequirements(); --- 43,52 ---- if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); ! if (!has_replication_privilege(GetUserId())) ! ereport(ERROR, ! (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("must be superuser or replication role to use replication slots"))); CheckSlotRequirements(); *************** pg_create_logical_replication_slot(PG_FU *** 94,100 **** if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); ! check_permissions(); CheckLogicalDecodingRequirements(); --- 89,98 ---- if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); ! if (!has_replication_privilege(GetUserId())) ! ereport(ERROR, ! (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("must be superuser or replication role to use replication slots"))); CheckLogicalDecodingRequirements(); *************** pg_drop_replication_slot(PG_FUNCTION_ARG *** 143,149 **** { Name name = PG_GETARG_NAME(0); ! check_permissions(); CheckSlotRequirements(); --- 141,150 ---- { Name name = PG_GETARG_NAME(0); ! if (!has_replication_privilege(GetUserId())) ! ereport(ERROR, ! (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("must be superuser or replication role to use replication slots"))); CheckSlotRequirements(); diff --git a/src/backend/replication/walsender.c b/src/backend/replication/walsender.c new file mode 100644 index 86c36bf..e1e44b0 *** a/src/backend/replication/walsender.c --- b/src/backend/replication/walsender.c *************** *** 71,76 **** --- 71,77 ---- #include "storage/proc.h" #include "storage/procarray.h" #include "tcop/tcopprot.h" + #include "utils/acl.h" #include "utils/builtins.h" #include "utils/guc.h" #include "utils/memutils.h" *************** pg_stat_get_wal_senders(PG_FUNCTION_ARGS *** 2803,2813 **** memset(nulls, 0, sizeof(nulls)); values[0] = Int32GetDatum(walsnd->pid); ! if (!superuser()) { /* ! * Only superusers can see details. Other users only get the pid ! * value to know it's a walsender, but no details. */ MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1); } --- 2804,2815 ---- memset(nulls, 0, sizeof(nulls)); values[0] = Int32GetDatum(walsnd->pid); ! if (!has_monitor_privilege(GetUserId())) { /* ! * Only users with the MONITOR attribute or superuser privileges can ! * see details. Other users only get the pid value to know it's a ! * walsender, but no details. */ MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1); } diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c new file mode 100644 index 29f7c3b..ca4b64d *** a/src/backend/utils/adt/misc.c --- b/src/backend/utils/adt/misc.c *************** *** 37,42 **** --- 37,43 ---- #include "utils/lsyscache.h" #include "utils/ruleutils.h" #include "tcop/tcopprot.h" + #include "utils/acl.h" #include "utils/builtins.h" #include "utils/timestamp.h" *************** pg_signal_backend(int pid, int sig) *** 113,119 **** return SIGNAL_BACKEND_ERROR; } ! if (!(superuser() || proc->roleId == GetUserId())) return SIGNAL_BACKEND_NOPERMISSION; /* --- 114,132 ---- return SIGNAL_BACKEND_ERROR; } ! /* ! * If the current user is not a superuser, then they aren't allowed to ! * signal backends which are owned by a superuser. ! */ ! if (!superuser() && superuser_arg(proc->roleId)) ! return SIGNAL_BACKEND_NOPERMISSION; ! ! /* ! * If the current user is not a member of the role owning the process and ! * does not have the SIGNAL permission, then permission is denied. ! */ ! if (!has_privs_of_role(GetUserId(), proc->roleId) ! && !has_signal_privilege(GetUserId())) return SIGNAL_BACKEND_NOPERMISSION; /* *************** pg_reload_conf(PG_FUNCTION_ARGS) *** 202,211 **** Datum pg_rotate_logfile(PG_FUNCTION_ARGS) { ! if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! (errmsg("must be superuser to rotate log files")))); if (!Logging_collector) { --- 215,224 ---- Datum pg_rotate_logfile(PG_FUNCTION_ARGS) { ! if (!has_log_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("must be superuser or have log permission to rotate log files"))); if (!Logging_collector) { diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c new file mode 100644 index 389ea49..2107490 *** a/src/backend/utils/adt/pgstatfuncs.c --- b/src/backend/utils/adt/pgstatfuncs.c *************** *** 20,25 **** --- 20,26 ---- #include "libpq/ip.h" #include "miscadmin.h" #include "pgstat.h" + #include "utils/acl.h" #include "utils/builtins.h" #include "utils/inet.h" #include "utils/timestamp.h" *************** pg_stat_get_activity(PG_FUNCTION_ARGS) *** 625,630 **** --- 626,632 ---- HeapTuple tuple; LocalPgBackendStatus *local_beentry; PgBackendStatus *beentry; + Oid current_user_id; MemSet(values, 0, sizeof(values)); MemSet(nulls, 0, sizeof(nulls)); *************** pg_stat_get_activity(PG_FUNCTION_ARGS) *** 674,681 **** else nulls[15] = true; ! /* Values only available to same user or superuser */ ! if (superuser() || beentry->st_userid == GetUserId()) { SockAddr zero_clientaddr; --- 676,689 ---- else nulls[15] = true; ! /* ! * Values only available to roles which are members of this role, ! * or which have the MONITOR privilege. ! */ ! current_user_id = GetUserId(); ! ! if (has_monitor_privilege(current_user_id) ! || has_privs_of_role(current_user_id, beentry->st_userid)) { SockAddr zero_clientaddr; *************** pg_stat_get_backend_activity(PG_FUNCTION *** 874,883 **** int32 beid = PG_GETARG_INT32(0); PgBackendStatus *beentry; const char *activity; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) activity = "<backend information not available>"; ! else if (!superuser() && beentry->st_userid != GetUserId()) activity = "<insufficient privilege>"; else if (*(beentry->st_activity) == '\0') activity = "<command string not enabled>"; --- 882,895 ---- int32 beid = PG_GETARG_INT32(0); PgBackendStatus *beentry; const char *activity; + Oid current_user_id; + + current_user_id = GetUserId(); if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) activity = "<backend information not available>"; ! else if (!has_monitor_privilege(current_user_id) ! && !has_privs_of_role(current_user_id, beentry->st_userid)) activity = "<insufficient privilege>"; else if (*(beentry->st_activity) == '\0') activity = "<command string not enabled>"; *************** pg_stat_get_backend_waiting(PG_FUNCTION_ *** 894,904 **** int32 beid = PG_GETARG_INT32(0); bool result; PgBackendStatus *beentry; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! if (!superuser() && beentry->st_userid != GetUserId()) PG_RETURN_NULL(); result = beentry->st_waiting; --- 906,920 ---- int32 beid = PG_GETARG_INT32(0); bool result; PgBackendStatus *beentry; + Oid current_user_id; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! current_user_id = GetUserId(); ! ! if (!has_monitor_privilege(current_user_id) ! && !has_privs_of_role(current_user_id, beentry->st_userid)) PG_RETURN_NULL(); result = beentry->st_waiting; *************** pg_stat_get_backend_activity_start(PG_FU *** 913,923 **** int32 beid = PG_GETARG_INT32(0); TimestampTz result; PgBackendStatus *beentry; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! if (!superuser() && beentry->st_userid != GetUserId()) PG_RETURN_NULL(); result = beentry->st_activity_start_timestamp; --- 929,943 ---- int32 beid = PG_GETARG_INT32(0); TimestampTz result; PgBackendStatus *beentry; + Oid current_user_id; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! current_user_id = GetUserId(); ! ! if (!has_monitor_privilege(current_user_id) ! && !has_privs_of_role(current_user_id, beentry->st_userid)) PG_RETURN_NULL(); result = beentry->st_activity_start_timestamp; *************** pg_stat_get_backend_xact_start(PG_FUNCTI *** 939,949 **** int32 beid = PG_GETARG_INT32(0); TimestampTz result; PgBackendStatus *beentry; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! if (!superuser() && beentry->st_userid != GetUserId()) PG_RETURN_NULL(); result = beentry->st_xact_start_timestamp; --- 959,973 ---- int32 beid = PG_GETARG_INT32(0); TimestampTz result; PgBackendStatus *beentry; + Oid current_user_id; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! current_user_id = GetUserId(); ! ! if (!has_monitor_privilege(current_user_id) ! && !has_privs_of_role(current_user_id, beentry->st_userid)) PG_RETURN_NULL(); result = beentry->st_xact_start_timestamp; *************** pg_stat_get_backend_start(PG_FUNCTION_AR *** 961,971 **** int32 beid = PG_GETARG_INT32(0); TimestampTz result; PgBackendStatus *beentry; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! if (!superuser() && beentry->st_userid != GetUserId()) PG_RETURN_NULL(); result = beentry->st_proc_start_timestamp; --- 985,999 ---- int32 beid = PG_GETARG_INT32(0); TimestampTz result; PgBackendStatus *beentry; + Oid current_user_id; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! current_user_id = GetUserId(); ! ! if (!has_monitor_privilege(current_user_id) ! && !has_privs_of_role(current_user_id, beentry->st_userid)) PG_RETURN_NULL(); result = beentry->st_proc_start_timestamp; *************** pg_stat_get_backend_client_addr(PG_FUNCT *** 985,995 **** SockAddr zero_clientaddr; char remote_host[NI_MAXHOST]; int ret; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! if (!superuser() && beentry->st_userid != GetUserId()) PG_RETURN_NULL(); /* A zeroed client addr means we don't know */ --- 1013,1027 ---- SockAddr zero_clientaddr; char remote_host[NI_MAXHOST]; int ret; + Oid current_user_id; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! current_user_id = GetUserId(); ! ! if (!has_monitor_privilege(current_user_id) ! && !has_privs_of_role(current_user_id, beentry->st_userid)) PG_RETURN_NULL(); /* A zeroed client addr means we don't know */ *************** pg_stat_get_backend_client_port(PG_FUNCT *** 1032,1042 **** SockAddr zero_clientaddr; char remote_port[NI_MAXSERV]; int ret; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! if (!superuser() && beentry->st_userid != GetUserId()) PG_RETURN_NULL(); /* A zeroed client addr means we don't know */ --- 1064,1082 ---- SockAddr zero_clientaddr; char remote_port[NI_MAXSERV]; int ret; + Oid current_user_id; if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! /* ! * User must have MONITOR attribute, be superuser or be the same ! * backend user. ! */ ! current_user_id = GetUserId(); ! ! if (!has_monitor_privilege(current_user_id) ! && !has_privs_of_role(current_user_id, beentry->st_userid)) PG_RETURN_NULL(); /* A zeroed client addr means we don't know */ diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c new file mode 100644 index 4646e09..4bac170 *** a/src/backend/utils/init/miscinit.c --- b/src/backend/utils/init/miscinit.c *************** SetUserIdAndContext(Oid userid, bool sec *** 430,454 **** SecurityRestrictionContext &= ~SECURITY_LOCAL_USERID_CHANGE; } - - /* - * Check whether specified role has explicit REPLICATION privilege - */ - bool - has_rolreplication(Oid roleid) - { - bool result = false; - HeapTuple utup; - - utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); - if (HeapTupleIsValid(utup)) - { - result = ((Form_pg_authid) GETSTRUCT(utup))->rolreplication; - ReleaseSysCache(utup); - } - return result; - } - /* * Initialize user identity during normal backend startup */ --- 430,435 ---- diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c new file mode 100644 index 1f5cf06..df9f0b4 *** a/src/backend/utils/init/postinit.c --- b/src/backend/utils/init/postinit.c *************** InitPostgres(const char *in_dbname, Oid *** 762,768 **** { Assert(!bootstrap); ! if (!superuser() && !has_rolreplication(GetUserId())) ereport(FATAL, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser or replication role to start walsender"))); --- 762,768 ---- { Assert(!bootstrap); ! if (!has_replication_privilege(GetUserId())) ereport(FATAL, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser or replication role to start walsender"))); diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h new file mode 100644 index e01e6aa..6b8e32d *** a/src/include/catalog/pg_authid.h --- b/src/include/catalog/pg_authid.h *************** CATALOG(pg_authid,1260) BKI_SHARED_RELAT *** 53,58 **** --- 53,63 ---- bool rolcanlogin; /* allowed to log in as session user? */ bool rolreplication; /* role used for streaming replication */ bool rolbypassrls; /* allowed to bypass row level security? */ + bool rolonlinebackup;/* allowed to peform backup operations? */ + bool rolxlogreplay; /* allowed to control xlog replay */ + bool rollog; /* allowed to rotate log files? */ + bool rolmonitor; /* allowed to view pg_stat_* details? */ + bool rolsignal; /* allowed to signal backed processes? */ int32 rolconnlimit; /* max connections allowed (-1=no limit) */ /* remaining fields may be null; use heap_getattr to read them! */ *************** typedef FormData_pg_authid *Form_pg_auth *** 74,80 **** * compiler constants for pg_authid * ---------------- */ ! #define Natts_pg_authid 12 #define Anum_pg_authid_rolname 1 #define Anum_pg_authid_rolsuper 2 #define Anum_pg_authid_rolinherit 3 --- 79,85 ---- * compiler constants for pg_authid * ---------------- */ ! #define Natts_pg_authid 17 #define Anum_pg_authid_rolname 1 #define Anum_pg_authid_rolsuper 2 #define Anum_pg_authid_rolinherit 3 *************** typedef FormData_pg_authid *Form_pg_auth *** 84,92 **** #define Anum_pg_authid_rolcanlogin 7 #define Anum_pg_authid_rolreplication 8 #define Anum_pg_authid_rolbypassrls 9 ! #define Anum_pg_authid_rolconnlimit 10 ! #define Anum_pg_authid_rolpassword 11 ! #define Anum_pg_authid_rolvaliduntil 12 /* ---------------- * initial contents of pg_authid --- 89,102 ---- #define Anum_pg_authid_rolcanlogin 7 #define Anum_pg_authid_rolreplication 8 #define Anum_pg_authid_rolbypassrls 9 ! #define Anum_pg_authid_rolonlinebackup 10 ! #define Anum_pg_authid_rolxlogreplay 11 ! #define Anum_pg_authid_rollog 12 ! #define Anum_pg_authid_rolmonitor 13 ! #define Anum_pg_authid_rolsignal 14 ! #define Anum_pg_authid_rolconnlimit 15 ! #define Anum_pg_authid_rolpassword 16 ! #define Anum_pg_authid_rolvaliduntil 17 /* ---------------- * initial contents of pg_authid *************** typedef FormData_pg_authid *Form_pg_auth *** 95,101 **** * user choices. * ---------------- */ ! DATA(insert OID = 10 ( "POSTGRES" t t t t t t t t -1 _null_ _null_)); #define BOOTSTRAP_SUPERUSERID 10 --- 105,111 ---- * user choices. * ---------------- */ ! DATA(insert OID = 10 ( "POSTGRES" t t t t t t t t t t t t t -1 _null_ _null_)); #define BOOTSTRAP_SUPERUSERID 10 diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h new file mode 100644 index 6e33a17..595564f *** a/src/include/miscadmin.h --- b/src/include/miscadmin.h *************** extern void ValidatePgVersion(const char *** 442,448 **** extern void process_shared_preload_libraries(void); extern void process_session_preload_libraries(void); extern void pg_bindtextdomain(const char *domain); - extern bool has_rolreplication(Oid roleid); /* in access/transam/xlog.c */ extern bool BackupInProgress(void); --- 442,447 ---- diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h new file mode 100644 index ab0df6c..39bc285 *** a/src/include/utils/acl.h --- b/src/include/utils/acl.h *************** extern bool pg_event_trigger_ownercheck( *** 328,332 **** --- 328,338 ---- extern bool pg_extension_ownercheck(Oid ext_oid, Oid roleid); extern bool has_createrole_privilege(Oid roleid); extern bool has_bypassrls_privilege(Oid roleid); + extern bool has_replication_privilege(Oid roleid); + extern bool has_online_backup_privilege(Oid roleid); + extern bool has_xlog_replay_privilege(Oid roleid); + extern bool has_log_privilege(Oid roleid); + extern bool has_monitor_privilege(Oid roleid); + extern bool has_signal_privilege(Oid roleid); #endif /* ACL_H */
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers