clickhouse.guru
Runbooks

Runbook: users, settings and security

Edit a settings profile without wiping it, check a setting's stability tier, GRANT and REVOKE, lock down url() and remote() hosts, and understand the INSERT-to-webhook trick.

Access and limits in ClickHouse are SQL objects: users, roles, grants and settings profiles. They apply instantly, no restart, and are easy to get subtly wrong: a profile edit that silently drops every other limit, an experimental flag turned on for everyone, a GRANT that does not do what you think, a table function that lets any user make the server call an internal HTTP service. This page holds the five checks worth knowing by heart.

What runs where. The settings-profile, tier and GRANT/REVOKE sections were run on ClickHouse 26.4 in clickhouse local with real output. The ON CLUSTER forms need a configured cluster and were syntax-checked with clickhouse format. The host restriction is server configuration (XML) and cannot be tried locally. The webhook example needs an HTTP endpoint that accepts the request; its statements were syntax-checked, and the Null-table-plus-view mechanics it relies on are standard behaviour but were not exercised against a real endpoint.

Related reading: users, roles and settings profiles.

Contents

How do I change one setting in a settings profile without losing the others?

A settings profile bundles limits (memory, threads, execution time) that apply to users or roles. The easy mistake is to alter one value and find that every other value is gone.

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;

ALTER SETTINGS PROFILE dashboard_ro SETTINGS max_memory_usage = 10000000000 CONST;

After the ALTER, system.settings_profile_elements held one row for the profile (max_memory_usage = 10000000000) instead of four. A plain ALTER ... SETTINGS replaces the whole list. Edit in place with ADD, MODIFY and DROP:

ALTER SETTINGS PROFILE dashboard_ro ADD SETTINGS max_threads = 2;
ALTER SETTINGS PROFILE dashboard_ro MODIFY SETTINGS max_execution_time = 120 MIN 1 MAX 600;
ALTER SETTINGS PROFILE dashboard_ro DROP SETTINGS max_threads;
  • ADD SETTINGS adds entries.
  • MODIFY SETTINGS changes the value or constraints of existing entries.
  • DROP SETTINGS removes entries.

Read the result back with SHOW CREATE SETTINGS PROFILE dashboard_ro; or by querying system.settings_profile_elements. After the three edits above the test printed:

CREATE SETTINGS PROFILE `dashboard_ro` SETTINGS max_memory_usage = 10000000000 CONST, max_execution_time = 120. MIN 1. MAX 600.

CONST and READONLY. CONST (earlier spelled READONLY) means the user cannot change the setting in their session, so it works as a limit. Other constraints are MIN, MAX and WRITABLE (the default: the user may change it within bounds). Without a constraint a user can simply run SET max_memory_usage = ... and lift the limit.

On a cluster, without replicated access storage, add ON CLUSTER my_cluster after the profile name so every node gets the change:

ALTER SETTINGS PROFILE dashboard_ro ON CLUSTER my_cluster MODIFY SETTINGS max_memory_usage = 10000000000 CONST;

Is this setting safe to use?

You found a setting in the source or a changelog that the docs do not describe, or an allow_experimental_* flag a feature needs. Ask the server before you ask a forum: system.settings and system.merge_tree_settings have a tier column.

SELECT name, tier, changed, description
FROM system.settings
WHERE name = 'allow_experimental_codecs';

-- MergeTree-level settings live in a second table
SELECT name, tier, value
FROM system.merge_tree_settings
WHERE tier != 'Production';

The first query returned Experimental for allow_experimental_codecs. The tier says what ClickHouse promises:

Tier Meaning
Production stable and safe to use
Beta stable and safe on its own; interactions with other features are less tested
Experimental under development, may break or disappear; try it on a copy of your data
Obsolete no longer has an effect or will be removed; stop setting it

On 26.4, system.settings holds 1344 Production, 22 Beta, 78 Experimental and 111 Obsolete settings (SELECT tier, count() FROM system.settings GROUP BY tier). List the experimental ones on your version:

SELECT name FROM system.settings WHERE tier = 'Experimental' ORDER BY name;

If a setting is missing from the docs and its tier is Production, the omission is probably an oversight; ask in an issue. If the tier is Experimental, do not use it on data you cannot rebuild. The name allow_experimental_* does not tell you the tier: on 26.4 allow_experimental_analyzer is Production, allow_experimental_codecs is Experimental, and allow_experimental_annoy_index or allow_experimental_bigint_types are Obsolete (the feature graduated or went away).

Enabling an experimental feature: per query with SET allow_experimental_xxx = 1 or a SETTINGS clause. For every query of a user, put it in the user’s settings profile in users.d/:

<clickhouse>
    <profiles>
        <default>
            <allow_experimental_xxx>1</allow_experimental_xxx>
        </default>
    </profiles>
</clickhouse>

(Replace xxx with the real flag name, and use a profile other than default if only some users should get it.) A flag whose tier is Obsolete can simply be removed from your config.

How do I grant and revoke SELECT on a table?

SQL access control is the recommended way to manage permissions: it applies instantly and is replicated if your access storage is replicated.

