How to Configure Pydantic AI ClickHouse Guardrails
You set up pydantic ai clickhouse guardrails by pairing Python schema rules with database permissions. First, restrict agent tool inputs in Python, then lock down database roles with SQL grants. Teams often start by giving an AI agent a broad database login. That shortcut creates big risks when a model writes its own SQL queries. A single bad prompt can cause the agent to read private tables or run heavy queries. Good guardrails split safety checks between your app code and the database engine.
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 pydantic ai clickhouse guardrails by pairing Python schema rules with database permissions. First, restrict agent tool inputs in Python, then lock down database roles with SQL grants. Teams often start by giving an AI agent a broad database login. That shortcut creates big risks when a model writes its own SQL queries. A single bad prompt can cause the agent to read private tables or run heavy queries. Good guardrails split safety checks between your app code and the database engine.
How pydantic ai clickhouse guardrails work in practice
Your first line of defense lives inside the Python agent framework. You define strict data models for every tool the AI agent can call. These models check the shape and type of every input before a query runs. Instead of letting the model write raw SQL, you can force it to pick fixed filter values. The framework rejects any tool call that fails these type checks. It then sends the error back to the model so the agent can try again.
Your second line of defense lives inside the database itself. ClickHouse manages users, roles, row policies, settings profiles, and quotas through SQL commands. To turn on SQL access control, an admin sets a storage folder in the server settings. At least one admin account also needs access management permissions turned on. That admin can then create a dedicated user role just for your AI agent. In ClickHouse, a user can only grant permissions that they already hold themselves. Creating the agent user requires the user creation permission. During setup, you can set the login method, allowed client hosts, default roles, and user settings.
Once the agent user exists, you restrict what data it can read. ClickHouse lets you grant read access on single columns rather than whole tables. If you grant read access on only two columns, the agent can query only those two columns. If the agent tries to read a third column, the database blocks the query. A wildcard query that asks for all columns will also fail and return no data. It only works if the table holds nothing but the allowed columns. You can also grant read access across a group of tables that share a name prefix.
What native database and app checks leave open
App schemas and database grants solve the problem of who can log in. They also control which tables and columns an agent role can touch. Still, they do not inspect what individual queries do while running in flight. If an agent has read access to a customer column, it can pull every row at once. Native column grants cannot rewrite sensitive values inside an allowed column on the fly. For example, if a permitted text column holds emails, phone numbers, card numbers, or national IDs, the query returns them raw.
AI Guardrails + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Static roles also make auditing hard when many agents share one database user. The database log shows the shared app user, not the human or task behind the prompt. Teams also struggle when an agent needs temporary access to run a one-off check. Granting wider permissions by hand takes time and leaves roles too open afterward.
Layering runtime controls over database roles
To close these gaps, teams pair native database roles with runtime query checks. Keep your Python type checks to stop bad tool calls early in the app. Keep your ClickHouse column grants so the agent role stays read-only by default. Then add a runtime check that looks at every query and result set in flight. This step lets you mask fields like emails or card numbers in query results. It also ties each query to a clear audit trail without changing your database setup.
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 wildcard queries when a user only has access to specific columns on a table. The query returns no data unless the table contains only the exact columns granted to that user. You should instruct your AI agent to list allowed column names in every query.
Can Pydantic validation stop all unsafe SQL queries by itself?
No, Python schema checks only verify that tool inputs match expected data types and formats. If your tool lets the model pass a raw query string, schema validation cannot judge query safety. You still need database role limits and runtime query checks to stop unsafe reads.
How do I turn on SQL-driven access control in ClickHouse?
You enable it by setting the access control storage path in your server settings file. You also need at least one user account with access management and named collection control enabled. After that, you can create users, roles, and column grants using standard SQL statements.
Related guides
In this post