Your support team, your Metabase dashboards and your coding agents often read the same production tables, sometimes through one shared login. Dynamic data masking decides, for every query and every identity, which values come back in clear and which come back masked. This page explains how Formal’s proxy-based data masking software does that, where it applies and where it stops.
What is dynamic data masking?
Dynamic data masking (DDM) replaces sensitive values in query results at read time, based on who runs the query. The stored data doesn’t change. Static data masking is a different job: it writes a masked copy of a database, usually for test environments. If you need test data, you want static data masking tools; if you need to control what production access returns, you want dynamic masking.
Some databases ship DDM natively. Snowflake applies masking policies to columns at query time, and SQL Server has its own feature. Native database masking works one engine at a time, though: each has its own policy syntax and role model, and none of them knows which person sits behind a shared BI or service account.
Why run data masking software at the proxy instead of in each database?
Formal’s Connector, the reverse proxy behind Formal PAM, parses each database wire protocol. Masking happens on the response: after the database returns rows, before the client sees them. That placement gives you three things that per-database data masking solutions don’t:
- One policy for many engines. The same Rego rule masks Postgres, Redshift, MySQL, MariaDB, Snowflake, BigQuery and MongoDB results, plus CSV objects read from S3 with
GetObject. - Real identities. Policies evaluate the Formal user, their groups and, for BI tools, the end user behind the machine user. Your database roles don’t have to mirror your org chart.
- Lineage-aware masking. The Connector maps each returned column to its source columns. In
SELECT email FROM users UNION SELECT email FROM customers, a mask on either source column masks the whole result column. Wildcards and joins resolve the same way (column lineage).
Every masked query lands in the logs with the policy that triggered it, so you can chart masking activity per resource or check which policy masked a column for a given user.
Which data masking techniques can a policy apply?
A mask decision has a type, which sets the privacy level, and a sub_type, which picks the transformation. These are the six types:
| Type | Privacy level | What the reader gets |
|---|---|---|
nullify | 4 | NULL |
hash.with_salt | 4 | A salted hash that changes on every read |
fake | 3 | A realistic fake value (name, email, SSN, phone, address, card) |
redact.constant_characters | 3 | A constant string such as [REDACTED] |
hash.no_salt | 2 | A deterministic hash, so joins still work |
redact.partial | 1 | A partial mask, such as ****@example.com |
When two policies mask the same column, the higher privacy level wins, and a block beats an allow. Set typesafe_fallback to keep typed columns valid: a masked number, date or UUID comes back as NULL or a typed default instead of breaking the client.
Here is the per-user masking policy from the BI integration docs. It nullifies emails, SSNs and phone numbers unless the end user is in the pii_access group:
package formal.v2
import future.keywords.if
import future.keywords.in
# Mask PII unless end-user has special access
response := {
"action": "mask",
"type": "nullify",
"columns": pii_columns
} if {
# Identify PII columns
pii_columns := [col |
col := input.row[_]
col["data_label"] in ["email_address", "social_security_number", "phone_number"]
]
count(pii_columns) > 0
# End-user doesn't have PII access
not "pii_access" in input.end_user.groups
}
Formal doesn’t validate mask names when you save a policy. An unknown subtype leaves the value unmasked, and fake or redact.partial without a subtype does too. Run new masking policies in dry-run mode, check the results in the logs, then activate them.
How does PII masking work per user, per BI viewer and per agent?
PII masking follows the identity on the connection. People connect with their own Formal identity, so a rule can key on input.user.groups. BI tools connect as a machine user and pass the viewer’s identity, so the rule keys on input.end_user instead. Our post on the Metabase zero day makes the case for this: masking PII before it reaches the BI tool limits what a compromised dashboard server can expose.
Agents get the same treatment. An agent that runs as a machine user has its own identity and its own policies. When a coding agent connects through the Formal Endpoint, response policies also see input.agent, including the agent type, so a column can come back masked to Claude Code and in clear to the engineer’s own psql session.
Masking hides values; the filter action removes whole rows. With the Policy Data Loader, a support engineer can see a customer’s row only while a Zendesk ticket for that customer is open, as in the filter example.
Can you redact PII before it reaches an LLM?
Yes, for LLM traffic that goes through the Connector. LLM resources are HTTP-based, so a request-stage rewrite can replace the request body, for example with regex.replace on SSN patterns, and the session continues. The OWASP Top 10 for LLM applications lists sensitive information disclosure as LLM02 and recommends this kind of PII redaction before content is processed.
Gusto runs the Formal HTTP Connector between Salesforce and its LLM provider’s API. According to the Gusto case study, the Connector detects and masks PII and PHI in customer support tickets in real time, with audit logs for every data interaction. For the wider view of prompts, responses and tool calls per user, see AI gateway.
Logs can expose data too. A log schema can encrypt or drop LLM content, or strip literals from SQL queries, before Formal stores or exports the log.
Where does sensitive data masking stop?
Sensitive data masking only applies to traffic that goes through the Connector. A client that connects straight to the database with its own password gets raw values, so keep database credentials in Formal’s native users and give people and agents their own Formal identity instead. Other limits to plan for:
- Postgres exports. With response policies on,
COPY TO STDOUTand\copyare blocked. UseSELECTso policies apply (Postgres). - ClickHouse. Response policies can’t mask ClickHouse columns. A structured mask on a ClickHouse response fails closed.
- MCP servers. MCP responses support
allowandencrypt, notmask. - Bypass attempts. Pair masking with a request policy that blocks constructs such as
RESULT_SCANorCLONE, as in the redact emails example.
For engine-specific setup, including identity propagation from BI tools, see Snowflake and Postgres.
