Why rotation gets deferred
The audit finding at Aldridge Risk was blunt: the primary application credential for the risk-reporting database had not changed since the service was built. Nobody was careless. Rotation had been attempted twice and rolled back both times, because changing the password while services hold open pooled connections produced authentication failures on the next pool refill, at an unpredictable moment after the change.
That unpredictability is the actual obstacle. ALTER ROLE ... PASSWORD in Postgres does not disturb existing sessions; they authenticated already. The failure arrives minutes later when a pool recycles a connection, by which time the deploy looks finished and the engineer has moved on. A rotation whose blast radius is 'sometime in the next twenty minutes' will never be run routinely.
Rotation frequency is a function of how boring the procedure is. Make it boring or it will not happen.
Two roles, one grant set
The pattern is two credentials per service, alternating. Objects are owned by a role that no application uses. Privileges are granted to a group role, risk_app. Two login roles, risk_app_a and risk_app_b, are members of that group and hold nothing directly. Rotation means changing the password of the inactive role, pointing the secret at it, letting pools drain, then invalidating the other.
Because both roles inherit identical privileges from the group, switching between them changes nothing about what the application can do. Grants are managed once, on the group. This also removes the second-most-common rotation failure, where the new credential works for reads and fails on the first write hours later because a GRANT was missed. Add ALTER DEFAULT PRIVILEGES for the owning role so new tables are covered automatically.
Applications read the credential from a secret with a version, and every pool is configured with a maximum connection lifetime — ours is 15 minutes. That bound is what makes the next step calculable.
The revoke is the part with a deadline
Rotation without revocation is theatre. The old credential must stop working, and the timing has to account for every consumer. With a 15-minute maximum connection lifetime and a 5-minute secret refresh interval, all live connections are guaranteed to use the new credential within 20 minutes. We wait 2x that, then run ALTER ROLE risk_app_b NOLOGIN followed by a password change, and terminate any remaining sessions for that role with pg_terminate_backend.
Between switch and revoke there is a verification gate. A canary job attempts a connection with the old credential and asserts success before the revoke and failure after. Without the second assertion you have no evidence the revoke worked, and 'we changed the password' is not the control the auditor asked for.
Cron jobs, ad-hoc analytics containers and that one Windows scheduled task are what break this. Before the first rotation, enumerate consumers from pg_stat_activity's application_name over a full month, not a day. We found four consumers nobody had listed, one of which ran quarterly.
What to measure
Three signals. Credential age p95 across all secrets, with an SLO of 30 days — it is a distribution, and the tail is where the three-year-old password hides. Authentication failure rate per role, which should be flat at zero and spikes for exactly one role during a botched rotation. And rotation success rate, since a job that silently stops running looks identical to one that has nothing to do.
Log line origin matters for the second signal. Postgres logs failed authentication with the role and client address, so ship those and alert on any failure for a role marked active. During normal operation this is silent, which makes it a useful alert rather than noise.
Aldridge now rotates every 30 days on a schedule, unattended, with no maintenance window. The engineering work took nine days. The reason it had not happened for three years was not difficulty, it was that the procedure had never been made repeatable.