How to Configure Pydantic AI Postgres Permissions
You set up pydantic ai postgres permissions by making a dedicated database role for your agent. Then you grant that role read rights only on the exact tables and columns it needs. Many teams skip this step when they first build an AI tool. They often pass the main app login to the agent framework. That login usually has full write rights across every table in the database. If a prompt tricks the agent, the model can delete rows or dump private records. How pydantic ai postgres permissions work
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 permissions by making a dedicated database role for your agent. Then you grant that role read rights only on the exact tables and columns it needs. Many teams skip this step when they first build an AI tool. They often pass the main app login to the agent framework. That login usually has full write rights across every table in the database. If a prompt tricks the agent, the model can delete rows or dump private records.
How pydantic ai postgres permissions work in your database
Pydantic AI connects to your database through the standard client library you give its tools. The agent framework itself does not limit what SQL statements run on the server. Instead, the Postgres role you use for the login controls every action the agent takes. When your Python code runs a tool function, the database checks the privileges of that role.
To lock down the agent, you start with a group role in Postgres. Group roles let you manage privileges in one place when you have many users or services. You grant individual service accounts membership in that group role. Next, you give the group role read rights on specific tables or views. Postgres lets you grant the select privilege on specific columns of a table or view. This privilege also lets the role export data from those allowed columns.
You should also check the public role in your database. The special public role grants rights to every role on the system. By default, every role gets rights to log in to databases and create temp tables. Every role can also run functions and procedures unless the owner removes those defaults. In Postgres 15 and newer, regular users cannot create new objects in the public schema by default. On older databases or upgraded systems, an admin must revoke that create right from the public role.
Where native database roles fall short for AI agents
Native database roles do a great job of blocking broad access to whole tables. They stop an agent from dropping tables or reading secret admin schemas. Still, database roles only check who logs in at the start of a session. They do not inspect what individual queries do while they run.
First, column privileges are an all-or-nothing rule. If an agent needs a customer table to check order status, it might also read emails. Sometimes you want the query to run while masking emails, phone numbers, or card numbers in results. Standard Postgres roles cannot rewrite matched fields in query results on the fly.
AI Agent Permissions: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Second, all agent calls usually share one database user account. Your database logs will show thousands of queries coming from that single bot role. You cannot tell which human user or app request triggered a specific SQL statement. You also cannot require a quick human approval when an agent tries to run an unusual update.
Best practices for scoping agent database tools
Keep your AI tools away from raw SQL generation whenever you can. Write fixed functions in your Python code that take strict input types. Let Pydantic validate those inputs before the function builds a safe, parameterized query. This pattern stops the model from inventing arbitrary join clauses or scanning huge tables.
When an agent must write data, split read and write tasks into separate database roles. Use a read-only role for all research and lookup tools. Use a separate role with narrow insert rights for tools that save state. Set a short statement timeout on both roles so bad queries do not lock up your server. Review your schema privileges after every migration to make sure new tables stay locked down.
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
Can I restrict an AI agent to specific columns in Postgres?
Yes, Postgres lets you grant the select privilege on specific columns of a table or view. The agent role will get a permission error if it queries any other column on that table.
Why can my read-only agent role still run database functions?
Postgres grants the special public role the right to run functions and procedures by default. An object owner or admin must revoke execute rights from the public role to stop this.
How do I stop an agent from creating tables in the public schema?
Postgres 15 and newer block this by default for standard roles. On older or upgraded databases, an admin must revoke the create privilege on the public schema from public.
Related guides
In this post