Chaining Row Policies for Multi-Tenant Isolation

When a single ClickHouse table holds every tenant’s rows behind one tenant_id column, application-side WHERE tenant_id = ? filters are one forgotten predicate away from leaking one customer’s data to another. Row policies push that filter into the engine itself, where it is appended to every SELECT at parse time and cannot be bypassed by a hand-written query. This guide shows how to chain permissive and restrictive CREATE ROW POLICY statements so each tenant role sees only its own partition of a shared table, how the two policy classes combine (permissive conditions are OR’d, restrictive conditions are AND’d), and how to prove the isolation holds by querying system.row_policies and impersonating a tenant role.

Prerequisites

ClickHouse 23.3+ with the SQL-driven access-control backend enabled (access_management = 1 on the administering user)
A shared multi-tenant MergeTree table carrying an explicit tenant_id column in its schema
Named roles that map one-to-one to tenants (or a role-to-tenant mapping you can express as a filter)
CREATE ROW POLICY and ALTER ROW POLICY privileges for the administering user
A decision on the users_without_row_policies_can_read_rows server setting — it changes what an uncovered user sees (covered in Gotchas)
clickhouse-client access to run SET ROLE and impersonation checks during verification

How permissive and restrictive policies combine

A row policy is a boolean filter attached to a (table, role) pair. ClickHouse evaluates every policy that applies to the current user and folds them into one predicate with a fixed rule: all permissive conditions are combined with OR, all restrictive conditions are combined with AND, and a row is visible only when the permissive group passes and the restrictive group passes. Formally, visible = (P1 OR P2 OR …) AND (R1 AND R2 AND …). Permissive is the default class; restrictive requires the explicit AS RESTRICTIVE clause. That asymmetry is the whole design: permissive policies widen what a role can see (each new tenant grant adds an OR branch), while restrictive policies narrow it for everyone at once (a global guardrail no permissive grant can override).

Step 1 — Confirm the shared table carries a tenant discriminator

Row-level isolation is only as good as the column it keys on. The shared table must store tenant_id as a first-class, non-nullable column, and it should lead the sort key so the appended filter also prunes granules rather than scanning the whole table.

sql

CREATE TABLE IF NOT EXISTS analytics.events
(
    tenant_id   UInt32,
    event_id    UUID,
    event_ts    DateTime64(3),
    event_type  LowCardinality(String),
    is_deleted  UInt8 DEFAULT 0,
    payload     String CODEC(ZSTD(3))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_ts)
ORDER BY (tenant_id, event_type, event_ts);

Because tenant_id is the leading ORDER BY column, a policy filter of tenant_id = 42 restricts the scan to that tenant’s granules, so isolation costs nothing at query time. Confirm the column exists and is populated before attaching any policy:

sql

SELECT tenant_id, count() FROM analytics.events GROUP BY tenant_id ORDER BY tenant_id;

Step 2 — Create one permissive policy per tenant role

Attach a permissive policy that binds each tenant role to its literal tenant_id. Each policy adds one OR branch, so a role receives exactly the tenants it is granted and nothing more.

sql

CREATE ROW POLICY IF NOT EXISTS pol_tenant_42 ON analytics.events
    FOR SELECT USING tenant_id = 42
    AS PERMISSIVE
    TO tenant_42_reader;

CREATE ROW POLICY IF NOT EXISTS pol_tenant_77 ON analytics.events
    FOR SELECT USING tenant_id = 77
    AS PERMISSIVE
    TO tenant_77_reader;

A support role that legitimately spans two tenants simply holds both roles; the engine OR’s their filters and the role sees the union tenant_id IN (42, 77) — no third policy required. Roles and grants themselves are managed in the companion RBAC roles and grants workflow; here we assume the roles already exist.

Step 3 — Add a restrictive policy as a global guardrail

Permissive policies can only widen visibility. To enforce an invariant that no tenant grant can escape — for example, that soft-deleted rows are never returned to any reader — use a restrictive policy applied TO ALL. It is AND’d onto every user’s filter, tenant policies included.

sql

CREATE ROW POLICY IF NOT EXISTS pol_hide_deleted ON analytics.events
    FOR SELECT USING is_deleted = 0
    AS RESTRICTIVE
    TO ALL;

After this, tenant_42_reader sees tenant_id = 42 AND is_deleted = 0. The restrictive branch is the right tool for cross-cutting rules — data-residency flags, embargo windows, PII redaction gates — precisely because it cannot be OR’d away by adding another tenant grant.

Step 4 — Bind isolation to a session variable for scale

Writing one literal policy per tenant does not scale past a few dozen tenants. A cleaner pattern injects the tenant identity as a custom session setting and writes a single restrictive policy that reads it. First declare a custom-setting prefix in config.xml:

xml

