- When a user logs into the DB Console using OIDC, the cluster extracts the groups claim from the ID token.
- If the groups claim is not present in the ID token, the cluster checks the access token (if it’s a JWT).
- If neither token yields any groups, the cluster queries the IdP’s userinfo endpoint as a fallback.
- Each group is mapped to a cluster role by matching the group name to a role name.
- The user is granted each corresponding role, and roles that the user holds directly but that no longer match the user’s groups are revoked. Roles held only indirectly through another role are not revoked individually. Refer to Role synchronization.
Prerequisites
- Enable OIDC Authentication for DB Console.
- Understand the structure of tokens issued by your identity provider.
- Know which claim in your tokens contains group memberships.
Configuration
Before you begin, it may be useful to enable authentication logging, which can help you confirm successful configuration or troubleshoot issues. For details, refer to Troubleshooting.Step 1: Enable OIDC authorization
Enable OIDC authorization and configure the groups claim:The
userinfo_group_key setting is only used when neither the ID token nor the access token yields any groups, either because the groups claim is missing from both or because it is present but empty. CockroachDB will query the IdP’s userinfo endpoint using this key to retrieve group memberships.Step 2: Create matching roles
Create CockroachDB roles that match your IdP group names and grant appropriate privileges to each role. Remember that role names must comply with CockroachDB’s identifier requirements.Group names from the IdP are normalized using case folding and Unicode normalization (NFC) before matching to role names. This means that group names are typically converted to lowercase for matching purposes.
developers and analysts:
Step 3: Confirm configuration
- On your identity provider, set up test users with memberships in groups that should be synced to CockroachDB roles.
- Navigate to the DB Console in your web browser and log in using OIDC. Refer to Log in to a cluster’s DB Console with SSO.
-
Using your
admincredentials in a SQL shell, verify the user’s role assignments:
How it works
Group extraction process
When a user authenticates with OIDC and OIDC authorization is enabled:- Token validation: CockroachDB validates the ID token signature, issuer, and claims.
-
Group extraction from ID token: CockroachDB looks for the configured
group_claim(default:groups) in the ID token. - Group extraction from access token: If the groups claim is not found in the ID token, CockroachDB checks the access token (if it’s a JWT format) for the same claim.
-
Userinfo endpoint fallback: If the ID token and access token yield no groups, either because the claim is not present in either token or because it is present but contains an empty list, CockroachDB queries the IdP’s userinfo endpoint and looks for the
userinfo_group_key(default:groups). Some identity providers return an empty array in the token and populate groups only in the userinfo response. - Combine and deduplicate: Groups from the ID token and access token are combined and deduplicated. Groups from the userinfo endpoint are processed separately.
-
Normalization: Group names are normalized using
MakeSQLUsernameFromUserInput, which performs case folding and Unicode normalization (NFC). - Role matching: Each normalized group name is compared against existing CockroachDB roles.
Groups claim format: The groups claim is expected to be a JSON array of strings, for example
["developers", "analysts"]. If the claim is a single string instead, CockroachDB splits it on commas only, so "developers,analysts" yields two groups, while a group name that contains spaces, such as "data platform", is treated as one group. Each name is trimmed of surrounding whitespace and lowercased, empty entries are dropped, and duplicates are removed. The same parsing applies to the value of userinfo_group_key in the userinfo response. Identity providers that convey multiple groups should return a JSON array.OIDC authorization checks three sources for groups: ID token, access token, and userinfo endpoint. Groups from the ID token and access token are combined into a single list, while userinfo groups are treated as a separate fallback that is consulted only when that combined list is empty.
Role synchronization
On each DB Console login, CockroachDB synchronizes the user’s role memberships:- Grant new roles: If a group matches an existing role name and the user doesn’t already have that role, it is granted.
-
Revoke stale roles: If the user directly holds roles that don’t match any current groups, those roles are revoked. Only the user’s direct role memberships are reconciled; roles the user holds indirectly through a directly granted role are not revoked individually. For example, if the user was directly granted
developers, anddevelopersis itself a member ofapp_readers, the user holdsapp_readersindirectly. If the IdP then lists onlyapp_readers, CockroachDB revokesdevelopersand grantsapp_readersdirectly. - Skip non-existent roles: If a group doesn’t match any existing role, it is silently skipped (no error is raised).
IdP groups that don’t correspond to CockroachDB roles are silently ignored. You must pre-create roles in CockroachDB for them to be granted.
Empty groups behavior
If the groups claim exists but contains an empty array ([]), and no other source yields any groups:
- All role memberships that the user holds directly are revoked.
- Login is rejected with error:
OIDC: unable to complete authentication: groups list was empty for <username>
Missing groups claim
If the configured groups claim cannot be found in the ID token, the access token, or the userinfo response (for example, because the claim name is misconfigured, the IdP was not asked for the scope that emits groups, or the IdP transiently failed to return the claim):- The user’s existing role memberships are left unchanged.
- Login is rejected with error:
OIDC: unable to complete authentication: no groups claim found for <username>
Troubleshooting
EnableSESSION logging to preserve data that will help troubleshoot OIDC authorization issues:
Once all functionality is configured and tested successfully, we recommend disabling session logging to conserve system resources.
cockroach-sql-auth.log from your logging directory.
Potential issues to investigate may pertain to:
- Token validation: Ensure the ID token is properly signed and issued by a trusted issuer.
- Missing groups claim: Check if the ID token or access token contains the configured claim name. Use a JWT decoder tool to inspect the tokens. If the claim cannot be found in any source, the login is rejected and the user’s roles are not modified.
- Claim name mismatch: Verify that
server.oidc_authentication.group_claimmatches the actual claim name in your tokens. - Userinfo endpoint: If relying on userinfo fallback, ensure the endpoint is accessible and returns the expected JSON structure.
- Role name mismatches: Remember that group names are normalized (typically lowercased). Check that your role names match the normalized group names.
- Empty groups: Verify that users have group memberships in the IdP. An explicitly empty groups list revokes the user’s directly held roles and rejects the login.
- User doesn’t exist: Ensure the user has been created in CockroachDB before attempting to log in, or enable automatic user provisioning. With OIDC authorization enabled, authorization runs before the login step, so a missing user fails authorization first (either no groups claim is found, or role synchronization reports that the role does not exist) and the browser receives a
403error. Theclient_authentication_failedevent withReasonset toUSER_NOT_FOUNDdescribed in Audit failed SSO logins is written only when OIDC authorization is disabled.
Common errors
Error:OIDC: unable to complete authentication: groups list was empty for <username>
- Cause: The groups claim exists but contains an empty array. The user’s directly held roles have been revoked.
- Solution: Ensure users have appropriate group memberships in your identity provider.
OIDC: unable to complete authentication: no groups claim found for <username>
- Cause: The configured groups claim was not found in the ID token, the access token, or the userinfo response. The user’s role memberships were not modified.
- Solution: Verify that
server.oidc_authentication.group_claimandserver.oidc_authentication.userinfo_group_keymatch the claim name your IdP uses, and thatserver.oidc_authentication.scopesincludes the scope your IdP requires to emit group information. If the configuration is correct, the failure may be transient; retry the login.
- Cause: Groups from the IdP don’t match any existing CockroachDB roles.
- Solution: Create roles with names matching the normalized group names from your IdP.
- Cause: The user doesn’t exist in CockroachDB and automatic user provisioning is not enabled.
- Log entry: With OIDC authorization enabled, the failure surfaces as an authorization error (no groups claim found, or a role synchronization error that the role does not exist) rather than as a
client_authentication_failedevent withReasonset toUSER_NOT_FOUND. That event is emitted only when OIDC authorization is disabled. For details, refer to Audit failed SSO logins. - Solution: Either create the user in CockroachDB manually before they attempt to log into the DB Console, or enable automatic user provisioning.
Security considerations
- Maintain backup authentication: Always keep a backup authentication method (like password) for administrative users in case of IdP outages.
- Validate token issuers: Ensure your OIDC configuration only accepts tokens from trusted issuers.
- Pre-create roles with minimal privileges: Create roles before enabling authorization and grant only the necessary privileges following the principle of least privilege.
- Monitor role synchronization: Regularly audit role assignments and changes. Enable audit logging if available to track when roles are granted or revoked.
- Secure the userinfo endpoint: If using userinfo fallback, ensure the endpoint requires proper authentication and is only accessible over HTTPS.
- Review empty groups policy: Understand that empty groups will block login. Ensure this aligns with your security requirements.
- Regularly audit IdP groups: Review and clean up group memberships in your identity provider to ensure they reflect current access requirements.
- User provisioning: If using automatic user provisioning, ensure your OIDC provider is properly secured and only trusted users can authenticate. Regularly review auto-provisioned users to identify accounts that may need deprovisioning. If not using automatic provisioning, ensure all users are created in CockroachDB before they need access to the DB Console.

