PostgreSQL Test Data Masking: Check Foreign Keys and Residuals

During PostgreSQL test data masking, foreign keys can stay valid while identifying-looking text remains in a demo copy. That is why a useful check asks three questions: did selected identifiers change, do the intended relationships survive, and what information did the transformation leave behind? A successful export is not an answer to all three.

We built a wholly synthetic mini-store and executed an original Python reference transformation three times. The fixture contains 90 rows. It preserves the checked relationships but deliberately leaves 30 fictional email-containing fields unchanged per run. We did not install or execute Brume, access PostgreSQL or process any real customer record.

What the fresh Brume announcement establishes

The 7 November maintainer announcement describes Brume 2.1 as a tool for producing transformed PostgreSQL test copies. It lists compatibility with versions 12–18, keyed deterministic transformations and consistent rewriting of linked identifiers. These are maintainer-described capabilities, not results of our independent product test.

Counting the named strategies gives seven: FAKE, MASK, HASH, NULLIFY, FPE_ID, FPE_UUID and DATE_SHIFT. A count of strategy names is not a privacy score. We also separate the stated version compatibility from a recommendation to use an older server release; choose an appropriately supported environment for your project.

The maintainer documentation explains linked columns and configurable nested-field paths. Those features motivate an audit beyond obvious primary-key columns. They do not mean every field in a particular project has been classified or transformed correctly. Our teaching implementation below is separate code and does not reproduce Brume’s format-preserving encryption.

Build a small fixture without copying people

The mini-store has 10 invented customers, 20 orders and 40 order lines. An extra 20 audit rows repeat the fictional customer emails. That gives 90 original rows across four tables. Each customer has two orders, and each order has two lines. The audit email has an application-level relationship without a declared foreign key.

A nested customer JSON field and a free-text audit note also contain the same fictional contact string. We deliberately leave those fields unchanged. This creates a visible counterexample: fixing selected identity columns and retaining joins can coexist with incomplete field coverage. Nothing in the exercise exposes an actual person.

The accompanying SQL file describes a PostgreSQL-oriented schema with text keys and foreign-key relationships. It is a design aid, not an executed database import. The actual run uses Python records and explicit relationship checks in memory. No server, container, database connection, user account or customer export is required.

PostgreSQL test data masking: foreign keys need consistent mappings

Our reference function uses keyed HMAC-derived tokens with separate customer, order, line, audit and email namespaces. A customer identifier is transformed with the same customer namespace wherever it appears. The repeated email uses the same email namespace in the customer and audit records. This is a teaching mapping, not a production privacy system.

The PostgreSQL foreign-key reference explains why a referencing value must match an appropriate parent value. Our memory checks test that property for the selected rows. However, merely finding some existing parent is insufficient: a faulty mapping could join a child to the wrong existing parent. Inspect intended identity as well as orphan counts when adapting the fixture.

Do not disable a constraint just to make a transformed import finish. A failed relationship check is evidence to investigate. Preserve source-to-target mapping rules in private review materials and use an isolated synthetic copy when learning. Avoid copying secrets into an example configuration or publishing them with a portfolio.

Three runs separate repeatability from key changes

Run A uses a fresh in-memory test key. Next, run B repeats the same transformation with that key. Finally, run C uses a different newly generated key. The harness never writes the key values. A and B produced identical transformed records in the retained run; changing the key changed all 10 customer identifiers in C.

Each run retained zero orphaned order-to-customer references, zero orphaned line-to-order references, zero duplicate customer identifiers and zero linked-email mismatches in this selected fixture. The identity comparisons also found zero unchanged customer IDs and zero unchanged primary email fields. These are narrow observed checks on our own code, not Brume compatibility or security measurements.

Fresh random keys mean a rerun will generate different pseudonym strings. The reproducible result is the fixture structure, the same-key equality check and the declared count checks. The pack retains the original output CSVs so readers can inspect the reported run. We do not claim byte-identical pseudonyms across independent reruns.

Count the residual fields that a clean join misses

Ten customer JSON contact fields and 20 audit-note fields still contain their original fictional email string in every run. The total is 30 field occurrences per transformed copy. These are two kinds of field, not 30 people or 30 unique email addresses. Repeated occurrences matter because the same value can surface through different views and exports.

A real field-risk register should also consider dates, rare combinations, free text, nested objects and partial masks. We do not model those risks comprehensively here. A low orphan count cannot establish anonymity, prevent all reidentification or establish compliance with Nigerian law. No legal-compliance assessment, privacy attack or independent reidentification study was performed.

Reproduce the check and inspect its boundaries

Download the synthetic masking check harness (ZIP). It includes the original Python script, proposed SQL schema, README, license and retained input, transformed-output and check ledgers. Run python3 reference_masking.py output from the code folder with Python 3.9 or newer. It uses only the standard library and makes no network calls.

The program generates invented records and fresh local keys, then asserts the documented structural results. Review the deliberate omissions before adapting it. Its truncated tokens are not format-preserving encryption, and a ten-customer uniqueness check is not a collision or cryptographic-security study. Do not present this small harness as a substitute for a reviewed transformation tool.

• Ninety-row synthetic input ledger (CSV)
• Three-run transformed reference records (CSV)
• Twenty-four executed count checks (CSV)
• Field-risk and residual-disclosure register (CSV)

The 24 count-check rows are eight named checks across three runs. The same-key equality and different-key customer-ID comparison are recorded separately in the summary JSON inside the pack. The 270 transformed rows repeat the original 90-row fixture three times and must not be treated as an independent statistical sample.

Use the right evidence in a Nigerian portfolio

A useful portfolio explains what the learner built and tested: fixture counts, relationship rules, selected identity changes, deliberate residual fields and explicit limits. It should not show copied customer records or claim that a maintainer’s compliance discussion proves the learner’s application meets Nigerian law. Describe unresolved fields before offering a dataset to other people.

Our pgAdmin export-encoding study concerns whether exported text preserves its meaning, a different test intent. The pgBadger count-reconciliation fixture similarly shows why a successful command should be checked against an independent expected result.

Method, limitations and citation

Research and local reference runs: 11 November 2026. We checked the maintainer announcement, product documentation and PostgreSQL constraint reference. We ran only the original Python transformation and its synthetic assertions. No Brume binary, PostgreSQL server, production data, timing benchmark or legal review result is represented by the figures.

Suggested citation: Nigeria Coding Academy, “PostgreSQL Test Data Masking: Check Foreign Keys and Residuals,” 11 November 2026. Attribute Brume capability statements to the maintainer and our reference results to the supplied teaching code. Send corrections with evidence through our contact page.