Vault dynamic database credentials in Python

Vault’s database secrets engine issues a brand-new database user on every request, valid only for its lease, then deletes it. There is no static password to leak. This page consumes those credentials from Python, extending the HashiCorp Vault Python SDK guide.

The complication that makes this more than an API call is the connection pool. A pooled connection is authenticated once, when it is opened, and then reused for hours — so a pool built with a credential whose lease has since expired holds connections belonging to a database user that no longer exists. Whether those connections keep working, fail immediately, or fail on the next statement depends on the database, and none of the three is a good thing to discover during an incident.

The pool outlives the credential that created it Connections are authenticated when opened and then reused, so a pool built with an expiring credential holds sessions for a database user Vault has since deleted. authentication happens once per connection, not per query lease issued · user created pool opens connections as this user lease ends · user dropped pooled connections persist across the whole span — never re-authenticated rebuild here (70% of TTL) → no interruption rebuild here → errors on live traffic The engine must be replaced before the lease ends, not when the first query fails.
The credential has a lifetime and the pool does not — reconciling the two is the whole design problem.

Problem 1: a static DB password in config

# ANTI-PATTERN: one long-lived password, unbounded blast radius
DATABASE_URL = "postgresql://app:staticPassword@db/app"   # leaks forever

A static credential is valid until someone notices and rotates it. The practical consequence is that rotation becomes an event rather than a routine: it requires coordinating a password change with a redeploy of every service using it, so it happens rarely, which in turn means the credential in circulation is usually old, widely known, and present in more places than anyone can enumerate.

There is a second cost that matters during an investigation. Every service and every person using the shared credential appears in the database’s logs as the same user, so “who ran this query?” has no answer beyond “something with the app password”. Dynamic credentials give each consumer a distinct, timestamped username, which turns that question into a lookup against the lease log rather than a round of guesswork.

Problem 2: ignoring the lease

# ANTI-PATTERN: uses dynamic creds but never renews the lease
creds = client.secrets.database.generate_credentials(name="app")
# ... the lease expires and the database user is deleted mid-connection

Dynamic credentials are temporary; the connection must be rebuilt before the lease ends. This is the failure that makes teams conclude dynamic credentials are impractical, and it is entirely a client-side omission — the engine did exactly what it promised, and the application treated a lease like a password.

The symptom is characteristic: the service runs correctly for exactly the lease duration after each deployment and then throws authentication errors on every query at once, since every pooled connection belongs to the same deleted user. Because a redeploy fixes it instantly, the natural response is to redeploy and move on, which resets the clock and hides the cause for another lease period.

Static password versus dynamic credential in the database log A shared static user makes every connection indistinguishable, while dynamic credentials give each consumer a distinct timestamped username. shared static user app · app · app · app · app every service, every person "who ran this?" — unanswerable rotation needs a coordinated deploy dynamic users v-approle-app-x7f2 · …-9k1p one per lease, timestamped attributable in the database log rotation is the default behaviour
Attribution is the underrated benefit: each lease produces a distinct username the database records.

Secure implementation

# db/vault_creds.py
import time
import hvac
from pydantic import SecretStr
from sqlalchemy import create_engine

class VaultDB:
    def __init__(self, client: hvac.Client, role: str, host: str, db: str):
        self._c, self._role, self._host, self._db = client, role, host, db
        self._engine = None
        self._renew_at = 0.0

    def engine(self):
        if self._engine is None or time.monotonic() > self._renew_at:
            lease = self._c.secrets.database.generate_credentials(name=self._role)
            d, ttl = lease["data"], lease["lease_duration"]
            pw = SecretStr(d["password"]).get_secret_value()
            self._engine = create_engine(
                f"postgresql://{d['username']}:{pw}@{self._host}/{self._db}",
                pool_pre_ping=True,
            )
            self._renew_at = time.monotonic() + ttl * 0.7   # rebuild before expiry
        return self._engine

A fresh credential is generated and the engine rebuilt at 70% of the lease TTL, so connections always use a live, short-lived user. The password is handled as SecretStr.

pool_pre_ping=True is doing quiet but essential work. It makes SQLAlchemy test each pooled connection before handing it out, discarding any that the server has closed — which is exactly what happens to connections belonging to a dropped user. Without it, a stale connection is handed to application code and fails on the query, turning a recoverable pool problem into a user-visible error.

