Authorization and Security in Local-First Apps

Core Concepts

Authorization timing: sync-time vs query-time

In a traditional server-rendered app, access control happens at query time: every API call is authenticated, and the server decides what to return before anything reaches the client. Local-first systems break this assumption. Data arrives on the device before the user acts on it, which means you need authorization decisions made earlier — at the moment data is synced, not the moment it's read.

Both PowerSync and ElectricSQL use sync-time authorization: permissions are computed once when data is replicated to the client, and the engine filters rows accordingly before they ever reach the local SQLite database. An alternative model, used by Zero, evaluates permissions on every query through a server-side transform endpoint. Query-time systems adapt to permission changes immediately but require an online connection or cached authorization policies. Sync-time systems work well offline, but create a time-of-check-to-time-of-use (TOCTOU) gap: if permissions change on the server, a client already offline holds stale data until the next sync.

How ElectricSQL enforces access control

ElectricSQL uses two complementary mechanisms.

Shapes provide row-level filtering through WHERE clauses evaluated server-side before data is synced. A shape defines a declarative subset of data — a table, selected columns, and an optional WHERE clause. When a client subscribes to a shape, only matching rows reach the local replica. Authorization happens inside the shape definition: a client subscribing to "all projects" only receives the projects they're authorized to access.

The authorization boundary sits with the proxy or backend that defines the shape. Clients can only narrow that boundary — filtering by status, date, or other subset parameters — but they cannot remove, broaden, or modify the server-defined WHERE clause. Even compromised client code cannot escape this constraint; it is enforced by the sync protocol itself.

DDLX permission rules are the second layer: a declarative DDL extension that defines roles and cascading permissions as database-side rules. A GRANT or ASSIGN statement sets a role (e.g., project_owner) at the parent resource level, and permission scopes cascade that role down to nested child resources — tasks, comments, attachments — without needing per-resource grants. For sync access to be authorized, the shape's traversal path through joined tables must map to a predefined permission scope. A shape that attempts an unauthorized join combination is rejected.

ElectricSQL also does not require encoding authorization logic into PostgreSQL Row-Level Security policies. It supports two patterns instead: proxy auth (authorizing shape requests through an HTTP proxy) and gatekeeper auth (generating shape-scoped access tokens). Both allow authorization logic to live in external systems — Auth0, a custom backend — without coupling it to the database schema.

Fig 1

How PowerSync enforces access control

PowerSync uses a two-query model in sync rules. The parameter query determines which "buckets" should be synced to a user's device by reading the JWT token via request.jwt(). The data query then defines which database rows belong to which buckets. A row is synced to a device only when the bucket IDs computed for that row intersect with the user's assigned buckets.

A practical example: to sync lists owned by the current user, the parameter query returns SELECT request.user_id() AS user_id, and the data query filters with WHERE owner_id = bucket.user_id. This means a user cannot access a list they don't own even if they know its ID — the bucket constraint is checked per row before syncing.

