---
title: Dynamic Postgres Credentials
path: tutorials/dynamic-postgres-credentials
status: published
---

# Dynamic Postgres Credentials in Production

Replace your service's long-lived `DB_PASSWORD` with short-lived credentials minted on demand. Each service instance gets its own DB user, valid for hours. If a key leaks, the blast radius is one user and one hour, not the whole fleet forever.

By the end:

- A ScaiVault dynamic engine configured for your Postgres.
- A `readonly` role that mints users with the right permissions.
- A reporting service that obtains and revokes credentials per workload.
- Root credentials rotated automatically every 7 days.

## What you need

- A Postgres database with admin access (so you can grant ScaiVault a role that can `CREATE ROLE`).
- ScaiVault token with `dynamic:manage`, `secrets:write`, `admin`.
- Connection details for the DB (hostname, port, database name).

## 1. Create a privileged ScaiVault DB user

Inside Postgres, give ScaiVault a role with just enough power to mint and revoke users:

```sql
CREATE ROLE scaivault_admin WITH LOGIN PASSWORD 'temporary-bootstrap-password' CREATEROLE NOSUPERUSER;
GRANT CONNECT ON DATABASE reporting TO scaivault_admin;
GRANT USAGE ON SCHEMA public TO scaivault_admin;
-- Allow scaivault_admin to grant SELECT on all current and future tables in public
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO scaivault_admin;
```

You'll rotate this temporary password to a strong one inside ScaiVault in a moment.

## 2. Store root credentials in ScaiVault

```bash
curl -X PUT https://scaivault.scailabs.ai/v1/secrets/infra/postgres/reporting/root \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "data": {
      "username": "scaivault_admin",
      "password": "temporary-bootstrap-password"
    },
    "secret_type": "json"
  }'
```

Bind a policy so only the dynamic engine machinery can read this path. Nothing else should ever see the DB root.

```bash
curl -X POST https://scaivault.scailabs.ai/v1/policies \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "dynamic-reporting-db-root",
    "rules": [{
      "path_pattern": "infra/postgres/reporting/root",
      "permissions": ["read", "rotate"]
    }]
  }'
# -> {"id": "pol_db_root", ...}
```

(Binding to the right system identity for the dynamic-engine subsystem depends on your deployment — typically a built-in `system:dynamic-engines` identity. Check with `scaivault auth whoami` while running as the engine to confirm.)

## 3. Configure the dynamic engine

```bash
curl -X POST https://scaivault.scailabs.ai/v1/dynamic/engines \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "reporting-db",
    "type": "database",
    "config": {
      "plugin": "postgresql",
      "connection_url": "postgresql://{{username}}:{{password}}@db.internal:5432/reporting?sslmode=require",
      "root_credentials_path": "infra/postgres/reporting/root"
    },
    "default_ttl": "1h",
    "max_ttl": "8h"
  }'
```

ScaiVault reads the root creds, substitutes them into the URL, and opens a test connection. If you get `400 invalid_config`, the credentials or the URL are wrong — fix and PATCH the engine.

Confirm health:

```bash
curl -H "Authorization: Bearer $TOKEN" \
     https://scaivault.scailabs.ai/v1/dynamic/engines/reporting-db \
  | jq '.connection_status'
# -> "healthy"
```

## 4. Define a role

The role's `creation_statements` run when a lease is generated; `revocation_statements` run when it expires or is explicitly revoked.

```bash
curl -X POST https://scaivault.scailabs.ai/v1/dynamic/engines/reporting-db/roles \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "readonly",
    "creation_statements": [
      "CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '\''{{password}}'\'' VALID UNTIL '\''{{expiration}}'\''",
      "GRANT CONNECT ON DATABASE reporting TO \"{{name}}\"",
      "GRANT USAGE ON SCHEMA public TO \"{{name}}\"",
      "GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\""
    ],
    "revocation_statements": [
      "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM \"{{name}}\"",
      "REVOKE ALL ON SCHEMA public FROM \"{{name}}\"",
      "REVOKE CONNECT ON DATABASE reporting FROM \"{{name}}\"",
      "REASSIGN OWNED BY \"{{name}}\" TO scaivault_admin",
      "DROP OWNED BY \"{{name}}\"",
      "DROP ROLE IF EXISTS \"{{name}}\""
    ],
    "default_ttl": "1h",
    "max_ttl": "8h"
  }'
```

Two non-obvious details in the revocation:

- **`REASSIGN OWNED BY` before `DROP`.** If the temporary user happened to create any objects (sessions, temp tables) it owns them; `DROP ROLE` then fails. `REASSIGN` moves ownership to `scaivault_admin` first.
- **`IF EXISTS` on `DROP ROLE`.** Belt-and-braces — if revocation runs twice for any reason, it shouldn't error.

## 5. Generate credentials from your service

The pattern: borrow a lease, do work, revoke. Don't keep leases around longer than the work needs.

