The Practical Developer

Rotating Postgres Credentials Without Dropping a Single Connection

July 7, 2026 by The Practical Developer

Credential rotation is a compliance requirement (PCI DSS, SOC 2, HIPAA) that most teams treat as a downtime event. Here is how to rotate postgres passwords in a Node.js application without restarting a single process or dropping a single connection.

node.js

database

postgres

security

reliability

Code on a screen with a security lock icon overlay, representing credential security in software

Your compliance officer sends you a Slack message: “All database passwords must be rotated by end of quarter. No exceptions.”

If your reaction is “I will deploy a new environment variable and restart the service,” you are about to cause a production incident. Restarts drop active connections. Dropped connections kill in-flight queries. In-flight queries during peak traffic mean errors, retries, and frustrated users.

Credential rotation should be invisible to callers. You should be able to swap the database password, SSL client certificate, or even the entire connection string while the application is handling requests, and no query should fail because of it.

This post shows you how to build a credential rotator for node-postgres that hot-swaps connection pools without dropping a single in-flight query.

Why rotation hurts

Before we talk about the fix, let’s be honest about why most teams treat rotation as a downtime event.

The restart approach

The simplest approach is to change the env var and restart:

export PGPASSWORD="new-secret"
pm2 restart all

This works, but every active query gets terminated. If you have any write operations in progress, they fail. If you have long-running reports or batch jobs, they fail. If you are behind a load balancer, the health check flaps and traffic gets rerouted unevenly.

A rolling restart helps but does not eliminate the problem. During the restart window, some percentage of your traffic hits a crashed connection. For a service doing 10,000 requests per second, even a 2% error rate is 200 failures.

The connection pool problem

pg.Pool in Node.js establishes connections at startup using the credentials you pass to the constructor. Those connections live for the lifetime of the pool. If you change the password in the environment, the pool does not care. All currently open connections continue to work for a little while (because Postgres authenticates at connect time, not at query time). But when connections eventually drop and the pool tries to reconnect, it fails. The pool enters a death spiral: connections age out, reconnection attempts fail, waitingCount climbs, and your API goes down.

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
// This pool is locked to whatever credentials were in DATABASE_URL at construction time.
// If DATABASE_URL changes, the pool does not care.

The strategy: pool swapping

The solution is to never mutate an existing pool. Instead, you build a wrapper that manages two pools: the active pool serving traffic and a replacement pool that is being prepared. When the replacement is ready, you swap the active reference, drain the old pool, and let its in-flight queries finish naturally.

This is the same pattern used by database proxies like PgBouncer and PgCat under the hood. You can implement it in about 100 lines of application code.

Building the CredentialRotator

Let’s start with the credential source. In production, you might read from Vault, AWS Secrets Manager, or a mounted Kubernetes secret. For this example, I will use a file watcher that picks up credential changes from a JSON file. The same pattern works for any external source.

import { readFileSync, watch } from 'fs';
import { Pool, PoolConfig } from 'pg';

interface DatabaseCredentials {
  host: string;
  port: number;
  database: string;
  user: string;
  password: string;
  ssl?: { ca?: string; cert?: string; key?: string };
}

interface ManagedPool {
  pool: Pool;
  id: number;
  activeQueries: number;
  isDraining: boolean;
}

The ManagedPool wraps a pg.Pool with metadata: a unique id so we can log which pool is serving, a counter of active queries, and a drain flag so we never hand new queries to a pool that is being retired.

The rotator class

class CredentialRotator {
  private pools: ManagedPool[] = [];
  private currentPoolIndex = 0;
  private credentialPath: string;
  private nextId = 0;
  private drainTimeoutMs: number;

  constructor(credentialPath: string, drainTimeoutMs = 30_000) {
    this.credentialPath = credentialPath;
    this.drainTimeoutMs = drainTimeoutMs;
  }

  async start(): Promise<void> {
    const creds = this.loadCredentials();
    this.pools.push(await this.createPool(creds));
    this.watchCredentials();
  }

  private loadCredentials(): DatabaseCredentials {
    const raw = readFileSync(this.credentialPath, 'utf-8');
    return JSON.parse(raw);
  }

