Your AI agent ran an UPDATE without a WHERE. How do you stop the next one?

The short answer

An UPDATE with no WHERE changes every row in the table, and Postgres has no built-in setting to refuse it. To stop an AI agent from sending one, put a guard where the agent can’t switch it off: a rule that reads the SQL before it runs, or a statement-level trigger that refuses any agent statement touching more than N rows. MySQL has sql_safe_updates; Postgres has the pg-safeupdate extension.

This post runs each guard against the same 500-row table, shows where each one stops, and covers what to do when the statement already committed.

What Postgres tells you

The failure is boring, which is why it’s common. The agent means to ship order 42. Somewhere between the plan and the tool call, the WHERE id = 42 is lost: the model drops a clause, or a tool builds SQL from a filter that came through empty. What reaches the database is this:

Code A database command in SQL. The text around it says what it does.

UPDATE public.orders SET status = 'shipped';

We built a 500-order table to match the store in our /challenge, with 160 orders already shipped, and ran the statement inside a transaction we rolled back:

Code What the screen or file shows, copied as is.

SELECT status, count(*) FROM public.orders GROUP BY 1;
  status   | count
-----------+-------
 cancelled |    37
 paid      |   223
 pending   |    80
 shipped   |   160

BEGIN;
UPDATE public.orders SET status = 'shipped';
UPDATE 500

SELECT status, count(*) FROM public.orders GROUP BY 1;
 status  | count
---------+-------
 shipped |   500
ROLLBACK;

UPDATE 500, though only 340 orders changed. Postgres counts every row the statement matched, including rows whose values didn’t change.² MySQL counts differently: by default its affected-rows value is the number of rows actually changed, unless the client connects with CLIENT_FOUND_ROWS.⁷ So the same mistake reports 500 on one database and 340 on the other, and neither number is what the agent tells you.

In the challenge’s missing-WHERE task, that’s exactly the spread: the statement changes the status of 340 orders, the tool reports 500 affected, and the agent reports 1 row.¹ Cancelled orders are now marked shipped. Nothing raised an error.

Can Postgres refuse an UPDATE without WHERE?

Not on its own. There’s no server setting for it, and grants decide which tables and columns a role may update, not how many rows. In the run below, a role with nothing but SELECT and UPDATE on orders could still reach all 500. Row-level security can narrow which rows a role sees, which shrinks the damage, but it doesn’t look for a WHERE.

The closest thing is an extension. pg-safeupdate raises an error when UPDATE or DELETE runs without conditions; you load it per session, per database with session_preload_libraries, or server-wide with shared_preload_libraries, and SET safeupdate.enabled=0 turns it off.⁴ Two caveats from its own README: it shows WHERE 1=1 as the way to update every row on purpose, so a trivially true condition passes, and the off switch is a plain SET.⁴ It’s a good guard against a person’s slip. Against an agent that reads the error and retries, it’s thinner.

MySQL has this built in. With sql_safe_updates on, UPDATE and DELETE fail unless the WHERE uses a key column, or there’s a LIMIT, or both.⁵ The error is 1175: “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.”⁶ The mysql client’s --safe-updates option, also spelled --i-am-a-dummy, sets it for the session.⁵ Like the extension, it’s a session setting, so it guards the session’s habits, not its intent.

A row limit inside the database

Here is a guard the agent can’t turn off, because it lives in a table the agent doesn’t own. Postgres statement-level triggers can see every row a statement changed through a transition table, and they run once per statement.³ So count the rows, and refuse if an agent’s single statement changed too many:

Code A database command in SQL. The text around it says what it does.

CREATE ROLE agent LOGIN;
GRANT SELECT, UPDATE ON public.orders TO agent;

CREATE FUNCTION public.cap_agent_writes() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE n bigint;
BEGIN
  IF current_user = 'agent' THEN
    SELECT count(*) INTO n FROM changed;
    IF n > 20 THEN
      RAISE EXCEPTION 'agent statement on % touched % rows (limit 20)', TG_TABLE_NAME, n
        USING HINT = 'Name the rows in a WHERE clause, or ask a person.';
    END IF;
  END IF;
  RETURN NULL;
END $$;

CREATE TRIGGER orders_cap_agent_update AFTER UPDATE ON public.orders
  REFERENCING NEW TABLE AS changed FOR EACH STATEMENT
  EXECUTE FUNCTION public.cap_agent_writes();

Connected as agent, on Postgres 17.11:

Code What the screen or file shows, copied as is.

UPDATE public.orders SET status = 'shipped';
ERROR:  agent statement on orders touched 500 rows (limit 20)
HINT:  Name the rows in a WHERE clause, or ask a person.

UPDATE public.orders SET status = 'shipped' WHERE id > 0;
ERROR:  agent statement on orders touched 500 rows (limit 20)

UPDATE public.orders SET status = 'shipped' WHERE id = 200;
UPDATE 1

UPDATE public.orders SET status = 'shipped' WHERE id BETWEEN 200 AND 215;
UPDATE 16

ALTER TABLE public.orders DISABLE TRIGGER orders_cap_agent_update;
ERROR:  must be owner of table orders

