ClickHouse® Defense Against the Dark Arts–Intro to Security and Privacy

Recorded: January 27, 2021
Presenters: Alexander Zaitsev – CTO, Altinity & Robert Hodges – CEO, Altinity

In this webinar, Altinity’s Robert Hodges and Alexander Zaitsev explore how to secure ClickHouse and build privacy-aware applications on top of it. Robert frames the two themes: security is mostly about how administrators configure ClickHouse to protect it and its data from attacks, while privacy goes beyond the database to how an application is designed to comply with standards like GDPR. He notes that security is one of Altinity’s biggest engineering focuses, spanning completed work like log query masking, AES functions, LDAP, and RBAC testing, plus in-flight work on Kerberos, BoringSSL, and audit trails.

The security half walks through setting up users in both XML and SQL, the granular privileges available through role-based access control, where user metadata is stored, and centralizing authentication with LDAP. Robert then covers encrypting connections by disabling unencrypted ports and enabling TLS, obtaining server certificates from public, private, or self-signed authorities, securing cluster communications, encrypting data at rest, and using query masking rules to prevent sensitive data from leaking into logs.

In the privacy half, Alexander covers building multi-tenant applications at different isolation levels, restricting access to data with database grants and row-level security, dropping tenants efficiently, and enforcing data retention with TTL expressions. The talk closes by noting that ClickHouse security advanced enormously through 2020, that privacy features carry real costs, especially deletion, and that work continues on BoringSSL, LDAP role mapping, Kerberos, and lightweight deletes.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:10 – Introduction and presenter bios
• 5:00 – A quick introduction to ClickHouse
• 8:21 – Setting up users and network masks
• 13:06 – Role-based access control in SQL
• 15:51 – User metadata and LDAP authentication
• 20:04 – Encrypting connections with ports and TLS
• 24:13 – Choosing server certificates
• 27:21 – Encrypting cluster communications
• 31:41 – Encryption at rest and preventing data leakage
• 38:51 – Privacy-aware applications and multi-tenancy
• 43:50 – Dropping tenants and data retention
• 49:00 – Other privacy considerations
• 52:05 – Summary and Q&A

Webinar Transcript

0:10 — Introduction, Bios, and Altinity’s Security Work

Robert Hodges: Hi everybody, this is Robert Hodges, and I’m one of your presenters today at our webinar, ClickHouse Defense Against the Dark Arts: Introduction to Security and Privacy. Before we get going, I’d like to give you a little information that will help you enjoy the webinar more and be able to ask us questions.

First and foremost, this webinar is being recorded. Because you signed up, we will automatically send you a link to both the slides and the webinar recording a short time afterwards, so you don’t have to take frantic notes. Next, we have a Q&A box, so as the webinar is proceeding, if you have questions at any time about what we’re presenting, feel free to pop up that Q&A box and ask. If they’re relevant to the slide we’re on, we’ll talk to them right then and there. We also have time at the end to take questions, and we’ll try to answer all of them. A final thing: we’ll run a very short poll at the end that asks three simple questions. It’s basic information that helps us make these webinars better in future. With that, let’s dive in.

Once again, my name is Robert Hodges, I’m CEO of Altinity, and my background is that I’m a database guy. I’ve been doing it for about 37 years. ClickHouse is the 20th database I’ve worked on in one form or another, and I’ve taken a couple of side trips into virtualization as well as security, which is actually one of my favorite topics in software engineering.

I’m joined today by Alexander Zaitsev, my friend and colleague. He is our CTO, one of the founders of Altinity, and he has decades of experience on petabyte-scale analytic systems including ClickHouse, but also extending to other technologies like Vertica.

A little bit about Altinity: we are the number one enterprise ClickHouse provider. This is a broad ecosystem, and many people offer services related to ClickHouse, but our focus is on making enterprises successful in building analytic applications that add value to their businesses. We are a major committer as well as a community sponsor for ClickHouse, particularly in the United States and European markets.

Normally I stop at the introductions and get straight to the technical detail, because I know you’re all geeks just the way we are, but I’d like to make a slight deviation and talk about why Altinity is somebody that has something to say about security and privacy. The main reason is that we work on it constantly. It’s probably the biggest single focus of our engineering effort. We also work on things like storage and new forms of compression, Kafka, and others, but security is definitely a really big focus.

Among the completed work, we’ve done things like log query masking, which we’ll talk about, and how you can make particular types of information disappear out of the log. We implemented AES encryption functions. We implemented LDAP, and there are a couple of different forms we’ll talk about. We also did almost all of the testing for role-based access control, and that’s an important part of what we do. ClickHouse is a community. There are hundreds of people who submit PRs on security features, even those we don’t implement ourselves, and one of the things we take responsibility for is creating the tests for them. So role-based access control is a very broad interface, and we wrote a test suite using a tool called TestFlows that has something like 7,000 checks on the different features of role-based access control.

