Snowflake: Redact PII from Query Result Sets

What this policy does

Direction: egress (tool_post_invoke) Default: allow (transform-only — never denies) Package: snowflake.egress.redact_pii

What it does

Scans the row content returned by the result-returning Snowflake MCP tools and rewrites personally identifiable information to fixed redaction tokens before the response reaches the agent:

Matches are replaced in place, leaving the surrounding row/column structure intact so the agent still gets a usable result set with only the sensitive fields masked. The policy is transform-only: it never denies a call, so a legitimate query still succeeds — it just comes back with SSN, email, and phone values masked. Responses with no matches (and all out-of-scope tools) pass through byte-identical. Every response field is read via object.get, so a missing or oddly-shaped payload is never an error — it simply passes through.

This is a backstop for tables that lack Snowflake dynamic data masking policies. A warehouse routinely holds regulated data (PII, PHI-eligible columns, financial records), and a SELECT * over a customer table can exfiltrate it wholesale; when a column has no column-level masking policy attached in Snowflake, this egress redaction is the last line of defence on the agent channel. It is intentionally narrow (three high-signal identifier classes) to limit false positives on free-text columns.

Group exemption

Redaction is gated by IdP group. Callers whose groups claim contains pii-cleared (a placeholder name — see Known limitations) receive unredacted responses. The check reads input.subject.claims.groups via object.get chains: a missing subject, missing claims, or missing groups claim means the caller is not cleared and redaction applies — the grant fails closed. This failure mode is safe: a caller whose claims fail to arrive gets over-redaction, never disclosure.

Compliance alignment

• SOC 2 CC6.7 — supports restricting the transmission/movement of confidential information by masking direct identifiers in query results as they leave the gateway toward the agent.
• SOC 2 C1.1 — supports identification and protection of confidential information on the warehouse read path; P4.1 — supports limiting personal-information use to identified purposes; P6.1 — supports controls over personal-information disclosure by keeping raw identifiers out of agent context that doesn't need them.
• HIPAA §164.502(b) / §164.514(d) — supports minimum-necessary, role-based limits: only placeholder pii-cleared group members see raw identifiers; everyone else gets a working result set with identifiers masked.
• HIPAA §164.514(a)–(b) — supports de-identification practice by stripping Safe-Harbor identifier classes (SSN, email, phone) from responses; §164.530(c) — supports privacy safeguards on the agent channel.
• GDPR Art. 5(1)(c) — data minimisation on agent reads of personal data; Art. 9 — reduces special-category exposure on the MCP path where identifiers co-occur with health/HR columns; Art. 5(1)(f) / Art. 32 — supports security of processing.
• CCPA/CPRA §1798.121 — supports limiting the use and disclosure of sensitive personal information (SSN) on the agent channel; §1798.150 — reduces nonredacted-PI breach exposure.

Why egress

The PII already lives in the warehouse — there is nothing to block at ingress, and denying the query outright would make the agent useless for everyday analytics work. The leak happens when the result set is returned to the MCP client, so the response path is the only place to catch it while keeping the query result useful. Ingress SQL guarding (DML/DDL/export denial, schema fencing) is a separate concern handled by companion policies.

Tool name matching

Applies on the output path (input.mode == "output") to the result-returning Snowflake tools, matched case-insensitively by suffix from input.resource.name with input.tool_metadata.name as a fallback. Suffix matching keeps the policy portable across the gateway server-name prefix (which is not standardised — different deployments name the Snowflake MCP server differently).

The suffix set combines verified wire names from the two open-source servers with the tool-type constants for the managed server:

• Community server (isaacwasserman) — verified wire name: read_query
• Snowflake-Labs server — verified wire names: run_snowflake_query, query_semantic_views
• Managed Snowflake MCP server — tool type identifiers, matched opportunistically: system_execute_sql, cortex_search_service_query, cortex_analyst_message

