Skip to content

pii.md

Latest commit

 

History

History
251 lines (187 loc) · 8.63 KB

pii.md

File metadata and controls

251 lines (187 loc) · 8.63 KB
Raw

flyquery — PII

Table of Contents

  1. Overview
  2. Three scanners
  3. Three policies
  4. Ordering guarantee
  5. Re-redact on late tag flip
  6. PII in query results
  7. Custom regex extension
  8. PII tags on schema objects
  9. Configuration reference

1. Overview

flyquery classifies columns for personally identifiable information (PII) and applies a configurable policy that controls whether PII-containing columns are queryable and whether sample values are retained.

PII scanning runs in two places:

  1. Stage 4 (sample) — before samples are persisted, a scanner tests candidate values. A column that triggers a match never has its sample values written to schema_objects.sample_values_json.
  2. Stage 8 (PII tag) — the PIIScanner port classifies each column from (column_name + description + sample_values) and sets pii_tag + pii_source on the flyquery_schema_objects row.

The scanner and policy are both configurable — globally via environment variables or per-dataset via ingest_policy_json.

2. Three scanners

FLYQUERY_PII_SCANNER selects the scanner.

regex (default)

Rule-based scanner using pattern matching on column names and sample values. Built-in patterns cover:

  • Email addresses (.*email.*, [a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,})
  • Phone numbers (E.164 patterns, national-format variants)
  • Social security numbers / national IDs (US SSN, UK NI, EU formats)
  • Credit card numbers (Luhn-valid 13–19 digit sequences)
  • IP addresses (IPv4 and IPv6)
  • Dates of birth (when combined with dob, birth_date column name signals)
  • Passport and driver's licence patterns (configurable; disabled by default)

Extend via FLYQUERY_PII_REGEX_PATTERNS_PATH (see §7).

Pros: Fast; no external dependency; works offline; easily auditable. Cons: Higher false-positive rate on numeric columns with Luhn-like sequences; misses semantic PII (e.g., "manager_name" → free-text names).

presidio

Uses Microsoft Presidio for NER- based PII detection. Detects names, locations, organisations, dates, credit-card numbers, and many more entity types via spaCy models.

Enable by setting FLYQUERY_PII_SCANNER=presidio. Requires the presidio-analyzer and presidio-anonymizer packages and a spaCy model (FLYQUERY_PRESIDIO_SPACY_MODEL, default en_core_web_sm).

Pros: Lower false-positive rate; catches semantic PII (names in free-text columns). Cons: Slower; requires presidio extra (pip install 'flyquery[presidio]'); spaCy model download on first run.

disabled

FLYQUERY_PII_SCANNER=disabled — PII scanning is skipped entirely. Samples are written without any PII gate. Stage 8 still runs but all columns are tagged pii_tag=NONE, pii_source=null.

Use only in air-gapped environments where data is pre-classified, or in development with synthetic data.

3. Three policies

FLYQUERY_PII_POLICY_SAMPLES applies at stage 4 (sample gate) and stage 8 (tag gate) for sample values:

Policy What happens on PII match
warn Log the finding; continue. Sample values are persisted. pii_findings_json populated on the ingest job.
redact (recommended) Purge sample_values_json in the same transaction. Log the finding. Column remains queryable.
reject Set schema_objects.is_active=false on the column. Column is not queryable until a human reviews and clears the flag.

FLYQUERY_PII_POLICY_RESULTS controls what happens when PII is detected in query result rows (post-execution scan by the Explainer):

Policy What happens
warn (default) pii_findings_json populated on flyquery_queries row. Result is returned unchanged.
redact PII-matching cells in the result preview are replaced with [REDACTED]. Full result in object storage is not modified.
reject Query returns 422 with error_code=PII_IN_RESULTS.

Policies can be overridden per-dataset via ingest_policy_json:

{
  "pii_policy_samples": "redact",
  "pii_policy_results": "warn",
  "pii_disabled": false
}

4. Ordering guarantee

