← All runbooks in Secrets, PKI & Certificates

high riskservice affecting~75 min

Runbook: Rotate a Database Password Without an Outage

1 · Prerequisites

Confirm every item is in place before any state change.

A superuser or role-administration connection to the database cluster, separate from the credential being rotated
Two login roles that hold identical privileges through membership of a shared group role, or authority to create the second one
The ability to read pg_stat_activity on the primary and on every replica the application connects to
Knowledge of the connection pool in use, its maximum connection lifetime, and how to make it turn connections over
Write access to wherever the application reads its database credentials, and the ability to roll each consumer
An inventory of every consumer, including migration jobs, reporting tools, backup agents and anything a person runs by hand
Agreement on who may stop the rotation, since distribution happens consumer by consumer

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

· Name the exact login role, and confirm it is not also the object owner. If the role the application logs in as also owns the tables, retiring it later becomes a data migration rather than a credential change. The durable arrangement is a group role that owns the objects and two login roles that are members of it.
· Confirm privilege parity between the two login roles. SELECT rolname, rolcanlogin, rolvaliduntil FROM pg_roles WHERE rolname LIKE 'app_rw%'; plus a membership check. Parity achieved by copying grants drifts within months; parity achieved by group membership does not.
· Find every consumer, not just the application. Migration jobs, nightly reports, the backup agent, the schema tool somebody runs from a laptop, and the read-only dashboard all authenticate. Each is a consumer even when nobody thinks of it as one.
· Read the pool configuration and write down the maximum connection lifetime. That number is how long a stale credential can stay invisible. A pool that never recycles a healthy connection will keep working for days after the password it used is gone.
· Establish whether a connection pooler sits in front of the database. A pooler has its own authentication to the clients and its own server-side credentials, so there are two rotations to plan and they are not simultaneous.
· Check the password encryption method in force. SHOW password_encryption; and the matching entries in the host-based authentication file. Setting a password under one method while the authentication rules expect another produces a refusal that looks like a wrong password.
· Confirm you can watch sessions by role. Without a view of who is connected as what, the central claim of this procedure, that nothing is still using the retired role, cannot be demonstrated.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1Record the starting state: both role names, their validity, their group membership, and the current session counts by role. The session count is the measurement that the rest of the procedure moves, so it needs a value from before anything changed.
2Confirm the standby role has no active sessions and identical privileges. If the previous rotation left sessions behind on the role you are about to reuse, stop and find out why. That is an unfinished rotation, and finishing it is a different task from starting one.
3Generate the new password with a generator, not by hand, and place it in the secret store first. The database is the second place the value should exist, not the first. If the store write fails after the database change, the value exists only in a terminal buffer.
4Set the password on the standby role only. ALTER ROLE app_rw_b WITH LOGIN PASSWORD '...'; touches a role that nothing is currently using, so there is no window in which a running consumer holds a value the server has stopped accepting.
5Test the standby credential out of band before any consumer receives it. Connect with it, run the cheapest statement the application runs, and confirm the effective role. SELECT current_user, now(); is enough to prove authentication and identity together.
6Distribute the standby credential to one consumer and roll it. Pick the consumer that is cheapest to restart and easiest to observe. Confirm on the server that a session has appeared under the new role before touching the next one.
7Work through the remaining consumers in increasing order of blast radius. Update its store, roll it, confirm the new sessions appear. Updating every consumer at once removes the ability to attribute a failure and gains nothing but a few minutes.
8Force the pools to turn over rather than waiting for them to. A password change does not disturb sessions that are already authenticated, so a warm pool will keep serving on the old credential indefinitely. Recycle deliberately, at a time you choose, instead of discovering the stale credential during an unrelated failover at three in the morning.
9Watch the session count on the retired role fall to zero and stay there. Include the replicas and the pooler backend connections. A count that reaches zero and then climbs again means something reconnects on a schedule and has not been updated.
10Decision point: do not proceed while any session remains on the retired role. Identify the client address and the application name behind each remaining session and update that consumer. Proceeding here is what turns a clean rotation into an outage attributed to something else entirely.
11Soft-disable the retired role before changing anything irreversible. ALTER ROLE app_rw_a NOLOGIN; blocks new logins and leaves existing sessions alone, so it is both a strong test and a one-statement rollback. Leave it in this state long enough to cover the slowest consumer.
12Set a fresh unknown password on the retired role and record that you did. The role stays in place for the next rotation, holds no usable secret, and cannot be logged into. Do not drop the role: dropping it fails while it owns anything, and it removes the pair that makes the next rotation easy.
13Purge the old value from every store and from the pooler's authentication file. A retired password in a pipeline variable or an authentication file is confusing rather than dangerous, and the confusion arrives during the next incident.

