pg_strict

pig build pkg pg_strict         # build RPM / DEB packages

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

shared_preload_libraries = 'pg_strict';

CREATE EXTENSION pg_strict;

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

ALTER DATABASE postgres SET pg_strict.require_where_on_update = 'on';
ALTER ROLE app_service SET pg_strict.require_where_on_delete = 'on';
ALTER ROLE dba_admin SET pg_strict.require_where_on_update = 'off';

BEGIN;
SET LOCAL pg_strict.require_where_on_delete = 'off';
DELETE FROM temp_table;  -- allowed within this transaction
COMMIT;

SELECT pg_strict_version();           -- extension version
SELECT pg_strict_config();            -- all settings with values and descriptions

-- Validate queries programmatically
SELECT pg_strict_check_where_clause('DELETE FROM t', 'DELETE');  -- returns boolean
SELECT pg_strict_validate_update('UPDATE t SET x=1');
SELECT pg_strict_validate_delete('DELETE FROM t');

-- Quick mode toggles
SELECT pg_strict_enable_update();     -- set update enforcement to 'on'
SELECT pg_strict_warn_delete();       -- set delete enforcement to 'warn'
SELECT pg_strict_disable_update();    -- set update enforcement to 'off'