Live deliveryBy Dynv team5 min read

Rotate a database password with zero downtime in Node

Short answer: never change the password of the role your app is using and then update the app; in between, every new connection fails. Use two login roles with identical privileges: set a new password on the idle one, point the app at it (hot-swapping the pool), then retire the old one. In our test the naive order failed 46 queries and the two-role order failed none.

Database passwords are the secret teams are most afraid to rotate, because the failure is immediate and visible. This is a runnable, tested pattern for PostgreSQL and Node using the pg driver and Dynv’s SDK to swap the connection pool live. The same shape works for MySQL and other databases that support several login users.

All results below are from a run on 2026-11-01 against PostgreSQL 17.11 and pg from npm, with a local Dynv server. (@dynv/sdk is in our repository and not yet published to npm; install it from a checkout, per the SDK docs.)

Why the naive order fails

The obvious rotation is: run ALTER ROLE app PASSWORD 'new', then update the app’s config. Between those two steps the app still holds the old password. Connections already open keep working, but any new connection (a pool growing, an idle connection recycled, a restart) fails authentication. How long the gap lasts and how many requests it hits depends on connection churn; with a busy, connection-heavy app it can be a visible outage.

The pattern: two roles, switch, retire

  1. Create two login roles with identical privileges, via a shared group role. Call them app_blue and app_green. The app uses one at a time.
  2. To rotate: set a new password on the idle role (the one the app isn’t using). Nothing is affected.
  3. Change the app’s DATABASE_URL to the idle role with the new password. The app builds a new pool, switches new queries to it, and finishes in-flight queries on the old pool.
  4. Once the old pool has drained, disable the old role (NOLOGIN) and end its sessions. Next rotation, the roles swap places.
CREATE ROLE app_rw NOLOGIN;                       -- privileges live here
CREATE ROLE app_blue  LOGIN PASSWORD 'blue-1'  IN ROLE app_rw;
CREATE ROLE app_green LOGIN PASSWORD 'green-1' IN ROLE app_rw;
-- GRANT ... ON ... TO app_rw;  (never grant to the login roles directly)

Note: current_user reports the login role, so you can see which one a connection used.

The app

DATABASE_URL is marked hot; the pool is rebuilt by resource() whenever it changes, and the old pool is ended after in-flight work drains:

// app.mjs
import { dynv } from "@dynv/sdk";
import pg from "pg";

const d = dynv();
await d.ready();

const db = d.resource(
  ["DATABASE_URL"],
  (env) => new pg.Pool({ connectionString: env.DATABASE_URL, max: 4, idleTimeoutMillis: 10 }),
  { dispose: (pool) => pool.end(), drainTimeoutMs: 10_000 },
);

let ok = 0, fail = 0, user = "?";
setInterval(async () => {
  try {
    const r = await db.use((pool) => pool.query("select current_user"));
    user = r.rows[0].current_user; ok++;
  } catch (e) {
    fail++;
    if (fail <= 3) console.log("query failed:", e.message);
  }
}, 50);
setInterval(() => console.log(`connected as ${user}  ok=${ok}  failed=${fail}`), 1000);

To make connection churn visible in a small test we set idleTimeoutMillis: 10, so the pool reconnects constantly. A real production pool reconnects less often, so the naive failure would be rarer and harder to reproduce, not safer. Set the variable hot and run it:

dynv admin set ledger DATABASE_URL="postgres://app_blue:[email protected]:5432/postgres"
dynv admin mode ledger hot DATABASE_URL
dynv run -- node app.mjs        # with DYNV_URL, DYNV_TOKEN, DYNV_PROCESS set

Result 1: the naive order

We changed the password on the role in use, waited two seconds, then updated Dynv:

$ psql -c "ALTER ROLE app_blue PASSWORD 'blue-2'"
$ sleep 2
$ dynv admin set ledger DATABASE_URL="postgres://app_blue:[email protected]:5432/postgres"

query failed: password authentication failed for user "app_blue"
connected as app_blue  ok=62  failed=17
connected as app_blue  ok=62  failed=37
connected as app_blue  ok=73  failed=46
connected as app_blue  ok=93  failed=46

46 failed queries in about two seconds. After the app received the new URL it recovered by itself, which is the best case: the pool swap fixed it, but it should never have been needed.

Result 2: two roles

Idle role gets a new password; the app is switched to it; the old role is then retired:

psql -c "ALTER ROLE app_green PASSWORD 'green-3'"
dynv admin set ledger DATABASE_URL="postgres://app_green:[email protected]:5432/postgres"
# once drained:
psql -c "ALTER ROLE app_blue NOLOGIN" \
     -c "select pg_terminate_backend(pid) from pg_stat_activity where usename='app_blue'"
connected as app_green  ok=99   failed=0
connected as app_green  ok=119  failed=0
connected as app_green  ok=139  failed=0
connected as app_green  ok=159  failed=0
connected as app_green  ok=179  failed=0

The process id never changed (no restart), the user changed from app_blue to app_green, and failed stayed at 0, including after the old role was disabled and its sessions terminated.

Practical notes

  • Several replicas. Every replica must receive the new URL before you retire the old role. Applying the change with dynv rotate (which waits and reports applied, pending, refused or offline per deployment) tells you when they all have; wait for that, and keep the old role enabled until then. Offline instances get the new value when they reconnect, so retire the old role only after they have.
  • Generate the new password with a tool, not by hand, and keep it out of shell history: dynv gen password:32 --stdout prints one; to set a value without it appearing in argv, use dynv admin set --stdin.
  • Rollover is coming soon. Dynv’s planned fallback-value feature (a signed old value with an expiry that the CLI exposes and retires itself) would let an app retry with the previous credential during a propagation window. Today, the two-role pattern gives you the same overlap at the database.
  • Poolers and proxies. If a pooler such as PgBouncer sits in front, it holds its own credentials; update those too, in the same order.
  • Which variables can be hot. The connection string is safe to rebuild a pool from. Variables that affect how the process starts (for example NODE_OPTIONS) cannot change live.

Limits

  • This is Postgres-specific in its SQL; the pattern is general. Managed databases may restrict ALTER ROLE or session termination; check your provider.
  • The test used one app instance and a local database. It shows the mechanism, not your production behavior; rehearse on staging.
  • Dynv rotates the value inside Dynv. It does not change the password in the database for you; you run the SQL, then deliver the result.

Facts last verified . Spotted an error? Tell us.

New here? Start here for the guides in a sensible order.