ClickHouse Row Policies: Row-Level Security for Multi-Tenant Tables

Q: Do row policies affect aggregations?

Yes. The filter is applied before any aggregation. SELECT count() FROM events returns the count of rows the user can see, not the total.

Q: Do row policies apply to INSERT?

No. Row policies are FOR SELECT only. To control INSERT, use grants ( GRANT INSERT ON db.table TO ... ) or column-level grants.

Q: Does `currentUser()` work for service accounts?

Yes. currentUser() returns the authenticated user's name. For tokens or external auth, the resolved user name is used. Pair with initial_user if you care about the user that originated a Distributed query.

Q: Can row policies hide rows from system administrators?

Users with ACCESS MANAGEMENT privilege can bypass row policies. Anyone less privileged sees only the policy-filtered view. That's the right default for tenancy; if you need policies that even admins respect, you have to remove ACCESS MANAGEMENT from those admins (and probably build a separate operator role).

A row policy in ClickHouse is a saved filter that gets automatically added to every SELECT against a table for the users or roles it applies to. The user does not see the filter; the server applies it transparently. Combined with column-level grants and roles, row policies are how you implement multi-tenant data isolation on a single shared table, and how you enforce that an analyst can only see rows for their team. The syntax is CREATE ROW POLICY ... USING <condition> TO <role>. This guide covers the full syntax, the difference between permissive and restrictive policies, the order in which multiple policies combine, and the patterns that work in production.

The Quick Answer

CREATE ROW POLICY tenant_acme ON analytics.events
USING tenant_id = 'acme'
TO acme_analysts;

Anyone with the acme_analysts role now sees only rows where tenant_id = 'acme'. Users without the role are unaffected unless another policy targets them.

Full CREATE ROW POLICY Syntax

CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name
    [ON CLUSTER cluster_name]
    ON [db.]table | [db.]*
    [IN access_storage_type]
    [FOR SELECT] USING condition
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role | user | ALL | ALL EXCEPT role}];

Multiple policy definitions can be combined in one statement (with commas separating each policy_name ON ... TO ... block). Every clause is optional except the policy name, the table, and the USING filter.

How USING Works

The USING expression must return a value that is treated as a boolean: zero means the row is hidden, non-zero means the row is visible.

-- Numeric column match
CREATE ROW POLICY by_tenant ON events USING tenant_id = currentUser() TO analysts;

-- Multiple conditions
CREATE ROW POLICY recent_only ON events
USING event_time >= now() - INTERVAL 90 DAY AND severity != 'debug'
TO support;

-- Refer to other tables via subquery (use sparingly; runs per row)
CREATE ROW POLICY my_team ON events
USING team_id IN (SELECT team_id FROM user_team_map WHERE user_name = currentUser())
TO analysts;

Useful built-in functions for row policies: currentUser(), currentRoles(), initial_user, and the global getMacro(...) for cluster-wide identifiers.

PERMISSIVE vs RESTRICTIVE

This is the part that surprises people first. The default is PERMISSIVE:

By default, policies are permissive, which means they are combined using the boolean OR operator.

And the contrast:

Restrictive policies are combined using the boolean AND operator.

The full rule from the docs:

row_is_visible = (one or more of the permissive policies' conditions are non-zero) AND (all of the restrictive policies' conditions are non-zero)

In plain English:

If any permissive policy says "yes", and every restrictive policy says "yes", the row is visible.
If no permissive policy applies to you, you see nothing (this is the gotcha).
A restrictive policy alone, with no permissive policy, hides the entire table.

The implication: when you start adding row policies to a table, you usually want either a single permissive policy per role, or a "catch-all" permissive policy that allows the rows everyone should see, plus restrictive policies that subtract.

Permissive example: per-tenant isolation

CREATE ROW POLICY tenant_acme ON events USING tenant_id = 'acme'  TO acme_users;
CREATE ROW POLICY tenant_widgets ON events USING tenant_id = 'widgets' TO widgets_users;

Acme users see acme rows, Widgets users see widgets rows. The combination is OR'd: if a user had both roles (shouldn't happen, but if), they would see both tenants' rows.

Restrictive example: hide PII from juniors

-- Permissive default: everyone sees everything
CREATE ROW POLICY all_rows ON events USING 1 TO ALL;

-- Restrictive: juniors don't see rows flagged as PII
CREATE ROW POLICY no_pii_for_juniors ON events
USING is_pii = 0 AS RESTRICTIVE TO junior_analysts;

Senior analysts (no restrictive policy) see everything. Junior analysts see only is_pii = 0 rows, because both policies apply and the restrictive one is AND'd.

TO: Who the Policy Applies To

TO acme_analysts                       -- specific role
TO alice, bob                          -- specific users
TO ALL                                 -- everyone with access to the table
TO ALL EXCEPT senior_admin             -- everyone except specific roles/users

TO ALL is the universal scope. TO ALL EXCEPT is the safety net: "everyone is locked down, except these escape-hatch roles." Avoid making the escape hatch a user; make it a role you can audit easily.

Wildcards and Database-Scoped Policies

-- All tables in a database
CREATE ROW POLICY tenant_acme ON analytics.* USING tenant_id = 'acme' TO acme_users;

-- Multiple policies in one statement
CREATE ROW POLICY
    p1 ON analytics.events USING tenant_id = 'acme',
    p2 ON analytics.users  USING tenant_id = 'acme'
