How to mask PII before it reaches an LLM

Three places people put this control, in increasing order of how well it works.

There is a fourth option worth naming because it is genuinely the best one when it applies: do not put the data in front of the model at all. If the task is analysis, an aggregate over a masked column is often the whole answer. If the task is debugging, a sanitised copy on a local database is safer than any masking scheme and costs less to maintain. Reach for the boundary controls below when the task needs current production rows and some of those rows contain personal data.

Put the views in their own schema. A separate schema means you can revoke the real one wholesale, and it means the agent's search_path can point at the safe names so that an agent writing SELECT * FROM users gets the masked relation without knowing anything about your design.

CREATE SCHEMA agent;

CREATE VIEW agent.users AS
SELECT
  u.id,
  u.name,
  -- Deterministic, so equal addresses produce equal hashes. Read the oracle
  -- section below before you expose this column to anything.
  encode(sha256(u.email::bytea), 'hex')          AS email_hash,
  -- A literal token: the agent gets a value of the right type and no data.
  '[redacted]'::text                             AS ssn,
  -- Partial reveal, when the last four digits are what the task needs.
  'XXX-XX-' || right(u.ssn, 4)                   AS ssn_last4
FROM public.users u;

 id | name |                            email_hash                            |    ssn     |  ssn_last4
----+------+------------------------------------------------------------------+------------+-------------
  1 | Ada  | 08168cd80dfd534ab0f10af10f1303fe00af2d43ab5c1432360d137f8197e17a | [redacted] | XXX-XX-1111
  2 | Bob  | e8f39b3e1382367d6d41ab34dc270d4e7533f978c9e9a775dfe2185b2f96b96c | [redacted] | XXX-XX-2222

sha256() has been built in since PostgreSQL 11 and needs no extension, which matters because digest() from pgcrypto is what most examples use and it is not installed by default. If you want the hash to be unguessable rather than merely opaque, concatenate a secret before hashing it and keep that secret out of the view definition, because a view definition is readable by anyone who can query the catalog.

The views are the presentation. This is the enforcement.

REVOKE ALL ON public.users FROM agent_reader;
REVOKE USAGE ON SCHEMA public FROM agent_reader;

GRANT USAGE ON SCHEMA agent TO agent_reader;
GRANT SELECT ON agent.users TO agent_reader;

-- So an agent that writes an unqualified name lands on the masked relation.
ALTER ROLE agent_reader SET search_path = agent, pg_catalog;

Now the trap, which is worth its own paragraph because it is invisible and because the line that causes it is the same line every read-only-role tutorial tells you to write. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO agent_reader is a standing instruction, not a one-time grant. It keeps applying to tables that do not exist yet.

-- Written months ago, when the role was a plain read-only role.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO agent_reader;

-- Written today, by someone who has never heard of agent_reader.
CREATE TABLE public.new_pii (id int, card_number text);
INSERT INTO public.new_pii VALUES (1, '4111111111111111');

app=> SELECT * FROM public.new_pii;
 id |   card_number
----+------------------
  1 | 4111111111111111
(1 row)

No view was involved, no revoke was missed, and nobody made a mistake today. The masking layer was simply not in the path, because the grant that put the agent on the base tables outlived the design that replaced it. Check for these explicitly, because they do not show up in \dp:

SELECT n.nspname          AS schema,
       pg_get_userbyid(d.defaclrole) AS granted_by,
       d.defaclobjtype   AS object_type,
       d.defaclacl       AS grants
  FROM pg_default_acl d
  LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace;

Revoke the ones that name your agent role with ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM agent_reader, and remember that default privileges are scoped to the role that created them, so if migrations run as a different owner you have to revoke as that owner too.

Three queries. The first should work, the second and third should not.

SELECT * FROM agent.users ORDER BY id;
SELECT * FROM public.users;
SELECT attname, most_common_vals, histogram_bounds
  FROM pg_stats
 WHERE schemaname = 'public' AND tablename = 'users';

On 17.11 the second returned permission denied for table users and the third returned zero rows. The third one is the interesting result, and it is the reason to prefer a view layer over the column-grant approach in the next section.

Column-level GRANT

You can grant SELECT on a subset of columns, and the database enforces it perfectly. It is a real feature and it is underused. It is also not masking, and the difference shows up immediately in agent workloads.

GRANT SELECT (id, name) ON public.users TO agent_reader;

-- SELECT id, name FROM public.users;   works
-- SELECT * FROM public.users;          ERROR: permission denied for table users

