How to Configure Pydantic AI ClickHouse Audit Log
To set up a pydantic ai clickhouse audit log, create a dedicated database user and record agent queries in system tables. You also restrict what the agent can read using column grants and row rules. Many teams start by giving their agent a broad admin role to test tools fast. When roles are too broad, a bad prompt can read private columns or change tables. How a pydantic ai clickhouse audit log works In a typical setup, your Python app defines tools that run SQL queries. The agent picks a too
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 a pydantic ai clickhouse audit log, create a dedicated database user and record agent queries in system tables. You also restrict what the agent can read using column grants and row rules. Many teams start by giving their agent a broad admin role to test tools fast. When roles are too broad, a bad prompt can read private columns or change tables.
How a pydantic ai clickhouse audit log works
In a typical setup, your Python app defines tools that run SQL queries. The agent picks a tool, builds a query, and sends it to the database. To track these actions, you first need a clear database identity for the agent. ClickHouse manages users, roles, row rules, settings profiles, and quotas as SQL access entities. Turning on SQL access control needs a storage folder on the server. It also needs at least one admin user with access management turned on.
Once SQL access control is active, an admin with user creation rights makes an agent user. A user can only grant the rights that they already hold themselves. During user creation, the admin sets how the agent logs in and which hosts can connect. The admin also sets default roles and user settings for that agent account. When the agent runs a tool, the database ties every query to that user name. You can then review the system query log to see what the agent ran.
Setting column grants for agent tools
A log tells you what happened after a query runs. You also need to limit what the agent can read in the first place. ClickHouse lets you grant read rights on specific columns in a table. If an agent only gets rights to two columns, it can only query those two. Any query that asks for a third column will fail right away.
A select-all query also fails and returns no data unless the agent owns every column. Since version 24.10, a trailing star lets you grant rights by table name prefix. This helps when your agent reads from many tables that share a naming pattern. You can give the agent role read rights on safe columns across those tables.
AI Audit Trails + Audit Log Integrity: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native database roles solve and what they miss
Native database roles and logs solve the first layer of agent safety. They prove which account logged in and block access to whole tables or restricted columns. They also give your team a trail of past queries to check during a review. Still, native setup rules only check who logs in and which columns they pick.
Database roles do not inspect what individual queries do in flight or rewrite sensitive values. If a granted text column holds emails, phone numbers, card numbers, or national IDs, the query returns them. A static role cannot mask those matched fields in results before the model reads them. Also, the Python app still holds a database key to log in. If you run many agents, rotating those keys and reading raw log tables takes manual work.
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
How do I stop a Pydantic AI agent from running select-all queries in ClickHouse?
Grant read rights only on the specific columns the agent needs. ClickHouse refuses select-all queries and returns no data unless the user owns every column in that table.
What do I need before creating a dedicated ClickHouse user for an AI agent?
You must turn on SQL access control with a server storage folder and an admin account. The admin also needs user creation rights and can only grant rights they already hold.
Can ClickHouse column grants hide sensitive fields inside a text column?
No, column grants are all or nothing for each column you share. To hide emails, phone numbers, card numbers, or national IDs inside returned rows, you need data masking that rewrites matched fields in results.
Related guides
In this post