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

1 files changed, 56 insertions, 32 deletions

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 3ba877d253..5edb59af36 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;
 }