Those are things where we’ve finished implementation and testing. We have in-flight work around Kerberos, BoringSSL, lightweight update and delete, and audit trail, which we’ll talk about later. And we have a roadmap that includes a bunch of stuff we’re working on with customers to define requirements, prepare designs, and move forward, everything from trusted builds to transparent data encryption with server-side key management. So that’s us. We really like security a lot, and for that reason this is going to be a fun webinar, because we get to talk about stuff we enjoy doing.

5:00 — A Quick Introduction to ClickHouse

Robert Hodges: I’d like to give a very quick introduction to ClickHouse, because not everybody on this call is a ClickHouse expert. Something popped up on the screen there that I didn’t expect, sorry about that. Let’s level-set so you understand what ClickHouse does.

If you haven’t used ClickHouse before, it’s an open source SQL data warehouse. You can think of it as the child of MySQL and Vertica. Imagine they got married and had a kid, and that child would look like each of the parents. There’s no relation in the code, but from the MySQL side you have things like a single binary that runs practically anywhere, you understand SQL, and you’re open source. ClickHouse is Apache 2.0, which is a much more flexible license than MySQL, and a nice contrast to some of our competitors. From the Vertica side, Vertica being a very well-known data warehouse, we get things like storing the data in columns, so arrays of highly compressed data, and parallel and vectorized execution. Not just vectorization, which refers to breaking things into pieces and using SIMD instructions on individual cores, but also the ability to run in parallel both across many CPU cores on a single host and across many CPUs. The scaling is enormous. There are many ClickHouse clusters that run into hundreds of nodes. And beyond that, because of the implementation, both the storage and the ability to parallelize, plus very tightly honed algorithms, ClickHouse is incredibly fast. This tends to be the thing people notice when they first use it.

Unfortunately for modern applications, it’s not enough to be just fast, and that’s really where this talk comes from. We’d like to explore two main issues: security, which is protecting ClickHouse and the data within it from attacks from within and from without, and privacy, which means building applications that comply with standards for protecting user data. These are closely related. The security part, which we’ll spend the lion’s share of this talk on, is specifically related to ClickHouse, and it’s mostly an implementation thing, where depending on how your administrators set up ClickHouse, you can achieve higher or lower levels of security. Privacy is interesting because to achieve it, you have to go a little beyond ClickHouse itself, thinking about how your application is designed and how it treats data that’s loaded into ClickHouse. We’ll go into both topics in detail.

By the way, there was a question: which data protection standards are you referring to, GDPR? Yes, that’s the main one, but there are now others like the California data protection laws that were recently passed. This is something that’s becoming increasingly standard in applications.

8:21 — Setting Up Users and Network Masks

Robert Hodges: Let’s start with securing ClickHouse servers. We’ll cover setting up users, authorizing access, encryption, both encrypting your connections and encrypting data at rest, preventing data leakage, and then some pointers on securing your ClickHouse host.

Let’s start with users. If you’ve been using ClickHouse for a while, you probably know these files. ClickHouse started out by just using an XML file to define all the users, plus things called profiles, which consist of configuration settings, and resource limits, and quotas, which are quotas for usage. The way you do this is you put the data into these XML files. ClickHouse is very good at recognizing when the files have been changed, so you don’t have to restart the server. If you want to add a user, you would see something like the following. If we wanted to define a root user, there’s a typo here, you’ll put this in the little file in the users.d directory, have it read-only for ClickHouse, and then add things like a tag that says root, which is the name of the user. The typo is that it’s “ro user” before, because I took this from a previous example. Then you have the password, which is a SHA-256 hashed value, in this case for the word secret. You’ll have things that say which networks we can log in from, which profile and quota you use, and then a very important thing in modern ClickHouse versions, access management, which says whether we can use the role-based access control commands to create users, assign privileges, and create roles. So you put this in the file, pop it into /etc/clickhouse-server/users.d, and all of a sudden you’ve got a user you can log in as.

There are a few tips that apply to users in general. The first thing is that when you’re generating SHA-256 passwords, it’s really common to forget that if you echo a word like secret to sha256sum, it includes a carriage return. Ask me how I know. So you say echo with the minus n flag. This is just a trick for generating it correctly, and if you forget and don’t have these slides, it’s also mentioned inside the ClickHouse configuration files.

Another feature that can be confusing but is very helpful is network masks. These say that the user can only come from certain hosts, and ClickHouse will look at the IP headers to figure out where the user is from. This is very flexible. The first example would only allow a user from localhost. The next would allow it from a specific subnet, that’s a CIDR address with the subnet on it. You could allow it from a specific host, and then, very flexibly, you have what’s called a host regular expression, where you can define a set of host names that match, and ClickHouse will figure out their IP addresses and fill this in for itself. This is super useful, for example in defining a root account that can only log in on the local host, which is good because it’s a powerful account. These are just things we’ve run into and answered in support cases.

