Dynamic Database Credentials with HashiCorp Vault: AppRole Auth and Short-Lived Postgres Secrets

What you'll build

A Docker Compose stack running Vault (dev mode) and Postgres, wired so an application authenticates via AppRole, receives a scoped Vault token, and uses it to fetch short-lived Postgres credentials that Vault creates and revokes on a TTL — no manual rotation, no shared passwords.

Prerequisites

• Docker Desktop 4.x, or Docker Engine + Compose plugin v2 on Linux
• Vault CLI 1.15+ (macOS: brew install hashicorp/tap/vault; other platforms: releases.hashicorp.com)
• psql client for verification (macOS: brew install libpq, then add /opt/homebrew/opt/libpq/bin to your PATH)
• Comfort with basic Vault concepts: tokens, policies, secrets engines

Confirm your CLI before starting:

vault version

1. Start Vault and Postgres

Create docker-compose.yml:

services:
  vault:
    image: hashicorp/vault:1.15
    container_name: vault
    ports:
      - "8200:8200"
    environment:
      VAULT_DEV_ROOT_TOKEN_ID: "root"
      VAULT_DEV_LISTEN_ADDRESS: "0.0.0.0:8200"
    cap_add:
      - IPC_LOCK
    command: server -dev

  postgres:
    image: postgres:16
    container_name: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: vaultadmin
      POSTGRES_PASSWORD: vaultpassword
      POSTGRES_DB: appdb

Dev mode runs in-memory with no persistence and a known root token. Fine for learning the wiring; never for production.

docker compose up -d

Point your CLI at the running server:

export VAULT_ADDR='http://127.0.0.1:8200'
export VAULT_TOKEN='root'

2. Enable and Configure the Database Secrets Engine

vault secrets enable database

Register the Postgres instance. Vault uses {{username}} and {{password}} as template placeholders in the connection URL and manages those root credentials internally.

vault write database/config/appdb \
    plugin_name=postgresql-database-plugin \
    allowed_roles="app-role" \
    connection_url="postgresql://{{username}}:{{password}}@postgres:5432/appdb?sslmode=disable" \
    username="vaultadmin" \
    password="vaultpassword"

The hostname is postgres (the Compose service name) because Vault makes this connection from inside the Docker network. Your host-side psql still connects on localhost:5432.

Create the database role. The creation_statements are raw SQL Vault executes each time credentials are requested:

vault write database/roles/app-role \
    db_name=appdb \
    creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
    default_ttl="1h" \
    max_ttl="24h"

Smoke test with your root token before tightening permissions:

vault read database/creds/app-role

You'll see a generated username like v-root-app-role-... and a password. That Postgres role is live right now and Vault will drop it in one hour.

3. Configure AppRole Auth

AppRole is built for machine-to-machine auth. Your application holds two pieces: a role_id (static, like an identifier) and a secret_id (dynamic, rotatable, expiring). Both are required to get a token.

Enable it:

vault auth enable approle

Write a policy scoped to just the credentials path:

vault policy write app-policy - <<'EOF'
path "database/creds/app-role" {
  capabilities = ["read"]
}
EOF

Create the AppRole role and attach the policy:

vault write auth/approle/role/myapp \
    token_policies="app-policy" \
    token_ttl=1h \
    token_max_ttl=4h \
    secret_id_ttl=24h \
    secret_id_num_uses=10

secret_id_num_uses=10 limits how many times one secret-id can be exchanged for a token — a sensible safeguard if a secret-id is inadvertently exposed.

Fetch the role-id:

vault read auth/approle/role/myapp/role-id

Generate a secret-id:

vault write -f auth/approle/role/myapp/secret-id

Save both values. In production, secret-ids reach your application through your CI/CD pipeline, a secrets manager, or a trusted orchestrator. Never hard-code or commit them.

4. Authenticate as the Application

vault write auth/approle/login \
    role_id="<your-role-id>" \
    secret_id="<your-secret-id>"

Vault returns a client_token bound to app-policy. Export it:

export APP_TOKEN="<client_token>"

Fetch Postgres credentials as the application would:

VAULT_TOKEN="$APP_TOKEN" vault read database/creds/app-role

You get a fresh username and password. When the 1-hour lease expires, Vault drops the Postgres role. No cleanup code, no cron job.

Verify It Works

Connect with the generated credentials:

psql "postgresql://<generated-username>:<generated-password>@localhost:5432/appdb" -c "\conninfo"

Check the expiry inside Postgres:

docker exec -it postgres psql -U vaultadmin -d appdb \
    -c "SELECT usename, valuntil FROM pg_user WHERE usename LIKE 'v-%';"

You'll see the username with valuntil set one hour out.

Now confirm the application token can't escape its policy:

VAULT_TOKEN="$APP_TOKEN" vault secrets list

Expected response: Code: 403. Errors: permission denied. The scope holds.

Troubleshooting

connection refused when writing the database config. Postgres is still initializing. Wait 5-10 seconds after docker compose up -d and retry. The connection comes from inside the Vault container, so check docker compose ps to confirm both services are running.

permission denied when generating credentials. Vault's vaultadmin user needs CREATEROLE in Postgres. The official postgres Docker image creates POSTGRES_USER as a superuser by default, so this shouldn't happen in this setup. Against an existing cluster, run: ALTER USER vaultadmin CREATEROLE;

invalid role id on AppRole login. Copy-paste artifacts are common here. Use vault read -field=role_id auth/approle/role/myapp/role-id to get just the bare value. Same trick for secret-id: vault write -field=secret_id -f auth/approle/role/myapp/secret-id.

Lease renewal fails with lease not found. The max_ttl is a hard ceiling — no renewal extends a lease past it. Increase max_ttl when creating the database role if your application sessions run longer than 24 hours.

Next Steps

• Lease renewal: applications should call vault lease renew <lease_id> before credentials expire rather than fetching a new pair every time.
• Response wrapping: deliver the secret-id via vault write -wrap-ttl=60s -f auth/approle/role/myapp/secret-id so the raw value is never transmitted in plaintext during delivery.
• Kubernetes auth: if your apps run in Kubernetes, the Kubernetes auth method is a better fit than AppRole, using pod service account tokens instead of secret-ids — no secret bootstrapping problem.
• Official docs: Vault PostgreSQL database secrets and AppRole auth method cover rotation policies, connection pooling caveats, and advanced role configuration.