Skip to content
Hands-on IT IT Drills

Day 4: Dynamic Database Secrets

Goal: Create, renew, revoke and rotate Postgres credentials on demand · Duration: approx. 90 min · Prerequisite: Day 3

Briefing

  • The database engine creates a separate DB user with an expiration date for each request.
  • Every user is tied to a lease: if it expires or is revoked, OpenBao deletes the user.
  • Static roles manage existing users and only rotate their password.
  • Afterwards, only OpenBao knows the admin password (rotate-root).
App --read creds--> OpenBao --CREATE ROLE ... VALID UNTIL--> Postgres
          <-- user/pass + lease_id --
Lease expired/revoked --DROP ROLE--> Postgres

Drill 1: Start the Lab

Terminal 1:

bao server -dev -dev-root-token-id=root

Terminal 2:

mkdir -p ~/bao-lab/tag04 && cd ~/bao-lab/tag04
export BAO_ADDR=http://127.0.0.1:8200 BAO_TOKEN=root

# Lab password, only for this lab
docker run -d --name bao-pg -e POSTGRES_PASSWORD=lab-pg-pw \
  -p 5432:5432 postgres:16
until docker exec bao-pg pg_isready -U postgres; do sleep 1; done

Expected (abridged, may differ):

/var/run/postgresql:5432 - accepting connections

Create test data:

docker exec -i bao-pg psql -U postgres <<'SQL'
CREATE TABLE products (id serial PRIMARY KEY, name text, price numeric);
INSERT INTO products (name, price) VALUES ('Tasse', 9.90), ('Shirt', 19.90);
SQL

Expected:

CREATE TABLE
INSERT 0 2

Tip: docker exec ... psql without -h uses the local socket – in the image without a password (trust). That remains your emergency access even after rotate-root.

For real password logins you need TCP via the container IP; loopback inside the container is also trust and would accept any password:

PG_IP=$(docker inspect bao-pg \
  -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}')
echo "$PG_IP"

Expected (may differ):

172.17.0.2

Save your progress? Sign up for free

Drill 2: Database Engine and Connection

bao secrets enable database

PG_URL='postgresql://{{username}}:{{password}}@127.0.0.1:5432/postgres'

bao write database/config/webshop-db \
  plugin_name=postgresql-database-plugin \
  connection_url="$PG_URL?sslmode=disable" \
  username=postgres \
  password=lab-pg-pw \
  allowed_roles="webshop-ro,webshop-app" \
  password_authentication=scram-sha-256

Expected:

Success! Enabled the database secrets engine at: database/
Success! Data written to: database/config/webshop-db

Why? {{username}}/{{password}} are placeholders that OpenBao fills in itself; that way the password never ends up in plain text in the URL. password_authentication=scram-sha-256 makes OpenBao send passwords to Postgres as a SCRAM hash instead of in plain text.

bao read database/config/webshop-db

Expected (abridged):

allowed_roles         [webshop-ro webshop-app]
connection_details    map[connection_url:postgresql://{{username}}:{{password}}@...
                      password_authentication:scram-sha-256 username:postgres]
plugin_name           postgresql-database-plugin

The password is never returned.

Save your progress? Sign up for free

Drill 3: Rotate the Root Password

bao write -f database/rotate-root/webshop-db
docker exec -e PGPASSWORD=lab-pg-pw bao-pg \
  psql -h "$PG_IP" -U postgres -c 'select 1'

Expected (abridged, may differ):

Success! Data written to: database/rotate-root/webshop-db
psql: error: ... FATAL:  password authentication failed for user "postgres"

Warning: From now on nobody except OpenBao knows the password of postgres. In production, never do this with the superuser that people need – create a dedicated admin user for OpenBao.

Save your progress? Sign up for free

Drill 4: Dynamic Role webshop-ro

cat > webshop-ro.sql <<'EOF'
CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";
EOF

bao write database/roles/webshop-ro \
  db_name=webshop-db \
  [email protected] \
  default_ttl=1h \
  max_ttl=24h

Expected:

Success! Data written to: database/roles/webshop-ro
bao read database/creds/webshop-ro

Expected (values vary):