Important — managed-server names are not guaranteed to match. On the Snowflake-managed MCP server each tool has an admin-chosen name and a fixed type; the type (SYSTEM_EXECUTE_SQL, CORTEX_SEARCH_SERVICE_QUERY, CORTEX_ANALYST_MESSAGE) is not visible on the wire at call time. The type constants are included in the suffix set so the policy fires for deployments that happen to name tools after their type, but a managed deployment that names its SQL tool sales-sql (or anything else) will not be matched until you add that name. Pin your configured names in pii_result_suffixes per the landscape guidance. See Known limitations.

Verify the exact names your gateway emits with the dump-input debug technique before relying on this in production.

Response shape

The policy reads input.payload.text — the MCP content-block array the gateway populates on tool_post_invoke — and rewrites each string block (including string blocks containing serialized JSON row data, since the regexes run over the serialized text). Non-string blocks pass through unmodified. When at least one block changes, the policy emits transform.transformed_payload containing the original payload with the rewritten text array (all other payload keys preserved). When nothing changes, no transform is emitted and the response passes through byte-identical.

Examples

Redacted (in-scope tool, non-cleared caller)

{
  "input": {
    "action": "tool_post_invoke",
    "mode": "output",
    "resource": { "name": "snowflake-read_query", "type": "tool" },
    "subject": { "sub": "auth0|u1", "claims": { "groups": ["analysts"] } },
    "payload": {
      "name": "snowflake-read_query",
      "text": ["cust 42 | ssn 123-45-6789 | jane@acme.com | 206-555-0100"]
    }
  }
}

allow = true, with transform.transformed_payload.text = ["cust 42 | ssn [REDACTED-SSN] | [REDACTED-EMAIL] | [REDACTED-PHONE]"].

Passed through (cleared caller)

{
  "input": {
    "action": "tool_post_invoke",
    "mode": "output",
    "resource": { "name": "snowflake-read_query", "type": "tool" },
    "subject": { "sub": "auth0|u2", "claims": { "groups": ["pii-cleared"] } },
    "payload": {
      "name": "snowflake-read_query",
      "text": ["cust 42 | ssn 123-45-6789"]
    }
  }
}

allow = true, no transform — the pii-cleared group receives raw content.

Composition

Single-purpose transform policy (default allow := true); it composes cleanly with deny/transform policies on the same egress pipeline. Recommended companions for apps/snowflake:

• mask-pan-egress (PF-01) — cardholder PAN masking (Luhn-validated, mask to BIN+last4) is intentionally left to that companion policy and is not handled here. Attach both for cardholder-data environments.
• A guard-warehouse-sql-style ingress deny (PF-07) that blocks DML/DDL, GRANT/REVOKE, and export constructs (COPY INTO @, external stages) in the SQL argument — so data redacted on read cannot be bulk-exported around the gateway instead.
• A default-deny-unknown-tools-style ingress allowlist (PF-28) — on the managed server, tool names are admin-defined and drift; a default-deny allowlist stops a newly-added (unredacted) result tool from silently reaching the agent.
• A cap-bulk-export-style ingress guard (PF-08) that clamps result limit, bounding the blast radius of any redaction miss.

Known limitations

