Skip to content

Cross-source joins

One SELECT joins a table in a SQLite file with a table in a DuckDB file. Sluice checks access for each table.

Example

Start the server with examples/cross-source-join/serve.sh. The script needs Go to write the DuckDB file.

Each data source is one catalog. Its name is the first part of catalog.schema.table.

# Transactional data in a SQLite file.
apiVersion: sluice.bino.bi/v1alpha1
kind: DataSource
metadata:
  name: shop
spec:
  type: sqlite
  path: ./data/shop.db

# Reference data in a DuckDB file.
apiVersion: sluice.bino.bi/v1alpha1
kind: DataSource
metadata:
  name: ref
spec:
  type: duckdb_file
  path: ./data/ref.duckdb

The allow covers one table in each catalog.

# One clause per catalog. A join needs an allow for every table it reads.
apiVersion: sluice.bino.bi/v1alpha1
kind: SqlAccessPolicy
metadata:
  name: allow-analytics
  priority: 100
spec:
  effect: allow
  match:
    any:
      - subjects:
          groups: ["analytics"]
        resources:
          catalogs: ["shop"]
          schemas: ["main"]
          tables: ["orders"]
      - subjects:
          groups: ["analytics"]
        resources:
          catalogs: ["ref"]
          schemas: ["main"]
          tables: ["countries"]

The analyst key joins both tables.

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, o.amount_cents, c.name AS country, c.region FROM shop.main.orders o JOIN ref.main.countries c ON c.code = o.country_code ORDER BY o.id"}'

{
  "query_id": "<query-id>",
  "columns": ["id", "amount_cents", "country", "region"],
  "rows": [
    [1, 1999, "United States", "Americas"],
    [2, 4599, "Germany", "EMEA"],
    [3, 7800, "France", "EMEA"],
    [4, 250, "United States", "Americas"],
    [5, 32000, "Japan", "APAC"]
  ],
  "row_count": 5,
  "truncated": false
}

What happened: Sluice attached both files and ran one query over them. The allow covers both tables, so the join passes.

More examples

The intern key may read ref.main.countries only. The same join is denied.

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_intern.supersecret" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT o.id, o.amount_cents, c.name AS country, c.region FROM shop.main.orders o JOIN ref.main.countries c ON c.code = o.country_code ORDER BY o.id"}'

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

A mask on orders.customer_id also applies inside the join.

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, o.customer_id, c.name AS country FROM shop.main.orders o JOIN ref.main.countries c ON c.code = o.country_code ORDER BY o.id"}'

{
  "query_id": "<query-id>",
  "columns": ["id", "customer_id", "country"],
  "rows": [
    [1, null, "United States"],
    [2, null, "Germany"],
    [3, null, "France"],
    [4, null, "United States"],
    [5, null, "Japan"]
  ],
  "row_count": 5,
  "truncated": false
}

This suite case shows the rewritten SQL. The mask stays on the orders side.

- name: analyst joins both catalogs, the mask stays on the orders side
  identity: { subject: analyst, groups: [analytics] }
  sql: >-
    SELECT o.id, o.customer_id, c.name AS country
    FROM shop.main.orders o JOIN ref.main.countries c ON c.code = o.country_code
  expect:
    outcome: allow
    applied: ["ColumnMaskPolicy/mask-customer-id", "SqlAccessPolicy/allow-analytics"]
    masks: ["shop.main.orders.customer_id=null"]
    rewrittenSql: >-
      SELECT o.id, o.customer_id, c.name AS country
      FROM (SELECT sluice_src.id, NULL AS customer_id, sluice_src.country_code, sluice_src.amount_cents
            FROM shop.main.orders sluice_src) o
      JOIN ref.main.countries c ON c.code = o.country_code

A QueryRejectPolicy rejects a query on more than one catalog for selected subjects.

# Contractors may read one catalog per query.
apiVersion: sluice.bino.bi/v1alpha1
kind: QueryRejectPolicy
metadata:
  name: one-catalog
  priority: 200
spec:
  match:
    any:
      - subjects:
          groups: ["contractors"]
  reject:
    rules:
      - name: no-second-catalog
        expression: "size(query.catalogs) > 1"
        message: "read one data source per query"

This suite case proves it.

- name: a contractor cannot join two catalogs
  identity: { subject: carl, groups: [analytics, contractors] }
  sql: >-
    SELECT o.id, c.name AS country
    FROM shop.main.orders o JOIN ref.main.countries c ON c.code = o.country_code
  expect:
    outcome: reject
    rejections: ["one-catalog/no-second-catalog"]

The same join on Postgres and S3 is in examples/cross-source-join/manual/postgres-s3/. make docs-check only validates its policies.

Limits

  • limits.disableCrossCatalog: true in the server config rejects every query that names more than one catalog: ACL_REJECTED.

See also