The old engine is replaced rather than closed, which is deliberate. Calling dispose() immediately would sever connections that are still executing queries; letting the old engine fall out of reference means its pool drains naturally as those queries finish and Python collects it. If you want the resources released promptly, keep a reference and dispose it after a delay comfortably longer than the slowest expected query.

There is one race this design does not eliminate: a request that grabs a connection just before the rebuild point and runs a slow query past the lease expiry. It is rare and it is real, so the layer above should catch an authentication error, ask for a fresh engine, and retry once. That is the same “retry exactly once” rule that applies to every lease-based credential — a second failure means something other than expiry is wrong.

Engine rebuild timeline within one lease A new engine is created at seventy percent of the lease duration while the old pool drains, leaving a margin before the database user is dropped. rebuild at 70% leaves 30% of margin engine A serving traffic pool A drains engine B serving 70% of lease_duration lease ends · user dropped A slow query started just before the rebuild can still outlive the lease — hence retry once.
The margin absorbs ordinary queries; the retry-once rule covers the slow query that outlives its lease anyway.

The database role is where least privilege lives

The Python side controls lifetime; the Vault database role controls power. The role holds the SQL statements Vault runs to create each user, and whatever those statements grant is what a leaked dynamic credential can do for the length of its lease.

# a read-mostly role: the creation statements are the privilege boundary
vault write database/roles/reports \
    db_name=appdb \
    default_ttl=1h \
    max_ttl=4h \
    creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
                         GRANT USAGE ON SCHEMA public TO \"{{name}}\"; \
                         GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";"

Two details in those statements matter beyond the grants. VALID UNTIL '' makes the database itself enforce the expiry, so even if Vault is unreachable when the lease ends and the revocation never runs, the credential stops working on schedule — a second, independent expiry mechanism. And granting only SELECT means this role produces credentials that cannot write, regardless of what the application code attempts.

Splitting roles by access pattern is where the model earns most. A reporting job gets reports; the API gets a role with INSERT, UPDATE, and DELETE on the tables it owns; a migration job gets a role with DDL rights and a very short TTL. Each service requests its own role, so the credential a compromised reporting job holds cannot modify anything, and the credential the API holds cannot drop tables.

The failure mode to watch is grant drift on new tables. GRANT SELECT ON ALL TABLES applies to tables that exist when the statement runs, not to tables created later — so a migration that adds a table leaves the reporting role unable to read it, in a way that appears as an application bug. ALTER DEFAULT PRIVILEGES on the owning role fixes this properly, and it is worth setting up once rather than rediscovering it after each schema change. The tell is a reporting query that fails with a permission error on exactly one table — the newest one — while everything else continues to work normally.

One Vault database role per access pattern A reporting role grants only SELECT, an API role grants row-level writes, and a migration role grants DDL with a very short TTL. the creation statements are the privilege boundary reports SELECT only default_ttl 1h a leak cannot write api SELECT, INSERT, UPDATE own tables only a leak cannot drop migrate DDL rights default_ttl 5m powerful, but brief
Power and lifetime are set together: the more a role can do, the shorter its credentials should live.

Rebuilding without dropping requests

The engine factory above swaps engines at a point in time, which is correct but leaves one question open: what should a request do if it arrives during the swap, or if it holds a connection whose user has just been deleted? Answering it explicitly is what makes the difference between a service that degrades gracefully and one that returns a burst of 500s once per lease.

# db/session.py — one retry on an authentication failure
from sqlalchemy.exc import OperationalError

def run(vault_db: VaultDB, statement, params=None):
    for attempt in (1, 2):
        engine = vault_db.engine()
        try:
            with engine.connect() as conn:
                return conn.execute(statement, params or {})
        except OperationalError as exc:
            if attempt == 2 or not _is_auth_failure(exc):
                raise
            vault_db.invalidate()      # force a fresh lease on the next engine() call

def _is_auth_failure(exc: OperationalError) -> bool:
    # Postgres: 28P01 invalid_password, 28000 invalid_authorization_specification
    return getattr(exc.orig, "sqlstate", None) in {"28P01", "28000"}