13:06 — Role-Based Access Control in SQL

Robert Hodges: The biggest question, though, when you’re manipulating these files, is wouldn’t it just be better if you could do this in SQL? The good news is that one of the really great improvements in ClickHouse over the course of 2020 was that we added full role-based access control, as well as the ability to create users, quotas, profiles, and all the other types of things required for user management.

This is a simple example where we’re creating a read-only user. We do a create user, revoke all privileges on it, then create a role that has the ability to select off the default database, the standard database always present on all ClickHouse servers, and then grant that role to the user. A lot of the syntax used here is standard, but in the ClickHouse case there are features reminiscent of MySQL, so if you’ve used MySQL security and are familiar with their RBAC implementation, you’re going to be pretty much at home.

This is a really great model and includes very granular protection. The number of things you can grant privileges on is huge. For tables, you have basically any command that affects the table, be it create, insert, alter, delete, or select, you name it. You can grant or revoke specific privileges to control whether individual users can run these commands. For databases you can do create, drop, and show. For access management, all the major entities like users, roles, and row policies can now be created and managed through SQL. And all the system commands can similarly be protected using role-based access control. We’ve tested this extensively. This was an effort that has run for close to eight months, and there are relatively few holes in this model that we’ve found. It’s quite stable, particularly if you’re using an up-to-date ClickHouse release.

15:51 — User Metadata and LDAP Authentication

Robert Hodges: One interesting question is what happens when you create a user, because one of the criticisms we hear about the old XML-based model is, hey, you created a user and there’s their password lying around on the file system. What happens when you create a user using these new SQL commands? The answer is there’s still a password, and it’s still on the file system. Here’s a simple example where we’re creating that read-only user. That gets submitted to the ClickHouse server, which creates a file out on /var/lib/clickhouse/access, the default location for this information. So there’s a new set of metadata being created on the server that helps manage users. If you’re familiar with how ClickHouse internally manages tables, this will look familiar. We say attach user, and this gets read when the ClickHouse server starts up so that it knows who that user is.

What that means is you still have this information on the file system. Moreover, if you want to change the properties of a user across a cluster, like the password, you’re going to have to go to each of the servers and execute this command. The create user command does support the on cluster keyword, so you can create a user and blast it out across a cluster, but the point is you still have to have this metadata file present on each of the servers for that user to be recognized.

So that brings up the question, is there a way of centralizing users so you don’t have to do this? It turns out there is. One of the things we’ve spent quite a bit of time on over the last year is LDAP. This is the first example of what we call pluggable authentication. In other words, there’s now an interface inside ClickHouse that allows us to build different implementations of authentication. Since ClickHouse version 20.8, you can now create a user and say identified with ldap_server and the name of that server. This is a new way to create users, and when this LDAP user tries to log in, ClickHouse will go and check inside an LDAP server to find out whether the user gave the right password. One thing I should note is there’s a change coming where the keyword will change to ldap from ldap_server, and that’s going to come when Kerberos support is fully implemented.

Here’s the flow. We define that user, then log in with clickhouse-client, which is everybody’s favorite client. That goes to the server, the ClickHouse server knows it needs to use ldap_local to look up this user, and there’s a new extension to the configuration file where you give the connection information for the LDAP servers. You give the host name, the port, and security information, for example if you’re using TLS encryption. It marches out to the LDAP server, looks up this user, and finds out their password. If it matches, they log in. The nice thing about this is you can change the password in one place and it takes effect for all users. Moreover, that password is no longer on the file system. This is a big improvement, one we plan on using ourselves in the services we run, and we expect it’ll be pretty popular in the community as well.

20:04 — Encrypting Connections with Ports and TLS

Robert Hodges: That’s user management and role-based access control, the ability to authorize things. Now I’d like to shift gears and talk about encryption, starting with in-flight connections, how people connect to ClickHouse over the network and how those connections are protected. One interesting thing about ClickHouse is that it has a huge diversity of connection types it can accept. This little picture shows all of the ClickHouse client ports that are supported. They go from the obvious ones, like the HTTP and the native TCP-compliant client, which have different ports, to a bunch of relatively new ones, everything from the MySQL client protocol down to the Prometheus metrics adapter. Finally, there’s a port the ClickHouse server maintains to help with replication. If you’re running a cluster, the way it replicates between tables using the replicated merge tree is that ZooKeeper stores centralized information about the parts, the pieces of the table that need to be replicated, and this inner server port, which defaults to 9009 when unencrypted, is where the servers go to fetch these parts.