  private async createPool(creds: DatabaseCredentials): Promise<ManagedPool> {
    const config: PoolConfig = {
      host: creds.host,
      port: creds.port,
      database: creds.database,
      user: creds.user,
      password: creds.password,
      max: 20,
      idleTimeoutMillis: 30_000,
      connectionTimeoutMillis: 5_000,
    };

    if (creds.ssl) {
      config.ssl = creds.ssl;
    }

    const pool = new Pool(config);

    // Prove the pool works before we start using it
    const client = await pool.connect();
    client.release();

    const managed: ManagedPool = {
      pool,
      id: this.nextId++,
      activeQueries: 0,
      isDraining: false,
    };

    console.log(`[CredentialRotator] Pool ${managed.id} created and verified`);
    return managed;
  }

  // Called by any query function to get the active pool
  getPool(): ManagedPool {
    return this.pools[this.currentPoolIndex];
  }

  async query(text: string, params?: any[]) {
    const mp = this.getPool();
    mp.activeQueries++;
    try {
      const result = await mp.pool.query(text, params);
      return result;
    } finally {
      mp.activeQueries--;
    }
  }

The query method is the core of the design. Every call increments the active query counter on the pool it uses. The counter lets us know when it is safe to drain the old pool. The finally block guarantees the counter is decremented even if the query throws.

The rotation logic

Now the part that swaps credentials without dropping connections:

  async rotate(): Promise<void> {
    const oldPool = this.getPool();
    const creds = this.loadCredentials();

    console.log(`[CredentialRotator] Rotating: creating pool ${this.nextId}`);

    const newPool = await this.createPool(creds);
    this.pools.push(newPool);

    // Atomically swap the active pool
    this.currentPoolIndex = this.pools.length - 1;
    oldPool.isDraining = true;

    console.log(`[CredentialRotator] Swap complete. Draining pool ${oldPool.id}`);

    // Wait for the old pool to drain, then close it
    await this.drainPool(oldPool);
  }

  private async drainPool(mp: ManagedPool): Promise<void> {
    const deadline = Date.now() + this.drainTimeoutMs;

    while (mp.activeQueries > 0) {
      if (Date.now() > deadline) {
        console.warn(
          `[CredentialRotator] Drain timeout for pool ${mp.id}. ` +
          `Force-closing with ${mp.activeQueries} active queries.`
        );
        break;
      }
      await sleep(100);
    }

    await mp.pool.end();
    console.log(`[CredentialRotator] Pool ${mp.id} closed`);

    // Remove from the pools array
    this.pools = this.pools.filter((p) => p.id !== mp.id);
  }
}

The rotate method does three things in order:

  1. Creates the new pool first. If the new pool cannot connect (bad password, expired certificate, network issue), the old pool is still serving traffic. The rotation fails safely instead of taking down the service.
  2. Atomically swaps the active reference. After this point, every new query() call goes to the new pool. The old pool stops receiving new work.
  3. Drains the old pool. It waits for in-flight queries to finish (up to a configurable timeout) and then calls pool.end() to close all connections gracefully.

This is the critical ordering. You never create a failure window where no pool is available.

Handling in-flight queries during drain

The drain loop polls every 100ms and checks if activeQueries has reached zero. In practice, most queries finish within milliseconds, so the drain completes almost instantly during normal traffic.

But what if a long-running query is still executing when you rotate? The drain loop waits up to drainTimeoutMs (default 30 seconds). If the query has not finished by then, the old pool is force-closed. The query fails, but only that one query fails, not every query on the service.

You can tune the drain timeout to match your slowest query. If you have reports that run for 60 seconds, set drainTimeoutMs to 90,000 or higher.

A note on transactions

The query method tracks a single query’s lifetime. If you use transactions, you need to track the transaction scope instead:

async transaction<T>(fn: (client: any) => Promise<T>): Promise<T> {
  const mp = this.getPool();
  mp.activeQueries++;
  const client = await mp.pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
    mp.activeQueries--;
  }
}

The same drain logic applies. While a transaction is in flight, the old pool stays alive. When the transaction finishes, the counter drops and the drain proceeds.

Configuring the credential watcher

Now wire up the file watcher that triggers rotation when the credentials change:

  private watchCredentials(): void {
    watch(this.credentialPath, (eventType) => {
      if (eventType === 'change') {
        console.log('[CredentialRotator] Credential file changed. Rotating...');
        this.rotate().catch((err) => {
          console.error('[CredentialRotator] Rotation failed:', err);
        });
      }
    });
  }
}

