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