Unleash ClickHouse real-time analytics in any environment: Altinity

Altinity is based on three insights that will permanently change the way businesses create value from data. A universe of business value is waiting to be unlocked through fast access to very large amounts of data. Inclusive open source communities are the best mechanism to develop fit-for-use database technologies and deliver them quickly to users. ClickHouse SQL data warehouse offers the best performance, scalability, and flexibility of any data management product in the market. The Altinity journey began in San Carlos, California. Alexander Zaitsev, Altinity’s CTO, ran into a fundamental problem with analytics for ad networks: data to make ad placement decisions is increasing far more rapidly than business revenue. ClickHouse was the only database that could scale economically, beating out far more mature analytical products.  But it was far from enterprise quality.

Alexander teamed up with Peter Zaitsev and Vadim Tkachenko, founders of Percona and world-renowned open source database experts.  Like Alexander, Peter and Vadim saw the potential of ClickHouse as “MySQL for analytics.”  Together they founded a company to provide guidance to enterprise ClickHouse users and make ClickHouse itself better. Mindaugas Zukas, an experienced manager, joined as the fourth founder to run the company, which began business in 2017.

Alexander also bugged his friend Robert Hodges about the great new database he was working on. Robert joined the team in 2019 as CEO. Like the rest of the group, he saw the opportunity to create a unique offering in the database world that could open up new data applications and enable new markets. Altinity is building a team of like-minded colleagues to make this vision a reality. Security is a key consideration of modern software systems. That is particularly true for applications handling sensitive data, such as database software. ClickHouse offers a rich set of tools to improve security. These security features can be divided into a few groups: user, network, and storage. 

Increasing ClickHouse security at the user level means setting up restrictions and applying security best practices on a per user basis. Some of the features offered by ClickHouse are: quotas, LDAP remote authentication, resource restrictions (i.e. which tables are accessible by which users), and network restrictions. You can read more about all of these in Altinity’s User Hardening documentation. 

User level network access restrictions in ClickHouse are powerful but not always well understood. This blog article is a deep dive into how they work and how to apply them to protect ClickHouse data.

Q. What is User Level Network Security on ClickHouse?

The ClickHouse user level network security toolset allows connections to be restricted to certain hosts. This can be achieved by using the IP, host, like pattern or host_regexp directives. While the IP directive achieves this by simply matching the client IP address with the authorized IP, the other three approaches depend on reverse DNS resolution. The image below shows the four different ways ClickHouse offers to restrict access to specific hosts. As already mentioned, the IP directive allows a list of authorized IP addresses to be specified for a given user. It is a simple approach and will cover basic scenarios where the IP address of the client is fixed. 

Unfortunately, not every ClickHouse client has a fixed IP address. Cloud applications can change IP addresses as they move across availability zones. ClickHouse clients on Kubernetes may change IP addresses when pods restart. In these cases, approaches based on hostnames & reverse DNS resolution are a better fit.

The host directive is exactly the same as the IP directive, but for hostnames. A list of authorized hostnames may be specified for a given user, and ClickHouse will make sure only those hostnames are allowed to establish a connection. The main benefit of this approach over the IP one is that hostnames tend to remain the same while IP addresses have a tendency to change.

Final Remarks

There are multiple ways to improve ClickHouse security at the user level, restricting which hosts can establish connections is one of them. To achieve this, ClickHouse offers four different settings: IP, host, like pattern and host_regexp. Each of these settings has its pros and cons and should be adopted depending on the scenario.

IP and host are simpler approaches and address the basic scenarios, but do not scale well if the IPs are not fixed or the authorized hostname list is likely to grow or shrink over time. In such scenarios, assuming the hostnames follow a predictable pattern, the like pattern and host_regexp are a better fit.

Both like pattern and host_regexp present a very flexible approach to hostname matching. The former being simpler and the latter allowing more fine-grained filtering. As general advice, users should default to the like pattern whenever possible. If the like pattern is too complex or does not fully cover the use case, host_regexp should be adopted.

When adopting one of the approaches that depend on reverse DNS resolution, one must take into account a possible latency increase due to DNS queries. By default, ClickHouse will cache DNS queries, which minimizes the side effects.

Last but not least, the multiple hostnames support added by Altinity not only complies with the network layer standards, but it also extends the use cases of this feature for a variety of scenarios. The use of the Altinity ClickHouse Operator to manage ClickHouse Kubernetes clusters is one of them.

Robert Hodges, CEO

“Altinity empowers developers to add high-performance analytics to any application regardless of where it runs, how much data it needs to answer questions, or how quickly the data arrives.”