How to Fix PostgreSQL 'cannot execute CREATE TABLE in a read-only transaction' Error

The Incident

ERROR: cannot execute CREATE TABLE in a read-only transaction
CONTEXT: SQL function "migrate_schema" during inlining

Postgres enforces a hard wall: no DDL, no DML writes, no sequence increments are permitted inside a read-only transaction. The moment the executor hits CREATE TABLE, it throws and rolls back the entire transaction block. If this fires mid-migration (Flyway, Liquibase, Alembic, Django), your schema is left in a partial state — the migration tool may mark the version as failed, requiring manual repair of the migration history table before the next deploy can proceed.

The Attack Vector / Blast Radius

This is not a security exploit in the traditional sense — but the blast radius in a production pipeline is severe:

1. Replica misrouting (most common prod incident): A connection pooler (PgBouncer, RDS Proxy, HAProxy) routes the migration runner to a read replica instead of the primary. Every DDL fails. The migration tool may retry in a loop, hammering the replica and spiking its replication lag.

default_transaction_read_only = on at the role or database level: A DBA hardened a reporting role or an entire database cluster with ALTER ROLE reporter SET default_transaction_read_only = on. If your migration runner inherits this role or connects to that DB, every transaction opens read-only silently. No warning. Just the error at DDL time.
Explicit SET TRANSACTION READ ONLY in a wrapper: A shared DB utility function or ORM hook sets SET TRANSACTION READ ONLY for "safe" read operations and the DDL accidentally executes inside that context.
RDS/Aurora Multi-AZ failover window: During failover, the old primary becomes a replica. Connections that survive the failover are now on a read-only node. Migrations queued during this window fail with exactly this error.

Cascading risk: Liquibase/Flyway lock the DATABASECHANGELOGLOCK table at migration start. If the transaction fails mid-flight, the lock row may remain set to TRUE, blocking all future migration runs until manually cleared.
How to Fix It
Basic Fix — Verify and Override Transaction Mode

• -- Implicit read-only context inherited from role or pooler
• BEGIN;
• CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);

• COMMIT;

• -- Explicitly assert READ WRITE before DDL

• BEGIN;

• SET LOCAL default_transaction_read_only = off; -- override session GUC for this txn

• SET TRANSACTION READ WRITE; -- belt-and-suspenders

• CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);

• COMMIT;

  Note: SET LOCAL only survives the current transaction block. This is intentional — do not use SET (session-level) in a shared connection pool.

Verify You Are on the Primary

• -- Blindly running migration against pool VIP

• psql -h db-pool.internal -U migrator -d appdb -f migrate.sql

• -- Confirm primary before running DDL

• psql -h db-pool.internal -U migrator -d appdb \

• -c "SELECT CASE WHEN pg_is_in_recovery() THEN 'REPLICA - ABORT' ELSE 'PRIMARY - OK' END AS node_role;"

• Only proceed if output is 'PRIMARY - OK'

• psql -h db-pool.internal -U migrator -d appdb -f migrate.sql

Enterprise Best Practice — Role Hygiene + Connection Routing

• -- Migration runner uses shared 'app_user' role with read-only default

• ALTER ROLE app_user SET default_transaction_read_only = on;

• -- Dedicated migration role, never read-only, explicitly scoped

• CREATE ROLE migrator NOINHERIT LOGIN PASSWORD '...';

• GRANT CREATE, CONNECT ON DATABASE appdb TO migrator;

• -- DO NOT set default_transaction_read_only on this role

• -- In PgBouncer: route migrator to primary_dsn ONLY

• ; pgbouncer.ini

• [databases]

• appdb_migrate = host=primary.db.internal port=5432 dbname=appdb auth_user=migrator

• appdb = host=pool.db.internal port=5432 dbname=appdb

In RDS/Aurora, always resolve the cluster writer endpoint (cluster.cluster-xxxx.rds.amazonaws.com) — never the reader endpoint — for migration runners. Enforce this in your Terraform:

• db_host = aws_rds_cluster.main.reader_endpoint
• db_host = aws_rds_cluster.main.endpoint # writer/primary endpoint

Prevention in CI/CD

1. Pre-migration node role check (shell gate):

!/bin/bash

ROLE=$(psql "$DATABASE_URL" -tAc "SELECT pg_is_in_recovery();")
if [ "$ROLE" = "t" ]; then
echo "FATAL: Connected to a replica. Aborting migration."
exit 1
fi

Drop this as a pre-step in your GitHub Actions / GitLab CI job before Flyway or Alembic runs.
Become a Medium member

1. Checkov / kics policy for Terraform RDS:

Ensure your Terraform never wires a migration runner to a reader endpoint. Write a custom Checkov check or use OPA:

OPA: deny migration jobs targeting reader endpoints

deny[msg] {
input.resource.type == "kubernetes_deployment"
input.resource.spec.template.spec.containers[_].env[e]
e.name == "DATABASE_URL"
contains(e.value, "reader")
msg := "Migration workload must not target a reader/replica endpoint."
}

1. Liquibase / Flyway — fail fast on read-only:

Flyway 9+: set flyway.connectRetries=0 and flyway.validateOnMigrate=true. Do not let it retry silently into a replica loop.

1. Audit role defaults in your IaC:

Detect any role with read-only default in your DB

psql -c "SELECT rolname, rolconfig FROM pg_roles WHERE 'default_transaction_read_only=on' = ANY(rolconfig);"

Run this as a nightly CI job and alert if your migration role appears in the output.