Integrating ClickHouse with LDAP (Part One)

ClickHouse has traditionally used XML configuration files to define server configuration including all database users. The users.xml configuration file or separate configuration files inside the /etc/clickhouse-server/users.d directory define users and properties associated with them such as profiles, network restrictions, quotas, and passwords. This approach works well for small organizations and installations but makes it hard to administer ClickHouse clusters in large organizations and installations. There are three main problems with the user configuration files. First, administrators must edit these configuration files by hand each time a new user has to be added, modified, or removed. Second, it suffers from the security burden of having passwords or their hashes stored in the configuration files. Third, assigning and managing user roles is limited and cumbersome.

ClickHouse can now address all three problems. With the introduction of support for RBAC, ClickHouse allows managing users and roles using SQL statements. There is no need to change configuration files to manage users and user roles. However, even with RBAC, you still need to manage ClickHouse users and roles separately from the rest of your organization’s services, and passwords are still stored on the file system.

Altinity has been working since mid-2020 to add ClickHouse support for Lightweight Directory Access Protocol, also known as LDAP. LDAP centralizes storage of usernames and passwords as well as their roles within an organization, which makes it possible to manage users and privileges from a single location. We’re happy to report that ClickHouse LDAP authentication and user directory support is merged, stable, and ready for general use. We have tested it carefully with the popular OpenLDAP package, available in most Linux distributions.

In this series of blog articles, we will describe how the new support for LDAP integration can help medium and large organizations integrate the management of ClickHouse users and roles with their existing services. We will describe different levels of LDAP integration with ClickHouse, starting with authenticating existing ClickHouse users using LDAP to fully integrating LDAP using external user directory and mapping selected LDAP groups to RBAC roles.

Testing Environment

We will use a docker-compose cluster to show how LDAP can be integrated with ClickHouse. You can find the docker-compose environment at https://gitlab.com/altinity-public/blogs/ldap-integration-with-clickhouse that will use yandex/clickhouse-server:21.1.2.15 image for our ClickHouse containers.

To use this environment, you will need git, docker, and docker-compose installed on your system. Then you can clone the repository that contains the test environment to your local system.

git clone https://gitlab.com/altinity-public/blogs/ldap-integration-with-clickhouse.git

Check that you can bring up the docker-compose cluster.

cd  ldap-integration-with-clickhouse
cd docker-compose/
docker-compose up -d

Creating network "docker-compose_default" with the default driver
Creating docker-compose_openldap1_1 ... done
Creating docker-compose_zookeeper_1 ... done
Creating docker-compose_clickhouse1_1 ... done
Creating docker-compose_clickhouse3_1 ... done
Creating docker-compose_clickhouse2_1 ... done
Creating phpldapadmin                 ... done
Creating docker-compose_all_services_ready_1 ... done

If everything goes well, you should see the messages above that indicate that openldap1, zookeeper, clickhouse1, clickhouse2, and clickhouse3, as well as phpldapadmin services, are up and running. We can also check that all services are healthy.

Note that you must execute all docker-compose commands inside the docker-compose folder.

docker-compose ps

               Name                              Command                  State                   Ports            
-------------------------------------------------------------------------------------------------------------------
docker-compose_all_services_ready_1   /hello                           Exit 0                                      
docker-compose_clickhouse1_1          bash -c clickhouse server  ...   Up (healthy)   8123/tcp, 9000/tcp, 9009/tcp 
docker-compose_clickhouse2_1          bash -c clickhouse server  ...   Up (healthy)   8123/tcp, 9000/tcp, 9009/tcp 
docker-compose_clickhouse3_1          bash -c clickhouse server  ...   Up (healthy)   8123/tcp, 9000/tcp, 9009/tcp 
docker-compose_openldap1_1            /container/tool/run --copy ...   Up (healthy)   389/tcp, 636/tcp             
docker-compose_zookeeper_1            /docker-entrypoint.sh zkSe ...   Up (healthy)   2181/tcp, 2888/tcp, 3888/tcp 
phpldapadmin                          /container/tool/run              Up (healthy)   443/tcp, 0.0.0.0:8080->80/tcp

