> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Configure JWT Authorization

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

If you manage users through an identity provider (IdP) that supports JSON Web Tokens (JWT), you can configure CockroachDB to automatically assign <InternalLink path="security-reference/authorization">roles</InternalLink> to users based on group claims in their JWT tokens, simplifying access control.

If JWT authorization is enabled:

1. When a client connects to the cluster using a JWT token, the cluster extracts the groups claim from the token.
2. If the groups claim is not present in the token, the cluster queries the IdP's userinfo endpoint as a fallback.
3. Each group is mapped to a cluster role by matching the group name to a role name.
4. The user is granted each corresponding role, and roles that no longer match the user's groups are revoked.
5. In conjunction with <InternalLink path="sso-sql#configure-user-provisioning">automatic user provisioning</InternalLink> if enabled, users are created automatically during their first authentication and simultaneously receive specified role memberships.

## Prerequisites

* Enable <InternalLink path="sso-sql">JWT Authentication</InternalLink>.
* Understand the structure of JWT tokens issued by your identity provider.
* Know which claim in your JWT 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](#troubleshooting).

### Step 1: Enable JWT authorization

Enable JWT authorization and configure the groups claim:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- Enable JWT authentication (if not already enabled)
SET CLUSTER SETTING server.jwt_authentication.enabled = true;

-- Enable JWT authorization
SET CLUSTER SETTING server.jwt_authentication.authorization.enabled = true;

-- Configure the JWT claim containing groups (default: 'groups')
SET CLUSTER SETTING server.jwt_authentication.group_claim = 'groups';

-- (Optional) Configure the userinfo endpoint JSON key for groups fallback
SET CLUSTER SETTING server.jwt_authentication.userinfo_group_key = 'groups';
```

<Note>
  The `userinfo_group_key` setting is only used when the groups claim is missing from the JWT token. CockroachDB will query the IdP's userinfo endpoint using this key to retrieve group memberships.
</Note>

### Step 2: Configure IdP-specific settings

The configuration varies by identity provider:

#### Okta

Okta typically includes groups in the default `groups` claim:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.jwt_authentication.group_claim = 'groups';
SET CLUSTER SETTING server.jwt_authentication.userinfo_group_key = 'groups';
```

Example JWT token from Okta:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
  "iss": "https://your-okta-domain.okta.com",
  "sub": "00u1abc2def3ghi4jkl",
  "aud": "your_client_id",
  "email": "alice@example.com",
  "groups": ["developers", "team-alpha"]
}
```

#### Keycloak

For Keycloak Groups (default mapping):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.jwt_authentication.group_claim = 'groups';
```

Example JWT token:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
  "iss": "https://keycloak.example.com/realms/myrealm",
  "sub": "user123",
  "email": "alice@example.com",
  "groups": ["developers", "team-alpha"]
}
```

<Note>
  **For Keycloak realm roles**: CockroachDB does not support nested JSON paths like `realm_access.roles`. If you need to use realm roles, create a Keycloak protocol mapper to flatten the roles into a top-level claim:
</Note>

1. In Keycloak, go to your client settings
2. Navigate to **Mappers** > **Create**
3. Choose **User Realm Role** mapper type
4. Set the **Token Claim Name** to `roles` (or another simple name)
5. Configure CockroachDB to use this claim:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.jwt_authentication.group_claim = 'roles';
```

#### Azure AD / Microsoft Entra ID

Azure AD typically uses the `groups` claim:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.jwt_authentication.group_claim = 'groups';
```

<Note>
  In Azure AD, you may need to configure group claims in your app registration. Refer to [Microsoft's documentation](https://learn.microsoft.com/en-us/azure/active-directory/hybrid/connect/how-to-connect-fed-group-claims) for details.
</Note>

### Step 3: 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 <InternalLink path="create-user#user-names">identifier requirements</InternalLink>.

<Note>
  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.
</Note>

For example, if your JWT tokens contain groups named `developers` and `analysts`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- Create role for developers
CREATE ROLE developers;
GRANT ALL ON DATABASE app TO developers;

-- Create role for analysts
CREATE ROLE analysts;
GRANT SELECT ON DATABASE analytics TO analysts;
```

<Note>
  If you are going to use <InternalLink path="sso-sql#configure-user-provisioning">automatic user provisioning</InternalLink> in conjunction with JWT authorization, be sure to complete the creation of group roles before enabling automatic user provisioning. Auto-provisioned users will only receive roles for groups that already exist as CockroachDB roles.
</Note>

### Step 4: Confirm configuration

1. On your identity provider, set up test users with memberships in groups that should be synced to CockroachDB roles.