Key                Value
---                -----
lease_id           database/creds/webshop-ro/h1jlcn7wLQwBMWxYp0L7N5X0
lease_duration     1h
lease_renewable    true
password           wIKwnNIajk5-V1EZlAAK
username           v-token-webshop--1UcSq5kzQEKAVeQsajVI-1791442902

Why? The username encodes the auth method (token), role and timestamp – in the DB log you see immediately where a user comes from.

Save your progress? Sign up for free

Drill 5: Check in Postgres

bao read -format=json database/creds/webshop-ro > creds.json
U=$(jq -r .data.username creds.json)
P=$(jq -r .data.password creds.json)
L=$(jq -r .lease_id creds.json)

docker exec bao-pg psql -U postgres -c '\du'
docker exec -e PGPASSWORD="$P" bao-pg \
  psql -h "$PG_IP" -U "$U" -d postgres -c 'SELECT * FROM products;'
docker exec -e PGPASSWORD="$P" bao-pg \
  psql -h "$PG_IP" -U "$U" -d postgres -c "INSERT INTO products (name) VALUES ('x');"

Expected (abridged, may differ):

 Role name                                        | Attributes
--------------------------------------------------+---------------------------------
 postgres                                         | Superuser, Create role, ...
 v-token-webshop--1UcSq5kzQEKAVeQsajVI-1791442902 | Password valid until 2026-...
 v-token-webshop--twbUGop2PLWgGtbGKUjA-1791442908 | Password valid until 2026-...

 id | name  | price
----+-------+-------
  1 | Tasse |  9.90
  2 | Shirt | 19.90

ERROR:  permission denied for table products

Save your progress? Sign up for free

Drill 6: Lease lookup, renew, revoke

bao lease lookup "$L"
bao lease renew -increment=2h "$L"
docker exec bao-pg psql -U postgres -c '\du' | grep "$U"

Expected (abridged):

expire_time     2026-10-08T10:01:48.243427+02:00
id              database/creds/webshop-ro/0F6WHISLDv1yBkAGlMFGXLPE
path            database/creds/webshop-ro
renewable       true
ttl             1h
...
lease_duration     2h
lease_renewable    true
 v-token-webshop--... | Password valid until 2026-10-08 12:01:53+02

Why? Renew also moves VALID UNTIL in Postgres. It cannot go beyond max_ttl – OpenBao caps it and warns (TTL ... exceeded the effective max_ttl).

bao lease revoke "$L"
sleep 1
docker exec -e PGPASSWORD="$P" bao-pg \
  psql -h "$PG_IP" -U "$U" -d postgres -c 'select 1'

Expected (abridged, may differ):

All revocation operations queued successfully!
psql: error: ... FATAL:  password authentication failed for user "v-token-webshop--..."

Save your progress? Sign up for free

Drill 7: Emergency Brake revoke -prefix

Scenario: credentials of a role have leaked. Remove all of them at once:

for i in 1 2 3; do bao read -field=username database/creds/webshop-ro; echo; done
bao list sys/leases/lookup/database/creds/webshop-ro

bao lease revoke -prefix database/creds/webshop-ro
sleep 1
bao list sys/leases/lookup/database/creds/webshop-ro
docker exec bao-pg psql -U postgres -c '\du' | grep -c v-token

Expected:

Keys
----
h1jlcn7wLQwBMWxYp0L7N5X0
p3zTCbvMLx0xzcUFrz0Rxkb4
vMMyLKRreID7Gey0e0nz9lIE
wpvHTl6qWECIhG1qXT77CvtG
All revocation operations queued successfully!
No value found at sys/leases/lookup/database/creds/webshop-ro
0

Save your progress? Sign up for free

Drill 8: Static Role for an Existing Service User

The legacy user webshop_app already exists and must not be recreated – only its password is rotated.

docker exec bao-pg psql -U postgres \
  -c "CREATE ROLE webshop_app WITH LOGIN PASSWORD 'lab-alt-pw';" \
  -c "GRANT SELECT, INSERT ON products TO webshop_app;"

bao write database/static-roles/webshop-app \
  db_name=webshop-db \
  username=webshop_app \
  rotation_period=24h

