The PostgresDB Proxy: A Zero Trust Exchange for Operational Data

17 Aug 2026 Artificial Intelligence Databases

PostgresDB Proxy: AI Powered Data Masking and Zero Trust Access for PostgreSQL

1. Introduction

There is a gap in many enterprise data platforms, and it is easy to stop noticing that it is there.

Considerable effort tends to go into the analytics side of the house, and on the whole that effort pays off. The warehouse has row level security, the BI layer has column policies, the data catalogue knows which fields are classified, and access is provisioned through a request workflow with both an approver and an expiry date.

Then somebody needs to check something in production, whether that is an engineer debugging an incident late at night, an analyst who needs a figure that has not yet reached the warehouse, or a support agent tracing a single customer’s transaction. Whatever the reason, they tend to do what people have always done, which is to open a SQL client and connect to the operational database using a shared service account.

Most of those controls do not apply at that point. The database sees one connection from one user with one set of grants, and it has no way of knowing who is on the other end of it. It cannot mask a column for one person and reveal it to another, because as far as it is concerned there is only one user. The audit trail records that app_service ran a query, which is accurate but not especially useful.

The suggestion in this article is that the more productive fix is not another control attached to the database, but a change in where the controls live. It is worth considering the database less as infrastructure that grants access and more as an exchange that mediates between identities and data, in roughly the way an API gateway mediates between callers and services. That is the thinking behind PGProxy, which is written in Go, MIT licensed, and now public.

2. The shape of the problem

It is worth being reasonably precise about what is missing, because there are several plausible fixes that address only part of it.

2.1 Network position is not an identity

Zero trust for services is fairly widely accepted at this point, including the ideas that trust should not be granted on the basis of network location, that requests should be verified, and that breach should be assumed. It is still common, though, to place a jump host inside the database subnet and treat the problem as handled. Being inside the VPC is not an identity, and an authenticated VPN session is not authorisation to run a particular query against a particular table. That reasoning would not usually pass review for an internal API, which makes it worth asking why it often passes for the database behind the API.

2.2 Authentication is not the same as a policy layer

PostgreSQL can certainly be connected to an identity provider. Azure Database for PostgreSQL supports Microsoft Entra identities directly, including users, groups, service principals and managed identities, and both RDS and Aurora PostgreSQL support IAM database authentication mapped onto database users. If the problem is simply that people share a password, those features address it and a proxy may not be necessary.

What they do not provide is a place to express the rest of it. Mapping an external identity onto a database principal tells the database who is connecting, but it does not give you somewhere to define what that person may see, to rewrite results accordingly, to govern which queries may run, to route expensive work away from the primary, or to keep a durable and queryable record of those decisions. It also tends to be solved one engine at a time, which helps less when the estate contains PostgreSQL, Aurora and Redshift. The gap is less about authentication than about a policy layer that can sit above any of these engines.

2.3 Masking at rest has drawbacks

Masked views or a redacted replica are both reasonable options. Both leave you with two representations of the same data, a synchronisation obligation that tends to surface during schema migrations, and some incentive for people to query the original when the masked copy is stale. Protection that makes the sanctioned path less convenient than the unsanctioned one is liable to be worked around, often by the people whose queries you most wanted visibility of.

2.4 Query history is usually thinner than expected

It is worth asking which queries reached your primary in the last day, who ran each of them, and which touched personal information. Many organisations cannot answer much of that. The view pg_stat_activity provides a live snapshot rather than a history. Setting log_statement = all produces a large volume of text that is rarely read, and it captures raw SQL, which means the log platform becomes a secondary store of personal information and a compliance question in its own right.

Taken together, the identity is often wrong, the enforcement point sits away from where the decision needs making, and the record is thin. Addressing one of those in isolation helps less than it might appear, which is why PGProxy addresses all three at the same point.

3. A policy enforcement point on the wire

PGProxy speaks the PostgreSQL wire protocol, so clients connect to it as they would to PostgreSQL. That includes psql, DBeaver, DataGrip, JDBC drivers and ORMs, none of which need to be aware that anything has changed.

What differs is what happens in between. Connections are authenticated against your identity provider, SELECT statements can be rewritten before reaching the backend, queries are recorded with the human identity attached, and queries likely to damage the database can be blocked or routed elsewhere.