• Managed-server tool names are admin-chosen — the type constants are a best-effort, not a guarantee. The Snowflake-managed MCP server names each tool arbitrarily; the tool type (SYSTEM_EXECUTE_SQL, CORTEX_SEARCH_SERVICE_QUERY, CORTEX_ANALYST_MESSAGE) is not on the wire at call time. This policy matches those type constants opportunistically, but a managed deployment that names its SQL/Cortex tools anything else (e.g. sales-sql, product-search) is not covered until you add the configured names to pii_result_suffixes. Pair this with a default-deny-unknown-tools allowlist so an unmatched result tool cannot silently leak.
• CORTEX_AGENT_RUN and GENERIC tools are not matched. Cortex Agent invocations run opaque multi-step plans server-side and GENERIC tools wrap arbitrary UDFs/procedures; their response shapes are not predictable. Deny those tools at ingress rather than relying on egress redaction (see the landscape note).
• Cardholder PAN is out of scope. PAN detection/masking is deliberately delegated to the companion mask-pan-egress (PF-01) policy; this policy does not attempt Luhn validation or card masking.
• Pattern-based detection is best-effort and conservative by design. SSNs are matched in the canonical hyphenated form only — bare 9-digit runs collide with row IDs and sequence values, and dot- or space-separated forms (123.45.6789, 123 45 6789) are not matched; phones only in separator-formatted US shapes ((206)555-0100 with no space after the parenthesis, tab-separated forms, and bare 10-digit runs are not matched); emails only when word-boundary anchored. Obfuscated, split-across-cells, spelled-out, full-width/unicode-digit, or non-US-formatted values are not caught. Treat this as a high-signal minimum-necessary layer, not a complete DLP solution.
• Characters glued directly to a value defeat the word-boundary anchors (red-team residual). The SSN and phone patterns are \b-anchored, so a value with an extra digit or letter adjacent and no delimiter escapes detection: 123-45-67890 (SSN with a trailing digit), id00123-45-6789 (leading digits), nameX123-45-6789 (letter-prefixed), and 206-555-01000 (phone with a trailing digit) all pass through unredacted. This is a deliberate trade-off — dropping the boundary anchors would emit partial redactions such as [REDACTED-SSN]0 (which still leaks the extra digit) and fire false positives on longer numeric IDs. Where result columns concatenate identifiers without delimiters, rely on column-level masking in Snowflake or a stricter companion policy rather than this egress backstop.
• The email pattern can over-match inside connection strings. A user:password@host.example.com substring in a returned DSN/connection string matches the email shape and is redacted. On egress this is over-redaction (safe), not disclosure, but it can obscure legitimate non-email content — tune email_pattern if your result sets routinely contain such strings.
• Non-string content blocks pass through unmodified. Redaction applies to string entries of input.payload.text (including serialized-JSON strings). If your gateway emits structured non-string blocks for Snowflake results, verify their shape with the dump-input technique.
• Group names are placeholders — replace pii-cleared with your IdP's group name at import time. The exemption expects the groups claim as an array of strings (a single bare string is also handled); if your IdP emits roles under a namespaced claim, adjust caller_groups. Missing claims always mean redaction applies — the failure mode is over-redaction, not disclosure. Never rely on stripped ContextForge-internal claims (is_admin, teams, user) for the exemption.
• Egress transformed_payload replaces the response payload wholesale. Verify the rewrite against your gateway version with the dump-input technique before production, and mind attachment order if other egress transforms (e.g. mask-pan-egress) run on the same pipeline.

Compliance note. This policy supports alignment with the cited framework controls on the MCP path only. No policy or bundle makes an organization compliant with any framework; web-UI, native-API, and in-app access are outside the gateway's reach by design. Validate against your own compliance program before relying on it.

Policy source (Rego)

package snowflake.egress.redact_pii

# Transform-only egress policy: rewrites SSN, email, and phone patterns in the
# result sets returned by Snowflake's result-returning MCP tools to fixed
# redaction tokens before the response reaches the agent. Never denies — a
# legitimate query still succeeds, just with sensitive fields masked. A backstop
# for tables that lack Snowflake dynamic data masking policies. Callers in the
# placeholder `pii-cleared` IdP group receive unredacted responses; the group
# check fails closed, so a caller with missing claims gets over-redaction, never
# disclosure. Cardholder PAN masking is left to the companion mask-pan-egress
# (PF-01) policy.
default allow := true

# -----------------------------------------------------------------------------
# Scope: the result-returning Snowflake tools. The gateway prefixes tool names
# with the configured MCP server name (not standardised), so we match by
# suffix, case-insensitively.
#
# The first three are VERIFIED wire names from the two open-source servers. The
# last three are the managed server's tool-TYPE identifiers, which are NOT
# guaranteed to be the wire name (managed-server tools are admin-named and the
# type is not visible at call time) — they are matched opportunistically. Pin
# your managed/Cortex deployment's actual tool names here. See the policy's
# Known limitations.
# -----------------------------------------------------------------------------

pii_result_suffixes := {
    # Community server (isaacwasserman) — verified: SELECT-only query tool
    "read_query",
    # Snowflake-Labs server — verified: SQL passthrough + semantic-view read
    "run_snowflake_query",
    "query_semantic_views",
    # Managed server — tool-TYPE constants (see note above), not guaranteed names
    "system_execute_sql",
    "cortex_search_service_query",
    "cortex_analyst_message",
}

