How to Configure Pydantic AI ClickHouse Sandbox

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a pydantic ai clickhouse sandbox by creating a limited database user and passing it to your agent tool. This setup restricts the agent to safe, read-only queries on approved tables. Many teams start by giving the agent a broad admin role during tests. That shortcut exposes private rows and allows accidental drops if a prompt goes wrong. A dedicated sandbox user keeps your data safe while the model runs SQL. How to set up a pydantic ai clickhouse sandbox with SQL roles ClickHouse ma

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 a pydantic ai clickhouse sandbox by creating a limited database user and passing it to your agent tool. This setup restricts the agent to safe, read-only queries on approved tables. Many teams start by giving the agent a broad admin role during tests. That shortcut exposes private rows and allows accidental drops if a prompt goes wrong. A dedicated sandbox user keeps your data safe while the model runs SQL.

How to set up a pydantic ai clickhouse sandbox with SQL roles

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You create and change all of these items with SQL statements. First, you must turn on SQL-driven access control in the server settings. This step takes a storage directory on the server and an admin account. That admin account needs user management rights turned on. Remember that a user can only grant privileges that they hold themselves.

Once you turn on management, you create a new user for the agent. Making a user requires the user creation privilege on your admin account. The creation statement sets the login method and limits which hosts can log in. It also sets default roles and user settings for the agent session. Next, you attach a settings profile and a resource quota to that role. These limits stop a bad agent loop from using all your server memory.

After the user exists, you grant read rights on specific columns. In ClickHouse, you can grant select rights on just one or two columns. The agent can then run queries that read only those allowed columns. If the agent asks for another column, the database blocks the query. A wildcard select query also fails and returns no data at all. It only works if the table holds just the granted columns. You can also use a trailing asterisk to grant rights by table name prefix.

Why native roles are not enough for agent queries

Native roles and column grants solve the first part of sandbox safety. They decide who can log in and which tables or columns stay off limits. Row policies also filter out rows that the agent should not read. For many static apps, these database rules are a solid start. They stop an agent from dropping tables or reading secret finance tables.

AI Sandbox Environments + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, static grants leave a big operational gap during live agent runs. Database roles check who logs in, not what each query does in flight. An agent Often needs to read a customer table to answer support questions. If you grant access to that table, the query returns raw text. Columns with emails, phone numbers, card numbers, or national IDs flow right into the model context. If you block those columns with native grants, wildcard queries break right away.

Static roles also make it hard to require human approval for risky queries. You must either pre-grant a right forever or block the agent every time. Teams also struggle to link a database query log back to a specific user prompt. You see the shared agent user in the log, not the person who triggered the run. Closing these gaps takes statement checks and field masking while queries run.

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

What happens when a Pydantic AI agent runs a wildcard query on restricted columns?

ClickHouse refuses a wildcard select query when the user only has partial column grants. The query returns no data at all unless the table contains only the granted columns.

How do you limit where the sandbox user can log in from?

When you create the user in ClickHouse, you set the allowed host rule in the statement. This rule restricts logins to the specific IP address or host where your agent app runs.

Can a ClickHouse sandbox user grant read rights to another agent?

No, a user can only grant privileges that they hold themselves, and creating users requires its own privilege. Your sandbox role should only hold select rights on approved columns.

The query runs. The emails stay hidden.