User Management in ClickHouse® Databases: The Unabridged Edition

Recorded: August 21 @ 08:00 am PDT
Presenter: Alexander Zaitsev, CTO and co-founder @Altinity, and Robert Hodges, CEO @Altinity

In this webinar, Robert Hodges (CEO, Altinity) and Alexander Zaitsev (CTO, Altinity) provide a thorough walkthrough of user management in ClickHouse® databases. The session is aimed at developers encountering ClickHouse user management for the first time, as well as DBAs managing production clusters.

The webinar begins with a developer-focused “self-defense” section covering the default user, XML-based user definitions stored in users.d, SHA-256 password generation, the simpler CREATE USER SQL command, network masks, settings profiles, and the ON CLUSTER keyword. It then digs into the full RBAC model: the relationship between users, roles, privileges, quotas, and settings profiles, illustrated through a worked example that builds up a read-only role with resource limits and grants. Row policies are covered in depth as a mechanism for row-level data access control in multi-tenant systems, including the permissive policy pattern and wildcard table assignments.

The session then compares XML users and RBAC side by side, explaining when each is preferable, and moves to cluster user management. Two approaches are covered: the ON CLUSTER command and the superior replicated user directory approach that stores RBAC definitions in ClickHouse Keeper so that new nodes automatically receive all user configurations. Additional authentication mechanisms, including LDAP, mTLS with certificates, and Kerberos, are covered as options for centralized and enterprise authentication needs. The final section addresses Kubernetes-specific patterns: defining XML users in the ClickHouseInstallation YAML resource, using Kubernetes Secrets for hashed passwords, and embedding raw XML profile definitions. The session closes with tips on system tables for user management introspection, password complexity rules in config.xml, and known gaps in the current implementation such as the Merge engine ignoring row policies.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 00:01 – Welcome and introduction to the webinar topic
• 00:26 – Speaker introductions: Robert Hodges and Alexander Zaitsev
• 00:58 – About Altinity: Altinity.Cloud, Altinity Kubernetes Operator for ClickHouse®, Altinity Stable® Builds
• 01:39 – Quick overview of ClickHouse
• 03:21 – Developer self-defense: the default user and first login
• 04:27 – XML-based user definitions in users.d
• 06:24 – Generating SHA-256 passwords and the simpler CREATE USER command
• 07:07 – What RBAC means in ClickHouse context
• 07:43 – Where user definitions are stored: users.d vs. /var/lib/clickhouse/access
• 09:20 – Network masks and authentication failures
• 11:44 – Settings profiles
• 12:54 – Creating users on clusters with ON CLUSTER
• 14:34 – The RBAC entity model: users, roles, privileges, quotas, profiles
• 16:30 – Worked example: building a read-only role with resource limits
• 21:07 – Row policies: row-level access control for multi-tenant systems
• 24:09 – XML users vs. RBAC: trade-off comparison
• 27:47 – Cluster user management: ON CLUSTER vs. replicated user directories
• 30:56 – Additional authentication: LDAP, mTLS, Kerberos
• 33:19 – Kubernetes integration: users in ClickHouseInstallation YAML
• 36:55 – Kubernetes Secrets for passwords
• 37:45 – Tricks for management: system tables, SHOW statements
• 39:25 – Password complexity rules in config.xml
• 40:47 – Access control settings and row policy loopholes
• 41:35 – Summary and takeaways
• 47:00 – Closing remarks and resources

Webinar Transcript

[00:01] – Welcome and Introduction

Robert: Hi everyone, welcome to our webinar on user management in ClickHouse® databases. We’re calling it the Unabridged Edition because in this presentation we’ll be shooting for a happy medium between things that are too basic and complete confusion. There’s a very rich model for user management in ClickHouse, and we’ll try to give you a tour of the major parts of it so you can see the whole picture.

[00:26] – Speaker Introductions

Robert: I’m Robert Hodges. I’ve been working with databases for over 40 years, starting with pre-relational databases, and working on ClickHouse for about six years. I have with me Alexander Zaitsev, co-founder of Altinity, CTO, and someone who has been working on making large systems work and on ClickHouse since 2017. He’s also the designer and architect of Altinity.Cloud.

