Security context

Row-level security in 0sql is part of planning. A policy in security.yml names the fields that trigger it, the dimension that holds the allowed values, and where in the caller’s context those values come from. Your application builds a context object for the user making the request and sends it beside the spec. The planner resolves the allowed values from it and adds a WHERE (filter rows) or a CASE (mask values) to the statement. Nothing from the context is stored. Reference: Security context and Security.

The policy

A call-center project tags Country and Employees on the Call Center table with pii, and restricts them to the call centers a user’s groups are tagged with.

# models/common/tbl.call_center.yml (fragment)
  - type: dimension
    name: Call Center ID
    data_type: string
    expression:
      sql: cc_call_center_id
  - type: dimension
    name: Country
    data_type: string
    tags: [pii]
    expression:
      sql: cc_country
  - type: dimension
    name: Employees
    data_type: integer
    tags: [pii]
    expression:
      sql: cc_employees
# security.yml
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

Read it as: when a query projects a field tagged pii, collect the values of call_center_id: tags across the user’s groups, and keep only rows whose Call Center ID is one of them. A system admin skips the policy. If the context yields no values the policy is skipped (unresolved: allow); with the default deny the query would return no rows (WHERE 1 = 0).

The context your app builds

Per request, from your own session or identity provider:

{
  "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"]}
  ]
}

Every key is optional; unknown keys are rejected. tags are the user’s own key:value tags for source: user policies; groups carry names and tags for source: groups policies. The cat:men tag is ignored by this policy because its tag_key is call_center_id.

filter_data

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"]}]}}'

With zsql: zsql sql --expr "country as Country" --context ctx.json.

SELECT
	T0."cc_country" AS "Country"
FROM
	call_center T0
WHERE
	LOWER(T0."cc_call_center_id") IN ('tmnt', 'avcl')
  • The policy fired because Country carries the pii tag.
  • Allowed values came from group tags. TMNT and AVCL are the values of the call_center_id: tags; the comparison is lower-cased on both sides.
  • The context dimension need not be projected. Call Center ID is reached through the model; if it cannot be reached from the query’s tables the request fails with Planner::SecurityPolicyError rather than returning unfiltered rows.

mask_data

Change the policy to mode: mask_data (with mask_value: "#######") and project employees, an integer, with the same context:

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

Rows stay. The triggered field is replaced where the context dimension is outside the allowed set. The mask is mask_value for string fields and NULL for numeric fields, so an integer column is never handed a string literal.

system_admin bypass

Send the same filter_data request with "system_admin": true in the context and the statement is the plain projection:

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

bypass.system_admin defaults to true; bypass.project_admin defaults to false. Set both to false on a policy that should apply to everyone.

Omitting the context

On a branch that has any policy, a request without context is refused before planning:

{"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.

Variations

  • Two policies, two predicates. A second policy keyed on tag_key: cat and triggered by another tag adds AND LOWER(…) IN ('men') when both tagged fields are projected; the cat:men tag in the context above is what unlocks it.
  • Self-service by email. source: user, value_from: email resolves the context’s email as the single allowed value: WHERE LOWER(employee_email) IN ('tank@matrix.com'). The user’s own tags work the same way with value_from: tag.
  • Group names as values. source: groups, value_from: name uses the group names (CC-TMNT, CC-AVCL) directly; no tag_key.

Next steps