pgWarden

A deterministic, policy‑enforcing PostgreSQL access proxy with an optional control plane and AI drift‑detection signals

1. Project Overview

pgWarden is a PostgreSQL wire‑protocol proxy that enforces least‑privilege, context‑aware access to sensitive data at the database boundary rather than inside application code.

It is designed for environments where:

• multiple workloads (apps, developers, AI systems) access the same database
• PII exposure must be strictly controlled
• auditability and reproducibility matter
• policy enforcement must survive infrastructure restarts

pgWarden is shipped as a single product name (“pgWarden”) for Docker and Kubernetes distribution. Internally, it is composed of orthogonal services that can be deployed together or separately:

1. Data Plane (Proxy / Enforcement Layer)
2. Control Plane (Policy Definition & Compilation)
3. Auth (Session Attestation in the Control Plane; optionally backed by a separate IdP such as Keycloak)
4. Signal Plane (Audit, Drift & Anomaly Detection – optional; orthogonal)

Deterministic enforcement is always the source of truth. Heuristic detection systems never gate access.

2. Design Principles

• Infrastructure‑level enforcement over application‑level conventions
• Deterministic, auditable behavior for all access decisions
• Zero trust by default between workloads
• No query rewriting and no ORM requirements
• No raw PII stored outside the upstream database (audit stores derived metadata, not payloads)
• Mostly stateless data plane, with a small last‑known‑good cache for resilience; stateful control plane
• AI systems treated as first‑class but constrained actors

3. Data Plane: Postgres Access Proxy (Enforcement Layer)

3.1 Responsibilities

The data plane is a PostgreSQL‑compatible wire‑protocol proxy that:

• Accepts inbound Postgres connections via multiple DSNs
• Enforces Postgres TLS on ingress by default (sslmode=require compatible); plaintext may be explicitly disabled for dev only
• Optionally enforces mTLS on ingress
• Maps each DSN to a connection context (e.g. prod app, dev, AI)
• Authenticates clients (inbound) and terminates authentication at the proxy
• Connects upstream using proxy‑managed credentials mapped to the DSN context
• Enforces visibility rules using Postgres roles, schemas, and views
• Emits structured audit metadata for every session

The proxy does not:

• parse or rewrite SQL
• store query contents
• make probabilistic access decisions

3.2 Context‑Bound DSNs

Each inbound DSN represents a policy surface, not a database.

Examples:

• app_rw
• app_ro
• developer_sanitized
• ai_inference

Each DSN maps to:

• a specific Postgres role
• a constrained schema/view set
• fixed session defaults
• optional WardenSense enablement (default OFF)

3.2.1 Security posture

In v1, authorization is primarily DSN/context‑based.

• TLS is required by default; mTLS is optional.
• Client username/password may still be required for the inbound connection, but privileges are determined by DSN context.

Upstream credential isolation (v1):

• The proxy terminates inbound auth.
• The proxy does not embed upstream DB credentials in application repos.
• The proxy obtains authorization to connect upstream using a short‑lived token issued by the control plane.

This reduces the blast radius of credential leakage from source repos.

3.3 Enforcement Model

Access control is enforced by:

• role‑based permissions
• schema isolation
• curated views for sensitive fields
• column masking at the view level (policy‑selectable)

Masking strategies (v1):

• partial reveal (parameterized): reveal last N characters, mask the rest with a configurable character.
• partial reveal start (parameterized): reveal first N characters, mask the rest.
• full mask: replace all characters with a mask character.
• default value (static placeholder): e.g., "John Smith", "(555) 555-5555"

No salts/keys are required for these strategies.

The proxy ensures clients cannot escape their assigned context, even if client credentials leak.

3.4 Credential rotation (v1)

Credential and certificate rotation is manual and declarative in v1:

• to rotate upstream DB credentials (stored in control plane), update them via admin portal and redeploy/reload as needed
• to rotate proxy TLS/mTLS materials, reprovision certs and reload

3.5 Upstream leases, pooling, and seamless refresh (v1)

Upstream credentials are never embedded into application repos. Instead, the proxy obtains authorization to create upstream connections via short‑lived leases issued by the control plane.

Lease model (connection-creation only):

• Leases are used only to authorize creation of upstream connections (not per query).
• The proxy maintains an upstream pool per DSN.

Defaults (global; per‑DSN override):

