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

说明与用途

通过将 AI 代理的角色限制为仅 SELECT、强制执行 default_transaction_read_only 并设置语句超时，防止代理破坏 PostgreSQL 的只读锁的指南。

问题

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.

结果

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.

工作流

• 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.

证据

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.

来源

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