Row-Level Security

Security policies restrict which rows and values each caller can see. They live in security.yml, deploy with the model, and are applied by the planner, so the SQL that comes back already carries the WHERE clause or the CASE mask. 0sql has no end users of its own: your application decides who the caller is and sends that as the context of every request.

Enforcement happens where the data is. 0sql never connects to your warehouse and never sees a row, so a policy is not a filter applied to results in transit: it is a predicate compiled into the statement before you run it. The rows a caller may not see are never read in the first place.

How a policy works

A policy has three parts, evaluated for every query node:

1. Triggers decide whether it fires: when the query projects a field carrying one of the policy’s tags, or one of its named fields. A query that projects no triggered field is unaffected.
2. Context dimension is the boundary: the dimension whose values partition the data, such as Call Center ID or Employee Email. It must be reachable from every table that holds a triggered field; if it is not, the query is refused (422 Planner::SecurityPolicyError) rather than returning unprotected rows.
3. Permission resolution works out which values of that dimension the caller may see, from the groups or the user in the context.

Modes

The context

Your application sends the caller’s security context in the request body context. The planner reads it and stores nothing.

{
  "email": "tank@matrix.com",
  "system_admin": false,
  "project_admin": false,
  "tags": ["region:EMEA"],
  "groups": [{"name": "CC-TMNT", "tags": ["call_center_id:TMNT"]}]
}

Unknown keys are rejected. Tags are key:value strings, on the user (tags) or on each group. Where they come from (your identity provider, your own database, a JWT) is up to you; 0sql only sees what you send.

A branch with at least one policy refuses to plan without a context (HTTP 400):

{"error": {"class": "ContextRequired", "message": "this branch has security policies; a context is required to plan"}}

On a branch without policies the context is optional.

Tagging fields

Add tags to any dimension or measure in a table file:

fields:
  - type: dimension
    name: Customer First Name
    tags: [pii]
    data_type: string
    expression:
      sql: c_first_name

Use one tag per sensitivity class (pii, hipaa, finance) so protecting a new field means tagging it, not editing the policy.

security.yml reference

policies:
  - name: PII Call Center Masking
    mode: mask_data              # mask_data | filter_data
    mask_value: "#######"        # mask_data only, default "#######"

    triggers:
      field_tags: [pii]          # any field tagged "pii"
      field_names:               # optional: specific fields by name
        - Customer First Name

    context_dimension: Call Center ID

    permission_resolution:
      source: groups             # groups | user
      value_from: tag            # groups: name | tag   /   user: email | tag
      tag_key: call_center_id    # required when value_from is "tag"
      unresolved: deny           # deny | allow

    bypass:
      system_admin: true
      project_admin: false

The file is optional. When present it must have a top-level policies list, and it is the source of truth for the branch on every zsql deploy: policies: [] removes every policy. Policies are branch-scoped; one deployed to staging does not affect main.

name

Required. The policy’s identity within the branch.

mode

Required. mask_data or filter_data.

mask_value

mask_data only. The string shown in place of restricted string values. Defaults to #######. Numeric fields are masked to NULL regardless.

triggers

• field_tags: fires when a query projects any field carrying one of these tags.
• field_names: explicit dimension or measure names, resolved at deploy time. A name that does not exist fails the deploy: Policy '<n>': trigger field '<f>' not found in this branch.

A policy with neither never fires.

context_dimension

Required. A dimension name, resolved at deploy time (Policy '<n>': context_dimension '<d>' not found in this branch.). It may not be a composite bracket-reference expression. Every table holding a triggered field needs a join path to it, so pick a key that every fact carries. If a later deploy drops the dimension, the policy stays but is inert and the deploy warns policy <uid> context dimension <d> not found; policy is inert.

permission_resolution

source defaults to groups. tag_key is required when value_from is tag. Any other combination resolves nothing.

unresolved decides what happens when a fired policy resolves no values. The default, deny, applies the policy with an empty set: filter_data returns no rows (WHERE 1 = 0) and mask_data masks every triggered value, so a caller your application forgot to tag sees nothing rather than everything. allow skips the policy for that caller, which suits a policy meant to restrict only some callers.

bypass

• system_admin (default true): a context with "system_admin": true skips the policy.
• project_admin (default false): a context with "project_admin": true skips the policy.

Examples

Filter rows by group tag

policies:
  - name: Call center rows
    mode: filter_data
    triggers:
      field_tags: [pii]
    context_dimension: Call Center ID
    permission_resolution:
      source: groups
      value_from: tag
      tag_key: call_center_id
      unresolved: allow
    bypass:
      system_admin: true
      project_admin: false

{"email": "tank@matrix.com", "system_admin": false, "project_admin": false, "tags": [],
 "groups": [{"name": "CC-TMNT", "tags": ["call_center_id:TMNT"]},
            {"name": "CC-AVCL", "tags": ["call_center_id:AVCL", "cat:men"]}]}

Allowed values are TMNT and AVCL. A query projecting a pii field gains WHERE LOWER(T0."cc_call_center_id") IN ('tmnt', 'avcl'). A second policy keyed on cat adds its own AND LOWER(...) IN ('men'). A context with "system_admin": true gets the plain SELECT.

Mask values by group name

policies:
  - name: Mask first name outside my call centers
    mode: mask_data
    mask_value: "#######"
    triggers:
      field_names: [Customer First Name]
    context_dimension: Call Center ID
    permission_resolution:
      source: groups
      value_from: name

{"email": "tank@matrix.com", "groups": [{"name": "TMNT", "tags": []}, {"name": "AVCL", "tags": []}]}

Allowed values are the group names. Every row stays and Customer First Name is projected as CASE WHEN LOWER(T0."cc_call_center_id") IN ('tmnt', 'avcl') THEN T0."c_first_name" ELSE '#######' END. With "groups": [] and the default unresolved: deny every value is masked.

Filter rows to the caller’s own record

policies:
  - name: Employee self service
    mode: filter_data
    triggers:
      field_tags: [employee_pii]
    context_dimension: Employee Email
    permission_resolution:
      source: user
      value_from: email
    bypass:
      system_admin: true
      project_admin: false

{"email": "tank@matrix.com"}

The SQL gains WHERE LOWER(employee_email) IN ('tank@matrix.com'). The user-tag variant, source: user, value_from: tag, tag_key: region, is unlocked by {"tags": ["region:EMEA"]}.

Testing a policy

Put a context in a file and plan with it.

cat > user.json <<'EOF'
{"email": "tank@matrix.com", "system_admin": false, "project_admin": false, "tags": [],
 "groups": [{"name": "CC-TMNT", "tags": ["call_center_id:TMNT"]}]}
EOF

zsql sql --expr "customer first name, store net paid" --context user.json
zsql explain --expr "customer first name, store net paid" --context user.json

zsql sql prints the SQL with the filter or mask in place. zsql explain adds the node tree; a node the policy touched lists it under security_filters or security_masks. Repeat with "system_admin": true to confirm the bypass, with "groups": [] to see unresolved at work, and with no --context to get ContextRequired. Tests in tests/*.yml plan without a context, so they check the model, not the policies.

Best practices

1. Tag once, protect everywhere. Prefer field_tags over field_names.
2. Pick a context dimension every fact can reach. An unreachable one fails the query, not the deploy.
3. Keep unresolved: deny and the system admin bypass on.
4. Build the context in one place in your application, so every call to /sql uses the same mapping from your identity provider to tags and groups.

Next steps

• The security context in a request
• Explain output
• Accounts, keys and access