• lease_ttl: 30m
• lease_refresh_window: 5m
• lease_refresh_jitter: 20% (randomized)
• pool.max_upstream_conns: 20
• pool.min_idle_upstream_conns: 2
• pool.idle_upstream_timeout: 5m
• pool.max_conn_lifetime: 30m

Seamless refresh:

• Proxy refreshes leases proactively within the refresh window.
• Proxy swaps active_lease to the newly fetched lease.
• Reseed strategy: after swapping, the proxy proactively creates upstream connections to restore min_idle_upstream_conns using the new lease.
• Pool draining: upstream connections created under the previous lease are marked draining:
  • not used for new sessions
  • closed when idle or when max_conn_lifetime is reached

Failure behavior:

• If the control plane is unreachable, existing upstream connections continue serving traffic.
• New upstream connections require a valid lease; if none is available, the proxy fails closed for new upstream creation while maintaining existing sessions.

4. Control Plane: Policy Definition & Compilation

4.1 Purpose

The control plane exists to make database access policies:

• easy to define
• hard to bypass
• reproducible
• durable across restarts and redeployments

It coexists with existing production database administration.

• pgWarden is not the sole authority for all production changes.
• However, pgWarden is authoritative for pgwarden‑managed artifacts (roles/grants/views it creates or owns).

The control plane does not replace SQL and does not expose raw database access to operators.

4.2 Core Responsibilities

• Manage multiple upstream databases from a single control plane
• Manage inbound DSNs per database
• Define context‑aware access policies
• Compile policies into deterministic Postgres artifacts
• Securely manage credentials and secrets

4.3 Policy Model (Conceptual)

Policies describe:

• Who (context / workload)
• What (schemas, tables, views)
• How (read/write, masking, row filtering)
• Where (target database)

Policies are declarative and compiled into:

• Postgres roles
• grants/revocations
• generated views

4.4 Compilation Flow

1. Operator defines or updates a policy
2. Control plane validates policy constraints
3. Policy is compiled into deterministic Postgres artifacts
4. Artifacts are applied idempotently to the target database(s)
5. Proxy reloads mappings without downtime

Compiler permissions (explicit):

• May create and manage roles (pgWarden‑scoped)
• May grant / revoke privileges
• May drop / replace pgWarden‑managed views

Coexistence rule: pgWarden must avoid clobbering non‑pgwarden objects. In practice, this is achieved by a clear ownership boundary (naming convention + metadata table) and by only mutating objects it owns.

4.5 State & Persistence

• Policies are stored centrally (e.g., Postgres / etcd / CRD‑like store)
• Secrets are never logged
• The data plane can restart safely using the last known good compiled state

4.6 Artifact Ownership & Reconciliation

pgWarden must coexist with normal DB operations while remaining authoritative for pgwarden‑managed objects.

Ownership boundary (recommended):

• Naming convention for managed objects, e.g. pgw_ prefix for roles/views/schemas
• Optional metadata catalog table (recommended) to record:
  • target id
  • object name/type
  • desired definition hash
  • last applied timestamp
  • last applied by deployment id

Reconcile behavior (desired):

• If pgWarden‑managed objects are modified manually, reconcile should overwrite back to the declared/compiled state.
• Reconcile must avoid mutating objects it does not own.

Rationale: overwrite‑back provides the least surprising path to “working state” for managed objects, while still allowing coexistence for everything else.

5. Audit & Observability

5.1 Deterministic Audit Signals

For every session, pgWarden emits structured metadata such as:

• context / DSN
• role
• source identity
• connection timing
• statement counts
• error rates

No query payloads are recorded. Audit output is derived metadata only; raw PII is not persisted in pgWarden‑owned stores.

5.2 Compliance & Forensics

Audit data is designed to support:

• access reviews
• incident response
• compliance reporting

6. WardenSense: Activity Drift & Anomaly Detection (Optional, Orthogonal)

WardenSense is pgWarden’s optional heuristics signal service. It is toggle-able per DSN connection context and is OFF by default.

6.1 Purpose

WardenSense detects unexpected patterns in database access behavior—especially from AI-driven workloads—without influencing enforcement.

It exists solely to:

• surface early warnings
• reduce mean-time-to-investigation

6.2 Deployment Shape

WardenSense runs as its own binary/service with its own database.

• It consumes pgWarden audit events (via telemetry sink)
• It stores derived features, baselines, and alert state in its own persistence layer

