pgwarden

A deterministic, policy‑enforcing PostgreSQL access proxy with an optional control plane and signal plane. pgwarden enforces least‑privilege access at the database boundary using Postgres roles, schemas, and views—without SQL parsing or query rewriting.

This repository contains production‑ready Go services, a reference OpenTelemetry Collector config, Helm examples, and an end‑to‑end Compose test harness.

What pgwarden does

• Terminates inbound Postgres connections and enforces TLS/mTLS on ingress
• Maps each inbound DSN to a policy context (role/schema/view set)
• Connects upstream with short‑lived leases issued by the control plane
• Enforces policy through roles, grants, and view‑level masking
• Emits structured audit events via OTLP logs (no SQL/PII)
• Optionally forwards filtered signals to WardenSense

Components

Data Plane (Proxy)

• PostgreSQL wire‑protocol proxy (no SQL parsing or rewriting)
• Inbound auth termination (password or none)
• Upstream connections pooled per DSN
• Lease refresh and LKG (last‑known‑good) config cache
• Emits audit events to OTLP when enabled

Control Plane

• REST API for targets, DSNs, policies, deployments, leases, proxies, events
• Policy compilation to Postgres roles + views + grants
• Applies policies idempotently to target databases
• Stores state in Postgres (targets, DSNs, policies, leases, events, deployments)

WardenSense (Optional)

• Separate service + DB
• Ingests audit events (HTTP + gRPC)
• Persists alerts and exposes status/alert APIs
• Observational only; never gates access

Quickstart (local)

1) Run unit tests

go test ./...

2) End‑to‑end Compose test harness

This brings up Postgres services, the OTEL collector, runs all unit tests, and then integration tests.

./scripts/compose-e2e.sh

Notes:

• The harness requires OpenSSL (available in the Nix dev shell). Run:

  nix develop
  

  then run the script.
• The harness enforces mTLS for the control‑plane DB connection.

Dev stack (Docker + frontend)

For local UI + API testing, use the dev compose stack:

./scripts/dev-up.sh

This brings up:

• Control plane: http://localhost:8080
• Frontend (nginx): http://localhost:8081
• Proxy (TLS required): localhost:5432

Stop and wipe volumes:

./scripts/dev-down.sh

Notes:

• scripts/dev-up.sh seeds a sample target/DSN for UI testing.
• Proxy TLS certs are generated into /tmp/pgwarden-proxy-certs.
• If auth is enabled, seeding is skipped unless a valid token is provided.

The frontend source lives in frontend/ (see frontend/README.md).

Architecture

[Clients] -> [pgwarden-proxy] -> [Upstream Postgres]
                  |
                  v
          [Control Plane]
                  |
              [Postgres]

[Proxy + Control Plane] --OTLP Logs--> [OpenTelemetry Collector]
                                      |-> Control Plane /v1/events/ingest
                                      |-> WardenSense (filtered)

Policy model

Policies are declarative and compiled into Postgres artifacts:

• Roles scoped to pgwarden
• View schemas containing curated views
• Grants for read/write access per DSN
• Optional column masking in view definitions

Policy deploys perform a full reconcile for a DSN by dropping and recreating pgwarden-managed view schemas with prefix pgw_<dsn>_.

Masking strategies (v1):

• partial_reveal: mask all but last N characters
• partial_reveal_start: reveal first N characters, mask the rest
• full_mask: mask the entire value by length
• default_value: static placeholder

Configuration

Configuration is YAML (same schema used across Docker and Kubernetes). Example:

proxy:
  listen_addr: "0.0.0.0:5432"
  tls:
    mode: "required"  # required | mtls | disabled
    cert_file: "/etc/pgwarden/tls/server.crt"
    key_file: "/etc/pgwarden/tls/server.key"
  control_plane:
    base_url: "http://control-plane:8080"
    poll_interval: 5s

  pooling_defaults:
    max_upstream_conns: 20
    min_idle_upstream_conns: 2
    idle_upstream_timeout: 5m
    max_conn_lifetime: 30m

  lease_defaults:
    ttl: 30m
    refresh_window: 5m
    refresh_jitter: 0.2
    reseed_on_refresh: true
    drain_old_conns: true

control_plane:
  http_listen_addr: "0.0.0.0:8080"
  auth:
    mode: "disabled"  # disabled | oidc | mtls
  db:
    dsn: "postgres://pgwarden@db:5432/pgwarden?sslmode=verify-ca&sslcert=/etc/pgwarden/client.crt&sslkey=/etc/pgwarden/client.key&sslrootcert=/etc/pgwarden/ca.crt"
  wardensense:
    enabled: false
    base_url: "http://pgwarden-wardensense:8090"
    grpc_addr: "pgwarden-wardensense:9095"
    auth_mode: "disabled" # disabled | token | mtls
    auth_token: ""
    mtls:
      cert_file: ""
      key_file: ""
      ca_file: ""
    timeout: 5s

targets:
  - id: "prod"
    name: "prod"
    upstream:
      host: "prod-db"
      port: 5432
      dbname: "app"
      username: "app_user"
      password: "app_pass"
    dsns:
      - name: "app_ro"
        mapped_role: "pgw_app_ro"
        wardensense_enabled: false
        inbound_auth:
          mode: "password"
          users:
            - username: "app"
              password_hash: "$2a$10$..."

signals:
  enabled: true
  audit:
    sink: "otlp"

Dev note: OTLP logs are collected via the included collector in docker-compose.dev.yaml. The collector forwards OTLP logs to /v1/events/ingest/otlp (control plane) and filters wardensense_enabled=true logs to /v1/wardensense/ingest/otlp.

