fixing CREATEROLE

From 66954f3084e4608b92ca708a99810d58a222cbbb Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Wed, 30 Nov 2022 15:13:41 -0500
Subject: [PATCH v2 1/5] Improve documentation of the CREATEROLE attibute.

In user-manag.sgml, document precisely what privileges are conveyed
by CREATEROLE. Make particular note of the fact that it allows
changing passwords and granting access to high-privilege roles.
Also remove the suggestion of using a user with CREATEROLE and
CREATEDB instead of a superuser, as there is no real security
advantage to this approach.

Elsewhere in the documentation, adjust text that suggests that
<literal>CREATEROLE</literal> only allows for role creation, and
refer to the documentation in user-manag.sgml as appropriate.

(Proposed for back-patching to all supported branches.)
---
 doc/src/sgml/ref/alter_role.sgml  |  2 +-
 doc/src/sgml/ref/create_role.sgml | 10 +++----
 doc/src/sgml/ref/createuser.sgml  | 18 ++++++++----
 doc/src/sgml/user-manag.sgml      | 47 ++++++++++++++++++++++---------
 4 files changed, 52 insertions(+), 25 deletions(-)

diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 5aa5648ae7..922686e5ce 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -307,7 +307,7 @@ ALTER ROLE fred VALID UNTIL 'infinity';
   </para>
 
   <para>
-   Give a role the ability to create other roles and new databases:
+   Give a role the ability to manage other roles and create new databases:
 
 <programlisting>
 ALTER ROLE miriam CREATEROLE CREATEDB;
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 029a193361..1ccc832558 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -119,11 +119,11 @@ in sync when changing the above synopsis!
       <listitem>
        <para>
         These clauses determine whether a role will be permitted to
-        create new roles (that is, execute <command>CREATE ROLE</command>).
-        A role with <literal>CREATEROLE</literal> privilege can also alter
-        and drop other roles.
-        If not specified,
-        <literal>NOCREATEROLE</literal> is the default.
+        create, alter, drop, comment on, change the security label for,
+        and grant or revoke membership in other roles.
+        See <xref linkend='role-creation' /> for more details about what
+        capabilities are conferred by this privilege.
+        If not specified, <literal>NOCREATEROLE</literal> is the default.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index c6a7c603f7..a41a2b24e6 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -41,10 +41,14 @@ PostgreSQL documentation
   </para>
 
   <para>
-   If you wish to create a new superuser, you must connect as a
-   superuser, not merely with <literal>CREATEROLE</literal> privilege.
+   If you wish to create a role with the <literal>SUPERUSER</literal>,
+   <literal>REPLICATION</literal>, or <literal>BYPASSRLS</literal> privilege,
+   you must connect as a superuser, not merely with
+   <literal>CREATEROLE</literal> privilege.
    Being a superuser implies the ability to bypass all access permission
-   checks within the database, so superuser access should not be granted lightly.
+   checks within the database, so superuser access should not be granted
+   lightly. <literal>CREATEROLE</literal> also conveys
+   <link linkend='role-creation'>very extensive privileges</link>.
   </para>
 
   <para>
@@ -247,8 +251,12 @@ PostgreSQL documentation
       <term><option>--createrole</option></term>
       <listitem>
        <para>
-        The new user will be allowed to create new roles (that is,
-        this user will have <literal>CREATEROLE</literal> privilege).
+        The new user will be allowed to create, alter, drop, comment on,
+        change the security label for, and grant or revoke membership in
+        other roles; that is,
+        this user will have <literal>CREATEROLE</literal> privilege.
+        See <xref linkend='role-creation' /> for more details about what
+        capabilities are conferred by this privilege.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 2bff4e47d0..0839daecca 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -191,7 +191,7 @@ CREATE USER <replaceable>name</replaceable>;
      </varlistentry>
 
      <varlistentry>
-      <term>role creation<indexterm><primary>role</primary><secondary>privilege to create</secondary></indexterm></term>
+      <term id='role-creation'>role creation<indexterm><primary>role</primary><secondary>privilege to create</secondary></indexterm></term>
       <listitem>
        <para>
         A role must be explicitly given permission to create more roles
@@ -200,9 +200,38 @@ CREATE USER <replaceable>name</replaceable>;
         <replaceable>name</replaceable> CREATEROLE</literal>.
         A role with <literal>CREATEROLE</literal> privilege can alter and drop
         other roles, too, as well as grant or revoke membership in them.
-        However, to create, alter, drop, or change membership of a
-        superuser role, superuser status is required;
-        <literal>CREATEROLE</literal> is insufficient for that.
+        Altering a role includes most changes that can be made using
+        <literal>ALTER ROLE</literal>, including, for example, changing
+        passwords.  It also includes modifications to a role that can
+        be made using the <literal>COMMENT</literal> and
+        <literal>SECURITY LABEL</literal> commands.
+       </para>
+       <para>
+        However, <literal>CREATEROLE</literal> does not convey the ability to
+        create <literal>SUPERUSER</literal> roles, nor does it convey any
+        power over <literal>SUPERUSER</literal> roles that already exist.
+        Furthermore, <literal>CREATEROLE</literal> does not convey the power
+        to create <literal>REPLICATION</literal> users, nor the ability to
+        grant or revoke the <literal>REPLICATION</literal> privilege, nor the
+        ability to the role properties of such users.  However, it does allow
+        <literal>ALTER ROLE .. SET</literal> and
+        <literal>ALTER ROLE .. RENAME</literal> to be used on
+        <literal>REPLICATION</literal> roles, as well as the use of
+        <literal>COMMENT ON ROLE</literal>,
+        <literal>SECURITY LABEL ON ROLE</literal>,
+        and <literal>DROP ROLE</literal>.
+        Finally, <literal>CREATEROLE</literal> does not
+        confer the ability to grant or revoke the <literal>BYPASSRLS</literal>
+        privilege.
+       </para>
+       <para>
+        Because the <literal>CREATEROLE</literal> privilege allows a user
+        to grant or revoke membership even in roles to which it does not (yet)
+        have any access, a <literal>CREATEROLE</literal> user can obtain access
+        to the capabilities of every predefined role in the system, including
+        highly privileged roles such as
+        <literal>pg_execute_server_program</literal> and
+        <literal>pg_write_server_files</literal>.
        </para>
       </listitem>
      </varlistentry>
@@ -280,16 +309,6 @@ CREATE USER <replaceable>name</replaceable>;
     and <xref linkend="sql-alterrole"/> commands for details.
    </para>
 
-  <tip>
-   <para>
-    It is good practice to create a role that has the <literal>CREATEDB</literal>
-    and <literal>CREATEROLE</literal> privileges, but is not a superuser, and then
-    use this role for all routine management of databases and roles.  This
-    approach avoids the dangers of operating as a superuser for tasks that
-    do not really require it.
-   </para>
-  </tip>
-
   <para>
    A role can also have role-specific defaults for many of the run-time
    configuration settings described in <xref
-- 
2.24.3 (Apple Git-128)

From 62f1e04467b8fd3d6b6025f4afc9572c2f77606b Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Wed, 24 Aug 2022 14:55:01 -0400
Subject: [PATCH v2 2/5] Refactor permissions-checking for role grants.

Instead of having checks in AddRoleMems() and DelRoleMems(), have
the callers perform checks where it's required. In some cases it
isn't, either because the caller has already performed a check for
the same condition, or because the check couldn't possibly fail.

The "Skip permission check if nothing to do" check in each of
AddRoleMems() and DelRoleMems() is pointless. Most call sites
can't pass an empty list, and in the one case where an empty
list could be passed, the presence of this check couldn't possibly
avoid an error.
---
 src/backend/commands/user.c | 116 +++++++++++++++++-------------------
 1 file changed, 54 insertions(+), 62 deletions(-)

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 8b6543edee..08e3fea135 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -94,6 +94,8 @@ static void DelRoleMems(const char *rolename, Oid roleid,
 						List *memberSpecs, List *memberIds,
 						Oid grantorId, GrantRoleOptions *popt,
 						DropBehavior behavior);
+static void check_role_membership_authorization(Oid currentUserId, Oid roleid,
+												bool is_grant);
 static Oid	check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId,
 							   bool is_grant);
 static RevokeRoleGrantAction *initialize_revoke_actions(CatCList *memlist);
@@ -505,6 +507,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 			Oid			oldroleid = oldroleform->oid;
 			char	   *oldrolename = NameStr(oldroleform->rolname);
 
+			/* can only add this role to roles for which you have rights */
+			check_role_membership_authorization(GetUserId(), oldroleid, true);
 			AddRoleMems(oldrolename, oldroleid,
 						thisrole_list,
 						thisrole_oidlist,
@@ -517,6 +521,9 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	/*
 	 * Add the specified members to this new role. adminmembers get the admin
 	 * option, rolemembers don't.
+	 *
+	 * NB: No permissions check is required here. If you have enough rights
+	 * to create a role, you can add any members you like.
 	 */
 	AddRoleMems(stmt->role, roleid,
 				rolemembers, roleSpecsToIds(rolemembers),
@@ -1442,6 +1449,8 @@ GrantRole(ParseState *pstate, GrantRoleStmt *stmt)
 					 errmsg("column names cannot be included in GRANT/REVOKE ROLE")));
 
 		roleid = get_role_oid(rolename, false);
+		check_role_membership_authorization(GetUserId(), roleid,
+											stmt->is_grant);
 		if (stmt->is_grant)
 			AddRoleMems(rolename, roleid,
 						stmt->grantee_roles, grantee_ids,
@@ -1566,43 +1575,6 @@ AddRoleMems(const char *rolename, Oid roleid,
 
 	Assert(list_length(memberSpecs) == list_length(memberIds));
 
-	/* Skip permission check if nothing to do */
-	if (!memberIds)
-		return;
-
-	/*
-	 * Check permissions: must have createrole or admin option on the role to
-	 * be changed.  To mess with a superuser role, you gotta be superuser.
-	 */
-	if (superuser_arg(roleid))
-	{
-		if (!superuser())
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must be superuser to alter superusers")));
-	}
-	else
-	{
-		if (!have_createrole_privilege() &&
-			!is_admin_of_role(currentUserId, roleid))
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must have admin option on role \"%s\"",
-							rolename)));
-	}
-
-	/*
-	 * The charter of pg_database_owner is to have exactly one, implicit,
-	 * situation-dependent member.  There's no technical need for this
-	 * restriction.  (One could lift it and take the further step of making
-	 * object_ownercheck(DatabaseRelationId, ...) equivalent to has_privs_of_role(roleid,
-	 * ROLE_PG_DATABASE_OWNER), in which case explicit, situation-independent
-	 * members could act as the owner of any database.)
-	 */
-	if (roleid == ROLE_PG_DATABASE_OWNER)
-		ereport(ERROR,
-				errmsg("role \"%s\" cannot have explicit members", rolename));
-
 	/* Validate grantor (and resolve implicit grantor if not specified). */
 	grantorId = check_role_grantor(currentUserId, roleid, grantorId, true);
 
@@ -1901,31 +1873,6 @@ DelRoleMems(const char *rolename, Oid roleid,
 
 	Assert(list_length(memberSpecs) == list_length(memberIds));
 
-	/* Skip permission check if nothing to do */
-	if (!memberIds)
-		return;
-
-	/*
-	 * Check permissions: must have createrole or admin option on the role to
-	 * be changed.  To mess with a superuser role, you gotta be superuser.
-	 */
-	if (superuser_arg(roleid))
-	{
-		if (!superuser())
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must be superuser to alter superusers")));
-	}
-	else
-	{
-		if (!have_createrole_privilege() &&
-			!is_admin_of_role(currentUserId, roleid))
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must have admin option on role \"%s\"",
-							rolename)));
-	}
-
 	/* Validate grantor (and resolve implicit grantor if not specified). */
 	grantorId = check_role_grantor(currentUserId, roleid, grantorId, false);
 
@@ -2039,6 +1986,51 @@ DelRoleMems(const char *rolename, Oid roleid,
 	table_close(pg_authmem_rel, NoLock);
 }
 
+/*
+ * Check that currentUserId has permission to modify the membership list for
+ * roleid. Throw an error if not.
+ */
+static void
+check_role_membership_authorization(Oid currentUserId, Oid roleid,
+									bool is_grant)
+{
+	/*
+	 * The charter of pg_database_owner is to have exactly one, implicit,
+	 * situation-dependent member.  There's no technical need for this
+	 * restriction.  (One could lift it and take the further step of making
+	 * object_ownercheck(DatabaseRelationId, ...) equivalent to
+	 * has_privs_of_role(roleid, ROLE_PG_DATABASE_OWNER), in which case
+	 * explicit, situation-independent members could act as the owner of any
+	 * database.)
+	 */
+	if (is_grant && roleid == ROLE_PG_DATABASE_OWNER)
+		ereport(ERROR,
+				errmsg("role \"%s\" cannot have explicit members",
+					   GetUserNameFromId(roleid, false)));
+
+	/* To mess with a superuser role, you gotta be superuser. */
+	if (superuser_arg(roleid))
+	{
+		if (!superuser_arg(currentUserId))
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("must be superuser to alter superusers")));
+	}
+	else
+	{
+		/*
+		 * Otherwise, must have createrole or admin option on the role to be
+		 * changed.
+		 */
+		if (!has_createrole_privilege(currentUserId) &&
+			!is_admin_of_role(currentUserId, roleid))
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("must have admin option on role \"%s\"",
+							GetUserNameFromId(roleid, false))));
+	}
+}
+
 /*
  * Sanity-check, or infer, the grantor for a GRANT or REVOKE statement
  * targeting a role.
-- 
2.24.3 (Apple Git-128)

From 2febc2fd27592f05a010fe0a3e17fe2d57e8a94d Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Mon, 21 Nov 2022 14:46:03 -0500
Subject: [PATCH v2 3/5] Pass down current user ID to AddRoleMems and
 DelRoleMems.

This is just refactoring; there should be no functonal change.
---
 src/backend/commands/user.c | 41 ++++++++++++++++++++-----------------
 1 file changed, 22 insertions(+), 19 deletions(-)

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 08e3fea135..37fc4f9627 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -87,10 +87,10 @@ int			Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
 /* Hook to check passwords in CreateRole() and AlterRole() */
 check_password_hook_type check_password_hook = NULL;
 
-static void AddRoleMems(const char *rolename, Oid roleid,
+static void AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid,
 						List *memberSpecs, List *memberIds,
 						Oid grantorId, GrantRoleOptions *popt);