In the vocabulary of NIST SP 800 207 this is a policy enforcement point for operational data, sitting where a subject accesses a resource and separated from the policy decisions it enforces. The useful property is less any individual capability than the fact that identity, transformation and observation happen at one point on the wire.

3.1 The precondition

All of the above depends on a piece of network architecture that deserves stating plainly, because an earlier version of this article rather assumed it.

PGProxy functions as a control only if it is the only permitted route to the operational database. The security group or firewall in front of the backend needs to accept connections from the proxy or its workload identity alone, and people should have no network path and no credential that reaches the database directly. If a direct path remains, PGProxy is a controlled entrance beside an uncontrolled one, and the properties described below become advisory rather than enforced.

The pattern is therefore not only that a person connects through the proxy to PostgreSQL. It is that they authenticate to an identity provider, reach PostgreSQL through the proxy, and have no route to PostgreSQL that avoids it. That last condition does much of the security work, and it lives in your network configuration rather than in the code, which is why it is worth being explicit about.

                    +-------------------+
                    |  Keycloak / Entra |
                    +---------+---------+
                              | JWT validation
                              v
  Human or app  --TLS-->  +--------------+  --->  PostgreSQL primary
  (JWT as password)       |   PGProxy    |  --->  Shunt replica
                          +--------------+
                           |     |     |
                           |     |     +--->  Reporter DB
                           |     |             (query_issues,
                           |     |              query_metrics)
                           |     +--------->  Valkey (decision cache)
                           +--------------->  Portkey gateway to Bedrock

  No direct route from client to PostgreSQL.
  Backend security group admits the proxy identity only.

3.2 Authentication against your identity provider

Setting OIDC_AUTH=true and pointing OIDC_DISCOVERY_URL at Keycloak or Entra ID produces the following sequence:

The client connects and sends a startup message containing any username.
The proxy requests a cleartext password.
The client sends a JWT as the password.
The proxy validates the signature, expiry, audience and roles against the identity provider.
The proxy discards the client’s stated username and database, then connects to the backend using the service account credentials in PG_BACKEND_URL.

Step five is the one worth pausing on. The identity the client claims is not merely checked but replaced, and the operative identity comes from the email or upn claim in the verified token. From there, OIDC_ALLOWED_ROLES gates on group membership and OIDC_ALLOWED_DATABASES constrains which databases a JWT user may reach.

The practical effect is that people do not need the database password, and access reviews move into the identity provider alongside other access reviews. The revocation story needs stating carefully, though, because it is easy to overstate. PGProxy validates a self contained token locally, so disabling an account in the identity provider prevents new tokens from being issued, while a token already issued continues to work until it expires according to its configured lifetime. New access stops at once and existing access decays over the token lifetime. That is an improvement on a shared password that never expires, but it is not immediate revocation. Deployments that need something closer to immediate should keep token lifetimes short and consider token introspection against the provider, which Keycloak exposes for this purpose.

RDS and Aurora IAM authentication are also supported, including automatic token refresh and cross account STS, as is IAM authentication for ElastiCache and Valkey, which allows static database credentials to be removed from the deployment.

3.3 Transport security

Step three places a bearer token granting database access into a PostgreSQL cleartext password message. Over TLS that is an ordinary technique. Without TLS it is a credential in the clear, and the PostgreSQL documentation is clear that cleartext password authentication should not be used on an untrusted network without encryption.

The current defaults do not reflect that, since TLS_ENABLED and REQUIRE_CLIENT_TLS both ship as false, and we would treat that as a defect rather than a choice. The intended rule is that enabling OIDC_AUTH should require client TLS, and that starting with OIDC on and client TLS off should fail unless a development mode has been named explicitly. Until that is implemented, TLS_ENABLED=true and REQUIRE_CLIENT_TLS=true should be regarded as mandatory alongside OIDC_AUTH=true.

3.4 Human access and application traffic

The opening of this article describes a person needing controlled operational access, which is the case PGProxy was built for. Because it speaks the wire protocol it will also sit in front of an application, and the two deployments want somewhat different things.

For human access the full set applies, meaning OIDC authentication, masking according to who is asking, per user attribution and query governance. That is the deployment we would suggest starting with. For application traffic the relevant parts are workload identity, query governance, routing and observability, since masking designed around human curiosity usually makes little sense for a service that needs real values to function. We would not suggest placing an LLM assisted rewriting proxy in the hot path of every application, and the analysis cache and non blocking mode exist partly so that the cost can be measured before that decision is made.

