How to Configure AutoGen ClickHouse Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up autogen clickhouse data masking by pairing restricted database roles with query result rewriting. This stops AI agents from sending raw emails, phone numbers, or card numbers to models. Many teams connect multi-agent apps to analytics databases using a single shared admin login. Broad database permissions let automated agents read every table and column without limits. If an agent runs an open query, private records flow straight into the model prompt. A safe setup requires strict dat

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 autogen clickhouse data masking by pairing restricted database roles with query result rewriting. This stops AI agents from sending raw emails, phone numbers, or card numbers to models. Many teams connect multi-agent apps to analytics databases using a single shared admin login. Broad database permissions let automated agents read every table and column without limits. If an agent runs an open query, private records flow straight into the model prompt. A safe setup requires strict database grants and active field masking.

How native permissions support autogen clickhouse data masking

ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn on SQL access control, admins set up a storage directory on the server. They also enable access management and named collection control for an admin user. That admin user can only grant privileges that they hold themselves. Creating a new database user for your AI app requires the create user privilege. The create user statement sets the login method, allowed client hosts, and default roles. It also applies per-user settings to limit what the agent account can do.

After creating the agent user, you can limit which columns the agent reads. ClickHouse lets admins grant select rights on specific columns in a database table. The agent can then run queries that ask for those allowed columns only. If the agent asks for a restricted column, the database refuses the query. The database also refuses a select-all asterisk query and returns no data at all. A select-all query works only if the table holds just the granted columns. Admins can also use a trailing asterisk to grant select rights by table name prefix. These column grants block agents from reading sensitive columns like national IDs or card numbers.

Why static database roles leave an operational gap

Native column grants work well when you want to block a column every time. Yet AI agents often need to read mixed text columns or run broad analysis queries. When a column is blocked, any agent query that includes it fails right away. This breaks automated workflows when an agent explores a schema with a select-all query. Teams then face pressure to grant wider table rights so the agents keep working.

Database roles and host rules only check who logs in at the start. They decide which tables a user can read, not how results look in flight. Once an agent has select rights on a column, the database sends raw text back. Native grants do not rewrite emails, phone numbers, or card numbers inside returned rows. Teams also must update column grants every time developers add a new table or column. If a developer adds a column with phone numbers, wide table grants expose it right away. You also lack a clear log tying each agent prompt to the exact SQL query.

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 agent data access

Start by listing every table your AutoGen agents need to answer user questions. Create a dedicated ClickHouse role for the agent app instead of sharing human logins. Grant select rights only on the specific tables and columns the agent must read. Restrict the agent user account so it can log in only from trusted app hosts. Test your agent workflows to see if column blocks break common SQL queries. When agents need access to tables with emails or card numbers, add result masking. Result masking rewrites matched fields in query results before the agent reads the output. This keeps agent queries running while keeping raw private numbers out of model prompts.

Check ClickHouse queries and results before execution

Access control in ClickHouse is essential setup. Setup alone does not check single queries for risk or rewrite sensitive strings in returned rows. By design, hoop.dev sits beside the server as a sidecar for your ClickHouse and parses every statement before execution.

First, hoop.dev guardrails block disallowed deletes, drops, and alter mutations. Then hoop.dev data masking rewrites sensitive string columns in responses and logs each decision per resource. Read the docs and source on GitHub.

Frequently asked questions

What happens when an AutoGen agent runs a select-all query on restricted columns?

ClickHouse refuses a select-all query if the user lacks rights on every single column. The database returns no data at all unless the table holds only the granted columns.

Can native ClickHouse column grants rewrite emails or card numbers in results?

No, native column grants only allow or block access to a whole column. To return the column with masked emails or card numbers, you must rewrite results in flight.

How do you turn on SQL access control in ClickHouse for agent accounts?

You configure the server storage directory for access entities and enable access management on a user. That admin user can then run SQL statements to create users, roles, and column grants.

The query runs. The emails stay hidden.