Locking Down ClickHouse Networking (Part 1)
In this two-part article series, we will be looking at securing the ClickHouse server by locking down its networking. Specifically, in this first part, we’ll identify what ports are opened by default as well as look at all the ports that can be opened by ClickHouse. We’ll also explore how we can add or remove open ports. Then, in the upcoming [Part 2], we will continue where we’ll leave off here and look at enabling secure connections using SSL and learn how to lock down the server’s access by filtering the hosts and networks from which connections can be made to our server.
We will be using the altinity/clickhouse-server:22.8.15.25.altinitystable Docker image to play around with ClickHouse. You can easily pull this image to make it available on your machine and follow along. For more information on whether using Altinity Stable builds is right for you, visit https://altinity.com/altinity-stable/.
docker pull altinity/clickhouse-server:22.8.15.25.altinitystable
22.8.15.25.altinitystable: Pulling from altinity/clickhouse-server Digest: sha256:9e5ddb1f26695de16eabbf00548220434535ff9376722d506fb237e02f22ec0c Status: Image is up to date for altinity/clickhouse-server:22.8.15.25.altinitystable docker.io/altinity/clickhouse-server:22.8.15.25.altinitystable
We can now run this image and execute bash inside of it to use it as our testing environment.
docker run -d altinity/clickhouse-server:22.8.15.25.altinitystable
In my case, the container ID is 03e3bb891946b7068848eda073a04524bd7cf6f8e91482f7b247528241c761ab, and therefore, I will login to my container using the following command:
docker exec -it 03e3bb891946b7068848eda073a04524bd7cf6f8e91482f7b247528241c761ab bash root@03e3bb891946:/#
Default opened ports
The first question that we want to answer related to ClickHouse network security is: What ports are opened by default? To find out, we will use the netstat standard network tools utility. It is not available inside the container by default; therefore, we have to install it first by installing the net-tools package using the apt-get command.
root@03e3bb891946:/# apt-get update root@03e3bb891946:/# apt-get install net-tools
Now that we have netstat installed, we can easily check what ports are open for listening.
root@03e3bb891946:/# netstat -tulpn | grep LISTEN tcp 0 0 0.0.0.0:9009 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:9000 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:9004 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:9005 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:8123 0.0.0.0:* LISTEN -
From the output, we see that inside the container, we have five ports opened for outside connections.
Let’s look at what each port is used for by looking at the ClickHouse default configuration file, /etc/clickhouse-server/config.xml.
root@03e3bb891946:/# cat /etc/clickhouse-server/config.xml | grep 9009 | head -1
<interserver_http_port>9009</interserver_http_port>
root@03e3bb891946:/# cat /etc/clickhouse-server/config.xml | grep 9000 | head -1
<tcp_port>9000</tcp_port>
root@03e3bb891946:/# cat /etc/clickhouse-server/config.xml | grep 9004 | head -1
<mysql_port>9004</mysql_port>
root@03e3bb891946:/# cat /etc/clickhouse-server/config.xml | grep 9005 | head -1
<postgresql_port>9005</postgresql_port>
root@03e3bb891946:/# cat /etc/clickhouse-server/config.xml | grep 8123 | head -1
<http_port>8123</http_port>
root@03e3bb891946:/#
We can summarise these ports and their functionality in the following table:
| Port | Functionality |
|---|---|
| 9009 | Low-level data access used for data exchange, replication, and general inter-server communication |
| 9000 | Native protocol port (also referred to as ClickHouse TCP protocol). Used by ClickHouse apps and processes like clickhouse-server, clickhouse-client, and native ClickHouse tools. used for inter-server communication for distributed queries |
| 9004 | MySQL emulation port that can be used to access ClickHouse using mysql client |
| 9005 | PostgreSQL emulation port that can be used to access ClickHouse using psql client |
| 8123 | HTTP protocol port used by web interfaces, JDBC, and ODBC |
All the ports that can be opened
However, ClickHouse supports many more interfaces. There are seventeen different ports, including the five ports we see open in our container by default.
Here is the full table from the documentation:
| Port | Description |
|---|---|
| 2181 | Zookeeper default service port. Note: see 9181 for ClickHouse Keeper |
| 8123 | HTTP API Port for HTTP requests used by JDBC, ODBC and web interfaces |
| 8443 | HTTP SSL/TLS port default port |
| 9000 | Native Protocol port (also referred to as ClickHouse TCP protocol). Used by ClickHouse apps and processes like clickhouse-server, clickhouse-client, and native ClickHouse tools. used for inter-server communication for distributed queries |
| 9004 | MySQL emulation port |
| 9005 | PostgreSQL emulation port (also used for secure communication if SSL is enabled for ClickHouse) |
| 9009 | Inter-server communication port for low-level data access. used for data exchange, replication, and inter-server communication |
| 9010 | SSL/TLS for inter-server communications |
| 9011 | Native protocol PROXYv1 protocol port |
| 9019 | JDBC Bridge |
| 9100 | gRPC port |
| 9181 | Recommended ClickHouse Keeper port |
| 9234 | Recommended ClickHouse Keeper Raft port (also used for secure communication if 1 enabled) |
| 9363 | Prometheus default metrics port |
| 9281 | Recommended Secure SSL ClickHouse Keeper port |
| 9440 | Native protocol SSL/TLS port |
| 42000 | Graphite default port |
Customising ClickHouse networking configuration
You can customise network settings by adding a file to the /etc/clickhouse-server/config.d/ folder that can add to, replace, or remove an entry from the default /etc/clickhouse-server/config.xml configuration file. You can find details about how to work with the configuration files at https://clickhouse.com/docs/en/operations/configuration-files, but you will see examples as we go along.
If we check the contents of the /etc/clickhouse-server/config.d folder, we will see that there is already one file named docker_related_config.xml.
root@03e3bb891946:/# ls -la /etc/clickhouse-server/config.d total 12 drwxrwxrwx 1 clickhouse clickhouse 4096 Apr 13 23:52 . drwxrwxrwx 1 clickhouse clickhouse 4096 Apr 13 23:52 .. -rw-rw-r-- 1 root root 314 Apr 13 23:52 docker_related_config.xml
If we look at its contents, we will see that it sets the <listen_host> parameter, which is set to allow connections from any host on any network.
root@03e3bb891946:/# cat /etc/clickhouse-server/config.d/docker_related_config.xml
<clickhouse>
<!-- Listen wildcard address to allow accepting connections from other containers and host network. -->
<listen_host>::</listen_host>
<listen_host>0.0.0.0</listen_host>
...
</clickhouse>
The <listen_host> setting is very important and, in addition to open ports, allows to lock down networking by specifying an IP address that will determine the network interface the server will listen on for requests. In this case, we have two entries. The first entry is for IPv6, and the :: value specifies a reserved address containing all 0’s expressed in a compressed format, and the second entry is for IPv4 with the reserved address 0.0.0.0. These entries instruct the server to bind to all the interfaces for IPv6 and IPv4.
We will discuss the <listen_host> and its companion, the <interserver_listen_host> setting, in more detail in their own section, where we talk about locking down allowed hosts and networks.
Removing, changing, and adding open ports
The first step in locking down our ClickHouse server is to remove any unnecessary open ports. In order to accomplish this, we will have to add custom configuration files to /etc/clickhouse-server/config.d/. By default, we have seen that we have MySQL, port 9004, and PostgreSQL, port 9005, protocol emulation ports open. If we don’t need to connect to our ClickHouse server using these protocols, then we should remove these ports.
To be fair, in our example, we are running ClickHouse inside a container, and we could in theory leave these ports open, as by default the container doesn’t expose any of its ports to the outside world. We could use the --publish or -p flag to make only ports 9000, 9009, and 8123 available on the host. However, disabling unnecessary ports is still a good practice, and if you are not running ClickHouse inside the container, then it’s definitely something you should do.
Removing open ports
Given that we are running our ClickHouse in a container, we need to mount our configuration files from some host folder. We can disable MySQL and PostgreSQL emulation ports using the following configuration file that we’ll create inside our local config.d directory.
mkdir config.d cat << EOF > ./config.d/disable_mysql_and_postresql_ports.xml <clickhouse> <!-- Disable MySQL and PostreSQL emulation ports --> <mysql_port remove="true"/> <postgresql_port remove="true"/> </clickhouse> EOF
Note that inside our ./config.d/disable_mysql_and_postresql_ports.xml file, we have used the remove attribute to delete the <mysql_port> and the <postgresql_port> settings from the configuration. Similar to the remove attribute, there is also a replace attribute that can be used to replace an entire element with the specified one. But in our case, the remove attribute is what we wanted. Now, we have to restart our container and mount our configuration files into the container. At first glance, there is a temptation to mount our configuration files as -v $(pwd)/config.d:/etc/clickhouse-server/config.d but this will override the whole /etc/clickhouse-server/config.d folder inside the container, including the default /etc/clickhouse-server/config.d/docker_related_config.xml, which we do not want to do.
However, there is an undocumented feature that, in addition to the /etc/clickhouse-server/config.d folder, we can also place custom configs into the /etc/clickhouse-server/conf.d folder as well! This is what we’ll do to preserve any default configuration files inside the /etc/clickhouse-server/config.d provided by the image.
Let’s stop our container and restart it with the following command that adds our config folder volume mount.
docker run -d -v $(pwd)/config.d:/etc/clickhouse-server/conf.d altinity/clickhouse-server:22.8.15.25.altinitystable
Login to the container as before, and let’s check what files we now have inside the /etc/clickhouse-server folder.
root@dac862701053:/# find /etc/clickhouse-server/ /etc/clickhouse-server/ /etc/clickhouse-server/users.d /etc/clickhouse-server/config.d /etc/clickhouse-server/config.d/docker_related_config.xml /etc/clickhouse-server/users.xml /etc/clickhouse-server/config.xml /etc/clickhouse-server/conf.d /etc/clickhouse-server/conf.d/disable_mysql_and_postresql_ports.xml
We can see that /etc/clickhouse-server/conf.d/disable_mysql_and_postresql_ports.xml is present, as is the default /etc/clickhouse-server/config.d/docker_related_config.xml, which we did not want to override.
When you work with ClickHouse configuration files, it is important to know that the final config.xml that will be used by ClickHouse is located at /var/lib/clickhouse/preprocessed_configs/config.xml. This file will be the final configuration file that contains the configuration after merging, removing, or replacing entries from all the different XML and YAML files that ClickHouse finds.
Let’s make sure the <mysql_port> and the <postresql_port> settings were successfully removed from our configuration by looking for these entries inside the /var/lib/clickhouse/preprocessed_configs/config.xml.
root@dac862701053:/# cat /var/lib/clickhouse/preprocessed_configs/config.xml | grep mysql_port root@dac862701053:/# cat /var/lib/clickhouse/preprocessed_configs/config.xml | grep postgresql_port
We can now again install the netstat utility into our container and verify that we only have three ports open instead of five.
root@dac862701053:/# netstat -tulpn | grep LISTEN tcp 0 0 0.0.0.0:9009 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:9000 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:8123 0.0.0.0:* LISTEN - root@dac862701053:/#
Changing open ports
Similar to how we removed MySQL and PostgreSQL ports, we can also change the port number by either using the replace attribute or just adding an updated entry that will be merged with other configuration files to override the corresponding entry. Let’s say that we want to change the default HTTP client port from 8123 to something like 8823.
We can do this by adding the following configuration file to our local config.d folder that is mounted in our container.
cat << EOF > ./config.d/http_port.xml <clickhouse> <!-- Change HTTP client port --> <http_port>8823</http_port> </clickhouse> EOF
Restart the container with the mounted config.d local folder. Check that the new /etc/clickhouse-server/conf.d/http_port.xml configuration file is present inside the container. Also check that the /var/lib/clickhouse/preprocessed_configs/config.xml contains the correct <http_port> port value and that the netstat shows that we have the 8823 port indeed open.
root@5f8c2345f406:/# find /etc/clickhouse-server/ /etc/clickhouse-server/ ... /etc/clickhouse-server/conf.d/http_port.xml /etc/clickhouse-server/conf.d/disable_mysql_and_postresql_ports.xml
root@5f8c2345f406:/# cat /var/lib/clickhouse/preprocessed_configs/config.xml | grep "<http_port>"
<http_port>8823</http_port>
root@5f8c2345f406:/# netstat -tulpn | grep LISTEN tcp 0 0 0.0.0.0:8823 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:9009 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:9000 0.0.0.0:* LISTEN -
As we can see, we now have the 8823 port open for HTTP client connections instead of the default 8123.
Adding open ports
Adding a port is exactly the same as what we have done for changing the value of the <http_port> setting. We just need to add another configuration file and restart our container. For example, let’s enable the Prometheus endpoint by creating the following ./config.d/prometheus.xml file:
cat << EOF > ./config.d/prometheus.xml
<clickhouse>
<prometheus>
<endpoint>/metrics</endpoint>
<port>9363</port>
<metrics>true</metrics>
<events>true</events>
<asynchronous_metrics>true</asynchronous_metrics>
<status_info>true</status_info>
</prometheus>
</clickhouse>
EOF
You can follow the same steps to verify that the configuration has been applied as before.
root@c5796f9d65fe:/# find /etc/clickhouse-server/ .. /etc/clickhouse-server/conf.d/http_port.xml /etc/clickhouse-server/conf.d/prometheus.xml /etc/clickhouse-server/conf.d/disable_mysql_and_postresql_ports.xml
root@c5796f9d65fe:/# cat /var/lib/clickhouse/preprocessed_configs/config.xml | grep "prometheus.xml"
/etc/clickhouse-server/conf.d/prometheus.xml
root@c5796f9d65fe:/# netstat -tulpn | grep LISTEN tcp 0 0 0.0.0.0:9000 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:9009 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:8823 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:9363 0.0.0.0:* LISTEN -
Conclusion
In this first part of the series, we have looked at how you can start locking down ClickHouse networking. As with any topic related to security, this task is not trivial, and security is affected by many configuration settings that can be defined for ClickHouse. Specifically, we have looked at what default ports are open by ClickHouse and how to list them, as well as the description of each port that ClickHouse can expose on a network. Limiting the number of open ports is highly recommended. Now that we are done with selecting the list of necessary ports, in [Part 2], we’ll look at enabling SSL encryption on each publicly facing communication port and also consider taking advantage of private CAs as well as X.509 certificate authentication to provide secure access to the server. Lastly, controlling the network interface to which the server binds will allow us to limit the hosts that can access the servers, but for fine-grained control, we’ll see that we’ll need to set allowed network settings either inside a particular user configuration or use user profiles to share the same settings between different users. Therefore, make sure to continue your journey in understanding ClickHouse network security in the [Part 2] of this article series.