CockroachDB 23.1.4 makes broader role delegation an explicit setting
An opt-in cluster setting lets CREATEROLE users manage non-admin role membership. Administrator roles retain a separate permission check.
Creating a role and deciding who may join it are separate administrative powers. CockroachDB 23.1.4 adds an opt-in setting that connects them for non-admin roles. With the setting enabled, a user holding CREATEROLE can grant and revoke those memberships without separately holding administration authority on every target role. The default remains disabled.
The June 20 release notes name the setting sql.auth.createrole_allows_grant_role_membership.enabled. They describe broader membership management as a security update and say the project expects the behavior to become the default in the future. As of this release, an administrator must choose to enable it. An expectation about a future default does not change the present permission model.
I appreciate CockroachDB's explicit operational tradeoffs. Delegating role administration is useful, but the scope of that delegation needs to remain visible. A setting that expands what an existing role option permits deserves attention even when the release calls it an optional capability.
The grant path checks the selected policy
The grant-role implementation at 23.1.4 shows how the choice affects authorization. It registers the new setting as a public Boolean with a false default. When enabled, the broad membership-management path checks whether the caller has CREATEROLE. When disabled, that path checks whether the caller has the admin role.
The implementation then examines each role being granted. If the caller qualifies for broader delegation and the target role does not have admin authority, it can proceed without requiring the target role's admin option. Otherwise it checks the caller's applicable administration permissions. This is a change to who may make membership assignments, rather than a change to the privileges stored inside the target role.
That distinction matters for an existing role catalog. Enabling the setting does not require editing every role to make the new path relevant. It changes what a holder of CREATEROLE can do with those non-admin roles. The authority represented by the existing option becomes broader under the selected cluster policy.
The setting can be enabled with the cluster-setting statement below. This is an explicit policy change, not a prerequisite for applying the maintenance release itself.
SET CLUSTER SETTING
sql.auth.createrole_allows_grant_role_membership.enabled = true;
Administrator authority remains separately guarded
The implementation checks whether the role being granted has admin authority, rather than relying solely on its spelling. Its broader permission path excludes such roles. A caller cannot infer from the new setting that every role, including an administrator role, is available for unrestricted delegation.
This preserves a meaningful boundary around high-privilege membership. The code's comments identify the need for an admin-option check when granting an admin role. The release-note shorthand of non-admin roles therefore corresponds to an explicit condition in the authorization implementation.
The statement also checks that both the roles and their proposed members exist. It detects membership cycles before writing assignments, including attempts to make a role a member of itself. These checks remain part of role management after the broader permission decision succeeds. Authorization to request a membership does not make every requested membership graph valid.
My preference is to read this setting as a delegation-policy choice. It can reduce repetitive administration grants for a trusted role manager, but it also gives that manager authority across existing non-admin roles. That scope is the relevant tradeoff when deciding whether CREATEROLE should imply broader membership control.
Membership changes refresh their stored state
The same implementation writes assignments to system.role_members. When rows change, it bumps the membership table version to trigger a refresh. It also records a grant event naming the grantee roles and members, preserving the administrative operation in its event machinery. That connects the administrative statement to the state consulted by later authorization work. The setting changes permission to perform the operation, while the membership update still needs its normal state-maintenance behavior.
A grant without the admin option also does not silently clear an existing admin option. The source preserves it on an existing assignment unless the operation explicitly changes the relevant authority. This is another reason to distinguish adding ordinary membership from changing who may administer that membership.
Other permission changes narrow visibility
Version 23.1.4 also changes what users with MODIFYSQLCLUSTERSETTING can view. The notes restrict that visibility to sql.defaults settings rather than all cluster settings. That change concerns observing configuration, while the new CREATEROLE switch concerns managing memberships. Both are permission changes, but they act in different directions on different operations.
The release also fixes SHOW SYSTEM GRANTS incorrectly requiring admin or root privileges. Together these changes make administration more specific. Operators gain an optional route for delegated membership control and a corrected inspection command, while configuration visibility follows a narrower scope. Applying the release preserves the disabled default for the membership switch until an administrator deliberately selects the expanded behavior.