Lab 23: Rotate a database password with zero downtime

Objective

You will rotate the password of a database account that a running service is using, twice. The first rotation is the one almost everybody writes the first time: change the password on the account the application uses. You will watch a healthy consumer start failing within a second, and you will keep the evidence.

The second rotation reaches the same end state with no failed connection at all. The mechanism is the dual-credential pattern: privilege lives on a group role that never logs in, identity lives on login roles that are cheap to create and cheap to destroy, and for a bounded window two credentials are simultaneously valid. The cutover moves consumers inside that window, and the window closes only once nothing is using the old credential.

By the end you will be able to answer a question that comes up in every change review: how do you know the old credential is gone, and how do you know nothing broke while you removed it.

Architecture

One PostgreSQL 17 container holds a table called ledger. One consumer container queries it once a second, opening a fresh connection each time and reading its username and password from a file on the host at the start of every iteration. Rotating the consumer therefore means editing one file, with no restart and no orchestrator involved, which keeps the lab focused on the database side of the problem.

flowchart LR
    A["consumer container\nreconnects once a second"] -- "reads each loop" --> B["app.env\ncredential file"]
    A -- "new connection" --> C["rbpki-pg-23\nPostgreSQL 17"]
    D["app_v1\nlogin role"] -- "member of" --> F["rbpki_app\nNOLOGIN group role"]
    E["app_v2\nlogin role"] -- "member of" --> F
    F -- "holds every grant on" --> G["ledger table"]

The privileges that matter are attached once, to rbpki_app. Neither login role is ever granted anything directly. That is what makes a second credential a two-line change instead of a privilege audit, and it is what makes dropping the first credential safe.

Requirements

• Docker 29.x or an equivalent engine, able to pull postgres:17-alpine and create a user-defined bridge network.
• A Linux host with a writable home directory. Everything the lab creates is prefixed rbpki- so it cannot collide with containers, networks or volumes you already run.
• Two terminals are convenient for Task 4, though not required.
• No out-of-band access requirement. This lab does not touch SSH, the firewall, the primary interface, or /etc/fstab.

Scenario

You own a payments service whose database account, app_v1, has held the same password since the service was built. It appears in a secret manager, in one Terraform state file, and in a wiki page nobody will admit to writing. Security has asked for it to be rotated this quarter, and the change review wants to know the expected impact.

Your first instinct is to schedule a five-minute window and change the password. This lab exists because that instinct is wrong in a way that is invisible until you measure it, and because the correct alternative costs about ten extra minutes of planning.

Tasks

Task 1 — Record the starting state

LAB="$HOME/rbpki-lab-23"
rm -rf "$LAB"
mkdir -p "$LAB/creds"
cd "$LAB"

# Record what Cleanup must restore.
docker ps -a --format '{{.Names}}' | sort > "$LAB/state.pre-lab"
docker network ls --format '{{.Name}}' | sort >> "$LAB/state.pre-lab"
wc -l "$LAB/state.pre-lab"

Keep this file. At the end of the lab you will regenerate the same two listings and compare them, which is a stronger statement than “I think I removed everything”.

Task 2 — Start the database and separate privilege from identity

docker network create rbpki-net-23
docker run -d --name rbpki-pg-23 --network rbpki-net-23 \
  -e POSTGRES_PASSWORD=lab-bootstrap-pw -e POSTGRES_DB=appdb \
  postgres:17-alpine

# Wait for the server to accept connections before doing anything else.
for _ in $(seq 1 30); do
  docker exec rbpki-pg-23 pg_isready -U postgres -d appdb && break
  sleep 1
done

Now create the object, the group role that owns the privileges, and the first login role. Read the shape of this carefully: the grants go to rbpki_app, and app_v1 receives nothing except membership.

docker exec -i rbpki-pg-23 psql -U postgres -d appdb <<'SQL'
CREATE ROLE rbpki_app NOLOGIN;
CREATE TABLE ledger (id serial PRIMARY KEY, note text);
INSERT INTO ledger (note) VALUES ('opening balance');
GRANT SELECT, INSERT ON ledger TO rbpki_app;
GRANT USAGE, SELECT ON SEQUENCE ledger_id_seq TO rbpki_app;
CREATE ROLE app_v1 LOGIN PASSWORD 'lab-only-not-real-v1';
GRANT rbpki_app TO app_v1;
SHOW password_encryption;
SQL

Every statement should report success, and the final line tells you which verifier PostgreSQL stores for new passwords. Note that value down: it is the reason a password change is not a metadata edit but a replacement of stored authentication material.

