How to Configure Pydantic AI Postgres Approval

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up pydantic ai postgres approval by pausing agent tool calls for human review. You also pair those checks with strict database roles that limit query scope. Many engineering teams start by giving an AI agent a broad database login. That shortcut creates high risk when a model generates a bad query. A single wrong update or delete statement can ruin production tables in seconds. Teams need clear guardrails before an agent runs any SQL statement. How pydantic ai postgres approval works w

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 postgres approval by pausing agent tool calls for human review. You also pair those checks with strict database roles that limit query scope. Many engineering teams start by giving an AI agent a broad database login. That shortcut creates high risk when a model generates a bad query. A single wrong update or delete statement can ruin production tables in seconds. Teams need clear guardrails before an agent runs any SQL statement.

How pydantic ai postgres approval works with database roles

A safe agent setup starts inside the database itself. You create group roles to manage permissions across many users or agent services. Then you grant each agent role membership in the right group role. For read tasks, you grant the SELECT privilege on specific tables or views. You can even grant SELECT on specific columns of a table. Keep in mind that the SELECT privilege also allows COPY TO operations.

Next, you must check the built-in PUBLIC role on your database server. The special role name PUBLIC grants privileges to every role on the system. By default, PUBLIC gets CONNECT and TEMPORARY privileges on databases. It also gets EXECUTE privileges on functions and procedures by default. The object owner can revoke these default privileges to lock down the agent. In PostgreSQL 15 and later, users cannot create objects in the public schema by default. On older or upgraded databases, an admin revokes CREATE on the public schema from PUBLIC.

After you lock down the database role, you build the app check. Your Python agent uses structured data models to define valid tool inputs. When the model wants to run a SQL query, it fills out that schema. Your app code catches this tool call before it reaches the database driver. If the request changes data, the app pauses the run and asks a human.

Why native role setup leaves an operational gap

Native database roles and app code checks solve the first part of the problem. Database privileges decide which tables and columns an agent role can read or change. App checks help validate the shape of arguments before the agent runs a tool. Together, they stop an unauthorized user from logging in or touching restricted schemas.

Still, static database roles only decide who logs in and which objects they touch. They do not inspect what individual SQL statements do in flight during a session. If an agent needs write permissions for its job, the database allows any valid update. The database cannot tell a safe one-row update from an accidental full-table wipe. Also, building custom approval queues inside every Python app takes a lot of time. Each team ends up writing different Slack bots, state stores, and timeout rules.

Approval Chains & Escalation + AI Agent Security: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Sensitive data exposure is another major gap with standard database roles. Even if you restrict columns, a table might still hold emails or phone numbers. If an agent queries that column, raw card numbers or national IDs enter the prompt. Native grants are all-or-nothing per column and do not rewrite sensitive fields in results.

Designing a human review workflow for agent queries

A strong approval workflow separates read queries from write queries right away. Safe read queries on approved tables can run right away without waiting for a person. Queries that change rows, drop objects, or touch sensitive tables should trigger a pause. The request should go to a reviewer with the exact SQL text and agent context.

Reviewers need a fast way to approve or reject the pending query. Once a reviewer approves the statement, the workflow resumes and runs that exact query. Every approval decision and query result should go into a central audit log. Centralizing this review step keeps rules consistent across all your AI agents and scripts.

Run guardrails and masking on the Postgres connection

Role grants and firewall rules are needed setup. Setup alone cannot check single SQL statements or mask sensitive rows in a response. By design, hoop.dev is an open-source proxy that sits beside Postgres, RDS, or Aurora as a sidecar for your Postgres. Clients and agent tools keep their credentials and point their connection string at the listener.

On each query, hoop.dev guardrails refuse disallowed drops, deletes, or table reads before they run. Next, hoop.dev data masking rewrites sensitive fields in returned rows that match your rules. Furthermore, hoop.dev holds SOC 2 Type II and logs every statement and verdict per resource. Check out the project on GitHub.

Frequently asked questions

Can I restrict an AI agent to specific columns in PostgreSQL?

Yes, PostgreSQL lets you grant the SELECT privilege on specific columns of a table. This stops the agent role from reading other unapproved columns in that same table.

Why should I check the PUBLIC role when setting up an agent login?

The PUBLIC role grants default privileges to every role on the database system. It gives CONNECT, TEMPORARY, and function EXECUTE rights unless the object owner revokes them.

Does schema validation in Python stop destructive SQL queries?

No, data validation only checks that the tool input matches the expected types and fields. A query string can pass type checks and still delete or overwrite important production rows.

The query runs. The emails stay hidden.