--- title: "How to mask PII before it reaches an LLM" description: "Mask at the database boundary, not in the prompt. The full SQL for a Postgres masking view, the grants people get wrong, and the hash-equality oracle that defeats it, demonstrated." canonical: "https://pgbeam.com/guides/mask-pii-before-it-reaches-an-llm" last-updated: "2026-09-03T00:00:00.000Z" --- # How to mask PII before it reaches an LLM > Mask at the database boundary, not in the prompt. The full SQL for a Postgres masking view, the grants people get wrong, and the hash-equality oracle that defeats it, demonstrated. Canonical: https://pgbeam.com/guides/mask-pii-before-it-reaches-an-llm Published: 2026-09-03 Updated: 2026-09-03 Every SQL block and every quoted result on this page was executed against PostgreSQL 17.11 on 2026-09-03. Mask at the database boundary, not in the prompt and not in your application code. The model's connection should be structurally incapable of reading the raw value, because any masking that happens after the bytes have been selected is a filter you have to remember to apply on every path, and the path you forget is the one that leaks. In stock Postgres that means a view layer: a schema of views that expose hashed, truncated, or literal-token replacements of the sensitive columns, with the base tables revoked from the agent's role. The complete SQL is two sections down and it runs on any Postgres from version 11. Two things decide whether it holds, and both are grants rather than views. Revoke the base tables and revoke `USAGE` on the schema that holds them, rather than only the column, because a view is only a boundary if the thing behind it is unreachable. Then check for a leftover `ALTER DEFAULT PRIVILEGES` line, which is the trap: on 17.11 a single stale default-privileges grant handed a read-only role a table full of card numbers that was created ten minutes after the grant was written. Then the limit that most write-ups skip. A masked column you can still filter on is not masked. With a SHA-256 email column exposed through a masking view, one `WHERE` clause confirmed a guessed address, and the address never appeared in a result set. That is not a bug in the view. It is what happens whenever a derived value is both hidden and comparable, and it is the reason masking needs a layer that can refuse a query as well as rewrite a row. ## At a glance | Field | Value | | --- | --- | | The honest first answer | A view layer in its own schema, with the base tables revoked. | | What enforces it | `REVOKE` on the base tables and on the schema. | | What quietly undoes it | A leftover `ALTER DEFAULT PRIVILEGES ... GRANT SELECT ON TABLES`. Verified. | | The gap it cannot close | Filtering on a masked or hashed column, which turns it into a guess-confirming oracle. | | Postgres version tested | 17.11. `sha256()` needs 11 or newer. | | Cost | Nothing, plus one extra migration per schema change forever. | ## Mask at the boundary, not in the prompt Three places people put this control, in increasing order of how well it works. | Where | What it stops | Why it fails | | --- | --- | --- | | In the system prompt | Nothing you can rely on. It reduces accidents. | The instruction and the untrusted content the model just read arrive through the same channel. The model is not an enforcement layer, and it does not get a vote on what a `SELECT` returns. | | In application code between the query and the model | The paths you remembered to route through it. | Every new tool, script, notebook, and MCP server is a new path. The redaction lives in one of them. Agents are specifically good at finding the others. | | At the database boundary | Every path, because the credential itself cannot read the value. | It does not fail the same way. It fails on maintenance (the view drifts from the schema) and on the oracle problem below, both of which are visible rather than silent. | 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. ## The view layer 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. ```sql title="A masking view, running on PostgreSQL 17.11" 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; ``` ```text title="What the agent sees" 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. > **Do not use security_invoker on a masking view** > > PostgreSQL 15 added `WITH (security_invoker = true)`, and it is the right setting for a view that should respect the caller's row-level security. It is the wrong setting here, and it silently breaks the design: verified on 17.11, a `security_invoker` masking view returns `permission denied for table users` for exactly the role you built it for, because it evaluates the base table as the caller. A masking view has to run with the view owner's privileges, which is the default, so leave it alone. ## Lock the base tables, and check your default privileges The views are the presentation. This is the enforcement. ```sql title="Make the real tables unreachable" 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. ```sql title="The demonstration, run in this order on 17.11" -- 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'); ``` ```text title="What the agent could then read" 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`: ```sql title="Find standing default-privilege grants" 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. ## Verify the masking, including the part that is supposed to fail Three queries. The first should work, the second and third should not. ```sql title="The probe" 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. > **pg_stats hands out sample values for any column you have SELECT on** > > `pg_stats` shows statistics for a column if the querying role holds `SELECT` on it, and those statistics include `most_common_vals` and `histogram_bounds`, which are real values sampled from your table. So a column privilege is two read paths, not one, and only the first is the one you were thinking about. Verified on 17.11: with `GRANT SELECT (id, name, ssn) ON public.users` in place, the agent read raw social security numbers straight out of `pg_stats.histogram_bounds`; revoke the `ssn` column privilege, as the view-layer design does, and that row vanishes from `pg_stats` entirely while `id` and `name` stay. Any masking scheme that leaves the base-table column privilege in place, and expects the agent to go through a view, needs to account for this. ## The two alternatives, and what each costs ### 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. ```sql title="Column-level grant, and what it does to a query" 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. ```sql title="The shape of an Anonymizer rule" 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. ## What a view layer does not cover ### 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. ```sql title="Run as the agent role, against the masking view" SELECT id, name FROM agent.users WHERE email_hash = encode(sha256('a@example.com'::bytea), 'hex'); ``` ```text title="The result on 17.11" 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. ## Where PgBeam fits 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. ```bash title="The equivalent of the view layer above" 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 three kinds, and exactly what lands on the wire** | Kind | Text-format column | Binary-format column | | --- | --- | --- | | `redact` | The literal token `[redacted]`. | NULL, because a text token would corrupt the declared wire type. | | `hash` | Lowercase SHA-256 hex of the value, 64 characters. Deterministic, so equal inputs give equal hashes in the returned rows. | NULL. | | `null` | NULL. | NULL. | 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 selected on its own, and using it anywhere its value changes what the server does 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). It also covers the projection itself when the column is wrapped in an expression there, which is the position most people assume is safe because the cell comes back masked. 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 over a masked column are blocked, not computed.** `upper(email)` or `coalesce(email, ssn)` cannot be proven to preserve the original, and nulling the output column is not enough: the expression still runs on the raw value, so an agent that writes `1/(CASE WHEN substr(email, 1, 1) > 'm' THEN 0 ELSE 1 END)` reads the column out one character per query from whether the statement errored. So the statement is refused instead, at any nesting depth, in a `RETURNING` list as well as a `SELECT` list, and through subqueries and CTEs. This costs no data: every shape now refused was returning NULL. Select the column plainly, or put the expression in a view your operator masks separately. - **Whole-row wrapping is blocked.** `row_to_json(u)`, `to_jsonb(u)`, `json_agg(t)` over a base relation or a derived table are refused rather than serialising the raw row, for the same reason. - **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](/for/pii-masking-for-llm-database-access) is it. ## PgBeam's limits, stated up front 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. ## When the view layer is the right answer - **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. ## Common questions ### 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. ## What we could not verify - The PostgreSQL Anonymizer block is the only SQL on this page we did not execute. It follows the project's documented interface, but we did not install the extension, and we are not claiming which managed Postgres platforms allow it. Check your provider's extension list before planning around it. - The set of side channels through error text is not enumerable, and we are not claiming it is closed by a view layer. The proxy replaces the message and preserves the SQLSTATE for statements touching a masked column, which closes the shapes we know about, not a proven-complete set. - `pg_stats` is the statistics leak we tested. Other catalogs and extension views expose sampled or aggregated values under their own privilege rules, and we did not audit all of them. ## Sources - [PostgreSQL documentation: pg_stats](https://www.postgresql.org/docs/current/view-pg-stats.html): `most_common_vals` and `histogram_bounds`, and the privilege rule that decides who sees them - [PostgreSQL documentation: CREATE VIEW](https://www.postgresql.org/docs/current/sql-createview.html): `security_invoker`, and whose privileges a view runs with by default - [PostgreSQL documentation: ALTER DEFAULT PRIVILEGES](https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html): why a default-privilege grant keeps applying to tables created later - [PostgreSQL documentation: cryptographic functions](https://www.postgresql.org/docs/current/functions-binarystring.html): the built-in `sha256()`, available since PostgreSQL 11 with no extension - [PostgreSQL Anonymizer](https://postgresql-anonymizer.readthedocs.io/): security-label masking rules, dynamic masking, and the faking functions - [PostgreSQL documentation: row security policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html): the row-level counterpart, for when the rule is about rows rather than columns ## Related - [How to give Claude Code read-only access to a production database](https://pgbeam.com/guides/claude-code-read-only-postgres): The role, the grants, and the settings that look like boundaries and are not. - [How to audit what an agent ran against your database](https://pgbeam.com/guides/audit-what-an-agent-ran-against-your-database): The other half of the question: not what it could read, but what it did read. - [PII masking for LLM database access](https://pgbeam.com/for/pii-masking-for-llm-database-access): The product page, if you have read the gaps above and want the shorter version. - [Masking reference](https://pgbeam.com/docs/masking): Rule syntax, the three kinds, and how masking interacts with caching and allowlists.