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

# Configuring ClickHouse to use LDAP for authentication and role mapping

> Describes how to configure ClickHouse to use LDAP for authentication and role mapping

<CloudNotSupportedBadge />

<Note>
  This page isn't applicable to [ClickHouse Cloud](https://clickhouse.com/cloud). The feature documented here isn't available in ClickHouse Cloud services.
  See the ClickHouse [Cloud Compatibility](/products/cloud/guides/cloud-compatibility) guide for more information.
</Note>

ClickHouse can be configured to use LDAP to authenticate ClickHouse database users. This guide provides a simple example of integrating ClickHouse with an LDAP system authenticating to a publicly available directory.

<Steps>
  <Step>
    <h2 id="1-configure-ldap-connection-settings-in-clickhouse">
      Configure LDAP connection settings in ClickHouse
    </h2>

    1. Test your connection to this public LDAP server:

       ```bash theme={null}
       $ ldapsearch -x -b dc=example,dc=com -H ldap://ldap.forumsys.com
       ```

       The reply will be something like this:

       ```response theme={null}
       # extended LDIF
       #
       # LDAPv3
       # base <dc=example,dc=com> with scope subtree
       # filter: (objectclass=*)
       # requesting: ALL
       #

       # example.com
       dn: dc=example,dc=com
       objectClass: top
       objectClass: dcObject
       objectClass: organization
       o: example.com
       dc: example
       ...
       ```

    2. Edit the `config.xml` file and add the following to configure LDAP:
       ```xml theme={null}
       <ldap_servers>
           <test_ldap_server>
           <host>ldap.forumsys.com</host>
           <port>389</port>
           <bind_dn>uid={user_name},dc=example,dc=com</bind_dn>
           <enable_tls>no</enable_tls>
           <tls_require_cert>never</tls_require_cert>
           </test_ldap_server>
       </ldap_servers>
       ```

    <Note>
      The `<test_ldap_server>` tags is an arbitrary label to identify a particular LDAP server.
    </Note>

    These are the basic settings used above:

    | Parameter          | Description                                   | Example                             |
    | ------------------ | --------------------------------------------- | ----------------------------------- |
    | host               | hostname or IP of LDAP server                 | ldap.forumsys.com                   |
    | port               | directory port for LDAP server                | 389                                 |
    | bind\_dn           | template path to users                        | `uid={user_name},dc=example,dc=com` |
    | enable\_tls        | whether to use secure ldap                    | no                                  |
    | tls\_require\_cert | whether to require certificate for connection | never                               |

    <Note>
      In this example, since the public server uses 389 and doesn't use a secure port, we disable TLS for demonstration purposes.
    </Note>

    <Note>
      View the [LDAP doc page](/concepts/features/security/external-authenticators/ldap) for more details on the LDAP settings.
    </Note>

    3. Add the `<ldap>` section to `<user_directories>` section to configure the user role mapping. This section defines when a user is authenticated and what role the user will receive. In this basic example, any user authenticating to LDAP will receive the `scientists_role` which will be defined at a later step in ClickHouse. The section should look similar to this:

       ```xml theme={null}
       <user_directories>
           <users_xml>
               <path>users.xml</path>
           </users_xml>
           <local_directory>
               <path>/var/lib/clickhouse/access/</path>
           </local_directory>
           <ldap>
                 <server>test_ldap_server</server>
                 <roles>
                    <scientists_role />
                 </roles>
                 <role_mapping>
                    <base_dn>dc=example,dc=com</base_dn>
                    <search_filter>(&amp;(objectClass=groupOfUniqueNames)(uniqueMember={bind_dn}))</search_filter>
                    <attribute>cn</attribute>
                 </role_mapping>
           </ldap>
       </user_directories>
       ```

       These are the basic settings used above:

       | Parameter      | Description                                                         | Example                                                       |
       | -------------- | ------------------------------------------------------------------- | ------------------------------------------------------------- |
       | server         | label defined in the prior ldap\_servers section                    | test\_ldap\_server                                            |
       | roles          | name of the roles defined in ClickHouse the users will be mapped to | scientists\_role                                              |
       | base\_dn       | base path to start search for groups with user                      | dc=example,dc=com                                             |
       | search\_filter | ldap search filter to identify groups to select for mapping users   | `(&(objectClass=groupOfUniqueNames)(uniqueMember={bind_dn}))` |
       | attribute      | which attribute name should value be returned from                  | cn                                                            |

    4. Restart your ClickHouse server to apply the settings.
  </Step>

  <Step>
    <h2 id="2-configure-clickhouse-database-roles-and-permissions">
      Configure ClickHouse database roles and permissions
    </h2>

    <Note>
      The procedures in this section assumes that SQL Access Control and Account Management in ClickHouse has been enabled. To enable, view the [SQL Users and Roles guide](/concepts/features/security/access-rights).
    </Note>

    1. Create a role in clickhouse with the same name used in the role mapping section of the `config.xml` file
       ```sql theme={null}
       CREATE ROLE scientists_role;
       ```

    2. Grant needed privileges to the role. The following statement grants admin privileges to any user able to authenticate through LDAP:
       ```sql theme={null}
       GRANT ALL ON *.* TO scientists_role;
       ```
  </Step>

  <Step>
    <h2 id="3-test-the-ldap-configuration">
      Test the LDAP configuration
    </h2>

    1. Login using the ClickHouse client
       ```bash theme={null}
       $ clickhouse-client --user einstein --password password
       ClickHouse client version 22.2.2.1.
       Connecting to localhost:9000 as user einstein.
       Connected to ClickHouse server version 22.2.2 revision 54455.

       chnode1 :)
       ```

    <Note>
      Use the `ldapsearch` command in step 1 to view all of the users available in the directory and for all of the users the password is `password`
    </Note>

    2. Test that the user was mapped correctly to the `scientists_role` role and has admin permissions

       ```sql theme={null}
       SHOW DATABASES
       ```

       ```response theme={null}
       Query id: 93b785ff-1482-4eda-95b0-b2d68b2c5e0f

       ┌─name───────────────┐
       │ INFORMATION_SCHEMA │
       │ db1_mysql          │
       │ db2                │
       │ db3                │
       │ db4_mysql          │
       │ db5_merge          │
       │ default            │
       │ information_schema │
       │ system             │
       └────────────────────┘

       9 rows in set. Elapsed: 0.004 sec.
       ```
  </Step>
</Steps>

<h2 id="summary">
  Summary
</h2>

This article demonstrated the basics of configuring ClickHouse to authenticate to an LDAP server and also to map to a role.  There are also options for configuring individual users in ClickHouse but having those users be authenticated by LDAP without configuring automated role mapping. The LDAP module can also be used to connect to Active Directory.