$ docker run --rm --network rbpki-net-23 -e PGPASSWORD=lab-only-not-real-v1 postgres:17-alpine psql -h rbpki-pg-23 -U app_v1 -d appdb -tAc 'SELECT current_user, count(*) FROM ledger'

Task 3 — Start a consumer that reconnects every second

The consumer models the worst realistic case: a short-lived worker that opens a connection, does its work, and exits. It re-reads its credential file on every iteration, so a credential change reaches it without a restart.

cat > "$LAB/creds/app.env" <<'EOF'
PGUSER=app_v1
PGPASSWORD=lab-only-not-real-v1
EOF

cat > "$LAB/consumer.sh" <<'EOF'
#!/bin/sh
# One fresh connection per iteration, credentials re-read every time.
while true; do
  . /creds/app.env
  export PGUSER PGPASSWORD
  TS=$(date -u +%H:%M:%S)
  if OUT=$(psql -h rbpki-pg-23 -d appdb -tAc "SELECT current_user" 2>&1); then
    echo "$TS OK   $OUT"
  else
    echo "$TS FAIL $PGUSER :: $(echo "$OUT" | tr '\n' ' ')"
  fi
  sleep 1
done
EOF
chmod +x "$LAB/consumer.sh"

docker run -d --name rbpki-client-23 --network rbpki-net-23 \
  -v "$LAB/creds:/creds:ro" -v "$LAB/consumer.sh:/consumer.sh:ro" \
  --entrypoint /consumer.sh postgres:17-alpine
sleep 5
docker logs rbpki-client-23

Each log line is a UTC timestamp, then OK or FAIL, then the role name the connection reported. Before you go further, confirm that every line so far says OK and names app_v1. If any line says FAIL, fix that before rotating anything: you cannot measure the damage of a change against a baseline that is already broken.

Task 4 — Break it on purpose: the single-credential rotation

Optionally, open a second terminal first and hold an interactive session open, so you can see the difference between an established connection and a new one:

docker exec -it rbpki-pg-23 psql -U postgres -d appdb -c 'SELECT 1'

Now do the naive rotation and watch the consumer.

$ docker exec rbpki-pg-23 psql -U postgres -d appdb -c "ALTER ROLE app_v1 PASSWORD 'lab-only-not-real-v2';"

sleep 8
docker logs rbpki-client-23 | tail -10
docker logs rbpki-client-23 2>&1 | grep -c FAIL || true

Within a second or two of the statement committing, the consumer’s lines change from OK to FAIL, and the failure text names the role it tried to authenticate as. Copy one complete failure line, exactly as your PostgreSQL 17 printed it, plus the failure count, into the first deliverable:

{
  docker logs rbpki-client-23 2>&1 | grep FAIL | tail -1
  printf 'failures during naive rotation: '
  docker logs rbpki-client-23 2>&1 | grep -c FAIL || true
} > "$LAB/naive-rotation-failure.txt"
cat "$LAB/naive-rotation-failure.txt"

Task 5 — Stop the bleeding, and name what actually failed

docker exec rbpki-pg-23 psql -U postgres -d appdb \
  -c "ALTER ROLE app_v1 PASSWORD 'lab-only-not-real-v1';"
sleep 4
docker logs rbpki-client-23 | tail -3

The consumer recovers on its next iteration, because it never held a connection to lose. That recovery is the trap: rolling the password back looks like a fix, and it is really an admission that the change had no safe path forward. You have now spent an outage to arrive back at the credential you were asked to retire.

Task 6 — Open a dual-credential window

The correct change never edits app_v1. It adds a peer.

docker exec -i rbpki-pg-23 psql -U postgres -d appdb <<'SQL'
CREATE ROLE app_v2 LOGIN PASSWORD 'lab-only-not-real-v2';
GRANT rbpki_app TO app_v2;
SQL

Two statements, no privilege reasoning, no audit of what app_v1 could do. That is entirely because of the decision in Task 2 to put the grants on rbpki_app. Had the grants been attached to app_v1 directly, creating a peer would mean reproducing an unknown set of privileges by hand, and the usual outcome is a peer that is subtly over-privileged or subtly broken.

Task 7 — Prove both credentials are valid at the same time

$ docker run --rm --network rbpki-net-23 -e PGPASSWORD=lab-only-not-real-v1 postgres:17-alpine psql -h rbpki-pg-23 -U app_v1 -d appdb -tAc 'SELECT current_user, count(*) FROM ledger'

$ docker run --rm --network rbpki-net-23 -e PGPASSWORD=lab-only-not-real-v2 postgres:17-alpine psql -h rbpki-pg-23 -U app_v2 -d appdb -tAc 'SELECT current_user, count(*) FROM ledger'

