How to Configure Pydantic AI ClickHouse Read Only Access

Published
Length
4 min read

Luis Felipe Dias

All posts

You set up pydantic ai clickhouse read only access by creating a restricted database user. Then you pass those read-only user keys to the tool functions in your agent. Many teams start by giving their AI agent an existing admin or writer account. That shortcut lets a bad model prompt alter tables, drop data, or read private rows. How pydantic ai clickhouse read only access works in native SQL ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities.

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 read only access by creating a restricted database user. Then you pass those read-only user keys to the tool functions in your agent. Many teams start by giving their AI agent an existing admin or writer account. That shortcut lets a bad model prompt alter tables, drop data, or read private rows.

How pydantic ai clickhouse read only access works in native SQL

ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To use SQL access control, the server needs a storage directory path set up. It also needs at least one user with access management rights turned on. An admin with user creation rights can then create a dedicated user for the agent.

A user in ClickHouse can only grant rights that they already hold themselves. During user creation, the admin sets the login method and allowed client hosts. The admin can also assign default roles and specific per-user settings for that account. Next, the admin grants read rights on specific tables or columns to the agent role.

ClickHouse lets you grant select rights on single columns instead of full tables. When you grant select on two columns, the agent can query only those two columns. If the agent tries to query a third column, the database blocks the query. A wildcard select query also fails unless the table contains only the allowed columns.

Recent ClickHouse releases also let admins grant select rights using a table name prefix. A trailing asterisk on a table name applies the grant to matching tables. Once the database role is ready, you wire it into your Python agent code. Pydantic AI agents run database queries through custom tool functions that you define.

Your tool function holds the ClickHouse client session and runs the SQL string. Pydantic checks the input types before the tool sends the query to the database. If the model writes a mutation query, the read-only ClickHouse user blocks it.

What native read-only roles solve and what they leave open

Native database roles stop an AI agent from deleting tables or changing stored rows. Column grants also help teams block access to known secret columns at setup time. Still, database roles only check who logs in and which objects they touch. They do not inspect what happens inside the query results at runtime.

Auditor Read-Only Access + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

First, static column grants break when agents generate wildcard queries during data exploration. If a table has ten columns and one is restricted, a wildcard select fails. The agent gets an error instead of receiving the nine safe columns it needed. Teams often give up on column grants and open the whole table to fix this.

Second, text columns and JSON blobs often hold sensitive data mixed with normal text. A support log column might include customer emails, phone numbers, card numbers, or national IDs. Granting select on that column exposes every value inside it to the language model. Native table grants cannot rewrite matched fields in results before they reach the model.

Third, shared agent credentials make audit logs hard to read during a security review. Every query from every user session shows up under the same bot database account. You cannot tell which human user or prompt triggered a heavy or sensitive query.

Managing schema changes and agent credentials across teams

Analytics schemas change often as product teams add new tables and event fields. Maintaining column-level grants by hand for each table takes constant admin work. Even prefix grants require strict naming rules across every database in your cluster. If an engineer creates a table with the wrong prefix, the agent loses read rights.

Storing static database passwords inside agent host variables also creates a secret rotation headache. Each worker node running the agent needs the database user key on disk. When you rotate that key, you must redeploy every worker at the same time.

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 my agent fail when running a wildcard select query in ClickHouse?

ClickHouse blocks wildcard select queries when a user only has grants on specific columns. The wildcard query works only if the user has rights on every column by default. Configure your agent prompt to list explicit column names in every select statement.

Can a ClickHouse user grant read access to another agent account?

A ClickHouse user can only grant privileges that they already hold on their own account. They also need access management rights enabled to create users or assign roles. Keep access management turned off for the database account that your AI agent uses.

How do I stop an AI agent from reading sensitive fields in text columns?

Column grants block whole columns, so they cannot filter values inside free-text fields. Use runtime data masking to rewrite emails, phone numbers, and card numbers in query results. This lets the agent read the text column while hiding private values in flight.

The query runs. The emails stay hidden.