Cookie settings

Necessary, always on: they keep the site working and remember your cookie choice.

Skip to content
Skip to content

Guide. Test data. Personal data.

How to find production personal data in your test databases

List every non-production copy, test it for real customer records without moving production values, and record the decision for each copy.

Dipal Panchal

Co-founder and CEO · Updated November 8, 2026

On this page

The rule that decides whether a copy holds real data

A test or development database holds real customer data when someone restores a production backup to chase a bug or load a demo. The copy then sits outside the controls you built for production.

Treat a copy as holding production data when any one test below is positive, and treat a copy as clean only when all of them are negative on a sample big enough to matter.

  • Overlap: the keyed hash of a value in the copy matches the keyed hash of a production value
  • Domains: email addresses end in domains outside the ones you reserve for test data
  • Cards: card-number columns hold values that are not your payment provider’s published test numbers
  • Shape: table and column names match production, row counts are close and no masking pattern shows

Who runs the checks, and what they need

The SQL here targets PostgreSQL 16, 17 and 18. Sylox ran both scripts in its lab on versions 16, 17 and 18, on synthetic data. The same tests work on other engines, but the SQL changes.

The database administrator (DBA) runs the checks, the copy’s owner decides, and the security or privacy lead signs the exceptions. Expect an afternoon for the first copy. The overlap test is the slow part, because it needs a secret key and a hash list that you handle with care.

  • A list of environments and a named owner for each
  • A read-only role on the production replica, and a role that can read the copy
  • A check that pgcrypto is installed where the keyed hash is computed (a trusted extension from PostgreSQL 13)
  • A secret key for the hash, held in your secrets store and never in a ticket

What the law and the standards ask for

Article 25(2) of the GDPR requires that, by default, only the personal data necessary for each specific purpose is processed. Article 5(1)(c) sets the same limit as a principle. The UK GDPR carries both articles in the same terms. A full production restore in a test database is hard to defend under either, because the testing purpose rarely needs real people’s records.

For card data, PCI DSS v4.0.1 requirement 6.5.5 says live primary account numbers are not used in pre-production environments, except where those environments are in scope and protected to the same standard. The tests below are Sylox’s recommended way to show the exception does not apply.

Step 1: list every non-production copy

Start with the environments people name: staging, user acceptance testing, quality assurance and development. Then look for the ones they forget: analytics sandboxes, restored backups, databases created by build pipelines and copies made for a vendor.

Cloud account inventories and backup restore logs find copies that no team lists. Give each copy an owner and a purpose. A copy nobody owns is the first candidate for deletion, and the glossary entry on shadow data explains why such copies are easy to lose.

Step 2: compare schemas and row counts with production

Run queries 1 to 3 below on the copy and on production, and compare the results. Tables such as customers, users, payments and addresses with matching columns are the likely copies. Row counts close to production on the same date point to a full restore rather than a sample.

Matching schemas don’t prove real data, because test fixtures reuse the same tables. They tell you where to look first. Query 3 reads estimated row counts from the statistics views, so compare orders of magnitude and not exact figures.

Step 3: test for signs of real records

Check email domains first. Good synthetic data uses the names RFC 2606 reserves for examples: example.com, example.net, example.org and the top-level names example, test, invalid and localhost. Query 4 counts addresses outside them, so any real customer domain stands out. Add your own test domains to the pattern.

Then check card numbers against the published test numbers of your payment provider. Query 5 counts values outside a short list, and you extend the list with your provider’s. A value that passes the Luhn check and isn’t on the list deserves a closer look, but generators make Luhn-valid numbers too, so confirm with the overlap test.

Checks 1 to 6 on a PostgreSQL copy

Read-only. Run them as a role that can read the copy, and run queries 1 to 3 on production too; replace the table and column names with yours.

SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 9, 2026, as the postgres superuser, against synthetic data.
-- 1. Databases on this server, with owner and size
SELECT datname, pg_get_userbyid(datdba) AS owner, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database WHERE NOT datistemplate ORDER BY 1;

-- 2. Columns per table, to compare with production
SELECT table_schema, table_name, count(*) AS columns
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
GROUP BY 1, 2 ORDER BY 1, 2;

-- 3. Estimated rows per table, to compare with production on the same date
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 20;