Each command prints the role it connected as and the same row count. Different first field, identical second field: that is the whole proof, and it is the point at which the change becomes reversible. Capture it.

{
  date -u +'%Y-%m-%dT%H:%M:%SZ'
  docker run --rm --network rbpki-net-23 -e PGPASSWORD=lab-only-not-real-v1 \
    postgres:17-alpine psql -h rbpki-pg-23 -U app_v1 -d appdb \
    -tAc 'SELECT current_user, count(*) FROM ledger'
  docker run --rm --network rbpki-net-23 -e PGPASSWORD=lab-only-not-real-v2 \
    postgres:17-alpine psql -h rbpki-pg-23 -U app_v2 -d appdb \
    -tAc 'SELECT current_user, count(*) FROM ledger'
} > "$LAB/dual-credential-proof.txt"
cat "$LAB/dual-credential-proof.txt"

Task 8 — Migrate the consumer and measure the damage

Count the failures before the swap, swap the credential file, then count again. The difference is the number of requests the cutover cost, and the target for that number is zero.

BEFORE=$(docker logs rbpki-client-23 2>&1 | grep -c FAIL || true)
cat > "$LAB/creds/app.env" <<'EOF'
PGUSER=app_v2
PGPASSWORD=lab-only-not-real-v2
EOF
sleep 8
AFTER=$(docker logs rbpki-client-23 2>&1 | grep -c FAIL || true)
echo "failures before=$BEFORE after=$AFTER delta=$((AFTER - BEFORE))"
docker logs rbpki-client-23 | tail -6 > "$LAB/cutover-log.txt"
cat "$LAB/cutover-log.txt"

delta must be 0, and the last log lines must name app_v2. A non-zero delta means the consumer saw the new credential before the database did, which in this lab means Task 6 was skipped or its GRANT failed. In production the equivalent mistake is deploying the application configuration ahead of the database change, and the fix is the same: the credential must exist and be proven before anything is told to use it.

Task 9 — Retire the old credential and prove it is gone

Before dropping anything, ask the database who is still using it. This is the abort gate: if the answer is not zero, the cutover is incomplete and dropping the role converts a clean change into an incident.

$ docker exec rbpki-pg-23 psql -U postgres -d appdb -c "SELECT usename, count(*) AS sessions FROM pg_stat_activity WHERE usename IN ('app_v1','app_v2') GROUP BY usename;"

With no app_v1 sessions listed, close the window.

docker exec -i rbpki-pg-23 psql -U postgres -d appdb <<'SQL'
REVOKE rbpki_app FROM app_v1;
DROP OWNED BY app_v1;
DROP ROLE app_v1;
SQL

DROP OWNED BY is the step people leave out. A role that still holds a default privilege or owns an object cannot be dropped, and the error that PostgreSQL returns describes a dependency rather than naming the fix, which is why half-retired credentials survive for years.

$ docker exec rbpki-pg-23 psql -U postgres -d appdb -tAc "SELECT rolname FROM pg_roles WHERE rolname LIKE 'app\_v%' ORDER BY 1"

Now capture the same reading, alongside a session count for the surviving credential:

{
  date -u +'%Y-%m-%dT%H:%M:%SZ'
  docker exec rbpki-pg-23 psql -U postgres -d appdb -tAc \
    "SELECT rolname FROM pg_roles WHERE rolname LIKE 'app\_v%' ORDER BY 1"
  docker exec rbpki-pg-23 psql -U postgres -d appdb -tAc \
    "SELECT count(*) FROM pg_stat_activity WHERE usename = 'app_v2'"
} > "$LAB/retirement-evidence.txt"
cat "$LAB/retirement-evidence.txt"

The catalogue must list app_v2 and nothing else. Try the old credential once and read the result carefully: PostgreSQL reports an authentication failure rather than telling you the role is missing, because it deliberately declines to reveal which roles exist. That is correct behaviour and it is also why a login attempt is a poor way to confirm a retirement. Record what you actually see, then rely on pg_roles.

Task 10 — Capture the deliverables

cd "$LAB"
cat > rotation-plan.md <<'EOF'
# Dual-credential rotation plan

1. Create the peer credential and grant it group membership.
   Abort if the GRANT fails: do not proceed to stage 2.
2. Prove both credentials serve the same query.
   Abort if either fails: the window is not open.
3. Migrate consumers one at a time, newest first.
   Abort if the failure count rises: roll the config back, not the DB.
4. Confirm zero sessions remain on the old credential.
   Abort if any session remains: wait, do not drop.
5. Revoke, drop owned, drop role, then re-read pg_roles.
   Abort criterion is spent: this stage is not reversible.
