Runbook: Rotate a Database Credential Without an Outage

1 · Prerequisites

Confirm every item is in place before any state change.

• The role to rotate, every application that uses it, and the mechanism each one uses to read its credential
• Write access to the secrets store that holds the credential, and knowledge of how quickly each consumer picks up a change
• A database connection with privilege to alter the role
• The reason for the rotation recorded: scheduled, suspected exposure, staff change, or audit finding — because a suspected exposure changes the urgency and the order of steps
• A way to observe connections by role, so you can tell when the last consumer of the old credential has gone
• Agreement on whether an overlap period is acceptable, or whether the old credential must die immediately

2 · Pre-checks

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

• · Enumerate every consumer. SELECT usename, application_name, client_addr, count(*) FROM pg_stat_activity WHERE usename = :role GROUP BY 1,2,3 ORDER BY 4 DESC; A rotation that misses one consumer produces an outage for that consumer at an unpredictable time, when its pool next reconnects.
• · Confirm the role's current state before changing it. SELECT rolname, rolcanlogin, rolvaliduntil, rolvaliduntil < now() AS expired, rolconnlimit FROM pg_authid WHERE rolname = :role; If rolvaliduntil is already in the past, the problem you were asked to solve is not the password.
• · **Confirm password_encryption is scram-sha-256.** SHOW password_encryption; The new password is hashed with whatever this says at the moment you set it.
• · Establish how each consumer reloads its credential. Some read it once at start-up; some poll the secrets store; some re-read on connection failure. This determines whether the rotation is instant or needs a deploy, and it determines the length of the overlap.
• · Confirm you can observe the effect. With log_connections on you can see authentications by role. Without it you are relying on absence of complaints, which is not a signal.
• · If this is a suspected exposure, say so and change the plan. An overlap period is a deliberate window in which the compromised credential still works. That is acceptable for a scheduled rotation and not acceptable for a suspected one.

3 · Procedure

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

