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.

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.

Interview Questions

Q: Your ETL pipeline applying static masking is taking 3× longer than without masking. What is likely the cause?

Likely causes: the masking functions include expensive operations per row, such as regex substitutions (like regexp_replace for credit card numbers), cryptographic hashing with salt-per-row (recomputes for each row), or external API calls to a masking service (network latency per row). Diagnosis: profile the ETL job with EXPLAIN ANALYZE to identify which masking step is slow. The fix depends on cause: precompute masked values for low-cardinality fields (e.g., hash once for each US state code), use format-preserving encryption instead of regex for structured data (credit cards, SSNs), or shift masking to the load phase where it runs once into the target environment rather than in-flight.

Q: You need to mask data but preserve referential integrity across tables. How do you approach this?

Deterministic masking with a per-table salt solves this. The same input value always produces the same masked value within a table, so foreign key relationships are preserved. Example: mask_email(email, table_salt). If john@example.com masks to a1b2@example.com in the customers table, and customer_id=5 references john@example.com, then a1b2@example.com in orders also references customer_id=5. The relationship is preserved even though the actual email changed. Critical: use different salts per table, otherwise the same email masks to the same value across all tables, allowing cross-table correlation attacks.

Q: Dynamic masking in PostgreSQL is returning null for a column that should be masked but is now showing as null to all users. What happened?

PostgreSQL RLS (Row-Level Security) dynamic masking with current_setting() or session variables can fail silently if the RLS policy is not applied to the user’s role, or if the masking expression references a session variable that is NULL. Common mistake: masking policy defined but not applied (FOR ALL instead of FOR SELECT). The column showing NULL to everyone means the masking expression is evaluating to NULL — check SELECT * FROM pg_policy WHERE polname LIKE '%masking%' and verify the policy applies to the correct roles. Also verify ALTER TABLE ... FORCE ROW LEVEL SECURITY is not overriding the masking policy.

Q: Your masked test database is still violating GDPR because unmasked data appears in logs. How do you prevent this?

Application logs can contain the actual data if developers log request parameters or database query results. The masking strategy must be end-to-end: database masking alone is insufficient. Implementation: audit log statements to detect queries or parameters containing PII, implement log masking at the application logging layer (redact known PII fields before writing to logs), ensure ORM query logging does not output raw parameter values, and test by grepping logs for known test data values after masking deployment. Any environment receiving production-adjacent data must have log-level PII redaction.

---

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"),
]

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.

For deeper compliance guidance, explore our GDPR compliance guide. And for related security topics, see encryption at rest and audit logging.