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.com, chistadata2.com, etc:

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

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

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

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

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

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

<users>
    <John>
        <password_sha256_hex>5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8</password_sha256_hex>
    </John>
</users>

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

<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>

<profiles>
    <restricted>
        <!-- The maximum number of threads when running a single query. -->
        <max_threads>8</max_threads>
    </default>
</profiles>

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

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

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