Skip to main content

Data Masking

Dynamic data masking replaces sensitive column values with masked versions at query time. Masking is applied by a query-rewriter that injects the masking expression into the SELECT output list — the raw value never reaches an unauthorized reader.

Policy definition and column attachment are separate steps. A policy is a named, reusable transform; attaching a policy to a column is a per-column ALTER. The same policy can be attached to many columns across many tables.

Syntax at a Glance​

-- Define the policy
CREATE [OR REPLACE] MASKING POLICY <name>
    AS (<input_type>) RETURNS <return_type>
    -> <expression>;

-- Attach to a column
ALTER TABLE <table>
    ALTER COLUMN <column>
    SET MASKING POLICY <name>
    [ EXEMPT <role1>, <role2>, ... ];

-- Detach
ALTER TABLE <table>
    ALTER COLUMN <column>
    UNSET MASKING POLICY;

-- Drop the policy (must be detached from all columns first,
-- unless CREATE OR REPLACE is updating it in place)
DROP MASKING POLICY [IF EXISTS] <name>;

CREATE MASKING POLICY​

The policy body is a SQL expression. Reference the column being masked as $value — the rewriter substitutes the actual column name at query time, so the same policy can be attached to different columns.

CREATE MASKING POLICY policy_email AS (STRING) RETURNS STRING
    -> concat(left($value, 2), '***@', split_part($value, '@', 2));

CREATE MASKING POLICY policy_phone AS (STRING) RETURNS STRING
    -> concat('XXX-XXX-', right($value, 4));

CREATE MASKING POLICY policy_ssn AS (STRING) RETURNS STRING
    -> concat('***-**-', right($value, 4));

CREATE MASKING POLICY policy_cc AS (STRING) RETURNS STRING
    -> concat('****-****-****-', right($value, 4));

CREATE MASKING POLICY policy_full_redact AS (STRING) RETURNS STRING
    -> '[REDACTED]';

CREATE MASKING POLICY policy_nullify AS (STRING) RETURNS STRING -> NULL;

<input_type> and <return_type> both accept the usual type names (STRING, BIGINT, DECIMAL(p,s), BOOLEAN, DATE, TIMESTAMP, BINARY, etc.). They don't have to match — you can hash a STRING into a BINARY by returning a different type.

CREATE OR REPLACE​

CREATE OR REPLACE rewrites the policy's body in place, preserving every column attachment. The next query after the replace uses the new expression. Existing attached columns keep working with no detach/reattach dance.

-- Iterate on the expression while the policy is attached to 50 columns
CREATE OR REPLACE MASKING POLICY policy_email AS (STRING) RETURNS STRING
    -> concat(left($value, 3), '...@', split_part($value, '@', 2));

-- Next SELECT against any attached column uses the new body
SELECT email FROM customers WHERE id = 1;

Without OR REPLACE, re-creating a policy with an existing name fails with Policy already exists: <name>.

Identifier case​

Policy names follow the Postgres identifier convention — unquoted names fold to lowercase, double-quoted names preserve case.

CREATE MASKING POLICY p_email ...;   -- stored as "p_email"
CREATE MASKING POLICY P_EMAIL ...;   -- FAILS: "Policy already exists: p_email"
CREATE MASKING POLICY "P_email" ...; -- stored as "P_email" (distinct object)

DROP MASKING POLICY P_EMAIL;         -- finds p_email (case-insensitive)
DROP MASKING POLICY "P_email";       -- drops the quoted-ident object

Attaching and Detaching Policies​

Attach a policy to a column with ALTER TABLE ... ALTER COLUMN ... SET MASKING POLICY. The policy must already exist, the column must exist, and the policy's input_type and RETURNS type must be compatible with the column's data type. The compatibility check is coarse-grained — STRING covers both VARCHAR and CHAR, INT covers the smaller integer widths, LONG covers BIGINT, etc. — so policies can be reused across same-family columns, but a cross-family attach (e.g. RETURNS INT on a VARCHAR column) is rejected up front.

