DEV Community

Honey Kiran
Honey Kiran

Posted on Originally published at honeykiran.substack.com

OpsBites: Stop. Your Agent's Read-Only Lock Can Break With One Word.

#postgres #database #ai #devops

Your AI agent wants database context. The fast answer is the master key.

One "read-only" wrapper let a stray COMMIT (the word that ends a transaction) unlock writes. A promise in a tool is a request. A rule on the role is a wall.

Here's the ladder.

A padlock labelled READ-ONLY, cracked open by the word COMMIT;

Context pipeline: production database to pg_dump --schema-only to schema.sql to your agent; the master-key route is blocked by the gate agent_ro: SELECT on 1 view.

Your agent needs the shape of your database: tables, columns, links. That's a map. A login that can write is the master key. OWASP calls that Excessive Agency: more power than the job needs.

The ladder: schema-only dump, masked snapshot, read-only replica - and production write access, marked never.

1 · Schema-only dump. The blueprint, zero rows · Can break: Nothing

2 · Masked snapshot (a throwaway copy, fake names). Real shapes, fake values · Can break: Nothing real

3 · Read-only replica (a live copy) + read-only role. Live-shaped data, SELECT only · Can break: It reads. It can't write

4 · Production, write access. Everything · Can break: Everything. Never this rung

Rung 1 is one line. It copies the blueprint (tables, indexes, rules) and leaves every row behind. For most of your agent's coding help, this is the whole answer.

pg_dump --schema-only your_db > schema.sql
Enter fullscreen mode Exit fullscreen mode

Three fences on the role: read-only default, 5-second timeout, one view only. Defaults are fences; the GRANT is the wall.

CREATE ROLE agent_ro LOGIN;
ALTER ROLE agent_ro SET default_transaction_read_only = on;
ALTER ROLE agent_ro SET statement_timeout = '5s';
GRANT SELECT ON agent_views.orders_daily TO agent_ro;
Enter fullscreen mode Exit fullscreen mode

A role is the login your agent's MCP server (the small tool-server it talks to) uses. The GRANT is the wall: read access to one view (a curated window), nothing else. Every session also starts read-only, and queries past 5 seconds die, but a session can change both defaults: fences, not the wall. The timeout default is off, so one cheerful five-table join can camp on your replica all afternoon. Test in staging (the test copy of production) first.

Also needed: GRANT USAGE ON SCHEMA agent_views TO agent_ro; or your agent can't even see the view.

The COMMIT story: a wrapper's read-only promise breaks with one word; a SELECT-only grant does not.

The reference MCP Postgres server (now archived) wrapped your agent's queries in a read-only transaction. Datadog Security Labs showed that one stray COMMIT; ended it, and everything after ran read-write. A wrapper's promise is a request.

Let your agent write the migration (the script that changes table structure). You run it. Rehearse it on the snapshot first:

BEGIN;
-- the agent-written migration goes here
-- read what it actually did
ROLLBACK;
Enter fullscreen mode Exit fullscreen mode

A few commands, like DROP DATABASE, can't run inside a transaction. Then a human runs it for real: staging first, then production. Actors rehearse too.

Save this: the Map, Not Keys ladder card with the four rungs and the six-item pre-flight checklist.

Before you paste that connection string

  • Schema-only first: did pg_dump --schema-only already answer your agent's need?
  • A role that can only SELECT: no INSERT, UPDATE, DELETE or structure changes in the agent's login.
  • Read-only on the role, not just the tool: default_transaction_read_only via ALTER ROLE.
  • A statement timeout: short, on that role, so a bad join dies young.
  • Only allowlist views granted: curated views, never raw tables.
  • Migrations rehearsed, human-committed: BEGIN … ROLLBACK on a disposable copy; a person presses the real button.

Give context generously. Give access stingily. Now finish that chai.

© 2026 Honey Kiran. All rights reserved.

Honey Kiran · DevOps & Platform Engineering

I write about SRE, Kubernetes, Terraform, and platform engineering — the defaults that bite, the incidents that teach, and the guardrails that hold.

Which rung is your agent on today, and does its "read-only" come from the database or just the tool? Tell me in the comments.

Examples are illustrative — validate in your own environment before running in production.

Disclaimer

  • This post describes my personal experience with my own systems and accounts, in my own words. It is a first-person account of what I observed and what I do in my own work — not a statement of any company’s intentions, policies, or internal decisions. Where I do not know something, the post says so.
  • I am not affiliated with, endorsed by, or speaking for any company, product, or vendor named in this post. All product and company names are trademarks of their respective owners and are mentioned only to describe the experience accurately.
  • Any error messages, documentation excerpts, or figures quoted here are reproduced from my own records or from the public sources linked in the post, believed accurate at the time of writing. Product behavior changes over time; verify against current official documentation before relying on any detail.
  • Nothing in this post is professional, financial, legal, or security advice for your specific situation. Examples are illustrative — validate in your own environment before running anything in production.
  • Corrections are welcome. If any company named here publishes an official statement, or if any detail is wrong, I will update this post and note the correction. The goal is an accurate public record, not a verdict.

Top comments (0)