GRANT SELECT ON analytics.events TO alice;

ON analytics.* covers a whole database, ON *.* everything. Grant to a role instead of a user to keep permissions manageable:

CREATE ROLE analyst;
GRANT SELECT ON analytics.events TO analyst;
GRANT analyst TO alice;

Revoke:

REVOKE SELECT ON analytics.events FROM analyst;

Revoking from a narrower scope than the grant is allowed. After GRANT SELECT ON analytics.* TO analyst, running REVOKE SELECT ON analytics.events FROM analyst leaves the role with SELECT on the database minus that table. Tested:

SHOW GRANTS FOR analyst;
GRANT SELECT ON analytics.* TO analyst
REVOKE SELECT ON analytics.events FROM analyst

Check what was granted, in a table you can filter:

SELECT user_name, role_name, access_type, database, `table`, is_partial_revoke
FROM system.grants
WHERE role_name = 'analyst' OR user_name = 'alice';

The partial revoke appears as a second row with is_partial_revoke = 1. Note that the grant also carries GRANT analyst TO alice as a role membership, which lives in system.role_grants, not in system.grants.

On a cluster, without a replicated access storage, grants live on the node where you ran them. Apply them everywhere with ON CLUSTER:

GRANT ON CLUSTER my_cluster SELECT ON analytics.events TO analyst;
REVOKE ON CLUSTER my_cluster SELECT ON analytics.events FROM analyst;

With a replicated access storage (<user_directories><replicated>), ON CLUSTER is unnecessary and the grant reaches all nodes by itself. The user-and-roles tutorial walks through the whole picture of users, roles, profiles and quotas: users, roles and settings profiles.

How do I restrict the hosts url() and remote() can reach?

By default a user with the right grants can make the server fetch any URL (url(), the URL engine, s3-style functions). SELECT * FROM url('http://internal-service/...', 'CSV') is a classic way to reach a cloud metadata service or an internal HTTP service from inside the network. remote_url_allow_hosts closes that. In the server config (for example /etc/clickhouse-server/config.d/url.xml):

<clickhouse>
    <remote_url_allow_hosts>
        <host>data.example.com</host>
        <host>raw.githubusercontent.com</host>
    </remote_url_allow_hosts>
</clickhouse>

Once the element exists, only the hosts in it are reachable; an empty element allows nothing:

DB::Exception: URL "http://localhost:8123" is not allowed in configuration file,
see <remote_url_allow_hosts>.

Matching rules:

  • A <host> is compared exactly as written in the URL, before DNS resolution: example.com, example.com. and www.example.com are three different hosts.
  • Without a port, any port of that host is allowed. With a port (example.com:8443), only that one.
  • IP literals are matched as written in the URL.
  • For patterns, use <host_regexp> (RE2). Regexps are not anchored: write ^...$ and escape the dots, or example.com also matches exampleXcom.evil.net.
  • If redirects are followed, every redirect target is checked as well.
<remote_url_allow_hosts>
    <host_regexp>^([a-z0-9-]+\.)?example\.com$</host_regexp>
</remote_url_allow_hosts>

Prefer an allow-list. A regex that forbids “everything starting with 127.” is possible but fragile; list what is allowed instead. Reload the config (see changing config without a restart) or restart for the change to apply. Also revoke the URL / REMOTE source privileges from users who do not need them with REVOKE ... ON *.*, so the setting is a second line of defence rather than the only one. For what remote() itself does, see querying another server.

How do I call a webhook from an INSERT?

A table with the URL engine sends every inserted row to an HTTP endpoint as a POST in the format you choose. Chain a materialized view to it and an insert into a trigger table becomes an HTTP call. The statements below were checked with clickhouse format; running them needs an endpoint that accepts the request.

-- 1. the endpoint, as a table: INSERT = POST
CREATE TABLE analytics.webhook_target
(
    bucket String,
    path   String
)
ENGINE = URL('https://hooks.example.com/ingest', 'JSONEachRow');

-- 2. a trigger table that stores nothing
CREATE TABLE analytics.webhook_caller
(
    bucket String,
    path   String
)
ENGINE = Null;

-- 3. every row inserted into the trigger is forwarded to the endpoint
CREATE MATERIALIZED VIEW analytics.webhook_caller_mv
TO analytics.webhook_target AS
SELECT bucket, path
FROM analytics.webhook_caller;

Fire it:

INSERT INTO analytics.webhook_caller VALUES ('b1', 'a/b.csv');

The endpoint receives {"bucket":"b1","path":"a/b.csv"}. This is a sharp tool:

  • The call is synchronous inside the insert: a slow or failing endpoint makes the insert slow or failed, and (for a materialized view on a real source table) can fail the insert into the source too.
  • There is no retry and no delivery guarantee. For anything important, queue the events in a table and have a worker consume them.
  • Every insert block triggers calls; one row per call is not guaranteed, so make the receiver accept batches of the format’s lines.
  • Outbound hosts can be restricted by the server, which also stops this: see the previous section.

runbooksecurity

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.