ALTER TABLE customers ALTER COLUMN email
    SET MASKING POLICY policy_email;

ALTER TABLE customers ALTER COLUMN email
    SET MASKING POLICY policy_email
    EXEMPT admin, data_engineer;   -- roles listed here bypass the mask

ALTER TABLE customers ALTER COLUMN email UNSET MASKING POLICY;

Policy-name lookup in SET MASKING POLICY is case-insensitive, matching the CREATE-time folding rule.

Attach-time type validation​

Attaching a policy whose input_type or RETURNS type doesn't match the column's data type now fails with a clear bind-time error. Earlier the attach silently succeeded but at query time the projection's declared type and the produced values disagreed, so every downstream cast, CTAS, comparison, and INSERT … SELECT broke in a hard-to-diagnose way.

-- VARCHAR column, but policy returns INT — rejected at ALTER time
CREATE MASKING POLICY mp_bad AS (VARCHAR) RETURNS INT -> 42;
ALTER TABLE customers ALTER COLUMN ssn SET MASKING POLICY mp_bad;
-- ERROR: Masking policy 'mp_bad' return type Int is incompatible
--   with column 'ssn' of type String — applying this policy would
--   silently change the column's projected type

-- Same-type policy attaches cleanly
CREATE MASKING POLICY mp_ok AS (VARCHAR) RETURNS VARCHAR -> '***';
ALTER TABLE customers ALTER COLUMN ssn SET MASKING POLICY mp_ok;

Use CREATE OR REPLACE MASKING POLICY to change a policy body in-place; to change the declared RETURNS type, detach every column first.

The $value Placeholder​

Inside the masking expression, $value is replaced with the column name being masked. The substitution is textual and happens when the rewriter resolves the policy for a specific column — so the same policy works for any column of a compatible type:

CREATE MASKING POLICY last4 AS (STRING) RETURNS STRING
    -> concat('****-', right($value, 4));

-- Attach to any STRING column
ALTER TABLE customers ALTER COLUMN ssn         SET MASKING POLICY last4;
ALTER TABLE customers ALTER COLUMN phone       SET MASKING POLICY last4;
ALTER TABLE customers ALTER COLUMN credit_card SET MASKING POLICY last4;

At query time the expression becomes concat('****-', right(ssn, 4)) / concat('****-', right(phone, 4)) / etc., depending on the column.

Supported Expression Kinds​

The masking rewriter parses the policy body at query-rewrite time and lowers it to a logical expression tree. The following AST node kinds are supported today:

AST kindExample
String / integer / float / boolean / NULL literals'[REDACTED]', 42, NULL
Column reference ($value or another bare column name)$value, email
Function call (including nullary like uuid())concat(...), left($value, 2), sha256($value)
Binary operator (comparison / logical / arithmetic / ||)$value = 'x', 'Dear, ' || $value, $value + 1
Unary operator (NOT, unary + / -)NOT ($value = 'x')
CASE WHEN ... THEN ... ELSE ... END (searched and simple)CASE WHEN right($value,4) IN (...) THEN $value ELSE '***' END
CAST / TRY_CASTcast(length($value) as string)
IS NULL / IS NOT NULL$value IS NULL
IN (list, ...)$value IN ('x', 'y')
POSITION(x IN y)position('-' in $value)
Parenthesized expressions (transparent)($value + 1) * 2

Not supported today​

Expression kinds the lowerer rejects with a clean unsupported masking expression planning error:

  • EXISTS (SELECT ...) and scalar subqueries
  • ANY / ALL / SOME subqueries
  • LIKE, ILIKE, SIMILAR TO, RLIKE (workaround: regexp_match / regexp_replace function calls)
  • BETWEEN (workaround: $value >= a AND $value <= b)
  • Window functions
  • Lambda / array / map / struct expressions
  • CONVERT ... USING / CAST with non-standard target types

current_role() inside a policy body lowers correctly but fails at query execution because the function is not yet registered in the executor's scalar UDF registry. Role-conditional masking via EXEMPT in the ALTER attachment is the supported path today.

Error handling​

