How to Configure Pydantic AI ClickHouse Credentials

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up pydantic ai clickhouse credentials by making a scoped database user and passing its details to your agent. Your agent then uses a Python client inside its tool functions to run queries. Many teams start by giving the agent a broad admin account to move fast. Broad permissions let a bad prompt read private columns or drop whole tables. Setting up strict roles and column rules keeps your data safe during agent runs. How to set up pydantic ai clickhouse credentials ClickHouse manages

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 credentials by making a scoped database user and passing its details to your agent. Your agent then uses a Python client inside its tool functions to run queries. Many teams start by giving the agent a broad admin account to move fast. Broad permissions let a bad prompt read private columns or drop whole tables. Setting up strict roles and column rules keeps your data safe during agent runs.

How to set up pydantic ai clickhouse credentials

ClickHouse manages users, roles, row policies, settings profiles, and quotas through SQL commands. To turn on SQL access control, you first set a storage folder on the server. You also need at least one admin account that holds access management rights. That admin can only hand out permissions that it already holds itself.

Making a new user for your agent takes the create user permission. When you create the account, you pick how the user logs in. You also set which client hosts can log in and pick default roles. You can attach user settings right to that account at creation time.

Once the user exists, your Python app loads the login details from secret storage. Pydantic AI passes those database client objects to agent tools through its dependency system. Each time the model calls a tool, the tool runs queries as that user.

Scoping table and column permissions for AI agents

AI agents often need to read just a few columns from a big table. ClickHouse lets you grant read access on single columns instead of the whole table. If you grant two columns, the agent can query only those two columns. If the agent asks for a third column, the database blocks the query.

A wildcard query that asks for all columns will also fail and return nothing. It only works if the table holds just the columns the user can read. Since version 24.10, you can also use a star at the end of a name. That trailing star grants read rights across all tables that share a name prefix.

Ephemeral Credentials + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native database roles solve and what keeps the session active

Native database roles and column grants stop an agent from reading restricted tables. They also block login attempts from unknown hosts and enforce basic account limits. This setup decides which account logs in and which columns that account can query.

Still, static credentials sit inside your app memory and config files during runtime. If an allowed column holds emails, phone numbers, or card numbers, queries return them raw. Native roles check who logs in, not what each query returns in flight. Teams also lack a single audit log that links agent prompts to SQL statements. Masking rules can rewrite matched fields like national IDs in results before the agent reads them.

Add runtime guardrails and masking beside ClickHouse

Database grants and network rules control who can reach ClickHouse. Even so, an allowed client or agent can send an alter mutation that deletes rows or read raw personal data. In practice, hoop.dev runs beside the server as a sidecar for your ClickHouse and checks native TCP queries with no change to your drivers.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run, and data masking rewrites matched string columns in results. The audit trail logs which entity types were masked and how many without storing raw values. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

Why did my agent wildcard query fail on a permitted ClickHouse table?

When a user has column-level read grants, ClickHouse blocks wildcard queries on that table. Your agent tool must list the exact allowed column names in every query.

How do I let an agent read a group of tables by prefix?

Since ClickHouse version 24.10, you can place a trailing star on the table name. This grants your agent read rights on every table matching that prefix.

What server setup is needed before creating SQL users for Pydantic AI?

You must set the access control storage folder on the ClickHouse server first. You also need an initial account with access management rights turned on.

The query runs. The emails stay hidden.