Access control¶

A SqlAccessPolicy allows or denies access to tables. Sluice denies a query unless an allow covers every table that it reads.

Example¶

Analysts may read orders.

apiVersion: sluice.bino.bi/v1alpha1
kind: SqlAccessPolicy
metadata:
  name: allow-analysts
  priority: 100
spec:
  effect: allow
  match:
    any:
      - subjects:
          groups: ["analysts"]
        resources:
          catalogs: ["shop"]
          schemas: ["main"]
          tables: ["orders"]

A query on the allowed table:

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

{
  "query_id": "<query-id>",
  "columns": ["id", "customer", "amount_cents"],
  "rows": [
    [1, "Alice", 1999],
    [2, "Bob", 4599],
    [3, "Alice", 250]
  ],
  "row_count": 3,
  "truncated": false
}

A query on another table:

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

{
  "code": "ACL_DENIED",
  "message": "no SqlAccessPolicy matched (default-deny)",
  "query_id": "<query-id>"
}

What happened: the allow names only orders. No policy allows customers, so Sluice denies the second query.

Fields¶

Field	Type	Default	Meaning
`effect`	string	required	`allow` or `deny`, in lower case.
`match`	object	required	Subjects and tables. See Matching.
`message`	string	`access denied`	For a deny: the error message.
`errorCode`	string	`ACL_DENIED`	For a deny: the error code.

exclude, conditions and enforcementMode work as on every policy.

resources.actions limits a clause to statement types: SELECT, INSERT, UPDATE or DELETE, in upper case. EXPLAIN SELECT counts as SELECT.

More examples¶

Every table needs an allow¶

An analyst joins orders with customers.

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_analyst.supersecret" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT o.id, c.email FROM shop.main.orders o JOIN shop.main.customers c ON c.name = o.customer"}'

{
  "code": "ACL_DENIED",
  "message": "no SqlAccessPolicy allows shop.main.customers",
  "query_id": "<query-id>"
}

A deny wins over an allow¶

Admins may read every table, but nobody reads salaries.

apiVersion: sluice.bino.bi/v1alpha1
kind: SqlAccessPolicy
metadata:
  name: allow-admins
  priority: 100
spec:
  effect: allow
  match:
    any:
      # No catalogs, schemas or tables: the clause matches every table.
      - subjects:
          groups: ["admins"]
        resources:
          actions: ["SELECT"]

apiVersion: sluice.bino.bi/v1alpha1
kind: SqlAccessPolicy
metadata:
  name: deny-salaries
  priority: 900
spec:
  effect: deny
  match:
    any:
      - resources:
          catalogs: ["shop"]
          schemas: ["main"]
          tables: ["salaries"]
  message: "salaries are not available through Sluice"

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_admin.supersecret" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT employee, amount_cents FROM shop.main.salaries"}'

{
  "code": "ACL_DENIED",
  "message": "salaries are not available through Sluice",
  "query_id": "<query-id>",
  "policy": "deny-salaries"
}

A deny on one table denies the whole query. If several denies match, the one with the highest priority gives the message.

- name: the deny wins over the allow for admins
  identity: { subject: ada, groups: [admins] }
  sql: "SELECT employee FROM shop.main.salaries"
  expect:
    outcome: deny
    denyPolicy: deny-salaries

- name: a deny on one table denies the whole query
  identity: { subject: ada, groups: [admins] }
  sql: "SELECT o.id, s.amount_cents FROM shop.main.orders o JOIN shop.main.salaries s ON s.employee = o.customer"
  expect:
    outcome: deny
    denyPolicy: deny-salaries

Actions¶

The admin allow lists SELECT, the analyst allow no action.

- name: the admin allow covers a SELECT
  identity: { subject: ada, groups: [admins] }
  sql: "SELECT id FROM shop.main.customers"
  expect:
    outcome: allow

- name: the admin allow does not cover a DELETE
  identity: { subject: ada, groups: [admins] }
  sql: "DELETE FROM shop.main.customers"
  expect:
    outcome: deny

- name: an allow without actions covers a DELETE, then Sluice rejects the write
  identity: { subject: alice, groups: [analysts] }
  sql: "DELETE FROM shop.main.orders"
  expect:
    outcome: allow
    errorCode: ACL_REJECTED

- name: SHOW has no action, so an allow with actions does not cover it
  identity: { subject: ada, groups: [admins] }
  sql: "SHOW TABLES"
  expect:
    outcome: deny

Deny, then reject, then approval¶

Sluice checks denies first. A denied query gets no reject rule, row filter, column mask or approval. Reject rules come next. An approval can hold only an allowed query.

- name: the reject rule fires on an allowed table
  identity: { subject: ada, groups: [admins] }
  sql: "SELECT * FROM shop.main.orders"
  expect:
    outcome: reject
    rejections: ["reject-select-star/no-select-star"]

- name: a deny comes before a reject
  identity: { subject: ada, groups: [admins] }
  sql: "SELECT * FROM shop.main.salaries"
  expect:
    outcome: deny
    denyPolicy: deny-salaries

Limits¶

Sluice runs only read statements. A write gets ACL_REJECTED, also when an allow lists its action. See Request lifecycle.
A statement without a table, such as SELECT 1, needs an allow clause without catalogs, schemas, tables and tags.
With another engine, a query without a matching allow can still run.