The exception rolls the whole statement back, so the 500-row update never happened. A bounded update of 16 rows went through. And the agent can’t disable the trigger, because only the table’s owner can.³

Unlike a WHERE check, this catches WHERE id > 0, a condition that is present, looks reasonable and matches everything. It judges what the statement did, not how it was written. Add a matching AFTER DELETE trigger with REFERENCING OLD TABLE for deletes, and pick the limit per table: 20 is right for orders a support agent ships one at a time, wrong for a nightly job. Transition tables need an AFTER trigger on a plain table, and an UPDATE trigger that uses them can’t name a column list.³

A rule before the statement runs

A trigger refuses after the work is done and rolled back. A rule that reads the SQL first never sends it. This is the postgres-check starter policy in Deedglass v0.9.0, evaluated offline on the same statements:

Code What the screen or file shows, copied as is.

$ deedglass policy evaluate --policy examples/policies/postgres-check.yaml \
    --tool postgres.execute --sql "UPDATE public.orders SET status = 'shipped'"
✕ deny · block-unbounded-writes · 829 µs
  Blocked by policy "block-unbounded-writes": UPDATE and DELETE need a WHERE clause that names the rows to change.

… --sql "UPDATE public.orders SET status = 'shipped' WHERE 1=1"
✕ deny · block-unbounded-writes · 61 µs

… --sql "WITH x AS (UPDATE public.orders SET status='shipped' RETURNING 1) SELECT count(*) FROM x"
✕ deny · block-unbounded-writes · 83 µs

… --sql "UPDATE public.orders SET status = 'shipped' WHERE id = 42"
✓ allow · writes-checked-at-proven · verify L2 · 68 µs

… --sql "UPDATE public.orders SET status = 'shipped' WHERE id > 0"
✓ allow · writes-checked-at-proven · verify L2 · 67 µs

The starter lets reads and writes run, but not an UPDATE or DELETE without a WHERE, and checks every write it allows against what Postgres committed.⁹ It also refuses trivially true conditions and an unbounded write hidden inside a WITH; it allows WHERE id > 0.⁹ That last line is why the trigger above is worth having too.

When the agent works through a shell instead of a tool, the same idea runs as a before-a-command hook: deedglass guard shell asks first when psql or mysql would run an UPDATE or DELETE with no WHERE, or one whose WHERE is always true.¹⁰ Other frameworks are adding the same check; Prisma’s newer stack, for one, describes middleware that can error on a DELETE or UPDATE without a WHERE.¹¹

Five guards, one table

The MySQL column follows the manual’s key-column rule; the rest are the runs above. No single row has every “refused”. That’s the argument for two layers: one that reads the statement, one that counts what it did.

If it already committed

First, make the next mistake cheap. In psql, autocommit is on by default, so each statement commits when it succeeds; with AUTOCOMMIT off, nothing commits until you say COMMIT.⁸ For an agent, the equivalent is a tool that wraps writes in a transaction and reads the row count before committing. Our rolled-back run above is that habit by hand.

If it’s done, the order of work matters more than the method:

1. Stop the agent and take its write access away, so it doesn’t “fix” the table with a second guess.
2. Find what changed. If you have before-images (a change log, logical decoding, an audit table), you know the exact 340 rows. If you don’t, the status counts from a backup tell you how many, not which.
3. Restore beside production, not over it. Point-in-time recovery to a side database, then copy back only the affected column of the affected rows.

The first hour after an agent deletes data walks through each step, and undo for agent sessions covers restoring from before-images row by row.

What none of these catch

• A wrong but narrow WHERE. WHERE customer_id = 42 instead of WHERE id = 42 passes every guard above if it changes few enough rows. Only comparing what committed with what the agent meant catches that.
• Writes the statement didn’t name. Triggers and cascades can change other rows or tables after a perfectly bounded statement; that’s the story in the agent that said 1 row while Postgres changed 301.
• A role that owns the table. If the agent connects as the owner or a superuser, it can drop the trigger. Our guide to Postgres write access for Claude Code and Cursor sets up a role that doesn’t.

Try the failure without a database of your own: the missing-WHERE task on /challenge puts a scripted agent’s report next to what the database committed. For your own setup, npx deedglass scan grades your agent’s setup locally, and /score explains the grade; the playground runs the same kind of write against a real Postgres in your browser.

Questions people ask

Does Postgres have sql_safe_updates like MySQL?

No. Postgres has no built-in setting that refuses an UPDATE without WHERE. The pg-safeupdate extension adds one, which any session can turn off with SET safeupdate.enabled=0.⁴

Why did UPDATE report more rows than actually changed?

Postgres counts every row the statement matched, including rows whose values didn’t change.² An UPDATE that sets status = 'shipped' on a table where 160 rows are already shipped reports all of them.

Does WHERE 1=1 get past safe-update checks?

It gets past pg-safeupdate, whose README uses it as the deliberate way to update every row.⁴ A rule that reads the SQL can refuse it, and a row-limit trigger refuses it because it counts rows, not clauses.

What is the safest way to give an agent UPDATE access?

A role that owns no tables, UPDATE only on the tables and columns it needs, a row-limit trigger on each, and a rule that refuses unbounded writes before they’re sent. Then check what committed.

Written with AI assistance and checked against our claims register.