Skip to main content

Masking Sensitive Output

The result of a script "query" may contain columns that must not appear in a report, an export, or an agent's context: email, ssn, phone, or a *_enc column. A mask {} block redacts those columns before the result leaves Atlas.

Test datasets use the same mask blocks to mask the rows they load.

Examples​

Common masked reads as code. Each tab is a complete script, and each Example execution block shows an end-to-end run on SQLite.

This exports customer rows with the identity columns hashed, the card's last four kept, and the phone digits replaced:

export.hcl
# Reusable, named mask: hash identity columns with a fixed salt so tokens stay
# joinable across reports (define once, apply with `use`).
mask "identity" {
columns = ["email", "ssn"]
method = HASH
salt = "prod-secret"
}

script "query" "customer_export" {
query "rows" {
sql = "SELECT id, name, email, ssn, card, phone FROM customers ORDER BY id"
format = CSV

# Apply the reusable identity mask...
mask { use = [mask.identity] }

# ...plus per-column masks: keep the card's last 4, star the phone digits.
mask {
columns = ["card"]
method = PARTIAL
keep_right = 4
}
mask {
columns = ["phone"]
method = REPLACE
match = "[0-9]"
with = "#"
}
}
}
Example execution

The example is self-contained on SQLite. Set up a database with sqlite3 export.db < setup.sql:

setup.sql
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, ssn TEXT, card TEXT, phone TEXT);
INSERT INTO customers VALUES
(1, 'Ada Lovelace', 'ada@example.com', '123-45-6789', '4111111111111111', '555-0100'),
(2, 'Bob Stone', 'bob@example.com', '987-65-4321', '4222222222222222', '555-0199');

Run the script:

atlas script query --url "sqlite://export.db" --file "file://export.hcl" --run customer_export --quiet
1,Ada Lovelace,71f2179ddbb6c48f28aca29b80efe46b136ce9568dd75041a98ad482be6ca2fc,f63009387803b2964829857acedf2a92374d7fe00c942020180b129a4f3c646e,************1111,###-####
2,Bob Stone,30afa44bf93ac9486cf6d5c225bd57bd2ac5a4c7175ae1b31fadc3859daeb300,239d31c6bcb443ca87fa5a32702bdf112421223126cbe3c24a77ecb8889c0f4e,************2222,###-####

The reusable identity mask turns email and ssn into keyed HMAC-SHA256 tokens. Because the salt is fixed, the same value hashes identically everywhere, so masked columns stay joinable. The inline PARTIAL keeps the card's last four, and the inline REPLACE stars every phone digit while leaving the dash. id and name match no mask, so they pass through. Masks apply in declaration order, and the first match wins.

Selecting Columns​

columns is a list of output-column names. An entry with a glob metacharacter (*, ?, [...]) is a glob (the same wildcard style as schema include/exclude); an entry without one behaves as an exact name.

Every entry is matched with the same path-style matcher, so a backslash escapes the next character and a * never crosses a /. A column is masked when its name equals an exact entry or matches a glob. For example, columns = ["*email*", "phone*"] matches email, contact_email, and phone, and leaves name untouched.

A mask that matches no output column is a no-op, not an error: Atlas never parses the SELECT, so the result columns are unknown until the query runs and there is nothing to validate against. Masking applies regardless of the column's SQL type: a masked value serializes as text (a masked int renders as text, and a BLOB is masked over its 0x... hex form).

NULL normally passes through unmasked: there is nothing to leak, and masking it to a token would hide the fact that the value is absent. This relies on the driver reporting the column's type, so on SQLite a NULL is masked anyway when the column is a BLOB, is declared without a type, or is an expression rather than a plain column reference (SELECT NULL AS lit). Alias and type such columns, or expect the mask token in place of the empty field.

Methods​

Four methods are supported. Each method accepts only its own parameters: a parameter that does not apply to the chosen method is a parse-time error, not silently ignored, for example mask: `salt` is not valid for method REDACT.

  • REDACT replaces the whole value with a fixed token (*** by default; override with token).
  • PARTIAL keeps characters at the start and end and replaces the rest with char (* by default). keep sets a symmetric window on both ends; keep_left/keep_right set them asymmetrically. A value with no characters left to mask between the kept ends is fully masked, so nothing leaks.
  • HASH replaces the value with a hex digest. With salt the digest is a keyed HMAC-SHA256; without salt it is a bare SHA256, which is recoverable for low-entropy values such as an SSN or a phone number, so supply a secret salt for sensitive columns. The digest is deterministic, so equal inputs mask to equal tokens: masked columns stay joinable, groupable, and correlatable across queries, runs, files, and even database engines.
  • REPLACE applies a regular-expression substitution (match is the pattern, with the replacement). Unlike columns, which selects which columns to mask, match operates on the value, so values can be redacted by pattern.

For example, each tab below masks one column of the row (1, 'ada@example.com', '123-45-6789', '4111111111111111', '555-0100'):

script "query" "u" {
query "u" {
sql = "SELECT id, email, ssn FROM users WHERE id = 1"
format = CSV
mask {
columns = ["email"]
method = REDACT
}
}
}
1,***,123-45-6789

Masking Entities​

entities masks the entities found in a value, such as email addresses and phone numbers, and keeps the rest of the value. It takes a list of entity names, or ALL for every entity:

