How to Configure Pydantic AI Postgres Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a pydantic ai postgres connect workflow by passing a database pool into your agent dependencies. You then write tool functions that run SQL queries through that pool. Many teams start by giving the agent a shared app login with broad database rights. When an agent uses a broad role, one bad prompt can read or change sensitive tables. How a pydantic ai postgres connect workflow works Pydantic AI agents use dependency injection to share resources with their registered tools. You crea

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 connect workflow by passing a database pool into your agent dependencies. You then write tool functions that run SQL queries through that pool. Many teams start by giving the agent a shared app login with broad database rights. When an agent uses a broad role, one bad prompt can read or change sensitive tables.

How a pydantic ai postgres connect workflow works

Pydantic AI agents use dependency injection to share resources with their registered tools. You create a database connection pool when your app starts up. You then pass this pool into the agent context during each run. When the model decides to fetch data, it calls your Python tool function. That tool pulls a session from the pool and runs the query.

Because the agent runs queries on its own, database role setup is vital. You should never give the agent your main app owner role. Instead, create a dedicated Postgres role just for the agent. You can set up group roles to manage permissions when you have many users or services. You then grant your agent role membership in a read-only group role.

In Postgres, the SELECT privilege lets a role read from a table or view. You can also grant SELECT on specific columns of a table. This lets you expose safe columns while hiding sensitive ones on the same table. Note that the SELECT privilege also allows the COPY TO command.

You must also check the default rights that Postgres gives to every role. The special role name PUBLIC grants permissions to every role on the system. By default, PUBLIC gets CONNECT and TEMPORARY rights on databases. It also gets EXECUTE rights on functions and procedures. The database owner can revoke these default rights from PUBLIC to lock down the agent.

Schema rights matter too when you set up an agent login. In PostgreSQL 15 and later, regular users cannot create objects in the public schema by default. Older databases, or ones upgraded from older versions, might still allow this. On those systems, an admin must revoke CREATE on the public schema from PUBLIC.

What native roles solve and what they leave open

Native Postgres roles and column grants give you a strong base layer of security. They decide who can log in and which tables or columns a role can read. If an agent tries to drop a table without permission, Postgres blocks the command right away. If you revoke EXECUTE from PUBLIC, the agent cannot run arbitrary database functions either.

AI Agent Security: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, static database roles leave an operational gap during live agent runs. A role check happens when the session starts and when a query targets an object. It does not inspect what individual statements do in flight or what data comes back. If an agent needs a customer table to answer support questions, it needs SELECT on that table.

Once the agent has SELECT on a column, it gets every raw value in that column. A single query can return thousands of emails, phone numbers, card numbers, or national IDs. Those raw values flow right into the model context window and third-party model logs. Even with column grants, you must maintain complex views if a column mixes safe and sensitive text.

Static roles also make it hard to tie a query back to a human user. The database log only sees the shared agent role name. It cannot tell which end user or prompt triggered a heavy or risky query. Teams also struggle when an agent needs occasional write rights for a specific task. Giving the agent permanent write rights is dangerous, but revoking and granting roles by hand is slow.

Check every Postgres query on the wire

Database roles and network rules decide who can open a session. They do not read what each SQL statement does after login. With hoop.dev, you run an open-source proxy, a sidecar for your Postgres. It runs beside your database and reads the Postgres wire protocol. Your apps and agents keep their login details and change only the host and port in the connection string their tool uses.

Before a query runs, hoop.dev guardrails check the statement by its effect, table, or pattern. They refuse bad deletes or drops with a real Postgres error. On the way back, hoop.dev data masking rewrites sensitive values in returned rows based on your rules. Every query and verdict goes to the audit trail. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and generates evidence for HIPAA, GDPR, and PCI DSS. See the code on GitHub.

Frequently asked questions

How does a Pydantic AI agent run Postgres queries?

You define a Python tool function on the agent and pass a database pool through run dependencies. The model calls the tool with structured inputs, and your code runs the query.

How do I make a Postgres role read-only for an AI agent?

Create a new role, revoke default PUBLIC rights as needed, and grant SELECT only on required tables or columns. In databases before PostgreSQL 15, also revoke CREATE on the public schema from PUBLIC.

Can Postgres column permissions stop sensitive data from reaching the model?

Granting SELECT on specific columns blocks the agent from reading excluded columns on that table. Still, if a permitted column contains emails or card numbers, Postgres returns those raw values in results.

The query runs. The emails stay hidden.