So we think about these as the attack surfaces. Anything marked in red we want to make sure is turned off, because those are the unencrypted ports. For example, the HTTP client defaults to 8123 when unencrypted and 8443 when it’s a TLS-encrypted, HTTPS connection. We want to turn off the red ones and light up the green ones, so we only have encrypted communications coming into the server. We’ll also close down any other port we’re not using.

Here’s the process. We’ll turn off the unused ports, enable TLS encryption, and then for clusters, in addition to switching to TLS on inner server communications, define a user for remote calls. The first thing is turning off unused ports. If you’re on a local server, there’s no reason to have the inner server HTTP port open, and certainly no reason to have the MySQL port unless you’re using it. You comment these out, and in fact the MySQL port should be commented out by default. Then you restart the server. ClickHouse does not pick up network changes automatically, so you always need a server restart for this.

The next thing is to enable TLS client connections, which includes turning off the unencrypted ports and enabling the TLS encrypted ports. That’s the easy part. The hard part is that to do this, you need to supply an X.509 certificate and a matching key, which is standard so the key exchange can work. You also have to supply Diffie-Hellman parameters. These are optional but fairly easy to create, and you can read the instructions in config.xml.

24:13 — Choosing Server Certificates

Robert Hodges: So the big topic is how to get your server certificates. For ClickHouse, there are basically three options we see people use. The first is to get your certificate from a public certificate authority like Let’s Encrypt. This is great if you’re doing external services. For example, we run Altinity.Cloud, and we use a public certificate authority for our certificates. The important thing is that this works well if you have large numbers of clients, or clients you don’t control, because those clients will accept this certificate automatically without you having to do any special configuration. For public-facing things or services with a large audience, this tends to be a really good solution.

Another solution, very common for internal systems, is to have your own internal certificate authority. That means you generate your own root certificate and use it to sign the certificates you hand out to the ClickHouse servers. This is good because it lets you support in-house systems very easily without bothering to call out to an external certificate authority, and you can do it very fast. There are some downsides we’ll talk about.

The final option is self-signed certificates. The only reason I would do this is for testing, just to confirm I can put a certificate in and ClickHouse will consume it and connect. But they are worse than useless for production data, because any client that connects has to turn off verification of the certificate, which effectively throws away the basic protections you get through encryption. So use this for testing, but in the ClickHouse case I would never use it for real data. For more information on these procedures, we have a blog article that tells about each of them in detail.

A few tips. If you’re wondering whether you really need TLS, just think about this: if you’re connecting through the TCP native protocol or the HTTP interface, your credentials are passed in the clear. There’s no extra step ClickHouse takes to encrypt these things, so you need to enable TLS to protect those credentials. Even on internal systems, if you think anybody can sniff the network, you should have TLS enabled. I even run TLS on my laptop, it’s really not very hard to set up. The second tip is that if you’re using a non-public CA, you have to be aware that the steps to get that certificate accepted differ for different applications, and are even different between versions of Ubuntu. This is just an example of what you have to do to get Node applications to accept your custom root certificate. Another thing about in-house custom authorities is that you have to have good hygiene. What I usually do on my laptop is set it up using scripts and then forget where I put it, lose the keys, and from time to time have to redo it. You don’t want to be like that. There are a number of products that help you maintain CAs, so be organized. Finally, a really useful command, if you’re setting ports and want to be sure you’ve enabled and disabled the right things, is this netstat command, a quick way to find out that the ports you expect are open and listening. In this case these are encrypted ports and don’t include the unencrypted ones, so this is what I would expect to see.

27:21 — Encrypting Cluster Communications

Robert Hodges: Moving to clusters, very briefly, it’s not too much harder. We want to include the cluster traffic. There are a couple of different ways cluster nodes talk to each other. One is distributed tables. If a query on a distributed table arrives on logos1, it will probably want to call out to another replica to get data, so that goes through port 9440. Then the inner server port used for replication between replicas uses port 9009.

Setting that up is fairly straightforward. One thing we recommend, if you’re using older versions of ClickHouse, is to create an internal user. I call this user internal, and I restrict it to only the hosts where my cluster is running. This means I can put it into the configuration files. First, for the inner server communication, I can use this user internal and I don’t have to leave a password in the file, because the network filter prevents it from being used outside the network. Of course, if somebody gets onto the host, they can use it as well. So this is turning off the inner server replication on 9009 and turning on 9010. The other thing we do is set TLS connections for distributed queries. This remote_servers.xml file usually defines this. We see the host for a replica, the port to call, that we want to use secure communications, and the user we want to use. This is the current way to do it if you’re using a stable release of ClickHouse 20.10 or below.

If you want to look forward a little, as of releases after 20.10, I can’t remember whether it’s 20.10 or 20.11, you’ll now have what’s called a shared secret, which you just write here. This is used for communication specifically between the clusters, and as long as those secrets match, it will connect across the network. The cool thing is that one of the things tied to this is you no longer have to define a specific user, because ClickHouse will try to use the same user that initiated the query on all the nodes where they run sub-queries. So this is going to be the recommended way to set this up going forward.

