How to Configure RDS Postgres Drop Table Protection

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up rds postgres drop table protection by separating table ownership from user roles. You also block destructive commands before they run on your database. Most teams start by giving developers shared admin accounts to move fast. Broad permissions let one wrong command delete production tables in seconds. Recovering from a dropped table takes hours of stressful backup work. A solid permission model stops these accidents before they happen. How rds postgres drop table protection works wi

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 rds postgres drop table protection by separating table ownership from user roles. You also block destructive commands before they run on your database. Most teams start by giving developers shared admin accounts to move fast. Broad permissions let one wrong command delete production tables in seconds. Recovering from a dropped table takes hours of stressful backup work. A solid permission model stops these accidents before they happen.

How rds postgres drop table protection works with roles

In PostgreSQL, only the table owner or a superuser can drop a table. Standard read and write permissions do not include the right to drop objects. To protect tables, you must control which role creates and owns them. You can set up group roles to manage privileges when you have many users. You then grant individual users membership in the right group role.

A dedicated migration role should create all tables during deployments. Human users and app accounts should log in with separate roles. Those daily roles only get permissions to read or change rows. For example, the SELECT privilege lets a role read from any column of a table. It also lets you limit reads to specific columns on a view or table.

You must also check default rules for the special role name PUBLIC. The PUBLIC role grants a privilege to every role on the system. By default, PUBLIC gets CONNECT and TEMPORARY rights on databases. It also gets EXECUTE rights on functions and procedures. The object owner can revoke these default rights at any time. 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 that CREATE right from PUBLIC. If a user cannot create a table, they cannot become its owner.

Why native roles leave a gap during live fixes

Role separation works well for routine app traffic and read-only queries. It keeps analysts and apps from dropping tables they do not own. Still, native database roles have a major limit during live incidents. Database permissions check who logs in and which role they hold. They do not inspect what individual statements do in flight once an owner role logs in.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

During an outage or migration, an engineer often needs the owner role to fix a schema. Once they use that role, the database allows every destructive command on owned tables. A single typo in a script can drop the wrong production table. Also, read permissions on sensitive tables still return raw emails, phone numbers, and card numbers. Native roles alone cannot block one bad command from an owner or mask fields in results.

Adding event triggers as a second check

PostgreSQL also supports event triggers that fire when someone runs schema change commands. An admin can write a function that checks for drop statements and aborts the transaction. This adds a helpful safety net inside the database engine itself. Yet a user with high privileges can still disable the trigger before running a script. Teams need a way to require peer review on single commands and mask sensitive results.

Add query and result controls beside Postgres

Grants and pools govern who logs into PostgreSQL. Yet every query in an open session still runs with that role's full scope. In practice, hoop.dev sits inline in the wire protocol beside your database as a sidecar for your Postgres. For every plain or prepared query an agent or human runs against production, hoop.dev decides to pass, mask, block, or escalate it for review.

When a tool or agent sends SQL, hoop.dev Postgres controls block banned statements before execution or hold risky ones for human approval via an AI Analyzer you configure. Matched sensitive columns in result rows are masked in memory before they reach the caller. Each query and decision is logged per listener, which generates evidence for SOC 2, HIPAA, and GDPR audits. View the binary on GitHub.

Frequently asked questions

Can a user with DELETE rights drop a table in RDS Postgres?

No, the DELETE right only lets a user remove rows inside a table. Only the table owner or a superuser can drop the table object itself. Note that a careless DELETE command without a filter still wipes all row data.

How do I stop users from owning new tables in the public schema?

PostgreSQL 15 and later block PUBLIC from creating objects in the public schema by default. On older databases, you revoke the CREATE right on the public schema from PUBLIC. Then only your dedicated deployment role can create and own new tables.

What happens if an engineer needs to run a safe schema change?

If an engineer logs in with the table owner role, they can drop any table it owns. Instead of sharing that role, require peer approval on each schema change command before it runs. This lets safe changes pass while stopping accidental drop commands.

The query runs. The emails stay hidden.