4. Masking before the data moves

The design decision underneath the masking feature is worth explaining, because it is not the obvious one.

The straightforward approach is to let the query run and redact the result set on the way back. That works, but by the time redaction happens the sensitive values have been read from disk, crossed a network boundary and passed through the memory of the proxy. If something then goes wrong, whether a crash dump, a logging bug or a protocol edge case, real personal information is what is exposed.

PGProxy rewrites the query instead, so that SELECT email FROM users reaches the database as an expression that constructs the masked value inside the database engine:

-- Client sends:
SELECT email, first_name FROM users WHERE id = 1

-- Backend receives:
SELECT
  CASE WHEN email IS NULL THEN NULL
       WHEN POSITION('@' IN email::TEXT) = 0 THEN '***'
       ELSE CONCAT(LEFT(SPLIT_PART(email::TEXT, '@', 1), 1), '***@', ...)
  END AS email,
  '***' AS first_name
FROM users WHERE id = 1

Along this path the unmasked value does not leave the database, so there is no interval in which the proxy holds plaintext it is not meant to reveal. That is a different posture from result set redaction, and it is easier to explain to an auditor.

4.1 Declarative policy

Policy lives in YAML and is version controlled like other configuration:

tables:
  - schema: public
    name: users
    columns:
      - name: email
        sensitive: true
        mask_type: email
      - name: id_number
        sensitive: true
        mask_type: last_n_chars
        params:
          n: 4

The mask types cover full for complete redaction, first_n_chars and last_n_chars for partial visibility, email for a format preserving mask that keeps shape based joins and application logic working, and phone for showing trailing digits only. There is also a hash type intended for correlating records across tables without seeing the value, and section 8.2 should be read before relying on it, since its current implementation does not meet the standard the rest of this section describes.

4.2 Three design decisions

Three choices do most of the work here.

The first is that SELECT * is expanded rather than skipped. The proxy reads the schema at startup, so a wildcard becomes an explicit column list with masking applied. Wildcards are a common way for masking systems to leak, and expansion closes that by construction rather than by vigilance.

The second is that configuration is validated against the live schema, with drift surfaced at startup. You get warnings both for configured columns that do not exist in the database and, more usefully, for database columns not mentioned in the configuration. A migration that adds a sensitive column without updating policy is a common failure mode, and this surfaces it at boot rather than later.

The third is that unclassified tables have a defined outcome, since unknown_table_behavior must be set explicitly to passthrough, block or llm_inference.

4.3 Where the model belongs

That third setting warrants more care than we originally gave it, because the project uses AI in two places that are not equivalent.

Using a model to judge whether a query is inefficient is reasonable. A wrong answer has operational consequences, analysis can run in non blocking mode until it is trusted, and no one’s personal information rests on the decision. Using a model to decide at runtime whether an unclassified column is sensitive is a different proposition, since the model becomes part of a confidentiality boundary and a false negative is a disclosure rather than a slow query.

The pattern we would now recommend, and which the configuration generator already follows, is that the model proposes policy, a person approves it, and deterministic code enforces it. On that reading, runtime llm_inference is better understood as a convenience for exploratory environments than as part of a high assurance configuration. Where assurance matters, block is the more defensible setting for unknown tables, since an unclassified table then produces a refusal you will notice rather than an inference you will not audit. Tables present in the configuration always take the deterministic path with no model involvement, and that is the path we would ask you to judge the design on.

4.4 Classifying an existing schema

The same reasoning produced the configuration generator. Running the binary with CONFIG_GENERATOR_MODE=true scans your schemas, samples a row per table, and drafts a masking configuration annotated with confidence comments.

Classifying a large legacy schema by hand is a common reason for data protection work to stall, and this produces a reviewable first draft fairly quickly. It is a draft rather than an answer, so it still needs review before it is committed, but reviewing a proposal is a different sort of task from starting with an empty file.

5. Query governance and routing

The third capability is query analysis. A query is sent to Claude, by way of a Portkey gateway to Bedrock, with schema metadata and the ability to run EXPLAIN, for an efficiency review intended to catch accidental cartesian products, missing predicates and unbounded scans. Decisions are cached in Valkey or Redis, so a repeated pattern costs one analysis rather than one per execution.

