Let an agent write, in dry-run first

Client

an agent that needs to write

Policy shape

rollback writes with a hard row cap

The agent genuinely needs to change data, and nobody is comfortable letting the first version of its UPDATE reach a real table.

This is the recipe for the step after read-only, and it is where most teams stall, because the choice looks like all or nothing. It is not. Rollback mode runs the statement inside a transaction that is always rolled back, so the agent learns exactly how many rows its WHERE clause matches, against production data, without changing any of them. When you are ready, switch the mode and the same policy commits.

Wire it up

Two flags carry this recipe: the write mode, and the cap on how many rows a single statement may touch. The whereless-write guard is on for agent credentials regardless.

1. 1

   Create the policy in rollback mode

   read_write is the access mode, because the statement kind has to be permitted before the write mode can do anything with it. rollback is what makes every write a dry run.

   TerminalCopy

   pgbeam policies create \
     --name agent-writes-dry \
     --mode read_write \
     --write-mode rollback \
     --allow public.orders \
     --max-affected-rows 50 \
     --statement-timeout-ms 8000

2. 2

   Issue the credential

   Give this its own credential rather than promoting the read-only one, so the read-only agent stays read-only and the audit trail separates the two.

   TerminalCopy

   pgbeam agents create --name migration-agent --policy pol_xxx --expires 7d

3. 3

   Let the agent try its write

   The statement runs against real data and is rolled back. The tag the agent gets back carries the count, which is the number it needs to sanity-check its own WHERE clause.

   What the agent sendsCopy

   UPDATE public.orders SET status = 'refunded' WHERE created_at < '2026-01-01';

4. 4

   Promote when the count looks right

   Update the policy rather than issuing a new credential, so the agent keeps working and the change is one line in the audit trail. Keep the row cap on: it is doing a different job from the write mode.

   TerminalCopy

   pgbeam policies update pol_xxx --write-mode normal

Approval mode is the middle setting between these two. --approval-mode writes holds each write for a human decision, with --approval-auto-max-rows to let small ones through unattended.

Four writes, four different answers

Each of these meets a different rule, and only one of them is the write mode itself.

1  UPDATE public.orders SET status = 'refunded' WHERE id = 42
2  UPDATE public.orders SET status = 'refunded'
3  UPDATE public.orders SET status = 'refunded' WHERE created_at < '2026-06-01'
4  WITH doomed AS (SELECT id FROM public.orders LIMIT 10)
   DELETE FROM public.orders WHERE id IN (SELECT id FROM doomed)

1  runs, reports UPDATE 1, rolled back
2  Verdict: block    Rule: whereless_write
3  blocked: this write would affect 18,402 row(s), exceeding the hard cap of 50
4  Verdict: block    Rule: max_affected_rows_writable_cte

What this does not cover

• Rollback mode requires the simple query protocol. A client that sends writes as prepared statements is refused with a message saying so rather than being silently committed.
• A hard affected-row cap cannot bound a data-modifying CTE, so a write wrapped in WITH is refused rather than approximated. Rewrite it as a top-level INSERT, UPDATE, or DELETE.
• Dry-run tells you the row count, not the consequence. A statement that matches three rows can still be the wrong three, and no cap catches that. This buys you a check on blast radius, not a check on intent.

More recipes

Questions