EntityMatches
CREDIT_CARDCard numbers of 13 to 19 digits, grouped or not, that start with a card issuer's prefix and pass the Luhn checksum.
EMAILEmail addresses.
IBANInternational bank account numbers of their country's length that pass the mod-97 checksum.
IPIPv4 and IPv6 addresses.
MACMAC addresses: 00:1A:2B:3C:4D:5E, 00-1A-2B-3C-4D-5E, or 001a.2b3c.4d5e.
PHONEInternational numbers of 8 to 15 digits starting with +, and North American numbers written with separators, such as (415) 555-0132, 415-555-0132, or 415.555.0132, whose area code and exchange start with 2 to 9.
SSNUS Social Security numbers, 123-45-6789 or 123 45 6789, except numbers never issued: area 000, 666, or 900 to 999, group 00, or serial 0000.
URLURLs starting with http://, https://, ftp://, or www..

The method applies to each entity on its own:

  • REDACT, the default, replaces each entity with its name in angle brackets, such as <EMAIL>, or with token when set.
  • PARTIAL and HASH mask the characters of each entity, and keep the text around it.
  • REPLACE does not take entities.

Entities that overlap are masked as one, under the name of the one that starts first.

This script masks the email addresses and phone numbers written in a free-text column:

notes.hcl
script "query" "notes" {
query "rows" {
sql = "SELECT id, body FROM notes ORDER BY id"
format = CSV
mask {
columns = ["body"]
entities = [EMAIL, PHONE]
}
}
}
Example execution

The example is self-contained on SQLite. Set up a database with sqlite3 notes.db < setup.sql:

setup.sql
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes VALUES
(1, 'Call +1 (415) 555-0132 or mail ada@example.com'),
(2, 'Nothing to hide');

Run the script:

atlas script query --url "sqlite://notes.db" --file "file://notes.hcl" --run notes --quiet
1,Call <PHONE> or mail <EMAIL>
2,Nothing to hide

Each entity is replaced by its name, and a value holding none passes through unchanged.

Entities are recognized by their format and checksum, not by meaning: names and street addresses are not entities, and an entity written in an unusual format stays unmasked. Mask a column holding only sensitive values with a whole-value mask instead.

Precedence and Scopes​

When more than one mask matches a column, the first one in declaration order wins. A query's own masks are consulted first (with any use references expanded in place), then the script-level defaults. So a narrow query mask overrides a broad default, and within one use list the leftmost entry takes the column.

A mask {} on a query masks that query. A mask {} at the script "query" level is a default applied to every query in the script, and a query's own mask overrides the default for the columns it matches, as the default and override example shows.

Reusable Masks​

Declare a mask once at the top level, give it a name, and apply it with use, instead of copying the same columns, method, and salt into every query. A top-level mask "<name>" is a definition only; it is never applied on its own, only through use = [mask.<name>, ...].

Inline columns/method and use are mutually exclusive: a mask {} block is either inline or a reference, and mixing them reports one error per conflicting attribute (use, columns attributes/blocks are mutually exclusive). Only columns and method are reported this way. Any other parameter set alongside use, such as salt, token, or keep_right, is dropped without an error, so keep a use block to use alone and change the definition when you need different parameters. For example:

mask "pii" {
columns = ["email", "ssn"]
method = REDACT
}

script "query" "customers" {
query "rows" {
sql = "SELECT id, email, ssn FROM customers ORDER BY id"
format = CSV
mask { use = [mask.pii] }
}
}

Because the mask is defined in one place, a HASH mask's salt is defined in one place too: the same input masks to the same token in every query that uses it. Pin the salt (sourced from a variable, KMS, or a secret manager) to keep tokens comparable.

Unlike a columns typo, which can only no-op, a use that names a mask that does not exist is a parse-time error, since names are known up front: This object does not have an attribute named "nope".

When no top-level mask is in scope at all, the error names the whole object instead, There is no variable named "mask", even for a name that is spelled correctly. That is the signal that the defining file was not passed to --file.

A top-level mask can group other masks with use, instead of selecting columns itself. A group expands in place, in order, so the first mask of the group that matches a column takes it. Here, customer groups the other two, and a query applies all of them with mask { use = [mask.customer] }:

mask "pii" {
columns = ["email", "ssn"]
method = REDACT
}

mask "phones" {
columns = ["phone*"]
method = PARTIAL
keep_right = 4
}

mask "customer" {
use = [mask.pii, mask.phones]
}

A group holds use alone: setting any other attribute alongside it is an error, such as use, token attributes/blocks are mutually exclusive. A mask that uses itself, directly or through another group, is an error even when nothing applies it: mask "customer" uses itself.

A named mask's parameters are validated only when something uses it: an unused definition with invalid parameters parses without complaint, and the error surfaces on the first use.

A set of files parsed together forms one document, so a mask "<name>" declared in one file is usable via use from a sibling. A file may hold only top-level mask definitions that scripts in other files reference, as the shared mask library example shows. Point --file at the directory (or list each file) so both parse together.

How it Works​

Masking is a post-processing pass over the rows Atlas has already read. Each run goes through three steps:

  1. The query executes as written. As everywhere in Data Scripts, the SELECT is never rewritten.
  2. Atlas reads the real values into memory and applies the masks to the result. Columns are selected by output name, matched case-sensitively, so you alias in the SELECT and mask the alias.
  3. The masked result is serialized per format and printed.

Masking therefore protects what leaves Atlas, not the data at rest. The raw value is still read from the database, so a mask is not a substitute for restricting the query itself.