Agents write SELECT * constantly. A hard failure gives the model nothing to work with, so it retries, guesses column lists, and burns turns discovering the shape of a table you could have handed it. Add the pg_stats leak above, and column grants end up being the right tool for a small number of genuinely forbidden columns and the wrong tool for a table you want an agent to work with.

The PostgreSQL Anonymizer extension

PostgreSQL Anonymizer is the mature open-source answer to this problem. It attaches masking rules to columns as security labels and rewrites queries for roles marked as masked, so you get dynamic masking without maintaining a parallel schema of views. It also ships faking and generalisation functions, which produce plausible values rather than tokens, and that is genuinely better for an agent that has to reason about the shape of the data.

CREATE EXTENSION IF NOT EXISTS anon CASCADE;

SECURITY LABEL FOR anon ON COLUMN users.email
  IS 'MASKED WITH FUNCTION anon.fake_email()';

SECURITY LABEL FOR anon ON ROLE agent_reader
  IS 'MASKED';

The catch is availability, and it is a real catch rather than a quibble. It is an extension, so you need to be able to install one, and the managed Postgres platforms differ on whether they allow it. We did not execute the block above, unlike everything else on this page, and it is listed under what we could not verify for that reason. If you run your own Postgres, evaluate it seriously before building a view layer by hand.

A hidden column you can filter on is an oracle

This is the one that matters, and it applies to every masking scheme that leaves a derived value comparable. The hashed email column above never shows an address to the agent. It does something worse: it answers questions about addresses.

SELECT id, name
  FROM agent.users
 WHERE email_hash = encode(sha256('a@example.com'::bytea), 'hex');

 id | name
----+------
  1 | Ada
(1 row)

One row came back, so the guess was right, and the agent now knows Ada's email address without that address ever crossing the wire. The same trick works on any comparable derived value and it does not need a hash: a masked column exposed in ORDER BY gives up the ordering of the underlying values, and one exposed in GROUP BY gives up their cardinality. LIKE against a partial reveal narrows it further. To close this you have to be able to refuse a query based on where the masked column appears, and a view cannot refuse anything. It can only return rows.

The schema drifts and the view does not

Every migration is now two migrations. Add a column, and it does not appear in the agent's view until someone remembers. Rename a column, and the view breaks at query time rather than at deploy time. Add a table with personal data in it, and it is either invisible to the agent (if you got the grants right) or fully visible (if you did not, per the default-privileges trap above). Neither outcome tells you anything until an agent hits it.

One view layer, one policy

A view encodes one decision about one column for everybody who can read it. If your analytics agent should see hashed emails and your support copilot should see the real ones, you are building two view schemas and two sets of grants, and keeping both current. The masking rule wants to live on the credential, and a view cannot know which credential is asking.

Error messages and side channels

Database errors quote values. A unique-violation message names the conflicting value, a cast failure names the input, and a constraint error can name a row. A view does not scrub error text, so a query crafted to fail in the right way can print what a SELECT would not return. This is a narrower channel than the oracle above, and it is a real one.

PgBeam sits in the PostgreSQL wire protocol between the agent and your database, on a globally distributed proxy in the region nearest your database. Masking rules attach to a credential's policy by schema.table.column and are applied to the result rows on their way back, so nothing about your schema changes and there is no parallel set of views to maintain.

pgbeam policies create --name analytics \
  --mask users.email=hash \
  --mask users.ssn=redact \
  --mask users.phone=null

pgbeam agents create --name analytics-bot --policy pol_1a2b3c

The part that answers the oracle problem is not the rewriting. It is that the proxy is allowed to say no. A masked column may be projected, and using it in a filtering or ordering position is refused with SQLSTATE 42501 and a message beginning blocked by PgBeam agent policy:. That covers WHERE, HAVING, JOIN ... ON and USING, GROUP BY, ORDER BY, DISTINCT, window partitions and frames, aggregate FILTER, LIMIT and OFFSET, TABLESAMPLE arguments, and the row-comparing set operations (UNION, INTERSECT, EXCEPT; UNION ALL compares nothing and is allowed). The exact query that defeated the view above is refused rather than answered.

Around that sit the other ways a masked value can be laundered out of a database, each closed rather than mitigated:

