Securing Customer PII in Utility Databases

A utility database is one of the richest stores of personal data a city holds — names, service addresses, bank details, and consumption curves that betray when a household is home or away. The failure mode this page resolves is the accidental read: a bulk rate recalculation issues SELECT *, an ORM lazy-loads a relationship that drags customer_ssn into application memory, or a developer’s debugging query returns a service address that then lands in a log aggregator. None of those requires an attacker — just a query broader than the caller’s role needed. This guide sits under Data Governance & Privacy Compliance, inside Municipal Utility Billing Architecture & Rate Taxonomy, and gives you one focused remedy: a field-level projection layer that tags every column with a sensitivity level, lets each role read only up to its ceiling, replaces PII with a join-safe pseudonym instead of dropping it, and defaults unknown columns to the most restrictive class so a schema migration can never silently leak a new field.

Prerequisites

The imports and the single data assumption this layer depends on — nothing else.

from __future__ import annotations
from dataclasses import dataclass
from enum import Enum
import hashlib
import hmac
import logging
import os

logger = logging.getLogger("billing.pii")
  • Python 3.11+ for StrEnum-style string enums, the X | None union syntax, and modern dataclass slots.
  • hmac + hashlib from the standard library — pseudonym tokens are keyed HMAC-SHA256, not bare hashes, so a stolen digest cannot be reversed against a rainbow table of account numbers. No third-party crypto is required.
  • A resolved caller role. Authentication and role resolution happen upstream in Security Boundaries & Role-Based Access; this layer governs which fields a settled role may read, not who the caller is.
  • An explicit column list per query. The projector operates on named columns. Expanding SELECT * into concrete column names must happen before this layer runs — a literal "*" is treated as unknown and withheld by design.

Annotated Implementation

The whole protection layer is a sensitivity registry, a per-role ceiling, and one projection function. Every column the billing schema exposes is tagged; every role declares the highest sensitivity it may ever receive in cleartext; the projector returns only the columns at or below that ceiling, masks PII to a stable token, and emits an audit statement that is safe to persist because it contains no raw values.

Field-level projection routes each requested column by sensitivity and role ceiling A requested column list enters the field projector. For each column the projector looks up its sensitivity tag in the registry, defaulting an unknown column to SECRET, then keeps it only if the tag's rank is at or below the caller role's cleartext ceiling. Columns split into three lanes: those at or below the ceiling are returned as cleartext; PII above the ceiling is masked to a keyed HMAC pseudonym that preserves its shape; SECRET or unknown columns above the ceiling are withheld and removed from the SELECT so they never reach application memory. All three outcomes feed a single sanitized audit statement that records the columns and the access decision but never a raw value. Requested columns the columns a caller asked to SELECT Field projector · project_for_role() 1 · tag = COLUMN_SENSITIVITY.get(col, SECRET) 2 · keep if rank(tag) ≤ role ceiling ● Returned delivered as cleartext tag rank ≤ role ceiling handed to the caller as-is account_id · consumption_kwh ● Masked delivered as an HMAC token PII above the ceiling value → pseudonym(), shape kept customer_name → …_token ● Withheld removed from the SELECT SECRET or unknown column never reaches memory customer_ssn · "*" Sanitized audit statement role=… SELECT <granted>, pseudonym(pii) AS pii_token [withheld=…] names the columns and the decision — never a raw value 
class Sensitivity(str, Enum):
    PUBLIC = "public"        # safe to return and to log (tokens, kWh)
    INTERNAL = "internal"    # billing-safe, role-gated (account id, totals)
    PII = "pii"              # identifying: name, address — masked, not raw
    SECRET = "secret"        # SSN, bank details — never leaves the vault


class Role(str, Enum):
    BILLING_MANAGER = "billing_manager"
    FINANCE = "finance"
    DEVELOPER = "developer"


# Every exposed column carries a sensitivity tag. A column with no entry is
# treated as SECRET below — the registry fails closed, never open, so a new
# column added by a migration is withheld until someone classifies it.
COLUMN_SENSITIVITY: dict[str, Sensitivity] = {
    "service_token": Sensitivity.PUBLIC,
    "consumption_kwh": Sensitivity.PUBLIC,
    "zone_id": Sensitivity.PUBLIC,
    "account_id": Sensitivity.INTERNAL,
    "invoice_total": Sensitivity.INTERNAL,
    "customer_name": Sensitivity.PII,
    "service_address": Sensitivity.PII,
    "customer_ssn": Sensitivity.SECRET,
    "bank_account": Sensitivity.SECRET,
}

