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

Description and use

Guide to protecting an AI agent from breaking read‑only locks in PostgreSQL by restricting its role to SELECT, enforcing default_transaction_read_only, and setting statement timeouts.

Problem

An AI agent needs database context but a read‑only wrapper can be broken by a single COMMIT statement, allowing write access to production data.

Outcome

By restricting the agent’s role to SELECT on a curated view, enforcing default_transaction_read_only, and applying a short statement timeout, the risk of a COMMIT breaking the read‑only lock is eliminated.

Workflow

• Create a read‑only role: CREATE ROLE agent_ro LOGIN;
• Set defaults: ALTER ROLE agent_ro SET default_transaction_read_only = on; ALTER ROLE agent_ro SET statement_timeout = '5s';
• Grant SELECT on a curated view: GRANT SELECT ON agent_views.orders_daily TO agent_ro;
• Grant USAGE on the schema: GRANT USAGE ON SCHEMA agent_views TO agent_ro;
• Use pg_dump --schema-only to provide the agent with database schema only;
• Rehearse migrations in a disposable snapshot using BEGIN … ROLLBACK;
• Run migrations in staging first, then production with human approval.

Evidence

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.

Source

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;

pg_dump --schema-only your_db > schema.sql