31:41 — Encryption at Rest and Preventing Data Leakage

Robert Hodges: That’s connectivity for a single host and for a cluster. Now let’s look at encryption at rest, and there are really two options. The first, if you’re really concerned about protecting data, is just to encrypt the entire file system where ClickHouse data is located. There are three options you may want to consider. The first is to get Linux to do it using the Linux Unified Key Setup, the standard encryption for the Linux operating system, where you can encrypt partitions or volumes. That’s purely local. If you’re in the cloud, Amazon in particular has automatic encryption on EBS volumes, stored out in the network, and you can enable that with a switch. We’ve tested it, and at least for gp2 storage there does not seem to be any performance overhead whatsoever, which matches Amazon’s claim. And if you’re on Kubernetes, the Amazon EBS provider has this as an encrypted option, so your volumes will automatically be encrypted. You can do more complex setups, but these are pretty standard ways to make sure your data is properly protected.

What you may also want to do is encrypt specific things in the database, because file-based encryption doesn’t help if somebody can get into the database, where they can see the data and select it. Starting with 20.11, you can use AES encryption functions. These are very similar to the MySQL AES functions, and one of the things we did was create versions tested to ensure they’re exactly compliant with MySQL encryption, because a lot of people share data between MySQL and ClickHouse. Here’s the basic form of the calls. For example, we use a common table expression to define our key, then use that key to encrypt hello world, getting a nice encrypted value. We can unhex it and decrypt it using the same key and get it back. This is pretty simple to do.

The one thing you’ll probably notice is that you have to manage the keys. There’s no magic here, no server-side key management, that’s something we’re planning to do in future, but it’s up to you to make sure the keys are properly handled and that you take care of rotating them. Another important thing is you don’t want to apply this willy-nilly, because there’s expense required to encrypt values, it takes CPU time, and expense to decrypt them. Since ClickHouse tends to focus on reads, this is going to change the performance of your queries. In the current implementation, OpenSSL is a little bit pokey. ClickHouse is going to be moving to BoringSSL, which is quite a bit faster, so we expect a significant speedup in the future.

Another issue this brings up, particularly with the AES encryption functions, is how to protect against sensitive data being leaked out of the system through logs. If you issue those calls and then look at your log, with it set to debug level, you’ll see a message like what’s on this slide. You’ll notice a couple of things. If you look at the decrypt on the left side at the bottom, the arguments have disappeared, so sensitive arguments just disappeared. On the other hand, we can see the key shown in the text, so somebody could get into the log and see what your key value is. So there are two questions: how did those sensitive arguments disappear, and how can I get rid of the remaining one? The answer is to use what are called query masking rules. These are defined in config.xml and are basically regular expressions that let you find and substitute items that appear in the log, and it’s not only the text log but also things like the query log table, so it prevents them from showing up anywhere. In this particular case, this regular expression rule looks for encrypt and decrypt function calls and removes all the arguments, turning them into question marks. So the answer to the first question is that this rule is here by default. And to get rid of the key shown in the common table expression, you would just put in another rule and make it disappear. That’s a great way to prevent data leakage.

Just a few final tips before we move off security. There are many ways hosts need to be protected. I’ll mention a few important for ClickHouse specifically. ClickHouse runs as a clickhouse user, which is good news, so you don’t have to worry about it running as root and running amok, at least we hope you don’t. Two key things to think about are protecting the directories that contain data and credentials, which are listed here, and it is important not to forget your logs, because those are also quite sensitive and potentially contain very sensitive information. The second thing is protecting the network around ClickHouse. ClickHouse can have well-defined users and use network masks, but things like load balancers, firewalls, and making the servers unroutable to people who shouldn’t see them anyway are things you want to consider in your security regime. So that’s been a lightning tour of the ways you can secure a ClickHouse server. Now I’m going to turn this over to Alexander Zaitsev, who’s going to talk about building privacy-aware applications. Alexander, take it away.

38:51 — Privacy-Aware Applications and Multi-Tenancy

Alexander Zaitsev: Thank you, Robert. Hi everybody, let’s continue. Probably one of the most important questions from an application design perspective, especially for SaaS businesses, is how to build multi-tenant applications in ClickHouse. There are different ways to do that, distinguished by different isolation levels. For example, you can have a dedicated ClickHouse installation per tenant, in which case you only need to care about shared networks if any. Or you might have a dedicated ClickHouse cluster per tenant inside the bigger cluster, in which case you share more resources like the Keeper, your configuration files, and network, and you need to make sure the data between different tenants is properly isolated. You can go to even less isolation, just separating databases and having a separate database per tenant. In this case you start to share ClickHouse hosts between tenants, and tenants share more and more resources of the same machine. Or you can go to dedicated tables and create separate tables per tenant, or you can even have the same table shared across multiple tenants.

