Postgres Dynamic Data Masking

Sensitive columns — SSNs, credit cards, emails, addresses — must stay queryable for support, analytics, and development. Broad cleartext access is not the answer. Data masking is. For some workloads (GDPR, HIPAA, PCI), masking is also a legal requirement.

Postgres ships no masking primitives in core. Two options dominate the practical landscape: PostgreSQL Anonymizer, a community extension that lives inside the database, and Bytebase Dynamic Data Masking, a policy layer in front of it. This post compares them.

PostgreSQL Anonymizer#

PostgreSQL Anonymizer is a community extension by Damaen and the team at Dalibo. Version 2.0 is a full rewrite in Rust on top of the PGRX framework — improved memory safety, lower overhead, and a cleaner extension surface than the SQL/PL-pgSQL ancestor.

Masking configuration is stored in PostgreSQL Security Labels. The pg_seclabel mechanism was designed for security modules, but it generalizes into a transactional, MVCC-aware way to attach custom metadata to any database object — no core patches required. Anonymizer pioneered this pattern for masking; pgEdge applies the same primitive to replication metadata. The mechanism is solid.

Five strategies#

Anonymizer 2.0 ships five distinct masking strategies. Pick by access pattern, not by aesthetics.

• Dynamic Masking. A role is declared MASKED. Sessions running under that role see masked output; other roles see cleartext. Role-based, transparent, no application changes.
• Static Masking. Rewrite the data in place. Destructive — only for clones and lower environments. Slow on large tables; Postgres rewrites every row containing at least one masked column.
• Anonymous Dumps. pg_dump_anon emits an anonymized dump for downstream consumers. The original cluster stays clean.
• Masking Views. Views over base tables that apply masking expressions. Grant access to the views, not the tables.
• Masking Data Wrappers. Foreign data wrappers that mask on read across federated sources.

Masking transformations#

Anonymizer 2.0 bundles eight transformation families: substitution, randomization, faking, pseudonymization, partial scrambling, shuffling, noise addition, generalization. The 2.0 faker is noticeably more realistic than 1.x — the locale-aware data generators replace the older static dictionaries.

-- 1. Load the extension and declare a masked role.
CREATE EXTENSION anon CASCADE;
SELECT anon.start_dynamic_masking();
CREATE ROLE analyst LOGIN PASSWORD '…';
SECURITY LABEL FOR anon ON ROLE analyst IS 'MASKED';

-- 2. Attach a masking rule via a security label on the column.
SECURITY LABEL FOR anon ON COLUMN customers.email
  IS 'MASKED WITH FUNCTION anon.fake_email()';

-- 3. analyst sees fake emails; postgres sees real ones.
SET ROLE analyst;
SELECT email FROM customers LIMIT 1;
--                email
-- ------------------------------
--  irma.fritsch@hahn-davis.test

What's new in 2.0#

• Role-based transparent dynamic masking — formerly tied to a single masked role; 2.0 supports nuanced role policy.
• Multiple masking policies — define and switch between policy sets per workload (e.g. one for analysts, another for support).
• Anonymous dump as a first-class workflow — not a bolt-on script.
• Debian packages — distribution-installable; no manual compile.

Limitations to know#

• GUI client compatibility. Per the docs, certain query patterns issued by tools like DBeaver or pgAdmin can behave unexpectedly under dynamic masking.
• View inventory. Masking Views still grow with every variation. Schema changes ripple through the view set.
• Per-extension, per-cluster. Anonymizer runs inside one Postgres cluster. Across a fleet of Postgres + RDS + Aurora + Postgres-flavored clouds, you maintain the extension and the labels on each one.
• Postgres only. A heterogeneous fleet (Postgres + MySQL + Snowflake) needs a separate masking story for the non-Postgres engines.

Bytebase Dynamic Data Masking#

Bytebase Dynamic Data Masking defines policies in Bytebase and enforces them at the SQL Editor query result. No extension. No security labels to maintain. One policy model across every PostgreSQL distribution — and every other engine in the fleet. Policy changes and exemption requests run through a built-in workflow — Request. Review. Approve. — with every step audited.

Policies are composed from three layers, evaluated in fixed precedence: Masking Exemption > Global Masking Rule > Column Masking.

1. Global Masking Rule. Workspace-level, ordered like iptables — the first matching rule wins. Match conditions span environment, project, database, and data classification level. Each match applies a Semantic Type, which in turn selects a masking algorithm (full, partial, MD5, range, or custom).

2. Column Masking. Project-level override. Project Owner assigns a Semantic Type to a specific column when the global rule does not apply. Global rules still take precedence over column masking.

3. Masking Exemption. Named users receive time-bound Query or Export exemptions to specific databases or tables. Service accounts are not eligible — exemptions are for human users querying through the SQL Editor. Every grant and every access is logged.

Masking is infectious: when a column is masked, the policy propagates to every view and derived structure that depends on it. The result reaches the SQL Editor directly:

Policies can also be codified via GitOps.

Masking decisions are recorded in the audit log. Every SQL execution entry carries per-column masking metadata — which columns came back masked, which Semantic Type triggered it, and which rule matched — alongside the user, source IP, statement, and row count. Granted exemptions, used exemptions, and policy edits are first-class audit events. The policy and the proof it was enforced live in the same record.

Enforcement boundary: Bytebase masks queries routed through the SQL Editor. Traffic that hits the database directly bypasses it. The operational pattern is to funnel human access through Bytebase — at which point masking applies uniformly across Postgres community, RDS, Aurora, Cloud SQL, AlloyDB, and managed forks.

Comparison#

Picking one#

• Single Postgres cluster, masking must enforce regardless of client. Use PostgreSQL Anonymizer 2.0. Dynamic masking for analyst access; pg_dump_anon for environment clones; static masking only when the data really must be destroyed.
• Mixed fleet — Postgres alongside MySQL, Snowflake, RDS, Aurora, or managed forks. Use Bytebase. One policy model. Every engine. Audited grants for every unmask, recorded in the same place as your access logs.
• Both. Anonymizer handles destructive workflows (anonymized dumps, lower-environment refresh); Bytebase handles human query traffic with approval and audit. They compose.

Try Bytebase Dynamic Data Masking with this tutorial. Questions? Join our Discord.