Blog

Integrating ClickHouse with LDAP (Part Two)

Earlier this month we published our first blog article about ClickHouse LDAP integration, an important step forward in ensuring proper security for accounts. We looked at how existing ClickHouse users can be switched to authenticate using LDAP server instead of explicitly specifying passwords in the XML configuration files.

In this article we will continue looking at how LDAP can be used with ClickHouse. We will now discuss how to take LDAP integration to the next level. We will use LDAP server as an external user directory. This will allow us to avoid defining users on the ClickHouse server but instead configure ClickHouse to look up and authenticate any users that are specified only on the LDAP server. Once we’ll get that working we will see how we can map LDAP user groups to RBAC roles to dynamically control access management for all the LDAP defined users.

This is fun, so let’s get started!

Setup

We will use exactly the same setup as in part one so if you want to follow along please setup your testing environment and then make sure that sanity checks pass. Finally, add LDAP server configuration and check that configuration has been successfully applied.

LDAP User Roles

Before we configure LDAP external directory we need to define one or more RBAC roles that will be assigned to our LDAP users. If we don’t do this then LDAP users will not have any privileges. So let’s create a separate role for our LDAP users.

docker-compose exec clickhouse1 bash -c 'clickhouse client -q "CREATE ROLE ldap_user_role"'

Check that role was created.

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

Now let’s just grant all privileges to the ldap_user_role to make our testing easier.

docker-compose exec clickhouse1 bash -c 'clickhouse client -q "GRANT ALL ON *.* TO ldap_user_role"'

And check that grant succeded.

docker-compose exec clickhouse1 bash -c 'clickhouse client -q "SHOW ACCESS" | grep ldap_user_role'
CREATE ROLE ldap_user_role
GRANT ALL ON *.* TO ldap_user_role

Looks good. Now we can proceed to enable the LDAP external user directory.

Using LDAP External User Directory

With docker-compose environment up and running and LDAP server configured in ClickHouse, we can tell ClickHouse to use LDAP server as an external user directory by defining <ldap> section in the <yandex><user_directories> of the config.xml.

However, to use best practices, we will not modify config.xml directly but instead add a separate configuration file to the /etc/clickhouse-server/config.d directory.

Note that if you have just finished working with part one then you need to remove ldapuser.xml configuration file so that it does not conflict with LDAP external directory that we will set up.

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

Then make sure we can’t login using ldapuser.

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

As expected, the login fails.

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

Now we are ready to add the LDAP external user directory.

docker-compose exec clickhouse1 bash -c 'cat <<HEREDOC > /etc/clickhouse-server/config.d/ldap_external_user_directory.xml
<?xml version="1.0" encoding="utf-8"?>
<yandex>
    <!--LDAP external user directory da296a1c-7874-11eb-998e-ddba30bbed5d -->
    <user_directories>
        <ldap>
            <server>openldap1</server>
            <roles>
                <ldap_user_role/>
            </roles>
        </ldap>
    </user_directories>
</yandex>
HEREDOC'

Note that in the <roles> section we have specified the roles to be assigned to all LDAP users. In this case we only have one ldap_user_role role but you can add as many as you need. These roles will be always assigned and must exist during the authentication attempt. If one of the roles does not exist, then LDAP users will not be able to authenticate until the missing role is added and you will get a message similar to the following.

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

Now restart ClickHouse server for the user directory configuration to be applied. Server restart is required to add or remove external user directories.

docker-compose restart clickhouse1
Restarting docker-compose_clickhouse1_1 ... done

Let’s check that configuration has been merged.

docker-compose exec clickhouse1 bash -c 'cat /var/lib/clickhouse/preprocessed_configs/config.xml | grep da296a1c-7874-11eb-998e-ddba30bbed5d'
    <!--LDAP external user directory da296a1c-7874-11eb-998e-ddba30bbed5d -->

On our openldap1 server we have ldapuser with the password set to be the same as the username. So let’s try to login to ClickHouse using this user.

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

It works, and we could login with an LDAP defined user. Now let’s dynamically add another user to our LDAP server and test that we can also login with it without touching any configuration files.

docker-compose exec openldap1 bash -c 'echo -e "dn: cn=new_ldap_user,ou=users,dc=company,dc=com
cn: new_ldap_user
gidnumber: 501
givenname: John
homedirectory: /home/users
objectclass: inetOrgPerson
objectclass: posixAccount
objectclass: top
sn: User
uid: new_ldap_user
uidnumber: 2000
userpassword: new_ldap_user" | ldapadd -x -H ldap://localhost -D "cn=admin,dc=company,dc=com" -w admin'

You should see the following message that confirms that a new user entry was added to LDAP.

adding new entry "cn=new_ldap_user,ou=users,dc=company,dc=com"

Now let’s try to login with this new LDAP user.

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

It worked and we could successfully authenticate using a dynamically added LDAP user. Now let’s dynamically remove the new user from LDAP.

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

Try to login with the removed LDAP user and you should see that login fails as expected.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -n --user "new_ldap_user" --password "new_ldap_user" -q "SELECT user()"'
Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: new_ldap_user: Authentication failed: password is incorrect or there is no user with such name. 

Enabling LDAP User Group Mapping To RBAC Roles

We have mentioned before that when LDAP users login they get assigned the roles that we have specified in the LDAP external user directory configuration. In our case we have only assigned one role which is the ldap_user_role. Let’s see what roles are assigned to our original ldapuser when we try to login to confirm it.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SHOW ROLES"'
ldap_user_role