-static void DelRoleMems(const char *rolename, Oid roleid,
+static void DelRoleMems(Oid currentUserId, const char *rolename, Oid roleid,
 						List *memberSpecs, List *memberIds,
 						Oid grantorId, GrantRoleOptions *popt,
 						DropBehavior behavior);
@@ -133,6 +133,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	HeapTuple	tuple;
 	Datum		new_record[Natts_pg_authid] = {0};
 	bool		new_record_nulls[Natts_pg_authid] = {0};
+	Oid			currentUserId = GetUserId();
 	Oid			roleid;
 	ListCell   *item;
 	ListCell   *option;
@@ -508,8 +509,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 			char	   *oldrolename = NameStr(oldroleform->rolname);
 
 			/* can only add this role to roles for which you have rights */
-			check_role_membership_authorization(GetUserId(), oldroleid, true);
-			AddRoleMems(oldrolename, oldroleid,
+			check_role_membership_authorization(currentUserId, oldroleid, true);
+			AddRoleMems(currentUserId, oldrolename, oldroleid,
 						thisrole_list,
 						thisrole_oidlist,
 						InvalidOid, &popt);
@@ -525,12 +526,12 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	 * NB: No permissions check is required here. If you have enough rights
 	 * to create a role, you can add any members you like.
 	 */
-	AddRoleMems(stmt->role, roleid,
+	AddRoleMems(currentUserId, stmt->role, roleid,
 				rolemembers, roleSpecsToIds(rolemembers),
 				InvalidOid, &popt);
 	popt.specified |= GRANT_ROLE_SPECIFIED_ADMIN;
 	popt.admin = true;
-	AddRoleMems(stmt->role, roleid,
+	AddRoleMems(currentUserId, stmt->role, roleid,
 				adminmembers, roleSpecsToIds(adminmembers),
 				InvalidOid, &popt);
 
@@ -583,6 +584,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	DefElem    *dvalidUntil = NULL;
 	DefElem    *dbypassRLS = NULL;
 	Oid			roleid;
+	Oid			currentUserId = GetUserId();
 	GrantRoleOptions	popt;
 
 	check_rolespec_name(stmt->role,
@@ -727,13 +729,13 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 					 errmsg("permission denied")));
 
 		/* without CREATEROLE, can only change your own password */
-		if (dpassword && roleid != GetUserId())
+		if (dpassword && roleid != currentUserId)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have CREATEROLE privilege to change another user's password")));
 
 		/* without CREATEROLE, can only add members to roles you admin */
-		if (drolemembers && !is_admin_of_role(GetUserId(), roleid))
+		if (drolemembers && !is_admin_of_role(currentUserId, roleid))
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have admin option on role \"%s\" to add members",
@@ -888,11 +890,11 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 		CommandCounterIncrement();
 
 		if (stmt->action == +1) /* add members to role */
-			AddRoleMems(rolename, roleid,
+			AddRoleMems(currentUserId, rolename, roleid,
 						rolemembers, roleSpecsToIds(rolemembers),
 						InvalidOid, &popt);
 		else if (stmt->action == -1)	/* drop members from role */
-			DelRoleMems(rolename, roleid,
+			DelRoleMems(currentUserId, rolename, roleid,
 						rolemembers, roleSpecsToIds(rolemembers),
 						InvalidOid, &popt, DROP_RESTRICT);
 	}
@@ -1378,6 +1380,7 @@ GrantRole(ParseState *pstate, GrantRoleStmt *stmt)
 	List	   *grantee_ids;
 	ListCell   *item;
 	GrantRoleOptions	popt;
+	Oid			currentUserId = GetUserId();
 
 	/* Parse options list. */
 	InitGrantRoleOptions(&popt);
@@ -1449,14 +1452,14 @@ GrantRole(ParseState *pstate, GrantRoleStmt *stmt)
 					 errmsg("column names cannot be included in GRANT/REVOKE ROLE")));
 
 		roleid = get_role_oid(rolename, false);
-		check_role_membership_authorization(GetUserId(), roleid,
-											stmt->is_grant);
+		check_role_membership_authorization(currentUserId,
+											roleid, stmt->is_grant);
 		if (stmt->is_grant)
-			AddRoleMems(rolename, roleid,
+			AddRoleMems(currentUserId, rolename, roleid,
 						stmt->grantee_roles, grantee_ids,
 						grantor, &popt);
 		else
-			DelRoleMems(rolename, roleid,
+			DelRoleMems(currentUserId, rolename, roleid,
 						stmt->grantee_roles, grantee_ids,
 						grantor, &popt, stmt->behavior);
 	}
@@ -1555,15 +1558,17 @@ roleSpecsToIds(List *memberNames)
 /*
  * AddRoleMems -- Add given members to the specified role
  *
+ * currentUserId: OID of role performing the operation
  * rolename: name of role to add to (used only for error messages)
  * roleid: OID of role to add to
  * memberSpecs: list of RoleSpec of roles to add (used only for error messages)
  * memberIds: OIDs of roles to add
- * grantorId: who is granting the membership (InvalidOid if not set explicitly)
+ * grantorId: OID that should be recorded as having granted the membership
+ * (InvalidOid if not set explicitly)
  * popt: information about grant options
  */
 static void
-AddRoleMems(const char *rolename, Oid roleid,
+AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid,
 			List *memberSpecs, List *memberIds,
 			Oid grantorId, GrantRoleOptions *popt)
 {
@@ -1571,7 +1576,6 @@ AddRoleMems(const char *rolename, Oid roleid,
 	TupleDesc	pg_authmem_dsc;
 	ListCell   *specitem;
 	ListCell   *iditem;
-	Oid			currentUserId = GetUserId();
 
 	Assert(list_length(memberSpecs) == list_length(memberIds));
 
@@ -1858,7 +1862,7 @@ AddRoleMems(const char *rolename, Oid roleid,
  * behavior: RESTRICT or CASCADE behavior for recursive removal
  */
 static void
-DelRoleMems(const char *rolename, Oid roleid,
+DelRoleMems(Oid currentUserId, const char *rolename, Oid roleid,
 			List *memberSpecs, List *memberIds,
 			Oid grantorId, GrantRoleOptions *popt, DropBehavior behavior)
 {
@@ -1866,7 +1870,6 @@ DelRoleMems(const char *rolename, Oid roleid,
 	TupleDesc	pg_authmem_dsc;
 	ListCell   *specitem;
 	ListCell   *iditem;
-	Oid			currentUserId = GetUserId();
 	CatCList   *memlist;
 	RevokeRoleGrantAction *actions;
 	int			i;
-- 
2.24.3 (Apple Git-128)

From e626c60a77843d2639012dd16891f20c8e68188c Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Thu, 1 Dec 2022 15:07:02 -0500
Subject: [PATCH v2 4/5] Restrict the privileges of CREATEROLE users.

Previously, CREATEROLE users were permitted to make nearly arbitrary
changes to roles that they didn't create, with certain exceptions,
particularly superuser roles.  Instead, allow CREATEROLE users to make such
changes to roles for which they possess ADMIN OPTION, and to
grant membership only in roles for which they possess ADMIN OPTION.

When a CREATEROLE user who is not a superuser creates a role, grant
ADMIN OPTION on the newly-created role to the creator, so that they
can administer roles they create or for which they have been given
privileges.

With these changes, CREATEROLE users still have very significant
powers that unprivileged users do not receive: they can alter, rename,
drop, comment on, change the password for, and change security labels
on roles.  However, they can now do these things only for roles for
which they possess appropriate privileges, rather than all
non-superuser roles; moreover, they cannot grant a role such as
pg_execute_server_program unless they themselves possess it.

FIXME: Add more regression tests.
---
 doc/src/sgml/ddl.sgml                         |  10 +-
 doc/src/sgml/ref/alter_role.sgml              |   8 +-
 doc/src/sgml/ref/comment.sgml                 |   3 +-
 doc/src/sgml/ref/create_role.sgml             |   4 +-
 doc/src/sgml/ref/createuser.sgml              |   3 +-
 doc/src/sgml/ref/drop_role.sgml               |   2 +-
 doc/src/sgml/ref/dropuser.sgml                |   7 +-
 doc/src/sgml/ref/grant.sgml                   |   4 +-
 doc/src/sgml/user-manag.sgml                  |  44 ++++++--
 src/backend/catalog/objectaddress.c           |  10 +-
 src/backend/commands/user.c                   | 100 +++++++++++++-----
 .../expected/dummy_seclabel.out               |  17 +--
 .../dummy_seclabel/sql/dummy_seclabel.sql     |  13 ++-
 src/test/regress/expected/create_role.out     |  37 ++++---
 src/test/regress/sql/create_role.sql          |  23 ++--
 15 files changed, 181 insertions(+), 104 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 38618de01c..52eabe78a6 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3228,13 +3228,11 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
        name. Therefore, if each user has a separate schema, they access
        their own schemas by default.)  This pattern is a secure schema
        usage pattern unless an untrusted user is the database owner or
-       holds the <literal>CREATEROLE</literal> privilege, in which case no
-       secure schema usage pattern exists.
+       has been granted <literal>ADMIN OPTION</literal> on a relevant role,
+       in which case no secure schema usage pattern exists.
       </para>
       <!-- A database owner can attack the database's users via "CREATE SCHEMA
-           trojan; ALTER DATABASE $mydb SET search_path = trojan, public;".  A
-           CREATEROLE user can issue "GRANT $dbowner TO $me" and then use the
-           database owner attack. -->
+           trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". -->
 
       <para>
        In <productname>PostgreSQL</productname> 15 and later, the default
@@ -3262,7 +3260,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
        unreliable</link>.  If you create functions or extensions in the public
        schema, use the first pattern instead.  Otherwise, like the first
        pattern, this is secure unless an untrusted user is the database owner
-       or holds the <literal>CREATEROLE</literal> privilege.
+       or has been granted <literal>ADMIN OPTION</literal> on a relevant role.
       </para>
      </listitem>
 
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 922686e5ce..d7dff8ba21 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -73,7 +73,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
    Roles having <literal>CREATEROLE</literal> privilege can change any of these
    settings except <literal>SUPERUSER</literal>, <literal>REPLICATION</literal>,
    and <literal>BYPASSRLS</literal>; but only for non-superuser and
-   non-replication roles.
+   non-replication roles for which they have been
+   granted <literal>ADMIN OPTION</literal>.
    Ordinary roles can only change their own password.
   </para>
 
@@ -81,7 +82,7 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
    The second variant changes the name of the role.
    Database superusers can rename any role.
    Roles having <literal>CREATEROLE</literal> privilege can rename non-superuser
-   roles.
+   roles for which they have been granted <literal>ADMIN OPTION</literal>.
    The current session user cannot be renamed.
    (Connect as a different user if you need to do that.)
    Because <literal>MD5</literal>-encrypted passwords use the role name as
@@ -116,7 +117,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
   <para>
    Superusers can change anyone's session defaults. Roles having
    <literal>CREATEROLE</literal> privilege can change defaults for non-superuser
-   roles. Ordinary roles can only set defaults for themselves.
+   roles for which they have been granted <literal>ADMIN OPTION</literal>.
+   Ordinary roles can only set defaults for themselves.
    Certain configuration variables cannot be set this way, or can only be
    set if a superuser issues the command.  Only superusers can change a setting
    for all roles in all databases.
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 23d9029af9..7499da1d62 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -99,7 +99,8 @@ COMMENT ON
    For most kinds of object, only the object's owner can set the comment.
    Roles don't have owners, so the rule for <literal>COMMENT ON ROLE</literal> is
    that you must be superuser to comment on a superuser role, or have the
-   <literal>CREATEROLE</literal> privilege to comment on non-superuser roles.
+   <literal>CREATEROLE</literal> privilege and have been granted
+   <literal>ADMIN OPTION</literal> on the target role.
    Likewise, access methods don't have owners either; you must be superuser
    to comment on an access method.
    Of course, a superuser can comment on anything.
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 1ccc832558..0863acbcac 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -119,8 +119,8 @@ in sync when changing the above synopsis!
       <listitem>
        <para>
         These clauses determine whether a role will be permitted to
-        create, alter, drop, comment on, change the security label for,
-        and grant or revoke membership in other roles.
+        create, alter, drop, comment on, and change the security label for
+        other roles.
         See <xref linkend='role-creation' /> for more details about what
         capabilities are conferred by this privilege.
         If not specified, <literal>NOCREATEROLE</literal> is the default.
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index a41a2b24e6..f91dc500a4 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -252,8 +252,7 @@ PostgreSQL documentation
       <listitem>
        <para>
         The new user will be allowed to create, alter, drop, comment on,
-        change the security label for, and grant or revoke membership in
-        other roles; that is,
+        change the security label for other roles; that is,
         this user will have <literal>CREATEROLE</literal> privilege.
         See <xref linkend='role-creation' /> for more details about what
         capabilities are conferred by this privilege.
diff --git a/doc/src/sgml/ref/drop_role.sgml b/doc/src/sgml/ref/drop_role.sgml
index 13dc1cc649..cbcb3cd3d3 100644
--- a/doc/src/sgml/ref/drop_role.sgml
+++ b/doc/src/sgml/ref/drop_role.sgml
@@ -32,7 +32,7 @@ DROP ROLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...
    <command>DROP ROLE</command> removes the specified role(s).
    To drop a superuser role, you must be a superuser yourself;
    to drop non-superuser roles, you must have <literal>CREATEROLE</literal>
-   privilege.
+   privilege and have been granted <literal>ADMIN OPTION</literal> on the role.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml
index 81580507e8..b6be26d5b0 100644
--- a/doc/src/sgml/ref/dropuser.sgml
+++ b/doc/src/sgml/ref/dropuser.sgml
@@ -35,9 +35,10 @@ PostgreSQL documentation
   <para>
    <application>dropuser</application> removes an existing
    <productname>PostgreSQL</productname> user.
-   Only superusers and users with the <literal>CREATEROLE</literal> privilege can
-   remove <productname>PostgreSQL</productname> users.  (To remove a
-   superuser, you must yourself be a superuser.)
+   Superusers can use this command to remove any role; otherwise, only
+   non-superuser roles can be removed, and only by a user who possesses
+   the <literal>CREATEROLE</literal> privilege and has been granted
+   <literal>ADMIN OPTION</literal> on the target role.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index c3c585be7e..53b68ffb8a 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -272,9 +272,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
    in the role as well.  Without the admin option, ordinary users cannot
    do that.  A role is not considered to hold <literal>WITH ADMIN
    OPTION</literal> on itself.  Database superusers can grant or revoke
-   membership in any role to anyone.  Roles having
-   <literal>CREATEROLE</literal> privilege can grant or revoke membership
-   in any role that is not a superuser. This option defaults to
+   membership in any role to anyone. This option defaults to
    <literal>FALSE</literal>.
   </para>
 
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 0839daecca..84a29338ce 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -199,7 +199,12 @@ CREATE USER <replaceable>name</replaceable>;
         checks). To create such a role, use <literal>CREATE ROLE
         <replaceable>name</replaceable> CREATEROLE</literal>.
         A role with <literal>CREATEROLE</literal> privilege can alter and drop
-        other roles, too, as well as grant or revoke membership in them.
+        roles which have been granted to the <literal>CREATEROLE</literal>
+        user with the <literal>ADMIN</literal> option. Such a grant occurs
+        automatically when a <literal>CREATEROLE</literal> user that is not
+        a superuser creates a new role, so that by default, a
+        <literal>CREATEROLE</literal> user can alter and drop the roles
+        which they have created.
         Altering a role includes most changes that can be made using
         <literal>ALTER ROLE</literal>, including, for example, changing
         passwords.  It also includes modifications to a role that can
@@ -224,15 +229,6 @@ CREATE USER <replaceable>name</replaceable>;
         confer the ability to grant or revoke the <literal>BYPASSRLS</literal>
         privilege.
        </para>
-       <para>
-        Because the <literal>CREATEROLE</literal> privilege allows a user
-        to grant or revoke membership even in roles to which it does not (yet)
-        have any access, a <literal>CREATEROLE</literal> user can obtain access
-        to the capabilities of every predefined role in the system, including
-        highly privileged roles such as
-        <literal>pg_execute_server_program</literal> and
-        <literal>pg_write_server_files</literal>.
-       </para>
       </listitem>
      </varlistentry>
 
@@ -329,6 +325,34 @@ ALTER ROLE myname SET enable_indexscan TO off;
    <literal>LOGIN</literal> privilege are fairly useless, since they will never
    be invoked.
   </para>
+
+  <para>
+   When a non-superuser creates a role using the <literal>CREATEROLE</literal>
+   privilege, the created role is automatically granted back to the creating
+   user, just as if the bootstrap superuser had executed the command
+   <literal>GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE,
+   INHERIT FALSE</literal>. Since a <literal>CREATEROLE</literal> user can
+   only exercise special privileges with regard to an existing role if they
+   have <literal>ADMIN OPTION</literal> on it, this grant is just sufficient
+   to allow a <literal>CREATEROLE</literal> user to administer the roles they
+   created. However, because it is created with <literal>INHERIT FALSE, SET
+   FALSE</literal>, the <literal>CREATEROLE</literal> user doesn't inherit the
+   privileges of the created role, nor can it access the privileges of that
+   role using <literal>SET ROLE</literal>. However, since any user who has
+   <literal>ADMIN OPTION</literal> on a role can grant membership in that
+   role to any other user, the <literal>CREATEROLE</literal> user can gain
+   access to the created role by simplying granting that role back to
+   themselves with the <literal>INHERIT</literal> and/or <literal>SET</literal>
+   options. Thus, the fact that privileges are not inherited by default nor
+   is <literal>SET ROLE</literal> granted by default is a safeguard against
+   accidents, not a security feature. Also note that, because this automatic
+   grant is granted by the bootstrap user, it cannot be removed or changed by
+   the <literal>CREATEROLE</literal> user; however, any superuser could
+   revoke it, modify it, and/or issue additional such grants to other
+   <literal>CREATEROLE</literal> users. Whichever <literal>CREATEROLE</literal>
+   users have <literal>ADMIN OPTION</literal> on a role at any given time
+   can administer it.
+  </para>
  </sect1>
 
  <sect1 id="role-membership">
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index fe97fbf79d..bb5307dd2f 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2543,7 +2543,9 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
 
 			/*
 			 * We treat roles as being "owned" by those with CREATEROLE priv,
-			 * except that superusers are only owned by superusers.
+			 * provided that they also have admin option on the role.
+			 *
+			 * However, superusers are only owned by superusers.
 			 */
 			if (superuser_arg(address.objectId))
 			{
@@ -2558,6 +2560,12 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
 					ereport(ERROR,
 							(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 							 errmsg("must have CREATEROLE privilege")));
+				if (!is_admin_of_role(roleid, address.objectId))
+					ereport(ERROR,
+							(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+							 errmsg("must have admin option on role \"%s\"",
+									GetUserNameFromId(address.objectId,
+													  true))));
 			}
 			break;
 		case OBJECT_TSPARSER:
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 37fc4f9627..1bfc42a157 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -519,6 +519,42 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		}
 	}
 
+	/*
+	 * If the current user isn't a superuser, make them an admin of the new
+	 * role so that they can administer the new object they just created.
+	 * Superusers will be able to do that anyway.
+	 *
+	 * The grantor of record for this implicit grant is the bootstrap
+	 * superuser, which means that the CREATEROLE user cannot revoke the
+	 * grant. They can however grant the created role back to themselves
+	 * with different options, since they enjoy ADMIN OPTION on it.
+	 */
+	if (!superuser())
+	{
+		RoleSpec   *current_role = makeNode(RoleSpec);
+		GrantRoleOptions	poptself;
+
+		current_role->roletype = ROLESPEC_CURRENT_ROLE;
+		current_role->location = -1;
+
+		poptself.specified = GRANT_ROLE_SPECIFIED_ADMIN
+			| GRANT_ROLE_SPECIFIED_INHERIT
+			| GRANT_ROLE_SPECIFIED_SET;
+		poptself.admin = true;
+		poptself.inherit = false;
+		poptself.set = false;
+
+		AddRoleMems(BOOTSTRAP_SUPERUSERID, stmt->role, roleid,
+					list_make1(current_role), list_make1_oid(GetUserId()),
+					BOOTSTRAP_SUPERUSERID, &poptself);
+
+		/*
+		 * We must make the implicit grant visible to the code below, else
+		 * the additional grants will fail.
+		 */
+		CommandCounterIncrement();
+	}
+
 	/*
 	 * Add the specified members to this new role. adminmembers get the admin
 	 * option, rolemembers don't.
@@ -694,9 +730,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	/*
 	 * To mess with a superuser or replication role in any way you gotta be
 	 * superuser.  We also insist on superuser to change the BYPASSRLS
-	 * property.  Otherwise, if you don't have createrole, you're only allowed
-	 * to (1) change your own password or (2) add members to a role for which
-	 * you have ADMIN OPTION.
+	 * property.
 	 */
 	if (authform->rolsuper || dissuper)
 	{
@@ -719,29 +753,35 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must be superuser to change bypassrls attribute")));
 	}
-	else if (!have_createrole_privilege())
+
+	/*
+	 * Most changes to a role require that you both have CREATEROLE privileges
+	 * and also ADMIN OPTION on the role.
+	 */
+	if (!have_createrole_privilege() ||
+		!is_admin_of_role(GetUserId(), roleid))
 	{
-		/* things you certainly can't do without CREATEROLE */
+		/* things an unprivileged user certainly can't do */
 		if (dinherit || dcreaterole || dcreatedb || dcanlogin || dconnlimit ||
 			dvalidUntil)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied")));
 
-		/* without CREATEROLE, can only change your own password */
+		/* an unprivileged user can change their own password */
 		if (dpassword && roleid != currentUserId)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have CREATEROLE privilege to change another user's password")));
-
-		/* without CREATEROLE, can only add members to roles you admin */
-		if (drolemembers && !is_admin_of_role(currentUserId, roleid))
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must have admin option on role \"%s\" to add members",
-							rolename)));
 	}
 
+	/* To add members to a role, you need ADMIN OPTION. */
+	if (drolemembers && !is_admin_of_role(currentUserId, roleid))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must have admin option on role \"%s\" to add members",
+						rolename)));
+
 	/* Convert validuntil to internal form */
 	if (dvalidUntil)
 	{
@@ -935,8 +975,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
 		shdepLockAndCheckObject(AuthIdRelationId, roleid);
 
 		/*
-		 * To mess with a superuser you gotta be superuser; else you need
-		 * createrole, or just want to change your own settings
+		 * To mess with a superuser you gotta be superuser; otherwise you
+		 * need CREATEROLE plus admin option on the target role; unless you're
+		 * just trying to change your own settings
 		 */
 		if (roleform->rolsuper)
 		{
@@ -947,7 +988,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
 		}
 		else
 		{
-			if (!have_createrole_privilege() && roleid != GetUserId())
+			if ((!have_createrole_privilege() ||
+				!is_admin_of_role(GetUserId(), roleid))
+				&& roleid != GetUserId())
 				ereport(ERROR,
 						(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 						 errmsg("permission denied")));
@@ -1067,13 +1110,18 @@ DropRole(DropRoleStmt *stmt)
 
 		/*
 		 * For safety's sake, we allow createrole holders to drop ordinary
-		 * roles but not superuser roles.  This is mainly to avoid the
-		 * scenario where you accidentally drop the last superuser.
+		 * roles but not superuser roles, and only if they also have ADMIN
+		 * OPTION.
 		 */
 		if (roleform->rolsuper && !superuser())
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must be superuser to drop superusers")));
+		if (!is_admin_of_role(GetUserId(), roleid))
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("must have admin option on role \"%s\"",
+							role)));
 
 		/* DROP hook for the role being removed */
 		InvokeObjectDropHook(AuthIdRelationId, roleid, 0);