When the policy body contains an unsupported construct, the first SELECT that touches the masked column fails at the planner with a message that names the column and echoes the source SQL — the policy storage isn't affected, so you can iterate with CREATE OR REPLACE without cleanup:

Query planning error: unsupported masking expression for column email:
expression kind not supported in masking policies: Discriminant(N)
(source SQL: "CASE WHEN EXISTS (SELECT ...) THEN $value ELSE '***' END")

Query Semantics​

Masking fires at the scan output, not at the top-level SELECT list. Every downstream operator — Filter, Join ON, GROUP BY, ORDER BY, scalar subqueries, CTAS / INSERT…SELECT — sees the masked value, never the raw column. This matches standard PostgreSQL data-masking semantics and closes a family of silent-leak shapes that the older output-boundary design left open.

What this means in practice​

-- WHERE matches against the MASKED value, not the raw email.
-- A binary-search PII oracle (`WHERE ssn = '111-22-3333'`) returns
-- zero rows because the literal compares against `'***-**-3333'`, not
-- the underlying string.
SELECT email FROM customers WHERE email = 'alice@acme.com';   -- 0 rows

-- Join key sees masked values on both sides. If the policy is
-- idempotent (e.g. constant `'[REDACTED]'`), every row matches every
-- row — a noisy result, but no cross-table value confirmation leak.
SELECT o.order_id, c.email
FROM orders o
JOIN customers c ON c.email = o.billing_email;

-- COUNT(DISTINCT) returns the true masked cardinality (usually 1 for
-- a constant mask), not the raw cardinality. Use this to reason about
-- the policy's k-anonymity / discriminability before rolling it out.
SELECT COUNT(DISTINCT email) FROM customers;

-- ORDER BY orders by the masked value — masked rows sort together
-- rather than leaking the raw ordering of underlying records.
SELECT * FROM customers ORDER BY email;

-- CTAS / INSERT…SELECT persists MASKED values to the destination.
-- A user who could read the masked view of `customers` cannot
-- materialise the raw data into a new table by writing
-- `CREATE TABLE leak AS SELECT * FROM customers`.
CREATE TABLE customer_copy AS SELECT * FROM customers;

The masking expression is wrapped over each Scan in the logical plan, so column references resolve to the masked value everywhere downstream by name. The Iceberg field-id-aware Join reorderer is masking-aware (it refuses to strip Projects that rebind a column name with a non-passthrough expression), and the CTAS / INSERT … SELECT dispatch path runs the masking rewriter on its inner query before the row data is materialised. There is no escape hatch via predicate pushdown, derived tables, subqueries, or generative-AI / vector-search side channels.