These designs come from your application requirements. If you don’t have too many tenants, the best way is to isolate as much as possible, probably on the installation level. If you have thousands of tenants, you cannot afford even table-level isolation, you have to use shared tables, and in this case you need to be much more careful about how you isolate queries and data between your customers.

ClickHouse provides multiple ways to control and restrict access to private data. The old-fashioned way is using users.xml, and this feature is almost not documented, I don’t know why, but you can specify the list of databases the user is allowed to access. So in this example, the user demo can access only the database demo and cannot read any other databases at all. This is the gold standard if you use older ClickHouse versions. With newer ClickHouse versions you actually have more flexibility. You can restrict a particular user to access a database, or access a particular table in the database. So it’s much more flexible and more powerful as well.

If you go to the shared table model, you need to make sure users cannot see each other’s data. You may control it on the application level by providing a proper filter, but it’s better to control it on the server level, and ClickHouse provides support for that. You can configure a so-called row-level security filter, and again there are two ways, an old way and a new way. The old way, you configure it in the XML file, because it’s defined as a separate filter condition applied to the where condition at the beginning stage of query processing. Or you can use the new approach, where you create a row policy filter and specify the same condition. This is great. One of the limitations here is that if you have many tenants, you’ll have to repeat the separation for every single tenant or user, and one possible improvement in this area is to do some templating, so you can specify a user that accesses the database and apply it as a filter to all the tables.

43:50 — Dropping Tenants and Data Retention

Alexander Zaitsev: Another popular application-level or configuration-level task is how to drop tenants. If you build a service, you may have some customers coming and some, unfortunately, signing off, and you need to make sure you can delete the data properly. ClickHouse supports several ways to do that, and the most efficient is to drop partitions. But in order to drop the partition, you need to design a schema so that a table is limited in a partition scope. If you have a database or a table per tenant, this is very easy to drop, you just drop databases or tables. If you have a partition per tenant, it’s also easy to drop. Again, the question is how many tenants you have. If you have a small number, you go with one design; if you have a bigger number, you go for another.

The worst-case scenario is when you have to use a shared table for all your tenants. In this case, to drop the tenant, you need to execute an alter table delete statement. This works, but it might touch all your data. Consider if you have several terabytes or tens of terabytes; when you execute alter table delete, ClickHouse will have to touch virtually all your database, rewriting it and removing the tenant, and this is a huge I/O overload. What can be optimized here is, first, if your data is also somehow partitioned, then you can limit your alter table delete to a certain partition. This is a new feature available in 20.12. And we are working on lightweight deletes that will allow ClickHouse users to delete data in a more or less synchronous way without much overhead.

Another important scenario is data retention requirements. Quite often we need to keep data for a certain amount of time, and depending on your schema design you can implement different scenarios. For example, we can define a TTL delete expression to keep the data for a single month and drop it after that. This is very efficient for monthly partitioning. Or we can have a TTL expression that uses the date column and another column we keep together with our data, which in this example is called ttl_days_to_keep. With such a schema design, we can have a very flexible retention policy, a separate retention policy for every tenant. For some tenants we may keep data for seven days, for others 30 days, and so on, depending on the subscription plan for example. To be efficient, you need to partition data daily in this case.

It’s interesting that you can specify function calls in TTL delete statements. In particular, you can put a dictionary call here, and that allows you to configure the actual TTL in some external table exposed as a dictionary and modify it if necessary. It’s worth mentioning that if you modify this TTL expression, it doesn’t apply to the data already in the database, but it will apply to new data that comes after that. ClickHouse also allows you to define an arbitrary expression during a TTL delete, where you can specify any SQL expression on your table columns. In addition to table-level TTLs, you can specify column-level TTLs. For example, you may have a table that keeps marketing data, and you don’t want to keep your customer ID for longer than one month, but everything else you may want to keep for one year, for example if you have models that require more data. You can do this in ClickHouse just by defining the column-level TTL expression like shown here on the slide.

49:00 — Other Privacy Considerations

Alexander Zaitsev: Another set of things to consider, in between application design and server configuration. First of all, as we mentioned earlier, there are some tables in ClickHouse that may expose sensitive data, and you need to make sure this data is masked or those tables are not accessible to users. There are at least three tables in question: system.query_log, system.text_log, and system.processes. These may show data. For example, if your user can run a show processes statement, he can potentially see all queries running on the server, and you may see sensitive data in those queries, so those operations need to be restricted.