EOF
ls -l rotation-plan.md naive-rotation-failure.txt \
      dual-credential-proof.txt cutover-log.txt retirement-evidence.txt

Validation

• docker logs rbpki-client-23 | tail -3 names app_v2 on every line and each line begins OK.
• The delta printed in Task 8 is 0. A value above zero means the cutover was not zero-downtime and the lab is not complete.
• retirement-evidence.txt contains app_v2 and does not contain app_v1.
• naive-rotation-failure.txt contains a failure count greater than zero. If it is zero, the naive rotation in Task 4 did not actually reach the consumer and Task 5 onwards proves nothing.
• The deliverables rotation-plan.md, naive-rotation-failure.txt, dual-credential-proof.txt, cutover-log.txt and retirement-evidence.txt exist and are non-empty.

Expected Outcome

$HOME/rbpki-lab-23/
├── consumer.sh
├── creds/
│   └── app.env
├── cutover-log.txt
├── dual-credential-proof.txt
├── naive-rotation-failure.txt
├── retirement-evidence.txt
├── rotation-plan.md
└── state.pre-lab

You can now state, with evidence rather than confidence, that a password rotation on a shared database account is an outage unless a second credential exists first, and you can show a reviewer the failure count on both sides of the same change.

Troubleshooting

The consumer logs nothing at all. The container exited. Run docker logs rbpki-client-23 and check that consumer.sh is executable on the host and that the bind mount path is absolute; Docker creates a directory where it expects a file if the path is wrong.

Every consumer line says FAIL from the very first one. The database was not ready when the consumer started, or Task 2’s SQL did not run. Re-run the pg_isready loop, confirm app_v1 exists with docker exec rbpki-pg-23 psql -U postgres -d appdb -c '\du', then docker restart rbpki-client-23.

DROP ROLE app_v1 reports that the role cannot be dropped. Some privilege or object still depends on it. Run DROP OWNED BY app_v1; in the same database first, and remember that DROP OWNED BY is per-database: a role used in several databases needs it in each.

The delta in Task 8 is greater than zero. The credential file was edited before app_v2 existed, or the GRANT rbpki_app TO app_v2 statement failed silently in a heredoc that ended early. Re-run Task 6 and repeat Task 7 before retrying the swap.

Cleanup

LAB="$HOME/rbpki-lab-23"

# 1. Stop and forget the containers this lab started.
docker rm -f rbpki-client-23 rbpki-pg-23

# 2. Remove the network this lab created.
docker network rm rbpki-net-23

# 3. Compare the estate against the Task 1 capture.
cat "$LAB/state.pre-lab" > /tmp/rbpki-23-before
{ docker ps -a --format '{{.Names}}' | sort
  docker network ls --format '{{.Name}}' | sort; } > /tmp/rbpki-23-after
diff /tmp/rbpki-23-before /tmp/rbpki-23-after && echo "estate restored"
rm -f /tmp/rbpki-23-before /tmp/rbpki-23-after

# 4. Remove the lab directory, including the credential file.
rm -rf "$LAB"

The lab is cleaned up when the diff in step 3 prints estate restored with no preceding differences, and ls "$HOME/rbpki-lab-23" reports that the directory does not exist. If diff shows lines, one rbpki- object survived; remove it by name and repeat.

Production notes

• The consumer here reconnects every second, which makes the outage instant and obvious. Real services hide it. Before rotating a shared account, find out how long the longest-lived connection in the fleet can be and treat that as the true length of the change window.
• Automating the pattern means an issuer that mints credential n+1 while n is still valid, which is exactly what a dynamic-credential engine does for you. The manual version in this lab is the fallback for the databases your secret manager cannot reach.
• The abort gate in Task 9 is a query, so make it an automated check rather than a human decision. A rotation job that drops a role without first proving zero sessions will eventually drop one that is still in use, at a time of its own choosing.
• Keep the retirement evidence. The question a reviewer asks six months later is never “did you rotate it” but “how do you know the old one no longer works”, and pg_roles answers that in a line.

What You Learned

• A password change is a credential replacement, not an edit. The stored verifier is replaced at commit time, and every subsequent authentication uses the new one. Nothing warns the consumers.
• Established connections are not protection. They defer the failure to the next reconnect, which is why pooled services fail hours after a change window closes rather than during it.
• Privilege on a group role makes rotation cheap. Adding a peer credential becomes two statements with no privilege archaeology, and removing the old one cannot silently remove a grant something else relied on.
• The window is closed by evidence, not by a timer. Zero sessions in pg_stat_activity, then revoke and drop, then a pg_roles reading that shows one credential where there were two.