Scan Databases for Unlabeled PII

Sample column values, run Presidio analyzers, and produce a report of likely personal data that lacks classification tags.

~40 minprivacypiidata-governance

Task

Find personal data stored in columns nobody labeled as PII before a privacy audit or a data subject request.

When to use this

A support ticket table has a free-text notes column. Customers paste emails, phone numbers, and order details into it, but the column is not classified as PII, so deletion and export jobs skip it entirely.

Prerequisites

Python 3.11 with presidio-analyzer installed
Read-only access to a database replica
A list of candidate text columns to scan

Steps

1. Install the analyzer and confirm the English NLP model loads.

pip install presidio-analyzer psycopg[binary] && python -c 'from presidio_analyzer import AnalyzerEngine; print(AnalyzerEngine().analyze(text="[email protected]", language="en"))'

Expected output: A result list containing an EMAIL_ADDRESS entity with a score above 0.6.

2. Sample text columns from a read replica and score each value with a conservative threshold.

from presidio_analyzer import AnalyzerEngine
import psycopg

analyzer = AnalyzerEngine()
with psycopg.connect("postgresql://[email protected]/appdb") as conn:
    rows = conn.execute(
        "SELECT id, notes FROM support_tickets "
        "WHERE notes IS NOT NULL TABLESAMPLE SYSTEM (1) LIMIT 500"
    ).fetchall()

hits = []
for row_id, text in rows:
    for result in analyzer.analyze(text=str(text), language="en"):
        if result.score >= 0.6:
            hits.append((row_id, result.entity_type, round(result.score, 2)))

for hit in hits[:20]:
    print(hit)

Expected output: A list of ticket ids with entity types such as EMAIL_ADDRESS, PHONE_NUMBER, and PERSON.

3. Aggregate hits by column and entity type into a PII inventory with recommended classification.

python scripts/pii_scan.py --output pii-inventory.csv && column -s, -t pii-inventory.csv | head

Expected output: A CSV listing column, entity type, hit count, and a classification recommendation.

4. Persist the classification as a column comment so downstream jobs can discover it.

psql -U app_owner -d appdb -c "COMMENT ON COLUMN support_tickets.notes IS 'PII: free text may contain EMAIL_ADDRESS, PHONE_NUMBER, PERSON';"

Expected output: The comment is visible in information_schema and in the data catalog sync.

Troubleshooting

Problem

Too many false positives on order numbers

Solution

Raise the score threshold and add a deny-list for known non-PII formats; review a sample manually before publishing the inventory.

Problem

The scan is too slow on large tables

Solution

Use TABLESAMPLE and LIMIT, scan only candidate text columns, and run against a replica rather than the primary.

Limitations

Detection is probabilistic; a low-confidence hit may still be personal data, and anonymized values may still match patterns.
Sampling can miss rare columns or values, and binary or encrypted fields are not analyzed.

Classification gaps are the real risk

Most companies know where structured PII lives: users.email, customers.phone. The gap is free-text fields, support notes, JSON blobs, and legacy columns added before privacy reviews existed. A scan turns those unknowns into a reviewable inventory, which is the input for deletion jobs, access controls, and subject requests.

Sample responsibly

Scan a replica, not the primary, and sample rather than reading every row. Presidio analyzes text with named entity recognition and pattern recognizers, so it works best on natural language and structured identifiers. Run it on candidate columns only, exclude clearly non-personal fields, and keep the raw values out of the report; record counts and entity types instead.

for result in analyzer.analyze(text=str(text), language="en"):
    if result.score >= 0.6:
        hits.append((row_id, result.entity_type, round(result.score, 2)))

Review before acting

Automated detection produces false positives: an order id can look like a phone number, a product name like a person. Sample twenty hits per column and confirm with the owning team. Record the decision, including “not PII”, so the next scan does not reopen the same question. Classify with a comment or catalog tag and connect it to retention and deletion policies.

Keep it current

Run the scan quarterly and after adding a new free-text field. Add a schema review check that requires a classification comment on new text columns. The goal is not a perfect detector but a documented, repeatable process that finds unlabeled personal data before an auditor or a customer does.