Before the modes, there is a point about this path that follows directly from section 4. Masking protects values leaving the database, but a query can carry sensitive values inwards. A statement such as SELECT * FROM customers WHERE id_number = '8001015009087' contains the identity number in the SQL itself, and the analysis path currently sends the query text as written to the model. Literals are not stripped and the fingerprint is not substituted, so on this path personal information does cross the boundary that the masking design works to hold. Normalising literals before analysis, in the way the reporter already does for its fingerprints, is the change we would prioritise, and until it is made, AI analysis is not well suited to tables where identifiers appear in predicates.

There are three modes. In non-blocking mode the proxy analyses and records while allowing queries through, which is a reasonable place to start, since it shows you your own traffic before anything changes for users. In blocking mode queries that fail analysis are rejected, with the reason returned to the client. In shunt mode, approved queries go to the primary while rejected ones are routed to a separate slow node, and writes and transactions are blocked.

Shunt mode addresses a tension that is otherwise resolved awkwardly. An analyst with an expensive ad hoc query usually has a legitimate question, but the query as written would affect production. The common outcomes are to block it, leaving the analyst stuck, or to allow it and hope. Shunt mode offers a third, in which the query completes on a replica where its cost is contained, so no one is blocked, the primary is unaffected, and the event is recorded. Controls that do not obstruct legitimate work tend to last longer.

These capabilities compose, which matters for adoption, since ANALYSIS_ENABLED and MASKING_ENABLED are independent. You can run masking only, analysis only, both, or plain passthrough, and adopt one capability at a time.

6. Observation

Enforcement without evidence is closer to an assertion, so the remaining part of PGProxy records what happened. It comes after sections 4 and 5 because it records the decisions those sections describe.

6.1 Live attribution

With JWT authentication enabled, the proxy sets application_name on the backend connection to jwt:[email protected], which means a familiar query answers something it previously could not:

SELECT usename, application_name, query
FROM pg_stat_activity
WHERE application_name LIKE 'jwt:%';

That gives human identity in a tool DBAs already use, with nothing new to install. The same mechanism works against Redshift through stv_recents.

6.2 Two tables, kept separate

The proxy writes to two tables. query_issues records decisions, including the query fingerprint, the reason and severity from analysis, any suggested fix, whether the query was approved or blocked, whether the decision came from cache, the routing outcome, the client IP, the database, the username and the application name. query_metrics records timings, covering LLM latency, backend latency, total latency, rows returned, a status of success, error, timeout or blocked, and any error message.

They join on fingerprint and are deliberately separate, since audit data suits long retention and detailed investigation while performance data suits short retention and time series aggregation. Combining them would impose one retention policy on two different requirements.

6.3 Fingerprints by default

STORE_RAW_QUERY defaults to false, so the default record is the normalised fingerprint rather than the literal text including any identifier in the WHERE clause. This is the appropriate default for the reason given in section 2.4, namely that an audit log accumulating raw SQL becomes an unmanaged copy of sensitive data. It is also, as section 5 notes, a discipline the analysis path does not yet follow.

6.4 What becomes answerable

With those tables in place, a set of questions that are otherwise difficult become ordinary SQL. You can ask which people queried the customer table this month and how often, which client addresses generate the most blocked queries and whether they cluster in one team, what P99 latency looks like by database and whether the model or the backend accounts for it, which patterns are both slow and flagged critical, and how many times a pattern ran against how many of those executions came from cache.

The metrics guide in the repository includes worked examples, along with materialised views for dashboards, pg_cron retention jobs and time based partitioning for higher volumes. It is an observability substrate rather than a fixed dashboard, so you can point Grafana at it or use it ad hoc.

7. Why it is open source

This was built for our own use in the first instance. We run PostgreSQL and Redshift under South African financial services regulation, and we wanted masking, identity based access and a defensible audit trail at the operational layer rather than only in the warehouse. Available options did not cover that combination without a per seat licence and a vendor in the data path. It is public because neither the problem nor the general shape of the answer seems specific to us.

