Skip to content

The sync index (a preview) ​

Sync engines (Electric, Zero, PowerSync) read Postgres by logical replication, then decide on their own servers which rows each client gets. Row-level security doesn't run on that path, so an engine has to be told the rules again. The sync index tells it: for each row of a synced table, the keys that may read it, kept in tables the engine replicates like any other, in the same transaction as the change.

This is a preview. Its lines, tables and functions may change in any release, and it is outside what 1.0 will promise. Electric, Zero and PowerSync run on it, with the app's own server.

Declaring it ​

authz
sync role sync_engine        -- the engine's Postgres role
sync app.files : view        -- each file's readers hold view on it
sync app.folders             -- no permission: the table's select rule

Two words change the way the index is worked out, not what it says:

authz
sync app.files : view by folder   -- a file's readers name its folder's key, which its readers hold
sync after commit                 -- a worker keeps the index after each change commits
  • sync role names the role the engine connects as. Make it for the engine: CREATE ROLE sync_engine LOGIN REPLICATION BYPASSRLS. It can't be the app role or PUBLIC (AZ803). Applying refuses a role that isn't there, one that is a member of the app role or has it as a member, one that is a member of the role applying the policy, and a superuser (AZ808), which may turn row-level security off.
  • sync schema.table : permission names a type's table, one line each, and the permission its readers hold. With no permission, they hold the table's select rule, which the table must have (AZ801).
  • Sync lines need a sync role, and a sync role needs a sync line (AZ802). So does sync after commit.
  • by folder, on a sync line, keys its readers by a type's inheritance (Keyed by folder).
  • sync after commit, once in the policy, has a worker keep the index (After commit).
  • mirrored, on a sync line, keeps a copy of the table for PowerSync, each row with its readers (PowerSync). With by, it is refused (AZ811).

Its tables ​

Applying makes the schema authz_sync. The sync role may read it, and nobody else may: not the app role, nor PUBLIC. Every table there has REPLICA IDENTITY FULL, which Electric asks for.

tablewhat it holds
<type>__readersfor each row: the type's key columns (typed as its key), and key, a key that may read it
keysprincipal_type, principal_id, key: for each principal, the keys it holds
groupsgrp, key: the keys that make up a group's key
expandedobject_type, object_id, key: what a row's readers were worked out from, where they are principals
definitionswhat the index was worked out by, so that applying knows whether to work it out again
dueobject_type, object_id, at: for each object with a share that starts or ends later, the next such time after its readers were last worked out
pendingwhere the policy says sync after commit: the objects each committed change reached, which the worker hasn't worked in yet
inheritskeyed by folder: key, source: for each folder's key, the folders whose own keys it takes (itself, and each one above it that it inherits from)
givenkeyed by folder: source, key: the keys each folder gives itself, before what it inherits
<type>__mirrormirrored: the table's rows, every column but those the policy masks, and readers, a JSON array of the keys that may read the row

A key is one of these:

  • a principal: user:42, service:7;
  • every signed-in principal of a type: user:*;
  • anyone;
  • a group: the holders of a relation or permission of a group's object, team#member:10 or org#admin:1;
  • keyed by folder, the holders of a permission that inherits, on one object: folder#view:7. No principal holds it: inherits and given say who does (Keyed by folder).

A principal holds its own key, its type's user:*, anyone, and the keys of the groups it is in, through groups inside groups. principal_id is the principal's own id, and principal_type its type. A principal that has no row, or fails its type's where, holds no key: it is nobody, and the index gives it nothing.

