In many environments there is a policy requiring users to login using unprivileged accounts, and then escalate their privileges if and when they require it. In PostgreSQL this could be done by granting the superuser role to an unprivileged user with noinherit, and then making the superuser role nologin. Something like:
8<------------- psql -U postgres create user joe noinherit; grant postgres to joe; alter user postgres nologin; \q psql -U joe -- do stuff *not requiring* escalated privs set role postgres; -- do stuff *requiring* escalated privs reset role; 8<------------- One of the problems with this is we would ideally like to know whenever joe escalates himself to postgres. Right now that is not really possible without doing untenable things such as logging every statement. In order to address this issue, I am proposing a new SET ROLE hook. The attached patch (role-esc-hook.diff) is I believe all we need. Then extension authors could implement logging of privilege escalation. A proof of concept extension patch is also attached. That one is not meant to be applied, just illustrates one potential use of the hook. I just smashed it on top of passwordcheck for the sake of convenience. With both patches applied, the following scenario: 8<------------------------ psql -U joe postgres psql (9.6devel) Type "help" for help. postgres=> set role postgres; SET postgres=# select rolname, rolpassword from pg_authid; rolname | rolpassword ----------+------------- joe | postgres | (2 rows) postgres=# set log_statement = none; SET postgres=# reset role; RESET 8<------------------------ Generates the following in the log: 8<------------------------ LOG: Role joe transitioning to Superuser Role postgres STATEMENT: set role postgres; LOG: statement: select rolname, rolpassword from pg_authid; LOG: statement: set log_statement = none; LOG: Superuser Role postgres transitioning to Role joe STATEMENT: reset role; 8<------------------------ Note that we cannot prevent joe from executing set log_statement = none; but we at least get the evidence in the log and can ask joe why he felt the need to do that. We could also set up alerts based on the logged events, etc. This particular hook will not capture role changes due to SECURITY DEFINER functions, but I think that is not only ok but preferred. Escalation during a SECURITY DEFINER function is a preplanned sanctioned event, unlike an ad hoc unconstrained role change to superuser. And given the demo patch, we could see any SECURITY DEFINER function created by the superuser when it gets created in the log (which again is subject to auditing, alerts, etc.) Ultimately I would also like to see a general hook available which would fire for all changes to GUC settings, but I think this one is still very useful as it is highly targeted. Comments? Thanks, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
diff --git a/src/backend/commands/variable.c b/src/backend/commands/variable.c
index 2d0a44e..76b3957 100644
*** a/src/backend/commands/variable.c
--- b/src/backend/commands/variable.c
***************
*** 31,36 ****
--- 31,38 ----
#include "utils/timestamp.h"
#include "mb/pg_wchar.h"
+ SetRole_hook_type SetRole_hook = NULL;
+
/*
* DATESTYLE
*/
*************** assign_role(const char *newval, void *ex
*** 904,909 ****
--- 906,919 ----
{
role_auth_extra *myextra = (role_auth_extra *) extra;
+ /*
+ * Any defined hooks must be able to execute in a failed
+ * transaction to restore a prior value of the ROLE GUC variable.
+ */
+ if (SetRole_hook)
+ (*SetRole_hook) (GetUserId(),
+ myextra->roleid, myextra->is_superuser);
+
SetCurrentRoleId(myextra->roleid, myextra->is_superuser);
}
diff --git a/src/include/commands/variable.h b/src/include/commands/variable.h
index 9cf2edd..be243df 100644
*** a/src/include/commands/variable.h
--- b/src/include/commands/variable.h
***************
*** 12,17 ****
--- 12,20 ----
#include "utils/guc.h"
+ /* Hook for plugins to get control in check_role() */
+ typedef void (*SetRole_hook_type) (Oid, Oid, bool);
+ extern PGDLLIMPORT SetRole_hook_type SetRole_hook;
extern bool check_datestyle(char **newval, void **extra, GucSource source);
extern void assign_datestyle(const char *newval, void *extra);
diff --git a/contrib/passwordcheck/passwordcheck.c b/contrib/passwordcheck/passwordcheck.c
index 78c44b2..867a703 100644
*** a/contrib/passwordcheck/passwordcheck.c
--- b/contrib/passwordcheck/passwordcheck.c
***************
*** 21,28 ****
--- 21,31 ----
#endif
#include "commands/user.h"
+ #include "commands/variable.h"
#include "fmgr.h"
+ #include "miscadmin.h"
#include "libpq/md5.h"
+ #include "utils/memutils.h"
PG_MODULE_MAGIC;
*************** PG_MODULE_MAGIC;
*** 31,36 ****
--- 34,41 ----
extern void _PG_init(void);
+ char *save_log_statement = NULL;
+
/*
* check_password
*
*************** check_password(const char *username,
*** 136,141 ****
--- 141,184 ----
/* all checks passed, password is ok */
}
+ static void
+ log_role_change(Oid OldUserId, Oid NewUserId, bool NewUser_is_superuser)
+ {
+ char *olduser;
+ char *newuser;
+ char *su = "Superuser ";
+ char *nsu = "";
+ bool OldUser_is_superuser = superuser_arg(OldUserId);
+ MemoryContext oldcontext;
+
+ if (!OidIsValid(NewUserId))
+ NewUserId = GetSessionUserId();
+ NewUser_is_superuser = superuser_arg(NewUserId);
+
+ olduser = GetUserNameFromId(OldUserId, true);
+ newuser = GetUserNameFromId(NewUserId, true);
+
+ elog(LOG, "%sRole %s transitioning to %sRole %s",
+ OldUser_is_superuser ? su : nsu,
+ olduser,
+ NewUser_is_superuser ? su : nsu,
+ newuser);
+
+ oldcontext = MemoryContextSwitchTo(TopMemoryContext);
+ if (NewUser_is_superuser && !save_log_statement)
+ {
+ save_log_statement = GetConfigOptionByName("log_statement", NULL, false);
+ SetConfigOption("log_statement", "all", PGC_SUSET, PGC_S_SESSION);
+ }
+ else if (!NewUser_is_superuser && save_log_statement)
+ {
+ SetConfigOption("log_statement", save_log_statement, PGC_SUSET, PGC_S_SESSION);
+ pfree(save_log_statement);
+ save_log_statement = NULL;
+ }
+ MemoryContextSwitchTo(oldcontext);
+ }
+
/*
* Module initialization function
*/
*************** _PG_init(void)
*** 144,147 ****
--- 187,191 ----
{
/* activate password checks when the module is loaded */
check_password_hook = check_password;
+ SetRole_hook = log_role_change;
}
signature.asc
Description: OpenPGP digital signature
