1. Home
  2. Knowledge Base
  3. ClickHouse User Security Guide

ClickHouse User Security Guide

In this article we will talk about how to secure your users and will enrich article with examples.

First lets start with users security options.

Increasing ClickHouse security at the user level involves the following steps:

User Configuration: Setup secure default users, roles and permissions through configuration or SQL.

User Network Settings: Limit communications by hostname or IP address

Secure Password: Store user informations as hashed.

Quotas: Limit how many resources users can use in given intervals.

Profiles: Use profiles to set common security settings across multiple accounts.

Database Restrictions: Set limitation the databases, tables and rows that a user can access.

Enable Remote Authentication: Enable LDAP authentication or Kerberos authentication to prevent storing hashed password information, and enforce password standards.

User XML Settings

Users informations stored inside the  user.xml file under the users element.

It is recommended that when creating users, rather than lumping them all into the user.xml file is to place them as separate XML files under the directory users.d, typically located in /etc/clickhouse-server/users.d/.

 

For example, the following section will create two users called ClickHouse and Paul:

  • clickhouse: This user has the password clickhouse_password stored in a sha256 hash, is assigned the profile clickhouse, and can access the ClickHouse database from any network host.
  • Paul: User can only access the database from localhost, has a basic password of Paul and is assigned to the default profile.
<users>
    <clickhouse>
        <networks>
            <ip>127.0.0.1</ip>
            <ip>0.0.0.0/0</ip>
            <ip>::/0</ip>
        </networks>          
        <password_sha256_hex>716b36073a90c6fe1d445ac1af85f4777c5b7a155cea359961826a030513e448</password_sha256_hex>
        <profile>clickhouse_operator</profile>
        <quota>default</quota>
    </clickhouse>
    <Paul>
        <networks>
            <ip>127.0.0.1</ip>
        </networks>
        <password_sha456_hex>73d1b1b1bc1dabfb97f216d897b7968e44b06457920f00f2dc6c1ed3be25ad4c</password_sha256_hex>
        <profile>default</profile>
    </Paul>
</users>

User SQL Settings

Access management must be enabled at the user level with the access_management setting. In this example, Access Management is enabled for the user Paul:

 

<users>
    <Paul>
       <access_management>1</access_management>
    </Paul>
</users>

 

Once Access Management is enabled, settings can be managed through SQL commands. For example, to create a new user called Michelle with their password set as a sha256 hash and restricted to a specific IP address subnet, the following SQL command can be used:

 

CREATE USER IF NOT EXISTS Michelle
  IDENTIFIED WITH SHA256_PASSWORD BY 'verysecretpassword'
  HOST IP '192.168.22.47/24' SETTINGS readonly=1;

User Network Settings

Users can have their access to the ClickHouse environment restricted by the network they are accessing the network from. Users can be restricted to only connect from:

  • IP: IP address or netmask.
    • For all IP addresses, use 0.0.0.0/0 for IPv4, ::/0 for IPv6
  • Host: The DNS resolved hostname the user is connecting from.
  • Host Regexp (Regular Expression): A regular expression of the hostname.

Accounts should be restricted to the networks that they connect from when possible.

 

User Network SQL Settings

For example, to restrict the user Paul to only connect from the local subnet of ‘192.168.0.0/16’:

ALTER USER Paul
  HOST IP '192.168.0.0/16';

Or to restrict this user to only connecting from the specific host names chistadata1.comchistadata2.com, etc:

ALTER USER Paul HOST REGEXP 'chistadata[1234].com';

User Network XML Settings

User network settings are stored under the user configuration files /etc/clickhouse-server/config.d with the <networks> element.

For example, the following will allow only from localhost:

<networks>
    <ip>127.0.0.1</ip>
</networks>

The following will restrict the user only to the site example.com or from chistadata1.com, chistadata2.com:

<networks>
    <host>example.com</host>
    <host_regexp>chistadata[1234].com</host_regexp>