-- 4. Email addresses outside the reserved example names (RFC 2606)
SELECT split_part(lower(btrim(email)), '@', 2) AS domain, count(*) AS addresses
FROM crm.customer
WHERE email IS NOT NULL
  AND split_part(lower(btrim(email)), '@', 2) !~ '(^|\.)(example\.(com|net|org)|example|test|invalid|localhost)$'
GROUP BY 1 ORDER BY 2 DESC LIMIT 20;

-- 5. Card values that are not published test numbers
SELECT count(*) AS card_values,
       count(*) FILTER (WHERE card_number !~ '^(4111111111111111|4242424242424242|5555555555554444|378282246310005)$') AS not_test_numbers
FROM billing.payment_card;

-- 6. Phone values: do they look masked or plain?
SELECT count(*) AS phone_values,
       count(*) FILTER (WHERE phone ~ '[*xX#]{3,}') AS looks_masked,
       count(*) FILTER (WHERE phone ~ '^[+]?[0-9 ()-]{7,}$') AS looks_plain
FROM crm.customer WHERE phone IS NOT NULL;

Read the six results together

Query 4 returning only your test domains and query 5 returning zero is a good sign, not proof. A copy can carry real names and phone numbers beside harmless emails. A zero from query 6 under looks_masked, with a large looks_plain, says the column was copied as it was.

Realistic substitute values leave no pattern for query 6 to find. For those columns, rely on the overlap test in the next step.

Step 4: test for overlap without moving production data

The strongest test is overlap: the same people in both places. Don’t copy production values into the test system to find out. Instead, compute a keyed hash of each normalized value, using HMAC-SHA-256 with a secret key, on both sides, and compare only the hashes.

Check that both sides normalize the same way: trim and lowercase each value first, so the same address gives the same hash. Save the production hashes to a file, load them into a temporary table on the copy and check which of the copy’s values have a hash in it. Any match means a real address sits in the test copy.

A hash of an email address can still identify a person to anyone who holds the key, so keep the key and the hash file restricted and delete both after the check. Sample production with a fixed seed, and record the sample size, the method and the result. A zero means none of the sampled addresses were found, so raise the sample, up to the whole column, before you record none found.

Keyed-hash overlap test, production to copy

Adds pgcrypto to a scratch database, so raise a change record first. Pass the secret key as a psql variable from your secrets store.

Changes configuration
SQLTested on PostgreSQL 16.15, 17.11 and 18.6 with pgcrypto, November 9, 2026, as the postgres superuser, against synthetic data.
-- A. On the production replica, as a read-only role (needs pgcrypto where the hash is computed)
\set ON_ERROR_STOP on
\pset format unaligned
\pset tuples_only on
\o prod_hashes.txt
SELECT encode(hmac(lower(btrim(email)), :'key', 'sha256'), 'hex')
FROM crm.customer TABLESAMPLE BERNOULLI (1) REPEATABLE (42)
WHERE email IS NOT NULL;
\o

-- B. On the copy, in a scratch database
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TEMP TABLE prod_hashes (h text PRIMARY KEY);
\copy prod_hashes FROM 'prod_hashes.txt'
SELECT count(*) AS copy_emails_checked,
       count(*) FILTER (WHERE encode(hmac(lower(btrim(email)), :'key', 'sha256'), 'hex')
                              IN (SELECT h FROM prod_hashes)) AS found_in_production_sample
FROM crm.customer WHERE email IS NOT NULL;
Rollback. Raise a change record before you run the change.
DROP EXTENSION IF EXISTS pgcrypto;  -- in the scratch database; then delete prod_hashes.txt and the key file

Step 5: check whether masking was applied

Many teams mask data on its way into test, so check the copy column by column. Query 6 detects a simple masking pattern, such as digits replaced by asterisks. Check and record which columns show a consistent mask and which show plain values.

Flag a partial mask. Check whether a masked name column sits next to a plain email column. That still identifies people. Treat the copy as holding personal data until every identifying column is covered or the overlap test is negative.

What the findings look like

Five non-production copies from one synthetic estate, with the decision on each. The card-number column was rejected as a finding, and the laptop dump was excluded from the checks.