The _is_auth_failure check is what keeps this from being a blanket retry. Retrying every OperationalError would silently repeat writes after a network blip, which is a correctness problem rather than a resilience feature; narrowing to the two authentication SQLSTATEs means the retry only fires for the one condition it was written for. On MySQL the equivalent is error 1045, and it is worth writing the check against the driver’s numeric code rather than matching on the message text, which is localised and version-dependent.

invalidate() is a small addition to VaultDB — set self._renew_at = 0.0 so the next engine() call fetches a fresh lease. Splitting it out means the retry path and the scheduled rebuild path share one implementation, so a fix to either applies to both.

Observability matters more here than usual, because a working retry is invisible. Counting how often the retry fires tells you whether the rebuild fraction is right: near-zero is healthy, a steady trickle means 70% is too late for your query durations, and a spike means something other than expiry is failing authentication. That single counter is worth more than any amount of guessing about the correct TTL.

Narrow retry on authentication failure only An authentication SQLSTATE triggers a credential refresh and a single retry, while any other operational error propagates immediately. OperationalError inspect sqlstate 28P01 / 28000 refresh lease, retry once anything else raise — never blind-retry a write count the retries steady trickle → rebuild too late spike → not an expiry problem
Retry the one condition you wrote it for, and count how often it fires — that counter tells you if the TTL fraction is right.

Gotchas & version-specific behaviour

  • Read lease_duration from the response; do not hard-code the TTL.
  • Rebuild the engine before the lease ends (e.g. at 70% TTL) — an expired user disappears from the database.
  • pool_pre_ping=True drops connections whose user was revoked.
  • The Vault DB role defines the SQL grants — scope it to least privilege.
  • Postgres refuses to drop a role that still owns objects, so a dynamic user that creates a table blocks its own revocation; keep DDL out of long-lived roles.
  • Every lease consumes a database connection slot until it is revoked; a short TTL with many services can exhaust max_connections faster than expected.

The connection-slot point is worth sizing before it bites. Each live lease implies a user that may hold pooled connections, so the peak is roughly the number of service instances multiplied by their pool size, plus whatever the previous generation of credentials still holds while draining. Overlapping generations during a rebuild means budgeting for two pools per instance for a short window, and a fleet that scales out under load can double that again at exactly the wrong moment.

Two adjustments keep that under control. Lower the per-instance pool size, since dynamic credentials make a large pool per instance less useful than it looks — connections are cheap to re-establish and expensive to hold across a rebuild. And put a connection pooler such as PgBouncer in front of the database if the fleet is large, accepting that a pooler in transaction mode changes what session state you can rely on. Either way, size the database’s max_connections against the peak with two overlapping generations rather than the steady state.

Production parity checklist

  • The app uses dynamic credentials, not a static DB password.
  • The engine is rebuilt before the lease expires.
  • The connection pool drops revoked connections.
  • The Vault database role is least-privilege.
  • Passwords are SecretStr-wrapped and never logged.
  • VALID UNTIL '' is present so the database enforces expiry independently.

The last item is the cheapest resilience in this whole design. Vault revokes credentials by connecting to the database and dropping the user, which requires Vault to be running and able to reach the database at the moment the lease ends. VALID UNTIL moves the guarantee into the database itself, so a Vault outage during a lease expiry cannot leave a live credential behind. It costs one clause in the creation statement.

Rolling this out to an existing service does not have to be a single risky change. Point one low-traffic consumer — a reporting job, a scheduled task — at a dynamic role first, with a generous TTL, and watch the retry counter and the database’s connection count for a few days. Shortening the TTL afterwards is a one-line change to the role, and by then you have real numbers for how the pool behaves across a rebuild rather than an estimate.

Key takeaways

Vault’s database engine plus a lease-aware engine factory means every database connection uses a short-lived user that cannot outlive its lease. Two properties carry the design: rebuild the engine on a fraction of the lease rather than at expiry, so the pool is always backed by a live user, and put least privilege in the role’s creation statements, so a leaked credential is limited in power as well as in time. Add VALID UNTIL so the database enforces the deadline independently of Vault’s availability, and retry once on an authentication error to cover the slow query that outlives its lease. Authenticate the client first with Vault AppRole Auth in Python.

Back to HashiCorp Vault Python SDK