Users, roles and settings profiles

ClickHouse has two ways to manage access: SQL statements (CREATE USER, GRANT, …) and XML files. SQL is what you should use for anything new. It takes effect immediately, needs no file editing or reload, and can be replicated across nodes. This tutorial builds a read-only analytics user from scratch and runs on clickhouse local, so you can follow along without a server.

Everything below uses the demo analytics database with the events and metrics tables from the other tutorials.

The four building blocks

Object	What it is
User	An identity that authenticates, optionally restricted by host.
Role	A named bundle of privileges you grant to users.
Settings profile	A named set of settings and constraints (memory, threads, time limits) applied to users or roles.
Quota	A cap on cumulative consumption (queries, rows, errors) per time interval.

Privileges go on roles, not on individual users. When a person leaves or changes teams you change one grant, not fifty.

Step 1: a settings profile with limits

A profile with limits is the guard rail for shared dashboards and BI tools. Mark the values CONST so users cannot override them with SET in their session. The older spelling of the same keyword, READONLY, is still accepted.

CREATE SETTINGS PROFILE dashboard_ro
    SETTINGS max_threads = 8 CONST,
             max_memory_usage = 5000000000 CONST,
             max_execution_time = 300 CONST,
             max_result_rows = 1000000 CONST;

Check what you created:

SELECT setting_name, value, writability
FROM system.settings_profile_elements
WHERE profile_name = 'dashboard_ro';

┌─setting_name───────┬─value──────┬─writability─┐
│ max_threads        │ 8          │ CONST       │
│ max_memory_usage   │ 5000000000 │ CONST       │
│ max_execution_time │ 300        │ CONST       │
│ max_result_rows    │ 1000000    │ CONST       │
└────────────────────┴────────────┴─────────────┘

Besides CONST, a setting can carry MIN and MAX bounds and stay writable within them: max_threads = 4 MIN 1 MAX 8.

Step 2: a role with read access

CREATE ROLE analyst;
GRANT SELECT ON analytics.* TO analyst;

analytics.* is every table in the database, including tables created later. To restrict to one table, use analytics.events.

Step 3: the user

Never put a plaintext password in a file. Use a hashed password type; with SQL, ClickHouse hashes it for you on creation and SHOW CREATE USER never reveals it.

CREATE USER alice
    IDENTIFIED WITH sha256_password BY '<password>'
    DEFAULT ROLE analyst
    SETTINGS PROFILE 'dashboard_ro';

GRANT analyst TO alice;

SHOW CREATE USER alice;
SHOW GRANTS FOR alice;

CREATE USER alice IDENTIFIED WITH sha256_password DEFAULT ROLE analyst SETTINGS PROFILE `dashboard_ro`
GRANT analyst TO alice

Useful variations:

• HOST NAME 'bi.example.com' (or HOST IP '<network>/<mask>') restricts where the user may connect from.
• IDENTIFIED WITH ldap SERVER 'my_ldap' or Kerberos / SSL certificates delegate authentication.
• ON CLUSTER my_cluster creates the user on every node when access storage is local to each node (needs a configured cluster).

Step 4: a quota

A quota limits consumption over a period, where a profile limits a single query.

CREATE QUOTA dashboard_quota
    FOR INTERVAL 1 hour MAX queries = 1000, result_rows = 100000000
    TO alice;

Step 5: change things later

Profiles are the place where a classic mistake happens. A plain ALTER ... SETTINGS replaces the whole list. Use ADD, MODIFY or DROP:

ALTER SETTINGS PROFILE dashboard_ro MODIFY SETTINGS max_memory_usage = 10000000000 CONST;
ALTER SETTINGS PROFILE dashboard_ro ADD SETTINGS max_bytes_to_read = 100000000000 CONST;

Grants are revoked the same way they are given:

REVOKE SELECT ON analytics.events FROM analyst;

After this, analyst can read the database except events; SHOW GRANTS FOR analyst prints both the grant and the exclusion.

What a server needs for this to work

SQL-driven access control is off for the default user unless you enable it. Create /etc/clickhouse-server/users.d/access.xml:

<clickhouse>
  <users>
    <default>
      <access_management>1</access_management>
    </default>
  </users>
</clickhouse>

Better still, create a dedicated administrator user with access_management in the XML and lock the default user down, since it has no password out of the box.

The XML alternative

Users defined in XML (users.xml or files in users.d/) are read-only through SQL and loaded when the file changes. Sometimes you want that, for instance when configuration is managed by Ansible or a Kubernetes ConfigMap.

<clickhouse>
  <users>
    <alice>
      <password_sha256_hex><!-- sha256 of the password, hex --></password_sha256_hex>
      <profile>dashboard_ro</profile>
      <networks><host>bi.example.com</host></networks>
      <quota>default</quota>
    </alice>
  </users>
</clickhouse>

Do not use <no_password/> outside a throwaway local setup. If you must, combine it with a <networks> restriction and a read-only profile.

Where to go from here

• Row-level filtering: CREATE ROW POLICY.
• Revoking and inspecting: SELECT * FROM system.grants, system.role_grants, system.users.
• Official docs: access control and settings profiles.