Speaking of Altinity.Cloud, we are a complete service and software provider for ClickHouse. We wrote the Altinity Kubernetes Operator for ClickHouse®, which some of you on this call are probably using to run ClickHouse in Kubernetes. We have Altinity Stable® Builds for ClickHouse®, our own open-source builds of ClickHouse that offer three years of maintenance, and we do a bunch of other open-source projects including many contributions to ClickHouse itself.

[01:39] – Quick Overview of ClickHouse

Robert: Just to level-set very quickly: ClickHouse is a real-time analytic database. It gives you very quick responses on data sets that can be very large and where data is arriving very quickly, often millions of rows per second. You can think of it as the love child of MySQL, an open-source database that runs practically anywhere with an Apache 2.0 license, and a traditional analytic database. From the analytic side, it has a shared-nothing architecture, stores data in columns with very high compression rates, and is extremely good at parallelizing scans. As a result it’s become extremely popular, with over 30,000 GitHub stars and hundreds of contributors submitting PRs each year.

[03:21] – Developer Self-Defense: The Default User and First Login

Robert: Let’s start by pretending we’re developers bringing up ClickHouse for the first time, just needing to get some work done. When you first install ClickHouse on a Linux machine, you can just run clickhouse-client and it will connect you to ClickHouse. What happened there? You’re using the default user, which is literally called default, and it has no password. Any newly installed developer version of ClickHouse is going to be essentially unprotected, with no additional users. Protecting this user and making users appropriate for your application is something we have to do ourselves. Fortunately, it’s pretty easy.

[04:27] – XML-Based User Definitions

Robert: It used to be, and some of you who have used ClickHouse for a while know this, that you would have to make a little XML file and stick it under /etc/clickhouse-server/users.d to define a new user. Here’s how that XML format works. There’s a clickhouse root tag, a users section, and the tags that follow are the names of actual users. For a user called xml_user, we can see various settings: access_management says this user can issue RBAC commands, a network mask restricts it to localhost, a SHA-256 hashed password is stored, and then there are references to profiles and quotas that we’ll discuss later. You put this file inside users.d and within a few seconds ClickHouse notices it and you’ve got a new account.

One confusing thing is how to generate the passwords. If you look inside the example users.xml file, it gives you a command: create a random password and run it through sha256sum. That second value is what gets stored in the XML file. This is a little confusing and kind of painful.

[06:24] – The Simpler CREATE USER Command and RBAC

Robert: There’s a better way to do it: the CREATE USER SQL command. This is a SQL command that generates a new login. A simple example creates a user identified by the password topsecret, and ClickHouse will take care of doing the SHA-256 hash for you. It’s restricted to localhost and uses the default settings profile. This is an example of what we call role-based access control (RBAC). When we say RBAC commands in the context of ClickHouse, we mean SQL commands to create and manage users. It’s much easier. In older versions of ClickHouse you had to create a special user that could issue these commands, but that’s no longer necessary. These commands just work out of the box from the default account.

[07:43] – Where User Definitions Are Stored

Robert: XML users are stored in users.d on the file system. The default user and default profile are stored in a file called users.xml. You generally don’t want to change that file because it gets overwritten in new releases. When you create users or other entities like quotas and profiles, you create little files and stick them under users.d. The config.xml file has settings that tell ClickHouse where to find user definitions and set standards for defining passwords.

The other place ClickHouse stores users by default is /var/lib/clickhouse/access. When you execute a SQL RBAC command like CREATE USER, it sticks a file in this directory with a long UUID as part of the name. Unless something is broken, you never need to mess with it directly, but you need to be aware these things are on the file system and need to be protected properly from outside access.

[09:20] – Network Masks and Authentication Failures

Robert: One really important feature of ClickHouse users is network masks, which restrict access to particular hosts or network interfaces. Here are three examples using RBAC commands. The first allows logins from any IP address. The second allows logins only from localhost. The third restricts using a CIDR address to a particular network. There are also regex expressions on host names. These are all documented in users.xml and in the ClickHouse docs.

I bring this up because as a developer, one of the first problems you may run into is getting this wrong and seeing a somewhat vanilla authentication failure message. This network mask is a common reason people run into problems, especially when doing port forwarding in Kubernetes or other setups where things get tweaked.

