Changing a PostgreSQL password without downtime: what ALTER USER actually does

Short answer: ALTER USER … WITH PASSWORD does not drop a single open connection. PostgreSQL checks the password once — when the connection is established — and sessions that are already running keep working with the old password for as long as they live. The downtime does not come from the change itself; it comes later, on the first reconnect of a service that reads the password from a stale .env, from an environment variable, or from a pool that was filled an hour ago.

Hence the practical conclusion: what is dangerous is not the password change but the window between the change and the moment every consumer learns the new value. Below: how to measure that window, how to shrink it to zero, and what has to be ready in the database first.

What happens in the database

There is exactly one statement:

ALTER USER app_a WITH PASSWORD 'new-password';

What the server does with it:

• stores a hash, not the password itself: since PostgreSQL 14 the default is scram-sha-256 (the password_encryption setting);
• tells open sessions nothing. They have already authenticated, and nobody asks them to do it again;
• starts requiring the new password from every new connection — immediately.

A side effect people forget: with log_statement = 'ddl' or 'all' the statement text lands in the server log together with the password. If you rotate passwords with a script, check that setting before the script runs for the first time.

Where the downtime actually comes from

Connections are not dropped, so nothing breaks right away. That is the trap: the breakage is deferred, and it arrives at an inconvenient moment.

In other words: a manual password change looks successful right up to the first reconnect — which is exactly why its consequences are usually investigated by a different person at a different hour than the one who made it.

Why the manual drill breaks

The classic order — "change the password in the database, then push it to the services" — creates a window where some instances already read the new value and some still read the old one. One of the two must fail: once the database has the new password, every new connection with the old value is refused.

Reversing the order does not help: pushing the new password out before it exists in the database is the same window, mirrored. The only way to remove the window is to stop requiring the two moments to coincide: consumers must keep reading the old value until the last step and switch atomically, only after the new value has been verified.

Four phases instead of one long evening

Automatic rotation in env-master splits the replacement into four steps:

create → set → test → finish

• create — generate the new password. This phase never touches the database;
• set — run ALTER USER over an administrative connection;
• test — connect as the new login with the new password and run SELECT 1;
• finish — move the current label, after which consumers start receiving the new value.

The third phase is the point of the whole thing. Rotation without verification is worse than no rotation: you get confidence instead of working access. The password is changed, distributed — and does not work, because the role has no CONNECT on the database it needs, or is not LOGIN. If test fails, the current label does not move: consumers keep working with the old value while the administrator gets a rotation.failed event, a webhook and a metric instead of a silent failure under load.

Between create and finish the new password is already stored under the pending label. This is not an implementation detail: if the process crashes after ALTER USER but before the write to storage, the password is lost — and it is already in effect in the database, with nowhere to recover it from.

One user or two

The two strategies differ only in whose password changes on a run.

single-user — the password of the single role your services use is changed. Open connections live on; new connections with the old password are refused until the service re-reads the value. With a connection pool this usually goes unnoticed: the pool keeps its connections open, and new ones come up with the new password.

dual-credential — two equivalent roles exist in the database, and a run changes the password of whichever is not in use. The scheme is meant as "zero downtime": while one name is being rotated, the other serves connections.

For the second scheme to work, what rotates must be the pair, not the password: the active role changes every run, and a password without a name is useless to the service. So dual-credential publishes a connection string:

postgres://app_b:pg_xxxxxxxx@db.internal:5432/billing

The name and the password arrive together, in one version of one entry — that is, atomically. Between two separate entries there is always a window in which they disagree, and landing in it means getting one role's password under another role's name. Name such entries accordingly: DATABASE_URL, not DB_PASSWORD.

What to prepare in the database

The rotator changes passwords, but it does not create roles and does not grant privileges. Before the first run the database must already have:

CREATE ROLE app_a LOGIN PASSWORD 'temporary';
GRANT CONNECT ON DATABASE billing TO app_a;
-- plus your grants on schemas and tables

The administrative connection that runs ALTER USER is a secret too, and it should be stored as one: a separate entry visible to services only, not a string inside the policy config. A superuser is not required: any role your PostgreSQL version allows to change the password of exactly these users will do.

How the service learns the new value

Changing a password is pointless if the service reads it once at startup. The order in which the window actually closes:

1. the service takes the value through the SDK, not from an environment variable;
2. the SDK keeps entries in a cache — 15 minutes by default, so the usual delay after finish is under a quarter of an hour;
3. refresh() drops the cache immediately; calling it from the entry.rotated webhook turns rotation into an event for the service instead of a wait;
4. the database driver receives the password when a connection is established — that is, the next time the pool adds one, not when the process restarts.

Checklist

• the password is changed by a statement that does not drop open connections — make sure you are not restarting the database "to apply it";
• the new password is verified by connecting with it before consumers hear about it;
• consumers read the value from storage, not from the image and not from .env;
• the administrative DSN is stored as a secret and is not visible to people;
• rotation has a schedule, not an occasion: a secret that only gets changed after an incident is several years old by the time the incident happens.

How to set this up in five steps is in the rotation documentation. Why secrets should not be delivered as a file in the first place is in "Why .env files don't belong in production".