PostgreSQL data masking: keeping production data out of test copies

Somewhere in your organisation there is a non-production database holding a copy of production data. The customer names are real. So are the national ID numbers. Nobody can say exactly when the copy was taken, and the team using it has no reason to treat it as carefully as production, because it is a test server.

A copy does not stop being personal data because it moved to a test host. It moves into the environment with the weakest access control, the shortest log retention, and the fewest people watching it.

Masking is one answer, and it is narrower than the word suggests. Here is what it actually does in CYBERTEC PGEE, and the limits you want to know before you design around it.

The copy is usually nobody's fault

Developers need data that behaves like production. Generated data keeps failing tests that real data would pass. So a dump happens, and at the time it is the fastest correct answer.

The technical answer is to keep the copy useful and make the sensitive columns useless to whoever reads them.

What column-level masking changes

PGEE masks at the column level. The documented behaviour is specific: production data stays visible and unchanged, and the masking applies to the copies that leave it, such as dumps and masked replication.

In the documented example, a mask is attached to the column itself:

```sql

CREATE TABLE t_product (

id serial,

product_number text,

price numeric,

units_available int

);

ALTER TABLE t_product ALTER COLUMN product_number

SET MASK md5(product_number);

ALTER TABLE t_product ALTER COLUMN units_available

SET MASK units_available * pi();

```

Exports then come out obfuscated:

```bash

pg_dump --masked pgee

```

Two things stand out. The mask is declared next to the schema, not in a script someone runs before taking a copy. And the same definition applies to every masked export, which is what makes it reviewable.

The rule that shapes everything

Only immutable functions are accepted as mask expressions. Volatile functions are rejected.

That restriction decides how the masked copy behaves, and mostly in your favour. The same input always produces the same output, so a masked column keeps its internal consistency. Foreign keys still resolve. Joins still work. Test suites that check counts and relationships still pass.

The same property cuts the other way. A deterministic transformation is closer to pseudonymisation than to randomisation. Two copies taken a month apart will contain the same masked value for the same person, so anyone holding both can link a record across environments. If breaking that link is the goal, the function you choose matters, and choosing it is a security decision rather than a formatting one. md5 appears in the vendor's example, and a hash is deterministic by design.

The copy that subscribes instead of restoring

Not every non-production environment is built from a dump. Some subscribe.

Logical replication can carry masked data to a subscriber while the primary keeps the real values:

```sql

CREATE PUBLICATION mypublication_masked

FOR TABLE t_product WITH (mask = on);

```

That opens a path for an environment to hold realistic data without ever holding a real value.

Where masking stops

Five limits worth writing down before you promise anything.

**Masking changes the copy, not production.** It is an export and replication feature. Production keeps the real values, which is the point, and it also means masking is not a substitute for encryption at rest or for access control on the primary.

**It is defined per column.** If a sensitive value sits inside a longer free-text field, an attachment, or a JSON document, the question is not whether masking is a good idea. It is whether the mask function can find the value at all. Test that before you assume it.

**It does not clean up copies that already exist.** Masking governs future dumps. The test server from three years ago has to be found and handled separately.

**A masked copy still needs an owner.** Distinct credentials, its own logging, and a date after which it is deleted.

**It is not a compliance certificate.** More on that below.

What the vendor maps masking to

CYBERTEC positions masking for regulated environments and lists target industries against frameworks: banking and financial services against PCI-DSS, GDPR and SOX, government and defence against national security regulations, critical infrastructure against NIS2, and healthcare against HIPAA and GDPR. The wider security architecture maps encryption at rest, encryption in transit, and obfuscated non-production copies to different control expectations.

Read that as a map of what the feature is built for, and take it to the people who own the obligation rather than treating it as a conclusion. A product feature does not make an environment compliant. Configuration, access control, logging, retention and process do, and several of those sit outside the database entirely. The useful output of the mapping is a shorter list of questions.

The inventory problem underneath

None of this works without a list.

You cannot classify sensitive columns on a copy you do not know exists, and most organisations lose track of non-production databases somewhere between the second and the fifth. PG-Inventory is Worlber's open-source tool for exactly this, published under the MIT licence: instance records with development, QA and production categories, encrypted credentials, role-based access with two-factor authentication, and probes every two minutes reporting status, version, operating system, CPU and memory. It reads cluster topology from the Patroni REST API. Start with the list, then decide what each entry is allowed to hold.

Where Worlber fits

PGEE is CYBERTEC's product. Worlber's offering includes CYBERTEC's PostgreSQL products and services, and our technical team installs and supports these clusters, including the security features described here. Where the copy sits on a database we already run, the masking design is part of the same engagement. Where a migration comes first, Worlber's migration services run scoping, pre-assessment, detailed assessment, execution and aftercare, and masking belongs in the assessment rather than after cutover.

The part worth remembering

A test copy is the easiest place for a routine support ticket to become a data protection problem. The fix is narrower than people expect and cheaper than they assume, and the first step is not masking. It is knowing which copies exist.

Send the table list from one non-production environment holding live data and Worlber will say which columns PGEE can mask, which need another approach, and whether the immutability rule breaks your joins. If masking is the wrong tool for that copy, we will say so and you keep the analysis. Talk to Worlber.

*Compliance frameworks are named as they appear in CYBERTEC's product documentation, which maps features to obligations. That mapping is not a certification statement, and obligations depend on how a system is configured and operated.*

أرسل لنا بيئة غير إنتاجية واحدة

اختر بيئة واحدة تحمل بيانات حية وأرسل لنا قائمة جداولها. سنحدد الأعمدة التي يمكن لـ PGEE إخفاؤها، والأعمدة التي تحتاج مقاربة مختلفة، وأثر قاعدة الدوال الثابتة على استعلاماتك المرتبطة.

تحدث إلى Worlber