A live question: can you change the host IP with ALTER USER later on? Yes, absolutely. One of the really nice things about CREATE USER is you can go ahead and alter this stuff at any time. RBAC enables that.

[11:44] – Settings Profiles

Robert: ClickHouse comes with a couple of default profiles, but they don’t have anything in them. Profiles are really important because they are where you can set values that will be your default settings when running commands in SQL. For example, a settings profile might say we’re going to log all queries so every time a query is executed by a user on this profile it goes into the query log. Another setting might force the FINAL keyword on all queries, which is really important when dealing with things like ReplacingMergeTree. When you have settings you want to apply to users, the profile setting is where you do it. It’s a very powerful mechanism.

[12:54] – Creating Users on Clusters with ON CLUSTER

Robert: One really important thing to understand from the beginning is how to create users on clusters. The RBAC commands I’ve shown you only apply to the host they’re running on. In a vanilla ClickHouse installation, if you’ve set up a cluster and want commands to run over the cluster so users get set up on all servers, you have to use the ON CLUSTER keyword. Every single RBAC command supports this. You would say CREATE USER ... ON CLUSTER '{cluster}' where {cluster} is a macro whose value is read from macros.xml. If you’re running on Kubernetes with the Altinity operator, this value is set for you automatically. If you’re running it yourself, you need to set this up. This command will then work regardless of what the cluster name is.

[14:34] – The Full RBAC Entity Model

Robert: Let’s dig into the RBAC model more closely. Here’s an entity-relationship diagram showing the different entities. Users can have an associated quota, which provides limits so they can’t hog all the system’s resources. They also have settings profiles. In the RBAC model, users can be granted privileges, for example GRANT SELECT, which says this user can select data from tables in a particular database or a single table. Giving those individually to every user is really inconvenient when you might have hundreds of users, which is why SQL has had roles for many years. A role is a template that has privileges, a settings profile, and a quota associated with it. You then grant that role to particular users and it applies to them as if everything had been granted directly.

[16:30] – Worked Example: Building a Read-Only Role with Resource Limits

Robert: Let’s work through a concrete example, building from left to right and ending with a user that has a profile, quota, and useful privileges.

Start with the settings profile. We’re going to limit max threads to a default of two with a range of one to four, and put a memory usage limit in. There’s a wide variety of interesting properties you can set. This will also be a read-only profile where users can’t do any updates.

Then create a role that uses that profile. We’ll call it yt_role. The role doesn’t do anything by itself yet, but it has this profile associated with it. Now we give it some privileges. The standard approach is to start with REVOKE ALL FROM yt_role, good hygiene to make sure nothing unexpected has crept in, then grant this user the ability to see tables in the system database and run selects on system tables in the system and default databases. Users with this role won’t be able to see or query other databases like foo.

Now let’s add a quota to the role to ensure it doesn’t hog resources. We want to limit it to five queries in a 30-second interval, and limit the maximum number of rows it can bring back. Quotas are very versatile: they can set all kinds of resource limits over a given time period. In practice, you’ll tend to have a role associated with a particular type of task, such as inserting data, and associate that role with a quota appropriate for inserts.

Finally, create the user. Roles are not logins. When you log into the system, you must have a user. The user is the thing that gets authenticated. The role is a template for granting privileges and associating quotas and profile values. A user can have multiple roles.

[21:07] – Row Policies: Row-Level Access Control for Multi-Tenant Systems

Robert: There’s another really interesting feature called row policies, which are very important for user management in large systems, particularly cases where you have many users from different groups and you want to give them differing levels of access to data.

Here’s a synthetic example. We have a table with a group column and a name column. We have a rule that user rp_user_2 can only see rows where group is less than 3. We don’t want separate tables for everybody, and we may have overlapping sets of data they can access. That’s what row policies do.

We create a few users and give them row policies on the table with differing permissions. For example, rp_user_2 can see every row where the group number is less than three. When they log in and do a SELECT, they simply see data where the group is one or two.

This is super flexible. You can have multiple row policies that apply to users. For example, you can set a row policy that applies to everybody, meaning if no other policy says otherwise you can only see rows where group is equal to one. This policy has a permissive tag, which means if there are other row policies that let you see more, ClickHouse will allow it. You can see how you can combine these, and yes, you can make yourself completely confused.

