Dynamic Data Masking in PostgreSQL: Techniques for PII Protection

Why Masking Matters

Your database has dozens of users who need data access but shouldn't see PII: analysts running reports, developers debugging, support staff investigating issues. Without masking, they see raw emails, phone numbers, and addresses.

Data masking solves this by creating a layer between the raw data and the consumer. Authorized roles see real data; everyone else sees masked versions.

Setting Up Masking

Create Masking Rules

SELECT pgcomply.mask('users', 'email', 'partial', ARRAY['postgres', 'dpo']);
SELECT pgcomply.mask('users', 'name', 'full', ARRAY['postgres', 'dpo']);
SELECT pgcomply.mask('users', 'phone', 'partial', ARRAY['postgres', 'dpo']);
SELECT pgcomply.mask('payments', 'iban', 'partial', ARRAY['postgres', 'finance']);

Each call specifies the table, column, masking type, and which roles are exempt (can see real data).

What Gets Generated

pgcomply creates a view named pgcomply.masked_{schema}_{table}:

-- Auto-generated view (simplified)
CREATE VIEW pgcomply.masked_public_users AS
SELECT
  user_id,
  CASE WHEN current_user = ANY(ARRAY['postgres','dpo'])
    THEN email
    ELSE pgcomply._mask_partial(email, 'email')
  END AS email,
  CASE WHEN current_user = ANY(ARRAY['postgres','dpo'])
    THEN name
    ELSE '********'
  END AS name,
  created_at
FROM public.users;

Query the Masked View

As an analyst:

SELECT email, name, phone FROM pgcomply.masked_public_users;

Result:

 email              | name     | phone
--------------------+----------+---------
 al***@e***le.com   | ******** | +4***567
 bo***@g***l.com    | ******** | +4***234

As a DPO (exempt role):

 email              | name        | phone
--------------------+-------------+-------------
 alice@example.com  | Alice Meyer | +49150123567
 bob@gmail.com      | Bob Schmidt | +49150456234

Masking Types Explained

Partial masking preserves the structure while hiding the content. This lets analysts see patterns (like email domains or phone country codes) without revealing identity.

Full masking replaces the entire value with asterisks. Use for names, free-text fields, and anything where structure reveals identity.

Hash masking produces a SHA-256 hash. Useful for joining datasets without exposing the original value — the same input always produces the same hash.

SELECT pgcomply.mask('users', 'tax_id', 'hash', ARRAY['postgres']);
-- Result: a3f1b2c4e5d6f7a8b9c0...

Checking Masking Status

SELECT * FROM pgcomply.masking_status();

Shows all active masking rules with their type, exempt roles, and generated view names.

Summary

Dynamic data masking is the simplest way to protect PII from unauthorized viewing without changing your data model or application code. pgcomply creates role-aware views automatically — point your analytics tools and application roles at the masked views instead of base tables. Setup takes one SQL call per column.