As you can see the ldapuser is indeed assigned the ldap_user_role and it is the only role that it has. You can’t add any other roles to the LDAP users, instead, you can only grant or revoke privileges from one of the roles specified in the configuration. Statically assigned roles can work in some cases but given that we are using LDAP we not only want to manage users using the LDAP but also user roles. Typically LDAP users will belong to one or more LDAP groups so wouldn’t be nice to assign RBAC roles based on LDAP groups to which LDAP user belongs? Indeed, it would be nice and role mapping does exactly that.

We can enable role mapping by adding <role_mapping> section to the LDAP external user directory definition.

docker-compose exec clickhouse1 bash -c 'cat <<HEREDOC > /etc/clickhouse-server/config.d/ldap_external_user_directory.xml
<?xml version="1.0" encoding="utf-8"?>
<yandex>
    <!--LDAP external user directory da296a1c-7874-11eb-998e-ddba30bbed5d -->
    <user_directories>
        <ldap>
            <server>openldap1</server>
            <roles>
                <ldap_user_role/>
            </roles>
            <role_mapping>
                <base_dn>ou=groups,dc=example,dc=com</base_dn>
                <scope>subtree</scope>
                <search_filter>(&amp;(objectClass=groupOfNames)(member={bind_dn}))</search_filter>
                <attribute>cn</attribute>
                <prefix>clickhouse_</prefix>
            </role_mapping>
        </ldap>
    </user_directories>
</yandex>
HEREDOC'

The <role_mapping> section above defines parameters used to execute an LDAP search using the LDAP user distinguished name. In the above configuration we are saying that we want to find all objects with the base distinguished name ou=groups,dc=example,dc=com, with the scope of the search being subtree, the search filter set to (&amp;(objectClass=groupOfNames)(member={bind_dn})), the attribute that we will use to map to the RBAC role is the value of the cn and the attribute value must have prefix clickhouse_.

This is a little bit complicated but what we are essentially saying with this configuration is that we just want to map LDAP group names to which LDAP user is assigned that start with clickhouse_ prefix to RBAC roles. If you are confused by these options then it is best to talk to your LDAP admin and they should be able to provide the correct values for your organization. Again the options above are used by ClickHouse to perform LDAP search and as you can see they provide flexibility to find and use different objects and their attribute to map to RBAC roles.

We also must note that in our XML configuration the ampersand & symbol needs to be escaped. Fortunately, XML has only a few characters that need to be escaped so it is easy to keep an eye for them. Here is the complete list.

  • "&quot;
  • '&apos;
  • <&lt;
  • >&gt;
  • &&amp;

Now let’s restart ClickHouse server and enable role mapping.

docker-compose restart clickhouse1

We can now add a new LDAP group and assign it to the ldapuser that we already have. Then we need to add a corresponding RBAC role that will be mapped. So let’s create an LDAP role group with common name set to clickhouse_role1.

docker-compose exec openldap1 bash -c 'echo -e "dn: cn=clickhouse_role1,ou=groups,dc=company,dc=com
objectclass: top
objectclass: groupOfUniqueNames
uniquemember: cn=admin,dc=company,dc=com" | ldapadd -x -H ldap://localhost -D "cn=admin,dc=company,dc=com" -w admin'

You should see the message confirming that our new entry was added to LDAP.

adding new entry "cn=clickhouse_role1,ou=groups,dc=company,dc=com"

Now add ldapuser to this group.

docker-compose exec openldap1 bash -c 'echo -e "dn: cn=clickhouse_role1,ou=groups,dc=company,dc=com
changetype: modify
add: uniquemember
uniquemember: cn=ldapuser,ou=users,dc=company,dc=com" | ldapmodify -x -H ldap://localhost -D "cn=admin,dc=company,dc=com" -w admin'

You should get a confirmation that LDAP group entry has been modified.

modifying entry "cn=clickhouse_role1,ou=groups,dc=company,dc=com"

Now all that is left to do is login with ldapuser and check what roles it now has.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SHOW ROLES"'

We see that we have the following:

ldap_user_role

Nothing changed and we only see the statically assigned role. Oops, what we have forgotten to do is to add a corresponding RBAC role! Easy to fix.

docker-compose exec clickhouse1 bash -c 'clickhouse client -q "CREATE ROLE clickhouse_role1"'

We give it another try.

docker-compose exec clickhouse1 bash -c 'clickhouse-client -n --user "ldapuser" --password "ldapuser" -q "SHOW ROLES"'

You should see that now ldapuser has two roles the statically assigned ldap_user_role and the mapped clickhouse_role1.

clickhouse_role1
ldap_user_role

This shows the basic behavior of how we can map LDAP groups to RBAC roles. Go ahead and add more LDAP groups and RBAC roles and play around with them.

Conclusion

It is been fun to play around with how ClickHouse can now be integrated with LDAP. We have seen that once we add the LDAP server definition to the ClickHouse configuration we can either authenticate existing ClickHouse users or enable LDAP external user directory to completely manage ClickHouse users using LDAP server. We also looked at how LDAP users can be assigned static RBAC roles or using role mapping can map LDAP groups to RBAC roles.

All in all, we are now able to move away from defining passwords and users inside XML configuration files and finally large organizations can integrate their ClickHouse installations with the rest of their services that use LDAP. So if your organization is using LDAP then go to talk to your LDAP admin and start managing all your ClickHouse users using LDAP. Your admins and security department will certainly appreciate this.

If you have more questions about ClickHouse security, don’t hesitate to contact us at Altinity. Our team loves working on security topics and would be delighted to help you.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.