One important thing about row policies: they’re specific to single tables and single users, but you can use wildcards to assign policies to groups of tables within a database. This allows you to create a more template-oriented system.

There is a known bug worth mentioning: if you use the Merge table engine, it ignores row policies. This is a known issue being worked on. If you find security bugs like this, email them to Altinity or directly to the ClickHouse team. It’s really important to log this stuff so we don’t have holes.

[24:09] – XML Users vs. RBAC: Trade-Off Comparison

Robert: Sometimes DBAs or people who’ve used databases for a long time will say role-based access control is the only way to do things. That’s not entirely true, especially for developers. Let’s look at the trade-offs.

When you go back to XML users, the big difference is that the XML model doesn’t have roles. Roles are very powerful because they allow you to combine privileges in a template-like way and apply them to groups of users. That simply doesn’t exist in XML. What you have are users, quotas, and settings profiles, and you can grant privileges directly to users in the XML. You can even use database filter tags similar to row policies. For some simple cases this isn’t bad.

Why would you still use XML? The trade-off table is clear. XML is a less rich model. RBAC can do anything involving security or user management, with literally hundreds of privileges that can be assigned to roles. You can build full tenant separation in a single database. XML just doesn’t do that.

What’s great about XML is that if you are deploying using infrastructure as code, because XML users are file-based, they’ll fit into your development pipelines. It’s just harder to integrate RBAC that way. For ease of deployment it’s kind of a wash: XML requires touching the file system via Ansible, Kubernetes ConfigMaps, or similar, while RBAC can be done over the network.

A really important difference: XML has no cluster support. It’s all local to the server and file system where the files live. RBAC commands can create users, profiles, and so on across the entire cluster.

[27:47] – Cluster User Management: ON CLUSTER and Replicated User Directories

Robert: ClickHouse has the ON CLUSTER command, and as we showed earlier, every RBAC command supports this. The cluster name is a macro value pulled from macros.xml. But there’s a downside: if a new replica comes up, you have to run all these commands again every time you add a replica or shard to the system. The commands need CREATE ... IF NOT EXISTS to be idempotent, but it’s still painful.

There’s a better way: keeping the RBAC definitions in Keeper or ZooKeeper. You still have your XML users, but you can define what we call a replicated user directory inside Keeper. You go into config.xml and add a replicated tag inside user_directories with a ZooKeeper path. Whenever you execute a CREATE USER command, you no longer need the ON CLUSTER clause, and when new replicas and shards show up they will automatically get all RBAC commands replicated to them.

This means the commands become much simpler: just CREATE USER or CREATE ROLE without the ON CLUSTER. This is the preferred way to do it in most cluster deployments. We use this in Altinity.Cloud. The feature was implemented by the Yandex team and is a great feature that everyone should know. Unfortunately, it’s not well documented yet. The best documentation for it is still the original pull request where it was implemented. We intend to fix that with a blog article soon.

[30:56] – Additional Authentication: LDAP, mTLS, and Kerberos

Robert: There are a bunch of other ways to control users depending on your needs. If you need truly centralized management of users and roles, that can be handled through LDAP. It can simply be used for authentication and validate passwords for particular users, or it can serve as a full user directory where users and roles are defined and mapped to actual roles in your ClickHouse servers. This is super useful and is what you would use if you wanted to connect to Microsoft Active Directory, which has LDAP interfaces.

Another option at the authentication level is using certificates instead of passwords. ClickHouse supports mTLS, mutual TLS between client and server, at the transport layer. This makes sure that a certificate showing up is signed by a CA that the server recognizes and allows you to associate a specific certificate with a specific client. So you have full authentication and know who you’re dealing with because they must present a unique certificate. Documentation is available for this.

Finally, there’s Kerberos. Altinity implemented both LDAP and Kerberos support in ClickHouse. Kerberos is not as widely used but it’s available if you need it.

[33:19] – Kubernetes Integration: Users in the ClickHouseInstallation Resource