bao read database/static-creds/webshop-app

Expected (values vary):

Success! Data written to: database/static-roles/webshop-app
Key                    Value
---                    -----
last_vault_rotation    2026-10-08T09:02:04.617182+02:00
password               rQAquKpI-H57-ptZpZuC
rotation_period        24h
ttl                    24h
username               webshop_app

Why? OpenBao rotates immediately on creation – lab-alt-pw is invalid afterwards. Static creds have no lease, but a ttl until the next rotation.

Rotate manually and check:

bao write -f database/rotate-role/webshop-app
P2=$(bao read -field=password database/static-creds/webshop-app)
docker exec -e PGPASSWORD="$P2" bao-pg \
  psql -h "$PG_IP" -U webshop_app -d postgres -tAc 'select current_user'

Expected:

Success! Data written to: database/rotate-role/webshop-app
webshop_app

Save your progress? Sign up for free

Drill 9: Policy for the App

bao policy write webshop-db - <<'EOF'
path "database/creds/webshop-ro" {
  capabilities = ["read"]
}
path "database/static-creds/webshop-app" {
  capabilities = ["read"]
}
EOF

T=$(bao token create -policy=webshop-db -field=token)
BAO_TOKEN=$T bao read -field=username database/creds/webshop-ro; echo
BAO_TOKEN=$T bao read database/config/webshop-db
BAO_TOKEN=$T bao write -f database/rotate-root/webshop-db

Expected (abridged):

Success! Uploaded policy: webshop-db
v-token-webshop--X8llUY9OAO9ZoRAU0dYL-1791442931
...
    * permission denied
...
    * permission denied

Tip: Attach webshop-db to the AppRole webshop from Day 3 (token_policies); then the usernames start with v-approle-.

Save your progress? Sign up for free

Checkpoint

#!/usr/bin/env bash
check() { if eval "$2" >/dev/null 2>&1; then echo "✔ $1"; else echo "✘ $1"; fi; }

check "Database Engine gemountet" "bao secrets list | grep -q '^database/'"
check "Config webshop-db lesbar" "bao read database/config/webshop-db"
check "Rolle webshop-ro existiert" "bao read database/roles/webshop-ro"
check "Static Role webshop-app existiert" "bao read database/static-roles/webshop-app"
U=$(bao read -field=username database/creds/webshop-ro)
check "Dynamischer User in Postgres" \
  "docker exec bao-pg psql -U postgres -tAc '\du' | grep -q '$U'"
check "Policy webshop-db existiert" "bao policy read webshop-db"
bao lease revoke -prefix database/creds/webshop-ro >/dev/null

Challenge

Create a role webshop-short with default_ttl=1m and max_ttl=2m, generate credentials and try to renew them to 1 h. Watch when the user disappears from \du.

Hint: The role first has to be listed in the connection’s allowed_roles.

Solution

bao write database/config/webshop-db \
  allowed_roles="webshop-ro,webshop-app,webshop-short"
bao write database/roles/webshop-short db_name=webshop-db \
  [email protected] default_ttl=1m max_ttl=2m

L=$(bao read -format=json database/creds/webshop-short | jq -r .lease_id)
bao lease renew -increment=1h "$L"
sleep 125
docker exec bao-pg psql -U postgres -c '\du' | grep -c v-token

Expected (abridged):

WARNING! The following warnings were returned from OpenBao:
  * TTL of "1h" exceeded the effective max_ttl of "1m59s"; TTL value is capped
  accordingly
lease_duration     1m59s
0

An update to database/config/... changes only the fields you pass; the rotated root password is preserved.

Save your progress? Sign up for free

Enterprise Note

  • A dedicated DB admin for OpenBao (CREATEROLE, minimal privileges) instead of postgres; rotate-root right after setup.
  • TLS to the database (sslmode=verify-full) instead of sslmode=disable.
  • Set revocation_statements explicitly (terminate sessions, reassign objects), otherwise DROP ROLE fails when dependencies exist.
  • Short TTLs plus lease renewal in the client (OpenBao Agent, Day 11); monitor the lease count (Day 12).
  • Static roles for legacy apps only with a plan for how the app loads the new password without downtime.