Sanity Checks

Let’s do some necessary sanity checks before we proceed. First, make sure our OpenLDAP server is up and running. We can perform a search operation to see what users we currently have defined on the LDAP server.

Again, note that you must execute all docker-compose commands inside the docker-compose folder.

docker-compose exec openldap1 bash -c 'ldapsearch -x -H ldap://localhost -b "ou=users,dc=company,dc=com" -D "cn=admin,dc=company,dc=com" -w admin'

# extended LDIF
#
# LDAPv3
# base <ou=users,dc=company,dc=com> with scope subtree
# filter: (objectclass=*)
# requesting: ALL
#

# users, company.com
dn: ou=users,dc=company,dc=com
objectClass: organizationalUnit
objectClass: top
ou: users

# ldapuser, users, company.com
dn: cn=ldapuser,ou=users,dc=company,dc=com
cn: ldapuser
gidNumber: 501
givenName: John
homeDirectory: /home/users/ldapuser
objectClass: inetOrgPerson
objectClass: posixAccount
objectClass: top
sn: User
uid: ldapuser
uidNumber: 1002
userPassword:: bGRhcHVzZXI=

# search result
search: 2
result: 0 Success

# numResponses: 3
# numEntries: 2

We have one user cn=ldapuser,ou=users,dc=company,dc=com already defined. The entry says that the username is ldapuser and, for testing purposes only, the user has the password set to be the same as the username.

Second, make sure ClickHouse services are ready to execute queries.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -q "SELECT version()"'

docker-compose exec clickhouse2 bash -c 'clickhouse-client -q "SELECT version()"'

docker-compose exec clickhouse3 bash -c 'clickhouse-client -q "SELECT version()"'

Configuring LDAP Server In ClickHouse

Before we can play around with different levels of integrating LDAP with ClickHouse, we need to let ClickHouse know what LDAP server is available and how we can connect to it. We can also add multiple LDAP servers. To keep it simple, we will only use the one provided in our docker-compose cluster by the openldap1 service. We need to add the following configuration file to the /etc/clickhouse-server/config.d directory to add an LDAP server.

docker-compose exec clickhouse1 bash -c 'cat <<HEREDOC > /etc/clickhouse-server/config.d/ldap_servers.xml
<?xml version="1.0" encoding="utf-8"?>
<yandex>
  <ldap_servers>
    <!--LDAP servers b9f1f80c_6598_11eb_80c1_39d7fbdc1e26-->
    <openldap1>
      <host>openldap1</host>
      <port>636</port>
      <enable_tls>yes</enable_tls>
      <auth_dn_prefix>cn=</auth_dn_prefix>
      <auth_dn_suffix>,ou=users,dc=company,dc=com</auth_dn_suffix>
      <tls_require_cert>never</tls_require_cert>
    </openldap1>
  </ldap_servers>
</yandex>
HEREDOC'

Check that our ldap_servers.xml config got merged into the preprocessed config.xml.

docker-compose exec clickhouse1 bash -c 'cat /var/lib/clickhouse/preprocessed_configs/config.xml | grep LDAP'

    <!--LDAP servers b9f1f80c_6598_11eb_80c1_39d7fbdc1e26-->

Our unique identification string is inside the preprocessed config, and therefore our config has been applied successfully.

Authenticating Existing ClickHouse Users Using LDAP

The first level of integration with LDAP that is provided by ClickHouse is to allow authentication of existing ClickHouse users using the LDAP server. It will enable us not to specify passwords for the users explicitly, but instead, we’ll let ClickHouse contact the LDAP server to validate user credentials.

Defining LDAP Authenticated Users In The users.xml

We can define LDAP authenticated user by adding a configuration file for the user to the /etc/clickhouse-server/users.d directory. We do not need to specify the password for the user, but instead, we must add the <ldap> section that will define the LDAP <server> to be used for authentications.