2. If <InternalLink path="sso-sql#configure-user-provisioning">automatic user provisioning</InternalLink> is not enabled, create the matching test users when logged in as an admin to CockroachDB:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE ROLE username1 LOGIN;
   CREATE ROLE username2 LOGIN;
   CREATE ROLE username3 LOGIN;
   ```

   If automatic user provisioning is enabled, users will be created automatically during their first login.

3. Obtain a JWT token from your identity provider and connect to CockroachDB using the token. Refer to <InternalLink path="sso-sql#authenticate-to-your-cluster">Authenticate to your cluster</InternalLink>.

4. Using your `admin` credentials, log in to the CockroachDB SQL shell and verify the user's role assignments:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   -- View roles granted to a specific user
   SHOW GRANTS FOR username1;

   -- Check if user has a specific role
   SELECT pg_has_role('username1', 'developers', 'member');
   ```

   For auto-provisioned users, you can identify them by their `PROVISIONSRC` role option:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   -- View all users and their provisioning source
   SELECT rolname, rolprovisionsrc FROM pg_roles
   WHERE rolprovisionsrc LIKE 'jwt_token:%';
   ```

## How it works

### Group extraction process

When a user authenticates with a JWT token and JWT authorization is enabled:

1. **Token validation**: CockroachDB first validates the JWT token signature, issuer, and claims.

2. **Group extraction**:
   * CockroachDB looks for the configured `group_claim` (default: `groups`) in the JWT token.
   * If the claim is missing or cannot be parsed, CockroachDB queries the IdP's userinfo endpoint and looks for the `userinfo_group_key` (default: `groups`).

3. **Normalization**: Group names are normalized using `MakeSQLUsernameFromUserInput`, which performs case folding and Unicode normalization (NFC).

4. **Role matching**: Each normalized group name is compared against existing CockroachDB roles.

### Role synchronization

On each login, CockroachDB synchronizes the user's role memberships:

1. **Grant new roles**: If a group matches an existing role name and the user doesn't already have that role, it is granted.

2. **Revoke stale roles**: If the user has roles that don't match any current groups, those roles are revoked.

3. **Skip non-existent roles**: If a group doesn't match any existing role, it is silently skipped (no error is raised).

<Note>
  IdP groups that don't correspond to CockroachDB roles are silently ignored. You must pre-create roles in CockroachDB for them to be granted.
</Note>

### Empty groups behavior

If the groups claim exists but contains an empty array (`[]`):

1. All existing role memberships are revoked from the user.
2. Login is **rejected** with error: `JWT authorization: empty group list`

This behavior ensures that users without group memberships cannot access the cluster, which is a security feature to prevent unauthorized access.

### Userinfo endpoint fallback

If the JWT token does not contain the configured groups claim, CockroachDB will attempt to query the IdP's userinfo endpoint:

1. CockroachDB makes an HTTP GET request to the userinfo endpoint using the access token.
2. The response is parsed as JSON, and CockroachDB looks for the `userinfo_group_key`.
3. If the userinfo lookup fails, login is rejected with error: `JWT authorization: userinfo lookup failed`

## Troubleshooting

Enable <InternalLink path="logging#sessions">`SESSION` logging</InternalLink> to preserve data that will help troubleshoot JWT authorization issues:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.auth_log.sql_sessions.enabled = true;
```

<Note>
  Once all functionality is configured and tested successfully, we recommend disabling session logging to conserve system resources.
</Note>

To view the logs, open `cockroach-session.log` from your <InternalLink path="configure-logs#logging-directory">logging directory</InternalLink>.

Potential issues to investigate may pertain to:

* **JWT token validation**: Ensure the token is properly signed and issued by a trusted issuer.
* **Missing groups claim**: Check if the token contains the configured claim name. Use a JWT decoder tool to inspect the token.
* **Claim name mismatch**: Verify that `server.jwt_authentication.group_claim` matches 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.

### Common errors

**Error**: `JWT authorization: empty group list`

* **Cause**: The groups claim exists but contains an empty array.
* **Solution**: Ensure users have appropriate group memberships in your identity provider.

**Error**: `JWT authorization: userinfo lookup failed`

* **Cause**: The token doesn't contain the groups claim, and the userinfo endpoint query failed.
* **Solution**: Either include groups in the JWT token or ensure the userinfo endpoint is accessible and configured correctly.

**Error**: User can log in but has no privileges

* **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.

## Security considerations

1. **Maintain backup authentication**: Always keep a backup authentication method (like password or client certificate) for administrative users in case of IdP outages.

2. **Validate token issuers**: Ensure `server.jwt_authentication.issuers` is properly configured to accept only trusted issuers.

3. **Pre-create roles with minimal privileges**: Create roles before enabling authorization and grant only the necessary privileges following the principle of least privilege.

4. **Monitor role synchronization**: Regularly audit role assignments and changes. Enable audit logging if available to track when roles are granted or revoked.

5. **Secure the userinfo endpoint**: If using userinfo fallback, ensure the endpoint requires proper authentication and is only accessible over HTTPS.

6. **Review empty groups policy**: Understand that empty groups will block login. Ensure this aligns with your security requirements.

7. **Regularly audit IdP groups**: Review and clean up group memberships in your identity provider to ensure they reflect current access requirements.

## See also

* <InternalLink path="sso-sql">Cluster Single Sign-on (SSO) using JWTs</InternalLink>
* <InternalLink path="ldap-authorization">LDAP Authorization</InternalLink>
* <InternalLink path="oidc-authorization">OIDC Authorization</InternalLink>
* <InternalLink path="security-reference/authorization">Authorization</InternalLink>
* <InternalLink path="security-reference/authorization">Security Reference: Authorization</InternalLink>