@@ -1312,7 +1360,8 @@ RenameRole(const char *oldname, const char *newname)
 				 errmsg("role \"%s\" already exists", newname)));
 
 	/*
-	 * createrole is enough privilege unless you want to mess with a superuser
+	 * Only superusers can mess with superusers. Otherwise, a user with
+	 * CREATEROLE can rename a role for which they have ADMIN OPTION.
 	 */
 	if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper)
 	{
@@ -1323,7 +1372,8 @@ RenameRole(const char *oldname, const char *newname)
 	}
 	else
 	{
-		if (!have_createrole_privilege())
+		if (!have_createrole_privilege() ||
+			!is_admin_of_role(GetUserId(), roleid))
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied to rename role")));
@@ -2022,11 +2072,9 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid,
 	else
 	{
 		/*
-		 * Otherwise, must have createrole or admin option on the role to be
-		 * changed.
+		 * Otherwise, must have admin option on the role to be changed.
 		 */
-		if (!has_createrole_privilege(currentUserId) &&
-			!is_admin_of_role(currentUserId, roleid))
+		if (!is_admin_of_role(currentUserId, roleid))
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have admin option on role \"%s\"",
@@ -2048,7 +2096,7 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid,
  * be passed as InvalidOid, and this function will infer the user to be
  * recorded as the grantor. In many cases, this will be the current user, but
  * things get more complicated when the current user doesn't possess ADMIN
- * OPTION on the role but rather relies on having CREATEROLE privileges, or
+ * OPTION on the role but rather relies on having SUPERUSER privileges, or
  * on inheriting the privileges of a role which does have ADMIN OPTION. See
  * below for details.
  *
@@ -2074,7 +2122,7 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant)
 		 * not depend on any other existing grants, so always default to this
 		 * interpretation when possible.
 		 */
-		if (has_createrole_privilege(currentUserId))
+		if (superuser_arg(currentUserId))
 			return BOOTSTRAP_SUPERUSERID;
 
 		/*
diff --git a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
index b2d898a7d1..c57d4fd2df 100644
--- a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
+++ b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
@@ -6,9 +6,11 @@ CREATE EXTENSION dummy_seclabel;
 SET client_min_messages TO 'warning';
 DROP ROLE IF EXISTS regress_dummy_seclabel_user1;
 DROP ROLE IF EXISTS regress_dummy_seclabel_user2;
+DROP ROLE IF EXISTS regress_dummy_seclabel_user3;
 RESET client_min_messages;
 CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE;
 CREATE USER regress_dummy_seclabel_user2;
+CREATE USER regress_dummy_seclabel_user3;
 CREATE TABLE dummy_seclabel_tbl1 (a int, b text);
 CREATE TABLE dummy_seclabel_tbl2 (x int, y text);
 CREATE VIEW dummy_seclabel_view1 AS SELECT * FROM dummy_seclabel_tbl2;
@@ -16,6 +18,8 @@ CREATE FUNCTION dummy_seclabel_four() RETURNS integer AS $$SELECT 4$$ language s
 CREATE DOMAIN dummy_seclabel_domain AS text;
 ALTER TABLE dummy_seclabel_tbl1 OWNER TO regress_dummy_seclabel_user1;
 ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2;
+GRANT regress_dummy_seclabel_user2, regress_dummy_seclabel_user3
+  TO regress_dummy_seclabel_user1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
 --
 -- Test of SECURITY LABEL statement with a plugin
 --
@@ -43,16 +47,16 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified';			-- OK
 -- Test for shared database object
 --
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user1;
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified';			-- OK
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...';	-- fail
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'classified';			-- OK
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS '...invalid label...';	-- fail
 ERROR:  '...invalid label...' is not a valid security label
 SECURITY LABEL FOR 'dummy' ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- OK
 SECURITY LABEL FOR 'unknown_seclabel' ON ROLE regress_dummy_seclabel_user1 IS 'unclassified';	-- fail
 ERROR:  security label provider "unknown_seclabel" is not loaded
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'secret';	-- fail (not superuser)
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'secret';	-- fail (not superuser)
 ERROR:  only superuser can set 'secret' label
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'unclassified';	-- fail (not found)
-ERROR:  role "regress_dummy_seclabel_user3" does not exist
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user4 IS 'unclassified';	-- fail (not found)
+ERROR:  role "regress_dummy_seclabel_user4" does not exist
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user2;
 SECURITY LABEL ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- fail (not privileged)
 ERROR:  must have CREATEROLE privilege
@@ -81,8 +85,8 @@ SELECT objtype, objname, provider, label FROM pg_seclabels
  domain       | dummy_seclabel_domain        | dummy    | classified
  function     | dummy_seclabel_four()        | dummy    | classified
  publication  | dummy_pub                    | dummy    | classified
- role         | regress_dummy_seclabel_user1 | dummy    | classified
  role         | regress_dummy_seclabel_user2 | dummy    | unclassified
+ role         | regress_dummy_seclabel_user3 | dummy    | classified
  schema       | dummy_seclabel_test          | dummy    | unclassified
  subscription | dummy_sub                    | dummy    | classified
  table        | dummy_seclabel_tbl1          | dummy    | top secret
@@ -115,3 +119,4 @@ DROP SUBSCRIPTION dummy_sub;
 DROP PUBLICATION dummy_pub;
 DROP ROLE regress_dummy_seclabel_user1;
 DROP ROLE regress_dummy_seclabel_user2;
+DROP ROLE regress_dummy_seclabel_user3;
diff --git a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
index 8c347b6a68..649409757e 100644
--- a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
+++ b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
@@ -8,11 +8,13 @@ SET client_min_messages TO 'warning';
 
 DROP ROLE IF EXISTS regress_dummy_seclabel_user1;
 DROP ROLE IF EXISTS regress_dummy_seclabel_user2;
+DROP ROLE IF EXISTS regress_dummy_seclabel_user3;
 
 RESET client_min_messages;
 
 CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE;
 CREATE USER regress_dummy_seclabel_user2;
+CREATE USER regress_dummy_seclabel_user3;
 
 CREATE TABLE dummy_seclabel_tbl1 (a int, b text);
 CREATE TABLE dummy_seclabel_tbl2 (x int, y text);
@@ -22,6 +24,8 @@ CREATE DOMAIN dummy_seclabel_domain AS text;
 
 ALTER TABLE dummy_seclabel_tbl1 OWNER TO regress_dummy_seclabel_user1;
 ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2;
+GRANT regress_dummy_seclabel_user2, regress_dummy_seclabel_user3
+  TO regress_dummy_seclabel_user1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
 
 --
 -- Test of SECURITY LABEL statement with a plugin
@@ -47,12 +51,12 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified';			-- OK
 --
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user1;
 
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified';			-- OK
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...';	-- fail
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'classified';			-- OK
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS '...invalid label...';	-- fail
 SECURITY LABEL FOR 'dummy' ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- OK
 SECURITY LABEL FOR 'unknown_seclabel' ON ROLE regress_dummy_seclabel_user1 IS 'unclassified';	-- fail
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'secret';	-- fail (not superuser)
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'unclassified';	-- fail (not found)
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'secret';	-- fail (not superuser)
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user4 IS 'unclassified';	-- fail (not found)
 
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user2;
 SECURITY LABEL ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- fail (not privileged)
@@ -113,3 +117,4 @@ DROP PUBLICATION dummy_pub;
 
 DROP ROLE regress_dummy_seclabel_user1;
 DROP ROLE regress_dummy_seclabel_user2;
+DROP ROLE regress_dummy_seclabel_user3;
diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out
index 4e67d72760..6e0cce4579 100644
--- a/src/test/regress/expected/create_role.out
+++ b/src/test/regress/expected/create_role.out
@@ -13,7 +13,7 @@ CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
 ERROR:  must be superuser to create bypassrls users
 -- ok, having CREATEROLE is enough to create users with these privileges
 CREATE ROLE regress_createdb CREATEDB;
-CREATE ROLE regress_createrole CREATEROLE;
+CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
 CREATE ROLE regress_login LOGIN;
 CREATE ROLE regress_inherit INHERIT;
 CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -70,20 +70,35 @@ ALTER VIEW tenant_view OWNER TO regress_role_admin;
 ERROR:  must be owner of view tenant_view
 DROP VIEW tenant_view;
 ERROR:  must be owner of view tenant_view
--- fail, cannot take ownership of these objects from regress_tenant
+-- fail, we don't inherit permissions from regress_tenant
 REASSIGN OWNED BY regress_tenant TO regress_createrole;
 ERROR:  permission denied to reassign objects
--- ok, having CREATEROLE is enough to create roles in privileged roles
+-- fail, CREATEROLE is not enough to create roles in privileged roles
 CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
+ERROR:  must have admin option on role "pg_read_all_data"
 CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
+ERROR:  must have admin option on role "pg_write_all_data"
 CREATE ROLE regress_monitor IN ROLE pg_monitor;
+ERROR:  must have admin option on role "pg_monitor"
 CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings;
+ERROR:  must have admin option on role "pg_read_all_settings"
 CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats;
+ERROR:  must have admin option on role "pg_read_all_stats"
 CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables;
+ERROR:  must have admin option on role "pg_stat_scan_tables"
 CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files;
+ERROR:  must have admin option on role "pg_read_server_files"
 CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files;
+ERROR:  must have admin option on role "pg_write_server_files"
 CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program;
+ERROR:  must have admin option on role "pg_execute_server_program"
 CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend;
+ERROR:  must have admin option on role "pg_signal_backend"
+-- fail, role still owns database objects
+DROP ROLE regress_tenant;
+ERROR:  role "regress_tenant" cannot be dropped because some objects depend on it
+DETAIL:  owner of table tenant_table
+owner of view tenant_view
 -- fail, creation of these roles failed above so they do not now exist
 SET SESSION AUTHORIZATION regress_role_admin;
 DROP ROLE regress_nosuch_superuser;
@@ -114,22 +129,6 @@ DROP ROLE regress_password_null;
 DROP ROLE regress_noiseword;
 DROP ROLE regress_inroles;
 DROP ROLE regress_adminroles;
-DROP ROLE regress_rolecreator;
-DROP ROLE regress_read_all_data;
-DROP ROLE regress_write_all_data;
-DROP ROLE regress_monitor;
-DROP ROLE regress_read_all_settings;
-DROP ROLE regress_read_all_stats;
-DROP ROLE regress_stat_scan_tables;
-DROP ROLE regress_read_server_files;
-DROP ROLE regress_write_server_files;
-DROP ROLE regress_execute_server_program;
-DROP ROLE regress_signal_backend;
--- fail, role still owns database objects
-DROP ROLE regress_tenant;
-ERROR:  role "regress_tenant" cannot be dropped because some objects depend on it
-DETAIL:  owner of table tenant_table
-owner of view tenant_view
 -- fail, cannot drop ourself nor superusers
 DROP ROLE regress_role_super;
 ERROR:  must be superuser to drop superusers
diff --git a/src/test/regress/sql/create_role.sql b/src/test/regress/sql/create_role.sql
index 292dc08797..d491684db3 100644
--- a/src/test/regress/sql/create_role.sql
+++ b/src/test/regress/sql/create_role.sql
@@ -11,7 +11,7 @@ CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
 
 -- ok, having CREATEROLE is enough to create users with these privileges
 CREATE ROLE regress_createdb CREATEDB;
-CREATE ROLE regress_createrole CREATEROLE;
+CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
 CREATE ROLE regress_login LOGIN;
 CREATE ROLE regress_inherit INHERIT;
 CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -71,10 +71,10 @@ DROP TABLE tenant_table;
 ALTER VIEW tenant_view OWNER TO regress_role_admin;
 DROP VIEW tenant_view;
 
--- fail, cannot take ownership of these objects from regress_tenant
+-- fail, we don't inherit permissions from regress_tenant
 REASSIGN OWNED BY regress_tenant TO regress_createrole;
 
--- ok, having CREATEROLE is enough to create roles in privileged roles
+-- fail, CREATEROLE is not enough to create roles in privileged roles
 CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
 CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
 CREATE ROLE regress_monitor IN ROLE pg_monitor;
@@ -86,6 +86,9 @@ CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files;
 CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program;
 CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend;
 
+-- fail, role still owns database objects
+DROP ROLE regress_tenant;
+
 -- fail, creation of these roles failed above so they do not now exist
 SET SESSION AUTHORIZATION regress_role_admin;
 DROP ROLE regress_nosuch_superuser;
@@ -109,20 +112,6 @@ DROP ROLE regress_password_null;
 DROP ROLE regress_noiseword;
 DROP ROLE regress_inroles;
 DROP ROLE regress_adminroles;
-DROP ROLE regress_rolecreator;
-DROP ROLE regress_read_all_data;
-DROP ROLE regress_write_all_data;
-DROP ROLE regress_monitor;
-DROP ROLE regress_read_all_settings;
-DROP ROLE regress_read_all_stats;
-DROP ROLE regress_stat_scan_tables;
-DROP ROLE regress_read_server_files;
-DROP ROLE regress_write_server_files;
-DROP ROLE regress_execute_server_program;
-DROP ROLE regress_signal_backend;
-
--- fail, role still owns database objects
-DROP ROLE regress_tenant;
 
 -- fail, cannot drop ourself nor superusers
 DROP ROLE regress_role_super;
-- 
2.24.3 (Apple Git-128)

From 615e4bb8ce69049eb83f5ca80e47952b13438ba5 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Fri, 2 Dec 2022 08:54:18 -0500
Subject: [PATCH v2 5/5] Add new GUC createrole_self_grant.

Can be set to the empty string, or to either or both of "set" or
"inherit". If set to a non-empty value, a non-superuser who creates
a role (necessarily by relying up the CREATEROLE privilege) will
grant that role back to themselves with the specified options.

This isn't a security feature, because the grant that this feature
triggers can also be performed explicitly. Instead, it's a user experience
feature. A superuser would necessarily inherit the privileges of any
created role and be able to access all such roles via SET ROLE;
with this patch, you can configure createrole_self_grant = 'set, inherit'
to provide a similar experience for a user who has CREATEROLE but not
SUPERUSER.

FIXME: Add some regression tests.
---
 doc/src/sgml/config.sgml                      | 33 +++++++
 doc/src/sgml/ref/create_role.sgml             |  1 +
 doc/src/sgml/ref/createuser.sgml              |  1 +
 src/backend/commands/user.c                   | 97 ++++++++++++++++++-
 src/backend/utils/misc/guc_tables.c           | 12 +++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/commands/user.h                   | 10 +-
 7 files changed, 150 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 39d1c89e33..503a92248e 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9365,6 +9365,39 @@ SET XML OPTION { DOCUMENT | CONTENT };
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-createrole-self-grant" xreflabel="createrole_self_grant">
+      <term><varname>createrole_self_grant</varname> (<type>string</type>)
+      <indexterm>
+       <primary><varname>createrole_self_grant</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        If a user who has <literal>CREATEROLE</literal> but not
+        <literal>SUPERUSER</literal> creates a role, and if this
+        is set to a non-empty value, the newly-created role will be granted
+        to the creating user with the options specified. The value must be
+        <literal>set</literal>, <literal>inherit</literal>, or a
+        comma-separated list of these.
+       </para>
+       <para>
+        The purpose of this option is to allow a <literal>CREATEROLE</literal>
+        user who is not a superuser to automatically inherit, or automatically
+        gain the ability to <literal>SET ROLE</literal> to, any created users.
+        Since a <literal>CREATEROLE</literal> user is always implicitly granted
+        <literal>ADMIN OPTION</literal> on created roles, that user could
+        always execute a <literal>GRANT</literal> statement that would achieve
+        the same effect as this setting. However, it can be convenient for
+        usability reasons if the grant happens automatically. A superuser
+        automatically inherits the privileges of every role and can always
+        <literal>SET ROLE</literal> to any role, and this setting can be used
+        to produce a similar behavior for <literal>CREATEROLE</literal> users
+        for users which they create.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
     </sect2>
      <sect2 id="runtime-config-client-format">
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 0863acbcac..7ce4e38b45 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -506,6 +506,7 @@ CREATE ROLE <replaceable class="parameter">name</replaceable> [ WITH ADMIN <repl
    <member><xref linkend="sql-grant"/></member>
    <member><xref linkend="sql-revoke"/></member>
    <member><xref linkend="app-createuser"/></member>
+   <member><xref linkend="guc-createrole-self-grant"/></member>
   </simplelist>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index f91dc500a4..9a1c3d01f4 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -555,6 +555,7 @@ PostgreSQL documentation
   <simplelist type="inline">
    <member><xref linkend="app-dropuser"/></member>
    <member><xref linkend="sql-createrole"/></member>
+   <member><xref linkend="guc-createrole-self-grant"/></member>
   </simplelist>
  </refsect1>
 
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 1bfc42a157..8ba218e792 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -39,6 +39,7 @@
 #include "utils/fmgroids.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
+#include "utils/varlena.h"
 
 /*
  * Removing a role grant - or the admin option on it - might recurse to
@@ -81,8 +82,11 @@ typedef struct
 #define GRANT_ROLE_SPECIFIED_INHERIT		0x0002
 #define GRANT_ROLE_SPECIFIED_SET			0x0004
 
-/* GUC parameter */
+/* GUC parameters */
 int			Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
+char	   *createrole_self_grant = "";
+bool		createrole_self_grant_enabled = false;
+GrantRoleOptions	createrole_self_grant_options;
 
 /* Hook to check passwords in CreateRole() and AlterRole() */
 check_password_hook_type check_password_hook = NULL;
@@ -532,10 +536,13 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	if (!superuser())
 	{
 		RoleSpec   *current_role = makeNode(RoleSpec);
-		GrantRoleOptions	poptself;
+		GrantRoleOptions poptself;
+		List	   *memberSpecs;
+		List	   *memberIds = list_make1_oid(currentUserId);
 
 		current_role->roletype = ROLESPEC_CURRENT_ROLE;
 		current_role->location = -1;
+		memberSpecs = list_make1(current_role);
 
 		poptself.specified = GRANT_ROLE_SPECIFIED_ADMIN
 			| GRANT_ROLE_SPECIFIED_INHERIT
@@ -545,7 +552,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		poptself.set = false;
 
 		AddRoleMems(BOOTSTRAP_SUPERUSERID, stmt->role, roleid,
-					list_make1(current_role), list_make1_oid(GetUserId()),
+					memberSpecs, memberIds,
 					BOOTSTRAP_SUPERUSERID, &poptself);
 
 		/*
@@ -553,6 +560,20 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		 * the additional grants will fail.
 		 */
 		CommandCounterIncrement();
+
+		/*
+		 * Because of the implicit grant above, a CREATEROLE user who creates
+		 * a role has the ability to grant that role back to themselves with
+		 * the INHERIT or SET options, if they wish to inherit the role's
+		 * privileges or be able to SET ROLE to it. The createrole_self_grant
+		 * GUC can be used to make this happen automatically. This has no
+		 * security implications since the same user is able to make the same
+		 * grant using an explicit GRANT statement; it's just convenient.
+		 */
+		if (createrole_self_grant_enabled)
+			AddRoleMems(currentUserId, stmt->role, roleid,
+						memberSpecs, memberIds,
+						currentUserId, &createrole_self_grant_options);
 	}
 
 	/*
@@ -2413,3 +2434,73 @@ InitGrantRoleOptions(GrantRoleOptions *popt)
 	popt->inherit = false;
 	popt->set = true;
 }
+
+/*
+ * GUC check_hook for createrole_self_grant
+ */
+bool
+check_createrole_self_grant(char **newval, void **extra, GucSource source)
+{
+	char	   *rawstring;
+	List	   *elemlist;
+	ListCell   *l;
+	unsigned	options = 0;
+	unsigned   *result;
+
+	/* Need a modifiable copy of string */
+	rawstring = pstrdup(*newval);
+
+	if (!SplitIdentifierString(rawstring, ',', &elemlist))
+	{
+		/* syntax error in list */
+		GUC_check_errdetail("List syntax is invalid.");
+		pfree(rawstring);
+		list_free(elemlist);
+		return false;
+	}
+
+	foreach(l, elemlist)
+	{
+		char	   *tok = (char *) lfirst(l);
+
+		if (pg_strcasecmp(tok, "SET") == 0)
+			options |= GRANT_ROLE_SPECIFIED_SET;
+		else if (pg_strcasecmp(tok, "INHERIT") == 0)
+			options |= GRANT_ROLE_SPECIFIED_INHERIT;
+		else
+		{
+			GUC_check_errdetail("Unrecognized key word: \"%s\".", tok);
+			pfree(rawstring);
+			list_free(elemlist);
+			return false;
+		}
+	}
+
+	pfree(rawstring);
+	list_free(elemlist);
+
+	result = (unsigned *) guc_malloc(LOG, sizeof(unsigned));
+	*result = options;
+	*extra = result;
+
+	return true;
+}
+
+/*
+ * GUC assign_hook for createrole_self_grant
+ */
+void
+assign_createrole_self_grant(const char *newval, void *extra)
+{
+	unsigned	options = * (unsigned *) extra;
+
+	createrole_self_grant_enabled = (options != 0);
+	createrole_self_grant_options.specified = GRANT_ROLE_SPECIFIED_ADMIN
+		| GRANT_ROLE_SPECIFIED_INHERIT
+		| GRANT_ROLE_SPECIFIED_SET;
+	createrole_self_grant_options.admin = false;
+	createrole_self_grant_options.inherit =
+		(options & GRANT_ROLE_SPECIFIED_INHERIT) != 0;
+	createrole_self_grant_options.set =
+		(options & GRANT_ROLE_SPECIFIED_SET) != 0;
+}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 1bf14eec66..3773be9ce4 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3916,6 +3916,18 @@ struct config_string ConfigureNamesString[] =
 		check_temp_tablespaces, assign_temp_tablespaces, NULL
 	},
 
+	{
+		{"createrole_self_grant", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Sets whether a CREATEROLE user automatically grants "
+						 "the role to themselves, and with which options."),
+			NULL,
+			GUC_LIST_INPUT
+		},
+		&createrole_self_grant,
+		"",
+		check_createrole_self_grant, assign_createrole_self_grant, NULL
+	},
+
 	{
 		{"dynamic_library_path", PGC_SUSET, CLIENT_CONN_OTHER,
 			gettext_noop("Sets the path for dynamically loadable modules."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 043864597f..efa005b38d 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -701,6 +701,7 @@
 #xmlbinary = 'base64'
 #xmloption = 'content'
 #gin_pending_list_limit = 4MB
+#createrole_self_grant = ''		# set and/or inherit
 
 # - Locale and Formatting -
 
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 54c720d880..97dcb93791 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -15,9 +15,11 @@
 #include "libpq/crypt.h"
 #include "nodes/parsenodes.h"
 #include "parser/parse_node.h"
+#include "utils/guc.h"
 
-/* GUC. Is actually of type PasswordType. */
-extern PGDLLIMPORT int Password_encryption;
+/* GUCs */
+extern PGDLLIMPORT int Password_encryption; /* values from enum PasswordType */
+extern PGDLLIMPORT char *createrole_self_grant;
 
 /* Hook to check passwords in CreateRole() and AlterRole() */
 typedef void (*check_password_hook_type) (const char *username, const char *shadow_pass, PasswordType password_type, Datum validuntil_time, bool validuntil_null);
@@ -34,4 +36,8 @@ extern void DropOwnedObjects(DropOwnedStmt *stmt);
 extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt);
 extern List *roleSpecsToIds(List *memberNames);
 
+extern bool check_createrole_self_grant(char **newval, void **extra,
+										GucSource source);
+extern void assign_createrole_self_grant(const char *newval, void *extra);
+
 #endif							/* USER_H */
-- 
2.24.3 (Apple Git-128)

From 512a18f4d98728844fc4ce030cc71ef074691cce Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Wed, 24 Aug 2022 14:55:01 -0400
Subject: [PATCH v3 1/4] Refactor permissions-checking for role grants.

Instead of having checks in AddRoleMems() and DelRoleMems(), have
the callers perform checks where it's required. In some cases it
isn't, either because the caller has already performed a check for
the same condition, or because the check couldn't possibly fail.

The "Skip permission check if nothing to do" check in each of
AddRoleMems() and DelRoleMems() is pointless. Most call sites
can't pass an empty list, and in the one case where an empty
list could be passed, the presence of this check couldn't possibly
avoid an error.
---
 src/backend/commands/user.c | 116 +++++++++++++++++-------------------
 1 file changed, 54 insertions(+), 62 deletions(-)

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index aee26f1789..280031e6eb 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -94,6 +94,8 @@ static void DelRoleMems(const char *rolename, Oid roleid,
 						List *memberSpecs, List *memberIds,
 						Oid grantorId, GrantRoleOptions *popt,
 						DropBehavior behavior);
+static void check_role_membership_authorization(Oid currentUserId, Oid roleid,
+												bool is_grant);
 static Oid	check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId,
 							   bool is_grant);
 static RevokeRoleGrantAction *initialize_revoke_actions(CatCList *memlist);
@@ -505,6 +507,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 			Oid			oldroleid = oldroleform->oid;
 			char	   *oldrolename = NameStr(oldroleform->rolname);
 
+			/* can only add this role to roles for which you have rights */
+			check_role_membership_authorization(GetUserId(), oldroleid, true);
 			AddRoleMems(oldrolename, oldroleid,
 						thisrole_list,
 						thisrole_oidlist,
@@ -517,6 +521,9 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	/*
 	 * Add the specified members to this new role. adminmembers get the admin
 	 * option, rolemembers don't.
+	 *
+	 * NB: No permissions check is required here. If you have enough rights
+	 * to create a role, you can add any members you like.
 	 */
 	AddRoleMems(stmt->role, roleid,
 				rolemembers, roleSpecsToIds(rolemembers),
@@ -1442,6 +1449,8 @@ GrantRole(ParseState *pstate, GrantRoleStmt *stmt)
 					 errmsg("column names cannot be included in GRANT/REVOKE ROLE")));
 
 		roleid = get_role_oid(rolename, false);
+		check_role_membership_authorization(GetUserId(), roleid,
+											stmt->is_grant);
 		if (stmt->is_grant)
 			AddRoleMems(rolename, roleid,
 						stmt->grantee_roles, grantee_ids,
@@ -1566,43 +1575,6 @@ AddRoleMems(const char *rolename, Oid roleid,
 
 	Assert(list_length(memberSpecs) == list_length(memberIds));
 
-	/* Skip permission check if nothing to do */
-	if (!memberIds)
-		return;
-
-	/*
-	 * Check permissions: must have createrole or admin option on the role to
-	 * be changed.  To mess with a superuser role, you gotta be superuser.
-	 */
-	if (superuser_arg(roleid))
-	{
-		if (!superuser())
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must be superuser to alter superusers")));
-	}
-	else
-	{
-		if (!have_createrole_privilege() &&
-			!is_admin_of_role(currentUserId, roleid))
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must have admin option on role \"%s\"",
-							rolename)));
-	}
-
-	/*
-	 * The charter of pg_database_owner is to have exactly one, implicit,
-	 * situation-dependent member.  There's no technical need for this
-	 * restriction.  (One could lift it and take the further step of making
-	 * object_ownercheck(DatabaseRelationId, ...) equivalent to has_privs_of_role(roleid,
-	 * ROLE_PG_DATABASE_OWNER), in which case explicit, situation-independent
-	 * members could act as the owner of any database.)
-	 */
-	if (roleid == ROLE_PG_DATABASE_OWNER)
-		ereport(ERROR,
-				errmsg("role \"%s\" cannot have explicit members", rolename));
-
 	/* Validate grantor (and resolve implicit grantor if not specified). */
 	grantorId = check_role_grantor(currentUserId, roleid, grantorId, true);
 
