Securing user access is one of the crucial things in the Database environment. Restricting user access based on the needed host will help to block unwanted remote access to the database. In this article, we will explain the different available options in ClickHouse to control user access based on the hosts. This article will cover the following sub-topics.
- Allow access from localhost only.
- Allow access from localhost and remote hosts
- Allow access from a particular remote host only
- Allow the access based on hostname
We know that In ClickHouse, we can control the user access from “config file level” as well as “SQL command shell”. Here, We will explain both options for all the above topics.
Our Setup:
To explain the topics, We have created a lab environment, which has 2 servers.
- access1
- access2
We are going to work with the server “access1”. The other server “access2” will be the remote server to test the remote connections.
By default, ClickHouse will not allow remote connections. To enable it, We have added the following config (listen_host) and allowed the remote connections. Remember, Using “listen_host” We can enable the remote access in server level. We have different settings ( ip, host ) for user specifc access control. 🙂
root@access1:~# less /etc/clickhouse-server/config.xml | grep -i listen_host <listen_host>::</listen_host> root@access1:~# hostname access1 root@access1:~# netstat -tulnp | grep 9000 tcp6 0 0 :::9000 :::* LISTEN 11138/clickhouse-se
We have created the following ClickHouse user “chista” to test all the scenarios. ( file: /etc/clickhouse-server/users.xml ).
<users> .. <chista> <password>chista</password> <networks> <ip></ip> </networks> <profile>default</profile> <quota>default</quota> </chista> .. </users>
Allow access from localhost only:
To allow the access only from localhost, we have to update the network settings for the user as following.
file level,
<networks> <ip>::1</ip> <ip>127.0.0.1</ip> </networks>
“::1” for IPv6 and “127.0.0.1” for IPv4 connections.
SQL command Shell, ( HOST LOCAL )
access1 :) CREATE USER chista_SQL HOST LOCAL IDENTIFIED WITH plaintext_password BY 'Chista@321'; CREATE USER chista_SQL IDENTIFIED WITH plaintext_password BY 'Chista@321' HOST LOCAL Query id: 1afa6514-392a-43cb-b7ab-f2ba19a1ca05 Ok. 0 rows in set. Elapsed: 0.003 sec.
So now we have created two users ( chista, chista_SQL ) using file and command line. Lets test them,
local connections using both users,
root@access1:~# clickhouse-client --user chista --password chista ClickHouse client version 22.8.4.7 (official build). Connecting to localhost:9000 as user chista. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :) root@access1:~# clickhouse-client --user chista_SQL --password Chista@321 ClickHouse client version 22.8.4.7 (official build). Connecting to localhost:9000 as user chista_SQL. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :)
Remote connections from access2 using both users,
root@access2:~# clickhouse-client --user chista --host 192.168.64.22 --password chista ClickHouse client version 22.8.4.7 (official build). Connecting to 192.168.64.22:9000 as user chista. Code: 516. DB::Exception: Received from 192.168.64.22:9000. DB::Exception: chista: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED) root@access2:~# clickhouse-client --user chista_SQL --host 192.168.64.22 --password Chista@321 ClickHouse client version 22.8.4.7 (official build). Connecting to 192.168.64.22:9000 as user chista_SQL. Code: 516. DB::Exception: Received from 192.168.64.22:9000. DB::Exception: chista_SQL: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED)
It illustrates the LOCAL connections are working. REMOTE connections are not working.
Allow access from localhost and remote hosts.
To allow access from localhost and remote hosts, we have to update the network settings for the user as follows.
file level,
<networks> <ip>::/0</ip> </networks>
SQL command shell, ( HOST ANY )
access1 :) CREATE USER chista_SQL HOST ANY IDENTIFIED WITH plaintext_password BY 'Chista@321'; CREATE USER chista_SQL IDENTIFIED WITH plaintext_password BY 'Chista@321' HOST ANY Query id: c26885fc-88b6-4f30-8572-b0f4556be894 Ok. 0 rows in set. Elapsed: 0.004 sec.
So now we have created two users ( chista, chista_SQL ) using file and command line. Lets test them,
Local connections using both users,
root@access1:~# clickhouse-client --user chista --password chista ClickHouse client version 22.8.4.7 (official build). Connecting to localhost:9000 as user chista. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :) root@access1:~# clickhouse-client --user chista_SQL --password Chista@321 ClickHouse client version 22.8.4.7 (official build). Connecting to localhost:9000 as user chista_SQL. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :)
Remote connections using both users,
root@access2:~# clickhouse-client --user chista --host 192.168.64.22 --password chista ClickHouse client version 22.8.4.7 (official build). Connecting to 192.168.64.22:9000 as user chista. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :) root@access2:~# clickhouse-client --user chista_SQL --host 192.168.64.22 --password Chista@321 ClickHouse client version 22.8.4.7 (official build). Connecting to 192.168.64.22:9000 as user chista_SQL. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :)
It illustrates both the LOCAL and REMOTE connections are working fine with this approach.
Allow the access from particular remote host only
Ok, now lets allow the access from only remote host ( acess2 ). Not localhost.
“192.168.64.23” is the IP for access2.
root@access2:~# hostname -I 192.168.64.23
To do this, we have to update the network settings for the user as following.
file level,
<networks> <ip>192.168.64.23</ip> </networks>
SQL command shell, ( HOST IP )
access1 :) CREATE USER chista_SQL HOST IP '192.168.64.23' IDENTIFIED WITH plaintext_password BY 'Chista@321'; CREATE USER chista_SQL IDENTIFIED WITH plaintext_password BY 'Chista@321' HOST IP '192.168.64.23' Query id: 621d01fb-5e85-4dd3-8ba2-2b254f56a03e Ok. 0 rows in set. Elapsed: 0.008 sec.
Lets test the access now,
Local connections,
root@access1:~# clickhouse-client --user chista --password chista ClickHouse client version 22.8.4.7 (official build). Connecting to localhost:9000 as user chista. Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: chista: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED) root@access1:~# root@access1:~# clickhouse-client --user chista_SQL --password Chista@321 ClickHouse client version 22.8.4.7 (official build). Connecting to localhost:9000 as user chista_SQL. Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: chista_SQL: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED)
Remote connections,
Note: To test this effectively, We have tried to access from another remote host as well. Hostname is “python”
root@access2:~# clickhouse-client --user chista --host 192.168.64.22 --password chista ClickHouse client version 22.8.4.7 (official build). Connecting to 192.168.64.22:9000 as user chista. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :) root@access2:~# clickhouse-client --user chista_SQL --host 192.168.64.22 --password Chista@321 ClickHouse client version 22.8.4.7 (official build). Connecting to 192.168.64.22:9000 as user chista_SQL. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :) root@python:~# clickhouse-client --user chista --host 192.168.64.22 --password chista ClickHouse client version 22.8.5.29 (official build). Connecting to 192.168.64.22:9000 as user chista. Code: 516. DB::Exception: Received from 192.168.64.22:9000. DB::Exception: chista: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED) root@python:~# clickhouse-client --user chista_SQL --host 192.168.64.22 --password Chista@321 ClickHouse client version 22.8.5.29 (official build). Connecting to 192.168.64.22:9000 as user chista_SQL. Code: 516. DB::Exception: Received from 192.168.64.22:9000. DB::Exception: chista_SQL: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED)
As we see above, the access is rejected from hosts “access1” and “python”. It is only allowed from “access2” based on our configuration.
Allow the access based on hostname
To make this happens, first we have to resolve a hostname into an IP address. We can do this configuration on the file “/etc/hosts”.
For example, We are trying to access the hosts “access1” from “access2”. The hosts “access1” and “access2” has the following configuration. We have to whitelist this on both the servers.
root@access1:~# hostname access1 root@access1:~# hostname -I 192.168.64.22 root@access2:~# hostname access2 root@access2:~# hostname -I 192.168.64.23
At “access1 & access2”, we need to make the following entries.
root@access1:~# less /etc/hosts | grep -i access 192.168.64.22 access1 access1 192.168.64.23 access2 access2 root@access2:~# less /etc/hosts | grep -i access 192.168.64.22 access1 access1 192.168.64.23 access2 access2
Now, we can communicate using hostname without providing the IP as the following telnet test.
oot@access2:~# telnet access1 9000 Trying 192.168.64.22... Connected to access1.
Now, lets come to the ClickHouse part. At ClickHouse config, we have to add the following config to allow the access.
File-level,
<networks> <host>access2</host> </networks>
SQL command shell, ( HOST NAME )
access1 :) CREATE USER chista_SQL HOST NAME 'access2' IDENTIFIED WITH plaintext_password BY 'Chista@321'; CREATE USER chista_SQL IDENTIFIED WITH plaintext_password BY 'Chista@321' HOST NAME 'access2' Query id: 170d6fea-36e9-48f7-a557-46dff335c5ce Ok. 0 rows in set. Elapsed: 0.004 sec.
Lets test this using the hostname.
root@access2:~# clickhouse-client --user chista --host access1 --password chista ClickHouse client version 22.8.4.7 (official build). Connecting to access1:9000 as user chista. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :) root@access2:~# clickhouse-client --user chista_SQL --host access1 --password Chista@321 ClickHouse client version 22.8.4.7 (official build). Connecting to access1:9000 as user chista_SQL. Connected to ClickHouse server version 22.8.4 revision 54460. access1 :)
As showed above, We used the “–host access1” instead of IP to connect the host remotely. It is working as expected.