# The highest sensitivity each role may ever receive in cleartext. A developer
# working against production may see only PUBLIC columns; PII and SECRET never
# reach application memory for any billing role.
ROLE_CEILING: dict[Role, Sensitivity] = {
    Role.BILLING_MANAGER: Sensitivity.INTERNAL,
    Role.FINANCE: Sensitivity.INTERNAL,
    Role.DEVELOPER: Sensitivity.PUBLIC,
}

# Total order so ceilings are comparable. Nothing above a role's ceiling is
# ever returned in the clear.
_RANK: dict[Sensitivity, int] = {
    Sensitivity.PUBLIC: 0,
    Sensitivity.INTERNAL: 1,
    Sensitivity.PII: 2,
    Sensitivity.SECRET: 3,
}


def pseudonym(value: str) -> str:
    """Deterministic, keyed token for a PII value. Same input -> same token,
    so joins, grouping, and dedupe still work, but the raw value is
    unrecoverable without the key. HMAC (not a bare SHA-256) defeats a rainbow
    table of known account numbers or addresses."""
    key = os.environ["PII_TOKEN_KEY"].encode()          # rotated out of band
    return "tok_" + hmac.new(key, value.encode(), hashlib.sha256).hexdigest()[:24]


@dataclass(frozen=True, slots=True)
class Projection:
    """The result of projecting a request for one role. Frozen so a projection
    can never be widened after the access decision was made and logged."""
    role: Role
    returned: tuple[str, ...]     # columns delivered as cleartext
    masked: tuple[str, ...]       # PII columns delivered as a token
    withheld: tuple[str, ...]     # columns removed from the query entirely
    audit_statement: str          # sanitized — safe to persist to the log


def project_for_role(role: Role, requested: list[str]) -> Projection:
    """Reduce a requested column list to what `role` may actually read."""
    ceiling = _RANK[ROLE_CEILING[role]]
    returned: list[str] = []
    masked: list[str] = []
    withheld: list[str] = []

    for col in requested:
        # Unknown column ("*", a typo, a freshly migrated field) -> SECRET.
        level = COLUMN_SENSITIVITY.get(col, Sensitivity.SECRET)
        if _RANK[level] <= ceiling:
            returned.append(col)                        # within the role's reach
        elif level is Sensitivity.PII:
            masked.append(col)                          # shape kept, value hidden
        else:
            withheld.append(col)                        # never selected at all

    # The SELECT the DB actually runs: cleartext columns plus tokenized PII.
    # SECRET columns above the ceiling are absent, so they cannot reach memory.
    select_cols = returned + [f"pseudonym({c}) AS {c}_token" for c in masked]
    audit_statement = (
        f"role={role.value} "
        f"SELECT {', '.join(select_cols) or '<no columns granted>'} "
        f"[withheld={','.join(withheld) or 'none'}]"
    )
    # The audit line names columns and the decision, never a raw value.
    logger.info("pii.projection %s", audit_statement)
    return Projection(role, tuple(returned), tuple(masked), tuple(withheld), audit_statement)

The load-bearing decision is the dict.get(col, Sensitivity.SECRET) default. Access control that fails open — where an untagged column is assumed safe — is how the next schema migration ships a leak. Here, anything the registry does not explicitly bless is treated as the most sensitive thing in the system and withheld, so classification is a deliberate act, not an omission. PII is masked rather than dropped because downstream stages still need a stable key: the rate engine can group by customer_name_token or join on service_address_token without ever handling the raw string, and the same input always yields the same token so reconciliation against the Data Governance & Privacy Compliance ledger stays intact.

