How to Configure Pydantic AI ClickHouse Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up pydantic ai clickhouse data masking, restrict database columns or rewrite sensitive values in query results. You can pair ClickHouse SQL access rules with app logic that scrubs emails and phone numbers. Teams often give AI agents a broad database user to speed up early testing. When permissions are too wide, a single model query can pull card numbers and national IDs into prompts. Locking down what the agent reads is the first step toward safe AI workflows. How native pydantic ai cli

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 pydantic ai clickhouse data masking, restrict database columns or rewrite sensitive values in query results. You can pair ClickHouse SQL access rules with app logic that scrubs emails and phone numbers. Teams often give AI agents a broad database user to speed up early testing. When permissions are too wide, a single model query can pull card numbers and national IDs into prompts. Locking down what the agent reads is the first step toward safe AI workflows.

How native pydantic ai clickhouse data masking works

ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn on SQL access control, an admin sets a storage directory on the server. At least one admin account needs access management rights enabled to start creating rules. An admin can only grant privileges that they hold on their own account. Creating a new account for your AI agent requires the user creation privilege. During setup, you define how the agent logs in and which hosts it can use. You can also assign default roles and specific settings profiles to that agent account.

Inside the database, you control column visibility by granting read access only on safe columns. If an agent only has access to two columns, queries for a third column will fail. Wildcard queries that ask for all columns also fail and return no data at all. A wildcard query only works if the agent has rights to every column in that table. Since version 24.10, admins can also use a trailing asterisk to grant rights by table name prefix.

On the app side, Pydantic AI uses Python functions as tools that the model calls. The agent runs a tool to send a SQL query to ClickHouse and gets rows back. Before those rows reach the model, your Python code validates and formats the output schema. Developers often write custom validators here to mask emails, phone numbers, or card numbers in results.

What native controls solve and what they miss

Native ClickHouse column grants work well when an AI agent does not need a column at all. If you block the national ID column, the database stops the query before any data leaves. Row policies also help by limiting which rows a specific database user can read. Together, these native controls decide who logs in and which static objects they can touch.

Still, static grants leave an operational gap when agents run dynamic queries in flight. Blocking a column breaks tools that run broad queries or inspect unknown schemas. Also, sensitive values like emails and phone numbers often live inside free-text log columns. You cannot block the whole text column if the agent needs to read the error message inside it.

AI Data Exfiltration Prevention + Data Masking (Static): architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Writing custom masking rules inside every Pydantic AI tool adds heavy maintenance work for your team. Every new agent or script needs the exact same scrubbing logic in its Python code. If one developer forgets a validator, raw card numbers flow straight into the model prompt. Native database grants check who logs in, not what individual queries return in flight.

Managing schema changes with column grants

Database schemas change fast as product teams ship new features and tables. When you rely only on column grants, every new column requires an update to your SQL roles. If a developer adds a column with phone numbers, wildcard grants on table prefixes might expose it. On the other hand, strict column lists mean agents break when schemas change.

Teams also run multiple AI agents alongside human analysts and background jobs. Keeping Python masking rules synced across ten different codebases takes constant effort. When masking rewrites matched fields in results at the network level, app code stays simple. You do not have to rewrite SQL grants every time a table changes.

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

Why do wildcard queries fail when using ClickHouse column grants?

ClickHouse blocks a select-all query if the user lacks access to even one column in the table. The query returns no data at all unless the user has grants for every single column.

Can ClickHouse column grants mask an email inside a text field?

No, native column grants are all-or-nothing for each column. They block the whole column rather than rewriting specific emails or card numbers inside a text string.

How does an admin turn on SQL access control in ClickHouse?

An admin configures the access control storage directory on the server and enables access management on an admin account. From there, they can create users, roles, and column grants with SQL statements.

The query runs. The emails stay hidden.