6.3 Detection Strategy (Current State)

The long-term intent for WardenSense is to support learned behavioral baselines (e.g., regression or ensemble-based models). In the current implementation, WardenSense deliberately uses deterministic, probabilistic heuristics rather than trained models.

Rationale

An early design goal for WardenSense was to support learned behavioral baselines. In practice, introducing a trained model without sufficient historical data would have produced worse outcomes than explicit heuristics.

A poorly trained model introduces:

• opaque decision boundaries
• unstable baselines
• false confidence in low-signal environments

In this context, a weak model would degrade operator trust while providing no meaningful improvement over simpler approaches.

Given limited and evolving data, deterministic heuristics provide:

• clearer failure modes
• predictable behavior
• debuggable signals
• lower risk of silent misclassification

For an advisory-only system, heuristics dominate poorly conditioned models on both correctness and operational trust.

Current Detection Pipeline

WardenSense operates over a strictly bounded and well-defined data source to preserve determinism and explainability.

Data Source

• Only db.session.summary events are ingested from ws_events
• Events are bucketed by (target_id, dsn_name, context_id) over a rolling window

Feature Aggregation (per window) For each bucketed scope and window, the following features are computed:

• session_count: number of session summaries in the window
• query_count: sum of frontend_msgs (if present)
• error_rate: error_count / max(backend_msgs, session_count)
• avg_latency_ms: mean of duration_ms
• p95_latency_ms: 95th percentile of duration_ms

Baseline Statistics For each scope, the last baselineWindows = 20 feature rows are used to compute:

• mean and standard deviation for error_rate
• mean and standard deviation for query_count
• mean and standard deviation for avg_latency_ms

Z-Score Computation Z-scores are computed as:

• z_error_rate = (error_rate - mean) / stddev
• z_query_count = (query_count - mean) / stddev
• z_latency = (avg_latency_ms - mean) / stddev

If stddev == 0, the z-score is treated as nil and the signal is considered stable.

Deterministic Rule Evaluation The following rules are evaluated for each window:

1. error_rate_high
   • Trigger: error_rate >= ErrorRateHighThreshold
   • Default: 0.2
2. error_rate_drift
   • Trigger: abs(z_error_rate) >= ZThreshold
   • Default ZThreshold: 3.0
3. latency_drift
   • Trigger: abs(z_latency) >= ZThreshold
4. query_volume_drift
   • Trigger: abs(z_query_count) >= ZThreshold

When any rule triggers, an alert is written to ws_alerts with the reason, scope, window, and computed z-scores.

Configuration Knobs

• pgWarden_WARDENSENSE_ERROR_RATE_THRESHOLD
• pgWarden_WARDENSENSE_Z_THRESHOLD
• pgWarden_WARDENSENSE_WINDOW
• pgWarden_WARDENSENSE_TICK

All thresholds, window sizes, and evaluation cadence are configuration-driven.

Design Invariant

Even with probabilistic heuristics, WardenSense never gates access or mutates enforcement policy. All outputs are advisory signals intended for human review.

Upgrade Path

The current architecture intentionally isolates feature aggregation from detection logic. This allows future replacement of heuristic evaluators with learned models once sufficient data and feedback loops exist, without altering ingestion, storage, or proxy behavior.

Future learning-based approaches are only justified once sufficient data exists to materially outperform explicit heuristics without sacrificing explainability.

7. Threat Model Summary

Protected against:

• accidental PII exposure
• credential reuse across contexts
• over‑privileged AI workloads
• lateral access escalation

Not intended to protect against:

• malicious superusers
• compromised upstream databases

8. Non‑Goals

• Query rewriting or SQL linting
• ORM replacement
• Data loss prevention inside queries
• Automated policy learning

9. Deployment Model (High Level)

• pgWarden proxy deployed as a mostly stateless service (with last‑known‑good cache)
• Control plane deployed as stateful service
• Optional signal processors consume audit streams

Works with:

• Kubernetes
• Nomad
• VM‑based deployments

10. What I’d Change Next

With additional time and scope:

• finer-grained administrative RBAC
• automated credential rotation
• additional masking primitives
• richer drift analysis in WardenSense

None of these would alter the core enforcement model.

Closing Note

pgWarden is intentionally boring in its enforcement logic. The goal is not cleverness, but predictability under failure.

Most of the complexity in this system exists to ensure that should things go wrong the outcome is auditable and safe.