1. 1Choose the strategy before touching anything. Overlap by second role is the only approach with no window at all: create a new role, migrate consumers, drop the old. Single-role rotation changes the password in place and requires every consumer to pick it up promptly. Say which one you are doing and why.
2. 2For the second-role strategy, create the new role as a member of the same group. CREATE ROLE app_ro_v2 LOGIN PASSWORD '<from-store>'; GRANT app_readonly TO app_ro_v2; It inherits the privileges without any grant being repeated, which is the reason the group role exists.
3. 3Copy the role-scoped settings. SELECT rolname, rolconfig FROM pg_roles WHERE rolname = :old_role; then apply the same ALTER ROLE ... SET statements to the new role. A statement_timeout or work_mem that does not follow the rotation produces a behaviour change nobody attributes to it.
4. 4Write the new credential to the secrets store, alongside the old one rather than over it, so consumers can migrate at their own pace.
5. 5Migrate consumers one at a time, watching each one connect as the new role before starting the next. SELECT usename, application_name, count(*) FROM pg_stat_activity WHERE usename IN (:old, :new) GROUP BY 1,2;
6. 6Wait for the old role to go quiet. Zero connections is necessary and not sufficient — a batch job that runs weekly will not appear. Check the log for authentications by the old role over a period longer than the longest scheduled job.
7. 7Disable the old role before dropping it. ALTER ROLE app_ro NOLOGIN; This is reversible in one statement and produces an unmistakable error — role "app_ro" is not permitted to log in — for anything you missed.
8. 8Wait, then drop. Leave the old role in NOLOGIN for at least one full cycle of the longest scheduled job. Then REASSIGN OWNED BY app_ro TO app_owner; if it owns anything, and DROP ROLE app_ro;.
9. 9For the single-role strategy, set the new password in one statement. ALTER ROLE app_ro PASSWORD '<from-store>'; Existing connections are unaffected; only new authentications use the new value.
10. 10Understand what that means for the window. Established sessions keep working, so a pool that never reconnects will not notice the rotation until it does — and then all at once. Force the transition deliberately rather than waiting to be surprised by it.
11. 11Update the secrets store, then restart or reload each consumer in a controlled order, confirming each reconnects successfully before moving to the next.
12. 12Prove the old credential no longer works. From the application's network path, attempt a connection with the old password and confirm it is refused. This is the step that distinguishes a rotation from a password change nobody depends on.
13. 13Record the rotation: role, strategy, date, secrets-store path, the consumers migrated, and the result of the old-credential test.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓Every consumer is connected as the intended role: SELECT usename, application_name, client_addr, count(*) FROM pg_stat_activity GROUP BY 1,2,3 ORDER BY 1; with no rows for the old role.
• ✓A connection attempt with the old credential, from the application network path, is refused. Check the server log for the FATAL and its DETAIL.
• ✓A connection attempt with the new credential, from the same path, succeeds — and the server log names the pg_hba.conf rule that matched.
• ✓The application's own health checks pass, and its error rate has not moved. A rotation that broke one endpoint out of forty is a rotation that has not been verified.
• ✓The role-scoped settings survived: SELECT rolname, rolconfig FROM pg_roles WHERE rolname = :role; matches what the old role carried.
• ✓The secrets store contains only the current credential once the migration is complete, and the old value has been removed rather than left as a comment.
• ✓For the second-role strategy, the old role is NOLOGIN or dropped, and the change note says which and when the other happens.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶For the second-role strategy, rollback is to point consumers back at the old role. Nothing has been destroyed until the old role is dropped, which is why the drop is the last step and is deliberately delayed.
• ↶If the old role was set NOLOGIN and something breaks, ALTER ROLE app_ro LOGIN; restores it in one statement. The error it produces — is not permitted to log in — is distinctive enough that the cause is unambiguous.
• ↶For the single-role strategy, there is no rollback to the old password: you do not have it, because it was generated in the secrets store and overwritten. If a consumer cannot take the new value, the recovery is to give that consumer the new value, not to restore the old one.
• ↶If a consumer is locked out and cannot be reconfigured quickly, create a temporary second role with the same group membership rather than reverting the rotation. This keeps the rotated credential rotated.
• ↶If the rotation was for a suspected exposure, do not roll back to the old credential under any circumstance. A consumer that cannot take the new value is an availability problem; restoring a compromised credential is a security incident.
• ↶If the role was dropped prematurely and an unknown consumer appears, recreate it with a new password and the same group membership, then investigate the consumer. A recreated role is not the old role, and anything relying on ownership will need attention.

6 · Escalation

When the runbook isn't enough, contact:

• · A consumer cannot be identified from pg_stat_activity or the log, and connections by the old role continue: escalate to the application owners rather than dropping the role. An unattributed connection is an inventory problem, and dropping the role converts it into an outage with no diagnosis.
• · The credential is used by a system you do not control, or by a third party: escalate before rotating. The rotation window is defined by their change process, not yours.
• · This is a suspected exposure and any consumer cannot take the new credential within the required window: escalate to security and to the service owner together. The decision to leave a compromised credential live is not a database-team decision.
• · The role turns out to be shared by several applications that were believed to be separate: escalate. This is the moment to split it, and doing that during a rotation without agreement will break something.
• · The role owns objects and nobody can say who should own them instead: escalate to the data owner before running REASSIGN OWNED BY. Ownership determines who can alter the objects, and moving it is not a neutral act.
• · The rotation is required by an audit finding with a deadline you cannot meet without an outage: escalate rather than taking the outage unannounced. The deadline and the availability requirement are both somebody else's to reconcile.

References

1. PostgreSQL 18 documentation, ALTER ROLE
2. PostgreSQL 18 documentation, Password Authentication
3. PostgreSQL 18 documentation, Role Membership
4. PostgreSQL 18 documentation, DROP ROLE