Skip to content

Column masks

A ColumnMaskPolicy replaces the values of a column before the client gets them. Use it for personal data that a subject must not read in full.

Example

Goal: analysts see only the last four characters of a phone number.

apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata:
  name: mask-phone
  priority: 50
spec:
  match:
    any:
      - resources:
          catalogs: ["hr"]
          schemas: ["main"]
          tables: ["employees"]
          columns: ["phone"]
  mask:
    type: partial
    args:
      showLast: 4

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

{
  "query_id": "<query-id>",
  "columns": ["id", "phone"],
  "rows": [
    [1, "*******0101"],
    [2, "*******0102"]
  ],
  "row_count": 2,
  "truncated": false
}

What happened: Sluice read the table through a subquery that masks phone. The column keeps its name. The rest of the query sees only the masked value, also in WHERE and ORDER BY.

Fields

Field Type Default Meaning
resources.columns in spec.match list The columns to mask. Without it, the policy masks nothing.
spec.mask.type string A mask type from the table below.
spec.mask.args object The options of the mask type.

Shared fields: Policies. Selectors: Matching.

  • Column names match in any letter case.
  • A selector with columns and no tables masks the column in every table.
  • A selector with tags takes the columns from a DataClassification. See Classification tags. Sluice still needs the column list of the table.
  • A row filter on the same table tests the real value.

Mask types

A mask runs in one of two places. "SQL" means the mask is part of the rewritten SQL. "Post-query" means Sluice changes the result rows after the query.

Type Result args Runs
null NULL None. YAML needs quotes: type: "null" SQL
constant A fixed value value: required. Text, number or boolean. A list shows as text such as [1 2] SQL
partial The first and last characters. maskChar replaces the rest. showFirst, showLast: default 0, one must be above 0. maskChar: default * SQL
hash A SHA-256 hash as hex text algorithm: sha256 or hmac_sha256, default sha256. saltRef: a secret:// reference to the salt, required for hmac_sha256 SQL for sha256, post-query for hmac_sha256
regex The text with every match of pattern replaced pattern: required, a regular expression in RE2 syntax, at most 512 bytes. replacement: default empty SQL
truncate The first length characters length: required, 1 or more. suffix: text to add after a cut SQL
fpe The characters of the alphabet, encrypted. Other characters stay. keyRef: required, a secret:// reference to the key. alphabet: numeric, lower or alphanumeric, default numeric. customAlphabet: 2 to 36 characters. tweak: hex, at most 16 bytes Post-query
jitter The number, moved by up to range range: required, between 0 and 1. 0.1 means 10 percent. seed Post-query
fake An invented value fakeType: required, one of name, first_name, last_name, email, phone, company, city, country, uuid. seed Post-query
  • hash, fpe, jitter and fake give the same output for the same input.
  • seed is plain text or a secret:// reference. See Server config for secrets.
  • Another type and most wrong args fail sluice policy validate. So does a saltRef or keyRef that is not a secret:// reference. It does not check the type of value. Unknown keys in args pass.

More examples

One column per mask type

The table employees has two rows:

INSERT INTO employees (id, name, phone, birth_date, address, ssn, card, salary, notes, email) VALUES
  (1, 'Alice Martin', '+1-555-0101', '1988-03-14', '12 Oak Street', '111-22-3333',
   '4111-1111-1111-1111', 72000, 'Prefers calls in the morning', 'alice@example.com'),
  (2, 'Bob Keller', '+1-555-0102', '1975-07-22', '7 Hill Road', '222-33-4444',
   '5500-0000-0000-0004', 58000, 'On leave', 'bob@example.com');

mask-phone from the first example masks phone. These policies mask the other columns:

apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata: { name: mask-name }
spec:
  match: { any: [{ resources: { tables: ["employees"], columns: ["name"] } }] }
  mask: { type: fake, args: { fakeType: name, seed: "secret://env/SLUICE_MASK_SEED" } }
---
apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata: { name: mask-birth-date }
spec:
  match: { any: [{ resources: { tables: ["employees"], columns: ["birth_date"] } }] }
  # Keep the year only.
  mask: { type: regex, args: { pattern: '-\d\d-\d\d$', replacement: "-01-01" } }
---
apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata: { name: mask-address }
spec:
  match: { any: [{ resources: { tables: ["employees"], columns: ["address"] } }] }
  mask: { type: constant, args: { value: "REDACTED" } }
---
apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata: { name: mask-ssn }
spec:
  match: { any: [{ resources: { tables: ["employees"], columns: ["ssn"] } }] }
  mask: { type: "null" }
---
apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata: { name: mask-card }
spec:
  match: { any: [{ resources: { tables: ["employees"], columns: ["card"] } }] }
  mask: { type: fpe, args: { keyRef: "secret://env/SLUICE_MASK_FPE_KEY" } }
---
apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata: { name: mask-salary }
spec:
  match: { any: [{ resources: { tables: ["employees"], columns: ["salary"] } }] }
  mask: { type: jitter, args: { range: 0.1, seed: "secret://env/SLUICE_MASK_SEED" } }
---
apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata: { name: mask-notes }
spec:
  match: { any: [{ resources: { tables: ["employees"], columns: ["notes"] } }] }
  mask: { type: truncate, args: { length: 10, suffix: "..." } }