Edge Cases and Billing Gotchas

  • A migration adds an untagged column. Someone ships emergency_contact_phone and forgets the registry entry. Because COLUMN_SENSITIVITY.get defaults to SECRET, every role’s projection withholds it until it is classified — the field is invisible instead of leaking. The withheld list in the audit statement makes the gap loud, so the missing tag surfaces in review rather than in a breach.
  • SELECT * reaches the projector. A literal "*" is an unknown column, so it is treated as SECRET and withheld, and the projection returns no data for it. This forces callers to expand to explicit column names before querying — the projector cannot bless a wildcard it cannot classify. Pair this with lazy="raise" on ORM relationships so an accidental lazy-load raises instead of silently pulling PII.
  • Fallback tiers must not touch customer tables. When a rate cannot be resolved, Fallback Routing for Missing Rate Data must key off a pre-computed zone_id (a PUBLIC column) and the Customer Class & Service Tier Mapping, never a SELECT against the customer identity table. Resolving a default by re-reading service_address reintroduces the exact PII exposure the projector removed.
  • Exception context leaks a raw value into a retry queue. A failed task serialized with its exception often captures the offending row — including a raw address — into the queue payload, which then persists far longer than the request. Strip exc.args and any row context to PUBLIC-only fields before enqueueing, and serialize with JSON (never pickle) so nothing outside the sanitized projection survives a retry.

Verification Snippet

Prove the two invariants that keep this layer honest: a SECRET column is never returned or masked for any role, and an unknown column fails closed. Assert on the exact projection, not on log output.

def test_projection_fails_closed_and_hides_secrets():
    # A billing manager may read INTERNAL, sees PII only as a token, and never
    # touches a SECRET column even when the query explicitly asks for it.
    p = project_for_role(
        Role.BILLING_MANAGER,
        ["invoice_total", "consumption_kwh", "customer_name", "customer_ssn"],
    )
    assert "invoice_total" in p.returned          # INTERNAL <= ceiling
    assert "consumption_kwh" in p.returned        # PUBLIC   <= ceiling
    assert "customer_name" in p.masked            # PII -> tokenized, not raw
    assert "customer_ssn" in p.withheld           # SECRET -> never selected
    assert "customer_ssn" not in p.returned and "customer_ssn" not in p.masked

    # A developer against production may see PUBLIC columns only.
    d = project_for_role(Role.DEVELOPER, ["consumption_kwh", "account_id"])
    assert d.returned == ("consumption_kwh",)     # account_id is INTERNAL
    assert "account_id" in d.withheld

    # An unclassified / wildcard column fails closed for everyone.
    u = project_for_role(Role.FINANCE, ["*", "new_unclassified_col"])
    assert u.returned == () and set(u.withheld) == {"*", "new_unclassified_col"}

Run this against a fixture that mirrors the production schema before every deploy. A single column that appears in returned when it should be withheld is a leak that would otherwise ship to every caller of that role.

Frequently Asked Questions

Why mask PII to a token instead of just dropping the column?
Downstream stages still need a stable key. The rate engine, dedupe, and reconciliation often group or join on an identifying field — they just must never see the raw value. A deterministic HMAC pseudonym gives the same token for the same input every time, so joins and grouping keep working while the raw name or address never leaves the vault. Dropping the column entirely would break those joins and push developers toward re-querying the raw table, reintroducing the exposure.
Why default an unknown column to SECRET rather than PUBLIC?
Because access control must fail closed. If an untagged column were assumed safe, the next schema migration that adds a sensitive field would leak it to every role until someone noticed. Defaulting to SECRET means a new column is invisible until it is deliberately classified, and the withheld list in the audit statement makes the missing tag obvious in review. Classification becomes an explicit act, not something you can forget your way into.
Isn't a SHA-256 hash of the account number enough for the token?
No. Account numbers, SSNs, and addresses come from small, guessable spaces, so a bare SHA-256 is trivially reversed with a precomputed table. Keying the digest with HMAC and a secret that lives outside the database means an attacker who exfiltrates the token column still cannot recover the original values without also stealing the key. Rotate the key out of band and re-tokenize on rotation.
Where does this sit relative to database row-level security?
They are complementary layers. Row-level security and role-scoped connections decide which rows a caller may reach; this projector decides which fields within those rows come back in cleartext. Enforce both: a role-scoped connection at the data layer, RLS on the rows, and field-level projection on the columns, so a mistake in any one layer is still caught by the others. The projector is the application-side backstop for the query the ORM actually builds.
How do we keep the audit log itself from becoming a PII leak?
The audit statement names columns and the access decision, never a raw value — it records that customer_name was tokenized and customer_ssn was withheld, not their contents. Combine that with a logging filter that scrubs known PII patterns before records reach stdout or an aggregator, and serialize task payloads as JSON so exception context cannot smuggle a raw row into a log or a retry queue.

Up one level: Data Governance & Privacy Compliance · Parent guide: Municipal Utility Billing Architecture & Rate Taxonomy · Return to the utilitybilling.org home.