Security context

The security context describes the caller of a query: an email, admin flags, tags and group memberships. It travels with the request and is read by the branch’s security policies to decide which rows the statement may return and which values it may show. 0sql has no users of its own; your application builds the context from its own authentication system, per request.

The JSON

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

Every key is optional. Unknown keys, at either level, are rejected. Nothing from the context is stored.

When it is required

A branch with at least one policy refuses to plan without a context:

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

HTTP 400. On a branch with no policies the context is optional and the security phase is skipped. POST .../explore accepts a context and ignores it. An empty object {} is a valid context: it identifies nobody, so every fired policy resolves nothing and applies its unresolved rule.

How a policy reads it

A policy (authored in security.yml, see Row-level security) fires on a node when the node projects a field (not a calculation) carrying one of the policy’s trigger tags or listed by name. Then, per policy:

1. Bypass. If bypass.system_admin is set and the context has system_admin: true, or bypass.project_admin and project_admin: true, the policy is skipped.

2. Allowed values of the policy’s context dimension are resolved from the context:

   source	value_from	allowed values
   groups	name	the names of the caller’s groups
   groups	tag	the values of group tags whose key is the policy’s tag_key
   user	email	the caller’s email
   user	tag	the values of the caller’s own tags with that key

   Values are deduplicated. Other combinations resolve nothing.

3. Unresolved. Nothing resolved and the policy says unresolved: allow: skipped. unresolved: deny (the default): filter_data renders WHERE 1 = 0 and mask_data masks every value.

4. Apply. filter_data adds WHERE LOWER(<context dimension>) IN ('tmnt', 'avcl') to the node. mask_data wraps the triggered field: CASE WHEN LOWER(<context dimension>) IN (...) THEN field ELSE <mask> END, where the mask is mask_value (default #######) for a string field and NULL for a numeric one.

If the context dimension cannot be joined from the node that fired the policy, the planner refuses rather than return unprotected rows:

{"error": {"class": "Planner::SecurityPolicyError",
 "message": "Security policy context dimension 'Call Center ID' is not reachable in the universe for this query. Cannot safely enforce security policy."}}

Examples

The branch has one policy: filter_data, triggered by the tag pii, context dimension Call Center ID, permissions from group tags with key call_center_id, system_admin bypass on, unresolved: allow. Country and Employees on call_center are tagged pii.

Filter

curl -s https://app.0sql.io/projects/tpcds/branches/main/sql \
  -H "Authorization: Bearer zqk_..." \
  -H "Content-Type: application/json" \
  -d '{
    "spec": {"name": "security_test", "projections": [{"field": "country", "alias": "Country"}]},
    "context": {
      "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"]}]
    }
  }'

cat > tank.json <<'EOF'
{"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"]}]}
EOF
zsql sql --expr 'country as Country' --context tank.json

// `user` comes from your own session or JWT
const context = {
  email: user.email,
  system_admin: user.roles.includes("admin"),
  groups: user.callCenters.map((id) => ({ name: `CC-${id}`, tags: [`call_center_id:${id}`] })),
};
const res = await fetch("https://app.0sql.io/projects/tpcds/branches/main/sql", {
  method: "POST",
  headers: { Authorization: `Bearer ${key}`, "Content-Type": "application/json" },
  body: JSON.stringify({ spec: { projections: [{ field: "country", alias: "Country" }] }, context }),
});

SELECT
	T0."cc_country" AS "Country"
FROM
	call_center T0
WHERE
	LOWER(T0."cc_call_center_id") IN ('tmnt', 'avcl')

The two call_center_id tags across the caller’s groups became the allowed values; the cat:men tag has a different key and is ignored.

Mask

The same context against a mask_data policy with mask_value: "#######", projecting employees (an integer):

{"spec": {"projections": [{"field": "employees", "alias": "Employees"}]},
 "context": {"email": "tank@matrix.com", "groups": [
   {"name": "CC-TMNT", "tags": ["call_center_id:TMNT"]},
   {"name": "CC-AVCL", "tags": ["call_center_id:AVCL", "cat:men"]}]}}

SELECT
	CASE WHEN LOWER(T0."cc_call_center_id") IN ('tmnt', 'avcl') THEN T0."cc_employees" ELSE NULL END AS "Employees"
FROM
	call_center T0

Every call center still appears as a row; the employee count is NULL outside the caller’s centers. On a string field the ELSE would be '#######'.

Admin bypass

The first request with "system_admin": true:

SELECT
	T0."cc_country" AS "Country"
FROM
	call_center T0

The policy’s bypass.system_admin is true, so it is skipped and no WHERE is added.

No context

The same request with no context key answers 400 ContextRequired.

Building the context in your application

• Build it per request from your own auth system: the session’s email, the roles that map to system_admin and project_admin, and the tenant, region or account memberships your policies key on as groups or tags.
• API keys are not users. A query key identifies your application and what it may plan; the context identifies the person the statement is for. One key serves every end user.
• Match the policy’s resolution. A policy that reads groups by tag with tag_key: call_center_id needs groups carrying call_center_id:<value> tags; names alone will not resolve, and an unresolved policy denies by default.
• The comparison is lower-cased on both sides, so TMNT in a tag matches tmnt in the warehouse.
• Policies apply inside segment CTEs, aggregation CTEs and decorator CTEs alike, because they fire per node. Use Explain to see security_filters and security_masks on each node.

Authoring policies, trigger tags, unresolved and bypass flags are covered on Row-level security.

Next steps

• Row-level security
• Explain to see what a policy added
• Errors and corrections