Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 35 ยท Architecture and Setup

Settings, Users and Configuration

Learn how ClickHouse settings work at server, user and query level, how to create users and roles with GRANT, use profiles and quotas, and organise config files.

A ClickHouse server is configured at three levels: server configuration files (ports, paths, clusters), users and access control (who can do what), and settings (how a query behaves). Knowing which level a knob belongs to saves a lot of hunting. This lesson walks through all three, with the safety habits you want before you let anyone else connect.

ClickHouse cannot run on this site. All configuration and statements below are read-only. The runnable blocks use SQLite to model a permissions table and a settings lookup, so you can practise reasoning about precedence.

Settings: three places, one winner

A setting is a named option such as max_threads, max_memory_usage or max_execution_time. You can set it in several places; the most specific one wins:

flowchart LR
    A["Server default"] --> B["User profile"]
    B --> C["Session SET"]
    C --> D["Query SETTINGS clause"]

Each arrow overrides the one before it. The query-level value wins.

SQL
-- for one query
SELECT count() FROM events SETTINGS max_threads = 4, max_execution_time = 30;

-- for the rest of the session
SET max_memory_usage = 10000000000;

-- inspect
SELECT name, value, changed, description
FROM system.settings
WHERE name LIKE 'max_execution%';

Settings with a changed = 1 flag differ from the default. Many settings accept human-friendly values in the client but plain numbers in config (bytes, seconds).

Useful ones to know early:

Setting Meaning
max_threads Threads per query
max_memory_usage Bytes of RAM one query may use before it is killed
max_execution_time Seconds before a query is cancelled
max_rows_to_read, max_bytes_to_read Refuse queries that scan too much
readonly 1 = read-only session, 2 = read-only but may change settings
async_insert Batch small inserts on the server
join_algorithm How joins are executed

Server configuration files

The main files live in /etc/clickhouse-server/:

  • config.xml defines ports, paths, logging, clusters and macros.
  • users.xml can define users and profiles (many setups use SQL-driven access control instead).
  • config.d/*.xml and users.d/*.xml are override fragments merged into the main files in alphabetical order. YAML is also supported.

Put your changes in a fragment, never in the shipped config.xml, so upgrades do not overwrite them:

XML
<!-- /etc/clickhouse-server/config.d/listen.xml -->
<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <max_concurrent_queries>200</max_concurrent_queries>
    <logger>
        <level>information</level>
    </logger>
</clickhouse>

Most server settings are read at startup; many reload without a restart (SYSTEM RELOAD CONFIG). Check the docs for each one. Bind to 0.0.0.0 only behind a firewall and with real authentication.

Users, roles and GRANT

Modern ClickHouse uses SQL-driven access control: you manage users, roles and privileges with statements, and they are stored on the server.

SQL
CREATE USER analyst IDENTIFIED WITH sha256_password BY 'choose-a-long-secret';

CREATE ROLE reporting;
GRANT SELECT ON shop.* TO reporting;
GRANT reporting TO analyst;

CREATE USER loader IDENTIFIED WITH sha256_password BY 'another-long-secret';
GRANT INSERT ON shop.events TO loader;

SHOW GRANTS FOR analyst;

Replace the secrets with your own and keep them in a secret manager. Key ideas:

  • Privileges attach to a database, a table or even a column (GRANT SELECT(user_id, ts) ON shop.events).
  • Give each application its own user with the least privilege it needs: a loader that can INSERT only, a dashboard user that can SELECT only.
  • Restrict where a user can connect from with HOST IP '10.0.0.0/8'.
  • Row policies limit which rows a user sees: CREATE ROW POLICY eu_only ON shop.events FOR SELECT USING region = 'EU' TO analyst.

Profiles and quotas

A settings profile is a named bundle of settings that you attach to users or roles. A quota caps how much a user may consume in a time window.

SQL
CREATE SETTINGS PROFILE dashboards
    SETTINGS max_memory_usage = 4000000000 MAX 8000000000,
             max_execution_time = 60 MAX 120,
             readonly = 2
    TO analyst;

CREATE QUOTA dashboard_quota
    FOR INTERVAL 1 hour MAX queries = 500, result_rows = 100000000
    TO analyst;

MAX makes a ceiling the user cannot raise with SET. Profiles and quotas stop one careless query from taking the whole server down, and they matter most once real users connect.

Practise with a stand-in permission table

ClickHouse stores grants in its own system tables. We can model the logic of roles and a privilege check with SQLite tables, to practise reasoning about who may do what.

SQL Stand-in: who can SELECT? (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: precedence of settings (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

System tables for self-inspection

SQL
SELECT * FROM system.users;                     -- accounts
SELECT * FROM system.grants WHERE user_name = 'analyst';
SELECT * FROM system.processes;                 -- running queries
SELECT query, query_duration_ms, read_rows, memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY event_time DESC LIMIT 10;
KILL QUERY WHERE query_id = '...';              -- stop a runaway query

system.query_log records every finished query with its time, rows read and memory used. It is the first place to look when something is slow.

Common beginner mistakes

  • Editing config.xml and losing the change on upgrade.
  • Leaving the default user without a password, reachable from the network.
  • Giving every application an administrator account.
  • Not setting max_memory_usage or max_execution_time for dashboard users.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which setting source wins when a value is set in several places?

    Server default, then profile, then session SET, then query SETTINGS; each overrides the previous.
  2. 2. Where should you put your own server configuration changes?

    Fragments are merged in and survive package upgrades.
  3. 3. Which statement gives a role read access to a database?

    GRANT assigns privileges to a user or role on an object such as shop.*.
  4. 4. What does the MAX modifier in a settings profile do?

    A constraint like MAX 8000000000 prevents the user from exceeding that value.

Summary

  • Settings apply at server, profile, session and query level; the most specific wins.
  • Put server configuration in config.d/ and users.d/ fragments.
  • Manage users, roles and grants with SQL; give each application least privilege.
  • Profiles and quotas cap memory, time and usage.
  • system.query_log and system.processes show what is running and what was slow.

Next: MergeTree: ORDER BY and the primary key