</networks>

Secure Password

Passwords can be stored in plaintext or SHA256.

SHA256 format passwords are labeled with the <password_sha256_hex> element in the config file. SHA256 password can be generated through the following command:

To generate a SHA256 password please run the following command:

echo -n "password" | sha256sum | tr -d '-'

or

echo -n "PasswordForUserPaul" | shasum -a 256 | tr -d '-'
c15bc95c03932a67f8c3d6b6370e44416b62fd690982c591754afb2f375cdc36

 

Secure Password SQL Settings

Passwords can be set when using the CREATE USER OR ALTER USER with the IDENTIFIED WITH option. For complete details, see the ClickHouse Official Create User page.

For example, to store the sha256 hashed value of “password” for the user Paul:

ALTER USER Paul IDENTIFIED WITH sha256_hash BY '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8';

Secure Password XML Settings

Passwords can be set as part of the user’s settings in the user configuration files in /etc/clickhouse-server/config.d. For complete details, see the Clickhouse Offical User Settings Page.

For example, to set the sha256 hashed value of “password” for the user Paul:
<users>
    <John>
        <password_sha256_hex>5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8</password_sha256_hex>
    </John>
</users>

Set Quotas

Quotas set how many resources can be accessed in a given time, limiting a user’s ability in the system.  To more details please visit ClickHouse Official Quotas page.

Quota XML Settings

Quotas are set by intervals, which can be set to different restrictions. For example, this quota named limited has one interval that sets maximum queries at 1000, and another interval that allows a total of 10000 queries over a 24 hour period.

<quotas>
    <limited>
        <interval>
            <duration>3600</duration>
            <queries>1000</queries>
        </interval>
        <interval>
            <duration>86400</duration>
            <queries>10000</queries>
    </limited>
</quotas>

 

Use Profiles

Profiles allow settings that can be applied to multiple uses applied with the same name. To more details please visit Official ClickHouse Docs.

Profile XML Settings

Profiles are applied to a user with the profile element. For example, this assigns the restricted profile to the user Paul:

<users>
    <Paul>
        <networks>
            <ip>127.0.0.1</ip>
            <ip>0.0.0.0/0</ip>
            <ip>::/0</ip>
        </networks>
        <password_sha256_hex>c15bc95c03932a67f8c3d6b6370e44416b62fd690982c591754afb2f375cdc36</password_sha256_hex>
        <profile>restricted</profile>

 

The profile restricted shown here only allows for eight threads to be used at a time for users with this profile:
<profiles>
    <restricted>
        <!-- The maximum number of threads when running a single query. -->
        <max_threads>8</max_threads>
    </default>
</profiles>

Database Restrictions

Restrict users to the databases they need, and when possible only the tables or rows within tables that they require access to.

To more details please visit ClickHouse Official Docs.

Database Restrictions XML Settings

The following restricts the user Paul to only access the database employee, and from there only the table marked clients where employee = 'Paul':

<Paul>
    <databases>
        <employee>
            <clients>
                <filter>employee = 'Paul'</filter>
            </clients>
        </employee>
    </databases>
</Paul>

Enable Remote Authentication

One method of reducing the exposure of user passwords is to use external authentication sources. This avoids storing password data on local file systems and allows changes in user authentication to be managed from a single source.

Enable LDAP

LDAP servers are defined in the ClickHouse configuration settings like /etc/clickhouse-server/config.d/ldap.xml. For more details, check the ClickHouse Official Docs.

To add one or more LDAP servers to your ClickHouse environment, each node will require the ldap settings:

<ldap>
    <server>ldapserver_hostname</server>
        <roles>
            <my_local_role1 />
            <my_local_role2 />
        </roles>
</ldap>

When creating users, specify the ldap server for the user:

create user if not exists newUser
    identified with ldap by 'ldapserver_hostname'
    host any;

 

Was this article helpful?

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.