Data Masking: Protecting Sensitive Data in Non-Production Environments

Here’s the problem: developers need realistic data to test features properly, but production data contains PII, financial records, and sensitive information that should never leave the protected production environment.

Data masking solves this. It transforms sensitive data into realistic but fake values.

flowchart LR
    Prod[("Production<br/>Database")]

    subgraph StaticPath["Static Masking Pipeline"]
        Dump[("pg_dump<br/>Production")]
        Mask[("Masking<br/>Transforms")]
        DevDB[("Dev/Test<br/>Database")]
    end

    subgraph DynamicPath["Dynamic Masking Pipeline"]
        Query[("User<br/>Query")]
        RLS[("Row-Level<br/>Security")]
        Masked[("Masked<br/>Result")]
    end

    subgraph MaskingRules["Masking Rules"]
        Null[("Null /<br/>Partial")]
        Hash[("Deterministic<br/>Hash")]
        Shuffle[("Value<br/>Shuffle")]
        FPE[("Format-Preserving<br/>Encryption")]
    end

    Dump --> Mask
    Mask --> DevDB
    Query --> RLS
    RLS --> Masked
    Mask -->|applies| RLS

    Mask -.-> Null
    Mask -.-> Hash
    Mask -.-> Shuffle
    Mask -.-> FPE

    Prod -.->|CDC or<br/>trigger| Mask

Data masking solves this. It transforms sensitive data into realistic but fake values. for “John Smith’s credit card” in a test environment finds nothing—because the data has been masked before reaching that environment.

This guide covers masking strategies, implementation approaches, and compliance considerations.

Introduction

Data masking bridges the gap between having realistic test data and protecting sensitive information from leaving production. Without masking, you either ship PII to non-production environments (compliance violation) or developers work with fake data that does not catch real bugs (quality problem). The right masking strategy solves both.

This guide covers static versus dynamic masking, the main techniques (nulling, shuffling, hashing, format-preserving encryption), when each technique is appropriate, and how to implement masking for GDPR and PII compliance. It also covers the risks — masking that is not deterministic breaks referential integrity, and masking that is reversible is a security risk.

Static vs. Dynamic Data Masking

Static Data Masking

Static masking transforms data at rest in non-production environments. A copy of the database is made, masked, and then made available to lower environments. The masked copy never changes—it’s a snapshot.

Use cases:

• Database copies for development or testing
• Analytics and BI environments
• Offsite backups of non-production data
• Any scenario where production data needs to leave a secured environment

Process:

-- Original production data
SELECT * FROM customers LIMIT 3;
-- id | name          | ssn          | email                  | credit_limit
-- 1  | John Smith    | 123-45-6789  | john.smith@example.com | 5000.00
-- 2  | Jane Doe      | 987-65-4321  | jane.doe@example.com   | 10000.00
-- 3  | Bob Wilson    | 555-12-9999  | bob.wilson@example.com | 7500.00

-- After static masking
SELECT * FROM masked_customers LIMIT 3;
-- id | name          | ssn          | email                     | credit_limit
-- 1  | Sarah Connor  | 111-22-3333  | anon_1@masked.example.com | 3500.00
-- 2  | Mike Johnson  | 444-55-6666  | anon_2@masked.example.com | 8500.00
-- 3  | Lisa Anderson | 777-88-9999  | anon_3@masked.example.com | 6200.00

Key characteristic: The masking operation is irreversible. Original values cannot be recovered from the masked database.

Dynamic Data Masking

Dynamic masking transforms data on-the-fly as it’s queried. The production database remains the source of truth; masking is applied at query time based on the requesting user’s role.

Use cases:

• Production environments with varying access levels
• Applications where users have different privilege levels
• Real-time masking without copy infrastructure
• Partial access scenarios (e.g., support engineers seeing partial SSN)

Use PostgreSQL row-level security:

-- Enable RLS for dynamic masking
ALTER TABLE customer_records ENABLE ROW LEVEL SECURITY;

-- Create masking policy
CREATE POLICY mask_ssn_policy ON customer_records
    FOR SELECT
    USING (
        CASE
            WHEN current_setting('app.user_role', true) = 'admin' THEN true
            WHEN current_setting('app.user_role', true) = 'support' THEN
                ssn IS NOT NULL AND -- Show that SSN exists but mask it
                true
            ELSE
                -- Production app sees full data
                true
        END
    );