4 · Verification

Confirm the procedure actually fixed the problem.

✓A query against pg_stat_activity on the primary and on every replica returns no session whose user is the retired role, sampled over a period longer than the pool's maximum connection lifetime.
✓Sessions under the new role are present in numbers consistent with the pool sizes recorded in the pre-checks, rather than a handful that suggest only one consumer migrated.
✓A connection attempt using the retired role is refused after the soft-disable, tested explicitly from a client host rather than inferred.
✓Each consumer's own health endpoint and error rate are unchanged, read from the application's telemetry rather than from the database.
✓The application performs a write and a read through the new role, proving the privileges arrived through group membership rather than being assumed.
✓The secret store holds the new value, no store on the inventory holds the old one, and the pooler authentication file has been updated and reloaded.
✓The change record names both roles, the rotation direction, the session counts before and after, and the time the retired role was disabled.

5 · Rollback

If verification fails, undo the procedure in reverse order.

↶A consumer fails immediately after receiving the new credential: restore the previous value in that consumer's store and roll it back. The other login role is still enabled with its own password throughout, so the fallback path is always available.
↶Many consumers fail together: stop distributing and investigate the standby role rather than the consumers. The usual causes are missing group membership, a validity date already in the past, or a password set under a different encryption method than the authentication rules expect.
↶Connections are refused after the soft-disable: run ALTER ROLE app_rw_a LOGIN; to restore it immediately. This is the reason the soft-disable exists and is why it comes before the password change rather than after.
↶A consumer surfaces after the retired password has been replaced: give it the current credential from the store rather than trying to restore the old password. The old value should not be recoverable, and if it is, that is itself a finding.
↶The rotation is abandoned partway: leave both roles enabled, restore the consumers that were changed, and record which consumer is on which role. Two live roles with a written record is a safe resting state; two live roles with no record is how the next rotation starts by guessing.
↶Terminating sessions was used to force turnover and the application handled it badly: stop terminating, let the pool recover, and fix the reconnection behaviour before continuing. A pool that cannot survive a dropped connection has a defect that a rotation merely revealed.

6 · Escalation

When the runbook isn't enough, contact:

· Sessions remain on the retired role from a client address nobody can identify: escalate to the platform owner before disabling anything, because disabling blind converts an unknown consumer into an unattributed outage.
· The login role also owns the schema objects: escalate to the data owner. Changing ownership is a migration with its own plan, and it should not be attempted inside a credential rotation window.
· A connection pooler authenticates clients from its own credential file and the file is managed by another team: escalate to that team, since their reload schedule sets the pace of the rotation.
· The database is a managed service where role administration is restricted: escalate to the service owner, as the provider may reserve the ability to alter roles or may rotate the master credential on its own schedule.
· The rotation is prompted by a suspected leak rather than by hygiene: escalate to the security on-call. The overlap window that keeps this procedure safe is exactly what a leak response cannot afford.

There is no safe way to change the password on the single role an application is currently using. Between the statement that changes it on the server and the moment every consumer has the new value, the running application holds a credential the database no longer accepts. Whether that gap is measured in seconds or hours, it is a gap, and it will be found by whichever consumer reconnects inside it.

The way out is to stop thinking of it as one credential. Keep two login roles that hold identical privileges through membership of a shared group role, and alternate between them. Only the role nothing is using ever gets its password changed, so there is never a moment when a correct consumer is holding a rejected value. This procedure rotates from role A to role B; the next one rotates back.

The reason this needs a runbook is the connection pool. A password change does not disturb sessions that are already authenticated, so a pool with warm connections keeps serving perfectly on a credential the database would now refuse. The rotation looks flawless. Then a deployment, a failover, or the pool’s own maximum connection lifetime causes a reconnect, and the outage arrives hours later with nothing in the change log to explain it.

When this runbook applies, and when it does not

It applies when an application’s database password must change while the application keeps serving: a scheduled rotation is due, somebody with the password has left, the credential was set by hand years ago, or an audit asks for evidence of rotation.

It does not apply when:

