Rotating database credentials without downtime

Ask any team when they last rotated their production database password. The honest answers cluster around 'when we set it up' and 'there was an incident.' Everyone knows it should happen on a schedule. Almost nobody does it, and the reason isn't laziness — it's that the obvious approach breaks production, so the task gets deferred until an incident forces it, which is exactly the worst moment to attempt an operation you've never practised.

Here's what breaks, the pattern that avoids it, and what to do when a credential is already leaked and you don't have the luxury of doing this gracefully.

Why the obvious approach hurts

The naive rotation is one statement: `ALTER ROLE app WITH PASSWORD '...'`. Then update the secret and restart the app.

The problem is the window between those two steps, and what lives inside it. Existing connections aren't affected — Postgres authenticates at connect time — but every new connection with the old password fails immediately. Your connection pool will open new connections during that window, because pools constantly recycle. Every serverless function cold-starting in that window fails. Every background worker that reconnects fails. Your read replicas, if they authenticate the same way, may drop out.

So a change that looks atomic is really a partial outage of unpredictable length across every service that touches the database, including the ones you forgot use it. That's why the runbook says 'schedule a maintenance window,' and why it never gets scheduled.

The two-user pattern

The standard solution is to never rotate the password of a user that's in active use. Instead, keep two users with identical permissions and alternate between them.

1. Create `app_a` and `app_b`, both members of a role that owns the actual permissions. Applications use one of them at a time.
2. To rotate: set a new password on the user that is currently unused — `app_b`, say. Nothing is connected as it, so nothing breaks.
3. Update your secret store to hand out `app_b` and its new password.
4. Roll your services. Each picks up the new credential on restart and connects as `app_b`. Old connections as `app_a` keep working the whole time, so there is no window where valid credentials don't exist.
5. Once nothing is connected as `app_a` — verify, don't assume — rotate its password too. It's now the standby for next time.

The key property is that at every instant, at least one valid credential exists and is in the secret store. No window, no partial outage, no maintenance window to schedule. Rotation becomes a routine operation you can run monthly without anybody noticing, which is the only kind of security practice that survives contact with a busy team.

-- One role owns the permissions; the two users inherit them.
-- This is the setup that makes rotation boring.
CREATE ROLE app_role NOLOGIN;
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

CREATE USER app_a WITH PASSWORD 'initial-a' IN ROLE app_role;
CREATE USER app_b WITH PASSWORD 'initial-b' IN ROLE app_role;

-- Rotation day: change the password of the user NOBODY is using.
ALTER ROLE app_b WITH PASSWORD 'new-strong-value';

-- Then flip the secret store to app_b and roll services.
-- Before rotating app_a, confirm nothing is still connected as it:
SELECT usename, count(*), max(backend_start)
FROM pg_stat_activity
WHERE usename IN ('app_a','app_b')
GROUP BY usename;

When the platform rotates for you

Managed platforms often expose rotation as a single operation. On PandaStack, resetting a database's credentials rotates the Postgres password and the HTTP broker token together, synchronously, and returns the new values only after verifying they actually work.

That synchronous-and-verified part matters more than it sounds. An asynchronous rotation that returns immediately can hand you a credential that was never applied — and you discover this when your deploy fails with an authentication error and you no longer know which password is live. Verifying before returning means the value in your hand is known-good.

# Rotate the database password and broker token together.
# Synchronous: the response contains verified, working credentials.
curl -sS -X POST https://api.pandastack.ai/v1/databases/$DB_ID/reset-credentials \
  -H "Authorization: Bearer $PANDASTACK_API_KEY"

# Then: write the new value to your secret store FIRST, and roll services
# after. Doing it the other way round means services restart into a
# credential that is no longer valid.

Note the ordering in that comment. Platform-managed rotation still leaves you a coordination problem: the moment the platform rotates, the old password stops working. So the secret store must be updated before services restart, and any service that restarts during the gap will fail. For zero-downtime rotation on a busy system, the two-user pattern is still the answer — the platform operation is the right tool for a rebuild, a handover, or a leak.

When the credential has already leaked

Different situation, different priorities. Graceful rotation optimises for no downtime. Leak response optimises for the credential being useless as fast as possible, and a brief outage is an acceptable price.

1. Rotate immediately. Do not schedule it, do not wait for a window. Accept the connection errors.
2. Terminate existing sessions. Rotation does not disconnect anyone already authenticated, so an attacker holding an open connection keeps it. Use `pg_terminate_backend` to close sessions for that user — this is the step people forget, and it's the one that actually ends the access.
3. Check what the credential could reach. If it had superuser or broad grants, the rotation is the beginning of the investigation, not the end. Assume anything it could read was read.
4. Look for what else leaked with it. Credentials rarely leak alone; a committed environment file or a compromised CI job usually held several.
5. Then fix the source. A password in a repository, a token in a log line, a secret in a build artifact — rotating without fixing the source means doing this again next month.

-- Leak response: rotate, THEN kick everyone off. Rotation alone does not
-- disconnect a session that is already authenticated.
ALTER ROLE app_a WITH PASSWORD 'rotated-now';

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_a'
  AND pid <> pg_backend_pid();

-- Then audit what that role could actually reach.
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolbypassrls
FROM pg_roles WHERE rolname = 'app_a';

The habits that make this a non-event

• Least privilege from day one. The application user should not own the schema or be a superuser. Rotating a credential that could only read three tables is a much calmer afternoon than rotating one that could drop the database.
• Separate credentials per consumer. The web app, the worker, the analytics job, and the migration runner should have different users. Then a leak in one is contained, and rotation touches one service rather than all of them.
• Short-lived credentials where the platform supports them. A credential valid for an hour is one you never have to rotate under pressure — the same logic behind short-lived installation tokens for git clones instead of stored deploy keys.
• Practise on staging. A rotation you have never performed is a procedure, not a capability. Run it quarterly on a non-production database, from the runbook, and time it.
• Know who is connected. Keep the pg_stat_activity query in the runbook. Most rotation surprises are a forgotten consumer, and five seconds of looking prevents them.

The reason to invest here isn't the scheduled rotation. It's that when a credential does leak — a laptop, a repo, a log aggregator with too much retention — you want the response to be a routine operation you ran last month, not an unfamiliar procedure attempted at 11pm by someone reading Stack Overflow while the security channel fills up.