How to Configure Mastra Postgres Prevent Delete
To set up a mastra postgres prevent delete rule, you restrict the database role permissions. You grant the agent role read and insert rights while leaving out delete rights. Many teams start by giving their AI agents a shared admin database login. That broad login lets a confused agent wipe out whole tables by mistake. A safer setup uses strict database roles to block bad writes before they run. How a mastra postgres prevent delete setup works Mastra agents connect to your database using stan
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 a mastra postgres prevent delete rule, you restrict the database role permissions. You grant the agent role read and insert rights while leaving out delete rights. Many teams start by giving their AI agents a shared admin database login. That broad login lets a confused agent wipe out whole tables by mistake. A safer setup uses strict database roles to block bad writes before they run.
How a mastra postgres prevent delete setup works
Mastra agents connect to your database using standard login details to run queries. When you build an agent workflow, you give it a database user and password. PostgreSQL checks the permissions of that user every time the agent sends a query. To stop row removal, you create a dedicated role just for the Mastra agent.
In PostgreSQL, you can set up group roles to manage permissions for many users. You then add the individual agent user into that specific group role. You grant the group role permission to read or add rows on specific tables. The read permission allows reading from any column or specific columns of a table. It also lets the user copy table data out when needed.
To prevent deletions, you simply never grant the delete permission to that agent role. You also need to check the built-in public role on your database server. The special public role grants rights to every single role on the system. By default, the public role gets connect and temporary rights on databases. It also gets execute rights on functions and procedures by default.
An object owner can revoke these default public rights to lock down the database. If a stored function deletes rows, a role with execute rights can run it. Removing execute rights from the public role stops the agent from running delete functions. In PostgreSQL 15 and later, regular users cannot create objects in the public schema. In older versions or upgraded databases, admins revoke create rights from the public role.
Where native database roles fall short for AI agents
Database role grants work well when an agent always needs the exact same rights. They decide who can log in and which tables that user can touch. Still, static database roles cannot inspect what an individual query does in flight. Sometimes a Mastra agent needs to delete old temporary rows during a cleanup task. If you grant delete rights for that task, the agent can delete any row.
A missing filter in an agent query can wipe out a whole customer table. Native table permissions cannot tell a safe single-row delete from a full table wipe. They also cannot pause a risky delete command to ask a human for approval. Also, column grants are hard to maintain when tables hold sensitive personal data.
End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
You can restrict read access to specific columns, which breaks queries that select all columns. When an agent reads customer tables, raw emails and phone numbers flow into prompts. Card numbers and national IDs can also end up in the model context window. Data masking rewrites matched fields in results so queries still run without exposing secrets.
Best practices for safe agent database queries
Start every Mastra project by creating a separate database role with zero default rights. Add read and write permissions only for the exact tables the agent must use. Audit your database schemas to make sure the public role has no extra grants. Check all custom functions to ensure they do not run deletes on sensitive tables.
Keep human admin credentials separate from the credentials used by your automated Mastra workflows. Log every query the agent sends so your team can review database activity later. Combine strict database roles with runtime query checks to stop unsafe commands in flight.
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
How do I stop a Mastra agent from deleting rows in PostgreSQL?
Create a dedicated PostgreSQL role for the agent and omit the delete privilege. Grant only select, insert, or update rights on the specific tables the agent uses.
Can the PostgreSQL public role let an agent delete data by mistake?
The public role gets execute rights on functions and procedures by default. If a function deletes data, you must revoke public execute rights on that function.
Why are static PostgreSQL roles not enough for AI agent safety?
Static roles apply the same broad rules to every query from that user session. They cannot block a query missing a filter or rewrite phone numbers in results.
Related guides
In this post