is_pii_result_tool if {
    input.mode == "output"
    some suffix in pii_result_suffixes
    endswith(lower(object.get(object.get(input, "resource", {}), "name", "")), suffix)
}

is_pii_result_tool if {
    # Egress hooks also expose the tool name under tool_metadata.name — check
    # both so we match regardless of which surface the gateway populates.
    input.mode == "output"
    some suffix in pii_result_suffixes
    meta := object.get(input, "tool_metadata", {})
    endswith(lower(object.get(meta, "name", "")), suffix)
}

# -----------------------------------------------------------------------------
# Group exemption — placeholder IdP group whose members receive unredacted
# responses. Replace "pii-cleared" with your IdP's group name at import time.
# object.get chains mean a missing subject/claims/groups claim is never
# cleared: the grant fails closed and redaction applies.
# -----------------------------------------------------------------------------

exempt_groups := {"pii-cleared"}

caller_groups := object.get(
    object.get(object.get(input, "subject", {}), "claims", {}),
    "groups",
    [],
)

is_exempt if {
    some g in caller_groups
    lower(g) in exempt_groups
}

is_exempt if {
    # Some IdPs emit a single group as a bare string rather than an array.
    is_string(caller_groups)
    lower(caller_groups) in exempt_groups
}

# -----------------------------------------------------------------------------
# Detection patterns — anchored and conservative to limit false positives on
# free-text warehouse columns.
# -----------------------------------------------------------------------------

# US SSN in the canonical hyphenated form only. Bare 9-digit runs collide with
# row IDs and sequence values, so they are deliberately not matched.
ssn_pattern := `\b\d{3}-\d{2}-\d{4}\b`

# Email addresses, word-boundary anchored: local part, "@", domain, TLD of at
# least two letters. Conservative TLD class keeps it from firing on stray "@".
email_pattern := `\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}\b`

# Separator-formatted US phone numbers (e.g. 206-555-0100, (206) 555-0100,
# +1 206.555.0100). Bare 10-digit runs are deliberately not matched. The 3-3-4
# grouping is disjoint from the SSN 3-2-4 grouping, so the two never collide.
phone_pattern := `(?:\+?1[-. ])?(?:\(\d{3}\)|\b\d{3})[-. ]\d{3}[-. ]\d{4}\b`

# -----------------------------------------------------------------------------
# Redaction steps — each is total over strings: it returns the input unchanged
# when its class doesn't apply, so the steps chain safely.
# -----------------------------------------------------------------------------

redact_ssn(t) := regex.replace(t, ssn_pattern, "[REDACTED-SSN]")

redact_phone(t) := regex.replace(t, phone_pattern, "[REDACTED-PHONE]")

redact_email(t) := regex.replace(t, email_pattern, "[REDACTED-EMAIL]")

# Order: SSN first (fixed 3-2-4 shape), then phones (3-3-4, disjoint from SSN),
# then emails (contain "@", disjoint from both digit patterns). The redaction
# tokens contain no digits-with-separators or "@", so no step can re-match a
# token emitted by an earlier step.
redact_block(b) := redact_email(redact_phone(redact_ssn(b))) if {
    is_string(b)
}

# Non-string content blocks (structured blocks) pass through unmodified.
redact_block(b) := b if { not is_string(b) }

# -----------------------------------------------------------------------------
# Transform — emitted only when in scope, the caller is not cleared, and at
# least one block actually changed. Otherwise the rule is undefined and the
# aggregator skips this policy, returning the response byte-identical.
# -----------------------------------------------------------------------------

response_payload := object.get(input, "payload", {})

text_blocks := object.get(response_payload, "text", [])

redacted_blocks := [out |
    some block in text_blocks
    out := redact_block(block)
]

transform := {
    "transformed_payload": object.union(response_payload, {"text": redacted_blocks}),
} if {
    is_pii_result_tool
    not is_exempt
    is_array(text_blocks)
    redacted_blocks != text_blocks
}

Canonical source: policy.md on GitHub · raw · raw on this site (.md)

Used in these guides

• Let agents query the warehouse without draining it

Related policies