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:18.104.22.168.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.214.171.124.altinitystable
126.96.36.199.altinitystable: Pulling from altinity/clickhouse-server Digest: sha256:9e5ddb1f26695de16eabbf00548220434535ff9376722d506fb237e02f22ec0c Status: Image is up to date for altinity/clickhouse-server:188.8.131.52.altinitystable docker.io/altinity/clickhouse-server:184.108.40.206.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:220.127.116.11.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:/#
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
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,
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:
|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 |
|9005||PostgreSQL emulation port that can be used to access ClickHouse using |
|8123||HTTP protocol port used by web interfaces, JDBC, and ODBC|
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:
|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|
|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|
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
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>
<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.
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
-p flag to make only ports
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.
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
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:18.104.22.168.altinitystable
Login to the container as before, and let’s check what files we now have inside the
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
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:/#
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
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
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
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 -
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.