sql_safe_updates for Postgres: UPDATE and DELETE need WHERE

MySQL has a switch that refuses DELETE FROM orders when the statement has no WHERE. Postgres has no such switch, and its developers decided not to add one. This post covers what you can use instead: the pg-safeupdate module, triggers, and, for AI agents, a gateway that checks each statement before Postgres sees it. For each one, we show what it catches and what it misses.

We ran every statement in the tables below, and the trigger SQL as printed, on 23 October 2026: against Postgres 17.11 in Docker with a 1,000-row orders table, and against MySQL 8.4.13 for the comparison. Midplane, the gateway in the last section, is ours.

What MySQL's sql_safe_updates does

With sql_safe_updates on, MySQL refuses an UPDATE or DELETE unless its WHERE uses a key column or the statement has a LIMIT. The MySQL manual describes the client option that turns it on: "With --safe-updates, you can modify rows only by specifying the key values that identify them, or a LIMIT clause, or both." The same option (also spelled --i-am-a-dummy) runs SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;, which also caps SELECT results at 1,000 rows and refuses very large joins.

Two things to know about it. It checks the shape of the statement rather than how many rows it changes, so WHERE id > 0 passes. And it's a session setting, so any client can run SET sql_safe_updates = 0: we did that as an ordinary user with no extra privileges. It protects people from typos. It doesn't stop a client that wants to change every row.

Postgres has no built-in equivalent

There is no setting for it in Postgres, and psql has no --safe-updates option either. The nearest habit in psql is \set AUTOCOMMIT off, which leaves every change open until you COMMIT, so you can roll back.

It has been proposed on the pgsql-hackers list and turned down. In 2006, Tom Lane replied that "These syntaxes are required by the SQL spec", and that the likelier mistake is one no check would catch: DELETE FROM tab WHERE key > 1 when you meant key > 10000000. A 2016 patch from David Fetter drew the same objection, "I wouldn't have any objection to an extension that enforces rules like these, but I don't think it belongs in core", and was returned with feedback in 2017, never committed. So the answer lives outside core.

pg-safeupdate

pg-safeupdate by Eric Radman is a small loadable module under an ISC-style license, at version 1.7 (July 2026). It hooks Postgres's parse analysis and raises an error when an UPDATE or DELETE has no WHERE clause at all, including one inside a WITH. It was first written to protect tables that PostgREST exposes.

It isn't a CREATE EXTENSION extension; you load it as a library. On a server you run yourself, build it against your Postgres (you need pg_config and the server headers, postgresql-server-dev-17 on Debian):

curl -sL https://github.com/eradman/pg-safeupdate/archive/refs/tags/1.7.tar.gz | tar xz
make -C pg-safeupdate-1.7
sudo make -C pg-safeupdate-1.7 install

Then choose who gets it:

-- One session (a superuser only):
LOAD 'safeupdate';
 
-- Every new session of one role, such as the role an agent or a tool uses:
ALTER ROLE agent SET session_preload_libraries = 'safeupdate';
 
-- Every new session in one database:
ALTER DATABASE shop SET session_preload_libraries = 'safeupdate';

Or set shared_preload_libraries = 'safeupdate' in postgresql.conf and restart, which covers every connection.

The per-role setting is the useful one for tools and agents: their role gets the check, and your migrations don't. The role can't take it back. Only a superuser can change safeupdate.enabled, and as the agent role, both SET safeupdate.enabled = off and ALTER ROLE agent RESET session_preload_libraries failed with permission denied.

What it caught and what it let through, as the agent role:

The gaps:

• Any WHERE counts. WHERE true and WHERE 1 = 1 pass by design; the README gives WHERE 1=1 as the way to update every row on purpose.
• TRUNCATE isn't checked. Only UPDATE and DELETE are, so don't grant TRUNCATE to a role you're protecting.
• MERGE isn't checked. Postgres 15 and later can delete or update every row through MERGE, and the module only looks at UPDATE and DELETE statements.
• A superuser can turn it off with SET safeupdate.enabled = 0.

Where you can use it

Supabase builds pg-safeupdate into its Postgres image and preloads it for authenticator, the role its Data API (PostgREST) connects as, and its custom configuration docs list safeupdate.enabled. But an open issue, supabase/postgres#1308, reports that it can't be enabled for the postgres role or the database, so direct SQL connections, which is how most tools and MCP servers connect, may not have it. Test it on your own project before you rely on it.

Triggers

Where you can't load a module, a trigger can check the effect of a statement instead of its shape. A statement-level AFTER trigger with a transition table sees every row the statement changed. If there are too many, it raises an error, and the whole statement rolls back.

CREATE OR REPLACE FUNCTION refuse_mass_change() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  max_rows bigint := TG_ARGV[0]::bigint;
  changed  bigint;
BEGIN
  SELECT count(*) INTO changed FROM old_rows;
  IF changed > max_rows THEN
    RAISE EXCEPTION '% on % would change % rows; the limit is %',
      TG_OP, TG_TABLE_NAME, changed, max_rows
      USING HINT = 'Narrow the WHERE clause.';
  END IF;
  RETURN NULL;