Only one login role exists and you cannot create a second. Create the pair first, as its own change, with the group role that owns the objects. Trying to invent the pattern during a rotation window is how privileges end up copied instead of inherited.
The credential is known to be leaked. The overlap window is a liability then, not a safety net. Accept the disruption, disable the role immediately and rebuild the pools.
The database issues short-lived dynamic credentials already. Each consumer holds its own leased credential that expires on its own, so there is nothing to rotate on a schedule. Adjust the lease.
The role that logs in also owns the schema. Retiring it later is a data-ownership migration. Fix the ownership first and rotate afterwards.

Blast radius

Action	Reversible?	What it costs if wrong
Reading roles, memberships and sessions	Yes	Nothing
Setting a password on the unused standby role	Yes	Nothing, because nothing is authenticating with it
Updating one consumer’s store and rolling it	Yes	That consumer only, until it is restored
Forcing a pool to recycle its connections	Yes	A short burst of reconnects, visible in latency
Terminating backend sessions to force turnover	Yes	In-flight statements on those sessions are lost
Disabling login on the retired role	Yes, with one statement	New connections as that role are refused immediately
Replacing the retired role’s password	No	Any consumer still holding it fails at its next reconnect
Dropping the retired role	No	It fails while it owns objects, and it removes the pair

Step 1 - Confirm the pair and their privilege parity

SELECT rolname, rolcanlogin, rolvaliduntil
FROM pg_roles
WHERE rolname IN ('app_rw', 'app_rw_a', 'app_rw_b')
ORDER BY rolname;

SELECT r.rolname AS member, g.rolname AS group_role
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.member
JOIN pg_roles g ON g.oid = m.roleid
WHERE g.rolname = 'app_rw';

Both login roles must appear as members of the same group role, and the group role is what holds the grants and owns the objects. Parity that was achieved by granting the same privileges twice looks identical today and drifts the first time somebody adds a table. Note the validity column as well: a role with a validity date in the past authenticates and then fails, which produces a confusing intermittent picture as pools recycle.

Step 2 - Record the session counts before you change anything

SELECT usename, count(*) AS sessions, min(backend_start) AS oldest
FROM pg_stat_activity
WHERE usename IN ('app_rw_a', 'app_rw_b')
GROUP BY usename
ORDER BY usename;

Run this on the primary and on every replica the application reads from. The oldest backend start time tells you how long connections survive in practice, which is usually more informative than the configured maximum lifetime. If any session already exists on the standby role, a previous rotation was never finished, and finding out why comes before starting a new one.

Step 3 - Set the password on the standby role, then test it

Read-only / SafeCaptured in the course lab: current_user reports the role a connection is actually using— Authentication and effective identity confirmed in a single statement.

$ psql -h 127.0.0.1 -U "$PGUSER" -d appdb -c "SELECT current_user, now();"

                   current_user                   |              now
--------------------------------------------------+-------------------------------
v-token-app-read-ghRGRAxnCRE9Q8zLVsIw-1787779423 | 2026-08-26 21:23:43.940523+00

Set the password with ALTER ROLE app_rw_b WITH LOGIN PASSWORD and then connect with it yourself, from a client host, before any consumer receives it. Confirm both that authentication succeeds and that the effective role is the one you expect. A credential that authenticates as the wrong role passes a naive connection test and fails the first statement that needs a privilege.

Step 4 - Distribute and roll each consumer

# One consumer at a time, cheapest and most observable first.
install -o root -g app -m 640 /root/rotation/pg-credential /etc/app/db-credential
systemctl reload app-api.service
curl -sS -o /dev/null -w '%{http_code}\n' http://127.0.0.1:8080/healthz

# Then confirm on the server that new sessions appeared under the new role.
psql -h db-03.example.com -U postgres -d appdb -c "SELECT usename, client_addr, application_name, backend_start FROM pg_stat_activity WHERE usename = 'app_rw_b' ORDER BY backend_start DESC LIMIT 10;"

Confirm each consumer twice: once from its own health endpoint, which proves the application is happy, and once from the database, which proves the connection is genuinely under the new role. The application’s own logs will happily report success while a pool quietly continues on the previous credential.

Step 5 - Force the pools to turn over

-- Prefer the pool's own recycle mechanism. Where that is unavailable,
-- terminate the retired role's sessions deliberately and in daylight.
SELECT pg_terminate_backend(pid), usename, client_addr, backend_start
FROM pg_stat_activity
WHERE usename = 'app_rw_a'
  AND pid <> pg_backend_pid();

