Platform
ScaiWave ScaiGrid ScaiCore ScaiBot ScaiDrive ScaiKey Models Tools & Services
Solutions
Organisations Developers Internet Service Providers Managed Service Providers AI-in-a-Box
Resources
Support Documentation Blog Downloads
Company
About Research Careers Investment Opportunities Contact
Log in

Dynamic Postgres Credentials

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
1
2
3
4
5
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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
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
1
2
3
4
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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
# 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
1
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
1
2
scaivault dynamic leases list --engine reporting-db --json \
  | jq -r '.data | group_by(.role) | map({role: .[0].role, count: length})'

Audit log:

bash
1
2
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#

Updated 2026-05-17 13:26:51 View source (.md) rev 1