CockroachDB with mTLS and role-separated access

Published

May 1, 2026

Context

The stack needs a relational database reachable by the Elixir gateway and the Phoenix zone backend. It must support schema migrations (DDL) separately from application queries (DML) to limit blast radius if application credentials are compromised.

Decision

Run a single-node CockroachDB on Fly.io with mTLS for all connections and three application roles:

  • gateway_admin — DDL access for Ecto migrations
  • gateway_writer — DML access for the running application
  • gateway_reader — read-only access

Certs are generated by gen_crdb_certs.sh with 100-year validity and stored as Fly secrets. The node cert must include both serverAuth and clientAuth in extendedKeyUsage because CockroachDB uses the node cert for internal gRPC client authentication.

Consequences

  • --advertise-addr must be localhost. A flycast address routes the internal gRPC loopback through Fly’s NAT, breaking the admin UI.
  • prepare: :unnamed is required in Postgrex to avoid statement-cache OOM on single-node deployments.
  • Port 26257 is never publicly exposed. Access is via Fly’s private network (6PN) using socket_options: [:inet6] in Ecto, because .internal DNS returns only AAAA records.
  • The root cert is provisioned on the CRDB machine only. gateway_admin is the highest-privilege cert available to the application.