Add ALTER ROLE ALL SET command

This generalizes the existing ALTER ROLE ... SET and ALTER DATABASE
... SET functionality to allow creating settings that apply to all users
in all databases.

reviewed by Pavel Stehule

5 files changed, 68 insertions, 33 deletions

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 3ba877d253a..5edb59af36b 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -814,41 +814,46 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
 {
 	HeapTuple	roletuple;
 	Oid			databaseid = InvalidOid;
-	Oid         roleid;
+	Oid         roleid = InvalidOid;
 
-	roletuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role));
+	if (stmt->role)
+	{
+		roletuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role));
 
-	if (!HeapTupleIsValid(roletuple))
-		ereport(ERROR,
-				(errcode(ERRCODE_UNDEFINED_OBJECT),
-				 errmsg("role \"%s\" does not exist", stmt->role)));
+		if (!HeapTupleIsValid(roletuple))
+			ereport(ERROR,
+					(errcode(ERRCODE_UNDEFINED_OBJECT),
+					 errmsg("role \"%s\" does not exist", stmt->role)));
 
-	roleid = HeapTupleGetOid(roletuple);
+		roleid = HeapTupleGetOid(roletuple);
 
-	/*
-	 * Obtain a lock on the role and make sure it didn't go away in the
-	 * meantime.
-	 */
-	shdepLockAndCheckObject(AuthIdRelationId, HeapTupleGetOid(roletuple));
+		/*
+		 * Obtain a lock on the role and make sure it didn't go away in the
+		 * meantime.
+		 */
+		shdepLockAndCheckObject(AuthIdRelationId, HeapTupleGetOid(roletuple));
 
-	/*
-	 * To mess with a superuser you gotta be superuser; else you need
-	 * createrole, or just want to change your own settings
-	 */
-	if (((Form_pg_authid) GETSTRUCT(roletuple))->rolsuper)
-	{
-		if (!superuser())
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must be superuser to alter superusers")));
-	}
-	else
-	{
-		if (!have_createrole_privilege() &&
-			HeapTupleGetOid(roletuple) != GetUserId())
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("permission denied")));
+		/*
+		 * To mess with a superuser you gotta be superuser; else you need
+		 * createrole, or just want to change your own settings
+		 */
+		if (((Form_pg_authid) GETSTRUCT(roletuple))->rolsuper)
+		{
+			if (!superuser())
+				ereport(ERROR,
+						(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+						 errmsg("must be superuser to alter superusers")));
+		}
+		else
+		{
+			if (!have_createrole_privilege() &&
+				HeapTupleGetOid(roletuple) != GetUserId())
+				ereport(ERROR,
+						(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+						 errmsg("permission denied")));
+		}
+
+		ReleaseSysCache(roletuple);
 	}
 
 	/* look up and lock the database, if specified */
@@ -856,10 +861,29 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
 	{
 		databaseid = get_database_oid(stmt->database, false);
 		shdepLockAndCheckObject(DatabaseRelationId, databaseid);
+
+		if (!stmt->role)
+		{
+			/*
+			 * If no role is specified, then this is effectively the same as
+			 * ALTER DATABASE ... SET, so use the same permission check.
+			 */
+			if (!pg_database_ownercheck(databaseid, GetUserId()))
+				aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
+							   stmt->database);
+		}
+	}
+
+	if (!stmt->role && !stmt->database)
+	{
+		/* Must be superuser to alter settings globally. */
+		if (!superuser())
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("must be superuser to alter settings globally")));
 	}
 
-	AlterSetting(databaseid, HeapTupleGetOid(roletuple), stmt->setstmt);
-	ReleaseSysCache(roletuple);
+	AlterSetting(databaseid, roleid, stmt->setstmt);
 
 	return roleid;
 }
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index fee05311c5c..b998431f5f3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1020,6 +1020,14 @@ AlterRoleSetStmt:
 					n->setstmt = $5;
 					$$ = (Node *)n;
 				}
+			| ALTER ROLE ALL opt_in_database SetResetClause
+				{
+					AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
+					n->role = NULL;
+					n->database = $4;
+					n->setstmt = $5;
+					$$ = (Node *)n;
+				}
 		;
 
 
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 7e21ceae880..84270061d8a 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1010,6 +1010,7 @@ process_settings(Oid databaseid, Oid roleid)
 	ApplySetting(databaseid, roleid, relsetting, PGC_S_DATABASE_USER);
 	ApplySetting(InvalidOid, roleid, relsetting, PGC_S_USER);
 	ApplySetting(databaseid, InvalidOid, relsetting, PGC_S_DATABASE);
+	ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_GLOBAL);
 
 	heap_close(relsetting, AccessShareLock);
 }
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 6128694200f..5437e0744f3 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -500,6 +500,7 @@ const char *const GucSource_Names[] =
 	 /* PGC_S_ENV_VAR */ "environment variable",
 	 /* PGC_S_FILE */ "configuration file",
 	 /* PGC_S_ARGV */ "command line",
+	 /* PGC_S_GLOBAL */ "global",
 	 /* PGC_S_DATABASE */ "database",
 	 /* PGC_S_USER */ "user",
 	 /* PGC_S_DATABASE_USER */ "database user",
@@ -5149,7 +5150,7 @@ set_config_option(const char *name, const char *value,
 			 */
 			elevel = IsUnderPostmaster ? DEBUG3 : LOG;
 		}
-		else if (source == PGC_S_DATABASE || source == PGC_S_USER ||
+		else if (source == PGC_S_GLOBAL || source == PGC_S_DATABASE || source == PGC_S_USER ||
 				 source == PGC_S_DATABASE_USER)
 			elevel = WARNING;
 		else
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 0023c007e0e..d497b1f6546 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -87,6 +87,7 @@ typedef enum
 	PGC_S_ENV_VAR,				/* postmaster environment variable */
 	PGC_S_FILE,					/* postgresql.conf */
 	PGC_S_ARGV,					/* postmaster command line */
+	PGC_S_GLOBAL,				/* global in-database setting */
 	PGC_S_DATABASE,				/* per-database setting */
 	PGC_S_USER,					/* per-user setting */
 	PGC_S_DATABASE_USER,		/* per-user-and-database setting */