-- Application sets user context
SET app.user_role = 'support';
SELECT ssn FROM customer_records LIMIT 1;
-- Returns: ***-**-6789 (masked via application-level view)

Masking Strategies

Nulling (Partial Nulling)

Replace part of the data with null or placeholder characters:

-- Partial email masking
CREATE OR REPLACE FUNCTION mask_email(email TEXT)
RETURNS TEXT AS $$
BEGIN
    IF email IS NULL THEN RETURN NULL;
    RETURN LEFT(email, 2) || '***@***' || RIGHT(email, 4);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Partial phone masking
CREATE OR REPLACE FUNCTION mask_phone(phone TEXT)
RETURNS TEXT AS $$
BEGIN
    IF phone IS NULL THEN RETURN NULL;
    RETURN '***-***-' || RIGHT(REPLACE(phone, '-', ''), 4);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT mask_email('john.smith@example.com');  -- jo***@****.com
SELECT mask_phone('555-123-4567');            -- ***-***-4567

Shuffling (Value Swapping)

Shuffle values within a column so that real values exist but are associated with different records:

-- Shuffle within category to maintain distribution
CREATE OR REPLACE FUNCTION shuffle_column(table_name TEXT, column_name TEXT)
RETURNS void AS $$
DECLARE
    row_count INTEGER;
    temp_values TEXT[];
    shuffled_values TEXT[];
BEGIN
    -- This is a simplified example; production implementations
    -- require transaction-safe shuffling without temp tables

    EXECUTE format('SELECT array_agg(%I) FROM %I', column_name, table_name)
    INTO temp_values;

    -- Fisher-Yates shuffle
    FOR i IN REVERSE array_length(temp_values, 1) .. 1 LOOP
        shuffled_values[i] = temp_values[i];
    END LOOP;

    -- Update would go here in production
END;
$$ LANGUAGE plpgsql;

Important caveat: Shuffling can create unintended associations. A customer’s address might end up with another customer’s name. This may or may not be acceptable depending on your use case.

Hashing

Replace values with one-way hashes. Hashing preserves the ability to identify matching values across tables without revealing the original:

