pg_savior

pg_savior : Postgres extension to save OOPS mistakes

Overview

Packages

Source

pig build pkg pg_savior;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_savior;		# install via package name, for the active PG version

pig install pg_savior -v 18;   # install for PG 18
pig install pg_savior -v 17;   # install for PG 17
pig install pg_savior -v 16;   # install for PG 16
pig install pg_savior -v 15;   # install for PG 15
pig install pg_savior -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pg_savior;

Usage

pg_savior: Postgres extension to save OOPS mistakes

The pg_savior extension intercepts query execution to prevent accidental data deletion. It hooks into the executor to detect dangerous DELETE operations and block them.

How It Works

When a DELETE statement is processed, pg_savior checks for:

• Missing WHERE clauses on DELETE commands
• Index scan operations in DELETE query plans
• Complex queries involving CTEs and subqueries in DELETE operations

When a risky DELETE is detected, the extension prevents execution and returns an informational message with zero rows affected.

Example

CREATE EXTENSION pg_savior;

-- Attempting a DELETE without WHERE clause
DELETE FROM emp;
-- INFO:  pg_savior: DELETE statement detected
-- INFO:  pg_savior: WHERE clause is mandatory for a DELETE statement
-- DELETE 0  (no rows affected, data preserved)

-- Normal DELETE with WHERE clause works as expected
DELETE FROM emp WHERE id = 42;
-- DELETE 1

Notes

• The extension operates through PostgreSQL executor hooks, requiring no changes to application code
• Only DELETE statements are currently intercepted; UPDATE operations are not affected
• Planned features include preventing CREATE INDEX without CONCURRENTLY and blocking DROP DATABASE