@@ -1902,31 +1874,6 @@ DelRoleMems(const char *rolename, Oid roleid,
 
 	Assert(list_length(memberSpecs) == list_length(memberIds));
 
-	/* Skip permission check if nothing to do */
-	if (!memberIds)
-		return;
-
-	/*
-	 * Check permissions: must have createrole or admin option on the role to
-	 * be changed.  To mess with a superuser role, you gotta be superuser.
-	 */
-	if (superuser_arg(roleid))
-	{
-		if (!superuser())
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must be superuser to alter superusers")));
-	}
-	else
-	{
-		if (!have_createrole_privilege() &&
-			!is_admin_of_role(currentUserId, roleid))
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must have admin option on role \"%s\"",
-							rolename)));
-	}
-
 	/* Validate grantor (and resolve implicit grantor if not specified). */
 	grantorId = check_role_grantor(currentUserId, roleid, grantorId, false);
 
@@ -2040,6 +1987,51 @@ DelRoleMems(const char *rolename, Oid roleid,
 	table_close(pg_authmem_rel, NoLock);
 }
 
+/*
+ * Check that currentUserId has permission to modify the membership list for
+ * roleid. Throw an error if not.
+ */
+static void
+check_role_membership_authorization(Oid currentUserId, Oid roleid,
+									bool is_grant)
+{
+	/*
+	 * The charter of pg_database_owner is to have exactly one, implicit,
+	 * situation-dependent member.  There's no technical need for this
+	 * restriction.  (One could lift it and take the further step of making
+	 * object_ownercheck(DatabaseRelationId, ...) equivalent to
+	 * has_privs_of_role(roleid, ROLE_PG_DATABASE_OWNER), in which case
+	 * explicit, situation-independent members could act as the owner of any
+	 * database.)
+	 */
+	if (is_grant && roleid == ROLE_PG_DATABASE_OWNER)
+		ereport(ERROR,
+				errmsg("role \"%s\" cannot have explicit members",
+					   GetUserNameFromId(roleid, false)));
+
+	/* To mess with a superuser role, you gotta be superuser. */
+	if (superuser_arg(roleid))
+	{
+		if (!superuser_arg(currentUserId))
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("must be superuser to alter superusers")));
+	}
+	else
+	{
+		/*
+		 * Otherwise, must have createrole or admin option on the role to be
+		 * changed.
+		 */
+		if (!has_createrole_privilege(currentUserId) &&
+			!is_admin_of_role(currentUserId, roleid))
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("must have admin option on role \"%s\"",
+							GetUserNameFromId(roleid, false))));
+	}
+}
+
 /*
  * Sanity-check, or infer, the grantor for a GRANT or REVOKE statement
  * targeting a role.
-- 
2.37.1 (Apple Git-137.1)

From eb7cd95567407d61e11bd357d2e0e42cc01cc324 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Fri, 2 Dec 2022 08:54:18 -0500
Subject: [PATCH v3 4/4] Add new GUC createrole_self_grant.

Can be set to the empty string, or to either or both of "set" or
"inherit". If set to a non-empty value, a non-superuser who creates
a role (necessarily by relying up the CREATEROLE privilege) will
grant that role back to themselves with the specified options.

This isn't a security feature, because the grant that this feature
triggers can also be performed explicitly. Instead, it's a user experience
feature. A superuser would necessarily inherit the privileges of any
created role and be able to access all such roles via SET ROLE;
with this patch, you can configure createrole_self_grant = 'set, inherit'
to provide a similar experience for a user who has CREATEROLE but not
SUPERUSER.

FIXME: Add some regression tests.
---
 doc/src/sgml/config.sgml                      | 33 +++++++
 doc/src/sgml/ref/create_role.sgml             |  1 +
 doc/src/sgml/ref/createuser.sgml              |  1 +
 src/backend/commands/user.c                   | 97 ++++++++++++++++++-
 src/backend/utils/misc/guc_tables.c           | 12 +++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/commands/user.h                   | 10 +-
 7 files changed, 150 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 05b3862d09..d86385d308 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9421,6 +9421,39 @@ SET XML OPTION { DOCUMENT | CONTENT };
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-createrole-self-grant" xreflabel="createrole_self_grant">
+      <term><varname>createrole_self_grant</varname> (<type>string</type>)
+      <indexterm>
+       <primary><varname>createrole_self_grant</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        If a user who has <literal>CREATEROLE</literal> but not
+        <literal>SUPERUSER</literal> creates a role, and if this
+        is set to a non-empty value, the newly-created role will be granted
+        to the creating user with the options specified. The value must be
+        <literal>set</literal>, <literal>inherit</literal>, or a
+        comma-separated list of these.
+       </para>
+       <para>
+        The purpose of this option is to allow a <literal>CREATEROLE</literal>
+        user who is not a superuser to automatically inherit, or automatically
+        gain the ability to <literal>SET ROLE</literal> to, any created users.
+        Since a <literal>CREATEROLE</literal> user is always implicitly granted
+        <literal>ADMIN OPTION</literal> on created roles, that user could
+        always execute a <literal>GRANT</literal> statement that would achieve
+        the same effect as this setting. However, it can be convenient for
+        usability reasons if the grant happens automatically. A superuser
+        automatically inherits the privileges of every role and can always
+        <literal>SET ROLE</literal> to any role, and this setting can be used
+        to produce a similar behavior for <literal>CREATEROLE</literal> users
+        for users which they create.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
     </sect2>
      <sect2 id="runtime-config-client-format">
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 0863acbcac..7ce4e38b45 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -506,6 +506,7 @@ CREATE ROLE <replaceable class="parameter">name</replaceable> [ WITH ADMIN <repl
    <member><xref linkend="sql-grant"/></member>
    <member><xref linkend="sql-revoke"/></member>
    <member><xref linkend="app-createuser"/></member>
+   <member><xref linkend="guc-createrole-self-grant"/></member>
   </simplelist>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index f91dc500a4..9a1c3d01f4 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -555,6 +555,7 @@ PostgreSQL documentation
   <simplelist type="inline">
    <member><xref linkend="app-dropuser"/></member>
    <member><xref linkend="sql-createrole"/></member>
+   <member><xref linkend="guc-createrole-self-grant"/></member>
   </simplelist>
  </refsect1>
 
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 1ae2d0a66f..4d193a6f9a 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -39,6 +39,7 @@
 #include "utils/fmgroids.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
+#include "utils/varlena.h"
 
 /*
  * Removing a role grant - or the admin option on it - might recurse to
@@ -81,8 +82,11 @@ typedef struct
 #define GRANT_ROLE_SPECIFIED_INHERIT		0x0002
 #define GRANT_ROLE_SPECIFIED_SET			0x0004
 
-/* GUC parameter */
+/* GUC parameters */
 int			Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
+char	   *createrole_self_grant = "";
+bool		createrole_self_grant_enabled = false;
+GrantRoleOptions	createrole_self_grant_options;
 
 /* Hook to check passwords in CreateRole() and AlterRole() */
 check_password_hook_type check_password_hook = NULL;
@@ -532,10 +536,13 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	if (!superuser())
 	{
 		RoleSpec   *current_role = makeNode(RoleSpec);
-		GrantRoleOptions	poptself;
+		GrantRoleOptions poptself;
+		List	   *memberSpecs;
+		List	   *memberIds = list_make1_oid(currentUserId);
 
 		current_role->roletype = ROLESPEC_CURRENT_ROLE;
 		current_role->location = -1;
+		memberSpecs = list_make1(current_role);
 
 		poptself.specified = GRANT_ROLE_SPECIFIED_ADMIN
 			| GRANT_ROLE_SPECIFIED_INHERIT
@@ -545,7 +552,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		poptself.set = false;
 
 		AddRoleMems(BOOTSTRAP_SUPERUSERID, stmt->role, roleid,
-					list_make1(current_role), list_make1_oid(GetUserId()),
+					memberSpecs, memberIds,
 					BOOTSTRAP_SUPERUSERID, &poptself);
 
 		/*
@@ -553,6 +560,20 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		 * the additional grants will fail.
 		 */
 		CommandCounterIncrement();
+
+		/*
+		 * Because of the implicit grant above, a CREATEROLE user who creates
+		 * a role has the ability to grant that role back to themselves with
+		 * the INHERIT or SET options, if they wish to inherit the role's
+		 * privileges or be able to SET ROLE to it. The createrole_self_grant
+		 * GUC can be used to make this happen automatically. This has no
+		 * security implications since the same user is able to make the same
+		 * grant using an explicit GRANT statement; it's just convenient.
+		 */
+		if (createrole_self_grant_enabled)
+			AddRoleMems(currentUserId, stmt->role, roleid,
+						memberSpecs, memberIds,
+						currentUserId, &createrole_self_grant_options);
 	}
 
 	/*
@@ -2414,3 +2435,73 @@ InitGrantRoleOptions(GrantRoleOptions *popt)
 	popt->inherit = false;
 	popt->set = true;
 }
+
+/*
+ * GUC check_hook for createrole_self_grant
+ */
+bool
+check_createrole_self_grant(char **newval, void **extra, GucSource source)
+{
+	char	   *rawstring;
+	List	   *elemlist;
+	ListCell   *l;
+	unsigned	options = 0;
+	unsigned   *result;
+
+	/* Need a modifiable copy of string */
+	rawstring = pstrdup(*newval);
+
+	if (!SplitIdentifierString(rawstring, ',', &elemlist))
+	{
+		/* syntax error in list */
+		GUC_check_errdetail("List syntax is invalid.");
+		pfree(rawstring);
+		list_free(elemlist);
+		return false;
+	}
+
+	foreach(l, elemlist)
+	{
+		char	   *tok = (char *) lfirst(l);
+
+		if (pg_strcasecmp(tok, "SET") == 0)
+			options |= GRANT_ROLE_SPECIFIED_SET;
+		else if (pg_strcasecmp(tok, "INHERIT") == 0)
+			options |= GRANT_ROLE_SPECIFIED_INHERIT;
+		else
+		{
+			GUC_check_errdetail("Unrecognized key word: \"%s\".", tok);
+			pfree(rawstring);
+			list_free(elemlist);
+			return false;
+		}
+	}
+
+	pfree(rawstring);
+	list_free(elemlist);
+
+	result = (unsigned *) guc_malloc(LOG, sizeof(unsigned));
+	*result = options;
+	*extra = result;
+
+	return true;
+}
+
+/*
+ * GUC assign_hook for createrole_self_grant
+ */
+void
+assign_createrole_self_grant(const char *newval, void *extra)
+{
+	unsigned	options = * (unsigned *) extra;
+
+	createrole_self_grant_enabled = (options != 0);
+	createrole_self_grant_options.specified = GRANT_ROLE_SPECIFIED_ADMIN
+		| GRANT_ROLE_SPECIFIED_INHERIT
+		| GRANT_ROLE_SPECIFIED_SET;
+	createrole_self_grant_options.admin = false;
+	createrole_self_grant_options.inherit =
+		(options & GRANT_ROLE_SPECIFIED_INHERIT) != 0;
+	createrole_self_grant_options.set =
+		(options & GRANT_ROLE_SPECIFIED_SET) != 0;
+}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 68328b1402..e222b087eb 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3937,6 +3937,18 @@ struct config_string ConfigureNamesString[] =
 		check_temp_tablespaces, assign_temp_tablespaces, NULL
 	},
 
