How to Configure Pydantic AI ClickHouse Permissions
You set up pydantic ai clickhouse permissions by creating a restricted database user with SQL. Then you pass those read-only login keys to your agent tool functions. Many engineering teams start by giving their AI agents a shared admin user. That fast path creates huge risks when a model writes its own SQL queries. An agent with broad database rights can delete tables or read sensitive customer data. Scoping down what the database user can do is a critical first step. How to set up pydantic ai
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 permissions by creating a restricted database user with SQL. Then you pass those read-only login keys to your agent tool functions. Many engineering teams start by giving their AI agents a shared admin user. That fast path creates huge risks when a model writes its own SQL queries. An agent with broad database rights can delete tables or read sensitive customer data. Scoping down what the database user can do is a critical first step.
How to set up pydantic ai clickhouse permissions
ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You create and change these entities with standard SQL statements. Before you start, your server needs a storage path for these access rules. You also need at least one admin user with access management turned on. ClickHouse enforces a strict rule when admins hand out database rights. A user can only grant the privileges that they hold themselves.
Creating a new user for your agent requires the user creation privilege. During this step, you set the login method and allowed client hosts. You also assign default roles and specific settings for that agent account. Once the user exists, you grant read rights on specific tables or columns. Column grants let the agent read only the fields you name. If the agent tries to query other columns, the database blocks the query. A wildcard query selecting all columns also fails and returns no data. That wildcard query only works if the table holds just the allowed columns. You can also use a trailing asterisk to grant rights by table name prefix.
How Pydantic AI tools run database queries
Pydantic AI lets you build agents that call Python functions as tools. To query data, you write a tool function that opens a ClickHouse session. You pass the client into the agent through its run context. When a user asks a question, the model picks the right tool to run. Sometimes the tool runs a fixed query with safe inputs checked by Pydantic. Other times, teams let the model write raw SQL to answer hard questions.
In both cases, the database only sees the static user account from your app. Every query from every agent run uses that exact same database role. The database checks the role when the client logs in and runs a statement. If the role has read access to a table, the query goes through.
AI Agent Permissions + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native roles solve and what they leave open
Native database roles and column grants stop many bad mistakes. They stop an agent from dropping tables or reading forbidden columns. They also restrict which host machines can log in with the agent key. This setup decides who logs in and which objects they can touch.
Still, static database roles cannot check what individual queries do in flight. Suppose an agent needs to query a customer table for support tickets. If that table holds emails, phone numbers, or card numbers, the agent gets them. Column grants are all or nothing for the columns you allow. They do not rewrite matched fields in results when sensitive text appears in free-form columns. Also, shared app keys hide which specific user or prompt triggered a bad query. Teams still need a way to inspect queries and mask sensitive fields in flight.
Check ClickHouse queries and result columns on the wire
ClickHouse users and row policies decide who connects. They do not check each statement on the wire or redact sensitive string columns in query results. With hoop.dev, you run an open-source proxy, a sidecar for your ClickHouse. It reads ClickHouse's native TCP protocol before queries run while ClickHouse stays in charge of login and grants.
Multi-statement queries and table mutations are split and checked by hoop.dev guardrails. Banned actions are refused with a native ClickHouse error. On returned rows, hoop.dev data masking rewrites String and FixedString columns that match your rules. The audit trail logs each query, table, and verdict. See the MIT-licensed source on GitHub.
Frequently asked questions
Why does a wildcard query fail for my Pydantic AI agent?
ClickHouse blocks wildcard queries when a user only has grants on specific columns. Tell your agent prompt to name the exact allowed columns in every query.
Can a ClickHouse user grant new permissions to the AI agent?
Yes, if that user has access management turned on. Remember that a ClickHouse user can only grant privileges that they hold themselves.
How do I limit which tables a Pydantic AI agent can read?
Create a dedicated role for the agent and grant select rights on single tables. You can also use a trailing asterisk to grant read rights by table prefix.
Related guides
In this post