Rotate Database Application Credentials Without Downtime

Replace an application database password in controlled phases: create a successor role, migrate every consumer, confirm zero old connections, then disable the old login.

intermediate~45 mindatabasesecurityoperations

Objective

Replace the password for an application database role while every running service moves to the new credential before the old one is disabled.

Environment

PostgreSQL 14 or newer with a login role used by the application
A versioned secret manager that can roll back to the previous secret version
Access to every consumer: web pods, workers, cron jobs and reporting tools
A recent tested backup plus role grants exported with pg_dumpall --roles-only

Steps

1. Inventory consumers and grants

Search secret manager metadata, deployment manifests and pg_stat_activity for every principal that uses the application role, then export the role grants.

Expected state: A written consumer list exists and the grants export matches the live role.

2. Create the successor role

Create app_rw_v2 with LOGIN and the same role memberships as app_rw, then set its password interactively with psql so no secret enters shell history.

Expected state: app_rw_v2 can connect and run a representative read and write query.

3. Publish the new secret and redeploy

Store the new credential under a new version, update the secret reference and roll every consumer so each pod or job reads the new version.

Expected state: All consumers connect as app_rw_v2 and old-role sessions fall to zero.

4. Disable the old login

Run ALTER ROLE app_rw NOLOGIN, keep the role and its grants for seven days, and watch application error rates and connection counts.

Expected state: No connection succeeds as app_rw and no consumer reports authentication failures.

5. Retire and record the rotation

Drop app_rw after the observation window, archive the grants export and record the rotation date, owner and next due date.

Expected state: The credential inventory lists only app_rw_v2 with an owner and a future rotation date.

Decision points

Should the application keep a single shared role or move to one role per service?

Keep one shared role for a faster rotation
Split into per-service roles during the rotation

Split when the consumer list is short; per-service roles make the next rotation and least-privilege review much cheaper.

How long should the old role stay available after cutover?

Disable immediately after the last consumer migrates
Keep NOLOGIN for seven days before dropping

Keep the disabled role for seven days so a missed consumer can be re-granted without recreating ownership.

Expected outcome

Every consumer authenticates with the new credential, the old login is disabled and removed, and no service experienced an authentication outage.

Verification

Open a psql session as app_rw and confirm it is rejected, connect as app_rw_v2, then check pg_stat_activity and application dashboards for zero authentication errors.

Cleanup

Drop the old role after seven days, delete exported grant files and temporary secret versions, and schedule the next rotation.

Rotate through a successor role

PostgreSQL keeps a single password per role, so editing the password in place forces every consumer to restart at once and gives you no overlap. Create app_rw_v2, grant it membership in the existing app_rw role so it inherits the same privileges, and migrate consumers one deployment at a time. The old role remains valid until the last caller has moved.

CREATE ROLE app_rw_v2 LOGIN;
GRANT app_rw TO app_rw_v2;
\password app_rw_v2

SELECT usename, count(*)
FROM pg_stat_activity
WHERE usename IN ('app_rw', 'app_rw_v2')
GROUP BY usename;

Safety checks before you start

Confirm the latest backup restores, export the role definitions with pg_dumpall --roles-only, and record which tables and sequences the role owns so a replacement can be recreated exactly. Check whether the application relies on current_user for row-level security or auditing; if it does, policy changes must ship with the new role name. Never paste the new password into a ticket or shell history: use \password or write directly from the secret manager.

Cut over and verify

Deploy the new secret reference, restart consumers in waves, and watch authentication errors after each wave. When pg_stat_activity shows no app_rw sessions, run ALTER ROLE app_rw NOLOGIN and keep observing for a full business cycle. Drop the role only after seven quiet days, and record the rotation in the credential inventory.

Rollback

Before the drop, rollback is a two-step change: run ALTER ROLE app_rw LOGIN and point consumers back to the previous secret version. Both actions are fast and do not touch data. After DROP ROLE app_rw, rollback requires recreating the role from the exported grants and restoring ownership; treat the drop as the irreversible step and delay it until the observation window has closed.