How to Configure Pydantic AI ClickHouse Approval

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a pydantic ai clickhouse approval flow by pairing scoped database roles with a human review step. The agent pauses before running risky SQL and waits for a reviewer to sign off. Many engineering teams start by giving their AI agent a broad database user account. When an agent holds wide privileges, one bad prompt can drop data or pull sensitive rows. How pydantic ai clickhouse approval works with database roles A safe agent setup starts inside the database itself. ClickHouse manage

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 approval flow by pairing scoped database roles with a human review step. The agent pauses before running risky SQL and waits for a reviewer to sign off. Many engineering teams start by giving their AI agent a broad database user account. When an agent holds wide privileges, one bad prompt can drop data or pull sensitive rows.

How pydantic ai clickhouse approval works with database roles

A safe agent setup starts inside the database itself. ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn on SQL-driven access control, you set a storage directory on the server. You also need an admin user with access management and named collection control enabled. That admin user can only grant privileges that they already hold themselves.

With those basics in place, you create a dedicated user for your agent. Making a new user requires the create user privilege in the database. The creation statement sets the login method, allowed client hosts, default roles, and user settings. You can also restrict which columns the agent is allowed to read. When you grant read rights on specific columns, the agent can query only those columns.

If the agent runs a query asking for other columns, the database blocks it. A wildcard query that selects all columns also fails and returns no data at all. That wildcard query only works if the table holds just the allowed columns. You can also use a trailing asterisk to grant read rights across matching table prefixes.

Why static roles are not enough for agent workflows

In the agent framework, developers wrap database query functions inside tool definitions. When the model wants to run a query, the app pauses the tool call. It sends the planned SQL string to a human reviewer in chat or a dashboard. The reviewer checks the statement and clicks approve or reject before the database runs it.

Native database roles and app-level checks solve the basic login problem. They decide who can log in and which tables or columns are off limits. Yet static roles do not inspect what individual statements do while in flight. An agent with valid read rights can still run a heavy join that slows production. An agent with write access for routine tasks can still run an unsafe delete statement.

Approval Chains & Escalation + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

App-level review checks also leave gaps when teams run many agents across different services. Each service needs custom review code, and credentials still sit inside every app environment. If an engineer runs the agent locally, the database sees the same static user account. It cannot tell which human approved the specific query or mask emails in the results.

Building a two-layer review workflow for agent queries

To keep production data safe, split your controls into two clear layers. First, use ClickHouse roles and column grants to lock down baseline permissions. Give the agent user read rights only on the exact columns its task requires. Lock the user account to specific client hosts and attach strict quota rules.

Second, enforce query review and field masking outside the agent code. Every query that alters data or reads sensitive tables should pause for human sign-off. When approved queries return rows, masking rules should rewrite emails, phone numbers, or card numbers. This setup keeps raw personal data out of model prompts while letting agents finish tasks.

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

What happens when an agent runs a wildcard query on restricted ClickHouse columns?

ClickHouse refuses a wildcard select query when the user only has specific column grants. The query returns no data unless the table contains only those exact granted columns.

Who can grant permissions to a new ClickHouse agent user?

An existing user with access management turned on and the create user privilege can do this. In ClickHouse, a user can only grant privileges that they hold themselves.

Why do read-only agent queries still need approval or masking rules?

Read-only queries can still scan huge tables or pull sensitive fields into model prompts. Masking rewrites matched fields like emails or national IDs in results before the model reads them.

The query runs. The emails stay hidden.