Postgres Data Masking & Anonymization SQL Generator

Paste the sensitive columns of a table and get a ready-to-run PostgreSQL masking script in one of three shapes: a security_invoker view that leaves the base table intact, a batched in-place UPDATE for anonymising a restored dev copy, or SECURITY LABEL statements for the PostgreSQL Anonymizer extension. Column rules are guessed from the column name — email, phone, card, national id, salted hash, partial or null — and every script ships with verification queries. All generation happens in your browser; no schema or data is uploaded.

SchemaTableMasked role

Output mode

View nameHash salt

Sensitive columns (one per line)

Masking rule per column

emailfull_namephonecredit_carddate_of_birthstreet_address

Masking script

-- Masked view over public.customers.
-- The base table keeps the real values; only this view is granted to analyst.
-- security_invoker means the view runs with the caller's privileges (PG 15+),
-- so it cannot be used to escalate past row level security on the base table.
CREATE OR REPLACE VIEW public.customers_masked
WITH (security_invoker = true) AS
SELECT
  CASE WHEN email IS NULL THEN NULL
         ELSE left(email, 1) || '***@' || split_part(email, '@', 2) END AS email,
  CASE WHEN full_name IS NULL THEN NULL
         ELSE left(full_name, 1) || '***' END AS full_name,
  CASE WHEN phone IS NULL THEN NULL
         ELSE repeat('*', greatest(length(phone) - 4, 0)) || right(phone, 4) END AS phone,
  CASE WHEN credit_card IS NULL THEN NULL
         ELSE '**** **** **** ' || right(regexp_replace(credit_card, '\D', '', 'g'), 4) END AS credit_card,
  date_trunc('year', date_of_birth)::date AS date_of_birth,
  CASE WHEN street_address IS NULL THEN NULL
         ELSE left(street_address, 2) || repeat('*', greatest(length(street_address) - 2, 0)) END AS street_address
FROM public.customers;

-- Give the analyst role the view only, never the base table.
REVOKE ALL ON public.customers FROM analyst;
GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON public.customers_masked TO analyst;

Verification queries

-- Sanity-check the result before handing the copy over.
SET ROLE analyst;
SELECT * FROM public.customers_masked LIMIT 10;
SELECT * FROM public.customers LIMIT 1;   -- should fail: permission denied
RESET ROLE;

-- Sensitive columns you may have missed (scan by name):
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE column_name ~* '(email|phone|ssn|card|passport|address|birth|password|token)'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2, 3;

Do more than postgres data masking & anonymization sql generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the schema and table, then paste the sensitive column names one per line — a masking rule is guessed from each name.
Adjust any rule that guessed wrong, pick the output mode (masked view, in-place UPDATE, or Anonymizer security labels) and set the role that should see masked data.
Copy the script, run it against a restored copy rather than production, and use the verification queries to confirm no real values survive.

Frequently asked questions

What is the difference between static and dynamic data masking in PostgreSQL?

Static masking rewrites the stored data — you restore a dump into a non-production database and run UPDATE statements, so the real values no longer exist anywhere in that copy. Dynamic masking keeps the real values and rewrites the result at query time based on who is asking, using a view or the PostgreSQL Anonymizer extension. Dynamic masking is right when analysts need live data from the production cluster; static masking is right when you are seeding a dev or test environment, because a copy with no real values in it cannot leak them.

How do I mask data but keep joins working across tables?

Use a deterministic transform rather than a random one. The hash rule in this generator emits encode(sha256((salt || value)::bytea), 'hex'), which maps a given input to the same output every time, so a customer_id masked in one table still matches the same masked value in another. Keep the salt secret and identical across every table in the same anonymisation run, and rotate it between runs so two dumps cannot be correlated.

Is a Postgres view enough to hide sensitive columns?

Only if the underlying table is not also granted to the same role — revoke SELECT on the base table, otherwise the user simply queries around the view. Create the view WITH (security_invoker = true) on PostgreSQL 15 and later so it runs with the caller's privileges and does not bypass row level security. A view is also not a defence against a role that can create functions or read pg_stats, so pair it with least-privilege grants. Chat2DB, a free AI-powered database client, makes it easy to inspect which roles hold which grants before you ship a masked environment: download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.