pg_strict

pg_strict : Prevent dangerous UPDATE and DELETE without WHERE clause

Overview

Packages

Source

pig build pkg pg_strict;		# 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_strict;		# install via package name, for the active PG version

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

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_strict';

Create this extension with:

CREATE EXTENSION pg_strict;

Usage

Source: README, Release v1.0.5, API source

pg_strict blocks or warns on UPDATE and DELETE statements that omit a WHERE clause. It installs a post_parse_analyze_hook, so it must be loaded from shared_preload_libraries.

Required setup

-- postgresql.conf
shared_preload_libraries = 'pg_strict'

CREATE EXTENSION pg_strict;

GUCs

• pg_strict.require_where_on_update
• pg_strict.require_where_on_delete

Each setting supports off, warn, and on.

SET pg_strict.require_where_on_update = 'on';
SET pg_strict.require_where_on_delete = 'warn';

Helper functions

SELECT pg_strict_version();
SELECT * FROM pg_strict_config();

SELECT pg_strict_check_where_clause('DELETE FROM t', 'DELETE');
SELECT pg_strict_validate_update('UPDATE t SET x = 1 WHERE id = 42');
SELECT pg_strict_validate_delete('DELETE FROM t WHERE id = 42');

SELECT pg_strict_enable_update();
SELECT pg_strict_warn_delete();
SELECT pg_strict_disable_delete();

• pg_strict_set_update_mode(mode) and pg_strict_set_delete_mode(mode) provide generic mode setters.
• SET LOCAL works for one-off bulk operations inside a transaction.

Caveats

• Enforcement is presence-based, not intent-based: any non-null WHERE clause satisfies the rule.
• Only UPDATE and DELETE are checked.
• Current upstream release is 1.0.5; upstream documents PostgreSQL 13 through 18, while the Pigsty package row in db/extension.csv covers PostgreSQL 14 through 18.
• The Pigsty note about pgrx 0.17.0 is packaging/build metadata, not a documented user-facing feature change.