<clickhouse>
    <custom_settings_prefixes>SQL_</custom_settings_prefixes>
</clickhouse>

Pin the value per user through a settings profile so the client cannot override it (readonly on the constraint locks it):

sql

ALTER USER tenant_42_svc SETTINGS SQL_tenant_id = 42 CONST;

Then a single policy isolates every tenant at once:

sql

CREATE ROW POLICY IF NOT EXISTS pol_tenant_scope ON analytics.events
    FOR SELECT USING tenant_id = getSetting('SQL_tenant_id')
    AS RESTRICTIVE
    TO ALL;

Because the setting is CONST, a tenant service account cannot widen its own scope, and onboarding a new tenant becomes an ALTER USER … SETTINGS call rather than a new DDL object. This is the pattern to reach for when tenants number in the hundreds.

Step 5 — Apply and reload

Row policies take effect immediately for new queries — there is no restart — but on a multi-node cluster you must create them everywhere. Use ON CLUSTER so the definition and its Keeper metadata propagate uniformly:

sql

CREATE ROW POLICY IF NOT EXISTS pol_tenant_42 ON CLUSTER analytics_cluster
    ON analytics.events FOR SELECT USING tenant_id = 42 AS PERMISSIVE TO tenant_42_reader;

Verification

First, enumerate every policy attached to the table and confirm the class and target of each. system.row_policies is the authoritative catalog:

sql

SELECT short_name, database, table, is_restrictive,
       apply_to_all, apply_to_list, select_filter
FROM system.row_policies
WHERE database = 'analytics' AND table = 'events'
ORDER BY is_restrictive, short_name;

Expected shape — permissive tenant policies (is_restrictive = 0) targeting named roles, plus the restrictive guard (is_restrictive = 1, apply_to_all = 1):

text

┌─short_name───────┬─is_restrictive─┬─apply_to_all─┬─apply_to_list─────────┬─select_filter──────────────────┐
│ pol_tenant_42    │              0 │            0 │ ['tenant_42_reader']  │ tenant_id = 42                 │
│ pol_tenant_77    │              0 │            0 │ ['tenant_77_reader']  │ tenant_id = 77                 │
│ pol_hide_deleted │              1 │            1 │ []                    │ is_deleted = 0                 │
└──────────────────┴────────────────┴──────────────┴───────────────────────┴────────────────────────────────┘

Then prove the isolation behaviorally. From an admin session, impersonate a tenant role and confirm the row set collapses to that tenant. SET ROLE restricts the current session to the named role’s privileges and policies:

sql

SET ROLE tenant_42_reader;
SELECT DISTINCT tenant_id FROM analytics.events;
-- Expect exactly one row: 42. Any other tenant_id is an isolation leak.

Finally, check that the appended filter also pruned the scan rather than reading everything and filtering late — isolation should be free, not a full scan:

sql

SET ROLE tenant_42_reader;
EXPLAIN indexes = 1
SELECT count() FROM analytics.events WHERE event_type = 'checkout';
-- The PrimaryKey condition should include tenant_id = 42 and prune parts/granules.

Gotchas & edge cases

An uncovered user may see everything, not nothing. The server setting users_without_row_policies_can_read_rows defaults to true in current builds, meaning a user with no applicable permissive policy on the table sees all rows, not zero. If you rely on “add a policy for the tenants and everyone else is locked out,” you are wrong by default. Either set that setting to false, or (better) use a restrictive TO ALL policy like Step 4 so every session — even one nobody remembered to grant — is filtered.
Restrictive-only tables need a permissive base. If a table has only restrictive policies and the setting above is false, users with no permissive policy see nothing regardless of the restrictive condition, because the permissive OR-group is empty and evaluates false. Pair a restrictive scope policy with an AS PERMISSIVE USING 1 TO ALL base, or keep the setting at its permissive default.
Row policies do not protect writers. They filter SELECT only. A role with INSERT, ALTER, or the ability to copy partitions can still write or exfiltrate across tenants. Keep tenant service accounts read-only, and scope write paths through the RBAC roles and grants model rather than assuming a row policy contains them.
Distributed tables evaluate policies on the shards, not the initiator. A policy created only on the node holding the Distributed engine table does nothing; the filter must exist on every shard that owns the underlying MergeTree data. Always create tenant policies ON CLUSTER and verify system.row_policies on each shard.

Managing RBAC Roles and Grants — creating the tenant roles that row policies attach to.
Enforcing TLS and Encrypted Connections — protecting the credentials that select a tenant identity on the wire.
Security & Access Control Boundaries — the privilege and perimeter model this isolation layer sits inside.
ClickHouse Core Architecture & Analytics Fundamentals — how parse-time predicate evaluation makes row policies cheap to enforce.

Up: Security & Access Control Boundaries