Skip to content

Rejects and rewrites

A QueryRejectPolicy rejects a query because of its form. A QueryRewritePolicy limits the rows and the run time of a query. Sluice applies both after access control.

Example

Reject a query

Goal: agents must name the columns they read.

apiVersion: sluice.bino.bi/v1alpha1
kind: QueryRejectPolicy
metadata:
  name: agent-shapes
  priority: 200
spec:
  match:
    any:
      - subjects:
          groups: ["agents"]
  reject:
    rules:
      - name: no-select-star
        expression: "query.has_select_star == true"
        message: "list the columns you need"
      - name: no-big-joins
        expression: "query.joins > 2"
        message: "use at most two JOINs"
        code: "TOO_MANY_JOINS"

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_agent.supersecret" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM shop.main.orders"}'

{
  "code": "ACL_REJECTED",
  "message": "list the columns you need",
  "query_id": "<query-id>",
  "policy": "agent-shapes"
}

What happened: the rule no-select-star was true. Sluice rejected the query with HTTP 403 and the rule's message.

Limit a query

Goal: an agent gets at most three rows and five seconds.

apiVersion: sluice.bino.bi/v1alpha1
kind: QueryRewritePolicy
metadata:
  name: agent-limits
  priority: 100
spec:
  match:
    any:
      - subjects:
          groups: ["agents"]
  rewrite:
    limit:
      max: 3
    timeout: "5s"

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_agent.supersecret" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT id, status FROM shop.main.orders ORDER BY id"}'

{
  "query_id": "<query-id>",
  "columns": ["id", "status"],
  "rows": [
    [1, "paid"],
    [2, "open"],
    [3, "paid"]
  ],
  "row_count": 3,
  "truncated": true,
  "warning": {
    "code": "ERR_RESULT_TRUNCATED",
    "message": "result truncated"
  }
}

- name: a query without LIMIT gets the limit
  identity: { subject: report-agent, groups: [agents] }
  sql: "SELECT id, status FROM shop.main.orders ORDER BY id"
  expect:
    outcome: allow
    rewrittenSql: "SELECT id, status FROM shop.main.orders ORDER BY id LIMIT 3"

What happened: Sluice added LIMIT 3 to the SQL. The result reached the limit, so the response says truncated: true.

Fields

spec.reject.rules needs at least one entry.

Field Type Default Meaning
name string Optional. A suite names a rule as policy/rule.
expression string CEL that returns a boolean. When empty, the rule fires for every matched query. An error at run time denies the query with ACL_DENIED.
message string Text for the client.
code string ACL_REJECTED Error code for the client. The HTTP status stays 403.

An expression reads subject.*, as in conditions, and these values. Some are partial checks:

Value Type Notes
query.has_select_star boolean The outer query selects * or t.*. In a UNION, any of its SELECTs does. A star in a subquery does not count.
query.has_where boolean In a UNION, every SELECT has a WHERE.
query.has_order_by, query.has_limit boolean The ORDER BY and LIMIT of the whole statement. One in a subquery or in one SELECT of a UNION does not count.
query.has_cte, query.is_recursive_cte boolean A WITH anywhere in the statement.
query.has_union boolean Also true for INTERSECT and EXCEPT.
query.is_aggregate boolean True for GROUP BY, HAVING and these functions: count, sum, avg, min, max, bit_and, bit_or, bit_xor, string_agg, array_agg, json_agg, jsonb_agg. False for other functions, such as median(x), and inside a call, a cast or CASE: round(avg(x)), sum(x)::INT. Any function with DISTINCT counts as an aggregate. In a UNION, every SELECT must aggregate.
query.limit int 0 when the LIMIT is not a plain number, such as 500 + 1 or '500'.
query.joins int Counts each JOIN keyword and each comma between FROM items, also in subqueries.
query.where_columns, query.group_by_columns list of text Names as written: o.status is not status. where_columns lists a column that is an operand of an operator, AND, OR, NOT or IS NULL of the outer WHERE. Operators are comparisons, arithmetic, text joining and NULLIF. A column in any other form is missing: a function call, a cast, CASE, IS TRUE, a value list, a row or a subquery. A bound of BETWEEN and a column before IN (SELECT ...) are missing too. group_by_columns lists only plain column names, not ROLLUP (x) or a position such as 1. In a UNION, both lists hold the columns of all its SELECTs.
query.catalogs list of text
query.tables list of text Lower case, catalog.schema.table.

spec.rewrite needs at least one of these fields. limit and sample change SELECT statements only.

Field Type Default Meaning
limit.max int Row limit, 1 to 2147483647. Sluice adds a LIMIT or lowers a larger one. A smaller whole number stays. Any other LIMIT, such as LIMIT 1 + 1, becomes max.
sample.rate number Share of rows to return, above 0 and at most 1.
sample.method string reservoir reservoir, bernoulli or system.
timeout duration Longest run time, "10s" or seconds as a number. Then the client gets ERR_TIMEOUT.

A policy timeout can only shorten the timeout of the request and the server. When several rewrite policies match, Sluice uses the lowest limit, the shortest timeout and the first sample in priority order. A QueryRewritePolicy with an empty spec.match loads and matches no query.

More examples

Sample

rewrite:
  sample:
    rate: 0.1
    method: bernoulli

- name: the query is wrapped in a sample
  identity: { subject: eve, groups: [explorers] }
  sql: "SELECT id FROM shop.main.orders"
  expect:
    outcome: allow
    rewrittenSql: >-
      SELECT * FROM (SELECT id FROM shop.main.orders) AS sluice_sample
      USING SAMPLE 10% (bernoulli)

With a limit and a sample, Sluice samples the limited rows.

Limits

  • The client error and the audit record name the policy, not the rule.
  • Most query.* values do not read subqueries. Only joins, has_cte, is_recursive_cte, tables and catalogs cover the whole statement.
  • Sluice refuses every statement with a $n parameter. See Request lifecycle.
  • A limit or a sample makes Sluice refuse some SQL forms.

See also