+	{
+		{"createrole_self_grant", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Sets whether a CREATEROLE user automatically grants "
+						 "the role to themselves, and with which options."),
+			NULL,
+			GUC_LIST_INPUT
+		},
+		&createrole_self_grant,
+		"",
+		check_createrole_self_grant, assign_createrole_self_grant, NULL
+	},
+
 	{
 		{"dynamic_library_path", PGC_SUSET, CLIENT_CONN_OTHER,
 			gettext_noop("Sets the path for dynamically loadable modules."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 5afdeb04de..cf8cc12164 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -702,6 +702,7 @@
 #xmlbinary = 'base64'
 #xmloption = 'content'
 #gin_pending_list_limit = 4MB
+#createrole_self_grant = ''		# set and/or inherit
 
 # - Locale and Formatting -
 
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 54c720d880..97dcb93791 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -15,9 +15,11 @@
 #include "libpq/crypt.h"
 #include "nodes/parsenodes.h"
 #include "parser/parse_node.h"
+#include "utils/guc.h"
 
-/* GUC. Is actually of type PasswordType. */
-extern PGDLLIMPORT int Password_encryption;
+/* GUCs */
+extern PGDLLIMPORT int Password_encryption; /* values from enum PasswordType */
+extern PGDLLIMPORT char *createrole_self_grant;
 
 /* Hook to check passwords in CreateRole() and AlterRole() */
 typedef void (*check_password_hook_type) (const char *username, const char *shadow_pass, PasswordType password_type, Datum validuntil_time, bool validuntil_null);
@@ -34,4 +36,8 @@ extern void DropOwnedObjects(DropOwnedStmt *stmt);
 extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt);
 extern List *roleSpecsToIds(List *memberNames);
 
+extern bool check_createrole_self_grant(char **newval, void **extra,
+										GucSource source);
+extern void assign_createrole_self_grant(const char *newval, void *extra);
+
 #endif							/* USER_H */
-- 
2.37.1 (Apple Git-137.1)

From 7984e5e1d61f2eca36516e20d8575ee678a5b28d Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Mon, 21 Nov 2022 14:46:03 -0500
Subject: [PATCH v3 2/4] Pass down current user ID to AddRoleMems and
 DelRoleMems.

This is just refactoring; there should be no functonal change.
---
 src/backend/commands/user.c | 41 ++++++++++++++++++++-----------------
 1 file changed, 22 insertions(+), 19 deletions(-)

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 280031e6eb..bc2b95ac81 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -87,10 +87,10 @@ int			Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
 /* Hook to check passwords in CreateRole() and AlterRole() */
 check_password_hook_type check_password_hook = NULL;
 
-static void AddRoleMems(const char *rolename, Oid roleid,
+static void AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid,
 						List *memberSpecs, List *memberIds,
 						Oid grantorId, GrantRoleOptions *popt);
-static void DelRoleMems(const char *rolename, Oid roleid,
+static void DelRoleMems(Oid currentUserId, const char *rolename, Oid roleid,
 						List *memberSpecs, List *memberIds,
 						Oid grantorId, GrantRoleOptions *popt,
 						DropBehavior behavior);
@@ -133,6 +133,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	HeapTuple	tuple;
 	Datum		new_record[Natts_pg_authid] = {0};
 	bool		new_record_nulls[Natts_pg_authid] = {0};
+	Oid			currentUserId = GetUserId();
 	Oid			roleid;
 	ListCell   *item;
 	ListCell   *option;
@@ -508,8 +509,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 			char	   *oldrolename = NameStr(oldroleform->rolname);
 
 			/* can only add this role to roles for which you have rights */
-			check_role_membership_authorization(GetUserId(), oldroleid, true);
-			AddRoleMems(oldrolename, oldroleid,
+			check_role_membership_authorization(currentUserId, oldroleid, true);
+			AddRoleMems(currentUserId, oldrolename, oldroleid,
 						thisrole_list,
 						thisrole_oidlist,
 						InvalidOid, &popt);
@@ -525,12 +526,12 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	 * NB: No permissions check is required here. If you have enough rights
 	 * to create a role, you can add any members you like.
 	 */
-	AddRoleMems(stmt->role, roleid,
+	AddRoleMems(currentUserId, stmt->role, roleid,
 				rolemembers, roleSpecsToIds(rolemembers),
 				InvalidOid, &popt);
 	popt.specified |= GRANT_ROLE_SPECIFIED_ADMIN;
 	popt.admin = true;
-	AddRoleMems(stmt->role, roleid,
+	AddRoleMems(currentUserId, stmt->role, roleid,
 				adminmembers, roleSpecsToIds(adminmembers),
 				InvalidOid, &popt);
 
@@ -583,6 +584,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	DefElem    *dvalidUntil = NULL;
 	DefElem    *dbypassRLS = NULL;
 	Oid			roleid;
+	Oid			currentUserId = GetUserId();
 	GrantRoleOptions	popt;
 
 	check_rolespec_name(stmt->role,
@@ -727,13 +729,13 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 					 errmsg("permission denied")));
 
 		/* without CREATEROLE, can only change your own password */
-		if (dpassword && roleid != GetUserId())
+		if (dpassword && roleid != currentUserId)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have CREATEROLE privilege to change another user's password")));
 
 		/* without CREATEROLE, can only add members to roles you admin */
-		if (drolemembers && !is_admin_of_role(GetUserId(), roleid))
+		if (drolemembers && !is_admin_of_role(currentUserId, roleid))
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have admin option on role \"%s\" to add members",
@@ -888,11 +890,11 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 		CommandCounterIncrement();
 
 		if (stmt->action == +1) /* add members to role */
-			AddRoleMems(rolename, roleid,
+			AddRoleMems(currentUserId, rolename, roleid,
 						rolemembers, roleSpecsToIds(rolemembers),
 						InvalidOid, &popt);
 		else if (stmt->action == -1)	/* drop members from role */
-			DelRoleMems(rolename, roleid,
+			DelRoleMems(currentUserId, rolename, roleid,
 						rolemembers, roleSpecsToIds(rolemembers),
 						InvalidOid, &popt, DROP_RESTRICT);
 	}
@@ -1378,6 +1380,7 @@ GrantRole(ParseState *pstate, GrantRoleStmt *stmt)
 	List	   *grantee_ids;
 	ListCell   *item;
 	GrantRoleOptions	popt;
+	Oid			currentUserId = GetUserId();
 
 	/* Parse options list. */
 	InitGrantRoleOptions(&popt);
@@ -1449,14 +1452,14 @@ GrantRole(ParseState *pstate, GrantRoleStmt *stmt)
 					 errmsg("column names cannot be included in GRANT/REVOKE ROLE")));
 
 		roleid = get_role_oid(rolename, false);
-		check_role_membership_authorization(GetUserId(), roleid,
-											stmt->is_grant);
+		check_role_membership_authorization(currentUserId,
+											roleid, stmt->is_grant);
 		if (stmt->is_grant)
-			AddRoleMems(rolename, roleid,
+			AddRoleMems(currentUserId, rolename, roleid,
 						stmt->grantee_roles, grantee_ids,
 						grantor, &popt);
 		else
-			DelRoleMems(rolename, roleid,
+			DelRoleMems(currentUserId, rolename, roleid,
 						stmt->grantee_roles, grantee_ids,
 						grantor, &popt, stmt->behavior);
 	}
@@ -1555,15 +1558,17 @@ roleSpecsToIds(List *memberNames)
 /*
  * AddRoleMems -- Add given members to the specified role
  *
+ * currentUserId: OID of role performing the operation
  * rolename: name of role to add to (used only for error messages)
  * roleid: OID of role to add to
  * memberSpecs: list of RoleSpec of roles to add (used only for error messages)
  * memberIds: OIDs of roles to add
- * grantorId: who is granting the membership (InvalidOid if not set explicitly)
+ * grantorId: OID that should be recorded as having granted the membership
+ * (InvalidOid if not set explicitly)
  * popt: information about grant options
  */
 static void
-AddRoleMems(const char *rolename, Oid roleid,
+AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid,
 			List *memberSpecs, List *memberIds,
 			Oid grantorId, GrantRoleOptions *popt)
 {
@@ -1571,7 +1576,6 @@ AddRoleMems(const char *rolename, Oid roleid,
 	TupleDesc	pg_authmem_dsc;
 	ListCell   *specitem;
 	ListCell   *iditem;
-	Oid			currentUserId = GetUserId();
 
 	Assert(list_length(memberSpecs) == list_length(memberIds));
 
@@ -1859,7 +1863,7 @@ AddRoleMems(const char *rolename, Oid roleid,
  * behavior: RESTRICT or CASCADE behavior for recursive removal
  */
 static void
-DelRoleMems(const char *rolename, Oid roleid,
+DelRoleMems(Oid currentUserId, const char *rolename, Oid roleid,
 			List *memberSpecs, List *memberIds,
 			Oid grantorId, GrantRoleOptions *popt, DropBehavior behavior)
 {
@@ -1867,7 +1871,6 @@ DelRoleMems(const char *rolename, Oid roleid,
 	TupleDesc	pg_authmem_dsc;
 	ListCell   *specitem;
 	ListCell   *iditem;
-	Oid			currentUserId = GetUserId();
 	CatCList   *memlist;
 	RevokeRoleGrantAction *actions;
 	int			i;
-- 
2.37.1 (Apple Git-137.1)

From 04510f4c721c369d0f4efc8b7b16bb651afa1dcc Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Thu, 1 Dec 2022 15:07:02 -0500
Subject: [PATCH v3 3/4] Restrict the privileges of CREATEROLE users.

Previously, CREATEROLE users were permitted to make nearly arbitrary
changes to roles that they didn't create, with certain exceptions,
particularly superuser roles.  Instead, allow CREATEROLE users to make such
changes to roles for which they possess ADMIN OPTION, and to
grant membership only in roles for which they possess ADMIN OPTION.

When a CREATEROLE user who is not a superuser creates a role, grant
ADMIN OPTION on the newly-created role to the creator, so that they
can administer roles they create or for which they have been given
privileges.

With these changes, CREATEROLE users still have very significant
powers that unprivileged users do not receive: they can alter, rename,
drop, comment on, change the password for, and change security labels
on roles.  However, they can now do these things only for roles for
which they possess appropriate privileges, rather than all
non-superuser roles; moreover, they cannot grant a role such as
pg_execute_server_program unless they themselves possess it.

FIXME: Add more regression tests.
---
 doc/src/sgml/ddl.sgml                         |  10 +-
 doc/src/sgml/ref/alter_role.sgml              |   8 +-
 doc/src/sgml/ref/comment.sgml                 |   3 +-
 doc/src/sgml/ref/create_role.sgml             |   4 +-
 doc/src/sgml/ref/createuser.sgml              |   3 +-
 doc/src/sgml/ref/drop_role.sgml               |   2 +-
 doc/src/sgml/ref/dropuser.sgml                |   7 +-
 doc/src/sgml/ref/grant.sgml                   |   4 +-
 doc/src/sgml/user-manag.sgml                  |  44 ++++++--
 src/backend/catalog/objectaddress.c           |  10 +-
 src/backend/commands/user.c                   | 100 +++++++++++++-----
 .../expected/dummy_seclabel.out               |  17 +--
 .../dummy_seclabel/sql/dummy_seclabel.sql     |  13 ++-
 src/test/regress/expected/create_role.out     |  37 ++++---
 src/test/regress/sql/create_role.sql          |  23 ++--
 15 files changed, 181 insertions(+), 104 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 6e92bbddd2..ae88266469 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3216,13 +3216,11 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
        name. Therefore, if each user has a separate schema, they access
        their own schemas by default.)  This pattern is a secure schema
        usage pattern unless an untrusted user is the database owner or
-       holds the <literal>CREATEROLE</literal> privilege, in which case no
-       secure schema usage pattern exists.
+       has been granted <literal>ADMIN OPTION</literal> on a relevant role,
+       in which case no secure schema usage pattern exists.
       </para>
       <!-- A database owner can attack the database's users via "CREATE SCHEMA
-           trojan; ALTER DATABASE $mydb SET search_path = trojan, public;".  A
-           CREATEROLE user can issue "GRANT $dbowner TO $me" and then use the
-           database owner attack. -->
+           trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". -->
 
       <para>
        In <productname>PostgreSQL</productname> 15 and later, the default
@@ -3250,7 +3248,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
        unreliable</link>.  If you create functions or extensions in the public
        schema, use the first pattern instead.  Otherwise, like the first
        pattern, this is secure unless an untrusted user is the database owner
-       or holds the <literal>CREATEROLE</literal> privilege.
+       or has been granted <literal>ADMIN OPTION</literal> on a relevant role.
       </para>
      </listitem>
 
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 8a8f828137..6a865f21fd 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -73,7 +73,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
    Roles having <literal>CREATEROLE</literal> privilege can change any of these
    settings except <literal>SUPERUSER</literal>, <literal>REPLICATION</literal>,
    and <literal>BYPASSRLS</literal>; but only for non-superuser and
-   non-replication roles.
+   non-replication roles for which they have been
+   granted <literal>ADMIN OPTION</literal>.
    Ordinary roles can only change their own password.
   </para>
 
@@ -81,7 +82,7 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
    The second variant changes the name of the role.
    Database superusers can rename any role.
    Roles having <literal>CREATEROLE</literal> privilege can rename non-superuser
-   roles.
+   roles for which they have been granted <literal>ADMIN OPTION</literal>.
    The current session user cannot be renamed.
    (Connect as a different user if you need to do that.)
    Because <literal>MD5</literal>-encrypted passwords use the role name as
@@ -116,7 +117,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
   <para>
    Superusers can change anyone's session defaults. Roles having
    <literal>CREATEROLE</literal> privilege can change defaults for non-superuser
-   roles. Ordinary roles can only set defaults for themselves.
+   roles for which they have been granted <literal>ADMIN OPTION</literal>.
+   Ordinary roles can only set defaults for themselves.
    Certain configuration variables cannot be set this way, or can only be
    set if a superuser issues the command.  Only superusers can change a setting
    for all roles in all databases.
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 23d9029af9..7499da1d62 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -99,7 +99,8 @@ COMMENT ON
    For most kinds of object, only the object's owner can set the comment.
    Roles don't have owners, so the rule for <literal>COMMENT ON ROLE</literal> is
    that you must be superuser to comment on a superuser role, or have the
-   <literal>CREATEROLE</literal> privilege to comment on non-superuser roles.
+   <literal>CREATEROLE</literal> privilege and have been granted
+   <literal>ADMIN OPTION</literal> on the target role.
    Likewise, access methods don't have owners either; you must be superuser
    to comment on an access method.
    Of course, a superuser can comment on anything.
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 1ccc832558..0863acbcac 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -119,8 +119,8 @@ in sync when changing the above synopsis!
       <listitem>
        <para>
         These clauses determine whether a role will be permitted to
-        create, alter, drop, comment on, change the security label for,
-        and grant or revoke membership in other roles.
+        create, alter, drop, comment on, and change the security label for
+        other roles.
         See <xref linkend='role-creation' /> for more details about what
         capabilities are conferred by this privilege.
         If not specified, <literal>NOCREATEROLE</literal> is the default.
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index a41a2b24e6..f91dc500a4 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -252,8 +252,7 @@ PostgreSQL documentation
       <listitem>
        <para>
         The new user will be allowed to create, alter, drop, comment on,
-        change the security label for, and grant or revoke membership in
-        other roles; that is,
+        change the security label for other roles; that is,
         this user will have <literal>CREATEROLE</literal> privilege.
         See <xref linkend='role-creation' /> for more details about what
         capabilities are conferred by this privilege.
diff --git a/doc/src/sgml/ref/drop_role.sgml b/doc/src/sgml/ref/drop_role.sgml
index 13dc1cc649..cbcb3cd3d3 100644
--- a/doc/src/sgml/ref/drop_role.sgml
+++ b/doc/src/sgml/ref/drop_role.sgml
@@ -32,7 +32,7 @@ DROP ROLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...
    <command>DROP ROLE</command> removes the specified role(s).
    To drop a superuser role, you must be a superuser yourself;
    to drop non-superuser roles, you must have <literal>CREATEROLE</literal>
-   privilege.
+   privilege and have been granted <literal>ADMIN OPTION</literal> on the role.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml
index 81580507e8..b6be26d5b0 100644
--- a/doc/src/sgml/ref/dropuser.sgml
+++ b/doc/src/sgml/ref/dropuser.sgml
@@ -35,9 +35,10 @@ PostgreSQL documentation
   <para>
    <application>dropuser</application> removes an existing
    <productname>PostgreSQL</productname> user.
-   Only superusers and users with the <literal>CREATEROLE</literal> privilege can
-   remove <productname>PostgreSQL</productname> users.  (To remove a
-   superuser, you must yourself be a superuser.)
+   Superusers can use this command to remove any role; otherwise, only
+   non-superuser roles can be removed, and only by a user who possesses
+   the <literal>CREATEROLE</literal> privilege and has been granted
+   <literal>ADMIN OPTION</literal> on the target role.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index 518bdb32d8..85f5f42ea6 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -271,9 +271,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
    in the role as well.  Without the admin option, ordinary users cannot
    do that.  A role is not considered to hold <literal>WITH ADMIN
    OPTION</literal> on itself.  Database superusers can grant or revoke
-   membership in any role to anyone.  Roles having
-   <literal>CREATEROLE</literal> privilege can grant or revoke membership
-   in any role that is not a superuser. This option defaults to
+   membership in any role to anyone. This option defaults to
    <literal>FALSE</literal>.
   </para>
 
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 7aa6bdac16..71a2d8f298 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -199,7 +199,12 @@ CREATE USER <replaceable>name</replaceable>;
         checks). To create such a role, use <literal>CREATE ROLE
         <replaceable>name</replaceable> CREATEROLE</literal>.
         A role with <literal>CREATEROLE</literal> privilege can alter and drop
-        other roles, too, as well as grant or revoke membership in them.
+        roles which have been granted to the <literal>CREATEROLE</literal>
+        user with the <literal>ADMIN</literal> option. Such a grant occurs
+        automatically when a <literal>CREATEROLE</literal> user that is not
+        a superuser creates a new role, so that by default, a
+        <literal>CREATEROLE</literal> user can alter and drop the roles
+        which they have created.
         Altering a role includes most changes that can be made using
         <literal>ALTER ROLE</literal>, including, for example, changing
         passwords.  It also includes modifications to a role that can
@@ -224,15 +229,6 @@ CREATE USER <replaceable>name</replaceable>;
         confer the ability to grant or revoke the <literal>BYPASSRLS</literal>
         privilege.
        </para>
-       <para>
-        Because the <literal>CREATEROLE</literal> privilege allows a user
-        to grant or revoke membership even in roles to which it does not (yet)
-        have any access, a <literal>CREATEROLE</literal> user can obtain access
-        to the capabilities of every predefined role in the system, including
-        highly privileged roles such as
-        <literal>pg_execute_server_program</literal> and
-        <literal>pg_write_server_files</literal>.
-       </para>
       </listitem>
      </varlistentry>
 
@@ -329,6 +325,34 @@ ALTER ROLE myname SET enable_indexscan TO off;
    <literal>LOGIN</literal> privilege are fairly useless, since they will never
    be invoked.
   </para>
+
+  <para>
+   When a non-superuser creates a role using the <literal>CREATEROLE</literal>
+   privilege, the created role is automatically granted back to the creating
+   user, just as if the bootstrap superuser had executed the command
+   <literal>GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE,
+   INHERIT FALSE</literal>. Since a <literal>CREATEROLE</literal> user can
+   only exercise special privileges with regard to an existing role if they
+   have <literal>ADMIN OPTION</literal> on it, this grant is just sufficient
+   to allow a <literal>CREATEROLE</literal> user to administer the roles they
+   created. However, because it is created with <literal>INHERIT FALSE, SET
+   FALSE</literal>, the <literal>CREATEROLE</literal> user doesn't inherit the
+   privileges of the created role, nor can it access the privileges of that
+   role using <literal>SET ROLE</literal>. However, since any user who has
+   <literal>ADMIN OPTION</literal> on a role can grant membership in that
+   role to any other user, the <literal>CREATEROLE</literal> user can gain
+   access to the created role by simplying granting that role back to
+   themselves with the <literal>INHERIT</literal> and/or <literal>SET</literal>
+   options. Thus, the fact that privileges are not inherited by default nor
+   is <literal>SET ROLE</literal> granted by default is a safeguard against
+   accidents, not a security feature. Also note that, because this automatic
+   grant is granted by the bootstrap user, it cannot be removed or changed by
+   the <literal>CREATEROLE</literal> user; however, any superuser could
+   revoke it, modify it, and/or issue additional such grants to other
+   <literal>CREATEROLE</literal> users. Whichever <literal>CREATEROLE</literal>
+   users have <literal>ADMIN OPTION</literal> on a role at any given time
+   can administer it.
+  </para>
  </sect1>
 
  <sect1 id="role-membership">
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 1367b5e7c5..25c50d66fd 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2538,7 +2538,9 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
 
 			/*
 			 * We treat roles as being "owned" by those with CREATEROLE priv,
-			 * except that superusers are only owned by superusers.
+			 * provided that they also have admin option on the role.
+			 *
+			 * However, superusers are only owned by superusers.
 			 */
 			if (superuser_arg(address.objectId))
 			{
@@ -2553,6 +2555,12 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
 					ereport(ERROR,
 							(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 							 errmsg("must have CREATEROLE privilege")));
+				if (!is_admin_of_role(roleid, address.objectId))
+					ereport(ERROR,
+							(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+							 errmsg("must have admin option on role \"%s\"",
+									GetUserNameFromId(address.objectId,
+													  true))));
 			}
 			break;
 		case OBJECT_TSPARSER:
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index bc2b95ac81..1ae2d0a66f 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -519,6 +519,42 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		}
 	}
 
+	/*
+	 * If the current user isn't a superuser, make them an admin of the new
+	 * role so that they can administer the new object they just created.
+	 * Superusers will be able to do that anyway.
+	 *
+	 * The grantor of record for this implicit grant is the bootstrap
+	 * superuser, which means that the CREATEROLE user cannot revoke the
+	 * grant. They can however grant the created role back to themselves
+	 * with different options, since they enjoy ADMIN OPTION on it.
+	 */
+	if (!superuser())
+	{
+		RoleSpec   *current_role = makeNode(RoleSpec);
+		GrantRoleOptions	poptself;
+
+		current_role->roletype = ROLESPEC_CURRENT_ROLE;
+		current_role->location = -1;
+
+		poptself.specified = GRANT_ROLE_SPECIFIED_ADMIN
+			| GRANT_ROLE_SPECIFIED_INHERIT
+			| GRANT_ROLE_SPECIFIED_SET;
+		poptself.admin = true;
+		poptself.inherit = false;
+		poptself.set = false;
+
+		AddRoleMems(BOOTSTRAP_SUPERUSERID, stmt->role, roleid,
+					list_make1(current_role), list_make1_oid(GetUserId()),
+					BOOTSTRAP_SUPERUSERID, &poptself);
+
+		/*
+		 * We must make the implicit grant visible to the code below, else
+		 * the additional grants will fail.
+		 */
+		CommandCounterIncrement();
+	}
+
 	/*
 	 * Add the specified members to this new role. adminmembers get the admin
 	 * option, rolemembers don't.
@@ -694,9 +730,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	/*
 	 * To mess with a superuser or replication role in any way you gotta be
 	 * superuser.  We also insist on superuser to change the BYPASSRLS
-	 * property.  Otherwise, if you don't have createrole, you're only allowed
-	 * to (1) change your own password or (2) add members to a role for which
-	 * you have ADMIN OPTION.
+	 * property.
 	 */
 	if (authform->rolsuper || dissuper)
 	{
@@ -719,29 +753,35 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must be superuser to change bypassrls attribute")));
 	}
-	else if (!have_createrole_privilege())
+
+	/*
+	 * Most changes to a role require that you both have CREATEROLE privileges
+	 * and also ADMIN OPTION on the role.
+	 */
+	if (!have_createrole_privilege() ||
+		!is_admin_of_role(GetUserId(), roleid))
 	{
-		/* things you certainly can't do without CREATEROLE */
+		/* things an unprivileged user certainly can't do */
 		if (dinherit || dcreaterole || dcreatedb || dcanlogin || dconnlimit ||
 			dvalidUntil)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied")));
 
-		/* without CREATEROLE, can only change your own password */
+		/* an unprivileged user can change their own password */
 		if (dpassword && roleid != currentUserId)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have CREATEROLE privilege to change another user's password")));
-
-		/* without CREATEROLE, can only add members to roles you admin */
-		if (drolemembers && !is_admin_of_role(currentUserId, roleid))
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must have admin option on role \"%s\" to add members",
-							rolename)));
 	}
 
+	/* To add members to a role, you need ADMIN OPTION. */
+	if (drolemembers && !is_admin_of_role(currentUserId, roleid))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must have admin option on role \"%s\" to add members",
+						rolename)));
+
 	/* Convert validuntil to internal form */
 	if (dvalidUntil)
 	{
@@ -935,8 +975,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
 		shdepLockAndCheckObject(AuthIdRelationId, roleid);
 
 		/*
-		 * To mess with a superuser you gotta be superuser; else you need
-		 * createrole, or just want to change your own settings
+		 * To mess with a superuser you gotta be superuser; otherwise you
+		 * need CREATEROLE plus admin option on the target role; unless you're
+		 * just trying to change your own settings
 		 */
 		if (roleform->rolsuper)
 		{
@@ -947,7 +988,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
 		}
 		else
 		{
-			if (!have_createrole_privilege() && roleid != GetUserId())
+			if ((!have_createrole_privilege() ||
+				!is_admin_of_role(GetUserId(), roleid))
+				&& roleid != GetUserId())
 				ereport(ERROR,
 						(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 						 errmsg("permission denied")));
@@ -1067,13 +1110,18 @@ DropRole(DropRoleStmt *stmt)
 
 		/*
 		 * For safety's sake, we allow createrole holders to drop ordinary
-		 * roles but not superuser roles.  This is mainly to avoid the
-		 * scenario where you accidentally drop the last superuser.
+		 * roles but not superuser roles, and only if they also have ADMIN
+		 * OPTION.
 		 */
 		if (roleform->rolsuper && !superuser())
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must be superuser to drop superusers")));
+		if (!is_admin_of_role(GetUserId(), roleid))
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("must have admin option on role \"%s\"",
+							role)));
 
 		/* DROP hook for the role being removed */
 		InvokeObjectDropHook(AuthIdRelationId, roleid, 0);
@@ -1312,7 +1360,8 @@ RenameRole(const char *oldname, const char *newname)
 				 errmsg("role \"%s\" already exists", newname)));
 
 	/*
-	 * createrole is enough privilege unless you want to mess with a superuser
+	 * Only superusers can mess with superusers. Otherwise, a user with
+	 * CREATEROLE can rename a role for which they have ADMIN OPTION.
 	 */
 	if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper)
 	{
@@ -1323,7 +1372,8 @@ RenameRole(const char *oldname, const char *newname)
 	}
 	else
 	{
-		if (!have_createrole_privilege())
+		if (!have_createrole_privilege() ||
+			!is_admin_of_role(GetUserId(), roleid))
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied to rename role")));
@@ -2023,11 +2073,9 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid,
 	else
 	{
 		/*
-		 * Otherwise, must have createrole or admin option on the role to be
-		 * changed.
+		 * Otherwise, must have admin option on the role to be changed.
 		 */
-		if (!has_createrole_privilege(currentUserId) &&
-			!is_admin_of_role(currentUserId, roleid))
+		if (!is_admin_of_role(currentUserId, roleid))
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have admin option on role \"%s\"",
@@ -2049,7 +2097,7 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid,
  * be passed as InvalidOid, and this function will infer the user to be
  * recorded as the grantor. In many cases, this will be the current user, but
  * things get more complicated when the current user doesn't possess ADMIN
- * OPTION on the role but rather relies on having CREATEROLE privileges, or
+ * OPTION on the role but rather relies on having SUPERUSER privileges, or
  * on inheriting the privileges of a role which does have ADMIN OPTION. See
  * below for details.
  *
@@ -2075,7 +2123,7 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant)
 		 * not depend on any other existing grants, so always default to this
 		 * interpretation when possible.
 		 */
-		if (has_createrole_privilege(currentUserId))
+		if (superuser_arg(currentUserId))
 			return BOOTSTRAP_SUPERUSERID;
 
 		/*
diff --git a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
index b2d898a7d1..c57d4fd2df 100644
--- a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
+++ b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
@@ -6,9 +6,11 @@ CREATE EXTENSION dummy_seclabel;
 SET client_min_messages TO 'warning';
 DROP ROLE IF EXISTS regress_dummy_seclabel_user1;
 DROP ROLE IF EXISTS regress_dummy_seclabel_user2;
+DROP ROLE IF EXISTS regress_dummy_seclabel_user3;
 RESET client_min_messages;
 CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE;
 CREATE USER regress_dummy_seclabel_user2;
+CREATE USER regress_dummy_seclabel_user3;
 CREATE TABLE dummy_seclabel_tbl1 (a int, b text);
 CREATE TABLE dummy_seclabel_tbl2 (x int, y text);
 CREATE VIEW dummy_seclabel_view1 AS SELECT * FROM dummy_seclabel_tbl2;
@@ -16,6 +18,8 @@ CREATE FUNCTION dummy_seclabel_four() RETURNS integer AS $$SELECT 4$$ language s
 CREATE DOMAIN dummy_seclabel_domain AS text;
 ALTER TABLE dummy_seclabel_tbl1 OWNER TO regress_dummy_seclabel_user1;
 ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2;
+GRANT regress_dummy_seclabel_user2, regress_dummy_seclabel_user3
+  TO regress_dummy_seclabel_user1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
 --
 -- Test of SECURITY LABEL statement with a plugin
 --
@@ -43,16 +47,16 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified';			-- OK
 -- Test for shared database object
 --
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user1;
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified';			-- OK
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...';	-- fail
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'classified';			-- OK
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS '...invalid label...';	-- fail
 ERROR:  '...invalid label...' is not a valid security label
 SECURITY LABEL FOR 'dummy' ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- OK
 SECURITY LABEL FOR 'unknown_seclabel' ON ROLE regress_dummy_seclabel_user1 IS 'unclassified';	-- fail
 ERROR:  security label provider "unknown_seclabel" is not loaded
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'secret';	-- fail (not superuser)
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'secret';	-- fail (not superuser)
 ERROR:  only superuser can set 'secret' label
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'unclassified';	-- fail (not found)
-ERROR:  role "regress_dummy_seclabel_user3" does not exist
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user4 IS 'unclassified';	-- fail (not found)
+ERROR:  role "regress_dummy_seclabel_user4" does not exist
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user2;
 SECURITY LABEL ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- fail (not privileged)
 ERROR:  must have CREATEROLE privilege
@@ -81,8 +85,8 @@ SELECT objtype, objname, provider, label FROM pg_seclabels
  domain       | dummy_seclabel_domain        | dummy    | classified
  function     | dummy_seclabel_four()        | dummy    | classified
  publication  | dummy_pub                    | dummy    | classified
- role         | regress_dummy_seclabel_user1 | dummy    | classified
  role         | regress_dummy_seclabel_user2 | dummy    | unclassified
+ role         | regress_dummy_seclabel_user3 | dummy    | classified
  schema       | dummy_seclabel_test          | dummy    | unclassified
  subscription | dummy_sub                    | dummy    | classified
  table        | dummy_seclabel_tbl1          | dummy    | top secret
@@ -115,3 +119,4 @@ DROP SUBSCRIPTION dummy_sub;
 DROP PUBLICATION dummy_pub;
 DROP ROLE regress_dummy_seclabel_user1;
 DROP ROLE regress_dummy_seclabel_user2;
+DROP ROLE regress_dummy_seclabel_user3;
diff --git a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
index 8c347b6a68..649409757e 100644
--- a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
+++ b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
@@ -8,11 +8,13 @@ SET client_min_messages TO 'warning';
 
 DROP ROLE IF EXISTS regress_dummy_seclabel_user1;
 DROP ROLE IF EXISTS regress_dummy_seclabel_user2;
+DROP ROLE IF EXISTS regress_dummy_seclabel_user3;
 
 RESET client_min_messages;
 
 CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE;
 CREATE USER regress_dummy_seclabel_user2;
+CREATE USER regress_dummy_seclabel_user3;
 
 CREATE TABLE dummy_seclabel_tbl1 (a int, b text);
 CREATE TABLE dummy_seclabel_tbl2 (x int, y text);
@@ -22,6 +24,8 @@ CREATE DOMAIN dummy_seclabel_domain AS text;
 
 ALTER TABLE dummy_seclabel_tbl1 OWNER TO regress_dummy_seclabel_user1;
 ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2;
+GRANT regress_dummy_seclabel_user2, regress_dummy_seclabel_user3
+  TO regress_dummy_seclabel_user1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
 
 --
 -- Test of SECURITY LABEL statement with a plugin
@@ -47,12 +51,12 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified';			-- OK
 --
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user1;
 
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified';			-- OK
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...';	-- fail
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'classified';			-- OK
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS '...invalid label...';	-- fail
 SECURITY LABEL FOR 'dummy' ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- OK
 SECURITY LABEL FOR 'unknown_seclabel' ON ROLE regress_dummy_seclabel_user1 IS 'unclassified';	-- fail
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'secret';	-- fail (not superuser)
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'unclassified';	-- fail (not found)
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'secret';	-- fail (not superuser)
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user4 IS 'unclassified';	-- fail (not found)
 
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user2;
 SECURITY LABEL ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- fail (not privileged)
@@ -113,3 +117,4 @@ DROP PUBLICATION dummy_pub;
 
 DROP ROLE regress_dummy_seclabel_user1;
 DROP ROLE regress_dummy_seclabel_user2;
+DROP ROLE regress_dummy_seclabel_user3;
diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out
index 4e67d72760..6e0cce4579 100644
--- a/src/test/regress/expected/create_role.out
+++ b/src/test/regress/expected/create_role.out
@@ -13,7 +13,7 @@ CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
 ERROR:  must be superuser to create bypassrls users
 -- ok, having CREATEROLE is enough to create users with these privileges
 CREATE ROLE regress_createdb CREATEDB;
-CREATE ROLE regress_createrole CREATEROLE;
+CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
 CREATE ROLE regress_login LOGIN;
 CREATE ROLE regress_inherit INHERIT;
 CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -70,20 +70,35 @@ ALTER VIEW tenant_view OWNER TO regress_role_admin;
 ERROR:  must be owner of view tenant_view
 DROP VIEW tenant_view;
 ERROR:  must be owner of view tenant_view
--- fail, cannot take ownership of these objects from regress_tenant
+-- fail, we don't inherit permissions from regress_tenant
 REASSIGN OWNED BY regress_tenant TO regress_createrole;
 ERROR:  permission denied to reassign objects
--- ok, having CREATEROLE is enough to create roles in privileged roles
+-- fail, CREATEROLE is not enough to create roles in privileged roles
 CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
+ERROR:  must have admin option on role "pg_read_all_data"
 CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
+ERROR:  must have admin option on role "pg_write_all_data"
 CREATE ROLE regress_monitor IN ROLE pg_monitor;
+ERROR:  must have admin option on role "pg_monitor"
 CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings;
+ERROR:  must have admin option on role "pg_read_all_settings"
 CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats;
+ERROR:  must have admin option on role "pg_read_all_stats"
 CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables;
+ERROR:  must have admin option on role "pg_stat_scan_tables"
 CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files;
+ERROR:  must have admin option on role "pg_read_server_files"
 CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files;
+ERROR:  must have admin option on role "pg_write_server_files"
 CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program;
+ERROR:  must have admin option on role "pg_execute_server_program"
 CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend;
+ERROR:  must have admin option on role "pg_signal_backend"
+-- fail, role still owns database objects
+DROP ROLE regress_tenant;
+ERROR:  role "regress_tenant" cannot be dropped because some objects depend on it
+DETAIL:  owner of table tenant_table
+owner of view tenant_view
 -- fail, creation of these roles failed above so they do not now exist
 SET SESSION AUTHORIZATION regress_role_admin;
 DROP ROLE regress_nosuch_superuser;
@@ -114,22 +129,6 @@ DROP ROLE regress_password_null;
 DROP ROLE regress_noiseword;
 DROP ROLE regress_inroles;
 DROP ROLE regress_adminroles;
-DROP ROLE regress_rolecreator;
-DROP ROLE regress_read_all_data;
-DROP ROLE regress_write_all_data;
-DROP ROLE regress_monitor;
-DROP ROLE regress_read_all_settings;
-DROP ROLE regress_read_all_stats;
-DROP ROLE regress_stat_scan_tables;
-DROP ROLE regress_read_server_files;
-DROP ROLE regress_write_server_files;
-DROP ROLE regress_execute_server_program;
-DROP ROLE regress_signal_backend;
--- fail, role still owns database objects
-DROP ROLE regress_tenant;
-ERROR:  role "regress_tenant" cannot be dropped because some objects depend on it
-DETAIL:  owner of table tenant_table
-owner of view tenant_view
 -- fail, cannot drop ourself nor superusers
 DROP ROLE regress_role_super;
 ERROR:  must be superuser to drop superusers
diff --git a/src/test/regress/sql/create_role.sql b/src/test/regress/sql/create_role.sql
index 292dc08797..d491684db3 100644
--- a/src/test/regress/sql/create_role.sql
+++ b/src/test/regress/sql/create_role.sql
@@ -11,7 +11,7 @@ CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
 
 -- ok, having CREATEROLE is enough to create users with these privileges
 CREATE ROLE regress_createdb CREATEDB;
-CREATE ROLE regress_createrole CREATEROLE;
+CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
 CREATE ROLE regress_login LOGIN;
 CREATE ROLE regress_inherit INHERIT;
 CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -71,10 +71,10 @@ DROP TABLE tenant_table;
 ALTER VIEW tenant_view OWNER TO regress_role_admin;
 DROP VIEW tenant_view;
 
--- fail, cannot take ownership of these objects from regress_tenant
+-- fail, we don't inherit permissions from regress_tenant
 REASSIGN OWNED BY regress_tenant TO regress_createrole;
 
--- ok, having CREATEROLE is enough to create roles in privileged roles
+-- fail, CREATEROLE is not enough to create roles in privileged roles
 CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
 CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
 CREATE ROLE regress_monitor IN ROLE pg_monitor;
@@ -86,6 +86,9 @@ CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files;
 CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program;
 CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend;
 
+-- fail, role still owns database objects
+DROP ROLE regress_tenant;
+
 -- fail, creation of these roles failed above so they do not now exist
 SET SESSION AUTHORIZATION regress_role_admin;
 DROP ROLE regress_nosuch_superuser;
@@ -109,20 +112,6 @@ DROP ROLE regress_password_null;
 DROP ROLE regress_noiseword;
 DROP ROLE regress_inroles;
 DROP ROLE regress_adminroles;
-DROP ROLE regress_rolecreator;
-DROP ROLE regress_read_all_data;
-DROP ROLE regress_write_all_data;
-DROP ROLE regress_monitor;
-DROP ROLE regress_read_all_settings;
-DROP ROLE regress_read_all_stats;
-DROP ROLE regress_stat_scan_tables;
-DROP ROLE regress_read_server_files;
-DROP ROLE regress_write_server_files;
-DROP ROLE regress_execute_server_program;
-DROP ROLE regress_signal_backend;
-
--- fail, role still owns database objects
-DROP ROLE regress_tenant;
 
 -- fail, cannot drop ourself nor superusers
 DROP ROLE regress_role_super;
-- 
2.37.1 (Apple Git-137.1)

From ff0407e48fd5505657d96053eeae81fe512a62ff Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Thu, 1 Dec 2022 15:07:02 -0500
Subject: [PATCH v4 1/2] Restrict the privileges of CREATEROLE users.

Previously, CREATEROLE users were permitted to make nearly arbitrary
changes to roles that they didn't create, with certain exceptions,
particularly superuser roles.  Instead, allow CREATEROLE users to make such
changes to roles for which they possess ADMIN OPTION, and to
grant membership only in roles for which they possess ADMIN OPTION.

When a CREATEROLE user who is not a superuser creates a role, grant
ADMIN OPTION on the newly-created role to the creator, so that they
can administer roles they create or for which they have been given
privileges.

With these changes, CREATEROLE users still have very significant
powers that unprivileged users do not receive: they can alter, rename,
drop, comment on, change the password for, and change security labels
on roles.  However, they can now do these things only for roles for
which they possess appropriate privileges, rather than all
non-superuser roles; moreover, they cannot grant a role such as
pg_execute_server_program unless they themselves possess it.
---
 doc/src/sgml/ddl.sgml                         |  10 +-
 doc/src/sgml/ref/alter_role.sgml              |   8 +-
 doc/src/sgml/ref/comment.sgml                 |   3 +-
 doc/src/sgml/ref/create_role.sgml             |   4 +-
 doc/src/sgml/ref/createuser.sgml              |   3 +-
 doc/src/sgml/ref/drop_role.sgml               |   2 +-
 doc/src/sgml/ref/dropuser.sgml                |   7 +-
 doc/src/sgml/ref/grant.sgml                   |   4 +-
 doc/src/sgml/user-manag.sgml                  |  44 ++++++--
 src/backend/catalog/objectaddress.c           |  10 +-
 src/backend/commands/user.c                   | 100 +++++++++++++-----
 .../expected/dummy_seclabel.out               |  17 +--
 .../dummy_seclabel/sql/dummy_seclabel.sql     |  13 ++-
 src/test/regress/expected/create_role.out     |  53 ++++++----
 src/test/regress/sql/create_role.sql          |  38 +++----
 15 files changed, 210 insertions(+), 106 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 6e92bbddd2..ae88266469 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3216,13 +3216,11 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
        name. Therefore, if each user has a separate schema, they access
        their own schemas by default.)  This pattern is a secure schema
        usage pattern unless an untrusted user is the database owner or
-       holds the <literal>CREATEROLE</literal> privilege, in which case no
-       secure schema usage pattern exists.
+       has been granted <literal>ADMIN OPTION</literal> on a relevant role,
+       in which case no secure schema usage pattern exists.
       </para>
       <!-- A database owner can attack the database's users via "CREATE SCHEMA
-           trojan; ALTER DATABASE $mydb SET search_path = trojan, public;".  A
-           CREATEROLE user can issue "GRANT $dbowner TO $me" and then use the
-           database owner attack. -->
+           trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". -->
 
       <para>
        In <productname>PostgreSQL</productname> 15 and later, the default
@@ -3250,7 +3248,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
        unreliable</link>.  If you create functions or extensions in the public
        schema, use the first pattern instead.  Otherwise, like the first
        pattern, this is secure unless an untrusted user is the database owner
-       or holds the <literal>CREATEROLE</literal> privilege.
+       or has been granted <literal>ADMIN OPTION</literal> on a relevant role.
       </para>
      </listitem>
 
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 8a8f828137..6a865f21fd 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -73,7 +73,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
    Roles having <literal>CREATEROLE</literal> privilege can change any of these
    settings except <literal>SUPERUSER</literal>, <literal>REPLICATION</literal>,
    and <literal>BYPASSRLS</literal>; but only for non-superuser and
-   non-replication roles.
+   non-replication roles for which they have been
+   granted <literal>ADMIN OPTION</literal>.
    Ordinary roles can only change their own password.
   </para>
 
@@ -81,7 +82,7 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
    The second variant changes the name of the role.
    Database superusers can rename any role.
    Roles having <literal>CREATEROLE</literal> privilege can rename non-superuser
-   roles.
+   roles for which they have been granted <literal>ADMIN OPTION</literal>.
    The current session user cannot be renamed.
    (Connect as a different user if you need to do that.)
    Because <literal>MD5</literal>-encrypted passwords use the role name as
@@ -116,7 +117,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
   <para>
    Superusers can change anyone's session defaults. Roles having
    <literal>CREATEROLE</literal> privilege can change defaults for non-superuser
-   roles. Ordinary roles can only set defaults for themselves.
+   roles for which they have been granted <literal>ADMIN OPTION</literal>.
+   Ordinary roles can only set defaults for themselves.
    Certain configuration variables cannot be set this way, or can only be
    set if a superuser issues the command.  Only superusers can change a setting
    for all roles in all databases.
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 23d9029af9..7499da1d62 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -99,7 +99,8 @@ COMMENT ON
    For most kinds of object, only the object's owner can set the comment.
    Roles don't have owners, so the rule for <literal>COMMENT ON ROLE</literal> is
    that you must be superuser to comment on a superuser role, or have the
-   <literal>CREATEROLE</literal> privilege to comment on non-superuser roles.
+   <literal>CREATEROLE</literal> privilege and have been granted
+   <literal>ADMIN OPTION</literal> on the target role.
    Likewise, access methods don't have owners either; you must be superuser
    to comment on an access method.
    Of course, a superuser can comment on anything.
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 1ccc832558..0863acbcac 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -119,8 +119,8 @@ in sync when changing the above synopsis!
       <listitem>
        <para>
         These clauses determine whether a role will be permitted to
-        create, alter, drop, comment on, change the security label for,
-        and grant or revoke membership in other roles.
+        create, alter, drop, comment on, and change the security label for
+        other roles.
         See <xref linkend='role-creation' /> for more details about what
         capabilities are conferred by this privilege.
         If not specified, <literal>NOCREATEROLE</literal> is the default.
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index a41a2b24e6..f91dc500a4 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -252,8 +252,7 @@ PostgreSQL documentation
       <listitem>
        <para>
         The new user will be allowed to create, alter, drop, comment on,
-        change the security label for, and grant or revoke membership in
-        other roles; that is,
+        change the security label for other roles; that is,
         this user will have <literal>CREATEROLE</literal> privilege.
         See <xref linkend='role-creation' /> for more details about what
         capabilities are conferred by this privilege.
diff --git a/doc/src/sgml/ref/drop_role.sgml b/doc/src/sgml/ref/drop_role.sgml
index 13dc1cc649..cbcb3cd3d3 100644
--- a/doc/src/sgml/ref/drop_role.sgml
+++ b/doc/src/sgml/ref/drop_role.sgml
@@ -32,7 +32,7 @@ DROP ROLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...
    <command>DROP ROLE</command> removes the specified role(s).
    To drop a superuser role, you must be a superuser yourself;
    to drop non-superuser roles, you must have <literal>CREATEROLE</literal>
-   privilege.
+   privilege and have been granted <literal>ADMIN OPTION</literal> on the role.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml
index 81580507e8..b6be26d5b0 100644
--- a/doc/src/sgml/ref/dropuser.sgml
+++ b/doc/src/sgml/ref/dropuser.sgml
@@ -35,9 +35,10 @@ PostgreSQL documentation
   <para>
    <application>dropuser</application> removes an existing
    <productname>PostgreSQL</productname> user.
-   Only superusers and users with the <literal>CREATEROLE</literal> privilege can
-   remove <productname>PostgreSQL</productname> users.  (To remove a
-   superuser, you must yourself be a superuser.)
+   Superusers can use this command to remove any role; otherwise, only
+   non-superuser roles can be removed, and only by a user who possesses
+   the <literal>CREATEROLE</literal> privilege and has been granted
+   <literal>ADMIN OPTION</literal> on the target role.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index 518bdb32d8..85f5f42ea6 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -271,9 +271,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
    in the role as well.  Without the admin option, ordinary users cannot
    do that.  A role is not considered to hold <literal>WITH ADMIN
    OPTION</literal> on itself.  Database superusers can grant or revoke
-   membership in any role to anyone.  Roles having
-   <literal>CREATEROLE</literal> privilege can grant or revoke membership
-   in any role that is not a superuser. This option defaults to
+   membership in any role to anyone. This option defaults to
    <literal>FALSE</literal>.
   </para>
 
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 7aa6bdac16..71a2d8f298 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -199,7 +199,12 @@ CREATE USER <replaceable>name</replaceable>;
         checks). To create such a role, use <literal>CREATE ROLE
         <replaceable>name</replaceable> CREATEROLE</literal>.
         A role with <literal>CREATEROLE</literal> privilege can alter and drop
-        other roles, too, as well as grant or revoke membership in them.
+        roles which have been granted to the <literal>CREATEROLE</literal>
+        user with the <literal>ADMIN</literal> option. Such a grant occurs
+        automatically when a <literal>CREATEROLE</literal> user that is not
+        a superuser creates a new role, so that by default, a
+        <literal>CREATEROLE</literal> user can alter and drop the roles
+        which they have created.
         Altering a role includes most changes that can be made using
         <literal>ALTER ROLE</literal>, including, for example, changing
         passwords.  It also includes modifications to a role that can
@@ -224,15 +229,6 @@ CREATE USER <replaceable>name</replaceable>;
         confer the ability to grant or revoke the <literal>BYPASSRLS</literal>
         privilege.
        </para>
-       <para>
-        Because the <literal>CREATEROLE</literal> privilege allows a user
-        to grant or revoke membership even in roles to which it does not (yet)
-        have any access, a <literal>CREATEROLE</literal> user can obtain access
-        to the capabilities of every predefined role in the system, including
-        highly privileged roles such as
-        <literal>pg_execute_server_program</literal> and
-        <literal>pg_write_server_files</literal>.
-       </para>
       </listitem>
      </varlistentry>
 
@@ -329,6 +325,34 @@ ALTER ROLE myname SET enable_indexscan TO off;
    <literal>LOGIN</literal> privilege are fairly useless, since they will never
    be invoked.
   </para>
+
+  <para>
+   When a non-superuser creates a role using the <literal>CREATEROLE</literal>
+   privilege, the created role is automatically granted back to the creating
+   user, just as if the bootstrap superuser had executed the command
+   <literal>GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE,
+   INHERIT FALSE</literal>. Since a <literal>CREATEROLE</literal> user can
+   only exercise special privileges with regard to an existing role if they
+   have <literal>ADMIN OPTION</literal> on it, this grant is just sufficient
+   to allow a <literal>CREATEROLE</literal> user to administer the roles they
+   created. However, because it is created with <literal>INHERIT FALSE, SET
+   FALSE</literal>, the <literal>CREATEROLE</literal> user doesn't inherit the
+   privileges of the created role, nor can it access the privileges of that
+   role using <literal>SET ROLE</literal>. However, since any user who has
+   <literal>ADMIN OPTION</literal> on a role can grant membership in that
+   role to any other user, the <literal>CREATEROLE</literal> user can gain
+   access to the created role by simplying granting that role back to
+   themselves with the <literal>INHERIT</literal> and/or <literal>SET</literal>
+   options. Thus, the fact that privileges are not inherited by default nor
+   is <literal>SET ROLE</literal> granted by default is a safeguard against
+   accidents, not a security feature. Also note that, because this automatic
+   grant is granted by the bootstrap user, it cannot be removed or changed by
+   the <literal>CREATEROLE</literal> user; however, any superuser could
+   revoke it, modify it, and/or issue additional such grants to other
+   <literal>CREATEROLE</literal> users. Whichever <literal>CREATEROLE</literal>
+   users have <literal>ADMIN OPTION</literal> on a role at any given time
+   can administer it.
+  </para>
  </sect1>
 
  <sect1 id="role-membership">
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 1367b5e7c5..25c50d66fd 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2538,7 +2538,9 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
 
 			/*
 			 * We treat roles as being "owned" by those with CREATEROLE priv,
-			 * except that superusers are only owned by superusers.
+			 * provided that they also have admin option on the role.
+			 *
+			 * However, superusers are only owned by superusers.
 			 */
 			if (superuser_arg(address.objectId))
 			{
@@ -2553,6 +2555,12 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
 					ereport(ERROR,
 							(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 							 errmsg("must have CREATEROLE privilege")));
+				if (!is_admin_of_role(roleid, address.objectId))
+					ereport(ERROR,
+							(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+							 errmsg("must have admin option on role \"%s\"",
+									GetUserNameFromId(address.objectId,
+													  true))));
 			}
 			break;
 		case OBJECT_TSPARSER:
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index bc2b95ac81..1ae2d0a66f 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -519,6 +519,42 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		}
 	}
 
