How to Configure Pydantic AI Postgres Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up pydantic ai postgres read only access, create a database role with only SELECT privileges. Pass that role to your agent through its database dependency object. Many teams start by giving agents standard app credentials. Those broad roles let a bad prompt change or delete rows. A strict read role stops write queries at the database level. How pydantic ai postgres read only access works in PostgreSQL Pydantic AI agents run database queries through tool functions that you define. You p

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 pydantic ai postgres read only access, create a database role with only SELECT privileges. Pass that role to your agent through its database dependency object. Many teams start by giving agents standard app credentials. Those broad roles let a bad prompt change or delete rows. A strict read role stops write queries at the database level.

How pydantic ai postgres read only access works in PostgreSQL

Pydantic AI agents run database queries through tool functions that you define. You pass a database client into the agent run context. The database itself must enforce read rules on that client connection. System prompts alone will not stop an agent from running write queries.

In PostgreSQL, you can set up a group role to manage these privileges. You then grant your agent login user membership in that group role. The SELECT privilege allows reading from any column of a table or view. You can also grant SELECT on only specific columns of a table. This lets you leave out secret columns when you set up the role. Note that the SELECT privilege also allows the COPY TO command.

You must also check the 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. An object owner can revoke these default grants from PUBLIC. In PostgreSQL 15 and later, default settings block PUBLIC from creating objects in the public schema. On older or upgraded databases, an admin must revoke CREATE on the public schema from PUBLIC.

What native roles solve and what they leave open

Native PostgreSQL roles stop an agent from changing table schemas or deleting rows. When the agent tool sends an insert or update statement, the database rejects it. This gives your team a strong baseline defense against bad agent outputs.

Still, static database roles only check who logs in at the start. They do not inspect what individual statements do in flight. First, a role with EXECUTE on functions can still trigger side effects if a function writes data. Second, table grants do not rewrite sensitive fields inside allowed columns. If an agent reads a customer table, the query result still returns raw emails, phone numbers, card numbers, or national IDs. Third, shared agent credentials make audit logs hard to read. The database log shows the shared agent role name, not the human or task behind the prompt. Finally, static roles do not stop an agent from running a massive join that locks up CPU.

Auditor Read-Only Access + AI Model Access Control: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Securing agent tool calls in production

To run database agents safely, keep your tool design small and specific. Do not give the agent a single tool that runs raw SQL strings when a typed query works. Build separate tool functions for specific questions the agent needs to answer. Have each tool accept structured inputs validated by your data models.

Combine those typed tools with strict column grants in the database. Revoke default execute rights on stored procedures from the PUBLIC role. Set statement timeouts on the database role so slow queries fail fast. Even with these steps, teams still need visibility into every query the agent runs and a way to mask sensitive fields in results.

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 a prompt rule replace a read-only database role for an AI agent?

No, a system prompt cannot replace database permissions. Models can misread user input or follow injected instructions, so the database must block write statements itself.

Why can my read-only Postgres role still create tables or run functions?

PostgreSQL grants default rights to the special PUBLIC role, including function execution and schema creation on older versions. You must revoke those defaults from PUBLIC to lock down the read role.

How do I limit which columns a Pydantic AI agent can read?

PostgreSQL lets you grant the SELECT privilege on specific columns of a table or view. Granting access only to safe columns keeps secret columns out of the agent's reach.

The query runs. The emails stay hidden.