How to Configure Codex Postgres Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up codex postgres prevent delete rules, you must restrict the database role. You assign a role that allows reading rows and blocks row removal. Teams often give AI coding tools a single shared database login to speed up work. When that login has broad rights, an automated script can wipe out live tables. A bad prompt or a simple logic bug can drop rows in seconds. How to set up codex postgres prevent delete permissions PostgreSQL controls table actions through role privileges. To stop

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 codex postgres prevent delete rules, you must restrict the database role. You assign a role that allows reading rows and blocks row removal. Teams often give AI coding tools a single shared database login to speed up work. When that login has broad rights, an automated script can wipe out live tables. A bad prompt or a simple logic bug can drop rows in seconds.

How to set up codex postgres prevent delete permissions

PostgreSQL controls table actions through role privileges. To stop an AI agent from removing rows, you create a dedicated role. You grant this role the privilege to read tables, views, and specific columns. You leave out the privilege that lets a user remove rows from tables. When the agent runs a query to remove rows, the database rejects it.

Database admins often use group roles when many users or tools need similar rights. You can create a read or write group role without delete rights. Then you add the agent login as a member of that group role. This keeps permission management simple as your team adds more automated tools.

You must also check the default rights given to the special public group. Every role on the system belongs to this public group at all times. By default, the public group can log in to databases and create temporary tables. It can also run functions and procedures across the database. If a function deletes data, the agent could call that function to remove rows. The object owner must revoke execute rights from the public group on dangerous functions.

Schema rules also play a big part in keeping tables safe. In PostgreSQL 15 and newer, regular users cannot create objects in the public schema. Older databases still let the public group create new tables and views there. On those older systems, an admin must revoke that creation right from the public group. This stops an AI tool from building shadow tables or replacing existing objects.

What native database roles solve and what they miss

Native database roles do a great job of blocking direct row removal. Once you strip delete privileges from the role, simple removal statements fail right away. The database engine checks the role before it touches any stored table data. This gives your team a strong baseline defense against accidental data loss.

Still, static database roles only check who logs in and which table they touch. They cannot check the intent or size of an individual statement in flight. For example, an agent might still need update rights to fix bad records. With update rights, a flawed query without a filter can overwrite every single row. Setting every email or phone number column to null destroys data just like a delete.

End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static roles also do not stop an agent from reading sensitive fields in bulk. The privilege to read a table also allows copying whole tables out at once. If a table holds card numbers or national IDs, the agent reads them all. Column permissions can block whole columns, but they break queries that expect full schemas. Teams also struggle when an agent needs to delete one bad test row safely. Granting delete rights for five minutes means changing database grants and remembering to undo them.

Why shared credentials create extra risk for AI agents

AI coding agents reach databases through local shell tools or external tool servers. Developers usually paste a database login string into a local settings file. That file sits on a laptop or inside a cloud workspace in plain text. Every command the agent runs uses that exact same static database identity.

When five developers share one agent role, the database log shows one role name. You cannot tell which person or which prompt triggered a bad query. If someone needs to run a cleanup script, they often switch to an admin login. Developers rarely switch back to the restricted role after the cleanup task finishes. The AI tool then inherits full admin power for the rest of the day.

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 sits inline in the Postgres protocol beside Postgres, RDS, or Aurora as a sidecar for your Postgres. On every production query from an engineer or AI agent, hoop.dev decides whether to pass, mask, block, or hold it for approval.

First, hoop.dev guardrails refuse disallowed drops or deletes before they run, and risky statements can pause for human review. Next, hoop.dev data masking rewrites sensitive fields in returned rows in memory. 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 PostgreSQL block delete statements on specific tables only?

Yes, admins grant and revoke rights one table at a time in PostgreSQL. You can allow row removal on scratch tables while blocking it on live customer tables.

Why can an AI agent still drop rows after I revoke delete rights?

The agent might call a stored procedure that runs with the owner's higher rights. Also, the public group grants execute rights on functions by default unless you revoke them.

Does revoking delete rights stop an agent from ruining table data?

No, an agent with update rights can still overwrite every row with blank values. You must inspect individual statements in flight to block broad updates that lack row filters.

The query runs. The emails stay hidden.