1. Home
  2. Knowledge Base
  3. ClickHouse
  4. Allow ClickHouse users to accept the connections from specific hosts
  1. Home
  2. Knowledge Base
  3. ClickHouse DBA
  4. Allow ClickHouse users to accept the connections from specific hosts

Allow ClickHouse users to accept the connections from specific hosts

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.

 

 

 

 

 

 

 

Was this article helpful?
Yes No

Related Articles

CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES

In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

256 Chapman Road STE 105-4, Newark, New Castle, Delaware, 19702, United States | ©2024 ChistaDATA Inc. All rights reserved. | ClickHouse is a trademark of ClickHouse Corporation. All other trademarks are property of their respective owners. Other product or company names mentioned may be trademarks or trade names of their respective owner.