The rows a principal may read are those whose readers hold one of its keys. An engine filters with that one join (Electric's where, set by the app's proxy, with the signed-in user's id):

sql
SELECT id FROM app.files
WHERE id IN (SELECT r.id FROM authz_sync.file__readers r
             WHERE r.key IN (SELECT k.key FROM authz_sync.keys k
                             WHERE k.principal_type = 'user' AND k.principal_id = '42'))

Keyed by folder, the filter has a second way in: a reader that inherits from a folder that gives the principal one of its keys (Keyed by folder).

For every principal with a row, those are the rows authz.list gives it, without what comes only through a caveat or a link (below).

How readers are worked out ​

From the permission's expression, as the compiler reads it:

  • a relation gives its subjects' keys: a column (owner_id), a link table, shares that have started and not ended;
  • a group named as a subject (team#member) gives its key, not its members: a member joining or leaving changes their keys, not the readers of what the group is given;
  • inheritance (parent.view) gives the readers of each ancestor's own items, read from the closure table;
  • rel.perm to another type's object copies that object's readers in, or gives the group's key where the type is a group's (named as a subject somewhere: org.admin gives org#admin:1);
  • a condition on the row (not {confidential}) is evaluated on it, and or is a union;
  • and, or and not, between parts that depend on who asks (writer and view, a deny) can't be one key: there the readers are the principals that hold a key of every part and of no denied one. Where nothing is denied, the keys stay as they are.

Keyed by folder ​

Copied in, a file's readers are those of each folder above it: a move rewrites the readers of every file below the moved folder, and a share on a folder those of every file below it. With by folder, the readers of what inherits are keys of their own instead:

  • a file's readers are its own keys and its folder's keys (folder#view:7, and folder#edit:7 where it reads edit), and a folder's, its own (folder#view:7);
  • inherits says whose own keys a folder's key takes: the folder itself, and each folder above it that it inherits from, as the policy's inheritance reaches them (where it stops, {inherit}, it stops);
  • given says what each folder gives itself, before what it inherits: its owner, the org's admins, a team or a user it is shared with;
  • each principal holds only its own keys and its groups', as copied in.

So a move rewrites no file's readers, no key and nothing a folder gives: only what the folders below it inherit, the rows their closure changed. A share on a folder is one row of given, whatever is below it. A member joining or leaving a team changes their keys, as copied in.

The engines' filter takes a second way in, two more nested subqueries, which Electric follows as it does the first:

sql
SELECT id FROM app.files
WHERE id IN (SELECT r.id FROM authz_sync.file__readers r
             WHERE r.key IN (SELECT k.key FROM authz_sync.keys k
                             WHERE k.principal_type = 'user' AND k.principal_id = '42')
                OR r.key IN (SELECT i.key FROM authz_sync.inherits i
                             WHERE i.source IN (SELECT g.source FROM authz_sync.given g
                                                WHERE g.key IN (SELECT k.key FROM authz_sync.keys k
                                                                WHERE k.principal_type = 'user' AND k.principal_id = '42'))))

rowstile client electric writes it for a line keyed by folder. What it costs: a principal's filter reaches the key of every folder it may read through these tables, and a key that every folder gives (the org's admins, through each folder's org) once for each folder above each one (Limits).

by names a type with a permission that inherits (parent.view, a recursion through several types too), which the synced permission reaches, through dots or as its own. Who holds it must be a union of what gives it: a recursion that joins parts that depend on who asks with and or not gives no key. Compiling refuses a line keyed by a type it can't be keyed by (AZ810). A permission of that type that doesn't inherit is copied in, as without by, and so is one between parts that depend on who asks, joined with and or not (a deny): those readers are worked out from the keys. A policy keyed by folder before this layout (each principal held the key of every folder it may read) is worked out again when applied: its tables are kept.

Up to date ​

  • In the same transaction as the change: each trigger that may change access notes what it changed, and at commit the readers of those objects, and of what reads them, are worked out again. What is written is the difference: a move rewrites the readers of what is below the moved folder (keyed by folder: what the folders below inherit), a membership change the member's keys.
  • Two writers whose work meets queue, and the others don't wait for each other. At commit, before it reads anything, the work takes lock rows: of the objects it works out (what changed, and what reads it), and, shared, of those it reads (the folders above, what they link to); a lock row stands for every object whose type and id hash to it, out of 4,096. A writer whose work writes what another's writes or reads waits for it to commit, then sees what it committed. Its work then looks again for what reads the change, and takes the lock rows of anything new; one below a lock row it holds is taken only if nobody holds it, else the work lets go of every lock row and takes them again in order, so writers never wait on each other in a circle: in READ COMMITTED, none ends with a deadlock (40P01). The keys are kept under one lock row, taken last, by the work that changes them: a membership change. A policy whose synced permissions join parts that depend on who asks with and or not (a deny) works its readers out from the keys: there every commit's work queues on that one lock row.
  • In REPEATABLE READ or SERIALIZABLE, a writer takes every lock row, and one whose snapshot is older than another's index write gets a serialization error (40001), to retry. What it costs: each such commit that reaches the index writes every lock row again (4,096 row versions, and about a megabyte of WAL), and those writers queue on one another. An app that writes synced tables often does better to write them in READ COMMITTED.
  • Shares that start or end later change who reads their objects with no write. authz.sync_tick() works out again the readers of the shares whose start or end passed since their objects' readers were last worked out, and says how many shares. Schedule it (Operations); until it runs, the index says what it said before. A writer's transaction works shares out as of when it began: one that starts or ends while the transaction is open is worked out again by the first tick after it commits, even when a tick ran meanwhile.
  • Applying keeps the index's tables, so the engines keep their shapes on them, and keeps their rows when what they are worked out by didn't change. Otherwise it works them out again and writes the difference. A table of the index dropped since is made again by the next apply, which doesn't answer unchanged. So is a readers table whose type's key columns changed: Postgres then takes it out of a publication that named it, and applying warns of each such publication, to add it back. A table no longer synced goes; a policy without sync lines drops the index, and so does rowstile remove.
  • What is written in replica mode isn't followed, as for the closure tables (How it works). authz.sync_verify() compares the index with a rebuild, and rowstile reapply --force works it out again.

authz.sync_tick(), authz.sync_verify(), authz.sync_work() and authz.sync_behind() are for administrators: the app role can't call them.

After commit ​

With sync after commit, a writer's transaction notes what its change reached, in authz_sync.pending, which commits with it, and writes nothing of the index; a worker works the notes into the index after, in a transaction of its own. Writers don't wait for the index, nor for each other on it, and a big move costs its writer what it costs without the index. The index is late instead:

  • SELECT authz.sync_work() works in every change committed when it starts, and says how many objects it worked on. Call it in a loop, or schedule it (pg_cron, the app's jobs), as often as the engines' clients should hear of a change: the index is as late as its last run, plus how long the run takes.
  • One works at a time: while another is at work, a call does nothing and says 0.
  • A run that fails, is stopped, or whose server stops, leaves the changes where they were: the next run works them in. Nothing is lost while no worker runs, and applying works in what waits.
  • authz.sync_behind() says how long the oldest change the index doesn't hold yet has waited, and is NULL when it holds every one committed.
  • authz.sync_verify() is false while changes wait, and says how many in a notice.
  • Shares that end are noted by authz.sync_tick(), for the worker.

The engines see the index as the worker leaves it: a revoke reaches them after the next run, and then after their own replication lag. Until then a client keeps getting what it may no longer read. In the same transaction (the default), authz.sync_work() has nothing to do and says 0, and authz.sync_behind() is NULL.

What it leaves out ​

The index fails closed: what it can't follow, compiling refuses, and what it leaves out gives nothing through it.

in what a synced permission readsthe index
a condition on the clock or chance (now(), 'today', random())refused (AZ804)
a condition on who asks or the session (authz.uid(), authz.ctx(), current_user)refused (AZ805)
a condition that reads other rows (a subquery, the app's function), in a permission, a type's or a link table's whererefused (AZ806); one that limits inheritance is followed by its closure table
a group inside itself whose members are worked out with and / notrefused (AZ807)
a share with a caveatgives nothing: a caveat is checked at each request. Applying warns of those there are
a share linkgives nothing: a synced client is a principal, without links. Applying warns of those there are
scopes, view-asnot in the index: the app's proxy refuses a session limited by them (Electric)
a masked columnapplying refuses a sync role that may read one (AZ809); lint reports a grant or a publication since

The sync role and lint ​

With sync lines, authz.lint() reports these as errors:

  • the sync role doesn't exist;
  • it is a superuser (one finding: the others below would be true of it too);
  • it is a member of the app role, or the app role of it: the app would read every row through it;
  • it lacks BYPASSRLS: the rules apply to the app role, so it reads no row of a table with rules, and an engine's first snapshot is empty while the changes it decodes hold every row;
  • it may read a masked column, a publication publishes one, or a mirror holds one;
  • it owns a governed table, or is a member of its owner: an owner may turn row-level security off;
  • a privilege on authz_sync that the policy doesn't give. Applying takes it back.

And as a warning, a PowerSync mirror whose columns are not its table's (The mirror).

The engine is trusted: it holds BYPASSRLS and every row, and rowstile can't check what it serves (the threat model).

Electric ​

Electric serves shapes over HTTP: the rows of one table that match a filter, and their changes as they happen. Clients ask the app's own server, and it asks Electric. The server asks the database who the request is, sets the shape's filter for that principal, and passes Electric's answer on. rowstile writes the filters, and the TypeScript SDK has the route that sets them. This is a preview too: integrations/electric runs it on Electric 1.8.1, in each of the index's modes (copied in, keyed by folder, after commit). The Electric stack page shows the app's side from end to end.

Electric's role and its publication ​

Electric connects as the sync role, in its manual publication mode (ELECTRIC_MANUAL_TABLE_PUBLISHING=true), so it owns no table and can't turn row-level security off. The role is made by whoever runs the database: a role that may make roles can give REPLICATION and BYPASSRLS only when it holds them. The grants and the publication can be the app's migration, after the policy's (the index's tables must be there):

sql
CREATE ROLE conf_electric LOGIN REPLICATION BYPASSRLS PASSWORD '...';   -- as an administrator
GRANT USAGE ON SCHEMA app TO conf_electric;
GRANT SELECT ON app.files, app.folders TO conf_electric;
ALTER TABLE app.files REPLICA IDENTITY FULL;            -- Electric asks for every column of the old row
ALTER TABLE app.folders REPLICA IDENTITY FULL;
CREATE PUBLICATION electric_publication_default FOR TABLE app.files, app.folders,
  authz_sync.keys, authz_sync.file__readers, authz_sync.folder__readers;

The publication holds the synced tables and the index's tables their filters read: keys, and each synced table's readers; keyed by folder, inherits and given too. Electric adds nothing to it in this mode: a table added later is synced from the next shape that asks for it.

A table with a masked column can't be in it whole: Electric asks for REPLICA IDENTITY FULL, and Postgres then refuses an update or a delete of the table when a publication's column list leaves a column out. So a publication of the table carries the masked column to Electric, and authz.lint() reports it. Keep such a column in a table of its own, which isn't synced.

The shapes: rowstile client electric ​

rowstile client electric writes each synced table's shape as TypeScript constants (with electric = "src/shapes.gen.ts" under [clients], rowstile client and rowstile dev write it). Only the policy's names and the filter's text are in it:

ts
export const shapes = {
  "app.files": {
    "table": "app.files",
    "keys": ["id"],                  // the key columns: always synced
    "masked": [],                    // the columns the policy masks: never synced
    "where": {
      "user": "\"id\" IN (SELECT \"id\" FROM authz_sync.\"file__readers\" WHERE \"key\" IN (SELECT \"key\" FROM authz_sync.\"keys\" WHERE \"principal_type\" = 'user' AND \"principal_id\" = $1))"
    }
  }
} as const;

where holds the index's join for each type that signs in, $1 the principal's id (keyed by folder, with its second way in: change a line's by and write the shapes again). Electric follows the nested IN (SELECT ...): a key gained or lost, a reader added or taken away, moves rows in or out of each shape that reads it, with no write to the rows.

The route: electricShape ​

electricShape(shape, options) (@rowstile/client) is a route handler, fetch's Request to its Response, for GET and POST (a subset):

ts
import { actingAs, electricShape, syncPrincipal } from "@rowstile/client";
import { shapes } from "./shapes.gen.js";

export const files = electricShape(shapes["app.files"], {
  url: process.env.ELECTRIC_URL!,          // Electric's /v1/shape: only the server reaches it
  secret: process.env.ELECTRIC_SECRET,
  signedIn: (req) => actingAs(userOf(req), () => sql.transaction((c) => syncPrincipal(c))),
  vary: ["cookie"],                        // the request headers that tell users apart
});

signedIn asks the database who the request is, in the app's own way: syncPrincipal(c) in a transaction signed in as the request's user, or after authz.login_key() or authz.login_jwt(). Every request asks, live polls too. Then the route:

  • answers 401 (AZ714) for nobody, or for a user who isn't there, and asks nothing of Electric;
  • answers 403 (AZ704) for a session limited by scopes (a key's, a token's) or viewing as someone, as authz.session_limited() says (Identity): the index holds what each principal may read, without scopes;
  • sets the shape's table, where, params[1] (the principal's id) and Electric's secret itself, whatever the client sent;
  • refuses a client's own where, params, another table, queryable_columns or secret, 400 (AZ710). Anded in, a filter that reads other tables would tell the client which rows match there, rows it may not read; left out, the client would get more than it asked for. A client narrows with Electric's subsets instead (subset__where, or a POST's body), which Electric adds to the filter with AND, keeps free of subqueries and limits to the synced columns;
  • refuses a POST whose body is more than a subset (where, params, limit, offset, order_by), 400;
  • always syncs the key columns, and never a masked one. A client may narrow columns; one that names a masked column, or a column the route doesn't sync, is refused with a 400. A table the policy masks a column of needs columns in the options, which leave it out, and they are the shape's queryable_columns too, so a subset can't filter on another;
  • answers with Vary on the headers vary names (and Electric's own), so a cache in front of it keeps a copy for each user and never gives one user's shape to another.

Applying the policy, applying it again and a changed policy leave the shapes as they are: the same handles, no 409 and no must-refetch. A user deleted loses their keys, so their shape empties; the route then answers them 401.

Its limits ​

  • Rows already on a device stay there while it is offline, and in any copy the client keeps. A revoke stops delivery: Electric then sends a move-out, an event naming the values that no longer match, and no delete, which the client must apply. Electric's own Shape (@electric-sql/client 1.5.28) doesn't apply them: it keeps the row until the shape is fetched again. Read the stream's move-out events (the conformance suite's tests follow them as Electric's Elixir client does), or use a client that applies them.
  • The window after a change is Electric's replication lag, and for a share that ends, until the next authz.sync_tick() too.
  • What the index leaves out (caveats, links, scopes) gives nothing through Electric either.
  • Electric is trusted: it holds BYPASSRLS and every row of what it publishes, and rowstile can't check what it serves. The conformance suite checks it end to end, for the forms it uses.

Zero ​

Zero syncs queries: a client asks for a named query, and zero-cache asks the app's query endpoint for its ZQL, runs it on its replica of the database, and keeps the client's copy current as rows change. The app's endpoint adds the index's filter to each query, for the principal the request signs in as. rowstile writes the filters and the index's tables as Zero sees them. This is a preview too: integrations/zero runs it on zero-cache 1.11.4, in each of the index's modes (copied in, keyed by folder, after commit). The Zero stack page shows the app's side from end to end.

Zero's role and its publication ​

zero-cache connects as the sync role, and makes its own schemas beside the app's (zero, zero_0, ...): the role may create in the database, and owns no table of the app's. It runs without event triggers, which only a superuser may make: then a schema change of a table it replicates makes it reset, and a plain rowstile apply changes none of them. A sync line added or dropped makes or drops a readers table, which is such a change: call Zero's schema change hook after it, or let it reset.

The publication is the app's migration, after the policy's, and zero-cache reads it as ZERO_APP_PUBLICATIONS:

sql
CREATE ROLE conf_zero LOGIN REPLICATION BYPASSRLS PASSWORD '...';     -- as an administrator
GRANT USAGE ON SCHEMA app TO conf_zero;
GRANT SELECT ON app.files, app.folders TO conf_zero;
GRANT SELECT (id, file_id, author_id) ON app.notes TO conf_zero;      -- every column but the masked body
CREATE PUBLICATION zero_data FOR TABLE app.files, app.folders, app.notes (id, file_id, author_id),
  authz_sync.keys, authz_sync.file__readers, authz_sync.folder__readers, authz_sync.note__readers;
GRANT CREATE ON DATABASE app_db TO conf_zero;                          -- for zero-cache's own schemas

Zero reads each row by its primary key, so a table keeps its default replica identity, and its publication may leave a masked column out. Keyed by folder, the filters read authz_sync.inherits and authz_sync.given too: publish them.

The index as Zero sees it: rowstile client zero ​

rowstile client zero writes a TypeScript module for the app's Zero schema (with zero = "src/zero.gen.ts" under [clients], rowstile client and rowstile dev write it):

  • syncTables and syncRelationships: the index's tables as Zero tables, and how they join, for createSchema;
  • readers: the relationship each synced table needs to its readers, readers: many(readers["app.files"]);
  • masked: the columns the policy masks, which the app's own Zero tables leave out;
  • readable: each table's filter, for the query endpoint, the index's join written with whereExists.
ts
import { readable, type SyncedBy } from "./zero.gen.js";

export const queries = defineQueries({
  files: defineQuery(({ ctx }: { ctx: SyncedBy }) => readable["app.files"](zql.file, ctx).orderBy("id", "asc")),
});

A client syncs the rows its queries read, not only what they return: of the index, its own keys and the readers rows they lead to (keyed by folder, the given rows on its keys and the inherits rows of what they give), never a key of another user's.

The endpoints ​

zero-cache calls the app's query and mutate endpoints with the client's token (Authorization: Bearer). Each asks the database who the request is, as Electric's route does (syncPrincipal(c), from @rowstile/client), then:

  • answers 401 for nobody, or for a user who isn't there, and a key the database refuses; Zero shows the client needs-auth, and it gets no row;
  • answers 403 for a session limited by scopes (a key's, a token's) or viewing as someone, as authz.session_limited() says: the index holds what each principal may read, without scopes;
  • makes each query's context from the request, never the client's: a client that says it is someone else is sent its token's rows. (The client runs its local copy of a query with the context it says, so its views show what of its own rows that other principal would read.)

The mutate endpoint signs each mutation's transaction in as the request's user before the mutator runs (actAs, or authz.login_key()), so the rules decide what it may write. A refused write comes back to its client as an error, in the database's words, and changes nothing. Zero's own update doesn't count the rows it changed, and row-level security makes an update of a row the user may not update change none: write the update as SQL on the server (tx.dbTransaction.query) and pass its result to the SDK's expect, which says whether the row was hidden or refused, and why. Two more needs of Zero's: the mutate endpoint's server schema holds the app's own tables only (Zero checks its tables against what the connection may read, and the app role may read nothing of the index's), and the app role writes Zero's record of each client's mutations (zero_0.clients, zero_0.mutations), which Zero's role grants once zero-cache has made them.

Applying the policy, applying it again and a changed policy leave the clients connected and their queries following changes. A user deleted loses their keys, so their queries empty; the endpoints then answer them 401.

Its limits ​

  • Rows already on a device stay there while it is offline, and in any copy the client keeps. A revoke stops delivery: Zero removes the row from the client's copy when it next syncs.
  • The window after a change is zero-cache's replication lag, and for a share that ends, until the next authz.sync_tick() too.
  • zero-cache asks the endpoints again when a client adds a query or connects again. Until then, a client signed in with a key revoked, or a session ended, since keeps getting its open queries' changes; one that connects with the key now is refused.
  • What the index leaves out (caveats, links, scopes) gives nothing through Zero either.
  • zero-cache is trusted: it holds BYPASSRLS and every row of what it publishes, and rowstile can't check what it serves. The conformance suite checks it end to end, for the forms it uses.

PowerSync ​

PowerSync syncs streams: a stream's query, run on the PowerSync service's replica, puts each row it gives in a bucket keyed by a value on the row, and each client gets the buckets its parameters name. A bucket's key can't come through a join to another table, so the rows carry their readers: mirrored keeps a copy of the table in authz_sync, and a stream puts each of its rows in the bucket of each key that may read it.

The mirror ​

authz
sync app.files : view mirrored

authz_sync.file__mirror holds every row of the table, every column but those the policy masks, and readers: a JSON array of the keys that may read the row, in order. It is kept as the table's rows are written (through a partition, or a table that inherits, too) and as the index writes their readers. A row written more than once before a statement is done (by a foreign key's cascade to the row itself, or by a trigger of the app's, which may also delete it or make it again) is in the mirror as the table has it then. A partition, or a table that inherits, emptied by TRUNCATE takes the rows stored there out of the mirror, and the index works out the readers of the type again. For every principal, the rows of the mirror whose readers hold one of its keys are those the index gives it. Applying keeps the mirror, and so does rowstile reapply --force, which writes its rows again where they differ. When the table's columns change (a column added, dropped, or of another type: the app's migrations), the next rowstile apply changes the mirror to them in place, so the publication that holds it keeps it, and writes its rows again where they differ. Until then authz.sync_verify() is false and authz.lint() warns of it. A column added to a mirror comes after readers: a stream names its columns, so their order doesn't matter. A policy that masks a column of the table since, or no longer masks one, changes it too: a masked column is never in the mirror, whatever was applied before. Until then the table's writes go on: a column added isn't in the mirror yet, and one dropped is NULL there. authz.sync_verify() compares it with the table's rows too. The app role can't read it, as the rest of the index.

A table with a column of its own named readers is refused (AZ811), and so is a mirrored line keyed by a type: its readers would be the type's keys, a bucket for each object a user may read, past PowerSync's limit of buckets.

PowerSync's role and its publication ​

The service connects as the sync role, which reads the mirrors (the policy grants it the index) and nothing of the app's tables. Its bucket storage is a database of its own, which that role owns. Both are made by whoever runs the database: a role that may make roles can give REPLICATION and BYPASSRLS only when it holds them. The publication must be named powersync; it holds the mirrors and the keys:

sql
CREATE ROLE conf_ps LOGIN REPLICATION BYPASSRLS PASSWORD '...';    -- as an administrator
CREATE DATABASE conf_ps_storage OWNER conf_ps;                     -- the service's bucket storage
CREATE PUBLICATION powersync FOR TABLE authz_sync.file__mirror, authz_sync.folder__mirror, authz_sync.note__mirror,
  authz_sync.keys;

The streams: rowstile client powersync ​

rowstile client powersync writes the service's sync config: a stream for each mirrored table, which puts each of its rows in the bucket of each key that may read it, for the principal the token names. Its columns are the mirror's, read from the database (with powersync = "powersync/sync-config.yaml" under [clients], rowstile client reads them in the database rowstile.toml names); without a database it says so, and exits 2.

yaml
config:
  edition: 3
streams:
  files:
    auto_subscribe: true
    query: |
      SELECT "files"."id", "files"."folder_id", "files"."owner_id", "files"."name", "files"."confidential"
      FROM authz_sync."file__mirror" "files", json_each("files".readers) r
      WHERE r.value IN (SELECT key FROM authz_sync.keys
                        WHERE principal_type = ifnull(auth.parameter('principal_type'), 'user')
                          AND principal_id = auth.user_id())

A principal gets a bucket for each key it holds (its own, its type's, anyone, its groups'), never one for each row. A stream never selects readers, which would tell a client who else reads the row, and a masked column isn't in the mirror: a client is synced neither.

The token ​

The service checks each client's token itself (HS256 here, or a JWKS): its sub is the principal's id and principal_type its type (user if it says none). The app gives the token, after asking the database who the request is, as Electric's route does (syncPrincipal(c), from @rowstile/client):

  • nobody, or a user who isn't there, gets no token (401), and so syncs nothing;
  • a session limited by scopes (a key's, a token's) or viewing as someone gets none either (403): the index holds what each principal may read, without scopes;
  • a token another key signed is refused by the service.

The app's token lasts five minutes: PowerSync's client asks for another when it ends, and the app asks the database again. The app doesn't start without the key it signs tokens with, of 32 characters at least.

The clients' writes go back through the app's own routes, signed in as the request's user: a refused one is the app's 403, in the database's words. Applying the policy, applying it again and a changed policy keep the mirrors, and the clients go on syncing.

Its limits ​

  • Rows already on a device stay there while it is offline, and in any copy the client keeps. A revoke stops delivery: the row leaves the client once it is in none of its buckets.
  • The window after a change is the service's replication lag, and for a share that ends, until the next authz.sync_tick() too.
  • The service believes a token until it ends, so a key revoked or a session ended since keeps syncing that long (five minutes, the app's); the app gives it no new one.
  • A mirrored line can't be keyed by a type (AZ811), so a move rewrites the readers of what is below it, in the mirror too.
  • The service is trusted: it holds BYPASSRLS and every row of what it publishes, and rowstile can't check what it serves. The conformance suite checks it end to end, for the forms it uses.