PostgreSQL data masking usually lives inside the database: an extension such as PostgreSQL Anonymizer declares masking rules on columns and applies them to masked roles. That breaks down when engineers, a BI tool and an AI agent all connect as the same role, because Postgres can’t tell them apart. We apply Postgres access control and masking in a Postgres proxy instead, where every query carries a person’s identity.
How does PostgreSQL data masking work in Formal?
Formal does Postgres data masking in the response, as rows stream back from the database through the Connector. The table doesn’t change; the client receives the masked value. This is dynamic data masking, so the same column can be masked for one group and readable for another, decided per query by policy.
Policies target columns by data label rather than by table name. The Data Discovery Satellite scans your schemas on a schedule and can classify columns with labels such as email_address or social_security_number, so a new table’s email column gets the existing mask without a policy change. This example from the enforcement guide masks the username part of every labeled email:
package formal.v2
import future.keywords.if
response := {
"action": "mask",
"type": "redact.partial",
"sub_type": "email_mask_username",
"columns": columns
} if {
columns := [col |
col := input.row[_]
col["data_label"] == "email_address"
]
count(columns) > 0
}
Two limits to plan for. Formal doesn’t validate mask names when you save a policy, so a typo leaves the value unmasked: run new policies in dry-run first. And while response policies are active, COPY TO STDOUT and \copy are blocked, so exports go through SELECT, where masking applies. For masking beyond Postgres, see data masking.
Postgres audit logging: pgAudit or the proxy?
pgAudit is the PostgreSQL extension for session and object audit logging, written through the standard PostgreSQL log. It records what a database role did. If ten engineers and a BI tool share one role, the audit log shows one role.
Formal logs at the proxy, where the identity is known. Each connection is a session tied to a Formal user; each query carries its text, execution time, rows affected, bind parameters and policy actions. Admins can terminate an active session from the Control Plane. Modern Health runs Formal across 20+ Postgres databases, and its case study reports audit logging beyond what default Postgres logging gave them.
How do engineers, BI tools and AI agents connect?
Engineers connect with their Formal identity and never see the database password, because the Connector logs in upstream with a Native User. That’s the least privilege lever: give the read-only Native User SELECT only, and keep write access behind a separate login for the groups that need it.
BI tools usually connect as one service account. With the BI integration, Metabase, Looker, Omni and Fivetran pass the end user’s identity to the Connector, so masking and logs follow the person behind the dashboard; custom apps use the formal-sqlcommenter library. ManoMano uses Formal for visibility into data access across Metabase, Snowflake and Postgres (see also the Metabase zero day).
Agents and people who need production for one task get just-in-time access: a policy blocks by default, and an approved request in Slack, a Workflow or the API creates a one-off or time-bound exception for that user. The same policies cover your other databases through database access management.