TO acme_users;

The first form applies a single policy to every table in the database (note: the table needs the column referenced in USING; if a table does not have tenant_id, the query fails). Use this carefully and test against every table the policy applies to.

FOR SELECT

FOR SELECT is the only operation row policies currently apply to (it shows up as a syntactic placeholder in the docs in case more operations are added). Row policies do not affect INSERT, ALTER, or DROP; for those, use grants and the privilege system.

ON CLUSTER

CREATE ROW POLICY tenant_acme ON CLUSTER my_cluster
ON analytics.events USING tenant_id = 'acme' TO acme_users;

ON CLUSTER fans the create out to every node. If you use a replicated access storage backend, this is unnecessary; the policy propagates automatically.

Common Patterns

Self-service multi-tenancy

-- One role per tenant, one policy per tenant
CREATE ROLE acme_users;
CREATE ROLE widgets_users;

CREATE ROW POLICY tenant_acme    ON events USING tenant_id = 'acme'    TO acme_users;
CREATE ROW POLICY tenant_widgets ON events USING tenant_id = 'widgets' TO widgets_users;

GRANT SELECT ON events TO acme_users, widgets_users;
GRANT acme_users    TO alice;
GRANT widgets_users TO bob;

Each user sees only their tenant's rows. Adding a new tenant is one role + one policy.

Drive isolation by current user

CREATE ROW POLICY my_team_only ON support_tickets
USING team_id IN (SELECT team_id FROM acl.user_teams WHERE user_name = currentUser())
TO support;

The lookup table approach scales better than one policy per team. The trade-off: a subquery runs as part of every SELECT against the protected table. For high-throughput workloads, prefer a denormalized column on the data rows (assigned_team) so the policy is a direct comparison.

Restrictive policy for time-windowed access

CREATE ROW POLICY no_old_data ON events
USING event_time >= now() - INTERVAL 90 DAY
AS RESTRICTIVE
TO analysts;

Combined with a permissive policy that grants visibility, this enforces a 90-day analytical window. Older data still exists but is invisible to the analysts role.

Locking down system tables

Row policies on system.* tables can hide sensitive information (other users' queries, settings) from non-admin roles. Use them carefully; the wrong policy on system.tables will break everything that introspects the cluster.

Inspecting Policies

-- All policies
SELECT * FROM system.row_policies;

-- Policies on a specific table
SHOW ROW POLICIES ON analytics.events;

-- Reproducible DDL
SHOW CREATE ROW POLICY tenant_acme ON analytics.events;

Common Errors

"I created a restrictive policy and now everyone sees nothing": you forgot the permissive base case. Add CREATE ROW POLICY all_rows ON table USING 1 TO ALL.
Unknown identifier in USING: the column referenced in USING does not exist on the target table, often because the policy was created on db.* and one table doesn't have the column.
Cannot create row policy: the user lacks ACCESS MANAGEMENT or the specific CREATE ROW POLICY privilege. See the grant guide.
A user sees an empty table after a permissive policy: only rows where USING is non-zero are returned. Double-check the expression returns 1, not True or other types ClickHouse might coerce unexpectedly.

How Pulse Helps With Row Policy Correctness

Row policies are correct-by-construction in theory and very easy to misconfigure in practice. A restrictive policy without a corresponding permissive base hides everything; a permissive policy with a typo in the USING clause silently exposes more rows than intended; a policy on db.* breaks when a new table without the policy column is added. Pulse continuously inspects row-policy configurations on ClickHouse clusters and surfaces tables protected only by restrictive policies (likely empty for the affected roles), permissive policies referencing columns that may not exist on every targeted table, policies that have not matched a query in months (possibly stale), and roles with overlapping policies where the OR-combination yields broader access than intended. Connect your ClickHouse cluster to Pulse and let it watch for row-policy drift before it becomes a data leak.

Frequently Asked Questions

Q: Do row policies affect aggregations?

Yes. The filter is applied before any aggregation. SELECT count() FROM events returns the count of rows the user can see, not the total.

Q: Do row policies apply to INSERT?

No. Row policies are FOR SELECT only. To control INSERT, use grants (GRANT INSERT ON db.table TO ...) or column-level grants.

Q: Can a user see which rows a policy hid?

No, the filter is transparent. The user does not get an indication that rows are being hidden, which is by design for multi-tenant scenarios.

Q: Does currentUser() work for service accounts?

Yes. currentUser() returns the authenticated user's name. For tokens or external auth, the resolved user name is used. Pair with initial_user if you care about the user that originated a Distributed query.

Q: Will a row policy slow down my queries?

The cost depends on the USING expression. A simple column comparison is essentially free; a subquery against a lookup table runs per query (not per row, but the subquery itself is part of the plan). For high-throughput workloads, prefer denormalized columns over join-style policies.

Q: What happens if I drop a role that a policy uses in TO?

The policy stays, but the role reference is gone, so it has no effect. Re-grant or update the policy to target a new role.

Q: Can row policies hide rows from system administrators?

Users with ACCESS MANAGEMENT privilege can bypass row policies. Anyone less privileged sees only the policy-filtered view. That's the right default for tenancy; if you need policies that even admins respect, you have to remove ACCESS MANAGEMENT from those admins (and probably build a separate operator role).