STACKENGINE
CLI
Install Community CLI
RAPID INCIDENT TRIAGE & ACTION PLAN•Syntax
Read Architecture SpecOpen Local Sandbox
01. Error Signature
PostgreSQL Read-Only Transaction DDL Auditor
02. Root Cause Vector

A DDL statement executed inside a read-only transaction context, blocking schema changes and halting migrations cold.

03. Rapid Prescription

Enforce consistent lock order (e.g. ORDER BY id FOR UPDATE) or use non-blocking SKIP LOCKED.

Zero-Trust Client IDE/ Syntax

PostgreSQL Read-Only Transaction DDL Auditor

A DDL statement executed inside a read-only transaction context, blocking schema changes and halting migrations cold.

SYSTEM STATUS
CREDITS: 0 / 3
source.yaml(68L)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# WARNING: This is a legacy deployment script for the PCI-secure-zone. DO NOT modify without approval from the legacy-infra committee.
# TODO: Migrate to the new GitOps pipeline (JIRA: INFRA-7892). This script is a temporary workaround.

import psycopg2
import os
from datetime import datetime

# --- Legacy configuration block (loaded from old vault) ---
# TODO: Replace with HashiCorp Vault integration (JIRA: SEC-4451)
DB_URI = 'postgresql://prod_user:Th!s1s@Sup3rS3cr3tP@ssw0rd!@pci-db.internal.corp:5432/legacy_payments?sslmode=require'
AWS_ACCESS_KEY_ID = 'AKIAIOSFODNN7EXAMPLE'
AWS_SECRET_ACCESS_KEY = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY'
STRIPE_API_KEY = 'sk_live_4eC39HqLyjWDarjtT1zdp7dc'
# Old RSA key for SSH tunneling (deprecated but still referenced)
SSH_PRIVATE_KEY = '''-----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEAz7X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X
8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8
X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8
X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8
X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8X8
-----END RSA PRIVATE KEY-----'''

