PostgreSQL has no built-in masking functions. You mask data with a view plus column privileges, with the postgresql_anonymizer extension (dynamic masking, static masking and anonymized dumps), or at a proxy such as Vericto, which rewrites the query without changing the database or the application. Row-level security filters rows, not columns.
What data masking is and when you need it
Data masking returns a***@example.com instead of ana@example.com to whoever should not see the real value. Dynamic masking does it at read time, per role or client; static masking rewrites the data, usually in a copy.
You need it for personal and payment data (PII), for compliance work such as GDPR or PCI DSS, for analysts and support staff who need rows but not full values, and for AI agents: an agent that writes its own SQL reads every column its role allows, and those values land in the model's context.
Every example below uses the same table, customers(id, name, email, card_number, ssn, created_at), with two rows: Ana and Bruno.
Option 1: a masking view and column privileges
The portable option. Revoke the table, grant the safe columns, and expose the sensitive ones only through a view that masks them. A view runs with its owner's privileges, so the analyst reads the view without having SELECT on the masked columns (rules and privileges, GRANT on columns).
CREATE ROLE analyst LOGIN;
REVOKE ALL ON customers FROM PUBLIC;
GRANT SELECT (id, name) ON customers TO analyst;
CREATE VIEW customers_masked AS
SELECT id, name,
left(email, 1) || '***@' || split_part(email, '@', 2) AS email,
'****' || right(card_number, 4) AS card_number,
'XXX-XX-' || right(ssn, 4) AS ssn,
date_trunc('month', created_at)::date AS created_month
FROM customers;
GRANT SELECT ON customers_masked TO analyst;
SET ROLE analyst;
SELECT id, name FROM customers;
SELECT email FROM customers;
SELECT * FROM customers;
SELECT id, name, email, card_number, ssn FROM customers_masked;
-- SELECT id, name FROM customers: 2 rows (1 Ana, 2 Bruno)
-- SELECT email FROM customers:
ERROR: permission denied for table customers
-- SELECT * FROM customers:
ERROR: permission denied for table customers
-- SELECT … FROM customers_masked:
id | name | email | card_number | ssn
----+-------+------------------+-------------+-------------
1 | Ana | a***@example.com | ****4242 | XXX-XX-6789
2 | Bruno | b***@example.org | ****1881 | XXX-XX-4321
(2 rows)Tested on PostgreSQL 17.11 and 18.6 (Docker images postgres:17 and postgres:18), 2026-11-14.
The error does not name the column: permission denied for table customers. SELECT * fails too, because it includes columns the role cannot read.
Do not make the masking view security_invoker
With security_invoker = true the base table is checked against the privileges of whoever queries the view (CREATE VIEW). The analyst has no SELECT on card_number, so the view fails:
CREATE VIEW customers_masked_invoker WITH (security_invoker = true) AS
SELECT id, '****' || right(card_number, 4) AS card_number FROM customers;
GRANT SELECT ON customers_masked_invoker TO analyst;
SET ROLE analyst;
SELECT * FROM customers_masked_invoker;
ERROR: permission denied for table customersTested on PostgreSQL 17.11 and 18.6 (Docker images postgres:17 and postgres:18), 2026-11-14.
What can go wrong
- Every client must query the view. An application or agent that has the table grant reads the real values.
- Each new sensitive column needs a change to the view and to the grants.
- If the view also filters rows, the docs recommend
security_barrier, which can be slower.
Option 2: the postgresql_anonymizer extension
PostgreSQL Anonymizer (anon, by Dalibo, under The PostgreSQL License) declares masking rules as security labels on columns and applies them dynamically, statically or in dumps. We checked version 3.3.2 (2026-10-28); 3.x supports PostgreSQL 14 to 18. 3.3.0 was a security release that fixed 12 CVEs, so stay on the latest version (changelog).
Install it (from the docs; the official image already had it loaded):
ALTER SYSTEM SET shared_preload_libraries = 'anon'; -- then restart PostgreSQL
CREATE EXTENSION anon;
SELECT anon.init(); -- loads the dataset the fake_* functions needFrom the docs, not tested here: PostgreSQL Anonymizer: installation
Dynamic masking
Current versions use transparent dynamic masking: turn it on for the database, label the columns, and label a role as MASKED (docs). The masked role reads the same table and gets masked values:
-- As postgres, in database shop:
ALTER DATABASE shop SET anon.transparent_dynamic_masking TO true;
SECURITY LABEL FOR anon ON COLUMN customers.name
IS 'MASKED WITH FUNCTION anon.dummy_first_name()';
SECURITY LABEL FOR anon ON COLUMN customers.email
IS 'MASKED WITH FUNCTION anon.partial_email(email)';
SECURITY LABEL FOR anon ON COLUMN customers.card_number
IS 'MASKED WITH FUNCTION anon.partial(card_number, 0, $$************$$, 4)';
SECURITY LABEL FOR anon ON COLUMN customers.ssn
IS 'MASKED WITH VALUE $$XXX-XX-XXXX$$';
CREATE ROLE analyst LOGIN PASSWORD 'x';
SECURITY LABEL FOR anon ON ROLE analyst IS 'MASKED';
GRANT pg_read_all_data TO analyst;
-- As analyst, in a new connection:
SELECT id, name, email, card_number, ssn FROM customers;
EXPLAIN SELECT * FROM customers;
id | name | email | card_number | ssn
----+-------+-----------------------+------------------+-------------
1 | Nadia | an******@ex******.com | ************4242 | XXX-XX-XXXX
2 | Will | br******@ex******.org | ************1881 | XXX-XX-XXXX
(2 rows)
ERROR: Anon: role is maskedTested on postgresql_anonymizer 3.3.2 on PostgreSQL 18.6 (registry.gitlab.com/dalibo/postgresql_anonymizer:stable), 2026-11-14.
anon.dummy_first_name()is random: the same rows came back with other names in a later run.anon.partial_email()keeps two leading characters, not one.- The docs list what a masked role cannot do: use
EXPLAIN, define security labels, use cached plans prepared by another role, or readSECURITY INVOKERviews. - The older
anon.start_dynamic_masking()was removed:
SELECT anon.start_dynamic_masking();
ERROR: function anon.start_dynamic_masking() does not existTested on postgresql_anonymizer 3.3.2 on PostgreSQL 18.6 (registry.gitlab.com/dalibo/postgresql_anonymizer:stable), 2026-11-14.
Static masking
Static masking rewrites the stored values. The docs warn that these methods destroy the original data, so run them on a copy. Since 3.2 the extension refuses to mask on behalf of a superuser (anon.nosuperuser, on by default); run it as the table owner:
-- As a superuser (anon.nosuperuser is on by default):
SELECT anon.anonymize_table('customers_copy');
-- As data_owner, a non-superuser that owns the table:
SELECT anon.anonymize_table('customers_copy');
SELECT id, name, email, card_number FROM customers_copy;
ERROR: Anon: Static and dynamic masking cannot be used with a superuser if anon.nosuperuser is set to true.
anonymize_table
-----------------
t
1 | Ana | an******@ex******.com | ************4242
2 | Bruno | br******@ex******.org | ************1881Tested on postgresql_anonymizer 3.3.2 on PostgreSQL 18.6 (registry.gitlab.com/dalibo/postgresql_anonymizer:stable), 2026-11-14.
fake_* functions need SELECT anon.init() before they return values; the docs recommend the dummy_* functions for new projects.
Row-level security filters rows, not columns
Row security policies restrict which rows a role sees. They do not change the values in the rows it does see:
CREATE ROLE support LOGIN;
GRANT SELECT ON customers TO support;
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY support_sees_ana ON customers FOR SELECT TO support USING (id = 1);
SET ROLE support;
SELECT id, name, email, card_number FROM customers;
id | name | email | card_number
----+------+-----------------+------------------
1 | Ana | ana@example.com | 4111111111114242
(1 row)Tested on PostgreSQL 17.11 and 18.6 (Docker images postgres:17 and postgres:18), 2026-11-14.
The support role sees only Ana, with her full email and card number. Use RLS together with masking, not instead of it.
- Superusers and roles with
BYPASSRLSalways bypass row security. - The table owner bypasses it too, unless you set
ALTER TABLE … FORCE ROW LEVEL SECURITY.
pg_dump and anonymized dumps
pg_dump has no masking option (docs). It can leave data out: --exclude-table-data keeps a table's definition without its rows, and since PostgreSQL 17 --filter reads include and exclude patterns from a file and --exclude-extension skips an extension. We checked that pg_dump --help lists both on PostgreSQL 17 and 18.
# Keep the table definition, leave its rows out of the dump:
pg_dump shop --exclude-table-data=customers --file=shop.sqlFrom the docs, not tested here: PostgreSQL docs: pg_dump
For an anonymized dump, run plain pg_dump as a role masked by anon (anonymous dumps). --no-security-labels keeps the masking rules out of the file:
pg_dump shop -h 127.0.0.1 --user analyst --no-security-labels --exclude-extension="anon" \
--table=customers --data-only --inserts
INSERT INTO public.customers VALUES (1, 'Ceasar', 'an******@ex******.com', '************4242', 'XXX-XX-XXXX', '2026-11-14 18:00:09.102807+00');
INSERT INTO public.customers VALUES (2, 'Nadia', 'br******@ex******.org', '************1881', 'XXX-XX-XXXX', '2026-11-14 18:00:09.102807+00');Tested on postgresql_anonymizer 3.3.2 on PostgreSQL 18.6 (registry.gitlab.com/dalibo/postgresql_anonymizer:stable), 2026-11-14.
The old pg_dump_anon wrapper was removed in 2.5.0, according to the changelog; the docs page still calls it deprecated.
Trade-offs: native options vs. the Vericto proxy
| View + column GRANT | postgresql_anonymizer | Row-level security | Vericto proxy | |
|---|---|---|---|---|
| What it masks | Columns, through the view | Columns (dynamic, static, dumps) | Nothing: it filters rows | Columns, in the query the proxy forwards |
| Application changes | Query the view | None for the masked role | None | Point the connection at the proxy |
| Per-agent policies | One role per agent | Per masked role | Per role | A dedicated proxy and database entry for the agent path, plus an agent-access allowlist |
| Audit | Database logs | Database logs | Database logs | An audit trail event per masked, blocked or flagged read, with the column |
| Performance | Expressions in the database | Masking functions in the database | Policy predicates in the database | One extra hop; the mask runs in the database (benchmark) |
| Superusers | See everything | Refuses to mask on their behalf by default | Bypass it | Masks whatever role connects through the proxy |
| Licence | PostgreSQL | The PostgreSQL License | PostgreSQL | AGPL-3.0-only |
Mask columns with Vericto
Vericto masks at the proxy, for agents and applications, without changing the database or the application.
- Tag columns per database in the Vericto dashboard, each with a policy:
block(the query is blocked),flag(it runs and is recorded, with an alert) ormask(the projection is rewritten), and formaska style:full,last4,emailorhash. - Every read of a tagged column is reported as VERICTO-085. When a query reads several tagged columns, the strictest policy wins:
block>mask>flag. - Vericto decides on the AST of the query, by column name. It never reads the results and does not look at the values.
The engine rewrites the projection and the database computes the masked value, so the clear value never reaches Vericto or the client. This is the real rewrite for PostgreSQL:
-- The query the agent or the app sends (PostgreSQL):
SELECT id, name, email, card_number FROM customers WHERE id = 42
-- FLAGGED VERICTO-085: SensitiveColumn > customers.card_number, customers.email (mask)
-- The query the proxy forwards instead (rewritten_query):
SELECT id, name, regexp_replace(email::text, '^(.)[^@]*(@.*)?$', E'\\1***\\2') AS email, CASE WHEN char_length(card_number::text) <= 8 THEN '[redacted]'::text ELSE '****' || "right"(card_number::text, 4) END AS card_number FROM customers WHERE id = 42Real output: vericto-engine 5.0.0, evaluate() on PostgreSQL with customers.email (mask, email), customers.card_number (mask, last4) and customers.ssn (block), 2026-11-14.
-- The rows the masked rewrite returns:
id | name | email | card_number
----+------+------------------+-------------
42 | Ana | a***@example.com | ****4242
(1 row)Tested on PostgreSQL 17.11 and 18.6 (Docker images postgres:17 and postgres:18), 2026-11-16.
The last4 style also replaces values of 8 characters or fewer with [redacted], as the CASE above shows.
Where it is a control and where it is advice
| How the query reaches the database | What happens under mask |
|---|---|
| TCP proxy (PostgreSQL and MySQL wire protocols) | The proxy forwards the rewritten query, never the original; the database returns masked values. This is a control. |
| Runtime API and SDKs | The answer carries rewritten_query; your application must run it. |
MCP (check_sql) | The masked query comes back as the only approved_query; the agent has to follow it. |
| CLI and validation API | The masked version is shown as a suggestion (CI, migrations). |
Masking is a control only when the agent or client reaches the data through the TCP proxy and has no other credential. Sensitive columns are configured in a Vericto workspace; the masking page lists where the feature is available. vericto-proxy mcp applies masks only when it is linked to a workspace: its local mode, with no account, has none.
What counts as a read
SELECT *, whole-row references and COPY … TO over a table with tagged columns are blocked under mask and block: list the columns.
SELECT * FROM customers
-- BLOCKED VERICTO-085: SensitiveColumn > * over customers (block): list the columns explicitly
-- Fix: List the columns explicitly: `*`, `t.*`, whole-row references and `COPY table TO` read every tagged columnReal output: vericto-engine 5.0.0, evaluate() on PostgreSQL with customers.email (mask, email), customers.card_number (mask, last4) and customers.ssn (block), 2026-11-14.
A block column stops the query:
SELECT id, ssn FROM customers
-- BLOCKED VERICTO-085: SensitiveColumn > customers.ssn (block)Real output: vericto-engine 5.0.0, evaluate() on PostgreSQL with customers.email (mask, email), customers.card_number (mask, last4) and customers.ssn (block), 2026-11-14.
A masked column comes back as text, and any expression over it is masked full:
SELECT lower(email) FROM customers
-- FLAGGED VERICTO-085: SensitiveColumn > customers.email (mask)
-- rewritten_query (an expression over a masked column is masked full):
SELECT concat('[redacted]'::text, "left"(lower(email)::text, 0)) AS lower FROM customersReal output: vericto-engine 5.0.0, evaluate() on PostgreSQL with customers.email (mask, email), customers.card_number (mask, last4) and customers.ssn (block), 2026-11-14.
A column used only in WHERE, JOIN, GROUP BY or ORDER BY is not a read:
SELECT id FROM customers WHERE email = 'ana@example.com'
-- FLAGGED VERICTO-050 (SELECT without LIMIT); no VERICTO-085:
-- a column used only in WHERE is not a read, nothing is maskedReal output: vericto-engine 5.0.0, evaluate() on PostgreSQL with customers.email (mask, email), customers.card_number (mask, last4) and customers.ssn (block), 2026-11-14.
Limits
- Because filtering is not a read, an agent can infer a masked value by filtering on it. For an agent, add an agent-access allowlist, where every reference counts.
- Vericto decides by column name, not content. A renamed column loses its tag; views, functions and partitions over a tagged table are not tagged.
- With any
blockormasktag, a query the engine cannot parse is blocked. - Copying a masked value into another table (
INSERT … SELECT,CREATE TABLE AS) is blocked, since masking it would change stored data. - It is defence in depth: column privileges, views and RLS stay the base.
Frequently asked questions
Sources
The official documentation this guide cites, checked on 2026-11-14:
- PostgreSQL docs: rules and privileges (views)
- PostgreSQL docs: CREATE VIEW (security_invoker)
- PostgreSQL docs: GRANT (column privileges)
- PostgreSQL docs: row security policies
- PostgreSQL docs: pg_dump
- PostgreSQL Anonymizer documentation
- PostgreSQL Anonymizer: installation
- PostgreSQL Anonymizer: transparent dynamic masking
- PostgreSQL Anonymizer: anonymous dumps
- PostgreSQL Anonymizer changelog (3.3.2, 2026-10-28)
- PostgreSQL Anonymizer licence (The PostgreSQL License)
- vericto-proxy 5.0.1: docs/SENSITIVE_COLUMNS.md
- Vericto docs: sensitive columns
- vericto-engine 5.0.0 (source and README)
Related pages
- Data masking for databases: native options and the proxy approach
- MySQL data masking: Enterprise functions, views, generated columns and a proxy
- MariaDB data masking: the MaxScale filter, views and a proxy
- How to connect AI agents to databases: 5 ways, step by step
- Vericto data masking
- Docs: sensitive columns
- Docs: agent access, an allowlist per agent
- What a read-only Postgres role does and doesn't stop when an AI agent connects