How to Configure Haystack ClickHouse Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up haystack clickhouse data masking, restrict column reads and rewrite sensitive query results. You do this by limiting database roles and transforming fields like emails or phone numbers. Teams often link AI pipelines to analytical stores using a single shared database user. When that user holds broad read rights, the pipeline pulls raw personal records into prompts. A tight setup blocks sensitive columns at the database or rewrites matched fields in results. How native permissions han

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 haystack clickhouse data masking, restrict column reads and rewrite sensitive query results. You do this by limiting database roles and transforming fields like emails or phone numbers. Teams often link AI pipelines to analytical stores using a single shared database user. When that user holds broad read rights, the pipeline pulls raw personal records into prompts. A tight setup blocks sensitive columns at the database or rewrites matched fields in results.

How native permissions handle haystack clickhouse data masking

ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To use SQL access control, an admin sets up a local storage directory on the server. At least one admin account needs access management and named collection control turned on. That admin can then create a dedicated database user for the Haystack pipeline. An admin can only grant privileges that they already hold themselves.

When creating the pipeline user, the admin sets the login method and allowed client hosts. The admin also assigns default roles and user settings for that account. Next, the team limits what the AI pipeline can read by granting column-level permissions. In ClickHouse, you can grant read rights on specific columns rather than the whole table. If the user queries only those allowed columns, the database returns the data.

If the pipeline tries to read a restricted column, the database blocks the query. A wildcard query across all columns also fails and returns no data at all. That wildcard query only works if the table contains just the granted columns. Teams can also grant permissions across multiple tables that share a name prefix. This column restriction stops the pipeline from reading dedicated columns for card numbers or national IDs.

What native column grants solve and what they leave open

Native users and column grants control which account logs in and which columns it reads. This works well when sensitive data lives in separate, well-named columns that Haystack never needs. The database simply rejects any query that touches a forbidden column.

Still, AI pipelines often read free-text columns or semi-structured logs to build context. Those text columns can mix normal notes with emails, phone numbers, or card numbers. Blocking the whole column breaks the Haystack retrieval step and starves the model of context. Allowing the column sends raw sensitive strings straight into the pipeline.

Also, static database roles cannot inspect what an individual query returns in flight. Every pipeline worker sharing that database login gets the exact same column access. If a developer changes a Haystack query to pull a wider table, static grants fail. Either the query crashes on a blocked column, or it pulls unmasked fields into memory. Teams need a way to keep queries working while rewriting matched sensitive fields in results.

Data Masking (Static) + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

How to plan your pipeline data access rules

Start by listing every table and column your Haystack retrievers actually need to read. Create a dedicated ClickHouse user and role just for the retrieval pipeline. Grant read rights only on the required columns and avoid broad table grants. Avoid wildcard queries in your retriever components so column restrictions do not break your app.

Next, check the allowed columns for mixed content that holds emails or national IDs. Because column grants cannot filter inside a text field, handle those fields during query execution. Applying field rewriting in flight lets the query succeed while replacing sensitive values in results.

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. It reads ClickHouse's native TCP protocol before queries run while ClickHouse stays in charge of login and grants.

Multi-statement queries and table mutations are split and checked by hoop.dev guardrails. Banned actions are refused with a native ClickHouse error. On returned rows, hoop.dev data masking rewrites String and FixedString columns that match your rules. The audit trail logs each query, table, and verdict. See the MIT-licensed source on GitHub.

Frequently asked questions

What happens if a Haystack retriever runs a wildcard query on a restricted ClickHouse table?

ClickHouse refuses a wildcard select query if the user only has rights on specific columns. The query returns no data unless the table holds only the permitted columns.

Can ClickHouse column grants mask emails or phone numbers inside a text column?

No, native column grants only allow or block access to the entire column. To replace emails, phone numbers, or card numbers inside text, you must rewrite matched fields in results.

What server setup is required before creating ClickHouse roles with SQL?

You must configure the access control storage directory on the ClickHouse server first. You also need at least one user account with access management permissions enabled.

Short role checks help teams work fast. Simple rules make each review clear. Good logs keep every team safe. Scoped keys stop bad changes early.

The query runs. The emails stay hidden.