Another practice is related to key management. ClickHouse doesn’t have good key management yet, this is in the design stage, and if you use encryption functions, then in order to forget the data you can just lose the keys, and if there are no keys, the data cannot be recovered at all. One more interesting capability is related to query settings. As we discussed earlier, you can define different settings on the user or profile level, but by default a user can change the settings using the set command. In order to protect some settings from being changed, ClickHouse has so-called constraints on settings, so you can define certain settings as read-only, or that other settings can be changed only above a certain limit, and so on. Those are good to keep in mind if you allow users to execute set operations, and non-read-only users usually can do that.

Last but not least is secret management. In general, currently it’s the application’s responsibility, and it’s not just management of secrets for encryption functions and some server-level things. For example, if you create a Kafka engine table in ClickHouse, or a MySQL engine table, or a dictionary that connects to some external source, you have your keys as part of the table definition, and those are exposed if somebody runs a show create table statement, where they will see the keys. This definitely needs to be fixed, and we’re working on that as we speak. So next, Robert, you can wrap up.

52:05 — Summary and Q&A

Robert Hodges: Great, thank you, Alexander. Let’s summarize quickly. As we showed in this talk, ClickHouse security has advanced enormously over the course of 2020. I’ve been working on ClickHouse for about two years, and when I started, it was XML files, and yes, you could do TLS but it was all pretty painful. The introduction of RBAC, LDAP, and AES encryption now gives you a really strong feature set to secure your data both in flight and at rest. Privacy is really a property of the applications. ClickHouse has a number of features that help you implement things like the GDPR right to be forgotten, but they have varying costs, and one of the things you really need to think about is how much it’s going to cost you, for example, to delete data, and there’s a trade-off between that cost and how many tenants you want to support. Alexander showed a number of options.

In 2021 we’re continuing to move forward on security. Two really interesting things have already merged. One is BoringSSL, so we’re moving off OpenSSL as the default implementation for TLS. One of the things this does is significantly boost the performance of encryption in the AES functions and elsewhere. And LDAP role mapping just merged a couple of days ago. We have work in progress on Kerberos as well as the lightweight update and deletes that Alexander referred to.

So with that, we’re done. Here are a few references for ClickHouse on GitHub as well as the Altinity blog. We’ll be publishing a bunch of stuff on security this year, and I’d encourage you, if this is an interesting topic, please give us a call if you have particular security concerns around ClickHouse. If you’re already a customer, that’s just a support case, and for those of you who are not, please feel free to call, we’d be delighted to talk about this topic. With that, I’m going to pop up this poll, and in the meantime we’ll open it up for questions.

There were two that came up in chat I’d like to cover. One is a repeat, which data protection standards are you referring to? GDPR is the main one, obviously, but there are other standards emerging. In the United States there are things like HIPAA, which has existed for a long time, and there’s PCI DSS, the credit card protection standards. In fact, the log cleansing was actually implemented as a way of allowing people to store credit card information safely and not have it leaked in logs. So there’s a bunch of standards applicable here, it’s not just GDPR.

There’s a question about which protocols are supported for TLS. Do you have 1.3 and 1.2? Yes, both are supported. What is specifically not supported is SSLv2 and SSLv3. Those are specifically turned off. You can enable them, but they’re no longer considered secure, so ClickHouse has them turned off by default.

We have some questions coming up. Alexander, here’s one for you. How do the traditional file-based approach and the RBAC SQL-based configuration of users play together? Are all RBAC features available in user configuration files?

Alexander Zaitsev: No, not all RBAC features are available in user configuration files. RBAC features are actually much more powerful. In user configuration files you can only control access to databases and row-level security, and that’s it. In RBAC you have roles, which I’m missing there, and I believe also the ability to control access on the table level and column level. We didn’t show it in examples, but you can also limit columns that a user can access, and also manage access to other ClickHouse capabilities like system calls, set queries, and so on.

Robert Hodges: Another interesting thing is that these commands also support the on cluster keyword, so you can blast these out across a cluster. So no more sshing in. Well, of course none of you guys did that, that’s only me. You’re probably using Terraform or something, but you can get that stuff blasted out pretty well.

Great question, here’s another one for you, Alexander. Does the multi-tenancy model impact ClickHouse insert performance? Is shared tables the fastest approach?

Alexander Zaitsev: Henry, actually it depends. It depends on how you insert the data and how many tenants you have. As I mentioned earlier, if you have hundreds of thousands of tenants, and you collect data in a single log and insert it in a single insert statement, it may result in one bunch of data having many tenants, and if you partition data by tenant one way or another, it will generate too many small pieces of your data, that’s small parts, which will definitely affect your performance. That’s why we typically recommend that if you have hundreds of thousands of tenants, go to a single table design, which is better from a performance and operational perspective, but it has some security flaws.

Robert Hodges: Here’s a question: what’s a ballpark percentage estimate for the security overhead on ClickHouse?

