How to Classify and Tag Sensitive Columns Across Databases

Last quarter I got a Slack message from our compliance lead: "Can you tell me every column in our systems that contains an email address?" I said yes, confidently, and then spent three days proving myself wrong. The email column in users was obvious. The contact_email in vendors — sure. But billing_details in invoices had full email addresses embedded in a text blob. notes in support_tickets was full of customer phone numbers pasted by agents. And a column literally named ssn_hash turned out to contain unhashed SSNs in about 400 rows from a migration three years ago that nobody cleaned up.

That's the actual state of PII in most companies. You know where the obvious stuff is. You have no idea where the non-obvious stuff is. And the gap between those two is exactly what regulators care about.

GDPR Article 30 requires a record of processing activities — including categories of personal data. CCPA requires you to tell consumers what personal information you've collected. SOC 2's Common Criteria 6.5 wants you to classify information assets. All three assume you've done the work of actually finding the data first. Most teams haven't.

Step 1: Scan column names with SQL

The cheapest, fastest pass is checking column names against known PII patterns. This won't catch everything — a column called field_7 could contain social security numbers — but it catches the easy wins and gives you a starting inventory.

SELECT
    c.table_schema,
    c.table_name,
    c.column_name,
    c.data_type,
    CASE
        WHEN c.column_name ~* '(email|e_mail|email_address|mail)' THEN 'email'
        WHEN c.column_name ~* '(phone|mobile|cell|fax|tel)' THEN 'phone'
        WHEN c.column_name ~* '(ssn|social_security|sin|national_id)' THEN 'national_id'
        WHEN c.column_name ~* '(credit_card|card_number|cc_num|pan)' THEN 'payment_card'
        WHEN c.column_name ~* '(first_name|last_name|full_name|surname)' THEN 'person_name'
        WHEN c.column_name ~* '(address|street|city|zip|postal|addr)' THEN 'address'
        WHEN c.column_name ~* '(dob|birth_date|date_of_birth|birthday)' THEN 'date_of_birth'
        WHEN c.column_name ~* '(passport|driver_license|licence)' THEN 'government_id'
        WHEN c.column_name ~* '(ip_address|ip_addr|user_ip|client_ip)' THEN 'ip_address'
        ELSE NULL
    END AS suspected_pii_type
FROM information_schema.columns c
WHERE c.table_schema NOT IN ('pg_catalog', 'information_schema')
  AND c.data_type IN ('text', 'character varying', 'varchar', 'char', 'jsonb', 'json')
ORDER BY suspected_pii_type NULLS LAST, c.table_schema, c.table_name;

This gives you two things: a list of columns that probably contain PII (where suspected_pii_type is not null), and a list of text columns that don't match any pattern but might still contain PII in their actual values.

The gotcha here is false positives. A column called phone_model will match the phone pattern. email_template_id will match email. You'll need to review the results, not blindly trust them — but reviewing 30 flagged columns is a morning's work, not a week-long project.

Also: filter by data type. An integer column named phone_type_code isn't storing phone numbers. Stick to text, varchar, and JSON types for name-based scanning — you'll add numeric types back in step 2 when you look at actual values.

Step 2: Sample data for PII patterns

Column names lie. Or more precisely, they tell you what someone intended when they created the table, not what ended up in it. The notes column in your CRM doesn't sound like PII, but it's where your sales team pastes customer email addresses, phone numbers, and occasionally full mailing addresses.

This Python script samples actual values from text columns and runs regex patterns against them:

import re
import psycopg2
 
PII_PATTERNS = {
    'email':       re.compile(r'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}'),
    'phone_us':    re.compile(r'\b(\+?1[-.\s]?)?\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}\b'),
    'ssn':         re.compile(r'\b\d{3}-\d{2}-\d{4}\b'),
    'credit_card': re.compile(r'\b(?:4\d{12}(?:\d{3})?|5[1-5]\d{14}|3[47]\d{13})\b'),
    'ip_address':  re.compile(r'\b(?:\d{1,3}\.){3}\d{1,3}\b'),
}
 
SAMPLE_SIZE = 100
 
def scan_column(cursor, schema, table, column):
    """Sample rows and check for PII patterns in actual values."""
    query = f"""
        SELECT "{column}"::text
        FROM "{schema}"."{table}"
        WHERE "{column}" IS NOT NULL
        LIMIT {SAMPLE_SIZE}
    """
    cursor.execute(query)
    rows = [r[0] for r in cursor.fetchall()]
 
    hits = {}
    for label, pattern in PII_PATTERNS.items():
        match_count = sum(1 for val in rows if pattern.search(val))
        if match_count > 0:
            hits[label] = round(match_count / len(rows) * 100, 1)
    return hits
 
conn = psycopg2.connect("dbname=mydb user=analyst")
cur = conn.cursor()
 
# Get all text columns
cur.execute("""
    SELECT table_schema, table_name, column_name
    FROM information_schema.columns
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
      AND data_type IN ('text', 'character varying', 'varchar', 'jsonb')
    ORDER BY table_schema, table_name
""")
 
