How to Configure Pydantic AI ClickHouse Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up a pydantic ai clickhouse prevent delete workflow, restrict the database user role. You must also validate every agent tool input before the query runs. Many teams connect AI agents to analytics tables with broad admin keys. A bad prompt can trick the model into dropping rows or entire tables. Broad permissions turn a simple read task into a major data loss event. How to set up pydantic ai clickhouse prevent delete rules Start by setting up SQL-driven access control inside your datab

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.

To set up a pydantic ai clickhouse prevent delete workflow, restrict the database user role. You must also validate every agent tool input before the query runs. Many teams connect AI agents to analytics tables with broad admin keys. A bad prompt can trick the model into dropping rows or entire tables. Broad permissions turn a simple read task into a major data loss event.

How to set up pydantic ai clickhouse prevent delete rules

Start by setting up SQL-driven access control inside your database server. ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. An admin needs a storage directory and user management rights to start. Remember that a user can only grant privileges that they hold themselves. Create a dedicated user account just for your AI agent app.

During user creation, you can set the login method and allowed client hosts. You can also assign default roles and specific user settings right away. Give this new role read rights without adding mutation or drop privileges. You can grant read rights on specific columns instead of the whole table. If you grant two columns, the agent can query only those two columns. Full table wildcard reads will fail unless the table has only those columns. You can also grant read access by table name prefix for shared datasets.

Next, build your agent tools with strict schemas instead of open text inputs. Do not let the model write raw database queries on its own. Define a tool schema that accepts only filter values, dates, or group fields. Your app code then builds a safe read statement from those checked fields. This step stops the model from sending delete statements through the tool.

What native database roles solve and what they miss

Native roles and tool schemas stop basic data loss from simple read agents. The database blocks delete requests when the role lacks write privileges. Typed tool inputs also stop the model from adding extra commands to queries. These two steps form a strong base for read-only agent workflows.

Still, static roles only check who logs in at the start. They do not inspect what individual statements do in flight when agents need write rights. Some agents must insert summary rows or update job status flags in tables. Once a role has broad write rights, static grants cannot block bad deletes. Also, column grants are all or nothing for sensitive fields. If an agent reads a customer column, the database returns raw emails or card numbers. Static database roles cannot rewrite matched fields in results on the fly. Teams also lack a clear log linking each database query to a specific agent run.

AI Data Exfiltration Prevention + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

How to handle mixed read and write agent tasks

When your agent needs both read and write tools, split the database accounts. Create one user account that only holds read grants on specific columns. Create a second user account that only holds insert rights on a target log table. Map each tool in your app to the matching database client object.

This split design keeps your core analytics tables safe from accidental deletes. The agent can still record its output in a separate table. Always validate the row count and payload size inside your tool function. Reject any request that tries to alter existing records or drop partitions.

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

Can a Pydantic AI agent run raw SQL safely against ClickHouse?

Running raw model-generated SQL is risky even with a read-only database user. It is safer to pass checked parameters into a fixed query template. If you must allow raw queries, use a strict read role with column grants.

Why do wildcard queries fail when using ClickHouse column grants?

ClickHouse blocks wildcard queries when a user only has rights on select columns. The query returns no data unless the table contains only the granted columns. Your agent tool must list each allowed column name in the query.

How do I protect PII when an AI agent queries ClickHouse?

First, omit columns with emails, phone numbers, or national IDs from your column grants. If the agent needs those columns, apply data masking to rewrite matched fields in results.

The query runs. The emails stay hidden.