Row filters¶

A RowFilterPolicy limits the rows that a subject reads from a table. The filter applies to every read of the table, also in joins, subqueries, CTEs and set operations.

Example¶

Goal: each sales rep reads only their own orders. The table has five orders:

INSERT INTO orders (id, owner, region, status, amount_cents) VALUES
  (1, 'ana',  'eu', 'paid',      1999),
  (2, 'ana',  'us', 'open',      4599),
  (3, 'ben',  'eu', 'paid',      7800),
  (4, 'ben',  'us', 'cancelled',  250),
  (5, 'cleo', 'eu', 'open',     32000);

apiVersion: sluice.bino.bi/v1alpha1
kind: RowFilterPolicy
metadata:
  name: filter-own-orders
  priority: 80
spec:
  match:
    any:
      - subjects:
          groups: ["sales"]
        resources:
          catalogs: ["shop"]
          schemas: ["main"]
          tables: ["orders"]
  filter:
    predicate:
      column: owner
      op: Equals
      value: "{{ subject.sub }}"

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

{
  "query_id": "<query-id>",
  "columns": ["id", "owner", "status", "amount_cents"],
  "rows": [
    [1, "ana", "paid", 1999],
    [2, "ana", "open", 4599]
  ],
  "row_count": 2,
  "truncated": false
}

A suite case shows the rewritten SQL:

- name: a sales rep reads only their own orders
  identity: { subject: ana, groups: [sales] }
  sql: "SELECT id, owner, status, amount_cents FROM shop.main.orders ORDER BY id"
  expect:
    outcome: allow
    filters: ["shop.main.orders"]
    rewrittenSql: >-
      SELECT id, owner, status, amount_cents
      FROM (SELECT * FROM shop.main.orders sluice_src WHERE sluice_src.owner = $1) orders
      ORDER BY id

What happened: Sluice replaced the table with a subquery that holds the filter. The subject id is the bound parameter $1, never part of the SQL text. Ana gets only her two orders.

Fields¶

Field	Type	Default	Meaning
`spec.filter.predicate`	object		The filter as a tree of column checks.
`spec.filter.expression`	string		The filter as a CEL expression. Set this or `predicate`, not both.
`spec.combine`	string	`restrictive`	How this filter joins other filters on the same table.

Shared fields: Policies. Selectors: Matching. Table names match in any letter case.

Predicate¶

A check has a column, an op and a value or values. IsNull and IsNotNull take neither. Group checks with all, any and not. One node holds one of these or one check, or the policy does not load.

`op`	Takes	SQL
`Equals`, `NotEquals`	`value`	`col = $1`, `col <> $1`
`GreaterThan`, `GreaterThanOrEqual`	`value`	`col > $1`, `col >= $1`
`LessThan`, `LessThanOrEqual`	`value`	`col < $1`, `col <= $1`
`Between`	`values`, exactly 2	`col BETWEEN $1 AND $2`
`In`, `NotIn`	`values`, 1 or more	`col IN ($1, $2)`, `col NOT IN ($1)`
`Like`, `NotLike`	`value`	`col ~~ $1`, `col !~~ $1`, as SQL `LIKE`
`StartsWith`, `EndsWith`, `Contains`	`value`	`starts_with(col, $1)` and so on. `%` and `_` are plain text.
`Matches`	`value`	`regexp_matches(col, $1)`. A partial match counts.
`IsNull`, `IsNotNull`	nothing	`col IS NULL`, `col IS NOT NULL`

Template values¶

A value can be a template that Sluice fills for each request.

Template	Value
`{{ subject.sub }}`	Subject id
`{{ subject.issuer }}`, `{{ subject.email }}`	Issuer and email. A subject without it is denied.
`{{ subject.auth_method }}`	`jwt` or `api_key`
`{{ subject.groups }}`	The groups, as a list. For `In` and `NotIn` only.
`{{ subject.claims.<path> }}`, `{{ subject.jwt.<path> }}`	A claim. Dots reach nested claims.
`{{ subject.<name> }}`	Short form for a claim, for example `{{ subject.tenantId }}`
`{{ request.remote_ip }}`, `{{ request.user_agent }}`	IP address and user agent of the client
`{{ request.headers.<name> }}`	A request header, for example `{{ request.headers.x-region }}`

The client sets its headers and its user agent. Filter on them only when a proxy that you control sets them.

A template must be the whole value, or the policy does not load. In In and NotIn, a template that gives a list stands for one value per element. This also holds for a claim that is a list. With an empty list, In matches no row and NotIn matches every row.

More examples¶

Nested checks¶

predicate:
  all:
    - column: status
      op: In
      values: ["open", "paid"]
    - any:
        - column: region
          op: Equals
          value: "eu"
        - column: amount_cents
          op: GreaterThan
          value: 10000
    - not:
        column: owner
        op: StartsWith
        value: "test-"

- name: all, any and not become AND, OR and NOT
  identity: { subject: sam, groups: [support] }
  sql: "SELECT id FROM shop.main.orders"
  expect:
    outcome: allow
    rewrittenSql: >-
      SELECT id FROM (SELECT * FROM shop.main.orders sluice_src
      WHERE sluice_src.status IN ($1, $2)
      AND (sluice_src.region = $3 OR sluice_src.amount_cents > $4)
      AND NOT starts_with(sluice_src.owner, $5)) orders

