How to Implement Row-Level Security in ClickHouse with TypeScript

If you're building a multi-tenant analytics system on ClickHouse and you're looking for row-level security (RLS) like Postgres's CREATE POLICY, you'll be disappointed: ClickHouse's approach is different. But the alternatives are solid — especially when you have a typed TypeScript layer to work with.

This post covers three patterns, from simplest to most robust.

Pattern 1: Column Filtering — Add tenant_id Everywhere

The simplest approach: add a tenant_id column to every table and include a WHERE clause on every query.

This works. ClickHouse is actually well-suited to this pattern because tenant_id can be the first column in the sort key, which means the primary key skip index eliminates most granules that don't belong to the tenant — making tenant-scoped queries very fast.

The problem is discipline: every developer writing a query must remember to add the tenant filter. One forgotten WHERE clause leaks another tenant's data. At scale, with many developers and many query functions, this becomes a liability.

Pattern 2: ClickHouse User Profiles and Row Policies

ClickHouse has a row policy feature that enforces filters at the database level, regardless of what SQL is sent:

With this in place, even a SELECT * FROM events run as tenant_42_user only returns rows where tenant_id = 42. The enforcement is at the database level, not the application level.

Connecting with a tenant-scoped user in hypequery:

The security guarantee is strong — even a malicious or buggy query can't see other tenants' data because the database enforces the restriction.

The tradeoff: managing one database user per tenant is operationally complex. You need to provision users on tenant creation, rotate passwords, and handle the lifecycle. For systems with hundreds or thousands of tenants this becomes a significant maintenance burden.

Pattern 3: Context Injection with hypequery/serve (Recommended)

The recommended approach for TypeScript teams using hypequery is context injection. The idea: inject the authenticated tenant's ID at the request context level so it's automatically available to every query without per-query boilerplate.

This makes tenant scoping part of the standard query path rather than a convention that each developer must follow.

Setting Up Context Injection

Using Context in Queries

Every query handler receives the context with tenantId already verified and typed:

Why This Is Better Than Per-Query Filtering

• You can't forget it. The context type requires tenantId. If you write a query that doesn't use ctx.tenantId, TypeScript won't error — but code review has a clear pattern to check for.
• No tenant ID in request inputs. Callers can't pass a different tenantId in the request body to access another tenant's data. The tenant comes from the verified JWT, not user-supplied input.
• Centralized audit point. All tenant verification logic is in one place — the context function in initServe. Change auth logic there and it applies everywhere.

Adding a Helper to Enforce the Pattern

If you want to make the tenant filter part of the default query path, wrap the query builder:

Choosing the Right Pattern

| Pattern | Enforcement | Operational complexity | Best for | |---|---|---|---| | Column filtering | Developer discipline | Low | Small teams, prototypes | | ClickHouse user profiles | Database-enforced | High | Compliance requirements, external access | | Context injection | Structural (code review) | Low | TypeScript teams using @hypequery/serve |

For most TypeScript applications using hypequery, context injection is the right default. It's low overhead, fits naturally into the @hypequery/serve architecture, and makes the tenant isolation pattern explicit and reviewable without the operational cost of per-tenant database users.