How to Configure Pydantic AI ClickHouse Least Privilege
You set up pydantic ai clickhouse least privilege with dedicated SQL users and strict column grants. You also limit your AI agent tools so they only run safe read queries. Many teams start by giving their agent a default database user with broad rights. That shortcut is fast for testing, but it exposes every table in your cluster. If an agent gets a bad prompt, a wide role lets it read private records. It could also drop tables or lock up your server with huge queries. How to set up pydantic a
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 least privilege with dedicated SQL users and strict column grants. You also limit your AI agent tools so they only run safe read queries. Many teams start by giving their agent a default database user with broad rights. That shortcut is fast for testing, but it exposes every table in your cluster. If an agent gets a bad prompt, a wide role lets it read private records. It could also drop tables or lock up your server with huge queries.
How to set up pydantic ai clickhouse least privilege in SQL
First, turn on SQL-driven access management in your ClickHouse server settings. This feature needs a storage folder path set up on your server. It also needs at least one admin user with access management rights turned on. Once active, you manage users, roles, row rules, settings profiles, and quotas through SQL. Remember that a user can only grant the rights that they already hold themselves.
Next, create a dedicated database user just for your Pydantic AI app. Adding a new user takes the create user right on the server. During setup, you pick the login method and list the hosts allowed to log in. You can also assign default roles and custom user settings right away.
After creating the user, grant read rights only on the exact columns the agent needs. ClickHouse lets you grant select rights on single columns inside a specific table. The agent can then run queries that use only those allowed columns. If the agent tries to query an unapproved column, the database blocks the query. A wildcard select query also fails and returns no data at all. That query only works if the table holds just the allowed columns. In versions since 24.10, a trailing star lets you grant rights by table name prefix.
Finally, wire this locked-down user into your Pydantic AI tool setup. Pass the database client into your agent through its run context. Define structured tool inputs so the model fills in safe filter values. Keep raw SQL writing out of the model when simple filter tools work.
What native database roles solve and what they miss
Native ClickHouse roles and column grants do a great job of blocking unauthorized table reads. They stop your AI agent from touching tables or columns outside its job scope. Host limits and quotas also help keep basic network and resource abuse in check. These database controls form a strong base for any AI data project.
Least Privilege Principle + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, static database roles only check who logs in and which columns they pick. They do not inspect what each query does in flight or what data comes back. An allowed column might still hold emails, phone numbers, card numbers, or national IDs. If the agent queries that column, raw personal data flows straight into the model prompt. Only dynamic data masking rewrites matched fields in results before they reach the model.
Also, every Pydantic AI agent run shares the same static database login credentials. Your database logs show the shared app user, not which human triggered the agent run. If a prompt causes a bad query, you cannot trace it to a specific person. You also cannot pause a risky query in flight to ask a human for approval.
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 with ClickHouse column grants?
ClickHouse blocks wildcard select queries when a user only has rights on specific columns. The query returns no data unless the table holds only those exact granted columns. Your Pydantic AI tools must list each allowed column name in every select query.
Can ClickHouse grant read access to tables that share a prefix?
Yes, starting in version 24.10, ClickHouse supports a trailing star in grant statements. This lets you grant select rights across all tables that share a specific name prefix. It helps when your agent reads from daily or monthly log tables.
How do you keep personal data out of Pydantic AI prompts?
Start by leaving columns with personal data out of your ClickHouse column grants. When an agent must read mixed text columns, use field masking on the query results. Masking rewrites emails, phone numbers, card numbers, and national IDs before the model reads them.
Related guides
In this post