+	/*
+	 * If the current user isn't a superuser, make them an admin of the new
+	 * role so that they can administer the new object they just created.
+	 * Superusers will be able to do that anyway.
+	 *
+	 * The grantor of record for this implicit grant is the bootstrap
+	 * superuser, which means that the CREATEROLE user cannot revoke the
+	 * grant. They can however grant the created role back to themselves
+	 * with different options, since they enjoy ADMIN OPTION on it.
+	 */
+	if (!superuser())
+	{
+		RoleSpec   *current_role = makeNode(RoleSpec);
+		GrantRoleOptions	poptself;
+
+		current_role->roletype = ROLESPEC_CURRENT_ROLE;
+		current_role->location = -1;
+
+		poptself.specified = GRANT_ROLE_SPECIFIED_ADMIN
+			| GRANT_ROLE_SPECIFIED_INHERIT
+			| GRANT_ROLE_SPECIFIED_SET;
+		poptself.admin = true;
+		poptself.inherit = false;
+		poptself.set = false;
+
+		AddRoleMems(BOOTSTRAP_SUPERUSERID, stmt->role, roleid,
+					list_make1(current_role), list_make1_oid(GetUserId()),
+					BOOTSTRAP_SUPERUSERID, &poptself);
+
+		/*
+		 * We must make the implicit grant visible to the code below, else
+		 * the additional grants will fail.
+		 */
+		CommandCounterIncrement();
+	}
+
 	/*
 	 * Add the specified members to this new role. adminmembers get the admin
 	 * option, rolemembers don't.
@@ -694,9 +730,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	/*
 	 * To mess with a superuser or replication role in any way you gotta be
 	 * superuser.  We also insist on superuser to change the BYPASSRLS
-	 * property.  Otherwise, if you don't have createrole, you're only allowed
-	 * to (1) change your own password or (2) add members to a role for which
-	 * you have ADMIN OPTION.
+	 * property.
 	 */
 	if (authform->rolsuper || dissuper)
 	{
@@ -719,29 +753,35 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must be superuser to change bypassrls attribute")));
 	}
