pg_savior is a PostgreSQL extension designed to prevent accidental data loss due to non-parameterized DELETE queries without a WHERE clause. It hooks into the query execution path.
The extension is currently under active development. This project is not ready for production yet.
- Detect DELETE queries without a WHERE clause
- Detect DELETE queries which use index scan
- Detects DELETE queries which uses CTE & subqueries
- Prevent CREATE INDEX without concurrently
- Prevent DROP DATABASE command
- Clone the repository.
- Navigate to the repository directory.
- Run
maketo build the extension. - Run
sudo make installto install the extension.
$ psql -U visi -d postgres psql
(14.9 (Homebrew))
Type "help" for help.
postgres=#
postgres=# create extension pg_savior;
CREATE EXTENSION
postgres=# select * from emp;
id
----
(0 rows)
postgres=# insert into emp values(1);
INSERT 0 1
postgres=# delete from emp;
INFO: pg_savior: DELETE statement detected
INFO: pg_savior: WHERE clause is mandatory for a DELETE statement
DELETE 0
postgres=# select * from emp;
id
----
1
(1 row)
After installing the extension, you can enable it in your PostgreSQL database with the following command:
CREATE EXTENSION pg_savior;The test suite of pg_savior uses pg_yregres. To run a specific test,
pg_yregress tests/D0001.yml
TAP version 14
1..4
# Initializing instances
# Subtest: initialize instance `default`
1..2
ok 1 - create extension pg_savior
ok 2 - create table emp ( id int )
ok 1 - initialize instance `default`
INFO: pg_savior: ExecutorStart_hook called
INFO: pg_savior: ExecutorStart called
INFO: pg_savior: queryDesc->sourceText: select oid, typname from pg_type
# Done initializing instances
ok 2 - D0001: pg_savior: Check if pg_savior is installed
# Subtest: D0001: pg_savior: delete without where clause
1..6
ok 1 - insert into emp values (1)
ok 2 - insert into emp values (2)
ok 3 - insert into emp values (3)
ok 4 - select count(*) from emp as count
ok 5 - delete from emp
ok 6 - select count(*) from emp as count
ok 3 - D0001: pg_savior: delete without where clause