How to Configure Dify ClickHouse Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up dify clickhouse data masking by restricting which columns the database user reads. Then you rewrite sensitive fields like emails or phone numbers before results return. Teams often link their AI workflows to analytics tables with a single shared database account. When that account has broad read rights, every generated query pulls raw user records. A bad prompt can expose card numbers or national IDs to the model. Setting strict rules at the database level helps limit what the app can

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.

You set up dify clickhouse data masking by restricting which columns the database user reads. Then you rewrite sensitive fields like emails or phone numbers before results return. Teams often link their AI workflows to analytics tables with a single shared database account. When that account has broad read rights, every generated query pulls raw user records. A bad prompt can expose card numbers or national IDs to the model. Setting strict rules at the database level helps limit what the app can read.

How to set up dify clickhouse data masking with native roles

ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To use SQL access control, an admin first sets a storage directory on the server. At least one admin account needs the access management permission turned on. That admin can then create a dedicated user account just for the AI app. Creating a user requires the create user privilege on the server. A user can only grant the privileges that they have themselves.

During setup, you define how the new user logs in and which hosts can connect. You also assign default roles and specific user settings for that account. Instead of giving full table read rights, you grant read permissions on safe columns only. When you grant select on specific columns, the user can only query those named fields. If the AI tool tries to read an ungranted column, the database blocks the query. A wildcard query that selects all columns also fails and returns no data at all. The wildcard query only works if the table holds just the granted columns. You can also use a trailing asterisk to grant rights by table name prefix.

What native column grants solve and what they leave open

Native column grants stop the AI workflow from reading restricted columns directly. This works well when your sensitive data sits neatly in separate columns. You simply leave the email and card number columns out of the grant list. The database checks who logs in and rejects queries that touch forbidden columns.

Still, static grants create friction when your AI app explores schemas or runs broad queries. Because a wildcard select fails on partially granted tables, automated workflows often break. Developers then fall back to granting full table access so their prompts stop failing. Native grants also cannot rewrite matched fields in results when data sits in text columns. If a log column holds raw phone numbers or national IDs, column grants are all-or-nothing. You either block the whole text column or let the raw strings pass through. Native setup decides who logs in, not what individual statements do in flight.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Why runtime query inspection matters for AI workflows

AI workflows generate queries dynamically based on user prompts and context. Static database roles cannot tell if a single query is safe or risky in flight. Teams also struggle when every workflow shares the same static database credentials in app settings. If you want to keep wildcard queries working while hiding sensitive text, you need runtime masking. Masking rewrites matched fields like emails and card numbers in query results on the fly. The query still runs without errors, and the workflow gets safe, masked values back.

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

Why do wildcard queries fail after I restrict ClickHouse columns for Dify?

ClickHouse blocks wildcard select queries when a user only has grants on specific columns. The query returns no data unless the table contains only the granted columns. Your AI tool must name each allowed column in its select statement.

Can ClickHouse column grants mask emails inside unstructured text columns?

No, native column grants only allow or block access to an entire column. If a text column contains emails or phone numbers, granting select exposes the whole string. You need runtime data masking to rewrite matched fields inside query results.

What permissions are needed to create a restricted ClickHouse user for Dify?

You need an admin account with the create user privilege and access management turned on. Remember that a ClickHouse user can only grant privileges that they already hold themselves.

The query runs. The emails stay hidden.