-- Deterministic hashing for consistent masking across tables
CREATE OR REPLACE FUNCTION hash_value(input_text TEXT, salt TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN encode(
        digest(input_text || salt, 'sha256'),
        'hex'
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage: Same input always produces same hash (useful for joining)
SELECT
    customer_id,
    hash_value(ssn, 'your-salt-key') as ssn_hash,
    hash_value(email, 'your-salt-key') as email_hash
FROM customers;

-- Result (deterministic):
-- customer_id | ssn_hash                                | email_hash
-- 1            | a1b2c3d4e5f6...                         | f6e5d4c3b2a1...
-- 2            | 9f8e7d6c5b4a...                         | 1a2b3c4d5e6f...

Security note: Hashing without salt is vulnerable to rainbow table attacks. Use a consistent application salt, and consider HMAC-based hashing for additional security.

Range/Noise

Replace numeric values with values in the same range but with noise added:

-- Credit limit masking: maintain realistic ranges
CREATE OR REPLACE FUNCTION mask_credit_limit(limit NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF limit IS NULL THEN RETURN NULL;
    -- Random value within 80-120% of original
    RETURN ROUND(limit * (0.8 + random() * 0.4) / 100) * 100;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Age masking: maintain demographic validity
CREATE OR REPLACE FUNCTION mask_age(age INTEGER)
RETURNS INTEGER AS $$
BEGIN
    IF age IS NULL THEN RETURN NULL;
    -- Add noise but keep within reasonable bounds
    RETURN GREATEST(18, LEAST(120, age + (random() * 10 - 5)::INTEGER));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Format-Preserving Encryption (FPE)

FPE encrypts data while maintaining its format—a credit card number stays 16 digits, an SSN stays in XXX-XX-XXXX format:

-- Using PostgreSQL pgcrypto for format-preserving encryption
CREATE OR REPLACE FUNCTION fpe_encrypt(plaintext TEXT, key TEXT)
RETURNS TEXT AS $$
BEGIN
    -- XOR-based FPE (simplified - use proper FPE library in production)
    -- This preserves format but is NOT cryptographically secure
    RETURN plaintext;  -- Placeholder - use proper FPE library
END;
$$ LANGUAGE plpgsql;

-- For production: consider libraries like Botan's FPE or commercial solutions
-- that implement NIST SP 800-38G FF1/FF3 algorithms

PostgreSQL Data Masking

PostgreSQL offers several approaches for data masking.

pg_mask Extension

The pg_mask extension provides declarative data masking:

-- Install pg_mask
CREATE EXTENSION pg_mask;

-- Define masking rules
SELECT pg_mask.define_mask(
    'customers',           -- table
    'ssn',                 -- column
    'pg_mask.redact_ssn'   -- masking function
);

Application-Level Masking Views

Create views that apply masking based on session variables:

-- Create masked view
CREATE VIEW customers_masked AS
SELECT
    id,
    name,
    CASE
        WHEN current_setting('app.show_full_ssn', true) = 'true' THEN ssn
        ELSE LEFT(ssn, 3) || '-**-' || RIGHT(ssn, 4)
    END AS ssn,
    CASE
        WHEN current_setting('app.show_full_email', true) = 'true' THEN email
        ELSE LEFT(email, 2) || '***@***.' || RIGHT(email, 3)
    END AS email
FROM customers;

-- Admin sees everything
SET app.show_full_ssn = 'true';
SET app.show_full_email = 'true';

-- Regular user sees masked
SET app.show_full_ssn = 'false';
SET app.show_full_email = 'false';

Using RLS for Dynamic Masking

Row-level security can implement column-level masking:

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

CREATE POLICY sensitive_columns_mask ON customers
    FOR SELECT
    USING (
        -- If user role is 'developer', mask sensitive columns
        CASE
            WHEN current_setting('app.user_role', true) <> 'admin' THEN
                ssn = '***-**-0000' AND
                email = 'masked@example.com'
            ELSE
                true
        END
    );

Oracle Data Masking

Oracle provides Data Masking and Subsetting Pack as part of Oracle Enterprise Manager.

Oracle Static Data Masking

-- Oracle uses DBMS_REDACT for dynamic data masking
BEGIN
    DBMS_REDACT.add_column(
        table_name  => 'CUSTOMERS',
        column_name => 'SSN',
        action      => DBMS_REDACT.ADD_FULL,
        mask_type   => DBMS_REDACT.MASK_FORMAT_SSN
    );

    DBMS_REDACT.add_column(
        table_name  => 'CUSTOMERS',
        column_name => 'CREDIT_CARD',
        action      => DBMS_REDACT.ADD_FULL,
        mask_type   => DBMS_REDACT.MASK_FORMAT_CC
    );
END;
/

-- After enabling redaction, queries return:
-- SELECT ssn, credit_card FROM customers;
-- SSN: ***-**-1234
-- CC: 4111-****-****-1111

Oracle Masking Definitions

-- Create custom masking format
BEGIN
    DBMS_REDACT.create_mask_format(
        mask_format_name => 'MASK_EMAIL',
        description      => 'Email masking format',
        type             => DBMS_REDACT.REGEXP,
        regex_pattern    => '^(.{2}).*@.*(.{3})$',
        regex_replacement => '\1***@\2***'
    );
END;
/

-- Apply custom format
BEGIN
    DBMS_REDACT.alter_column(
        table_name   => 'CUSTOMERS',
        column_name  => 'EMAIL',
        action       => DBMS_REDACT.MODIFY,
        mask_type    => DBMS_REDACT.CUSTOM,
        mask_format  => 'MASK_EMAIL'
    );
END;
/

When to Use / When Not to Use Each Masking Strategy

Use Static Masking when:

• Data leaves a secured production environment entirely
• You need a permanent, non-reversible protected copy
• Developers need realistic data for offline testing

Do not use Static Masking when:

• Data must remain reversible for debugging
• Masking rules change frequently (static copies become stale)

Use Dynamic Masking when:

• You need real-time masking based on user role
• Same environment serves multiple access levels
• Data must not be duplicated in masked form

Do not use Dynamic Masking when:

• Query performance overhead is unacceptable
• Complex masking requires cross-table logic RLS cannot express

Use Shuffling when:

• You need referential integrity across masked tables
• Statistical distribution must be preserved for testing

Do not use Shuffling when:

• Shuffled values could create dangerous false associations (e.g., address + name mismatch)
• Compliance forbids mixing real values across records

Masking Strategy Trade-offs

Dimension	Static Masking	Dynamic Masking	Shuffling	Hashing	Range/Noise
Reversibility	Irreversible	Reversible (role-based)	Irreversible	One-way only	Irreversible
Performance impact	None on production	Moderate (RLS overhead)	None	Low	None
Referential integrity	Breaks unless coordinated	Maintained via RLS	Breaks linkages	Maintained (deterministic)	Breaks linkages
Searchability	Lost	Partial (via RLS)	Lost	Partial (hash joins)	Lost
Storage overhead	Full copy needed	No extra storage	No extra storage	No extra storage	No extra storage
Compliance value	High	Medium	Medium	High	Medium

Production Failure Scenarios

Failure	Impact	Mitigation
Static masked export reaching non-secured environment	PII exposure, GDPR violation	CI/CD gate checks for masked data before release
Dynamic masking bypass via SQL function	Sensitive data visible to unauthorized roles	Test RLS policies with SELECT * queries, deny by default
Shuffling breaking foreign key relationships	Data integrity violations in test	Preserve key relationships during shuffle, test with FK validation
Masking failure in ETL pipeline	Raw PII loaded to test environment	Automated post-load validation, alert on unmasked fields
Deterministic hash collision causing incorrect joins	Wrong data associated across tables	Use salted HMAC, not raw hashing

Capacity Estimation: Masking Overhead in ETL Pipelines

Data masking in ETL pipelines adds CPU overhead proportional to the number of rows processed and the masking function complexity.

Masking throughput formula:

masked_rows_per_second = base_etl_throughput × (1 - masking_overhead_ratio)
masking_overhead_ratio = masking_cpu_time / (masking_cpu_time + etl_cpu_time)

For a simple nulling mask (zero CPU overhead on the database): masking overhead ≈ 0%. For a deterministic HMAC mask (compute hash per row): ~5-10% CPU overhead. For complex regex substitution or format-preserving encryption: ~20-50% overhead.

In a PostgreSQL ETL pipeline masking 1M rows with deterministic HMAC:

• Base ETL throughput without masking: 50,000 rows/second
• With HMAC masking: ~45,000 rows/second (10% overhead)
• Time difference: 20 seconds vs 22 seconds for 1M rows — marginal for batch jobs

But for real-time masking in a web request path: a 10% CPU overhead on each API response could require 10% more application servers. This is why dynamic masking is usually applied at the application layer, not the database layer, for high-traffic systems.

Storage overhead for static masking: If masking creates a masked copy (not in-place), the storage requirement equals the original database size. For a 500GB production database, the masked staging copy is also 500GB. With 10 masking environments (dev, QA, staging × multiple regions), that is 5TB of masked data. Budget for masked data storage alongside production storage.

Quick Recap Checklist

Use this checklist when designing or reviewing data masking for non-production environments:

• Data classification completed for all PII and sensitive fields
• Static masking applied for all data leaving secured environments
• Dynamic masking configured for production with varying access levels
• Per-table salts used for deterministic masking to preserve referential integrity
• FPE used for format-dependent fields (credit cards, SSNs)
• Shuffling restricted to low-risk data categories only
• Log masking implemented at application layer
• CI/CD gates verify masking before environment promotion
• Masking validated post-ETL to confirm no unmasked PII remains
• Masking strategy reviewed quarterly for new data fields

---

Non-Production Environment Protection

Masking non-production data requires multiple layers:

CI/CD Pipeline Integration

# Example: Masking data before environment promotion
stages:
  - name: build
    script:
      - run_tests

  - name: data_prep
    script:
      - ./scripts/mask_database.sh staging
    only:
      - main

  - name: deploy_staging
    script:
      - deploy_to_staging
    depends: [data_prep]

#!/bin/bash
# mask_database.sh - Apply masking to database copy
set -euo pipefail

TARGET_ENV=$1
CONFIG_FILE="/etc/masking/${TARGET_ENV}.conf"

echo "Starting data masking for ${TARGET_ENV}..."

# Dump production (or use replica)
pg_dump -h prod-db.internal -U backup_user -d mydb -F c -f /tmp/prod_backup.dump

# Create masked copy
pg_restore -h ${TARGET_ENV}-db.internal -U app_user -d mydb_masked /tmp/prod_backup.dump

# Apply masking transformations
psql -h ${TARGET_ENV}-db.internal -U app_user -d mydb_masked << 'EOF'
    -- Apply masking functions
    UPDATE customers SET
        ssn = mask_ssn(ssn),
        email = mask_email(email),
        credit_limit = mask_credit_limit(credit_limit);

    -- Shuffle addresses to break linkage with names
    UPDATE customer_addresses SET
        address = (SELECT address FROM customer_addresses ORDER BY random() LIMIT 1);

    -- Verify masking applied
    SELECT
        'SSN values are masked: ' ||
        CASE WHEN ssn LIKE '%***%' THEN 'YES' ELSE 'NO' END,
        'Email values are masked: ' ||
        CASE WHEN email LIKE '%***@***%' THEN 'YES' ELSE 'NO' END
    FROM customers LIMIT 1;
EOF

# Clean up
rm /tmp/prod_backup.dump

echo "Data masking complete for ${TARGET_ENV}"

Environment Segmentation

Not all environments need the same data:

Environment	Data Required	Masking Level
Development	Schema structure only	None (empty tables)
Dev Integration	Basic functionality	Shallow (null out non-essential)
QA	Full test coverage	Moderate (key fields)
Staging	Production-like	Full (all PII/financial)
UAT	Client demo scenarios	Custom per client
Production	Everything	None (protected by access controls)

GDPR and PII Considerations

GDPR doesn’t prohibit using personal data in non-production—it requires appropriate safeguards:

• Data minimization: Don’t copy more data than needed
• Purpose limitation: Use data only for its intended purpose (testing)
• Security measures: Appropriate technical measures (masking)
• Contractual terms: Data processing agreements with vendors

Data Classification Framework

from enum import Enum
from dataclasses import dataclass
from typing import Optional, List

class SensitivityLevel(Enum):
    PUBLIC = 1        # No masking needed
    INTERNAL = 2      # Basic masking (internal use only)
    CONFIDENTIAL = 3  # Full masking (names, emails, etc.)
    RESTRICTED = 4    # Maximum protection (SSN, financial, health)

@dataclass
class DataField:
    name: str
    sensitivity: SensitivityLevel
    masking_strategy: str
    masked_example: str

# Example classification
PERSONAL_DATA_FIELDS = [
    DataField("full_name", SensitivityLevel.CONFIDENTIAL, "initials", "J.S."),
    DataField("ssn", SensitivityLevel.RESTRICTED, "partial", "***-**-1234"),
    DataField("email", SensitivityLevel.CONFIDENTIAL, "domain_mask", "j***@***.com"),
    DataField("phone", SensitivityLevel.CONFIDENTIAL, "last_four", "***-***-5678"),
    DataField("date_of_birth", SensitivityLevel.INTERNAL, "year_only", "1985"),
    DataField("salary", SensitivityLevel.RESTRICTED, "range", "$50k-$75k"),
    DataField("address", SensitivityLevel.CONFIDENTIAL, "city_only", "Austin, TX"),
]

---

Interview Questions

---

Further Reading

• GDPR Compliance — Data protection requirements
• Encryption at Rest — Protecting stored data
• Audit Logging — Tracking data access

---

Conclusion

Data masking transforms sensitive data into protected formats that remain useful for testing and development. Static masking creates irreversible protected copies for non-production environments. Dynamic masking applies transformations in real-time based on user context.

Choose masking strategies that match your use case—nulling for identifiers, hashing for cross-table joins, range/noise for numeric values, and FPE when format preservation is critical.

Masking is one layer of a defense-in-depth strategy. Combined with environment segmentation, access controls, and data minimization, it significantly reduces the risk of data exposure in non-production environments.