The PII gate runs BEFORE sample persistence on first ingest:

Stage 4 (sample):
  For each column value batch:
    1. Run PIIScanner on the values
    2. IF match AND policy=redact → discard; do NOT write to sample_values_json
    3. IF match AND policy=reject → mark column is_active=false; skip sample
    4. IF match AND policy=warn  → log finding; write sample anyway
    5. IF no match → write to sample_values_json

This ordering ensures that PII values never touch the database, even transiently. The scanner runs in memory; values that fail the gate are discarded without being committed.

5. Re-redact on late tag flip

An operator can manually set pii_tag on a schema object via:

PUT /api/v1/schema-objects/{id}
{
  "pii_tag": "EMAIL",
  "pii_source": "HUMAN"
}

If the column already has sample_values_json persisted (because the original scan missed it or the scanner was set to warn), flyquery scrubs the samples in-place in the same transaction:

if update.pii_tag and update.pii_tag != "NONE":
    schema_object.sample_values_json = None   # atomic with the tag update

This ensures that a retroactive PII classification immediately removes the sample data without a separate step.

To trigger a full re-ingestion with the new policy (to update profile_json as well), use:

POST /api/v1/ingest-jobs  {"job_kind": "REPARSE", "file_id": "<id>"}

6. PII in query results

The ExplainerAgent receives up to 50 rows of result preview. flyquery applies FLYQUERY_PII_POLICY_RESULTS to the preview before returning it to the caller.

Note: The full result in object storage (Parquet at results/{query_id}.parquet) is NOT post-processed for PII. If you need PII-free results at rest, set policy=redact and rely on the preview only, or apply your own column-level filtering before downloading the presigned Parquet.

pii_findings_json on flyquery_queries records which columns in the result triggered PII findings (scanner and entity type). This field is populated regardless of policy (even warn).

7. Custom regex extension

Additional regex patterns can be supplied via a YAML file pointed to by FLYQUERY_PII_REGEX_PATTERNS_PATH:

# custom_pii_patterns.yaml
patterns:
  - name: EMPLOYEE_ID
    pattern: "EMP[0-9]{6}"
    column_name_hints: ["employee_id", "emp_id"]
  - name: INTERNAL_ACCOUNT
    pattern: "ACC[0-9]{10}"
    column_name_hints: ["account_number", "acct_no"]

Each pattern entry has:

  • name — the pii_tag value set when matched.
  • pattern — a Python re-compatible regex applied to sample values.
  • column_name_hints (optional) — if the column name contains any of these strings, the pattern match threshold is lowered (flag on name alone, even without a sample match).

Custom patterns are merged with the built-in set. Duplicate name values override the built-in.

8. PII tags on schema objects

flyquery_schema_objects.pii_tag holds the detected category:

Value Meaning
NONE No PII detected
EMAIL Email address
PHONE Phone number
SSN Social security / national ID number
CREDIT_CARD Credit card number
IP_ADDRESS IP address
DOB Date of birth
NAME Personal name (Presidio only)
LOCATION Address / location (Presidio only)
CUSTOM:<name> Matched a custom pattern (name from YAML)

flyquery_schema_objects.pii_source records how the tag was set: REGEX, PRESIDIO, AGENT (v1+ GovernanceClassifierAgent), or HUMAN.

HUMAN-set tags take precedence and are preserved across re-uploads (annotation transplant in stage 3).

9. Configuration reference

Variable Default Effect
FLYQUERY_PII_SCANNER regex Scanner: regex | presidio | disabled
FLYQUERY_PII_POLICY_SAMPLES redact Policy for sample values: warn | redact | reject
FLYQUERY_PII_POLICY_RESULTS warn Policy for query result rows
FLYQUERY_PII_REGEX_PATTERNS_PATH (unset) Path to custom regex YAML
FLYQUERY_PRESIDIO_SPACY_MODEL en_core_web_sm spaCy model for Presidio
FLYQUERY_SAMPLE_N 8 Number of sample values read per column

See also security-model.md § 7 for the security angle.