Skip to content

CONCEPT Cited by 1 source

ClickHouse distributed query

In ClickHouse, cross-shard queries route through distributed tables (backed by the Distributed table engine in a conventionally-named default database). The coordinator fans out subqueries to underlying shard tables on each node (conventionally in a database named r0) and merges results back.

Two user-account models

Historical model: distributed subqueries run under a shared system account. Per-user grants + resource limits apply only at the coordinator; any user query can consume shared capacity that affects other users.

Per-user model: distributed subqueries run under the initiating user's account. Each subquery checks the user's grants on the r0.* shard tables independently. Per-user resource limits + grants apply end-to-end; one bad subquery from one user cannot harm others.

The per-user model is better least-privilege but requires each user to have explicit grants on the r0 underlying tables (they already have implicit access via the distributed tables in default, so functionally nothing changes — but the explicit grant is needed so the privilege check can succeed at the shard node).

Metadata-visibility side-effect

system.columns, system.tables etc. list tables the user can see via grants. Before the per-user migration, users see only the default.* distributed-table columns. After, they see both default.* and r0.* columns — same table names in both databases.

Any query that filters system.columns by table but not by database will therefore return row counts that depend on the grants in effect — roughly doubled after migration for the same table name.

This is not a ClickHouse bug; it is correct metadata visibility. The hazard lives in downstream code that assumed pre-migration semantics.

Canonical hazard instance

Cloudflare's Bot Management feature-file generator ran SELECT name, type FROM system.columns WHERE table = 'http_requests_features' ORDER BY name with no database filter. Mid-rollout of the per-user migration, the query's row count depended on which shard coordinator the session hit → intermittent good/bad feature files → see sources/2025-11-18-cloudflare-outage-on-november-18-2025.

Seen in

Last updated · 200 distilled / 1,178 read