Do this at a moment you choose. The alternative is not avoiding the reconnect, it is having the reconnect happen during an incident that somebody else is already handling. Terminating a backend loses the statement in flight on that session, so use the pool’s own recycle setting where one exists, and reserve termination for the connections that will not turn over any other way.

Step 6 - Prove nothing is still using the retired role

SELECT usename, client_addr, application_name, state, backend_start
FROM pg_stat_activity
WHERE usename = 'app_rw_a'
ORDER BY backend_start;

An empty result on the primary and on every replica, sampled over a period longer than the pool’s maximum connection lifetime, is the evidence this procedure is built around. A count that falls to zero and then climbs again is a consumer that reconnects on a schedule and has not been updated. The client address and the application name in each remaining row are normally enough to name the host, and a session nobody can attribute is a finding in its own right rather than a reason to proceed.

Step 7 - Soft-disable, then retire the credential

-- Reversible in one statement. Leave it here long enough to be sure.
ALTER ROLE app_rw_a NOLOGIN;

-- Only then remove the usable secret. Keep the role for the next rotation.
ALTER ROLE app_rw_a WITH PASSWORD 'generated-value-that-is-recorded-nowhere';

Blocking login is the strong test: any consumer you missed fails now, in your window, with you watching, and one statement puts it back. Existing sessions are unaffected by the change, which is why step 6 has to have passed first. Do not drop the role. It will refuse to drop while it owns anything, and keeping it is what makes the next rotation a fifteen minute job rather than a redesign.

Step 8 - Consider making this the last scheduled rotation

Read-only / SafeCaptured in the course lab: a leased credential, and the database after it is revoked— The role is created on demand, carries an expiry, and is removed when the lease ends.

$ bao lease revoke database/creds/app-readonly/xoHI541EXoFgKn1OTiusOdd9
psql -U v-token-app-read-ghRGRAxnCRE9Q8zLVsIw-1787779423 -d appdb -c "SELECT 1;"

All revocation operations queued successfully!

psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  role "v-token-app-read-ghRGRAxnCRE9Q8zLVsIw-1787779423" does not exist

A database secrets engine issues each consumer its own short-lived role with an expiry set at creation time, and removes it when the lease ends. There is then no shared password to rotate, no inventory of stores to reconcile and no observation window to sit through. The work moves to making every consumer fetch and refresh a credential at runtime, which is the project that retires this runbook rather than repeating it every quarter.

Common pitfalls

Symptom	Cause	Action
The rotation is clean and the outage arrives days later	A pool never recycled and was still on the old credential	Force turnover during the window; set a finite connection lifetime
Authentication fails for every consumer at once	The password was set under an encryption method the authentication rules do not accept	Check the encryption setting and the host-based rules, then set it again
The new role connects but statements are denied	Privileges were copied rather than inherited from the group role	Grant membership of the group role; do not re-grant object privileges
Sessions reappear on the retired role after reaching zero	A scheduled job reconnects and was never updated	Identify it by client address, update it, restart the observation
Dropping the retired role fails	It owns objects	Do not drop it; keep the pair and move ownership separately
The pooler still authenticates clients with the old value	Its own credential file was not updated or reloaded	Update the file and reload the pooler; treat it as a consumer
Terminating sessions caused visible errors	The application does not retry a dropped connection	Fix the retry behaviour; a rotation only revealed the defect

Verification

The rotation is finished when pg_stat_activity on the primary and on every replica returns no session under the retired role, sampled over a period longer than the pool’s maximum connection lifetime, and the session counts under the new role match the pool sizes recorded before the change. A connection attempt as the retired role is refused after the soft-disable, tested from a client host rather than assumed. Each consumer’s health endpoint and error rate are unchanged in its own telemetry, and the application has completed both a read and a write through the new role. The secret store holds the new value, no store on the inventory holds the old one, and the pooler’s authentication file has been updated and reloaded.

Rollback

While both roles are enabled the rollback is per consumer: restore the previous value in that consumer’s store and roll it back. If many consumers fail together, stop and investigate the standby role rather than the hosts, since the usual causes are a missing group membership, a validity date in the past, or a password set under the wrong encryption method. If connections are refused after the soft-disable, re-enable login with a single statement, which is exactly why that step precedes the password change. If a consumer surfaces after the retired password has been replaced, hand it the current credential from the store, and treat a recoverable old password as a finding. If the rotation is abandoned partway, leave both roles enabled and record which consumer is on which.