How to Configure ClickHouse PII

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up clickhouse pii rules, use SQL-driven access control and column-level read grants. You create specific roles that can only read safe columns in a table. Most data teams start by giving broad read roles to analysts and engineers. When roles are too broad, queries return raw emails, phone numbers, and card numbers. Fixing this requires tight role boundaries and clear rules for each table. How native clickhouse pii roles work ClickHouse manages users, roles, row policies, settings profi

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

To set up clickhouse pii rules, use SQL-driven access control and column-level read grants. You create specific roles that can only read safe columns in a table. Most data teams start by giving broad read roles to analysts and engineers. When roles are too broad, queries return raw emails, phone numbers, and card numbers. Fixing this requires tight role boundaries and clear rules for each table.

How native clickhouse pii roles work

ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn on SQL-driven access control, you first set a storage folder on the server. You also need at least one admin user with access management turned on. Once active, admins can create new users and roles using standard SQL commands. A user can only grant the rights that they already hold themselves.

Creating a new user requires the right to create users in the database. During user setup, an admin sets how the person logs in. The admin can also limit which hosts the user can connect from. You can assign default roles and specific user settings at the same time. This builds the base layer for controlling who can reach sensitive tables.

After setting up users, you lock down sensitive fields using column-level read grants. Instead of granting read access to a whole table, you name the allowed columns. For example, you can grant a role read rights on two safe columns only. You leave out columns that hold emails, phone numbers, or national IDs. The user can then run queries that ask for those two safe columns. If the user asks for a restricted column, the database blocks the query.

What native column grants solve and what keeps the session active

Column grants work well when users write exact queries for known safe fields. Since version 24.10, a trailing asterisk also lets admins grant rights by table prefix. This helps teams manage read rules across many tables that share a naming pattern. Native roles stop unauthorized users from reading restricted columns directly.

Still, column grants create friction for day-to-day fixing and ad-hoc analysis. If a user has partial column rights, running a select-all query fails right away. The database refuses the select-all query and returns no data at all. A select-all query only works if the user holds rights on every single column. Because of this, analysts often ask for full table rights just to run quick checks.

ClickHouse Access Management + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static grants also decide everything at login time rather than checking queries in flight. If an engineer needs to inspect a full row during an outage, they need full access. Once they have that role, every query returns raw card numbers and phone numbers. Native grants cannot rewrite matched fields in query results on the fly. Teams must choose between breaking select-all queries or exposing raw sensitive fields to staff.

Why teams separate table schemas from sensitive fields

To work around select-all failures, some teams split tables into two separate parts. They keep safe event data in one table and sensitive fields in a locked table. This lets analysts run broad queries on the main table without hitting permission errors. Yet this approach doubles the schema work and makes joins harder to maintain. Every time a developer adds a new column, they must update multiple table definitions. Keeping schemas simple while protecting sensitive data requires controls that act on results directly.

Check ClickHouse queries and result columns on the wire

ClickHouse users and row policies decide who connects. They do not check each statement on the wire or redact sensitive string columns in query results. With hoop.dev, you run an open-source proxy, a sidecar for your ClickHouse, inline in ClickHouse's native TCP protocol. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.

Multi-statement queries and table mutations are split and checked by hoop.dev guardrails, which refuse banned actions with a native ClickHouse error or hold risky ones for approval. On returned rows, hoop.dev data masking rewrites String and FixedString columns in memory, and the audit trail logs each query and verdict. See the MIT-licensed source on GitHub.

Frequently asked questions

What happens when a user with partial column grants runs a select-all query?

ClickHouse refuses the select-all query and returns no data at all. Select-all only works when the user has read rights on every column in that table by default.

How do you turn on SQL-driven access control in ClickHouse?

You configure the server access control storage folder and enable access management for an admin user. That admin can then create users, roles, and row policies with SQL.

Can a ClickHouse user grant column access to a teammate?

A user can only grant the privileges that they already hold themselves. They also need permission to grant rights before they can share access with others.

The query runs. The emails stay hidden.