function sleep(ms: number): Promise<void> {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

In production, replace the file watcher with a subscription to your secret store:

  • AWS Secrets Manager: Poll GetSecretValue every 30 seconds and compare the version tag.
  • HashiCorp Vault: Use the Vault agent sidecar that writes to a shared volume, then watch that file.
  • Kubernetes Secrets: Use the STALE_META annotation with a controller that rewrites the mounted secret, then watch the symlink change.
  • Environment variables via systemd: Use ExecReload to signal the process to re-read env vars.

The pattern is the same regardless of the source. The rotate() method does not care where the credentials came from.

Using the rotator in your application

Here is the full wiring in an Express application:

import express from 'express';
import { CredentialRotator } from './credential-rotator';

const app = express();
const rotator = new CredentialRotator('/etc/secrets/database.json');

app.get('/users/:id', async (req, res) => {
  const result = await rotator.query(
    'SELECT * FROM users WHERE id = $1',
    [req.params.id]
  );
  res.json(result.rows[0]);
});

async function main() {
  await rotator.start();
  app.listen(3000);
}

main().catch(console.error);

That is it. The rest of your application code does not know or care that credentials can change at any moment.

Testing the rotation

You need to verify that rotation actually works without errors. Here is a test that creates a temporary credential file, starts the rotator, changes the password, and verifies that queries succeed on both sides of the rotation:

import { test } from 'node:test';
import assert from 'node:assert/strict';
import { writeFileSync, unlinkSync } from 'fs';
import { randomBytes } from 'crypto';
import { CredentialRotator } from './credential-rotator';

test('credential rotation without dropped connections', async (t) => {
  const credPath = '/tmp/test-creds.json';
  const password = randomBytes(16).toString('hex');

  writeFileSync(credPath, JSON.stringify({
    host: 'localhost',
    port: 5432,
    database: 'testdb',
    user: 'testuser',
    password,
  }));

  const rotator = new CredentialRotator(credPath);
  await rotator.start();

  // Verify the initial pool works
  const result1 = await rotator.query('SELECT 1 as value');
  assert.equal(result1.rows[0].value, 1);

  // Rotate to a new password
  const newPassword = randomBytes(16).toString('hex');
  writeFileSync(credPath, JSON.stringify({
    host: 'localhost',
    port: 5432,
    database: 'testdb',
    user: 'testuser',
    password: newPassword,
  }));

  // Give the watcher a moment to react
  await new Promise((r) => setTimeout(r, 500));

  // Verify queries still work with the new pool
  const result2 = await rotator.query('SELECT 1 as value');
  assert.equal(result2.rows[0].value, 1);

  unlinkSync(credPath);
});

What about connection string rotation?

If you are using a full connection string (postgres://user:pass@host:5432/db) instead of individual fields, the same pattern applies. Parse the connection string, extract the parts, and pass them to PoolConfig:

function parseConnectionString(cs: string): DatabaseCredentials {
  const url = new URL(cs);
  return {
    host: url.hostname,
    port: Number(url.port),
    database: url.pathname.slice(1),
    user: decodeURIComponent(url.username),
    password: decodeURIComponent(url.password),
  };
}

The rotator itself does not care whether the source is a connection string or individual fields.

The takeaway

Credential rotation does not require downtime. The pool-swap pattern lets you rotate database passwords, SSL certificates, or entire connection strings while the application is handling traffic, with zero dropped queries on the new pool and a graceful drain of the old one.

The three rules to remember:

  1. Create the new pool before retiring the old one. If the new pool fails to connect, the old pool stays active. No blast radius.
  2. Swap the reference atomically. After the swap, every new query goes to the new pool. The old pool finishes its in-flight work and is drained.
  3. Set a drain timeout. If a long-running query gets stuck, force-close the old pool after a reasonable deadline. One failed query is better than a pool that never drains.

Credential rotation is not a deployment event. It is a configuration change that your application should handle automatically, just like it handles a DNS record update or a configuration reload. Build the rotator once, wire it to your secret store, and sleep better knowing that the next compliance audit will not require a production outage.

A note from Yojji

Building production systems that survive configuration changes without dropping traffic is exactly the kind of reliability engineering that separates mature teams from teams that scramble every time a certificate expires. Yojji’s engineers build credential rotation, connection management, and graceful degradation into the Node.js and Postgres backends they ship for clients, treating operational resilience as a first-class feature rather than an afterthought. Yojji is an international custom software development company founded in 2016, with offices across Europe, the US, and the UK, specializing in the JavaScript ecosystem and cloud-native infrastructure.

Keep Reading