Records
SourceLocationDetected typeWhySensitivityDecision
Staging database (restored backup)crm.customer.emailEmail addressKeyed hashes match the production sampleHighDelete the copy; fix the refresh job before the next load
UAT databasebilling.payment_card.card_numberPayment card numberOnly published test numbers foundHighRejected as a finding: every value is a provider test card
Analytics sandboxcrm.customer.phonePhone numberNo overlap; values follow a consistent maskMediumKeep, with the owner’s written reason and a review date
Vendor demo copyhr.employee.national_idNational identifierRow counts match production; no masking patternHighDelete; ask the vendor to confirm deletion in writing
Developer laptop dumpNot scannedUnknownExcluded: not reachable from the review networkUnknownOwner to bring it in or delete it by the next review

Step 6: decide, then fix the path that made the copy

The copy’s owner decides: delete it and reload synthetic data, restrict access, or keep it for a stated reason and date. Record who decided and when. A decision to keep a copy with real personal data needs the same controls as production, including access review and retention.

Then fix the path that created the copy. If test environments refresh from production, check whether the job replaces identifying values on the way in, and change it if not. Run the checks again after the next refresh and keep both results, so the record shows the fix held.

What these checks miss

Each gap can leave real data undetected. List the ones that apply in the record for each copy.

What these checks miss
GapWhat to do about it
Free-text columnsNotes and comment fields hold names and numbers that a domain or card check never reads; sample them by hand
Values that were transformedA hash only matches the normalization you chose; test each form, such as phone numbers with and without country codes
Format-preserving substitutesThey look like real values and match no production hash; confirm with the team that built the job
Copies outside the databaseExports, spreadsheets and dumps on laptops and shared drives need a file-level search
Other enginesThe SQL here is PostgreSQL; the tests carry over, but the functions and views differ

The record to keep for each copy

One record per copy, kept read-only once signed.

The record to keep for each copy
Evidence itemProduced by, when and whereCompleteness checkSupports
The list of copies and their ownersDBA and cloud team, at the startReconciled with the cloud account inventoryGDPR Article 5(2); ISO/IEC 27001:2022 A.5.9
Output of checks 1 to 6, with the account that ran themDBA, on the review dateThe same queries ran on production; row counts comparedGDPR Article 25(2)
The overlap result, sample size and seedDBA, on the review dateA known shared test value was found, as a controlGDPR Article 25(2); PCI DSS 6.5.5
The decision, reason and effective dateCopy owner; privacy lead for exceptionsEvery copy has a decisionGDPR Article 5(1)(c)
The change to the refresh job, and the re-checkPlatform team, after the fixThe next refresh passed the same checksGDPR Article 25(1)
With IRIS

IRIS finds and classifies sensitive data in the databases you connect, test copies included, and checks the form of sampled values. Capabilities vary by edition.

Sensitive data found in a connected test copy, with its source and scan date: IRIS produces part of this, in the sources you connect.

A check of whether sampled values in a sensitive column are encrypted, masked, hashed or plain: IRIS produces this.

Columns your team confirms or declines, with a reviewer’s decision: IRIS produces this.

Changing the copy or its refresh job in the source system: Not produced by IRIS.

A dated re-scan that records the copy after your fix: IRIS produces this.

Explore discovery and classification

Re-run the checks after the next refresh

The check that closes this work is a second run of the six queries and the overlap test after the next refresh. If the overlap is zero and the card and domain checks pass, the fix held. If not, the job still copies production.

A request for one person’s data reaches these copies too. The next guide shows how to search every copy and record what you searched: How to respond to a DSAR across your databases.

Official sources

  1. GDPR Article 25, data protection by design and by default, EUR-Lex. Opened November 8, 2026.
  2. GDPR Article 5, principles relating to processing, EUR-Lex. Opened November 8, 2026.
  3. UK GDPR Article 25, legislation.gov.uk. Opened November 8, 2026.
  4. PCI DSS v4.0.1, requirement 6.5.5, PCI Security Standards Council. Opened November 8, 2026.
  5. PostgreSQL 18: pgcrypto, PostgreSQL Global Development Group. Opened November 8, 2026.
  6. PostgreSQL 18: pattern matching, PostgreSQL Global Development Group. Opened November 8, 2026.
Share this guide
FAQ

Test data questions

Protect your brand. Start with your data.

Connect one test database next to production. We’ll show which sensitive columns in the copy are stored in plain form.