safeupdate
Require criteria for UPDATE and DELETE
Repository
eradman/pg-safeupdate
https://github.com/eradman/pg-safeupdate
Source
pg-safeupdate-1.5.tar.gz
pg-safeupdate-1.5.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
safeupdate | 1.5 | ADMIN | ISC | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 5820 | safeupdate | No | Yes | Yes | No | No | No | - |
| Related | pg_readonly pg_upless pg_savior pg_permissions pgaudit set_user login_hook noset |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 1.5 | 1817161514 | safeupdate | - |
| RPM | PGDG | 1.5 | 1817161514 | safeupdate_$v | - |
| DEB | PIGSTY | 1.5 | 1817161514 | postgresql-$v-pg-safeupdate | - |
Build
You can build the DEB packages for safeupdate using pig build:
pig build pkg safeupdate # build DEB packages
Install
You can install safeupdate directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install safeupdate; # Install for current active PG version
pig ext install -y safeupdate -v 18 # PG 18
pig ext install -y safeupdate -v 17 # PG 17
pig ext install -y safeupdate -v 16 # PG 16
pig ext install -y safeupdate -v 15 # PG 15
pig ext install -y safeupdate -v 14 # PG 14
dnf install -y safeupdate_18 # PG 18
dnf install -y safeupdate_17 # PG 17
dnf install -y safeupdate_16 # PG 16
dnf install -y safeupdate_15 # PG 15
dnf install -y safeupdate_14 # PG 14
apt install -y postgresql-18-pg-safeupdate # PG 18
apt install -y postgresql-17-pg-safeupdate # PG 17
apt install -y postgresql-16-pg-safeupdate # PG 16
apt install -y postgresql-15-pg-safeupdate # PG 15
apt install -y postgresql-14-pg-safeupdate # PG 14
Preload:
shared_preload_libraries = 'safeupdate';
Usage
The safeupdate extension prevents accidental mass data changes by raising an error whenever UPDATE or DELETE statements are executed without a WHERE clause.
Activation
-- Per-session
LOAD 'safeupdate';
-- Per-database (persistent)
ALTER DATABASE mydb SET session_preload_libraries = 'safeupdate';
-- Global (all databases, requires restart)
-- shared_preload_libraries = 'safeupdate' -- in postgresql.conf
Behavior
-- Blocked: UPDATE without WHERE
UPDATE rack SET fan_speed = 70;
-- ERROR: UPDATE requires a WHERE clause
-- Blocked: DELETE without WHERE
DELETE FROM rack;
-- ERROR: DELETE requires a WHERE clause
-- Allowed: with WHERE clause
UPDATE rack SET fan_speed = 90 WHERE fan_speed = 70;
-- Workaround: explicit always-true condition
UPDATE rack SET fan_speed = 90 WHERE 1 = 1;
Administrative Override
-- Temporarily disable protection in current session
SET safeupdate.enabled = 0;
CTE-based modifications without WHERE conditions are also blocked. The extension is particularly useful with PostgREST or other systems that provide direct write access to the database.
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.