results = []
for schema, table, column in cur.fetchall():
    hits = scan_column(cur, schema, table, column)
    if hits:
        results.append({
            'schema': schema, 'table': table,
            'column': column, 'pii_detected': hits
        })
 
for r in results:
    print(f"{r['schema']}.{r['table']}.{r['column']}: {r['pii_detected']}")

A few things to watch for. First, sampling 100 rows is a balance between speed and coverage — if you have a million-row table and the PII only exists in rows from a specific migration window, you might miss it. For critical tables, bump the sample or add ORDER BY random() (which is slow on large tables, but accurate). Second, the credit card regex catches Visa, Mastercard, and Amex patterns but will also match some random 16-digit numbers. Run a Luhn check on matches if you want to reduce false positives.

Encrypted or hashed columns are another trap. A column full of bcrypt hashes or AES ciphertext won't match any PII pattern — which is correct from a "this column doesn't expose PII in plaintext" perspective, but doesn't tell you whether the underlying data is sensitive. You need the column name scan from step 1 to catch those: if a column is called ssn_encrypted, the name tells you what the data is even when the values don't.

Step 3: Tag columns with sensitivity labels

Once you've identified which columns contain PII, you need a classification scheme — not a bespoke taxonomy, just enough structure that people across the team mean the same thing when they say "sensitive." Four tiers work for most organizations:

In Postgres, you can store these labels directly as column comments — the same mechanism from schema drift detection and column profiling:

COMMENT ON COLUMN users.email IS 'PII:confidential - user email address';
COMMENT ON COLUMN users.ssn_encrypted IS 'PII:restricted - encrypted SSN, AES-256';
COMMENT ON COLUMN orders.billing_address IS 'PII:confidential - full mailing address';
COMMENT ON COLUMN products.sku IS 'PII:public - product identifier, no sensitivity';

This approach is free and lives in the database itself, which means it can't drift from the schema — the label and the column are in the same system. The limitation is that it's per-database. If you have three Postgres instances, a MySQL database, and a BigQuery dataset, you're maintaining labels in five places with no single view.

Step 4: Build the privacy map

The point of all this scanning and tagging isn't a spreadsheet that lives in someone's Drive folder and never gets opened again. It's a queryable inventory you can pull up when the auditor asks "where do you store email addresses" and answer in minutes, not days.

Here's what a useful output looks like after running steps 1 and 2 across your databases:

DATABASE        TABLE              COLUMN             PII_TYPE        SENSITIVITY   SOURCE
-----------     ----------------   ----------------   ------------    -----------   --------
app_prod        users              email              email           confidential  name+data
app_prod        users              phone              phone_us        confidential  name+data
app_prod        users              ssn_encrypted      national_id     restricted    name_only
app_prod        support_tickets    notes              email,phone_us  confidential  data_only
app_prod        invoices           billing_details    email           confidential  data_only
analytics       events             ip_address         ip_address      internal      name+data
analytics       events             user_agent         -               internal      manual
warehouse       stg_customers      full_name          person_name     confidential  name+data
warehouse       stg_customers      addr_line_1        address         confidential  name+data

Notice the SOURCE column. name+data means both the column name scan and the data sampling flagged it — high confidence. data_only means the column name looked innocent but actual values contained PII — these are the ones that would have slipped through a name-only scan. name_only means the name matched but the data didn't (encrypted, hashed, or a false positive like phone_model). manual is a human override.

That support_tickets.notes row is the kind of thing that keeps a DPO up at night. An unstructured text column with no PII in its name, flagged only because the data scan found email addresses and phone numbers pasted into free-text notes. No amount of column naming conventions will prevent this — users put PII in text fields, and the only way to catch it is to look at the actual data.

Keeping it current

A one-time scan is useful. A one-time scan that nobody re-runs is a liability, because it gives you false confidence — "we already did the PII audit" — while new tables, new columns, and new data patterns accumulate unchecked. The scan from six months ago doesn't know about the customer_contacts table your team added in March.

You can schedule the Python script above on a cron, diff the results against the last run, and alert on new PII detections. That works at small scale. It falls apart once you're past a handful of databases, because now you're maintaining connection strings, credentials, and scan schedules for each one — and hoping nobody adds a new database without telling you.

This is where a data catalog with built-in privacy classification earns its keep. Fastero's data catalog auto-scans columns across all connected sources — all 21+ supported connectors — and tags PII patterns automatically as part of discovery. Connect a new Postgres instance or BigQuery dataset, and the privacy classification runs without you writing a script or scheduling a cron. New columns get scanned when they appear. The sensitivity labels live in one place across every source, not scattered across five databases' COMMENT ON metadata. If you're already using Fastero to profile datasets or monitor data quality, the privacy map is built from the same connections you already have.

The manual approach in this post is the right starting point — it's free, it works today, and it forces you to understand your actual data landscape rather than trusting a tool to abstract it away. But if you're running this scan across more than two or three databases, or you need the results to stay current without babysitting a fleet of cron jobs, automating it through a catalog that already knows your schema is the path that actually sustains. For more on that broader picture, automated data lineage from query to dashboard covers how the same connection metadata powers lineage and impact analysis.

Try Fastero free — auto-scan every connected database for PII and build a privacy map across all your sources. No credit card required.