How to Implement Multi‑Tenant Resource Isolation in ClickHouse Using Quotas and Profiles

Introduction

Providing multi‑tenant isolation in ClickHouse is essential to prevent a single user from exhausting database resources and affecting others. Beyond hardware isolation, a cost‑effective method uses ClickHouse's built‑in resource‑limiting mechanisms.

Resource‑Limiting Mechanisms

ClickHouse offers two primary ways to control user resources:

Quotas : limit usage over a time interval (e.g., number of queries per 10 seconds).

Settings Profiles : a collection of settings applied per query, independent of time.

Both can be defined in XML configuration files or managed via SQL.

XML Configuration Example

<?xml version="1.0"?>
<yandex>
  <logger>
    <level>trace</level>
    <log>/tmp/log/clickhouse-server.log</log>
    <errorlog>/tmp/log/clickhouse-server.err.log</errorlog>
    <size>1000M</size>
    <count>10</count>
  </logger>
  <query_log>
    <database>system</database>
    <table>query_log</table>
    <partition_by>toYYYYMM(event_date)</partition_by>
    <flush_interval_milliseconds>1000</flush_interval_milliseconds>
  </query_log>
  <tcp_port>9000</tcp_port>
  <listen_host>127.0.0.1</listen_host>
  <max_concurrent_queries>500</max_concurrent_queries>
  <mark_cache_size>5368709120</mark_cache_size>
  <path>./clickhouse/</path>
  <users_config>users.xml</users_config>
</yandex>

The corresponding users.xml defines profiles, quotas, and users:

<?xml version="1.0"?>
<yandex>
  <profiles>
    <default>
      <readonly>1</readonly>
    </default>
    <pA>
      <max_memory_usage>10G</max_memory_usage>
      <max_memory_usage_for_user>10G</max_memory_usage_for_user>
      <max_memory_usage_for_all_queries>10G</max_memory_usage_for_all_queries>
      <max_query_size>1073741824</max_query_size>
      <readonly>1</readonly>
    </pA>
  </profiles>
  <users>
    <zeppelin>
      <password>password</password>
      <networks>
        <ip>::/0</ip>
      </networks>
      <profile>pA</profile>
      <quota>qA</quota>
    </zeppelin>
  </users>
  <quotas>
    <qA>
      <interval>
        <duration>10</duration> <!-- 10 seconds -->
        <queries>2</queries>
      </interval>
    </qA>
  </quotas>
</yandex>

These files bind the user zeppelin to profile pA and quota qA, limiting the user to two queries every ten seconds.

Profile vs. Quota

Profiles apply settings per query (e.g., readonly), while quotas are time‑based counters. A profile’s max_rows_to_read limits rows read in a single query, not over the user’s lifetime.

Quotas allow you to limit resource usage over a period of time or track the use of resources. A settings profile is a collection of settings grouped under the same name.

Quotas behave similarly to a token‑bucket algorithm: tokens are generated each interval; once exhausted, further queries are rejected until the next interval replenishes the bucket.

SQL‑Driven Access Control

ClickHouse recommends using SQL‑driven workflows for managing users, roles, profiles, and quotas. This approach avoids the rigidity of static XML files and enables dynamic, programmatic changes.

Both of the configuration methods work simultaneously, so if you use the server configuration files for managing accounts and access rights, you can smoothly switch to SQL‑driven workflow.

Step‑by‑Step Solution

Create a super‑admin user ledzeppelin and a regular user zeppelin using SQL.

Revoke all privileges from the default user and make it read‑only via a profile.

Create a settings profile, a quota, and a role, then bind them together.

Grant the role to zeppelin, effectively applying the profile and quota.

CREATE USER ledzeppelin;

GRANT ALL ON *.* TO ledzeppelin WITH GRANT OPTION;

CREATE USER zeppelin;

CREATE SETTINGS PROFILE IF NOT EXISTS z_profile SETTINGS readonly = 2;

CREATE QUOTA IF NOT EXISTS z_quota FOR INTERVAL 10 second MAX queries 1;

CREATE ROLE IF NOT EXISTS z_role;

GRANT SELECT ON db.* TO z_role;

ALTER SETTINGS PROFILE z_profile TO z_role;

ALTER QUOTA z_quota TO z_role;

GRANT z_role TO zeppelin;

If a role needs to be changed, revoke it from the user, modify the profile or quota, then grant the role again.

REVOKE z_role FROM zeppelin;
-- modify profile/quota
GRANT z_role TO zeppelin;

Persistence of SQL‑Created Entities

SQL‑driven access entities are stored under the directory defined by access_control_path (default /var/lib/clickhouse/access/). After a server restart, ClickHouse reloads these files, preserving the configurations.

<yandex>
  ...
  <access_control_path>/var/lib/clickhouse/access/ledzeppelin/</access_control_path>
  ...
</yandex>

Conclusion

The article shows how to start from ClickHouse user resource isolation, use Quotas and Settings Profiles for enforcement, and extend to full user management with roles. Both XML configuration and SQL‑driven methods are viable, with the latter being the recommended, flexible approach for modern deployments.