-	else if (!have_createrole_privilege())
+
+	/*
+	 * Most changes to a role require that you both have CREATEROLE privileges
+	 * and also ADMIN OPTION on the role.
+	 */
+	if (!have_createrole_privilege() ||
+		!is_admin_of_role(GetUserId(), roleid))
 	{
-		/* things you certainly can't do without CREATEROLE */
+		/* things an unprivileged user certainly can't do */
 		if (dinherit || dcreaterole || dcreatedb || dcanlogin || dconnlimit ||
 			dvalidUntil)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied")));
 
-		/* without CREATEROLE, can only change your own password */
+		/* an unprivileged user can change their own password */
 		if (dpassword && roleid != currentUserId)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have CREATEROLE privilege to change another user's password")));
-
-		/* without CREATEROLE, can only add members to roles you admin */
-		if (drolemembers && !is_admin_of_role(currentUserId, roleid))
-			ereport(ERROR,
-					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must have admin option on role \"%s\" to add members",
-							rolename)));
 	}
 
+	/* To add members to a role, you need ADMIN OPTION. */
+	if (drolemembers && !is_admin_of_role(currentUserId, roleid))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must have admin option on role \"%s\" to add members",
+						rolename)));
+
 	/* Convert validuntil to internal form */
 	if (dvalidUntil)
 	{
@@ -935,8 +975,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
 		shdepLockAndCheckObject(AuthIdRelationId, roleid);
 
 		/*
-		 * To mess with a superuser you gotta be superuser; else you need
-		 * createrole, or just want to change your own settings
+		 * To mess with a superuser you gotta be superuser; otherwise you
+		 * need CREATEROLE plus admin option on the target role; unless you're
+		 * just trying to change your own settings
 		 */
 		if (roleform->rolsuper)
 		{
@@ -947,7 +988,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
 		}
 		else
 		{
-			if (!have_createrole_privilege() && roleid != GetUserId())
+			if ((!have_createrole_privilege() ||
+				!is_admin_of_role(GetUserId(), roleid))
+				&& roleid != GetUserId())
 				ereport(ERROR,
 						(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 						 errmsg("permission denied")));
@@ -1067,13 +1110,18 @@ DropRole(DropRoleStmt *stmt)
 
 		/*
 		 * For safety's sake, we allow createrole holders to drop ordinary
-		 * roles but not superuser roles.  This is mainly to avoid the
-		 * scenario where you accidentally drop the last superuser.
+		 * roles but not superuser roles, and only if they also have ADMIN
+		 * OPTION.
 		 */
 		if (roleform->rolsuper && !superuser())
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must be superuser to drop superusers")));
+		if (!is_admin_of_role(GetUserId(), roleid))
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("must have admin option on role \"%s\"",
+							role)));
 
 		/* DROP hook for the role being removed */
 		InvokeObjectDropHook(AuthIdRelationId, roleid, 0);
@@ -1312,7 +1360,8 @@ RenameRole(const char *oldname, const char *newname)
 				 errmsg("role \"%s\" already exists", newname)));
 
 	/*
-	 * createrole is enough privilege unless you want to mess with a superuser
+	 * Only superusers can mess with superusers. Otherwise, a user with
+	 * CREATEROLE can rename a role for which they have ADMIN OPTION.
 	 */
 	if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper)
 	{
@@ -1323,7 +1372,8 @@ RenameRole(const char *oldname, const char *newname)
 	}
 	else
 	{
-		if (!have_createrole_privilege())
+		if (!have_createrole_privilege() ||
+			!is_admin_of_role(GetUserId(), roleid))
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied to rename role")));
@@ -2023,11 +2073,9 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid,
 	else
 	{
 		/*
-		 * Otherwise, must have createrole or admin option on the role to be
-		 * changed.
+		 * Otherwise, must have admin option on the role to be changed.
 		 */
-		if (!has_createrole_privilege(currentUserId) &&
-			!is_admin_of_role(currentUserId, roleid))
+		if (!is_admin_of_role(currentUserId, roleid))
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must have admin option on role \"%s\"",
@@ -2049,7 +2097,7 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid,
  * be passed as InvalidOid, and this function will infer the user to be
  * recorded as the grantor. In many cases, this will be the current user, but
  * things get more complicated when the current user doesn't possess ADMIN
- * OPTION on the role but rather relies on having CREATEROLE privileges, or
+ * OPTION on the role but rather relies on having SUPERUSER privileges, or
  * on inheriting the privileges of a role which does have ADMIN OPTION. See
  * below for details.
  *
@@ -2075,7 +2123,7 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant)
 		 * not depend on any other existing grants, so always default to this
 		 * interpretation when possible.
 		 */
-		if (has_createrole_privilege(currentUserId))
+		if (superuser_arg(currentUserId))
 			return BOOTSTRAP_SUPERUSERID;
 
 		/*
diff --git a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
index b2d898a7d1..c57d4fd2df 100644
--- a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
+++ b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out
@@ -6,9 +6,11 @@ CREATE EXTENSION dummy_seclabel;
 SET client_min_messages TO 'warning';
 DROP ROLE IF EXISTS regress_dummy_seclabel_user1;
 DROP ROLE IF EXISTS regress_dummy_seclabel_user2;
+DROP ROLE IF EXISTS regress_dummy_seclabel_user3;
 RESET client_min_messages;
 CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE;
 CREATE USER regress_dummy_seclabel_user2;
+CREATE USER regress_dummy_seclabel_user3;
 CREATE TABLE dummy_seclabel_tbl1 (a int, b text);
 CREATE TABLE dummy_seclabel_tbl2 (x int, y text);
 CREATE VIEW dummy_seclabel_view1 AS SELECT * FROM dummy_seclabel_tbl2;
@@ -16,6 +18,8 @@ CREATE FUNCTION dummy_seclabel_four() RETURNS integer AS $$SELECT 4$$ language s
 CREATE DOMAIN dummy_seclabel_domain AS text;
 ALTER TABLE dummy_seclabel_tbl1 OWNER TO regress_dummy_seclabel_user1;
 ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2;
+GRANT regress_dummy_seclabel_user2, regress_dummy_seclabel_user3
+  TO regress_dummy_seclabel_user1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
 --
 -- Test of SECURITY LABEL statement with a plugin
 --
@@ -43,16 +47,16 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified';			-- OK
 -- Test for shared database object
 --
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user1;
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified';			-- OK
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...';	-- fail
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'classified';			-- OK
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS '...invalid label...';	-- fail
 ERROR:  '...invalid label...' is not a valid security label
 SECURITY LABEL FOR 'dummy' ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- OK
 SECURITY LABEL FOR 'unknown_seclabel' ON ROLE regress_dummy_seclabel_user1 IS 'unclassified';	-- fail
 ERROR:  security label provider "unknown_seclabel" is not loaded
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'secret';	-- fail (not superuser)
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'secret';	-- fail (not superuser)
 ERROR:  only superuser can set 'secret' label
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'unclassified';	-- fail (not found)
-ERROR:  role "regress_dummy_seclabel_user3" does not exist
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user4 IS 'unclassified';	-- fail (not found)
+ERROR:  role "regress_dummy_seclabel_user4" does not exist
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user2;
 SECURITY LABEL ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- fail (not privileged)
 ERROR:  must have CREATEROLE privilege
@@ -81,8 +85,8 @@ SELECT objtype, objname, provider, label FROM pg_seclabels
  domain       | dummy_seclabel_domain        | dummy    | classified
  function     | dummy_seclabel_four()        | dummy    | classified
  publication  | dummy_pub                    | dummy    | classified
- role         | regress_dummy_seclabel_user1 | dummy    | classified
  role         | regress_dummy_seclabel_user2 | dummy    | unclassified
+ role         | regress_dummy_seclabel_user3 | dummy    | classified
  schema       | dummy_seclabel_test          | dummy    | unclassified
  subscription | dummy_sub                    | dummy    | classified
  table        | dummy_seclabel_tbl1          | dummy    | top secret
@@ -115,3 +119,4 @@ DROP SUBSCRIPTION dummy_sub;
 DROP PUBLICATION dummy_pub;
 DROP ROLE regress_dummy_seclabel_user1;
 DROP ROLE regress_dummy_seclabel_user2;
+DROP ROLE regress_dummy_seclabel_user3;
diff --git a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
index 8c347b6a68..649409757e 100644
--- a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
+++ b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql
@@ -8,11 +8,13 @@ SET client_min_messages TO 'warning';
 
 DROP ROLE IF EXISTS regress_dummy_seclabel_user1;
 DROP ROLE IF EXISTS regress_dummy_seclabel_user2;
+DROP ROLE IF EXISTS regress_dummy_seclabel_user3;
 
 RESET client_min_messages;
 
 CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE;
 CREATE USER regress_dummy_seclabel_user2;
+CREATE USER regress_dummy_seclabel_user3;
 
 CREATE TABLE dummy_seclabel_tbl1 (a int, b text);
 CREATE TABLE dummy_seclabel_tbl2 (x int, y text);
@@ -22,6 +24,8 @@ CREATE DOMAIN dummy_seclabel_domain AS text;
 
 ALTER TABLE dummy_seclabel_tbl1 OWNER TO regress_dummy_seclabel_user1;
 ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2;
+GRANT regress_dummy_seclabel_user2, regress_dummy_seclabel_user3
+  TO regress_dummy_seclabel_user1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
 
 --
 -- Test of SECURITY LABEL statement with a plugin
@@ -47,12 +51,12 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified';			-- OK
 --
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user1;
 
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified';			-- OK
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...';	-- fail
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'classified';			-- OK
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS '...invalid label...';	-- fail
 SECURITY LABEL FOR 'dummy' ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- OK
 SECURITY LABEL FOR 'unknown_seclabel' ON ROLE regress_dummy_seclabel_user1 IS 'unclassified';	-- fail
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'secret';	-- fail (not superuser)
-SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'unclassified';	-- fail (not found)
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'secret';	-- fail (not superuser)
+SECURITY LABEL ON ROLE regress_dummy_seclabel_user4 IS 'unclassified';	-- fail (not found)
 
 SET SESSION AUTHORIZATION regress_dummy_seclabel_user2;
 SECURITY LABEL ON ROLE regress_dummy_seclabel_user2 IS 'unclassified';	-- fail (not privileged)
@@ -113,3 +117,4 @@ DROP PUBLICATION dummy_pub;
 
 DROP ROLE regress_dummy_seclabel_user1;
 DROP ROLE regress_dummy_seclabel_user2;
+DROP ROLE regress_dummy_seclabel_user3;
diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out
index 4e67d72760..f5f745504c 100644
--- a/src/test/regress/expected/create_role.out
+++ b/src/test/regress/expected/create_role.out
@@ -1,6 +1,7 @@
 -- ok, superuser can create users with any set of privileges
 CREATE ROLE regress_role_super SUPERUSER;
 CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS;
+CREATE ROLE regress_role_normal;
 -- fail, only superusers can create users with these privileges
 SET SESSION AUTHORIZATION regress_role_admin;
 CREATE ROLE regress_nosuch_superuser SUPERUSER;
@@ -13,7 +14,7 @@ CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
 ERROR:  must be superuser to create bypassrls users
 -- ok, having CREATEROLE is enough to create users with these privileges
 CREATE ROLE regress_createdb CREATEDB;
-CREATE ROLE regress_createrole CREATEROLE;
+CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
 CREATE ROLE regress_login LOGIN;
 CREATE ROLE regress_inherit INHERIT;
 CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -51,7 +52,19 @@ CREATE ROLE regress_plainrole;
 -- ok, roles with CREATEROLE can create new roles with it
 CREATE ROLE regress_rolecreator CREATEROLE;
 -- ok, roles with CREATEROLE can create new roles with privilege they lack
-CREATE ROLE regress_tenant CREATEDB CREATEROLE LOGIN INHERIT CONNECTION LIMIT 5;
+CREATE ROLE regress_hasprivs CREATEDB CREATEROLE LOGIN INHERIT
+	CONNECTION LIMIT 5;
+-- ok, we should be able to modify a role we created
+COMMENT ON ROLE regress_hasprivs IS 'some comment';
+ALTER ROLE regress_hasprivs RENAME TO regress_tenant;
+ALTER ROLE regress_tenant NOINHERIT NOLOGIN CONNECTION LIMIT 7;
+-- fail, we should be unable to modify a role we did not create
+COMMENT ON ROLE regress_role_normal IS 'some comment';
+ERROR:  must have admin option on role "regress_role_normal"
+ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal;
+ERROR:  permission denied to rename role
+ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7;
+ERROR:  permission denied
 -- ok, regress_tenant can create objects within the database
 SET SESSION AUTHORIZATION regress_tenant;
 CREATE TABLE tenant_table (i integer);
@@ -70,20 +83,35 @@ ALTER VIEW tenant_view OWNER TO regress_role_admin;
 ERROR:  must be owner of view tenant_view
 DROP VIEW tenant_view;
 ERROR:  must be owner of view tenant_view
--- fail, cannot take ownership of these objects from regress_tenant
+-- fail, we don't inherit permissions from regress_tenant
 REASSIGN OWNED BY regress_tenant TO regress_createrole;
 ERROR:  permission denied to reassign objects
--- ok, having CREATEROLE is enough to create roles in privileged roles
+-- fail, CREATEROLE is not enough to create roles in privileged roles
 CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
+ERROR:  must have admin option on role "pg_read_all_data"
 CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
+ERROR:  must have admin option on role "pg_write_all_data"
 CREATE ROLE regress_monitor IN ROLE pg_monitor;
+ERROR:  must have admin option on role "pg_monitor"
 CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings;
+ERROR:  must have admin option on role "pg_read_all_settings"
 CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats;
+ERROR:  must have admin option on role "pg_read_all_stats"
 CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables;
+ERROR:  must have admin option on role "pg_stat_scan_tables"
 CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files;
+ERROR:  must have admin option on role "pg_read_server_files"
 CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files;
+ERROR:  must have admin option on role "pg_write_server_files"
 CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program;
+ERROR:  must have admin option on role "pg_execute_server_program"
 CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend;
+ERROR:  must have admin option on role "pg_signal_backend"
+-- fail, role still owns database objects
+DROP ROLE regress_tenant;
+ERROR:  role "regress_tenant" cannot be dropped because some objects depend on it
+DETAIL:  owner of table tenant_table
+owner of view tenant_view
 -- fail, creation of these roles failed above so they do not now exist
 SET SESSION AUTHORIZATION regress_role_admin;
 DROP ROLE regress_nosuch_superuser;
@@ -114,22 +142,6 @@ DROP ROLE regress_password_null;
 DROP ROLE regress_noiseword;
 DROP ROLE regress_inroles;
 DROP ROLE regress_adminroles;
-DROP ROLE regress_rolecreator;
-DROP ROLE regress_read_all_data;
-DROP ROLE regress_write_all_data;
-DROP ROLE regress_monitor;
-DROP ROLE regress_read_all_settings;
-DROP ROLE regress_read_all_stats;
-DROP ROLE regress_stat_scan_tables;
-DROP ROLE regress_read_server_files;
-DROP ROLE regress_write_server_files;
-DROP ROLE regress_execute_server_program;
-DROP ROLE regress_signal_backend;
--- fail, role still owns database objects
-DROP ROLE regress_tenant;
-ERROR:  role "regress_tenant" cannot be dropped because some objects depend on it
-DETAIL:  owner of table tenant_table
-owner of view tenant_view
 -- fail, cannot drop ourself nor superusers
 DROP ROLE regress_role_super;
 ERROR:  must be superuser to drop superusers
@@ -143,3 +155,4 @@ DROP VIEW tenant_view;
 DROP ROLE regress_tenant;
 DROP ROLE regress_role_admin;
 DROP ROLE regress_role_super;
+DROP ROLE regress_role_normal;
diff --git a/src/test/regress/sql/create_role.sql b/src/test/regress/sql/create_role.sql
index 292dc08797..ddc80578d9 100644
--- a/src/test/regress/sql/create_role.sql
+++ b/src/test/regress/sql/create_role.sql
@@ -1,6 +1,7 @@
 -- ok, superuser can create users with any set of privileges
 CREATE ROLE regress_role_super SUPERUSER;
 CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS;
+CREATE ROLE regress_role_normal;
 
 -- fail, only superusers can create users with these privileges
 SET SESSION AUTHORIZATION regress_role_admin;
@@ -11,7 +12,7 @@ CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
 
 -- ok, having CREATEROLE is enough to create users with these privileges
 CREATE ROLE regress_createdb CREATEDB;
-CREATE ROLE regress_createrole CREATEROLE;
+CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
 CREATE ROLE regress_login LOGIN;
 CREATE ROLE regress_inherit INHERIT;
 CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -54,7 +55,18 @@ CREATE ROLE regress_plainrole;
 CREATE ROLE regress_rolecreator CREATEROLE;
 
 -- ok, roles with CREATEROLE can create new roles with privilege they lack
-CREATE ROLE regress_tenant CREATEDB CREATEROLE LOGIN INHERIT CONNECTION LIMIT 5;
+CREATE ROLE regress_hasprivs CREATEDB CREATEROLE LOGIN INHERIT
+	CONNECTION LIMIT 5;
+
+-- ok, we should be able to modify a role we created
+COMMENT ON ROLE regress_hasprivs IS 'some comment';
+ALTER ROLE regress_hasprivs RENAME TO regress_tenant;
+ALTER ROLE regress_tenant NOINHERIT NOLOGIN CONNECTION LIMIT 7;
+
+-- fail, we should be unable to modify a role we did not create
+COMMENT ON ROLE regress_role_normal IS 'some comment';
+ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal;
+ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7;
 
 -- ok, regress_tenant can create objects within the database
 SET SESSION AUTHORIZATION regress_tenant;
@@ -71,10 +83,10 @@ DROP TABLE tenant_table;
 ALTER VIEW tenant_view OWNER TO regress_role_admin;
 DROP VIEW tenant_view;
 
--- fail, cannot take ownership of these objects from regress_tenant
+-- fail, we don't inherit permissions from regress_tenant
 REASSIGN OWNED BY regress_tenant TO regress_createrole;
 
--- ok, having CREATEROLE is enough to create roles in privileged roles
+-- fail, CREATEROLE is not enough to create roles in privileged roles
 CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
 CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
 CREATE ROLE regress_monitor IN ROLE pg_monitor;
@@ -86,6 +98,9 @@ CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files;
 CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program;
 CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend;
 
+-- fail, role still owns database objects
+DROP ROLE regress_tenant;
+
 -- fail, creation of these roles failed above so they do not now exist
 SET SESSION AUTHORIZATION regress_role_admin;
 DROP ROLE regress_nosuch_superuser;
@@ -109,20 +124,6 @@ DROP ROLE regress_password_null;
 DROP ROLE regress_noiseword;
 DROP ROLE regress_inroles;
 DROP ROLE regress_adminroles;
-DROP ROLE regress_rolecreator;
-DROP ROLE regress_read_all_data;
-DROP ROLE regress_write_all_data;
-DROP ROLE regress_monitor;
-DROP ROLE regress_read_all_settings;
-DROP ROLE regress_read_all_stats;
-DROP ROLE regress_stat_scan_tables;
-DROP ROLE regress_read_server_files;
-DROP ROLE regress_write_server_files;
-DROP ROLE regress_execute_server_program;
-DROP ROLE regress_signal_backend;
-
--- fail, role still owns database objects
-DROP ROLE regress_tenant;
 
 -- fail, cannot drop ourself nor superusers
 DROP ROLE regress_role_super;
@@ -136,3 +137,4 @@ DROP VIEW tenant_view;
 DROP ROLE regress_tenant;
 DROP ROLE regress_role_admin;
 DROP ROLE regress_role_super;
+DROP ROLE regress_role_normal;
-- 
2.37.1 (Apple Git-137.1)

From 2310de9be1301ea1215288db43d0f9e04c522501 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Thu, 5 Jan 2023 13:56:01 -0500
Subject: [PATCH v4 2/2] Add new GUC createrole_self_grant.

Can be set to the empty string, or to either or both of "set" or
"inherit". If set to a non-empty value, a non-superuser who creates
a role (necessarily by relying up the CREATEROLE privilege) will
grant that role back to themselves with the specified options.

This isn't a security feature, because the grant that this feature
triggers can also be performed explicitly. Instead, it's a user experience
feature. A superuser would necessarily inherit the privileges of any
created role and be able to access all such roles via SET ROLE;
with this patch, you can configure createrole_self_grant = 'set, inherit'
to provide a similar experience for a user who has CREATEROLE but not
SUPERUSER.
---
 doc/src/sgml/config.sgml                      | 33 +++++++
 doc/src/sgml/ref/create_role.sgml             |  1 +
 doc/src/sgml/ref/createuser.sgml              |  1 +
 src/backend/commands/user.c                   | 97 ++++++++++++++++++-
 src/backend/utils/misc/guc_tables.c           | 12 +++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/commands/user.h                   | 10 +-
 src/test/regress/expected/create_role.out     | 33 +++++++
 src/test/regress/sql/create_role.sql          | 37 +++++++
 9 files changed, 220 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 05b3862d09..d86385d308 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9421,6 +9421,39 @@ SET XML OPTION { DOCUMENT | CONTENT };
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-createrole-self-grant" xreflabel="createrole_self_grant">
+      <term><varname>createrole_self_grant</varname> (<type>string</type>)
+      <indexterm>
+       <primary><varname>createrole_self_grant</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        If a user who has <literal>CREATEROLE</literal> but not
+        <literal>SUPERUSER</literal> creates a role, and if this
+        is set to a non-empty value, the newly-created role will be granted
+        to the creating user with the options specified. The value must be
+        <literal>set</literal>, <literal>inherit</literal>, or a
+        comma-separated list of these.
+       </para>
+       <para>
+        The purpose of this option is to allow a <literal>CREATEROLE</literal>
+        user who is not a superuser to automatically inherit, or automatically
+        gain the ability to <literal>SET ROLE</literal> to, any created users.
+        Since a <literal>CREATEROLE</literal> user is always implicitly granted
+        <literal>ADMIN OPTION</literal> on created roles, that user could
+        always execute a <literal>GRANT</literal> statement that would achieve
+        the same effect as this setting. However, it can be convenient for
+        usability reasons if the grant happens automatically. A superuser
+        automatically inherits the privileges of every role and can always
+        <literal>SET ROLE</literal> to any role, and this setting can be used
+        to produce a similar behavior for <literal>CREATEROLE</literal> users
+        for users which they create.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
     </sect2>
      <sect2 id="runtime-config-client-format">
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 0863acbcac..7ce4e38b45 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -506,6 +506,7 @@ CREATE ROLE <replaceable class="parameter">name</replaceable> [ WITH ADMIN <repl
    <member><xref linkend="sql-grant"/></member>
    <member><xref linkend="sql-revoke"/></member>
    <member><xref linkend="app-createuser"/></member>
+   <member><xref linkend="guc-createrole-self-grant"/></member>
   </simplelist>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index f91dc500a4..9a1c3d01f4 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -555,6 +555,7 @@ PostgreSQL documentation
   <simplelist type="inline">
    <member><xref linkend="app-dropuser"/></member>
    <member><xref linkend="sql-createrole"/></member>
+   <member><xref linkend="guc-createrole-self-grant"/></member>
   </simplelist>
  </refsect1>
 
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 1ae2d0a66f..4d193a6f9a 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -39,6 +39,7 @@
 #include "utils/fmgroids.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
+#include "utils/varlena.h"
 
 /*
  * Removing a role grant - or the admin option on it - might recurse to
@@ -81,8 +82,11 @@ typedef struct
 #define GRANT_ROLE_SPECIFIED_INHERIT		0x0002
 #define GRANT_ROLE_SPECIFIED_SET			0x0004
 
-/* GUC parameter */
+/* GUC parameters */
 int			Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
+char	   *createrole_self_grant = "";
+bool		createrole_self_grant_enabled = false;
+GrantRoleOptions	createrole_self_grant_options;
 
 /* Hook to check passwords in CreateRole() and AlterRole() */
 check_password_hook_type check_password_hook = NULL;
@@ -532,10 +536,13 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	if (!superuser())
 	{
 		RoleSpec   *current_role = makeNode(RoleSpec);
-		GrantRoleOptions	poptself;
+		GrantRoleOptions poptself;
+		List	   *memberSpecs;
+		List	   *memberIds = list_make1_oid(currentUserId);
 
 		current_role->roletype = ROLESPEC_CURRENT_ROLE;
 		current_role->location = -1;
+		memberSpecs = list_make1(current_role);
 
 		poptself.specified = GRANT_ROLE_SPECIFIED_ADMIN
 			| GRANT_ROLE_SPECIFIED_INHERIT
@@ -545,7 +552,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		poptself.set = false;
 
 		AddRoleMems(BOOTSTRAP_SUPERUSERID, stmt->role, roleid,
-					list_make1(current_role), list_make1_oid(GetUserId()),
+					memberSpecs, memberIds,
 					BOOTSTRAP_SUPERUSERID, &poptself);
 
 		/*
@@ -553,6 +560,20 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		 * the additional grants will fail.
 		 */
 		CommandCounterIncrement();
+
+		/*
+		 * Because of the implicit grant above, a CREATEROLE user who creates
+		 * a role has the ability to grant that role back to themselves with
+		 * the INHERIT or SET options, if they wish to inherit the role's
+		 * privileges or be able to SET ROLE to it. The createrole_self_grant
+		 * GUC can be used to make this happen automatically. This has no
+		 * security implications since the same user is able to make the same
+		 * grant using an explicit GRANT statement; it's just convenient.
+		 */
+		if (createrole_self_grant_enabled)
+			AddRoleMems(currentUserId, stmt->role, roleid,
+						memberSpecs, memberIds,
+						currentUserId, &createrole_self_grant_options);
 	}
 
 	/*
@@ -2414,3 +2435,73 @@ InitGrantRoleOptions(GrantRoleOptions *popt)
 	popt->inherit = false;
 	popt->set = true;
 }
+
+/*
+ * GUC check_hook for createrole_self_grant
+ */
+bool
+check_createrole_self_grant(char **newval, void **extra, GucSource source)
+{
+	char	   *rawstring;
+	List	   *elemlist;
+	ListCell   *l;
+	unsigned	options = 0;
+	unsigned   *result;
+
+	/* Need a modifiable copy of string */
+	rawstring = pstrdup(*newval);
+
+	if (!SplitIdentifierString(rawstring, ',', &elemlist))
+	{
+		/* syntax error in list */
+		GUC_check_errdetail("List syntax is invalid.");
+		pfree(rawstring);
+		list_free(elemlist);
+		return false;
+	}
+
+	foreach(l, elemlist)
+	{
+		char	   *tok = (char *) lfirst(l);
+
+		if (pg_strcasecmp(tok, "SET") == 0)
+			options |= GRANT_ROLE_SPECIFIED_SET;
+		else if (pg_strcasecmp(tok, "INHERIT") == 0)
+			options |= GRANT_ROLE_SPECIFIED_INHERIT;
+		else
+		{
+			GUC_check_errdetail("Unrecognized key word: \"%s\".", tok);
+			pfree(rawstring);
+			list_free(elemlist);
+			return false;
+		}
+	}
+
+	pfree(rawstring);
+	list_free(elemlist);
+
+	result = (unsigned *) guc_malloc(LOG, sizeof(unsigned));
+	*result = options;
+	*extra = result;
+
+	return true;
+}
+
+/*
+ * GUC assign_hook for createrole_self_grant
+ */
+void
+assign_createrole_self_grant(const char *newval, void *extra)
+{
+	unsigned	options = * (unsigned *) extra;
+
+	createrole_self_grant_enabled = (options != 0);
+	createrole_self_grant_options.specified = GRANT_ROLE_SPECIFIED_ADMIN
+		| GRANT_ROLE_SPECIFIED_INHERIT
+		| GRANT_ROLE_SPECIFIED_SET;
+	createrole_self_grant_options.admin = false;
+	createrole_self_grant_options.inherit =
+		(options & GRANT_ROLE_SPECIFIED_INHERIT) != 0;
+	createrole_self_grant_options.set =
+		(options & GRANT_ROLE_SPECIFIED_SET) != 0;
+}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 68328b1402..e222b087eb 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3937,6 +3937,18 @@ struct config_string ConfigureNamesString[] =
 		check_temp_tablespaces, assign_temp_tablespaces, NULL
 	},
 