Dev note: WardenSense alerting is tuned aggressively in docker-compose.dev.yaml (PGWARDEN_WARDENSENSE_WINDOW, PGWARDEN_WARDENSENSE_TICK, PGWARDEN_WARDENSENSE_ERROR_RATE_THRESHOLD, PGWARDEN_WARDENSENSE_Z_THRESHOLD). Defaults are more conservative for production.

Environment overrides

Supported env overrides (applied after YAML defaults):

• PGWARDEN_PROXY_LISTEN_ADDR
• PGWARDEN_PROXY_TLS_MODE
• PGWARDEN_PROXY_TLS_CERT_FILE
• PGWARDEN_PROXY_TLS_KEY_FILE
• PGWARDEN_PROXY_TLS_CLIENT_CA_FILE
• PGWARDEN_PROXY_CONTROL_PLANE_BASE_URL
• PGWARDEN_PROXY_POLL_INTERVAL
• PGWARDEN_CONTROL_PLANE_HTTP_LISTEN_ADDR
• PGWARDEN_CONTROL_PLANE_DB_DSN
• PGWARDEN_CONTROL_PLANE_AUTH_MODE
• PGWARDEN_CONTROL_PLANE_OIDC_ISSUER_URL
• PGWARDEN_CONTROL_PLANE_OIDC_CLIENT_ID
• PGWARDEN_CONTROL_PLANE_OIDC_REDIRECT_URL
• PGWARDEN_CONTROL_PLANE_WARDENSENSE_ENABLED
• PGWARDEN_CONTROL_PLANE_WARDENSENSE_BASE_URL
• PGWARDEN_CONTROL_PLANE_WARDENSENSE_GRPC_ADDR
• PGWARDEN_CONTROL_PLANE_WARDENSENSE_AUTH_MODE
• PGWARDEN_CONTROL_PLANE_WARDENSENSE_AUTH_TOKEN
• PGWARDEN_CONTROL_PLANE_WARDENSENSE_MTLS_CERT_FILE
• PGWARDEN_CONTROL_PLANE_WARDENSENSE_MTLS_KEY_FILE
• PGWARDEN_CONTROL_PLANE_WARDENSENSE_MTLS_CA_FILE
• PGWARDEN_CONTROL_PLANE_WARDENSENSE_TIMEOUT
• PGWARDEN_SIGNALS_ENABLED
• PGWARDEN_SIGNALS_AUDIT_SINK
• PGWARDEN_SIGNALS_AUDIT_HTTP_ENDPOINT
• PGWARDEN_SIGNALS_AUDIT_HTTP_TOKEN
• PGWARDEN_WARDENSENSE_WINDOW
• PGWARDEN_WARDENSENSE_TICK
• PGWARDEN_WARDENSENSE_ERROR_RATE_THRESHOLD
• PGWARDEN_WARDENSENSE_Z_THRESHOLD

Security Notes

• Control‑plane DB requires mTLS (sslmode=verify‑ca|verify‑full + sslcert/sslkey).
• Proxy ingress TLS is required by default; mTLS optional.
• The proxy never logs raw SQL or PII.
• WardenSense receives audit metadata only.

API Reference

See docs/api.md for full endpoints. Highlights:

• Health: /livez, /readyz, /healthz, /v1/healthz
• Targets/DSNs/Policies: /v1/targets, /v1/dsns, /v1/policies
• Deployments: /v1/deployments
• Leases: /v1/leases, /v1/leases/redeem
• Events: /v1/events, /v1/events/ingest
• WardenSense: /v1/wardensense/status, /v1/wardensense/alerts

Observability

• OTLP logs export when signals.audit.sink=otlp.
• Collector example config routes all events to the control plane and filtered events to WardenSense.
• OTLP log attributes include event_id, event_time, event_type, severity, schema_version, plane, instance_id, version, and optional request_id, session_id, deployment_id, target_id, dsn_name, context_id plus event-specific fields (e.g. duration_ms, frontend_msgs, backend_msgs, error_count, tx_status, error).

Project Layout

cmd/
  pgwarden-control-plane/
  pgwarden-proxy/
  pgwarden-wardensense/
internal/
  auth/            # control plane auth middleware
  config/          # config parsing + env overrides
  controlplane/    # API handlers + routing
  events/          # audit event envelope
  lkg/             # last-known-good config store
  otel/            # OTLP logging
  policy/          # compile policies to SQL
  proxy/           # wire-protocol proxy
  store/           # Postgres store + migrations
  wardensense/     # WardenSense service
examples/
  docker-compose.yaml
  otel/
  charts/

Testing

• Unit tests:

  go test ./...
  

• Local proxy vs direct benchmark (self-contained Docker stack):

  ./scripts/bench-local.sh
  

  This spins up a minimal local stack (control plane + proxy + target DB), seeds data, issues a lease via mTLS, and prints a direct vs proxy latency summary.

• Integration tests (requires running services):

  PGWARDEN_TEST_CONTROLPLANE_DSN=postgres://... \
  PGWARDEN_TEST_TARGET_DSN=postgres://... \
  go test -tags=integration ./internal/controlplane -v
  

• Compose E2E harness:

  ./scripts/compose-e2e.sh
  

More detail: tests/README.md and tests/compose-e2e.md.

Naming conventions

• Policy names are auto-generated as policy_<target>_<dsn> by the UI.
• Target and DSN names accept only letters, numbers, _, and -.
• pgwarden-managed view schemas use the prefix pgw_<dsn>_.

Auth (Bundled Keycloak)

For on‑prem installs without external dependencies, pgWarden can be bundled with Keycloak as the OIDC provider.

Dev compose defaults:

• Keycloak URL: http://localhost:8082
• Realm