Rotating a database password with no downtime

Changing a database password is the rotation that most often looks successful and is not. The application keeps serving traffic, the monitoring stays green, the change is signed off, and the failure arrives hours or days later during an unrelated event. The reason is entirely mechanical, and once you see it the correct procedure follows from it.

Why the pool hides the failure

A connection pool authenticates when it opens a connection, not when it runs a statement. Once a session is established the credential has done its job and is never presented again for the life of that connection. Changing the stored password therefore has no effect on any session that already exists.

sequenceDiagram
    participant App as Application pool
    participant DB as PostgreSQL
    App->>DB: open session as app_rw_a, password P1
    DB-->>App: authenticated, session established
    Note over App,DB: session reused for hours, no further auth
    Note over DB: password for app_rw_a changed to P2
    App->>DB: statement on the existing session
    DB-->>App: succeeds, nothing has changed
    Note over App: pool recycles the connection
    App->>DB: open session as app_rw_a, password P1
    DB-->>App: authentication failed

What forces the pool to reopen a connection is therefore what determines when the outage starts. The usual triggers are a maximum connection lifetime, an idle timeout on either side, an application restart or deployment, a network interruption, and a database failover. None of them is under the control of whoever ran the password change, and several of them cluster at times when the team is not watching.

# Read the pool's maximum connection lifetime before you plan
# anything. It is the lower bound on how long the outgoing
# credential must remain valid.
CONF_DIR=/etc/api
grep -Rn --include='*.yaml' -e max_lifetime -e maxLifetime -e pool_recycle "$CONF_DIR"

A pool configured with a thirty-minute maximum lifetime turns over completely within thirty minutes, which makes the rotation window short and the failure prompt. A pool with no maximum lifetime recycles a connection only when something goes wrong with it, which can be weeks. The second case is more dangerous precisely because it is quieter.

Two roles, not two passwords

PostgreSQL stores one password verifier per role. The password field is a single slot in the sense of the previous lesson, so the overlap cannot live there. It lives one level up, in the set of login roles.

The critical detail is that the two roles must have identical authority, and the reliable way to achieve that is to own no privileges directly. Put every grant on a group role, then make both login roles members of it. Privileges are then equal by construction, and cannot drift because someone granted a table to one role and forgot the other.

-- Privileges live on the group role. The login roles inherit
-- them and own nothing themselves.
CREATE ROLE app_rw NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;

-- The incoming login role. Never type a real password into DDL:
-- with statement logging enabled it lands in the server log in
-- clear. Use the psql \password command, which computes the
-- verifier locally and sends only the verifier.
CREATE ROLE app_rw_b LOGIN IN ROLE app_rw PASSWORD 'lab-only-not-real';

The ALTER DEFAULT PRIVILEGES line matters more than it looks. GRANT ... ON ALL TABLES applies only to the tables that exist at the moment it runs. Without the default-privileges statement, a table created next month is invisible to the group role, and the rotation you perform next month fails for a reason that has nothing to do with the rotation.

The ordering, with its exit criteria

Each step below ends on an observation. The steps up to and including the drain are all reversible by a single action.

1. Create the group role and move the grants. Confirm the existing role still works through the group by revoking its direct grants and running a representative query.
2. Create the incoming login role and set its password. Confirm it can connect and perform the same operations, using a real query against a real table rather than a bare connection test.
3. Publish the new credential to the secret store. The application does not read it yet. Confirm the stored value resolves and is readable by the identity the application uses.
4. Cut the application over, one instance at a time. Restart the first instance, confirm it is serving, then continue. Nothing is removed at this point, so a bad instance can be reverted by restarting it with the previous configuration.
5. Force the remaining connections to turn over. Either wait out the maximum connection lifetime or roll the remaining instances deliberately. Waiting is only acceptable when the pool has a bounded lifetime.
6. Measure the drain. Hold until the session view reports no sessions authenticated as the outgoing role, for longer than the pool lifetime.
7. Disable the outgoing role. This is a reversible withdrawal, not a deletion.
8. Delete the outgoing role. Only after a soak, and only after its object ownership has been dealt with.