+	{
+		{"createrole_self_grant", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Sets whether a CREATEROLE user automatically grants "
+						 "the role to themselves, and with which options."),
+			NULL,
+			GUC_LIST_INPUT
+		},
+		&createrole_self_grant,
+		"",
+		check_createrole_self_grant, assign_createrole_self_grant, NULL
+	},
+
 	{
 		{"dynamic_library_path", PGC_SUSET, CLIENT_CONN_OTHER,
 			gettext_noop("Sets the path for dynamically loadable modules."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 5afdeb04de..cf8cc12164 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -702,6 +702,7 @@
 #xmlbinary = 'base64'
 #xmloption = 'content'
 #gin_pending_list_limit = 4MB
+#createrole_self_grant = ''		# set and/or inherit
 
 # - Locale and Formatting -
 
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 54c720d880..97dcb93791 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -15,9 +15,11 @@
 #include "libpq/crypt.h"
 #include "nodes/parsenodes.h"
 #include "parser/parse_node.h"
+#include "utils/guc.h"
 
-/* GUC. Is actually of type PasswordType. */
-extern PGDLLIMPORT int Password_encryption;
+/* GUCs */
+extern PGDLLIMPORT int Password_encryption; /* values from enum PasswordType */
+extern PGDLLIMPORT char *createrole_self_grant;
 
 /* Hook to check passwords in CreateRole() and AlterRole() */
 typedef void (*check_password_hook_type) (const char *username, const char *shadow_pass, PasswordType password_type, Datum validuntil_time, bool validuntil_null);
@@ -34,4 +36,8 @@ extern void DropOwnedObjects(DropOwnedStmt *stmt);
 extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt);
 extern List *roleSpecsToIds(List *memberNames);
 
+extern bool check_createrole_self_grant(char **newval, void **extra,
+										GucSource source);
+extern void assign_createrole_self_grant(const char *newval, void *extra);
+
 #endif							/* USER_H */
diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out
index f5f745504c..bed3749888 100644
--- a/src/test/regress/expected/create_role.out
+++ b/src/test/regress/expected/create_role.out
@@ -1,6 +1,7 @@
 -- ok, superuser can create users with any set of privileges
 CREATE ROLE regress_role_super SUPERUSER;
 CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS;
+GRANT CREATE ON DATABASE regression TO regress_role_admin WITH GRANT OPTION;
 CREATE ROLE regress_role_normal;
 -- fail, only superusers can create users with these privileges
 SET SESSION AUTHORIZATION regress_role_admin;
@@ -15,6 +16,7 @@ ERROR:  must be superuser to create bypassrls users
 -- ok, having CREATEROLE is enough to create users with these privileges
 CREATE ROLE regress_createdb CREATEDB;
 CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
+GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION;
 CREATE ROLE regress_login LOGIN;
 CREATE ROLE regress_inherit INHERIT;
 CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -83,9 +85,37 @@ ALTER VIEW tenant_view OWNER TO regress_role_admin;
 ERROR:  must be owner of view tenant_view
 DROP VIEW tenant_view;
 ERROR:  must be owner of view tenant_view
+-- fail, can't create objects owned as regress_tenant
+CREATE SCHEMA regress_tenant_schema AUTHORIZATION regress_tenant;
+ERROR:  must be able to SET ROLE "regress_tenant"
 -- fail, we don't inherit permissions from regress_tenant
 REASSIGN OWNED BY regress_tenant TO regress_createrole;
 ERROR:  permission denied to reassign objects
+-- ok, create a role with a value for createrole_self_grant
+SET createrole_self_grant = 'set, inherit';
+CREATE ROLE regress_tenant2;
+GRANT CREATE ON DATABASE regression TO regress_tenant2;
+-- ok, regress_tenant2 can create objects within the database
+SET SESSION AUTHORIZATION regress_tenant2;
+CREATE TABLE tenant2_table (i integer);
+REVOKE ALL PRIVILEGES ON tenant2_table FROM PUBLIC;
+-- ok, because we have SET and INHERIT on regress_tenant2
+SET SESSION AUTHORIZATION regress_createrole;
+CREATE SCHEMA regress_tenant2_schema AUTHORIZATION regress_tenant2;
+ALTER SCHEMA regress_tenant2_schema OWNER TO regress_createrole;
+ALTER TABLE tenant2_table OWNER TO regress_createrole;
+ALTER TABLE tenant2_table OWNER TO regress_tenant2;
+-- with SET but not INHERIT, we can give away objects but not take them
+REVOKE INHERIT OPTION FOR regress_tenant2 FROM regress_createrole;
+ALTER SCHEMA regress_tenant2_schema OWNER TO regress_tenant2;
+ALTER TABLE tenant2_table OWNER TO regress_createrole;
+ERROR:  must be owner of table tenant2_table
+-- with INHERIT but not SET, we can take objects but not give them away
+GRANT regress_tenant2 TO regress_createrole WITH INHERIT TRUE, SET FALSE;
+ALTER TABLE tenant2_table OWNER TO regress_createrole;
+ALTER TABLE tenant2_table OWNER TO regress_tenant2;
+ERROR:  must be able to SET ROLE "regress_tenant2"
+DROP TABLE tenant2_table;
 -- fail, CREATEROLE is not enough to create roles in privileged roles
 CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
 ERROR:  must have admin option on role "pg_read_all_data"
@@ -131,6 +161,8 @@ ERROR:  role "regress_nosuch_recursive" does not exist
 DROP ROLE regress_nosuch_admin_recursive;
 ERROR:  role "regress_nosuch_admin_recursive" does not exist
 DROP ROLE regress_plainrole;
+-- must revoke privileges before dropping role
+REVOKE CREATE ON DATABASE regression FROM regress_createrole CASCADE;
 -- ok, should be able to drop non-superuser roles we created
 DROP ROLE regress_createdb;
 DROP ROLE regress_createrole;
@@ -149,6 +181,7 @@ DROP ROLE regress_role_admin;
 ERROR:  current user cannot be dropped
 -- ok
 RESET SESSION AUTHORIZATION;
+REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE;
 DROP INDEX tenant_idx;
 DROP TABLE tenant_table;
 DROP VIEW tenant_view;
diff --git a/src/test/regress/sql/create_role.sql b/src/test/regress/sql/create_role.sql
index ddc80578d9..edaed43588 100644
--- a/src/test/regress/sql/create_role.sql
+++ b/src/test/regress/sql/create_role.sql
@@ -1,6 +1,7 @@
 -- ok, superuser can create users with any set of privileges
 CREATE ROLE regress_role_super SUPERUSER;
 CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS;
+GRANT CREATE ON DATABASE regression TO regress_role_admin WITH GRANT OPTION;
 CREATE ROLE regress_role_normal;
 
 -- fail, only superusers can create users with these privileges
@@ -13,6 +14,7 @@ CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
 -- ok, having CREATEROLE is enough to create users with these privileges
 CREATE ROLE regress_createdb CREATEDB;
 CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
+GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION;
 CREATE ROLE regress_login LOGIN;
 CREATE ROLE regress_inherit INHERIT;
 CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -83,9 +85,40 @@ DROP TABLE tenant_table;
 ALTER VIEW tenant_view OWNER TO regress_role_admin;
 DROP VIEW tenant_view;
 
+-- fail, can't create objects owned as regress_tenant
+CREATE SCHEMA regress_tenant_schema AUTHORIZATION regress_tenant;
+
 -- fail, we don't inherit permissions from regress_tenant
 REASSIGN OWNED BY regress_tenant TO regress_createrole;
 
+-- ok, create a role with a value for createrole_self_grant
+SET createrole_self_grant = 'set, inherit';
+CREATE ROLE regress_tenant2;
+GRANT CREATE ON DATABASE regression TO regress_tenant2;
+
+-- ok, regress_tenant2 can create objects within the database
+SET SESSION AUTHORIZATION regress_tenant2;
+CREATE TABLE tenant2_table (i integer);
+REVOKE ALL PRIVILEGES ON tenant2_table FROM PUBLIC;
+
+-- ok, because we have SET and INHERIT on regress_tenant2
+SET SESSION AUTHORIZATION regress_createrole;
+CREATE SCHEMA regress_tenant2_schema AUTHORIZATION regress_tenant2;
+ALTER SCHEMA regress_tenant2_schema OWNER TO regress_createrole;
+ALTER TABLE tenant2_table OWNER TO regress_createrole;
+ALTER TABLE tenant2_table OWNER TO regress_tenant2;
+
+-- with SET but not INHERIT, we can give away objects but not take them
+REVOKE INHERIT OPTION FOR regress_tenant2 FROM regress_createrole;
+ALTER SCHEMA regress_tenant2_schema OWNER TO regress_tenant2;
+ALTER TABLE tenant2_table OWNER TO regress_createrole;
+
+-- with INHERIT but not SET, we can take objects but not give them away
+GRANT regress_tenant2 TO regress_createrole WITH INHERIT TRUE, SET FALSE;
+ALTER TABLE tenant2_table OWNER TO regress_createrole;
+ALTER TABLE tenant2_table OWNER TO regress_tenant2;
+DROP TABLE tenant2_table;
+
 -- fail, CREATEROLE is not enough to create roles in privileged roles
 CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
 CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
@@ -113,6 +146,9 @@ DROP ROLE regress_nosuch_recursive;
 DROP ROLE regress_nosuch_admin_recursive;
 DROP ROLE regress_plainrole;
 
+-- must revoke privileges before dropping role
+REVOKE CREATE ON DATABASE regression FROM regress_createrole CASCADE;
+
 -- ok, should be able to drop non-superuser roles we created
 DROP ROLE regress_createdb;
 DROP ROLE regress_createrole;
@@ -131,6 +167,7 @@ DROP ROLE regress_role_admin;
 
 -- ok
 RESET SESSION AUTHORIZATION;
+REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE;
 DROP INDEX tenant_idx;
 DROP TABLE tenant_table;
 DROP VIEW tenant_view;
-- 
2.37.1 (Apple Git-137.1)