Skip to content

PII masking

PII is personal data, such as an email address. Analysts query a customer table, but they never see the real email, phone or ssn.

Example

Start the server with examples/pii-masking/serve.sh. Each column has its own column mask. This one returns NULL for email.

apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata:
  name: mask-email
  priority: 90
spec:
  match:
    any:
      - resources:
          catalogs: ["shop"]
          schemas: ["main"]
          tables: ["customers"]
          columns: ["email"]
  mask:
    type: "null"

The masks on phone and ssn select their column in the same way. Both return a constant.

mask:
  type: "constant"
  args:
    value: "+1-555-REDACTED"

mask:
  type: "constant"
  args:
    value: "REDACTED"

The analyst key reads the table.

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_analyst.supersecret" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT id, email, phone, ssn, birth_date FROM shop.main.customers ORDER BY id"}'

{
  "query_id": "<query-id>",
  "columns": ["id", "email", "phone", "ssn", "birth_date"],
  "rows": [
    [1, null, "+1-555-REDACTED", "REDACTED", "1988-03-14"],
    [2, null, "+1-555-REDACTED", "REDACTED", "1975-07-22"],
    [3, null, "+1-555-REDACTED", "REDACTED", "1990-11-30"],
    [4, null, "+1-555-REDACTED", "REDACTED", "2001-02-02"]
  ],
  "row_count": 4,
  "truncated": false
}

What happened: Sluice replaced the table with a subquery that returns the masked values. birth_date has no mask, so it comes back unchanged.

This suite shows the rewritten SQL.

# A masked table needs its column list. The test runner has no database.
schema:
  shop.main.customers: [id, tenant_id, email, phone, ssn, birth_date, created_at]

cases:
  - name: email, phone and ssn are masked
    identity: { subject: pii-analyst, groups: [analytics] }
    sql: "SELECT id, email, phone, ssn FROM shop.main.customers"
    expect:
      outcome: allow
      masks:
        - "shop.main.customers.email=null"
        - "shop.main.customers.phone=constant"
        - "shop.main.customers.ssn=constant"
      rewrittenSql: >-
        SELECT id, email, phone, ssn
        FROM (SELECT sluice_src.id, sluice_src.tenant_id, NULL AS email, '+1-555-REDACTED' AS phone,
        'REDACTED' AS ssn, sluice_src.birth_date, sluice_src.created_at
        FROM shop.main.customers sluice_src) customers

More examples

A filter on a masked column sees the masked value. This search for alice finds nothing.

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_analyst.supersecret" \
  -H "Content-Type: application/json" \
  -d @- <<'EOF'
{"sql": "SELECT id FROM shop.main.customers WHERE email LIKE '%alice%'"}
EOF

{
  "query_id": "<query-id>",
  "columns": ["id"],
  "rows": [],
  "row_count": 0,
  "truncated": false
}

GROUP BY on a masked column groups the masked value.

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_analyst.supersecret" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT ssn, count(*) AS customers FROM shop.main.customers GROUP BY ssn"}'

{
  "query_id": "<query-id>",
  "columns": ["ssn", "customers"],
  "rows": [
    ["REDACTED", 4]
  ],
  "row_count": 1,
  "truncated": false
}

Limits

  • A mask without resources.columns masks nothing. Sluice gives no warning.
  • Some mask types, for example fake, run after the query. A filter, join or GROUP BY on such a column fails with ERR_MASK_UNSUPPORTED_CONTEXT. See Column masks.

See also