docker-compose exec clickhouse1 bash -c 'cat <<HEREDOC > /etc/clickhouse-server/users.d/ldapuser.xml
<?xml version="1.0" encoding="utf-8"?>
<yandex>
  <users>
    <!--LDAP users bb6f3d71_6598_11eb_80c1_39d7fbdc1e26-->
    <ldapuser>
      <ldap>
        <server>openldap1</server>
      </ldap>
    </ldapuser>
  </users>
</yandex>
HEREDOC'

Confirm that our config is merged into the preprocessed users.xml.

$ docker-compose exec clickhouse1 bash -c 'cat /var/lib/clickhouse/preprocessed_configs/users.xml | grep bb6f3d71_6598_11eb_80c1_39d7fbdc1e26'

    <!--LDAP users bb6f3d71_6598_11eb_80c1_39d7fbdc1e26-->

We can now try to execute a query using the ldapuser user.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SELECT user()"'

ldapuser

It works! Now provide a wrong password to see it fail.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser2" -q "SELECT user()"'

Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: ldapuser: Authentication failed: password is incorrect or there is no user with such name.

Delete the configuration file for the user, and let’s use RBAC commands instead.

docker-compose exec clickhouse1 bash -c 'rm -rf /etc/clickhouse-server/users.d/ldapuser.xml'

Defining LDAP Authenticated Users Using RBAC

Instead of defining users using XML configuration files, we can do the same in a much easier way using RBAC commands. Using RBAC commands is recommended because you can use the ON CLUSTER clause to create or drop users in the whole cluster.

The syntax for the CREATE USER command is defined as follows.

CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1] 
        [, name2 [ON CLUSTER cluster_name2] ...]
    [IDENTIFIED [WITH {NO_PASSWORD|PLAINTEXT_PASSWORD|SHA256_PASSWORD|SHA256_HASH|DOUBLE_SHA1_PASSWORD|DOUBLE_SHA1_HASH}] BY {'password'|'hash'}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

And now you can also use IDENTIFIED WITH LDAP_SERVER clause to authenticate a user using an LDAP server.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -q "CREATE USER ldapuser IDENTIFIED WITH LDAP_SERVER BY '\''openldap1'\''"'

Check that the user was created.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -q "SHOW USERS"'

default
ldapuser

Finally, execute the query using the ldapuser user.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SELECT user()"'

ldapuser

Let’s see it fail if the password is invalid.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser2" -q "SELECT user()"'

Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: ldapuser: Authentication failed: password is incorrect or there is no user with such name.

Defining LDAP Authenticated Users Using RBAC Across The Cluster

The RBAC commands allow to create or drop users on the cluster. Let’s add LDAP server configuration to the other two ClickHouse nodes, clickhouse2 and clickhouse3.

First, add LDAP server configuration to clickhouse2.

docker-compose exec clickhouse2 bash -c 'cat <<HEREDOC > /etc/clickhouse-server/config.d/ldap_servers.xml
<?xml version="1.0" encoding="utf-8"?>
<yandex>
  <ldap_servers>
    <!--LDAP servers b9f1f80c_6598_11eb_80c1_39d7fbdc1e26-->
    <openldap1>
      <host>openldap1</host>
      <port>636</port>
      <enable_tls>yes</enable_tls>
      <auth_dn_prefix>cn=</auth_dn_prefix>
      <auth_dn_suffix>,ou=users,dc=company,dc=com</auth_dn_suffix>
      <tls_require_cert>never</tls_require_cert>
    </openldap1>
  </ldap_servers>
</yandex>
HEREDOC'

Let’s do the same for clickhouse3.

docker-compose exec clickhouse3 bash -c 'cat <<HEREDOC > /etc/clickhouse-server/config.d/ldap_servers.xml
<?xml version="1.0" encoding="utf-8"?>
<yandex>
  <ldap_servers>
    <!--LDAP servers b9f1f80c_6598_11eb_80c1_39d7fbdc1e26-->
    <openldap1>
      <host>openldap1</host>
      <port>636</port>
      <enable_tls>yes</enable_tls>
      <auth_dn_prefix>cn=</auth_dn_prefix>
      <auth_dn_suffix>,ou=users,dc=company,dc=com</auth_dn_suffix>
      <tls_require_cert>never</tls_require_cert>
    </openldap1>
  </ldap_servers>
</yandex>
HEREDOC'

Now we can create ldapuser on the cluster by adding the ON CLUSTER clause to our CREATE USER command.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -q "CREATE USER IF NOT EXISTS ldapuser IDENTIFIED WITH LDAP_SERVER BY '\''openldap1'\'' ON CLUSTER '\''replicated_cluster'\''"'

clickhouse2	9000	0		2	0
clickhouse1	9000	0		1	0
clickhouse3	9000	0		0	0

Now we can execute commands as ldapuser on each cluster node.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SELECT user()"'

docker-compose exec clickhouse2 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SELECT user()"'

docker-compose exec clickhouse3 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SELECT user()"'

Optimizing Login of LDAP Authenticated Users

When users are authenticated using the LDAP server, ClickHouse must contact the LDAP server for each user login. This might not be optimal when a lot of users are being authenticated. To address this problem we can use the <verification_cooldown> parameter in the LDAP server configuration that specifies a period of time, in seconds, after a successful bind attempt, during which the LDAP user will be assumed to be successfully authenticated for all consecutive requests without contacting the LDAP server. By default, this parameter is set to 0 to disable caching and force contacting the LDAP server for each authentication request.

Right now, caching is disabled so let’s measure our baseline performance on the clickhouse1.

docker-compose exec clickhouse1 bash -c 'time for n in {1..1000}; do clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SELECT 1" > /dev/null; done'

real  0m30.189s
user  0m14.492s
sys   0m9.847s

Let’s change how we define the LDAP server on the clickhouse1 node to cache LDAP user authentications for five minutes.

docker-compose exec clickhouse1 bash -c 'cat <<HEREDOC > /etc/clickhouse-server/config.d/ldap_servers.xml
<?xml version="1.0" encoding="utf-8"?>
<yandex>
  <ldap_servers>
    <!--LDAP servers b9f1f80c_6598_11eb_80c1_39d7fbdc1e26-->
    <openldap1>
      <host>openldap1</host>
      <port>636</port>
      <enable_tls>yes</enable_tls>
      <verification_cooldown>300</verification_cooldown>
      <auth_dn_prefix>cn=</auth_dn_prefix>
      <auth_dn_suffix>,ou=users,dc=company,dc=com</auth_dn_suffix>
      <tls_require_cert>never</tls_require_cert>
    </openldap1>
  </ldap_servers>
</yandex>
HEREDOC'

Here are the new numbers when caching is enabled.

docker-compose exec clickhouse1 bash -c 'time for n in {1..1000}; do clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SELECT 1" > /dev/null; done'

real  0m22.472s
user  0m12.000s
sys   0m8.894s

As we can see, we get about 26% improvement in login performance as we minimize the overhead due to repeated requests to the LDAP server.

Conclusion

In this article, we have introduced support for LDAP integration with ClickHouse. We have looked at how an LDAP server definition can be added to the ClickHouse configuration. We have also looked at the simplest case where we can use LDAP server to authenticated ClickHouse defined users either using an XML configuration file or RBAC commands. The RBAC commands provide support for the ON CLUSTER clause that helps create or drop users on the cluster without the need to modify configuration files on each cluster node. Finally, we have looked at how we can enable caching of LDAP user authentications to optimize repeated logins.

Stay tuned for the next part in this series, where we will talk about how we can use an LDAP server as an external user directory that eliminates the need to define users on the ClickHouse server explicitly. If you have further questions or want to discuss your security requirements, feel free to contact us at info@altinity.com. We’ll be glad to help!