Step two carries more weight than its position suggests. A connection test that only opens a session proves that the password is right and proves nothing about authority, because login succeeds before any privilege is consulted. The incoming role has to run something representative: read from the busiest table, insert into whatever the write path touches, call the functions the application calls. Privilege problems discovered at step two are a five-minute grant. The same problems discovered at step four are a partial outage in whichever slice of traffic migrated first, and they arrive as application errors rather than as connection errors, so they are attributed to the deployment rather than to the rotation.

Step four is where the rotation earns its no-downtime claim, and it earns it by being ordinary. Instances are moved one at a time because that is how any other configuration change is rolled out, and because a single instance that fails to start on the new role is a rollback of one instance rather than of the service. Nothing has been removed at this point: the outgoing role still works, so reverting an instance means restarting it with the previous configuration, and that is the whole recovery.

Proving the drain from the database

The deployment tool knows which application instances it restarted. The database knows which sessions exist. Only the second answers the question:

SELECT usename,
       count(*)            AS sessions,
       min(backend_start)  AS oldest_session
FROM pg_stat_activity
WHERE datname = 'appdb'
GROUP BY usename
ORDER BY usename;

Two columns carry the meaning. A non-zero count for the outgoing role means at least one holder is still connected under the old credential, and retiring it now would break that holder. The age of the oldest session tells you whether the pool is turning over at all: if the oldest session predates the rotation by days, the pool has no effective maximum lifetime and step five was skipped.

Retiring the old role without losing data

Retirement has a reversible half and an irreversible half, and in a database the irreversible half has an unusually sharp edge.

-- Reversible: the role and everything it owns remain, but it can
-- no longer authenticate. Undo is a single ALTER ROLE ... LOGIN.
ALTER ROLE app_rw_a NOLOGIN;

-- Irreversible, and only after the soak. Move ownership first;
-- deleting a role that still owns objects will either fail or,
-- with the wrong command, destroy those objects.
REASSIGN OWNED BY app_rw_a TO app_rw;
DROP ROLE app_rw_a;

REASSIGN OWNED BY transfers ownership of tables, sequences and other objects to the target role. Its neighbour DROP OWNED BY does something entirely different: it removes those objects. Reaching for the second when a DROP ROLE fails is a very fast way to lose production tables, and the error message that prompted it says nothing about the difference.

Once the role is gone, a holder that still presents it does not receive a password error. It receives a message that the role does not exist, which is what an explicitly revoked dynamic credential produces as well:

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

That message is worth recognising, because it distinguishes a retired credential from a mistyped one. A wrong password reports authentication failure; a deleted role reports absence. During an incident that distinction tells you immediately whether you are looking at a rotation that went one step too far.

Production discipline

1. Read the pool configuration first. The maximum connection lifetime is the number that decides how long the outgoing role must remain able to log in.
2. Grant through a group role. Two login roles with hand-copied grants will diverge, and the divergence is discovered in production by the half of the traffic that moved.
3. Never put a password in DDL you are logging. Use the client command that derives the verifier locally so the plaintext never enters the server log.
4. Read the drain from the session view. The deployment report covers managed instances; the database covers everyone.
5. Disable before you delete, and reassign before you drop. The reversible step costs nothing, and the command next to the right one destroys data.

Cross-course references

• Observability for Production Sysadmins - Part LIX (DatabaseObs) covers connection and session metrics, which turn the drain measurement in this lesson into a dashboard rather than a manual query.
• Kubernetes for Production Sysadmins - Part LXV (SecretsSec) covers how a workload receives a database credential and what has to happen for it to notice a new one.
• Linux for Production Sysadmins - Part LXXII (Secrets) covers the host-side storage that the application reads its connection string from.

Quiz