END $$;
 
CREATE TRIGGER orders_update_guard
  AFTER UPDATE ON orders
  REFERENCING OLD TABLE AS old_rows
  FOR EACH STATEMENT EXECUTE FUNCTION refuse_mass_change(100);
 
CREATE TRIGGER orders_delete_guard
  AFTER DELETE ON orders
  REFERENCING OLD TABLE AS old_rows
  FOR EACH STATEMENT EXECUTE FUNCTION refuse_mass_change(100);
 
CREATE OR REPLACE FUNCTION refuse_truncate() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'TRUNCATE on % is not allowed', TG_TABLE_NAME;
END $$;
 
CREATE TRIGGER orders_truncate_guard
  BEFORE TRUNCATE ON orders
  FOR EACH STATEMENT EXECUTE FUNCTION refuse_truncate();

You need one trigger per event: Postgres refuses a transition table on a trigger with more than one, with transition tables cannot be specified for triggers with more than one event.

Because it counts rows, it catches what pg-safeupdate misses:

The gaps:

• The work happens first. The statement changes every row, writes the WAL and holds its locks, and only then rolls back. On a large table, that's a long, expensive way to say no.
• It's a threshold, not a rule. A wrong 99-row delete passes, and a legitimate 101-row cleanup fails.
• It's per table. A table created next week has no triggers until someone adds them.
• It applies to everyone. Migrations and backfills hit the limit too. To limit it to one role, check current_user at the top of the function. Don't add an escape hatch through a custom setting: if your app can set it, so can the agent.
• Owners and superusers can skip it. An owner can DISABLE TRIGGER, and a superuser can SET session_replication_role = replica, which skips triggers. Both failed for the agent role, so the role you're guarding must not own the tables. Not owning them also keeps it from running DROP TABLE, which no trigger here covers.

A gateway, for AI agents

pg-safeupdate and triggers protect the database from every client. If the client you're worried about is an AI agent, there's another place to check: between the agent and Postgres, before the statement reaches the database at all.

That's what Midplane does. It's an open-source (MIT) MCP gateway: the agent sends SQL to it over MCP, it parses each statement with Postgres's own parser, checks it against your policy, writes an audit record, and only then runs it. These rules hold whatever the policy says: an UPDATE or DELETE needs a WHERE, a write hidden inside a WITH is denied, TRUNCATE is denied, and so is anything Midplane can't analyze, MERGE included.

The policy then sorts every allowed write into one of two classes, and gives each class one of three answers:

databases:
  main:
    dsn: { env: DSN_MAIN }
    policy:
      table_access:
        tables:
          public.orders: read_write   # deny | read | read_write
          public.refunds: read
      writes:
        row_changes: hold      # INSERT, UPDATE, DELETE, CREATE TABLE: allow | hold | deny
        schema_changes: deny   # ALTER, DROP, CREATE INDEX: allow | hold | deny

allow runs the write, deny refuses it, and hold waits for a person to approve it. When nothing is set, row changes are allowed and schema changes are denied. Either way, a write is only considered on a table that is read_write, from an agent whose grant includes writes. Every key is in the configuration reference.

Here's what Midplane 0.22.0 did with the same statements, with row_changes: allow:

That last row is the same gap pg-safeupdate has. The WHERE check is about the statement's shape, and WHERE true has the right shape. A check can't tell key > 1 from key > 10000000 either, which was Tom Lane's point in 2006.

What covers it is hold. With row_changes: hold, the gateway doesn't run the write. It counts the rows the write would change, with a SELECT count(*) built from the write and run read-only. For DELETE FROM orders WHERE status = 'cancelled', that count is:

SELECT pg_catalog.count(*) AS count FROM orders WHERE status = 'cancelled'

It files the statement, the agent's stated reason and the count for a person to review, never the rows. For DELETE FROM orders WHERE true, the reviewer would see a count of 1,000, whatever the agent's reason says. If they approve, the agent sends the same statement again, and the gateway runs it in a transaction that rolls back if it changes a different number of rows than the reviewer saw. The approval is bound to that exact statement, reason and agent grant, so changing one byte makes it a new request (approvals and taint). Either way, the attempt and the decision are in the audit log before anything runs.

What Midplane doesn't do

• It only sees statements that go through it. Your app, your migrations and anyone in psql connect to Postgres directly, so keep pg-safeupdate or triggers for them. And if the agent also has a shell with a database URL, the gateway never sees those statements.
• Local mode can't approve. Approvals need a gateway linked to Midplane Cloud, which is invite-only for now (get started). In local mode a held write is refused: with row_changes: hold, that's what we got for both WHERE id = 7 and WHERE true.
• The count is a check, not a guarantee. For an upsert, the count is an upper bound, and a write that can't be counted runs without the check; the approver is told when that happens.
• It doesn't replace database permissions. It can narrow what the gateway's role may do, never widen it, so the role should still lack TRUNCATE and own nothing (prepare your database).

Which to use

All of these sit on top of the cheapest protection: don't grant UPDATE or DELETE to a role that doesn't need them, and keep backups you've restored at least once.