Robert: If you’re using Kubernetes and running ClickHouse, there’s a pretty good chance you’re using the Altinity Kubernetes Operator for ClickHouse®. Here’s a quick level-set: Kubernetes manages container-based applications through resources. The operator defines a resource called the ClickHouseInstallation. When you want to create a cluster, you submit a YAML file that contains the cluster definition. Kubernetes sees the resource, hands it to the operator, the operator looks at existing resources and creates what’s missing, and your cluster comes up and runs. This process is called reconciliation.

These ClickHouseInstallation YAML files can also include user definitions, profiles, and quotas. Here’s an example of an XML user defined directly in the YAML configuration. If you look at the YAML, you can see how it maps directly to XML tags, which is exactly what the operator generates from it. This user has the correct network mask, a SHA-256 hashed password, and so on. When your cluster comes up, that user will be there.

[36:55] – Kubernetes Secrets for Passwords

Robert: In distributed systems, you don’t want hashed passwords being passed around in plain text in YAML files. Kubernetes has what are called Secrets: another resource type that lets you pass values like password hashes to applications securely. You create the Secret resource with the actual password value. The Altinity operator has specialized syntax that mimics the normal Kubernetes pattern for handling passwords. As you can see, the password is just pulled from the Secret by its resource name and key. Whatever value is found gets inserted in the right place. This is best practice for managing users inside Kubernetes.

Another thing you can do is put raw XML files in the ClickHouseInstallation resource. If you want to set up a profile with max_threads and log_queries, you can stick the raw XML in there and the operator will generate the files, put them on the file system, and when you log in, everything just works.

[37:45] – System Tables for User Management Introspection

Robert: Let’s talk about some tricks for management you may find useful. First, what the heck is going on? ClickHouse has system tables, and they’re great. There are system tables for all the entities we’ve described: users, roles, quotas, settings profiles. For example, you can see the effects of your settings in a profile by going and looking at system.settings, where changed settings will be marked with a changed = 1 flag. There’s a bunch you can do with this to find out what’s going on in the system and make sure what you specified in your RBAC commands is what actually happened.

The SHOW statement is also very useful. SHOW USERS will, assuming you have the privilege to do so, show you all the users. SHOW QUOTAS, and so on. These are super useful to see what’s defined on the system.

[39:25] – Password Complexity Rules and Access Control Settings

Robert: Password complexity rules are a relatively new addition in ClickHouse, added in the last couple of years. They allow you to specify rules for passwords. For example, you can specify that accounts without a password are allowed, though this is not something you normally want in production. You can also enforce password complexity rules: for example, passwords must be at least 12 characters. These are disabled by default but can be turned on in config.xml. We use these in Altinity.Cloud.

There are also access control settings. For example, there’s a setting that says users without row policies can read rows. Row policies have an interesting property: if a table has row policies but a user shows up without any row policies assigned, what do you do? By default they can still read rows. This effectively creates a loophole where anyone who doesn’t get assigned a row policy can see everything. These settings control behavior like that. Look at them and set them accordingly.

[41:35] – Summary and Takeaways

Robert: This has been a lightning tour of user management. Let me give you some summary takeaways.

RBAC is a very rich, DBA-friendly model. People accustomed to working in SQL and SQL scripts will find it works over the network and over clusters. If you’re managing large systems with many users accessing the system, it’s definitely the way to go.

If you’re just bringing up default users and deploying ClickHouse using infrastructure as code, XML files are a very developer-friendly way to bring up a cluster with default users already configured and ready to roll.

The replicated user directory approach for clusters is a great feature. Unfortunately, it’s not well documented yet. The best documentation is still the pull request where it was implemented. We intend to fix that with a blog article. Kubernetes deployments can go either way, XML users or RBAC commands, and the Altinity operator has a number of Kubernetes-specific features for security. The Altinity Kubernetes Operator hardening guide talks about many of these topics specifically for Kubernetes.

The model is not completely consistent. The difference between XML and RBAC, the different features available with LDAP versus XML versus RBAC, they’re not all totally consistent. And it’s not all fully documented, so sometimes you have to look in multiple places. A really key tip: go look at config.xml and users.xml. They have great comments. Another tip: if you need examples and can’t find them anywhere else, look at the ClickHouse test files. The stateless tests have readable examples of working commands for things like quotas. Search for test number 00297 to find quota examples.

