Configure LDAP Authorization

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 automatic user provisioning if enabled, users are created automatically during their first authentication and simultaneously receive specified role memberships.

​

Prerequisites

• Enable LDAP Authentication.

​

Configuration

​

Step 1: Enable LDAP authorization

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

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)"';

​

Search filter examples

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

-- For Azure Active Directory:
"ldapgrouplistfilter=(objectCategory=CN=Group,CN=Schema,CN=Configuration,DC=example,DC=com)"

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

-- Only map specific groups to CockroachDB roles
"ldapgrouplistfilter=(|(cn=crdb_analysts)(cn=crdb_developers))"

​

Step 2: Create matching roles

-- 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;

​

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 automatic user provisioning is not enabled, create the matching test users when logged in as an admin to CockroachDB:
   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 Connect to a cluster using LDAP).
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:
   -- 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

SET CLUSTER SETTING server.auth_log.sql_sessions.enabled = true;

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