Data Masking in PostgreSQL: Handling Sensitive Data in Test Environments

Data masking means showing personal and sensitive data as altered, partly hidden or fake values instead of the real ones. PostgreSQL offers two main approaches: static masking, where the data copied to test and development environments is changed permanently, and dynamic masking, where the data stays in place and is shown masked only to users without the right permissions. Copying production data to a test environment without masking it is one of the most common data protection risks we see.

This guide is for development teams that feed test environments with production data and for IT managers responsible for compliance with data protection laws such as GDPR and KVKK (Türkiye’s data protection law). We tested the examples on PostgreSQL 18.

Static and Dynamic Masking at a Glance

Why Masking Is Necessary

Restoring a copy of the production database into a test environment is a common way to give developers realistic data. But test environments are rarely protected as well as production: wider access, weaker passwords, external developers and queries nobody logs. Real personal data in these environments makes every security measure in production pointless.

Data protection laws require personal data to be processed only as far as is relevant, limited and proportionate to the purpose. A software test doesn’t need real customers’ national ID numbers.

Step 1: Find the Personal Data

The first step in masking is knowing which columns hold personal or sensitive data. Column names are a good place to start. The pattern below includes common Turkish column names alongside English ones:

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND column_name ~* '(tckn|tc_?kimlik|kimlik|ad_?soyad|full_?name|e_?mail|eposta|telefon|phone|gsm|adres|address|iban|kart|card|dogum|birth|maas|salary)'
ORDER BY table_schema, table_name, column_name;

Column names aren’t enough on their own; free-text fields such as “description” or “notes” can also hold personal data. Run pattern checks on sample data in these fields (for example 11-digit numbers or email addresses). Turn the findings into an inventory table that shows which column is masked with which method.

Step 2: Choose the Masking Method

Static Masking for Test Environments

The simplest and most transparent method is to run a masking script on the copy restored to the test environment. The script must run automatically on every restore and finish before the copy is opened to users.

-- Runs on the TEST copy right after the restore, before anyone gets access
UPDATE customers
SET full_name = 'Customer ' || id,
    email     = 'customer' || id || '@example.test',
    phone     = '0500' || lpad((id % 10000000)::text, 7, '0'),
    tckn      = NULL;

-- Consistent pseudonym: the same input always maps to the same output
UPDATE orders
SET customer_email = 'u_' || left(md5(customer_email || 'project-secret-salt'), 12) || '@example.test';

The salt used for consistent pseudonyms must stay secret; otherwise common email addresses can be tried until they match.

Dynamic Masking for Production

The built-in method: views and column privileges

With PostgreSQL’s built-in features, dynamic masking is done by creating a masked view and granting users access to the view instead of the table:

CREATE TABLE customers (id int PRIMARY KEY, full_name text, tckn text, phone text);
INSERT INTO customers VALUES (1, 'Ayşe Yılmaz', '12345678901', '05321234567');

CREATE VIEW customers_masked AS
SELECT id,
       left(full_name, 1) || '*** ' || left(split_part(full_name, ' ', 2), 1) || '***' AS full_name,
       repeat('*', 7) || right(tckn, 4) AS tckn,
       left(phone, 4) || ' *** ** ' || right(phone, 2) AS phone
FROM customers;

CREATE ROLE support_user LOGIN PASSWORD 'change_me';
REVOKE ALL ON customers FROM support_user;
GRANT SELECT ON customers_masked TO support_user;

-- As support_user:
SELECT * FROM customers_masked;
--  id | full_name | tckn        | phone
--   1 | A*** Y*** | *******8901 | 0532 *** ** 67

This method is simple and transparent, but the application has to query the view instead of the table, and masking rules are managed separately in each view.

Extensions

Whichever tool you choose, the masking rules need to be tied to an inventory, with a process that adds new columns to that inventory.

Ways Masking Can Be Bypassed

Dynamic masking is safe only if an unauthorized user has no path to the underlying data. Check these points:

• Direct table access: the role that uses the masked view must have no privileges on the base table.
• Inference attacks: if filters like WHERE tckn LIKE '1%' work on a masked column, the value can be worked out by guessing.
• Backups and exports: backups contain the real data; access to them must be restricted and they must be encrypted.
• Logs: query logs can contain real values as parameters.

Checklist for the Masking Process

Data Masking with Aryasoft

Aryasoft finds the personal data in your PostgreSQL and SQL Server environments, sets column-level masking rules with you and builds a process that automatically refreshes test environments with masked data. We share our field observations on data governance in our data governance post, and PostgreSQL’s other security layers in our PostgreSQL security guide. Details are on our PostgreSQL services page.

Frequently Asked Questions

Sources

The technical details in this guide were checked against the following sources in November 2026.

1. PostgreSQL documentation: CREATE VIEW
2. PostgreSQL documentation: Privileges
3. PostgreSQL Anonymizer documentation
4. AWS: Using dynamic masking with Aurora PostgreSQL (pg_columnmask)
5. Turkish Personal Data Protection Authority (KVKK)