```python
import os, httpx
from contextlib import contextmanager

TOKEN = os.environ["SCAIVAULT_TOKEN"]
BASE  = "https://scaivault.scailabs.ai"

@contextmanager
def dynamic_pg_credentials(ttl="1h"):
    r = httpx.post(
        f"{BASE}/v1/dynamic/engines/reporting-db/creds/readonly",
        headers={"Authorization": f"Bearer {TOKEN}"},
        json={"ttl": ttl, "metadata": {"purpose": "nightly report"}},
    )
    r.raise_for_status()
    lease = r.json()
    try:
        yield lease["data"]
    finally:
        httpx.delete(
            f"{BASE}/v1/dynamic/leases/{lease['lease_id']}",
            headers={"Authorization": f"Bearer {TOKEN}"},
        )

# Usage
with dynamic_pg_credentials(ttl="2h") as creds:
    import psycopg2
    conn = psycopg2.connect(creds["connection_url"])
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) FROM orders")
        print(cur.fetchone())
    conn.close()
```

Every run gets a fresh user. Postgres logs will show `v_readonly_<random>` connections — you can correlate to the lease in ScaiVault audit by username.

## 6. Pre-warm pools (optional)

For services with steady traffic, generating a credential per request is expensive (~50ms for the CREATE ROLE). Two solutions:

**Connection pool with renewal.** Hold one lease for the lifetime of the pool. Renew it before expiry. Use the same credential for many connections.

```python
import asyncio, httpx

class ManagedCredential:
    def __init__(self, ttl="2h", refresh_margin_s=600):
        self.ttl = ttl
        self.refresh_margin_s = refresh_margin_s
        self.lease = None

    async def acquire(self):
        async with httpx.AsyncClient() as c:
            r = await c.post(
                f"{BASE}/v1/dynamic/engines/reporting-db/creds/readonly",
                headers={"Authorization": f"Bearer {TOKEN}"},
                json={"ttl": self.ttl},
            )
            self.lease = r.json()

    async def renew_loop(self):
        while True:
            # Renew when within margin of expiry
            await asyncio.sleep(self._seconds_until_renew())
            async with httpx.AsyncClient() as c:
                r = await c.post(
                    f"{BASE}/v1/dynamic/leases/{self.lease['lease_id']}/renew",
                    headers={"Authorization": f"Bearer {TOKEN}"},
                    json={"increment": self.ttl},
                )
                if r.status_code >= 400:
                    # Past max_ttl — regenerate
                    await self.acquire()

    async def revoke(self):
        async with httpx.AsyncClient() as c:
            await c.delete(
                f"{BASE}/v1/dynamic/leases/{self.lease['lease_id']}",
                headers={"Authorization": f"Bearer {TOKEN}"},
            )
```

**One credential per pod, renewed.** For Kubernetes services, fetch on startup, renew via sidecar, revoke on shutdown.

## 7. Auto-rotate the root credential

The DB root sitting at `infra/postgres/reporting/root` is itself a secret. Put it on a rotation policy so it doesn't sit static forever.

```bash
# Create a rotation policy
curl -X POST https://scaivault.scailabs.ai/v1/rotation/policies \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "db-roots-weekly",
    "interval": "7d",
    "grace_period": "24h",
    "auto_generate": true,
    "secret_policy_id": "sp_db_passwords"
  }'
# -> {"id": "rot_db_root", ...}

# Assign
curl -X POST https://scaivault.scailabs.ai/v1/rotation/policies/rot_db_root/secrets \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"secret_path": "infra/postgres/reporting/root"}'
```

With `auto_generate: true` and a value-generation policy attached, ScaiVault picks a new strong password on rotation. But it can't update Postgres for you — wire a webhook on `secret.rotated` for `infra/postgres/reporting/root` that runs:

```sql
ALTER ROLE scaivault_admin PASSWORD '<new password from ScaiVault>';
```

Two things to coordinate:
1. The dynamic engine refreshes its in-memory credential on the *next* lease generation after the rotation. There's a small window where it might use the old password — minimize this by short-lived caching in the engine (configurable via engine `config.credentials_cache_ttl: 30s`).
2. The webhook ALTER ROLE must run before the grace period closes. 24h is generous; align it with your operations window.

## 8. Observability

`scaivault dynamic leases list --engine reporting-db --json` shows current active leases. If the count grows unbounded, some caller isn't revoking — find the role and prefix:

```bash
scaivault dynamic leases list --engine reporting-db --json \
  | jq -r '.data | group_by(.role) | map({role: .[0].role, count: length})'
```

Audit log:

```bash
scaivault audit query --action dynamic_generate --json \
  | jq -r '.logs[] | "\(.timestamp) \(.identity_id) lease \(.extra_data.lease_id)"'
```

## What you have now

- One DB root credential, stored in ScaiVault, rotated weekly.
- Every workload gets a fresh Postgres user, valid for one hour by default.
- Leases revoke automatically; expired users disappear from the DB.
- Audit log shows exactly who borrowed which credentials for how long.
- A leaked credential is valid for at most one hour and used by exactly one workload.

## What's next

- [Dynamic Secrets guide](../api-guides/dynamic-secrets) — every engine type.
- [Rotation tutorial](./rotate-oauth-credentials) — pattern for non-auto-generated credentials.
- [Cookbook](../api-guides/cookbook) — quick recipes for adjacent tasks.