Using Vault’s Database Secrets Engine for Short‑Lived PostgreSQL Credentials

Use Vault to issue short‑lived PostgreSQL credentials on demand

Instead of distributing a static password that lives forever in configuration files, Vault’s database secrets engine creates a unique PostgreSQL role each time an application asks for credentials. The role is bound to a lease; when the lease expires or is revoked, Vault automatically drops the role. This gives you per‑credential rotation, fine‑grained audit attribution, and eliminates the need to store long‑lived passwords in Vault or operator runbooks.

How it works

Vault treats the database as a trusted backend. A privileged “root” credential (configured once) lets Vault create and drop users. When you define a role, you supply a creation_statements template that Vault fills in with generated values for username, password, and expiration. On a read request (vault read database/creds/), Vault:

1. Generates a random username and password.
2. Renders the creation SQL with those values.
3. Executes the SQL against the database using the root credential.
4. Returns the username, password, and a lease ID to the caller.
5. Tracks the lease; at expiry or revocation it runs the role’s revocation_statements (typically DROP ROLE) to clean up.

Worked configuration example

The following commands assume you are running a Vault dev server (vault server -dev) and have a PostgreSQL instance reachable at :. Replace the placeholders with your actual values. You need a token with sudo or root privileges, or a policy that permits sudo on the database/* paths.

# 1. Enable the database secrets engine at a chosen path
vault secrets enable database

# 2. Configure the connection to PostgreSQL
#    The connection_url uses the format: postgresql://{{username}}:{{password}}@{{host}}:{{port}}/{{database}}
#    Here we supply a privileged root user that Vault will use to create/drop roles.
vault write database/config/my-postgres \
    plugin_name=postgresql-database-plugin \
    connection_url="postgresql://{{username}}:{{password}}@:/mydb?sslmode=disable" \
    username="root" \
    password="" \
    allowed_roles="my-app-role" \
    username_template="{{{{username}}}}"  # optional, defaults to generated

# 3. Define a role that specifies how to create and revoke users
vault write database/roles/my-app-role \
    db_name=my-postgres \
    creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
    revocation_statements="DROP ROLE IF EXISTS \"{{name}}\";" \
    default_ttl="1h" \
    max_ttl="24h"

# 4. Request credentials (application would do this)
vault read -format=json database/creds/my-app-role

The output of the read command includes a lease_id, a generated username, and a password. The username will look something like v-token-myapprole-abc123 and is valid for the lease period.

Limits and common mistakes

• TTL too short for connection pools. If an application holds a database connection longer than the credential’s TTL, the connection will fail mid‑transaction. Applications must either renew the lease before expiry (using the lease ID) or discard and recreate pools after reading new credentials. Vault Agent’s auto_auth or client SDKs can automate renewal.
• Privileged root credential exposure. The connection config requires a database user that can CREATE/DROP roles. Limit this user to only the necessary privileges (e.g., CREATEROLE and DROP ROLE) and rotate it immediately after setup with vault write -force database/rotate-root/my-postgres.
• Failed revocation leaves orphaned users. In PostgreSQL, if the role to be dropped has active sessions, the DROP ROLE command fails and Vault logs an error. Monitor Vault’s audit device or logs for revocation_failed events and manually clean up orphaned roles.
• Creation‑statement templating is engine‑specific. The {{name}}, {{password}}, and {{expiration}} placeholders are understood only by the database secrets engine. Using them in other engines (e.g., KV) will result in literal strings.
• High request rates can stress the DB catalog. Issuing a new role for every request at very high QPS may cause bloat in PostgreSQL’s pg_authid catalog. Choose a TTL that balances security with catalog size, or consider static roles for legacy accounts that cannot be dropped.
• Version‑dependent plugin names and flags. The exact plugin name (postgresql-database-plugin) and CLI flags may differ between Vault releases. Always consult the documentation for the Vault version you are running.

Verification steps (non‑exhaustive)

1. Start a dev Vault server: vault server -dev -dev-root-token-id=root.
2. Export the token: export VAULT_TOKEN=root.
3. Run the configuration commands above, replacing placeholders with a test PostgreSQL container (e.g., from Docker).
4. Read credentials: vault read database/creds/my-app-role. Note the lease ID.
5. Connect to PostgreSQL with the returned username/password and run \du to confirm the role exists and has only the SELECT grant on public schema.
6. Wait for the TTL to expire or run vault lease revoke <lease_id>, then verify the role is gone (\du shows no such role) and that a new connection attempt fails with authentication error.
7. Check the audit log (if enabled) for entries matching the credential request and revocation: vault audit lookup.