There is a further argument. A component sitting between applications and a production database is one you should be able to read, meaning the code rather than a datasheet, including which queries are rewritten and how, what happens on protocol edge cases, and where token validation occurs. A closed source proxy in that position asks you to accept its security properties on trust, at a point in the architecture where that is least comfortable. Sections 5 and 8 exist because the same principle applies to us, and the useful version of it includes the unfinished parts.

The testing reflects that intent. There are unit tests alongside Gherkin and godog BDD suites that start the real binary and run queries against real PostgreSQL, covering blocking, shunt and masking scenarios end to end, plus mutation testing with gremlins targeting better than seventy five percent test efficacy, which checks that the tests would catch a regression rather than only that lines executed. There are also health and readiness endpoints for Kubernetes and network load balancers, connection rate limiting per source IP, message size caps, and TCP keepalives that survive NAT gateways. MD5 password authentication is disabled by default, on the basis that PostgreSQL introduced SCRAM SHA 256 in version 10 and deprecated MD5 passwords in version 18, with removal staged across subsequent releases.

The licence is MIT, so it can be forked, run or built on commercially. We would rather the pattern spread than the code stay ours.

8. Current limitations

A component asking to sit in the path of production data should be clear about its edges, so these are the ones we know about.

8.1 The analysis path sends query text to the model

Described in section 5 and repeated here because it is the most consequential gap. Literals are not stripped before a query is sent for efficiency analysis, so sensitive values in predicates cross the boundary the rest of the design maintains. Normalising literals before analysis is the change we would make first.

8.2 The hash mask type needs replacing

The hash mask is implemented as an MD5 digest truncated to eight hexadecimal characters. Two things follow from that. Thirty two bits of output means collisions arrive sooner than a hash would suggest, and an unkeyed digest over a predictable format such as an email address, a mobile number or a national identity number is susceptible to dictionary attack rather than protected cryptographically. A keyed HMAC over SHA 256, with the secret held outside the configuration, would preserve deterministic joins while making offline guessing impractical. Until that is in place, hash is better treated as a correlation convenience in low sensitivity contexts than as a privacy control. The repository documentation also describes this mask as SHA 256 while the code implements MD5, and that inconsistency is being corrected.

8.3 SQL and protocol coverage is incomplete

Any component enforcing a boundary by rewriting SQL will eventually meet a construct it did not anticipate, whether an unusual alias, a CTE, a correlated subquery, a set operation, a JSON path expression, a function wrapping a sensitive column, a view, a cursor, a prepared statement in the extended query protocol, or COPY. An earlier version of this article said every SELECT statement can be rewritten, which was broader than the implementation supports.

The rule we hold to is that supported constructs are rewritten, constructs known to be safe are passed through, and anything unknown or unparseable fails closed rather than passing silently. A compatibility matrix showing where the boundary currently falls is outstanding, and until it exists it is worth testing your own query shapes rather than assuming coverage.

8.4 Other known gaps

Administrative bypass is not built, so DBAs who legitimately need unmasked data have no first class path through the proxy, and role based bypass with mandatory audit logging is on the roadmap. Masking metadata is not invalidated on DDL, so schema changes require a restart or periodic refresh. AI analysis adds latency on a cache miss, which is why latency is measured per query and broken out by component, and why non blocking mode exists so the figure can be observed before it is depended on. The deterministic masking path is also only as good as the configuration behind it, since the generator drafts it and drift warnings police it, but the classification decision remains yours.

9. Closing thought

The value in the zero trust exchange framing is not the phrase, which is only vocabulary, but the reframing introduced at the start, which is to treat the operational database less as infrastructure that grants access and more as an exchange mediating between identities and data. An exchange authenticates both sides of a transaction, applies policy to each one, transforms what passes through according to who is asking, keeps a record of what it did, and does not grant standing access on the basis of where a connection originated.

That is broadly the standard already applied to API gateways and service meshes, and it is not obvious why the path to more sensitive data should be held to a lower one. For a long time the tooling was not there, which is part of why the gap became unremarkable.

PGProxy is one attempt at it, available at github.com/capitec/pg-proxy under an MIT licence, with issues and pull requests welcome. Close the direct path to the database, point a client at port 5432, and have a look at what your traffic consists of.

Leon Rajindrapersadh and Terrence Naidoo are presenting this architecture at AWS Summit Johannesburg on 19 August 2026, in Breakout 8, Ballroom B, at 13:30, for anyone who would rather discuss it in person.