> ## 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 LDAP 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 a service compatible with the Lightweight Directory Access Protocol (LDAP), such as Active Directory or Microsoft Entra ID, you can configure CockroachDB to automatically assign <InternalLink path="security-reference/authorization">roles</InternalLink> to users based on LDAP group memberships, simplifying access control.

If LDAP authorization is enabled:

1. When a client connects to the cluster using LDAP, the cluster looks up the user's group membership in the LDAP service.
2. Each LDAP group is mapped to a cluster role using the group's Common Name (CN) in the LDAP service.
3. The user is granted each corresponding role, and roles that no longer match the user's groups are revoked.
4. In conjunction with <InternalLink path="ldap-authentication#option-1-automatic-user-provisioning-recommended">automatic user provisioning</InternalLink> if enabled, users are created automatically during their first authentication and simultaneously receive specified role memberships.

## Prerequisites

* Enable <InternalLink path="ldap-authentication">LDAP Authentication</InternalLink>.

## Configuration

Before you begin, it may be useful to enable authentication logging, which can help you confirm sucessful configuration or troubleshoot issues. For details, refer to [Troubleshooting](#troubleshooting).

### Step 1: Enable LDAP authorization

Add the `ldapgrouplistfilter` parameter to the HBA configuration that you enabled for <InternalLink path="ldap-authentication">LDAP Authentication</InternalLink>. The configuration will include two important LDAP filters:

1. `ldapsearchfilter`: Determines which users can authenticate
2. `ldapgrouplistfilter`: Defines which groups should be considered for authorization

Here's a basic example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.host_based_authentication.configuration = '
host    all    all    all    ldap    ldapserver=ldap.example.com
    ldapport=636
    "ldapbasedn=ou=users,dc=example,dc=com"
    "ldapbinddn=cn=readonly,dc=example,dc=com"
    ldapbindpasswd=readonly_password
    ldapsearchattribute=uid
    "ldapsearchfilter=(memberof=cn=cockroachdb_users,ou=groups,dc=example,dc=com)"
    "ldapgrouplistfilter=(objectClass=groupOfNames)"';
```

For more precise control, you can configure these filters to match your security requirements. Refer to the examples below, and further documentation on [LDAP syntax filters](https://learn.microsoft.com/en-us/archive/technet-wiki/5392.active-directory-ldap-syntax-filters).

#### Search filter examples

To restrict authentication to members of specific groups:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- Users must be members of either the database users group or the analytics team
"ldapsearchfilter=(|(memberof=cn=cockroachdb_users,ou=groups,dc=example,dc=com)(memberof=cn=analytics_team,ou=groups,dc=example,dc=com))"
```

#### Group List filter examples

The `ldapgrouplistfilter` configuration varies by LDAP server type:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- For Azure Active Directory:
"ldapgrouplistfilter=(objectCategory=CN=Group,CN=Schema,CN=Configuration,DC=example,DC=com)"

-- For OpenLDAP:
"ldapgrouplistfilter=(objectClass=groupOfNames)"
```

For enhanced security, restrict the groups that can be mapped to CockroachDB roles:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- Only map specific groups to CockroachDB roles
"ldapgrouplistfilter=(|(cn=crdb_analysts)(cn=crdb_developers))"
```

<Note>
  We recommend that you explicitly specify which groups should be mapped to CockroachDB roles rather than using broader filters. This ensures that only intended groups are granted database access.
</Note>

### Step 2: Create matching roles

Create CockroachDB roles that match your LDAP 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>.

For example, if you've configured the group filter to allow `crdb_analysts` and `crdb_developers`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- Create role for analysts
CREATE ROLE crdb_analysts;
GRANT SELECT ON DATABASE analytics TO crdb_analysts;

-- Create role for developers
CREATE ROLE crdb_developers;
GRANT ALL ON DATABASE app TO crdb_developers;
```

<Note>
  If you are going to use <InternalLink path="ldap-authentication#option-1-automatic-user-provisioning-recommended">automatic user provisioning</InternalLink> in conjunction with LDAP 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 3: Confirm configuration

1. On the LDAP server, set up test users with memberships in groups that should be synced to CockroachDB users.

2. If <InternalLink path="ldap-authentication#option-1-automatic-user-provisioning-recommended">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. Log in to CockroachDB as each test user (refer to <InternalLink path="ldap-authentication#connect-to-a-cluster-using-ldap">Connect to a cluster using LDAP</InternalLink>).

4. Using your `admin` credentials, log in to the CockroachDB SQL shell and run `SHOW USERS;` to view and verify users and their role assignments.

   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 role assignments
   SHOW USERS;

   -- Filter for auto-provisioned users
   SELECT * FROM [SHOW USERS] AS u
   WHERE EXISTS (
       SELECT 1 FROM unnest(u.options) AS opt
       WHERE opt LIKE 'PROVISIONSRC=ldap:%'
   );
   ```

## Troubleshooting

Enable <InternalLink path="logging#sessions">`SESSION` logging</InternalLink> to preserve data that will help troubleshoot LDAP 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:

* Network connectivity to the LDAP server.
* Incorrect bind DN or password.
* Search filter not matching the intended users.
* TLS certificates.
* Missing or mismatched role names.

## Security Considerations

1. Always keep a backup authentication method (like password) for administrative users.
2. Use LDAPS (LDAP over TLS) in production environments.
3. Use a restricted service account for directory searches.
4. Regularly audit LDAP group memberships.
5. Monitor authentication logs for unusual patterns.