Trade-offs​

  • Predicate selectivity: a WHERE on a masked column can't match raw values, so per-row pre-filters are limited to whatever expression the mask produces (often '***' or '[REDACTED]'). For aggregation queries this is usually fine; for narrow point-lookups, design the policy with that in mind.
  • Index pruning: file / row-group statistics-based pruning still works on raw values for predicates that the dispatcher determines to be safe (filters that don't reference a masked column). Filters that touch a masked column are lifted above the scan-wrap so they evaluate against masked output — they don't drive pruning but they also can't leak.
  • EXEMPT roles bypass the wrap entirely: the bare Scan flows up unchanged, so predicates and joins still see raw data. This is the intended escape hatch for analysts who should see the raw column.

EXEMPT Roles​

The EXEMPT <role, ...> clause on ALTER TABLE ... SET MASKING POLICY lists roles that bypass the policy — members of those roles see the raw column value.

ALTER TABLE customers ALTER COLUMN email
    SET MASKING POLICY policy_email
    EXEMPT admin, data_engineer, compliance;

Role names are preserved as-written (not case-folded) so admin and Admin are distinct. Match the case you use in GRANT ROLE.

Matching respects the full RBAC effective-role closure: a caller is exempt if any name in their session-active role set (direct grants + hierarchy, filtered by USE ROLE / USE SECONDARY ROLES) appears in the EXEMPT list. Granting senior_analyst an exemption also exempts anyone who holds a role that inherits from senior_analyst. USE SECONDARY ROLES NONE drops non-primary roles from the matching set, which can cause a previously-exempt session to start seeing masked values.

Complete End-to-End Example​

-- Sample table.
CREATE TABLE customers (
    id          BIGINT,
    full_name   STRING,
    email       STRING,
    phone       STRING,
    ssn         STRING,
    credit_card STRING
);

INSERT INTO customers VALUES
    (1, 'Alice Johnson', 'alice@acme.com',   '415-555-0142', '111-22-3333', '4111111111111234'),
    (2, 'Bob Martinez',  'bob@globex.com',   '212-555-0199', '222-33-4444', '5500000000005678'),
    (3, 'Carol Chen',    'carol@initech.io', '650-555-0170', '333-44-5555', '340000000009876');

-- Masking policies.
CREATE MASKING POLICY policy_email AS (STRING) RETURNS STRING
    -> concat(left($value, 2), '***@', split_part($value, '@', 2));
CREATE MASKING POLICY policy_phone AS (STRING) RETURNS STRING
    -> concat('XXX-XXX-', right($value, 4));
CREATE MASKING POLICY policy_ssn AS (STRING) RETURNS STRING
    -> concat('***-**-', right($value, 4));
CREATE MASKING POLICY policy_cc AS (STRING) RETURNS STRING
    -> concat('****-****-****-', right($value, 4));
CREATE MASKING POLICY policy_name AS (STRING) RETURNS STRING -> '[REDACTED]';

-- Attach one per column.
ALTER TABLE customers ALTER COLUMN email       SET MASKING POLICY policy_email;
ALTER TABLE customers ALTER COLUMN phone       SET MASKING POLICY policy_phone;
ALTER TABLE customers ALTER COLUMN ssn         SET MASKING POLICY policy_ssn;
ALTER TABLE customers ALTER COLUMN credit_card SET MASKING POLICY policy_cc;
ALTER TABLE customers ALTER COLUMN full_name   SET MASKING POLICY policy_name;

-- SELECT returns masked values.
SELECT * FROM customers ORDER BY id;
--  id | full_name  | email            | phone        | ssn         | credit_card
--  ---+------------+------------------+--------------+-------------+--------------------
--   1 | [REDACTED] | al***@acme.com   | XXX-XXX-0142 | ***-**-3333 | ****-****-****-1234
--   2 | [REDACTED] | bo***@globex.com | XXX-XXX-0199 | ***-**-4444 | ****-****-****-5678
--   3 | [REDACTED] | ca***@initech.io | XXX-XXX-0170 | ***-**-5555 | ****-****-****-9876

Management Queries​

-- List policies (all columns that have a mask attached).
SHOW MASKING POLICIES;
SHOW MASKING POLICIES ON customers;

For joinable / filterable introspection, every active column-masking binding also surfaces in INFORMATION_SCHEMA.POLICY_REFERENCES alongside row-access policies:

SELECT policy_name, ref_column_name
FROM my_catalog.information_schema.policy_references
WHERE ref_entity_name = 'customers'
  AND policy_kind     = 'MASKING_POLICY';

Teardown​

To remove a policy, detach it from every column first, then drop:

ALTER TABLE customers ALTER COLUMN email       UNSET MASKING POLICY;
ALTER TABLE customers ALTER COLUMN phone       UNSET MASKING POLICY;
-- ... repeat for each attachment

DROP MASKING POLICY IF EXISTS policy_email;
DROP MASKING POLICY IF EXISTS policy_phone;

DROP MASKING POLICY refuses when the policy is still attached to any column (Cannot drop policy '<n>': still in use by columns: <...>), protecting against silently breaking every attached column. Use CREATE OR REPLACE for in-place body changes while attached.

Reference​

  • Parser syntax: see DDL reference.
  • Identifier case rules: unquoted → lowercase, double-quoted → preserve (Postgres convention).
  • Leading SQL comments (-- / /* */) before a CREATE / DROP / ALTER statement are permitted.
Last updated on