Read-only database access for agents

Privileges are the boundary, not a setting

There are two ways to make a Postgres connection “read-only”, and only one holds against an agent that writes its own SQL.

The first is a setting. default_transaction_read_only sets the default read-only status of each new transaction, and it is off by default.² It is a default, though, and a session can change its own defaults: in our test, a session of a role turned statement_timeout off with a plain SET, even though the setting came from ALTER ROLE … SET.⁷ A read-only transaction can also be ended, and the next one may not be read-only.

That isn’t hypothetical. The reference Postgres MCP server was archived on 28 May 2025.⁴ Datadog Security Labs showed that its read-only mode ran each query in a read-only transaction that a stacked COMMIT; ends, after which later statements run with the role’s full rights.³

The second is privileges. In Postgres, only an object’s owner or a superuser can do anything with it until privileges are granted, and the INSERT privilege is what allows an INSERT. In our test, a role with SELECT only was refused an INSERT with permission denied, including after its session ran SET default_transaction_read_only = off.¹ No statement the agent sends can grant it a privilege it doesn’t hold.

Setting it up

• One role per agent, so the database log and any audit can tell agents apart.
• Grant table by table. SELECT on the tables the job needs, USAGE on their schema and CONNECT on the database. Postgres grants no privileges on tables to PUBLIC by default,¹ so anything you don’t grant stays out of reach.
• Avoid the shortcuts. The predefined role pg_read_all_data reads every table, view and sequence, as if it had SELECT on all of them.⁵ That is everything, including tables the agent has no business seeing.
• Check the role’s attributes. A superuser ignores grants entirely, and BYPASSRLS skips row-level security.
• Cap the cost. statement_timeout aborts any statement that runs longer than its limit.² A connection limit stops one agent from taking every connection.

What read-only still allows

• Reading sensitive data. An agent that can read customer records and also send email or fetch URLs can leak them. Grant columns and rows, not whole tables, where it matters; see row-level security.
• Running functions. Postgres grants EXECUTE on new functions to PUBLIC by default,¹ and a SECURITY DEFINER function runs with the privileges of its owner.⁶ A read-only role that can call one that writes can write through it. Revoke EXECUTE from PUBLIC on those functions.
• Expensive queries. A full scan of a large table during peak hours is a read, and it can still hurt.

When the agent has to write, keep the read-only role for reads and add a second, narrow role for the writes. The guide to safe write access goes through it step by step.