How to Configure Pydantic AI Postgres Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a pydantic ai postgres prevent delete rule by limiting the database role. You give the agent role read, insert, and update rights, and leave out delete rights. Many teams start by giving their agent a broad database login to move fast. That broad login lets one bad prompt wipe out whole tables in seconds. A safer setup removes delete rights at the database level before the agent runs. How to set up pydantic ai postgres prevent delete rules To build a pydantic ai postgres prevent de

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 postgres prevent delete rule by limiting the database role. You give the agent role read, insert, and update rights, and leave out delete rights. Many teams start by giving their agent a broad database login to move fast. That broad login lets one bad prompt wipe out whole tables in seconds. A safer setup removes delete rights at the database level before the agent runs.

How to set up pydantic ai postgres prevent delete rules

To build a pydantic ai postgres prevent delete setup, start inside the database. Do not rely on text rules in the agent prompt to stop data loss. Prompts can fail when users send tricky inputs or when models make mistakes. Instead, create a dedicated group role in Postgres just for your AI agent. Grant the agent user membership in this group role to keep rules clean.

Next, give that group role only the exact table rights it needs. Grant read rights so the agent can look up rows and specific columns. If the agent must write data, grant insert or update rights on those tables. Never grant the delete right on any table or view in the database. When the agent tries to run a delete statement, Postgres rejects it right away.

You also need to check the default rights given to the special PUBLIC role. In Postgres, the PUBLIC role gives basic rights to every role on the system. By default, PUBLIC gets the right to log in and run functions and procedures. If a stored function deletes rows, your agent might run it through PUBLIC rights. The object owner must revoke execute rights from PUBLIC on any risky functions.

Check schema rights too, especially if you run an older database or upgraded one. In Postgres 15 and later, regular users cannot create objects in the public schema. In older versions, an admin must revoke create rights on the public schema from PUBLIC. Locking down both table rights and schema defaults keeps the agent inside safe bounds.

Why native roles are only the first step for agent safety

Native Postgres roles do a great job of blocking direct delete statements on tables. They decide who can log in and which broad table actions are allowed. Still, native roles cannot inspect what an individual statement does while it runs. An agent with update rights can still set every column in a table to null. That bad update ruins your data just as fast as a delete statement.

AI Data Exfiltration Prevention: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Table roles also apply the same static rules to every query from that agent. Sometimes an agent needs to delete one stuck row during a support task. If you grant delete rights for that task, the agent keeps them for all queries. Native roles cannot ask a human to approve a single risky command in flight.

Finally, database roles do not filter sensitive fields in the query results. You can grant read rights on specific columns, which also allows copying data out. Yet if the agent reads a text column, raw emails or card numbers return unchanged. Protecting production data requires checking queries and masking results like phone numbers in real time.

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 prompt instructions alone stop a Pydantic AI agent from deleting Postgres rows?

No, prompt rules are only suggestions to the language model and can fail. You must remove delete rights from the Postgres role so the database blocks the query.

Why can my agent still delete data after I revoke table delete rights?

The special PUBLIC role gets execute rights on functions and procedures by default. Your agent might be calling a function that deletes rows, so revoke execute from PUBLIC.

How do I let an AI agent read only certain columns in Postgres?

Postgres lets you grant the read privilege on specific columns of a table or view. Grant read rights only on safe columns and leave out columns with private keys.

The query runs. The emails stay hidden.