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.
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.
-- 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.
-- 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 fileStep 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.
| Source | Location | Detected type | Why | Sensitivity | Decision |
|---|---|---|---|---|---|
| Staging database (restored backup) | crm.customer.email | Email address | Keyed hashes match the production sample | High | Delete the copy; fix the refresh job before the next load |
| UAT database | billing.payment_card.card_number | Payment card number | Only published test numbers found | High | Rejected as a finding: every value is a provider test card |
| Analytics sandbox | crm.customer.phone | Phone number | No overlap; values follow a consistent mask | Medium | Keep, with the owner’s written reason and a review date |
| Vendor demo copy | hr.employee.national_id | National identifier | Row counts match production; no masking pattern | High | Delete; ask the vendor to confirm deletion in writing |
| Developer laptop dump | Not scanned | Unknown | Excluded: not reachable from the review network | Unknown | Owner 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.
| Gap | What to do about it |
|---|---|
| Free-text columns | Notes and comment fields hold names and numbers that a domain or card check never reads; sample them by hand |
| Values that were transformed | A hash only matches the normalization you chose; test each form, such as phone numbers with and without country codes |
| Format-preserving substitutes | They look like real values and match no production hash; confirm with the team that built the job |
| Copies outside the database | Exports, spreadsheets and dumps on laptops and shared drives need a file-level search |
| Other engines | The 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.
| Evidence item | Produced by, when and where | Completeness check | Supports |
|---|---|---|---|
| The list of copies and their owners | DBA and cloud team, at the start | Reconciled with the cloud account inventory | GDPR Article 5(2); ISO/IEC 27001:2022 A.5.9 |
| Output of checks 1 to 6, with the account that ran them | DBA, on the review date | The same queries ran on production; row counts compared | GDPR Article 25(2) |
| The overlap result, sample size and seed | DBA, on the review date | A known shared test value was found, as a control | GDPR Article 25(2); PCI DSS 6.5.5 |
| The decision, reason and effective date | Copy owner; privacy lead for exceptions | Every copy has a decision | GDPR Article 5(1)(c) |
| The change to the refresh job, and the re-check | Platform team, after the fix | The next refresh passed the same checks | GDPR Article 25(1) |
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 classificationRe-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
- GDPR Article 25, data protection by design and by default, EUR-Lex. Opened November 8, 2026.
- GDPR Article 5, principles relating to processing, EUR-Lex. Opened November 8, 2026.
- UK GDPR Article 25, legislation.gov.uk. Opened November 8, 2026.
- PCI DSS v4.0.1, requirement 6.5.5, PCI Security Standards Council. Opened November 8, 2026.
- PostgreSQL 18: pgcrypto, PostgreSQL Global Development Group. Opened November 8, 2026.
- PostgreSQL 18: pattern matching, PostgreSQL Global Development Group. Opened November 8, 2026.
Start with a free assessment of one database you choose.
Get a free assessmentTest data questions
No. It is a processing question under Article 25, not a breach in itself. It becomes an incident when the copy was reachable by people or systems that had no reason to see the data.
Yes, if every identifying column is covered and the overlap test is negative. Record the method and re-test after each refresh.
No. Only keyed hashes travel, and without the secret key they can’t be turned back into values. Treat the hash file as sensitive anyway and delete it after the check.
No. IRIS finds and checks the data. Your team changes the copy or the refresh job in the source system, and a re-scan records the result.
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.