Crucially, every bucket parameter must appear as a filter condition in every data query. This is not optional. Omitting a bucket parameter from a data query is both a correctness failure (a user could access restricted rows) and a performance failure (it bypasses PowerSync's row-to-bucket optimization). Developers cannot selectively apply bucket filters.

JWT vs client parameters

Both engines need to know who is making the sync request. Two mechanisms exist: JWT-embedded parameters and client parameters.

JWT parameters (request.jwt() in PowerSync) are extracted from cryptographically signed tokens issued by the server. They cannot be forged by the client. Client parameters (request.parameters()) are transmitted directly by client code and can be set to any value by a malicious or buggy client.

The rule is clear: use request.jwt() for all access control decisions. Client parameters are only safe for non-security-critical filtering — for example, letting a user choose which time range to sync, where viewing extra data causes no harm. PowerSync's dashboard actively warns when sync rules use client parameters without a corresponding JWT constraint, catching this mistake at development time.

JWT parameters are cryptographically signed and cannot be forged. Client parameters are transmitted directly by the client and can be set to any value. Never authorize access using client parameters alone.

Offline token revocation

The offline problem cuts deeper than permissions. Once data is on the device, the server cannot revoke access to it while the client is offline. If a user's account is suspended or their project membership is revoked while they're disconnected, they retain access to whatever was already synced until they reconnect.

This is an inherent property of sync-time authorization systems. JWT expiration alone does not provide revocation: APIs accept valid tokens until they expire without checking revocation status with the authorization server. In local-first apps, where clients may be offline for extended periods, this is a material security gap for sensitive use cases.

Capability tokens are a practical mitigation. When a user is granted sync access, they receive a short-lived token (TTL of 60–300 seconds, or scoped to a task). When the token expires, the client must reconnect to obtain a fresh one before syncing more data. This bounds offline access: a revoked user can still read already-synced data, but they cannot extend their reach by pulling new data. For higher-security contexts, a hybrid model validates the token locally offline while optionally querying a control plane to confirm the token has not been explicitly revoked.

GDPR and data residency

Syncing personal data to client devices creates compliance risks that have no clean solution in the GDPR framework. When EU personal data is replicated to personal devices in different geographic locations, the organization loses control over where that data physically resides. GDPR requires appropriate safeguards for data transfers outside the EU, and the Schrems II judgment invalidated the EU-US Privacy Shield, requiring case-by-case assessment for any transfer mechanism. No standard safeguards currently apply to personal device replication.

Right-to-deletion is equally difficult. Purging data from the central database doesn't guarantee deletion from devices that have already synced it, particularly if the app has been uninstalled or the user is permanently offline. Non-compliance can result in fines up to 4% of global annual turnover or €20 million, whichever is higher.

For EU users, teams must evaluate whether the operational benefits of local-first justify these compliance risks, and which data categories are safe to replicate at all.

Key Principles

1. Server-side authorization is not optional. Security-boundary authorization — "user A must never see user B's private data" — must be enforced by the sync engine before data reaches the client. Client-side enforcement is always bypassable. Business-rule authorization — "hide archived projects from the default view" — can be layered on top client-side, but never as the sole control.

2. Centralize authorization in sync rules. The ad-hoc pattern of defining permission checks separately at each data access point is one of the most common and dangerous anti-patterns. In local-first systems, scattered authorization logic leads to forgotten checks and silent data leaks through local replicas. Define a single source of truth: DDLX permission scopes in ElectricSQL, or parameter queries in PowerSync.

3. Never use client parameters for access control. Client parameters are convenient but untrustworthy for security decisions. Use them only for non-security-critical filtering. Any access control decision must flow through a JWT-verified identity.

4. Offline access is a design trade-off, not a bug to fix. The inability to immediately revoke offline access is an architectural property of sync-time systems. Acknowledge it explicitly, bound it using short-lived tokens, and document it as a known trade-off. Attempting to design it away usually defeats the purpose of going local-first.

5. Sync only what GDPR allows you to replicate. Before syncing personal data to client devices, determine whether the data category is safe to replicate given residency constraints. Sensitive personal data of EU citizens that cannot be controlled once on a device may need to remain server-side or be anonymized before sync.

Worked Example

Scenario: A project management app. Users belong to one or more projects. Within each project, they can see tasks. Users should never see tasks from projects they don't belong to.

PowerSync approach

The parameter query identifies which projects the current user is a member of using the JWT user_id:

-- Parameter query: compute project buckets for this user
SELECT project_id AS bucket_id
FROM project_memberships
WHERE user_id = request.user_id()

The data query syncs tasks belonging only to those project buckets:

-- Data query: sync tasks within authorized project buckets
SELECT id, title, status, project_id
FROM tasks
WHERE project_id = bucket.bucket_id

Because every bucket parameter (bucket_id) appears in the data query filter, a user who is not a member of a project never receives its tasks — even if they know a task ID.

ElectricSQL approach

Define a project_member role using DDLX:

-- Assign roles based on project membership
ELECTRIC ASSIGN 'project_member'
  TO project_memberships.user_id
  IF (project_memberships.user_id = AUTH.user_id);

Then define a permission scope that cascades access to tasks:

-- Tasks inherit access from the project_member role
ELECTRIC GRANT READ ON tasks TO 'project_member'
  USING (tasks.project_id = project_memberships.project_id);

A client subscribes to a shape:

const shape = await db.electric.syncShapeToTable({
  shape: {
    url: `${ELECTRIC_URL}/v1/shape`,
    table: 'tasks',
    where: `project_id = '${projectId}'`
  },
  table: 'tasks',
  primaryKey: ['id']
})

The proxy validates that the client's projectId matches their project_member scope. A client cannot modify the shape definition to substitute a different project ID — the shape traversal would not map to an authorized permission scope and would be rejected.

Common Misconceptions

"Client-side validation is sufficient as a second layer of defense." It is not. Client code can be modified, intercepted, or replaced. Any data that reaches the local replica is accessible to a sufficiently motivated attacker on that device. Authorization must prevent unauthorized data from being synced at all.

"Short JWT expiration handles token revocation." Expiration limits the duration of exposure but does not provide revocation. A token that is valid for 24 hours will continue to be accepted for 24 hours even if explicitly revoked at the authorization server. For sync-time systems, this window can coincide with an extended offline period.

"GDPR doesn't apply because we control the backend." GDPR's data residency requirements follow the data, not the system architecture. Once EU personal data is synced to a device that could be in the US or another non-adequate country, the transfer rules apply regardless of where your backend runs.

"I can use client parameters to let users choose their own data subset." This is valid for non-security-critical filtering (e.g., "show me only the last 30 days"). It is not valid for access control. A client that submits user_id = 'someOtherUserId' as a client parameter will receive that user's data if your sync rules rely on client parameters for authorization.

"DDLX handles everything — I don't need shapes." DDLX defines what data a user is permitted to access. Shapes define what data actually gets synced. Both layers serve different purposes. A permission scope without a corresponding shape subscription means authorized data is never sent. A shape without a corresponding permission scope is rejected.

Boundary Conditions

When sync-time authorization is insufficient. If your security model requires immediate revocation (e.g., a user's access must be terminated within seconds of account suspension), sync-time authorization cannot guarantee this for offline clients. Query-time engines like Zero can reject queries server-side and provide tighter revocation semantics, at the cost of requiring online access for every query.

When DDLX hierarchical models don't fit your data. ElectricSQL's cascading permission scopes work well for tree-structured data (project → tasks → comments). They require careful design for matrix-like access models where a user has different roles in different contexts simultaneously, or where permissions are additive rather than inherited.

When personal data cannot be replicated. Some data categories cannot safely be synced to devices: highly sensitive health data, financial records subject to strict residency laws, or data whose right-to-deletion must be enforced immediately. For these, local-first sync is the wrong tool. Keep them server-side and access them through standard API calls.

When offline token TTLs conflict with user experience. Very short TTLs (e.g., 60 seconds) minimize security exposure but require frequent reconnections, which degrades offline experience. For apps targeting truly intermittent connectivity (field work, travel), a longer TTL with explicit documentation of the security trade-off may be the right call. There is no universally correct TTL — it depends on the sensitivity of the data and the expected offline duration.

When bucket rule complexity grows. Every bucket parameter must appear in every PowerSync data query. As the number of parameters grows (e.g., multi-tenant apps with org, team, and user-level filtering), the sync rule YAML becomes complex and brittle. This is a signal to re-evaluate the data model or split sync rules into separate namespaces.