Robert Hodges: It depends on what security you apply. Again, it’s a trade-off between being secure and being fast. If you use encryption functions, this is a huge hit on the data being encrypted. The TLS encryption for connections is absolutely transparent, and you will hardly notice that. And AES with BoringSSL, there were some interesting numbers. One of the tests Milovidov ran when he merged this showed something like a 30 times increase in performance for AES, but there’s still no question that it’s slow, and it’s slow in your queries, so you’re definitely going to feel that. Other things, there’s virtually no impact.

Here’s one: is it possible to configure the Kafka engine to use multi-tenancy models, for example split messages from one Kafka topic to separate databases or tables?

Alexander Zaitsev: Yes, you can do it. You can split a single topic to multiple destinations, because you typically attach a materialized view to your Kafka engine, and in the materialized view you can have a where condition, and depending on the where condition, which could be a tenant ID, you can route this data to a different table or database. So yes, you can do it, no problem.

Robert Hodges: An expansion on distinguishing between different security settings: we don’t have a comprehensive list, but in general, security things like RBAC are really cheap, they’re just not very expensive. Anything that involves encryption is more expensive. The biggest issue we find people run into, the one that will undo you, is the cost of deleting things. Let’s take the worst example. Say you have a thousand tenants sprinkled across a big table on a big cluster. If you’re not careful, deleting one tenant out of that will force you to touch every part in every version of that table on every cluster. It’s a huge overhead, and when you touch a part you actually have to rewrite the part, that’s how ClickHouse deletes data. That’s why we’re so interested in this lightweight delete feature. So that’s something you need to be aware of and design into your application so it doesn’t become a problem.

Here’s one: is there a tutorial on how to set up certificates with Let’s Encrypt and automate renewal with Certbot? Can’t find anything on that.

Robert Hodges: Alexander, I think the answer is I don’t know of one. I’ve seen blog articles, but that’s a great question, and if we can find something we will post it. I wrote the blog article on ClickHouse network security, and I just said you’ll figure it out. But that’s a good one, we will go back and look for that.

Okay, I think that covers most of the questions. We’ll stay here a minute more if there are any further ones, but otherwise, thank you very much. We really hope this was useful, and I look forward to talking to you more about security topics. As I said, we’ll be continuing to talk about this on our blog and share some of the interesting things going on with security on ClickHouse. If you have more specific questions, please feel free to check in with us at info@altinity.com, we’d be happy to discuss it further. So with that, Alexander, thank you very much, and thank you to everybody in the audience. We really appreciated having you here today.

Alexander Zaitsev: Thank you.

FAQ

Should you manage ClickHouse users in XML files or with SQL? You can do both. The original approach defines users, profiles, and quotas in XML files, which ClickHouse reloads without a restart. Since 2020, ClickHouse also supports full role-based access control through SQL, letting you create users, roles, quotas, and profiles with granular privileges. SQL RBAC is more powerful: XML configuration only controls database access and row-level security, while RBAC adds table-level and column-level privileges and management of system commands.

How do you encrypt connections to ClickHouse? Disable the unencrypted ports (such as 8123 for HTTP and 9000 for native TCP), enable the TLS-encrypted ports (8443 and the secure TCP port), and supply an X.509 certificate, a matching private key, and optionally Diffie-Hellman parameters. A server restart is required because ClickHouse does not pick up network changes automatically. This matters because credentials are otherwise passed in the clear over both the native and HTTP interfaces.

What are the options for ClickHouse server certificates? There are three. A public certificate authority such as Let’s Encrypt is best for external or large audiences, since clients accept the certificate automatically. A private, internal certificate authority is good for internal systems you control and is fast to set up. Self-signed certificates should only be used for testing, since any client must turn off certificate verification, which defeats the protection encryption provides.

How do you encrypt ClickHouse data at rest? Two approaches. The simplest is whole-filesystem encryption, using Linux Unified Key Setup locally, or encrypted EBS volumes in Amazon, which can also be enabled automatically through the Kubernetes EBS provider with little or no performance overhead on gp2 storage. For finer control you can encrypt specific values with AES functions, but you must manage and rotate the keys yourself, and encryption and decryption add CPU cost to queries.

How do you keep sensitive data out of ClickHouse logs? Use query masking rules, defined as regular expressions in config.xml. They find and substitute sensitive items wherever they appear, including the text log and the query log table. A default rule already strips the arguments of encrypt and decrypt function calls, and you can add your own rules to mask other sensitive values, such as keys. You should also restrict access to system tables like query_log, text_log, and processes, which can expose query text.

What is the most expensive part of building privacy-aware applications on ClickHouse? Deleting data. ClickHouse implements data retention with TTL expressions and supports dropping tenants by dropping databases, tables, or partitions, which is cheap when the schema is designed for it. The expensive case is a shared table across many tenants: an alter table delete may have to touch and rewrite essentially every part of the table on every node. This is why schema design around partitions, and the upcoming lightweight delete feature, matter so much.

© 2026 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.