CEL expression¶

row.<column> is a column. subject.<path> reads the same values as a template.

filter:
  expression: 'row.region == subject.claims.region && row.status != "cancelled"'

- name: a CEL filter becomes the same SQL as a predicate
  identity: { subject: rita, groups: [regional], claims: { region: eu } }
  sql: "SELECT id FROM shop.main.orders"
  expect:
    outcome: allow
    rewrittenSql: >-
      SELECT id FROM (SELECT * FROM shop.main.orders sluice_src
      WHERE sluice_src.region = $1 AND sluice_src.status <> $2) orders

You can use comparisons, &&, ||, !, row.x in [...] and startsWith, endsWith, contains with a text literal. Anything else is a load error.

Several filters on one table¶

Filters join in order of priority, highest first. Each filter joins the earlier ones with its own combine: restrictive is AND, permissive is OR.

apiVersion: sluice.bino.bi/v1alpha1
kind: RowFilterPolicy
metadata:
  name: filter-open-orders
  priority: 70
spec:
  match:
    any:
      - subjects:
          groups: ["leads"]
        resources:
          catalogs: ["shop"]
          schemas: ["main"]
          tables: ["orders"]
  # OR with the filters before this one.
  combine: permissive
  filter:
    predicate:
      column: status
      op: Equals
      value: "open"

Ben is in both groups, sales and leads:

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

{
  "query_id": "<query-id>",
  "columns": ["id", "owner", "status", "amount_cents"],
  "rows": [
    [2, "ana", "open", 4599],
    [3, "ben", "paid", 7800],
    [4, "ben", "cancelled", 250],
    [5, "cleo", "open", 32000]
  ],
  "row_count": 4,
  "truncated": false
}

- name: a permissive filter is joined with OR
  identity: { subject: ben, groups: [sales, leads] }
  sql: "SELECT id FROM shop.main.orders"
  expect:
    outcome: allow
    rewrittenSql: >-
      SELECT id FROM (SELECT * FROM shop.main.orders sluice_src
      WHERE sluice_src.owner = $1 OR sluice_src.status = $2) orders

SQL forms refused on filtered or masked tables¶

On a table with a row filter or column mask, Sluice refuses these forms. A query with only a limit or sample may use the first four.

Form	Error code	Write instead
`EXPLAIN`	`ERR_UNSUPPORTED_SYNTAX`	Not available
The text `sluice_src`	`ERR_UNSUPPORTED_SYNTAX`	Another name
Schema before a column: `main.orders.id`	`ERR_UNSUPPORTED_SYNTAX`	`orders.id`
A table without alias named `show`, `at`, `by`, `pivot` or a similar DuckDB keyword: `FROM shop.main.show`	`ERR_UNSUPPORTED_SYNTAX`	`FROM shop.main.show s`
`**` or `//` beside another arithmetic operator	`ERR_UNSUPPORTED_SYNTAX`	`pow(a, b)`, `divide(a, b)`
A condition in parentheses before `IS NULL`, `IN`, `BETWEEN`, `= ANY`	`ERR_UNSUPPORTED_SYNTAX`	`coalesce(a OR b) IS NULL`
Lambda with a comparison: `x -> x > 1`	`ERR_UNSUPPORTED_SYNTAX`	`x -> (x > 1)`
`->>` beside `AND`, `OR`, `NOT`	`ERR_UNSUPPORTED_SYNTAX`	`(j ->> 'a') = '1'`
`EXTRACT(days FROM d)`	`ERR_UNSUPPORTED_SYNTAX`	`EXTRACT(day FROM d)`
`ARRAY[[1, 2], [3, 4]]`	`ERR_UNSUPPORTED_SYNTAX`	`ARRAY[ARRAY[1, 2], ARRAY[3, 4]]`
Quoted DuckDB keyword: `WITH "at" AS`	`ERR_UNSUPPORTED_SYNTAX`	Another name
Some reserved words as a named argument: `struct_pack("default" := id)`	`ERR_UNSUPPORTED_SYNTAX`	Another name
Bare keyword alias: `SELECT 1 at`	`ERR_SYNTAX`	`SELECT 1 AS "at"`
`0x1f`, `0o17`, `0b11`	`ERR_SYNTAX`. At the end of a select item: `ERR_UNSUPPORTED_SYNTAX`	The decimal number
`INTERVAL '1 02' DAY TO HOUR`	`ERR_SYNTAX`	`INTERVAL '1 day 2 hours'`
`E'\u0041'`, `U&'\0041'`	`ERR_SYNTAX`	The character itself

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

{
  "code": "ERR_UNSUPPORTED_SYNTAX",
  "message": "the statement would change meaning when rewritten for row filters, masks or limits; see docs/policies/row-filters.md",
  "query_id": "<query-id>"
}

A row filter, column mask, limit or sample can change the case of a name. An alias or a struct_pack key comes back in lower case when the query also spells the word another way. EXTRACT(MONTH FROM d) AS Month returns month. Quote the alias to keep its spelling: AS "Month".

Limits¶

A template value that the subject does not have denies the query with ACL_DENIED. So does a list value on an op other than In and NotIn.
Sluice does not check the filter column. A table without it fails with ERR_QUERY_FAILED.
combine must be lower case. Any other value acts as restrictive.
Write table names with three parts. shop.orders is rejected with ACL_REJECTED.