How to Mask Data in PostgreSQL

How do I mask sensitive data such as emails in PostgreSQL without duplicating tables?

Data masking hides sensitive column values or entire rows from unauthorized users by means of views, row-level security, and conditional expressions.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why should I mask data in PostgreSQL?

Masking limits exposure of PII, PCI, or financial fields while letting analysts query the same tables. It supports GDPR, HIPAA, and SOC2 requirements without duplicating data.

Which masking technique fits my use case?

Use security-barrier views for column masking, Row-Level Security (RLS) for row filtering, or a combination when both column and row restrictions apply.

How do I mask sensitive columns with a view?

Create a SECURITY_BARRIER view that shows real values to admins and obfuscated values to others, then revoke direct access to the base table.

Step-by-step email masking example

1. CREATE ROLE read_only_role; 2. REVOKE ALL ON customers FROM PUBLIC; 3. CREATE VIEW customers_masked WITH (security_barrier) AS SELECT id, CASE WHEN current_user = 'admin' THEN email ELSE regexp_replace(email,'[^@]+','***') END AS email, created_at FROM customers; 4. GRANT SELECT ON customers_masked TO read_only_role;

How do I hide entire rows with RLS?

Enable RLS, then write a SELECT policy that returns rows only when they belong to the current user or role.

Step-by-step order filtering example

1. ALTER TABLE orders ENABLE ROW LEVEL SECURITY; 2. CREATE POLICY orders_own ON orders FOR SELECT USING (customer_id = current_setting('app.current_customer')::int); 3. SET app.current_customer = 42 before queries.

What are best practices for data masking?

Grant privileges on the masked view, not the base table; always add SECURITY_BARRIER; test with EXPLAIN to verify predicate pushdown; audit roles regularly.

Can I combine masking and encryption?

Yes. Store raw values encrypted with pgcrypto and expose a masked or decrypted view depending on user role, giving double protection.

Why How to Mask Data in PostgreSQL is important

How to Mask Data in PostgreSQL Example Usage


-- Analyst logged in as read_only_role
SELECT id, email
FROM customers_masked
WHERE id = 7;
-- Result: 7 | ***@galaxy.dev

-- Admin user sees full value
SET role admin;
SELECT id, email FROM customers_masked WHERE id = 7;
-- Result: 7 | alice@example.com

How to Mask Data in PostgreSQL Syntax


-- Column masking with a security-barrier view
CREATE OR REPLACE VIEW customers_masked
    WITH (security_barrier)
AS
SELECT id,
       CASE
           WHEN current_user = 'admin'
           THEN email
           ELSE regexp_replace(email, '[^@]+', '***')
       END AS email,
       created_at
FROM customers;

-- Grant access only to the masked view
REVOKE  ALL ON customers        FROM PUBLIC;
GRANT   SELECT ON customers_masked TO read_only_role;

-- Row-level security example
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY orders_own ON orders
FOR SELECT
USING (customer_id = current_setting('app.current_customer')::int);

-- Set session variable before running the query
SET app.current_customer = 42;

Common Mistakes

Using a plain view instead of a SECURITY_BARRIER view. Without the flag, PostgreSQL can push down filters that bypass your masking expression. Always add WITH (security_barrier).
Granting SELECT on the base table after creating the masked view. Users then bypass masking entirely. Revoke all rights on the base table and grant only on the view.

Frequently Asked Questions (FAQs)

Is masking reversible?

Yes for privileged roles; a view can reveal full data to admins while showing masked data to others. Completely irreversible masking requires overwriting the column.