• **Expressions fail closed.** upper(email) or coalesce(email, ssn) cannot be proven to preserve the original, so the output column is masked to NULL rather than computed. This follows the value through subqueries and CTEs, so an aggregate over a derived column is masked too.
• **Whole-row wrapping fails closed.** row_to_json(u), to_jsonb(u), json_agg(t) over a base relation or a derived table are masked to NULL rather than serialising the raw row.
• **Writes that copy the value are blocked.** A masked column as the source of an UPDATE, an INSERT ... SELECT, a CREATE TABLE AS, a CREATE VIEW, or a data-modifying CTE is refused. Otherwise an agent could copy the cleartext into a table it is allowed to read.
• **Renames and index tricks are blocked.** Renaming the column or table, or naming a masked column in a CHECK constraint, an index expression, an INCLUDE list, or a partial-index predicate, would unhook the rule or build a searchable copy. Both are refused.
• **Error text is withheld, SQLSTATE is preserved.** When a statement touching a masked column fails upstream, the agent gets the real SQLSTATE and a replacement message, so it can still handle the error without the error handing it the value.
• **Paths that hide the relation are blocked outright.** COPY, the server-side large-object functions, and stored routine bodies and DO blocks are refused for agent credentials, because a mask plan cannot be built for a statement whose target the parser cannot see.

Masking applies to agent credentials only. Your application's own passthrough connection is never masked, so this is additive rather than something you have to route around, and the same rules apply whether the agent arrives over the hosted MCP endpoint or over a scoped Postgres connection string. If you want the product-shaped version of this page, PII masking for LLM database access is it.

The masking-specific ones first, because they change how you write rules.

• **hash is unsalted SHA-256.** Equal values hash equal, which is what makes it useful, and a low-entropy value such as an email address or a phone number is recoverable by brute force from the hash alone. Treat hash as the weakest of the three kinds and use redact or null for anything you would not accept being guessed offline.
• **A masked column may be selected, not filtered on.** That is the point, and it is also a constraint: an existing query that groups by a column you are about to mask will start failing, and it has to be rewritten rather than tuned.
• **Cursors are blocked for any credential with a mask rule.** DECLARE and FETCH name a portal rather than a relation, so no mask plan can be built for the fetch.
• **Two extended-protocol shapes fail closed with SQLSTATE 0A000**: more than one statement parsed before a single sync, and an execute cycle with no describe. Common drivers describe per cycle and are unaffected; a client-side statement cache that skips the describe has to be turned off.
• **Nothing PgBeam enforces sees through a view**, masking included. Rules are keyed on the relation named in the statement, and the wire path parses SQL with no catalog, so it cannot know that patient_summary reads patients. A view over a masked column returns the raw value, and the guard that refuses WHERE masked_column LIKE ... on a direct read is open through the view too. What bounds it is that an agent credential cannot create the view: every CREATE VIEW form over a masked column is refused. Reaching the unmasked read needs a view somebody else built. Write a rule naming the view, under the view's own column name, and it masks exactly as a rule naming a table does.
• **There is no column allowlist.** Masking is the column-level control; allowlisting is relation-level.
• **SET search_path is blocked for agent credentials**, because leaving it open would let a bare relation name resolve somewhere the rules do not cover.
• **Query budgets are per-region approximations.** Row caps and statement timeouts are exact.
• **No SOC 2 Type II.** The audit log is hash-chained and tamper-evident and the retention is real. The certification is on the roadmap and is not claimed.

• **The agent reads a handful of tables and you control the migrations.** Two or three views you own is less machinery than anything else on this page.
• **Every reader gets the same treatment.** The one-policy-per-view limit only bites when different consumers need different visibility.
• **Nothing derived is comparable.** If the masked columns come back as constant tokens with no hash and no partial reveal, the oracle has nothing to work with. You lose the ability to correlate rows, which is often an acceptable trade.
• **You can install extensions.** Then evaluate PostgreSQL Anonymizer first. It solves the maintenance half of this properly, and it is free.

Can I just tell the model to ignore the PII?

No, and the reason is mechanical rather than a matter of trust. Once the value is in the response it is in the context window, it is in the provider's request logs, it is in whatever transcript your framework writes to disk, and it is in the summary the model produces of its own work. An instruction cannot retract bytes that were already sent.

Is hashing enough for an email address?

Not on its own. An unsalted hash of a low-entropy value is a lookup problem, not a cryptography problem, and email addresses are low entropy. Hash when you need to correlate rows and the consequence of an offline recovery is acceptable. Redact or null when it is not.

Does masking slow queries down?

The view-layer approach costs whatever the expressions cost, which for a hash over a wide result set is not nothing. Rewriting result rows in a proxy costs a pass over the rows being returned, which is bounded by the result size rather than the table size.

What about masking in the ORM?

It works for the paths that go through the ORM. An agent with a SQL tool, a psql shell, or an MCP server is not one of those paths, and neither is the migration script someone runs at 2am. Boundary controls do not have this failure mode.