How to Protect Your Database from Accidental DROP DATABASE

🚨 The Problem

Today a customer asked a great (and very real) question:

“What if a DBA accidentally runs DROP DATABASE on a production system? Can YugabyteDB stop that, even if it’s an admin user?”

At first glance, this sounds like a perfect use case for a PostgreSQL DDL trigger.

But here’s the catch…

💡 Key Insight

You cannot block DROP DATABASE using a trigger in PostgreSQL or YugabyteDB.

Why?

DROP DATABASE operates on a shared/global object.

Event triggers do not fire for shared objects (databases, roles, tablespaces).

👉 That means no trigger, hook, or policy will intercept it.

⚖️ What Actually Works?

To truly protect a database, you need a layered approach:

1. RBAC (Role-Based Access Control) → Prevent the mistake
2. PITR / Snapshots → Recover from the mistake

🔍 Quick Comparison

Approach	Works for DROP DATABASE?	What It Protects Against	Notes
DDL / Event Triggers	❌ No	Other DDL operations	Does not fire for databases
RBAC (NOLOGIN Owner)	✅ Yes	Accidental DBA actions	Best prevention mechanism
PITR / Snapshots	✅ Yes	Superuser mistakes	Your safety net

🧱 Layer 1: Prevent the Drop (RBAC Pattern)

The most effective protection is simple:

👉 Make sure your DBAs are NOT the database owner and NOT superusers

Step 1: Create a NOLOGIN owner

				
					CREATE ROLE prod_db_owner NOLOGIN;

Step 2: Create the protected database

				
					CREATE DATABASE app_prod OWNER prod_db_owner;

Step 3: Create a day-to-day DBA role

				
					CREATE ROLE dba_ops LOGIN PASSWORD 'secure-password' NOSUPERUSER;

Step 4: Grant access (without ownership)

				
					GRANT CONNECT ON DATABASE app_prod TO dba_ops;

\c app_prod

GRANT USAGE, CREATE ON SCHEMA public TO dba_ops;
GRANT ALL ON ALL TABLES IN SCHEMA public TO dba_ops;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO dba_ops;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO dba_ops;

🔧 Make It Production-Ready (Future Objects)

				
					-- Ensure dba_ops can manage future objects too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO dba_ops;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO dba_ops;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON FUNCTIONS TO dba_ops;

⚠️ Common Gotcha

Without ALTER DEFAULT PRIVILEGES, your DBA role may suddenly hit permission denied errors when new tables are created.

This is one of the most common permission issues in real-world PostgreSQL and YugabyteDB environments.

🚫 Test It

				
					\c yugabyte dba_ops

DROP DATABASE app_prod;

👉 This will fail, because:

● dba_ops is not the owner
● dba_ops is not a superuser

🛡️ Layer 2: Recover from the Drop (PITR / Snapshots)

Now for the hard truth:

👉 If someone logs in as yugabyte, they can drop anything

No SQL-level protection can stop a superuser.

That’s why you need a recovery layer.

🛡️ Defense in Depth: Prevention + Recovery

RBAC protects you from accidental drops by DBAs.

But PITR protects you from catastrophic mistakes by superusers.

With PITR enabled:

● The underlying data is retained for a configured time window
● You can restore to a point before the drop occurred
● The database can be recovered even after deletion

Best Practice: Always enable PITR or snapshots for critical production systems.

🧠 Practical Production Guidance

For real-world environments:

● Never use yugabyte for daily work
● Treat yugabyte like root access
● Use named roles for accountability (dba_ops, etc.)
● Keep database ownership separate from login roles
● Always enable PITR or backup strategy

🎯 Final Takeaway

❌ You cannot block DROP DATABASE with a trigger
✅ You can prevent it with proper role design
🛡️ You must assume it will eventually happen anyway

Accidental deletes aren’t a matter of if … they’re a matter of when.

You can’t SQL-trigger your way out of a superuser…
but you can design your system so one mistake doesn’t become a disaster.

The winning strategy is simple:

● Prevent accidents with RBAC
● Survive accidents with PITR

Have Fun!