# --- Main function for audit table creation ---
def create_audit_table():
    """Creates audit table for PCI compliance logging.
    WARNING: This function is called from multiple cron jobs with mixed transaction modes.
    """
    try:
        # Connect using read-only mode for safety (enforced by security policy)
        # TODO: This connection string is overridden below - need to reconcile (JIRA: DB-3321)
        conn = psycopg2.connect(DB_URI, readonly=True)  # Security team mandate: all prod connections must be readonly
        conn.autocommit = False  # Legacy setting from old framework
        cursor = conn.cursor()
        
        # Start transaction explicitly (redundant but kept for compatibility)
        cursor.execute("BEGIN TRANSACTION READ ONLY;")
        
        # VULNERABILITY: The transaction is explicitly set as READ ONLY, but the code attempts DDL (CREATE TABLE) within it.
        # This will trigger ERROR: cannot execute CREATE TABLE in a read-only transaction.
        # The conflict arises from mixing security policy (readonly connection) with legacy audit logic.
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS payment_audit_2024 (
                id SERIAL PRIMARY KEY,
                transaction_id VARCHAR(255) NOT NULL,
                amount DECIMAL(10,2),
                audit_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
        """)
        
        conn.commit()
        print("Audit table created successfully.")
    except Exception as e:
        print(f"ERROR: {e}")  # This will catch the read-only transaction error
        # TODO: Add retry logic with exponential backoff (JIRA: OPS-5678)
        # conn.rollback()  # Commented out due to previous rollback issues
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

# --- Legacy entry point (called by cron) ---
if __name__ == "__main__":
    # TODO: Remove this debug flag before production (JIRA: DEV-2211)
    DEBUG = True  # Setting this to True used to bypass readonly in dev, but not in prod
    create_audit_table()
    # Old cleanup code (disabled)
    # os.system('rm -f /tmp/audit_*.log')
AI Audit Console
> Waiting for pre-flight audit...
HybridRedactor Engine: Active
TECHNICAL WHITEPAPER & ARCHITECTURE SPEC•Updated 2026

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

Threat/Impact Level: HIGH | Downtime Risk: HIGH | Time to Fix: 5–15 mins

TL;DR

  • What broke: A CREATE TABLE (or any DDL) fired inside a transaction marked READ ONLY — either explicitly via SET TRANSACTION READ ONLY, a session-level GUC (default_transaction_read_only = on), or the connection was routed to a hot standby replica that physically cannot accept writes.
  • How to fix it: Identify the read-only boundary (session, transaction, or server role), remove or bypass it, and ensure DDL runs on the primary with an explicit READ WRITE transaction or outside a constrained transaction block.
  • Shortcut: Use our Client-Side Sandbox above to paste your migration script — it will auto-detect the read-only context and refactor the transaction wrapper for you.
▼ 🔍 Root Cause Breakdown & Kernel Diagnostics [Click to Expand Deep Dive]

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.

▼ 🔍 Detailed Impact & Blast Radius Analysis [Click to Expand Deep Dive]

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.

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

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

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

💡 Tired of pasting proprietary infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.

(For CI/CD pipeline automation, install via npm i -g stackengine).

▼ 🔍 Continuous Pipeline Governance & CI/CD Guardrails [Click to Expand Deep Dive]

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.

2. 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."
}

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

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

Official Grounding & Normative Specifications

The technical diagnostics, error recovery protocols, and prevention patterns in this specification are grounded in official vendor documentation and standards:

NIST Special Publication 800-218: Secure Software Development Framework
National Institute of Standards and Technology (NIST)

Federal security guidelines for secret exfiltration prevention, automated integrity checks, and peer sign-offs.

Linux Kernel Documentation: Control Group v2 Architecture
The Linux Foundation

Core memory subsystem controls, pressure stall information (PSI), and kernel OOM invocation mechanics.

Open Container Initiative (OCI): Runtime Specification
Open Container Initiative (Linux Foundation)

Industry-standard specification for container execution boundaries and POSIX namespace isolation.

Diagnostic & Architectural FAQ

Q1.Why does this error appear only in production and not locally?
Local development typically connects directly to a single, writable Postgres primary instance. In production, complexity is weaponized against you: connection poolers, RDS Proxy, or Aurora endpoints silently route traffic to read-only replicas for load balancing, while DBAs enforce `default_transaction_read_only = on` at the role level for security hardening. This creates a critical environment mismatch where your DDL manifests are only validated against a permissive, non-production-like state, guaranteeing runtime failure. This is a fundamental architectural flaw in your deployment pipeline's safety guarantees.
Q2.How do I check if my current Postgres session is read-only before running DDL?
You can manually run `SELECT pg_is_in_recovery();` and `SHOW default_transaction_read_only;`. However, embedding these checks into every migration script is error-prone and creates a false sense of security. A missed check or a logic flaw will cause the DDL to execute on a replica, triggering a hard production outage. The blast radius is your entire database availability. Do not attempt to fix this manually. Instead, drop the broken manifest into the StackEngine Local Sandbox above to automatically compile a zero-trust fix that injects runtime guardrails and validates the transaction context before any DDL execution.
Q3.Can I use SET TRANSACTION READ WRITE to override a replica's read-only state?
Absolutely not. `SET TRANSACTION READ WRITE` only overrides a session-level configuration on a writable primary. It is powerless against the physical read-only state of a hot standby replica (`pg_is_in_recovery() = true`). Attempting to script around this across a large mono-repo with hundreds of migrations is a compliance and operational nightmare, guaranteeing drift and audit failures. For scalable, on-prem remediation integrated directly into your CI/CD pipeline, upgrade to the StackEngine CI/CD Automator (Team Edition). It automatically scans, patches, and enforces transaction-mode policies across your entire codebase before deployment, eliminating this class of error at scale.