How to Configure Goose Postgres Prevent Delete
To set up a goose postgres prevent delete rule, strip delete rights from the agent role. Grant the agent role only read and insert rights on your target database tables. Teams often give an AI coding agent broad database owner keys to move fast. Broad keys let a bad prompt wipe rows or drop production tables by mistake. How to set up goose postgres prevent delete with database roles PostgreSQL controls table actions through role grants. Instead of sharing a main admin login, create a group ro
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 goose postgres prevent delete rule, strip delete rights from the agent role. Grant the agent role only read and insert rights on your target database tables. Teams often give an AI coding agent broad database owner keys to move fast. Broad keys let a bad prompt wipe rows or drop production tables by mistake.
How to set up goose postgres prevent delete with database roles
PostgreSQL controls table actions through role grants. Instead of sharing a main admin login, create a group role for your agent. You can set up group roles to manage permissions across many users and services. Then you grant the agent user membership in that group role.
Give this role the privilege to read from specific columns or whole tables. The read privilege also lets the role export data out of the table. If the agent must write data, grant insert and update rights without delete rights. Never grant the delete privilege to the agent role.
Check the public role settings on your database as well. The special public role grants rights to every role on the system. By default, every role gets rights to log in and make temporary tables. Every role also gets the right to run functions and procedures. An object owner can take back these default rights at any time. If a stored function deletes rows, the agent could run it by default. Take back function run rights from the public role to close this path.
Check your schema creation rules too. In PostgreSQL 15 and newer, regular users cannot make objects in the public schema. Older databases, or upgraded ones, may still let any role create objects there. An admin should take away schema creation rights from the public role on older systems.
What native database roles solve and what they miss
Native database roles stop direct row removal when the agent runs a delete query. The database engine checks the role grants and rejects the command right away. This setup is a good first step for any team running automated tools.
End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, database roles only decide who can log in and which table actions work. They do not inspect what individual queries do in flight. An agent with update rights can still set every column in a table to blank. That mistake ruins production data just as fast as a delete command.
Static grants also treat every query from the same login the same way. If an agent needs delete rights for one cleanup task, the role keeps that right forever. You must also manage default grants for every admin role that builds new tables. If a developer forgets one grant rule, the agent gets the wrong access.
Finally, column read grants require manual updates whenever your schema changes. If an agent reads a table with customer emails or phone numbers, raw values return. Column grants are all or nothing, whereas data masking rewrites matched fields in results. Teams need a way to inspect commands and rewrite sensitive fields like card numbers.
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 PostgreSQL role update data without having delete rights?
Yes. PostgreSQL separates insert, update, and delete privileges at the table level. You can grant a role read, insert, and update rights while leaving out delete rights.
Why can my agent still run a function that deletes rows?
PostgreSQL grants function run rights to the public role by default. If a function runs with owner rights, any user can call it unless you revoke execution.
Do default privilege rules cover tables made by all users?
No. Default privilege changes only apply to objects made by the specific role you name. If another admin creates a new table, you must set rules for that admin too.
Related guides
In this post