How to Configure Pydantic AI CockroachDB Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a pydantic ai cockroachdb connect flow by giving your agent a Postgres driver. The agent then uses a database role and TLS certs to run SQL queries. Teams often start by handing the agent a broad admin role to test fast. That broad role lets a bad prompt read or change every table in the cluster. How to set up a pydantic ai cockroachdb connect flow CockroachDB speaks the standard PostgreSQL wire protocol out of the box. Because of this, your Python agent can use standard async or s

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 cockroachdb connect flow by giving your agent a Postgres driver. The agent then uses a database role and TLS certs to run SQL queries. Teams often start by handing the agent a broad admin role to test fast. That broad role lets a bad prompt read or change every table in the cluster.

How to set up a pydantic ai cockroachdb connect flow

CockroachDB speaks the standard PostgreSQL wire protocol out of the box. Because of this, your Python agent can use standard async or sync Postgres libraries. You start by creating a dedicated SQL user inside your CockroachDB cluster. Then you grant that user read or write privileges on specific database tables.

Next, you set up the dependency injection system inside your agent framework. This system holds a shared database pool and passes it to agent tools. When the model decides to fetch data, it calls one of these registered tools. The tool pulls an active session from the pool and runs the SQL statement.

The database returns rows to the tool, which validates them against a strict schema. After validation passes, the tool sends the structured results back to the language model. You must also enforce TLS verification so traffic between the app and cluster stays encrypted.

What native roles solve and what they leave open

Native database roles and network rules solve the basic login problem. They verify who can log in and which network paths can reach the cluster. Table grants also stop an agent from reading tables outside its assigned schema. This setup works well when your app only runs fixed, pre-written SQL queries.

Still, AI agents often build dynamic queries or pick tool inputs from user prompts. A native database role only checks permissions at the table or view level. Once the user logs in, the database trusts every statement that role can run. It does not inspect what individual queries do in flight before they execute.

AI Agent Security: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

If an agent has read rights on a customer table, it can pull everything. The query result can include emails, phone numbers, card numbers, or national IDs. Without query-level checks, those raw values flow straight into the model context and logs. A single bad tool call can also run a slow query that locks resources.

Why static database keys create extra risk for agents

Most teams store the database password or client cert in local app secrets. Every worker running the agent needs direct network reach to the database nodes. If a worker host gets compromised, the attacker gets the static database key too.

Rotating those keys across many agent workers takes manual work and causes downtime. Database audit logs also show every action coming from the same shared app user. When a bad query runs, the team cannot tie it to one specific request. You need a way to inspect queries and mask fields like emails in results.

Check database and API queries on the wire

Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev runs beside those services as an open-source proxy, a sidecar for your Postgres or API. It inspects requests coming in and results going back on the wire.

Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run. On responses, hoop.dev data masking rewrites matched sensitive values in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.

Frequently asked questions

Which Python driver works best for this setup?

CockroachDB uses the Postgres wire protocol, so standard drivers like asyncpg or psycopg work well. Pick an async driver if your agent runs many tool calls at the same time.

How should I limit what the AI agent can query?

Create a read-only SQL role that only has select grants on specific tables. Never give the agent a root role or permission to drop or alter tables.

How do I keep sensitive fields out of the model prompt?

Database grants alone return whole columns unless you maintain separate views for every table. Dynamic data masking rewrites matched fields like card numbers or phone numbers in query results.

The query runs. The emails stay hidden.