Skip to content
Visory LLC

Postgres Setup

The dashboard connects to a PostgreSQL Flexible Server using Azure AD token authentication (username = identity name, password = a short-lived access token — never a static password). There are two authentication modes, mirroring the pattern already used by orchestration’s test_postgres_local DAG.

Two authentication modes

Section titled “Two authentication modes”
Mode When Identity PostgreSQL user Token source
Developer Local Docker Your personal Azure AD account your.email@domain.com AZURE_PG_TOKEN (via direnv)
Managed Identity Production Container App visory-dev-scus-portal-uami AZURE_USERNAME (UAMI name) Azure SDK (ManagedIdentityCredential)

This avoids needing a service-principal PostgreSQL role for every developer — your own Azure account, once granted access, works locally with zero extra setup.

How mode selection works

Section titled “How mode selection works”

apps/web/src/lib/db/pgconnection.ts uses the same lazy credential selection as keyVault.ts:

  • Local: Without AZURE_CLIENT_ID, use the developer’s Azure CLI identity.
  • Production: With AZURE_CLIENT_ID, use the attached UAMI.
  • Both modes: Use AZURE_USERNAME as the PostgreSQL role name and obtain a short-lived token.

Local setup

Section titled “Local setup”
  1. Ensure the root .envrc loads the postgres hook in addition to azure (fetches AZURE_USERNAME + AZURE_PG_TOKEN, refreshed every 24h):
    Terminal window
    eval "$(python3 "$DEVBOX_PROJECT_ROOT/scripts/environ/export_settings.py" \
      --cache .cache/env_cache azure postgres)"
  2. Reload: direnv reload
  3. Verify that both variables are set without printing their values: [ -n "$AZURE_USERNAME" ] && [ -n "$AZURE_PG_TOKEN" ].
  4. Start the app: cd apps/mx_fuels_reporting_app && make js
  5. Confirm the connection mode in logs:
    Terminal window
    docker logs mx-fuels-web | grep "PG pool"
    Expect a log entry with mode: developer (Azure CLI).
  6. Load http://localhost:3000/balance — it should query v_conservation_of_mass successfully.

Common errors

Section titled “Common errors”
Symptom Fix
AZURE_USERNAME is not set rm -f .cache/env_cache && direnv reload
AZURE_PG_TOKEN is not set Confirm .envrc includes the postgres hook, then direnv reload
pg_hba.conf rejects connection (28000) Your Azure account has no PostgreSQL role yet — ask an admin to create one (see RBAC & Identity)

Read-only access model

Section titled “Read-only access model”

The dashboard’s PostgreSQL role(s) are read-only by design — SELECT on specific views only, with INSERT/UPDATE/DELETE/TRUNCATE/CREATE explicitly revoked. Currently only one route uses this: /api/balance (SELECT ... FROM v_conservation_of_mass). See RBAC & Identity for the exact grant/revoke SQL and how to verify or reset the read-only posture.