If you find something missing or a security bug, give us a call or report it. Altinity implemented LDAP support, Kerberos, row policy templates, and other security features, and we’re happy to evolve the security model in whatever directions are needed.

A final word: test this stuff thoroughly. It’s complicated and it’s pretty easy to make errors. Be paranoid. Test everything. If you have questions, come check in with us.

FAQ Section

Q: What is the difference between XML users and RBAC users in ClickHouse?

A: XML users are defined in files placed in /etc/clickhouse-server/users.d and are limited to a single server. They support quotas and settings profiles and can have privileges granted directly, but they do not support roles, which are the mechanism for grouping and reusing privilege sets across many users. RBAC uses SQL commands like CREATE USER, CREATE ROLE, and GRANT and supports the full privilege model including roles, row policies, and hundreds of fine-grained permissions. RBAC commands can be executed over the network without touching the file system and can be propagated across an entire cluster. For infrastructure-as-code deployments that need ClickHouse to start with known users, XML is convenient. For production multi-user systems, RBAC is the right choice.

Q: What are row policies and when should I use them?

A: Row policies are per-table rules that automatically filter the rows a user can see when they run a SELECT query. ClickHouse adds the row policy predicate to every query automatically, so the restriction is transparent to the user. They are particularly useful in multi-tenant systems where multiple users share a single table but should only see data belonging to their own tenant or group. Policies can be permissive, meaning that a user can see everything their most permissive policy allows, and they can be assigned using wildcards to cover groups of tables in a database. One important known limitation: the Merge table engine currently ignores row policies.

Q: What is the best way to propagate RBAC user definitions across a ClickHouse cluster?

A: The best approach is to configure a replicated user directory that stores RBAC definitions in ClickHouse Keeper or ZooKeeper. This is done by adding a replicated tag with a ZooKeeper path inside user_directories in config.xml. Once this is configured, CREATE USER, CREATE ROLE, and all other RBAC commands no longer require the ON CLUSTER clause. New nodes and shards that join the cluster will automatically receive all user definitions. The older approach of using ON CLUSTER '{cluster}' in every RBAC command works but requires re-running all commands whenever a new node is added. Altinity.Cloud uses the replicated user directory approach internally.

Q: How are ClickHouse users managed when running on Kubernetes with the Altinity operator?

A: The Altinity Kubernetes Operator for ClickHouse® supports defining users, profiles, and quotas directly in the ClickHouseInstallation YAML resource. The operator translates the YAML into XML configuration files and places them on the file system. For password security, the operator supports referencing Kubernetes Secrets by resource name and key, so hashed passwords are never stored in plain text in YAML. You can also embed raw XML configuration snippets in the resource for settings like profiles that don’t have first-class YAML support. Both XML users and RBAC commands are valid approaches in Kubernetes; the Altinity Kubernetes Operator hardening guide covers Kubernetes-specific security best practices in detail.

Q: What external authentication mechanisms does ClickHouse support beyond local passwords?

A: ClickHouse supports three main external authentication mechanisms. LDAP integration can be used purely for password validation against an LDAP server, or as a full user directory where users and roles defined in LDAP are mapped to ClickHouse roles. This is the mechanism to use for integration with Microsoft Active Directory. Mutual TLS (mTLS) allows client authentication using certificates at the transport layer; each client must present a unique certificate signed by a recognized CA, giving strong cryptographic proof of identity without passwords. Kerberos is also supported and is useful in environments that already use Kerberos for authentication infrastructure. All three were implemented with significant contribution from Altinity.

Q: How do I inspect what users, roles, and privileges are currently defined in ClickHouse?

A: ClickHouse has system tables for all RBAC entities: system.users, system.roles, system.quotas, system.settings_profiles, and system.grants. You can also use SHOW USERS, SHOW ROLES, SHOW QUOTAS, and SHOW GRANTS as convenient shortcuts, provided you have the necessary privileges. To verify that a settings profile is having the expected effect, query system.settings WHERE changed = 1 to see only the settings that differ from the defaults. The ClickHouse security hardening guide provides additional guidance on verifying and auditing user configuration.

© Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc.; Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.