Issue Short-Lived PostgreSQL Credentials with Vault's Database Secrets Engine

The problem and the outcome

Static PostgreSQL passwords tend to end up in environment files, CI variables, and config repos, and they almost never get rotated. Vault's database secrets engine fixes this by generating a unique PostgreSQL role for each request, with a lease that expires automatically. When the lease ends (or is revoked), Vault drops the role from the database. This guide walks through enabling the engine, configuring a PostgreSQL connection, defining a role, issuing credentials, and verifying that revocation actually works. It assumes Vault 1.x and a recent PostgreSQL; exact parameter names have shifted across releases, so check the docs for your version with vault version before copy‑pasting into production.

Prerequisites

• A running, unsealed Vault server and a token (or other auth) with permission to enable secrets engines and write to database/*.
• A PostgreSQL instance reachable from Vault over the network. Use TLS in production; sslmode=disable is only acceptable for local testing.
• A dedicated PostgreSQL admin account for Vault that can create roles and grant the privileges your dynamic users need. Note that CREATEROLE alone may not be enough to grant membership in roles or privileges on objects the account doesn't own — this varies by PostgreSQL version, so test grants explicitly.
• A Vault policy for clients allowing read on database/creds/<role>.

Enable the engine and configure the connection

Run these with the Vault CLI against your server (set VAULT_ADDR and authenticate first). You need admin‑level privileges on Vault for this section.

vault secrets enable database
vault write database/config/appdb \
  plugin_name=postgresql-database-plugin \
  allowed_roles=\"app-readonly\" \
  connection_url=\"postgresql://{{username}}:{{password}}@db.internal:5432/appdb?sslmode=require\" \
  username=\"vault_admin\" \
  password=\"REPLACE_WITH_ADMIN_PASSWORD\"

The {{username}} and {{password}} placeholders in connection_url are filled from the credentials you pass. Be careful not to leave the admin password in shell history — pass it via a file or environment variable if your shell logs commands, and rotate it after setup (see below).

Define a role

A role maps a name to the SQL Vault runs when issuing and revoking credentials:

vault write database/roles/app-readonly \
  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}}\";\" \
  revocation_statements=\"REASSIGN OWNED BY \"{{name}}\" TO postgres; DROP OWNED BY \"{{name}}\"; DROP ROLE \"{{name}}\";\" \
  default_ttl=\"1h\" \
  max_ttl=\"24h\"

Three details matter here. First, VALID UNTIL '{{expiration}}' gives PostgreSQL a database‑side expiry, so even if Vault's lease data is lost (say, after a storage restore), the credential still dies on schedule. Second, a plain DROP ROLE fails if the role owns objects or holds grants — the REASSIGN OWNED/DROP OWNED cleanup in revocation_statements prevents revocation errors that would leave orphaned roles behind. Third, the TTL choice is the core engineering decision: a 1h/24h pair is a common starting point. Prefer short default TTLs with frequent re‑issuance (or Vault Agent managing renewal) over long max TTLs, since every hour of TTL is an hour of exposure if the credential leaks.

Issue credentials and check the result

As the application identity (a token with the read policy), request credentials:

vault read database/creds/app-readonly

The response includes a lease_id, lease_duration, and a generated username like v-token-app-readonly-... with a random password. Verify end to end — don't assume it worked:

1. Confirm the engine is mounted: vault secrets list -detailed should show database/.
2. Log in with the issued credentials: psql "host=db.internal dbname=appdb user=<issued-user> password=<issued-pass> sslmode=require" and run a SELECT.
3. In psql, run \du and confirm the generated role exists with the expected expiry.
4. Check the lease: vault lease lookup <lease_id> shows the remaining TTL. Renew with vault lease renew <lease_id> and confirm the TTL extends but never exceeds max_ttl.

Revocation and recovery

Revoking a lease runs the role's revocation_statements:

vault lease revoke <lease_id>
vault lease revoke -prefix database/creds/app-readonly   # bulk revoke all leases for the role

After revoking, repeat the psql login — it should now fail, and \du should no longer show the role. If the Vault admin database password was ever exposed, rotate the stored credential without changing it in your own records:

vault write -force database/rotate-root/appdb

Disabling the engine (vault secrets disable database) revokes all its leases at once, which is a blunt but effective emergency option.

Limitations

Dynamic credentials don't help if the application can't handle re‑authentication — connection pools need to support credential refresh or short‑lived connections. Revocation depends on Vault being able to reach PostgreSQL at revoke time; network partitions can delay cleanup, which is another reason the VALID UNTIL backstop matters. Finally, parameter names and plugin behavior are version‑sensitive: validate every statement here against the documentation for your exact Vault and PostgreSQL versions before rolling out.