a month ago
I'm evaluating a safe production PostgreSQL migration procedure and need to understand what Railway officially supports for establishing a temporary maintenance boundary.
Specifically, is there a Railway-supported way to:
- Prevent all application/database write connections during maintenance.
- Handle both pooled and direct/unpooled connections.
- Retire existing sessions and prevent excluded clients from reconnecting.
- Prevent privileged database users from performing writes or DDL during the maintenance window.
- Keep the restriction effective across service, database, pooler, or deployment restarts.
- Preserve a separate, controlled connection for the authorized migration/recovery operator.
- Independently verify that the restriction is actually enforced.
- Obtain reliable audit evidence showing who established the restriction and when.
I'm particularly interested in whether Railway has a recommended combination of PostgreSQL roles/permissions, PgBouncer controls, networking controls, or Railway platform controls for this use case.
I'm not asking anyone to perform a production change. I'm only looking for the officially supported capabilities and recommended procedure, including any important limitations or administrator bypasses.
If complete exclusion of privileged actors isn't possible through Railway's supported controls, I'd also appreciate knowing that explicitly.
1 Reply
a month ago
This thread has been opened as a bounty so the community can help solve it.
Status changed to Open Railway • about 1 month ago
a month ago
AI-assisted documentation review; I have not tested your Railway deployment.
PostgreSQL/PgBouncer offer application maintenance controls, but I found no documented Railway guarantee covering all eight requirements. Anyone still able to change roles, pooler configuration or the deployment remains inside the trust boundary. This synthesizes documented capabilities, not a Railway-certified recipe. Railway PostgreSQL
- Gate first, drain second. Commit restrictions for every excluded login identity before terminating its sessions, then recheck. Preserve exact authorized sessions, not every connection sharing an operator username.
NOLOGINcontrols initial login; it does not revoke role membership, inherited privileges or permittedSET ROLE. A still-authorized administrator can undo the restriction. LOGIN, SET ROLE, ALTER ROLE - Confirm termination. With
pg_terminate_backend, zero/default timeout confirms signal delivery, not completed termination. A positive timeout and subsequent activity checks provide stronger evidence; only a superuser can terminate a superuser backend. Signaling functions - Treat pooler authentication separately. PgBouncer may use its own authentication file and a forced backend user. Existing pooled server connections can survive a client-role change. The default dynamic
auth_querychecksrolcanlogin, but verify whether your clients actually use it. Configuration - Pooler commands cover only that route.
DISABLErejects new clients;PAUSEdrains according to pooling mode;KILLdrops client/server connections immediately. Direct PostgreSQL connections bypass these commands. Do not assume runtime pause/disable state survives a pooler restart. Administration - Network and password changes need verification. Removing public TCP access leaves the private project route. In the official Postgres base image, initialization variables affect an empty data directory, not an existing database. Changing an environment password alone therefore does not prove database credential rotation; verify the deployed image's behavior and client/pooler secrets. image initialization
- HBA customization is distinct from a supported maintenance switch. PostgreSQL supports ordered HBA rules, and Railway documents custom images. Neither establishes a documented guarantee that your chosen policy survives every redeploy or upgrade. HBA
For your eight requirements, I would collect this evidence in staging:
- Exclude writers: Test fresh connections and attempted write/DDL from excluded identities; inventory application and job writers
- Pool and direct: Test both routes and the actual pooler backend identity independently
- Drain/reconnect: Excluded sessions disappear and reconnect fails after the gate commits
- Privileged actors: Test excluded privileged identities; trust anyone still able to reverse controls
- Restarts: Repeat negative tests after DB, pooler and application restart/redeploy
- Operator: Exact controlled migration session and recovery path remain usable; no shared credential
- Independent checks: Correlate client failures with roles, pooler state and fresh
pg_stat_activityobservations - Attribution: Store actor, timestamps, establishment statements and test results outside the tested administration boundary
Activity observations need appropriate visibility and fresh snapshots. PostgreSQL logging can include DDL and user/time/session context; ALTER SYSTEM writes postgresql.auto.conf with reload/restart requirements. These are evidence sources, not administrator-proof audit guarantees. Activity, logging, ALTER SYSTEM
A transaction read-only default is supplementary, not a login/session exclusion mechanism. None of these staging checks has been run by me. Check your deployed PostgreSQL and PgBouncer versions against the linked documentation. Transaction defaults