---
apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata: { name: mask-email }
spec:
  match: { any: [{ resources: { tables: ["employees"], columns: ["email"] } }] }
  mask: { type: hash, args: { algorithm: hmac_sha256, saltRef: "secret://env/SLUICE_MASK_HMAC_KEY" } }

The secrets come from the environment:

# The fpe key must be 16, 24 or 32 bytes long.
export SLUICE_MASK_FPE_KEY="demo-fpe-key-16b"
export SLUICE_MASK_HMAC_KEY="demo-hmac-key"
export SLUICE_MASK_SEED="demo-seed"

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_analyst.supersecret" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM hr.main.employees ORDER BY id"}'

{
  "query_id": "<query-id>",
  "columns": ["id", "name", "phone", "birth_date", "address", "ssn", "card", "salary", "notes", "email"],
  "rows": [
    [1, "Wren Jensen", "*******0101", "1988-01-01", "REDACTED", null, "6450-0314-1819-8257", 75868, "Prefers ca...", "17e8e1c5ed55183c9d382d4f35c5e7733975be4e06c656cac8d6be2c54cb968e"],
    [2, "Alex Vega", "*******0102", "1975-01-01", "REDACTED", null, "8747-8067-1365-4115", 55975, "On leave", "9a2ef719edfa3b9aaa89d6ab7d2c9188fc672e0ad1de4456f86efa3823439760"]
  ],
  "row_count": 2,
  "truncated": false
}

What happened: every column shows its mask. card keeps its format. salary stays within 10 percent of the real number.

A suite shows where each mask runs:

# A masked table needs its column list. The test runner has no database.
schema:
  hr.main.employees: [id, name, phone, birth_date, address, ssn, card, salary, notes, email]

cases:
  - name: every column gets its mask
    identity: { subject: hr-analyst, groups: [analysts] }
    sql: "SELECT * FROM hr.main.employees"
    expect:
      outcome: allow
      rewrittenSqlContains:
        - "NULL AS ssn"
        - "'REDACTED' AS address"
        - "regexp_replace(sluice_src.birth_date::varchar, $4, $5, 'g') AS birth_date"
      # Post-query masks. The SQL reads the real value.
      postMasks:
        - "hr.main.employees.card=fpe"
        - "hr.main.employees.email=hash"
        - "hr.main.employees.name=fake"
        - "hr.main.employees.salary=jitter"

A sha256 mask with saltRef also needs its secret when the suite runs.

Several masks on one column

One mask wins. Masks never stack. Sluice picks the higher priority, then the more specific selector, then the first name in alphabetical order.

hide-phone also masks phone. mask-phone from the first example has the higher priority and wins:

apiVersion: sluice.bino.bi/v1alpha1
kind: ColumnMaskPolicy
metadata:
  name: hide-phone
  priority: 10
spec:
  match:
    any:
      - resources:
          columns: ["phone"]
  mask:
    type: "null"

- name: each column has one mask
  identity: { subject: hr-analyst, groups: [analysts] }
  sql: "SELECT id FROM hr.main.employees"
  expect:
    outcome: allow
    masks:
      - "hr.main.employees.address=constant"
      - "hr.main.employees.birth_date=regex"
      - "hr.main.employees.card=fpe"
      - "hr.main.employees.email=hash"
      - "hr.main.employees.name=fake"
      - "hr.main.employees.notes=truncate"
      # mask-phone and hide-phone match. mask-phone has the higher priority.
      - "hr.main.employees.phone=partial"
      - "hr.main.employees.salary=jitter"
      - "hr.main.employees.ssn=null"

SQL forms refused on a post-query mask

A column with a post-query mask can only be a plain item of the outer select list.

Refused form Error code
The masked column in WHERE, GROUP BY, ORDER BY, a join condition, an expression, a subquery or a CTE ERR_MASK_UNSUPPORTED_CONTEXT
SELECT * inside or over a subquery or a CTE. List the columns. ERR_MASK_UNSUPPORTED_CONTEXT
SELECT DISTINCT with the masked column. Its alias or position in another clause, for example ORDER BY 2 ERR_MASK_UNSUPPORTED_CONTEXT
USING and NATURAL joins, the table alias as a value, a column alias list, #3, a column with its schema ERR_MASK_UNSUPPORTED_CONTEXT 
curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_analyst.supersecret" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{"sql": "SELECT id FROM hr.main.employees WHERE card LIKE '4111%'"}
JSON

{
  "code": "ERR_MASK_UNSUPPORTED_CONTEXT",
  "message": "a post-query masked column is used outside a bare select item",
  "query_id": "<query-id>"
}

Every masked table also refuses EXPLAIN and some DuckDB forms. See SQL forms refused on filtered or masked tables.

Errors on a masked table

Cause Result
The table does not exist ERR_REWRITE_FAILED
Sluice cannot read the data source ERR_DATASOURCE_UNAVAILABLE
A column changed after the last reload ERR_QUERY_FAILED
A comparison needs the column type from before the mask ERR_QUERY_FAILED
The query reads rowid ERR_QUERY_FAILED
A secret is missing HTTP 500, ERR_INTERNAL
The fpe key is not 16, 24 or 32 bytes long HTTP 500, ERR_INTERNAL
jitter on a text column, fpe on a very short value HTTP 200 with a cut JSON body

Limits

  • Sluice keeps the column list of a masked table until a reload. Reload after a column changes.
  • sluice policy validate does not read secrets.
  • A sha256 hash without saltRef can be guessed for common values. Use hmac_sha256 or set saltRef.

See also