How it queries

Copy Markdown View Source

Every question Sekisho answers is one condition, built in Elixir and run in your database. This guide shows what that condition is, so you can read its plans and give it the indexes it needs.

One condition

For a permission on a schema:

(allow OR id IN (records where a role that gives it is held)) AND NOT deny

allow and deny are your conditions, as you wrote them. The records where a role is held are a set of ids: the union of every path to them.

SELECT d.* FROM documents AS d
WHERE (d.author_id = $1 OR d.id IN (
         SELECT resource_id FROM sekisho_grants
          WHERE resource_type = 'documents' AND role = ANY($2) AND actor_type = 'users' AND actor_id = $3
         UNION ALL
         SELECT id FROM documents WHERE org_id IN (SELECT resource_id FROM memberships ...)
         UNION ALL
         SELECT id FROM documents WHERE folder_id IN (
           WITH RECURSIVE tree AS (
             SELECT resource_id FROM sekisho_grants WHERE ... -- the folders shared with me
             UNION
             SELECT f.id FROM folders f JOIN tree t ON f.parent_id = t.id
           ) SELECT id FROM tree)))
  AND NOT d.locked

Each path is a query of its own, on its own indexes, and the database hashes the union once. There is no chain of OR over joins, which a planner cannot estimate.

  • allowed?/3 on a record is this condition and id = $record: one query on the primary key.
  • where_allowed/3 adds it to your query.
  • with_permissions/3 puts one condition per permission in the SELECT of one query over the records.
  • A rule about the actor alone (true, false) adds nothing: no query for allowed?/3, and your query as it is for where_allowed/3.

Nested records

A role passed down a relation to the same schema (a folder's parent) is a recursive query, from the records that hold it themselves down to all their descendants. It uses UNION, not UNION ALL, so a loop in your data ends.

Indexes

Sekisho's tables come with theirs: grants by record, by actor, and links by token. Yours need:

  • the foreign keys that derive follows: documents(folder_id), documents(org_id), folders(parent_id);
  • the actor columns your sources filter by: memberships(user_id), team_memberships(user_id);
  • the columns of your own conditions, as for any query.

Phoenix's generators index foreign keys; check the ones you added by hand.

Statistics and JIT

The plans rely on PostgreSQL's estimates. Keep autovacuum on, or ANALYZE after a large import. PostgreSQL's JIT compiler starts above a cost it estimates; on a database whose statistics are missing (a test database, a fresh restore) a large estimate can make JIT cost more than the query. SET jit = off for the role, as many apps do, if you see "JIT" in a plan of a fast query.

What it does not do

  • No cache between requests. A grant, a membership or a role change counts at the next question. Within a LiveView, the hook checks again when grants change.
  • No checks in memory. A record is checked as it is in the database. A rule about the actor alone needs no database at all.
  • PostgreSQL only, for the recursive queries and the arrays of custom roles.