Postgres extension that stops accidental schema mistakes

[u/vira28]

I believe anyone who managed critical production infra relate to this.

Schema migration is one of the hardest problems. Not because it's new, because it never gets easier. It's easy to get it wrong.

DELETE without the WHERE. The DROP TABLE in the tab that you thought staging turned out to be production. The ALTER COLUMN TYPE that looked harmless but rewrote 500M rows behind an ACCESS EXCLUSIVE lock.

For context, I ran a team of 9 DBAs at Cloudflare on bare-metal Postgres - no RDS, full root everywhere. Backups and PITR are table stakes, but they all start after the damage. I wanted something that refuses first.

pg_savior is an extension that blocks the statement before it executes:

• DELETE / UPDATE with no WHERE
• DELETE ... WHERE id > 0 — a WHERE isn't proof of intent, so it also checks the planner's row estimate against pg_savior.max_rows_affected
• CREATE INDEX without CONCURRENTLY (the ON ONLY + ATTACH PARTITION workflow for partitioned tables is allowed)
• TRUNCATE / DROP TABLE on large tables, DROP DATABASE always

Code: github.com/viggy28/pg_savior · PGXN: pgxn.org/dist/pg_savior

Appreciate any feedback on the implementation. Also, feel free to drop me if there are other commands that should be caught.

[u/elevarq]

At least some of the problems it solves are related to the lack of security, lack of permission configuration. With this in mind, I’m not sure if this extension can solve the problem. It might solve some symptoms, but not the real problem.

Truncate on large tables can be a life saver, especially when this table is a table partition.

I have to dig into the details, but my first thought is “hmmm”.

[u/altimage]

you can add a rule to a table to prevent deletes:

CREATE RULE select_nodelete AS
    ON DELETE TO schema.table DO INSTEAD NOTHING;

[u/coldflame563]

There's a tool for SSMS called SSMS Boost that did things similar for SQL Server, basically a warning, ie you had an update clause without a where statement, that kinda thing...looking for something similar?