Skip to main content

PG.L6.DEFAULT_TRANSACTION_READ_ONLY — the write fails before anything can reason about it

  • Category: safety
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

With default_transaction_read_only = on, any transaction that does not explicitly declare READ WRITE refuses writes outright. The failure — read_only_sql_transaction, SQLSTATE 25006 — reads like a privilege problem while being nothing of the kind, which is why naming the code matters: it is what lets somebody tell the two apart instead of granting privileges that were never missing.

Why an audit reports it rather than noting it​

Because of what it does to the rest of the run. An audit reasons about what a write would cost — which lock it takes, whether it rewrites the table, whether it can run online. On an instance that refuses the write before any of that happens, those statements stay true about the SQL and stop being true about this server.

The finding is therefore as much about the report's own validity as about the setting.

The expected exception​

A dedicated read replica, or an instance deliberately held read-only during a migration window, is exactly this setting used correctly. It belongs on the ignore list with the reason written down:

'ignore' => [
    ['rule' => 'PG.L6.DEFAULT_TRANSACTION_READ_ONLY', 'reason' => 'reporting replica; writes are not expected here'],
],

Only the server's value is judged. A read connection that sets the flag for its own session is doing precisely what it should, and is never reported as a server finding.

Flagged​

ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

Preferred​

ALTER SYSTEM SET default_transaction_read_only = off;
SELECT pg_reload_conf();

If this instance really is a replica, the audit has more to say about it than this one finding — PG.L6.* results describe a server whose role is worth stating in the report header, and a replica is a different subject from the primary it follows.

Sources​