Skip to content

rls.md

Latest commit

 

History

History
198 lines (135 loc) · 7.24 KB

rls.md

File metadata and controls

198 lines (135 loc) · 7.24 KB
Raw

Row Level Security (RLS) with CloudSync

CloudSync is fully compatible with PostgreSQL Row Level Security. Standard RLS policies work out of the box.

How It Works

Column-batch merge

CloudSync resolves CRDT conflicts at the column level — a sync payload may contain individual column changes arriving one at a time. Before writing to the target table, CloudSync buffers all winning column values for the same primary key and flushes them as a single SQL statement. This ensures the database sees a complete row with all columns present.

UPDATE vs INSERT selection

When flushing a batch, CloudSync chooses the statement type based on whether the row already exists locally:

  • New row: INSERT ... ON CONFLICT DO UPDATE — all columns are present (including the ownership column), so the INSERT WITH CHECK policy can evaluate correctly.
  • Existing row: UPDATE ... SET ... WHERE pk = ... — only the changed columns are set. The UPDATE USING policy checks the existing row, which already has the correct ownership column value.

Denied writes

When RLS denies a write, cloudsync_payload_apply stops at that change and raises the policy's error (SQLSTATE 42501). No later change in the payload is applied, and the failed statement is rolled back, so the payload leaves no rows or sync metadata behind and the receive checkpoint does not move.

Once the policy allows the rows (or the rows it depends on have been applied), deliver the same payload again: it applies in full. A policy that depends on rows later in the same payload, such as a membership row that grants access to the rows before it, is not retried within the payload.

Quick Setup

Given a table with an ownership column (user_id):

CREATE TABLE documents (
    id      TEXT PRIMARY KEY,
    user_id UUID,
    title   TEXT,
    content TEXT
);

SELECT cloudsync_init('documents');

Enable RLS and create standard policies:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "select_own" ON documents FOR SELECT
    USING (auth.uid() = user_id);

CREATE POLICY "insert_own" ON documents FOR INSERT
    WITH CHECK (auth.uid() = user_id);

CREATE POLICY "update_own" ON documents FOR UPDATE
    USING (auth.uid() = user_id)
    WITH CHECK (auth.uid() = user_id);

CREATE POLICY "delete_own" ON documents FOR DELETE
    USING (auth.uid() = user_id);

When you authenticate PostgreSQL requests with JWTs, CloudSync also executes SET LOCAL ROLE using the JWT role claim. That means the role named in the token must already exist in PostgreSQL and must have the permissions needed to read and write the synced tables. See PostgreSQL Role Requirement.

Example: Two-User Sync with RLS

This example shows the complete flow of syncing data between two databases where the target enforces RLS.

Setup

-- Source database (DB A) — no RLS, represents the sync server
CREATE TABLE documents (
    id TEXT PRIMARY KEY, user_id UUID, title TEXT, content TEXT
);
SELECT cloudsync_init('documents');

-- Target database (DB B) — RLS enforced
CREATE TABLE documents (
    id TEXT PRIMARY KEY, user_id UUID, title TEXT, content TEXT
);
SELECT cloudsync_init('documents');
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- (policies as above)

Insert sync

User 1 creates a document on DB A:

-- On DB A
INSERT INTO documents VALUES ('doc1', 'user1-uuid', 'Hello', 'World');

Apply the payload on DB B as the authenticated user:

-- On DB B (running as user1)
SET app.current_user_id = 'user1-uuid';
SET ROLE authenticated;
SELECT cloudsync_payload_apply(decode(:payload_hex, 'hex'));

The insert succeeds because user_id matches auth.uid().

Insert denial

User 1 tries to sync a document owned by user 2:

-- On DB A
INSERT INTO documents VALUES ('doc2', 'user2-uuid', 'Secret', 'Data');
-- On DB B (running as user1)
SET app.current_user_id = 'user1-uuid';
SET ROLE authenticated;
SELECT cloudsync_payload_apply(decode(:payload_hex, 'hex'));

The insert is denied by RLS: cloudsync_payload_apply raises 42501 and the row does not appear in DB B.

Partial update sync

User 1 updates only the title of their own document:

-- On DB A
UPDATE documents SET title = 'Hello Updated' WHERE id = 'doc1';

The sync payload contains only the changed column (title). CloudSync detects that the row already exists on DB B and uses a plain UPDATE statement:

UPDATE documents SET title = $2 WHERE id = $1;

The UPDATE policy checks the existing row (which has the correct user_id), so it succeeds.

Mixed payload

When a single payload contains rows for multiple users, the first denied row fails the whole apply:

-- On DB A
INSERT INTO documents VALUES ('doc3', 'user1-uuid', 'Mine', '...');
INSERT INTO documents VALUES ('doc4', 'user2-uuid', 'Theirs', '...');
-- On DB B (running as user1)
SELECT cloudsync_payload_apply(decode(:payload_hex, 'hex'));
-- ERROR: doc4 is denied (42501); doc3 is rolled back with the statement

Supabase Notes

When using Supabase:

  1. auth.uid(): Returns the authenticated user's UUID from the JWT claims.
  2. JWT propagation: Ensure the JWT token is set before sync operations: 
    SELECT set_config('request.jwt.claims', '{"sub": "user-uuid", ...}', true);
  3. Service role bypass: The Supabase service role bypasses RLS entirely. Use the authenticated role for user-context operations where RLS enforcement is desired.

Troubleshooting

"new row violates row-level security policy"

Symptom: Insert operations fail during sync.

Cause: The ownership column value doesn't match the authenticated user.

Solution: Verify that:

  • The JWT / session variable is set correctly before calling cloudsync_payload_apply
  • The user_id column in the synced data matches auth.uid()
  • RLS policies reference the correct ownership column

Apply reports a count, but rows are missing

Symptom: cloudsync_payload_apply returns a non-zero column-change count, but SELECT on the target table shows no new rows. No error is raised to the caller.

Cause: The calling role is missing a grant on one of CloudSync's internal objects — the per-table shadow (<table>_cloudsync), a metadata table (cloudsync_settings, cloudsync_site_id, cloudsync_table_settings, cloudsync_schema_versions, cloudsync_payload_fragments, app_schema_version), the cloudsync_changes view, or the cloudsync_site_id_id_seq sequence. The per-PK savepoint rolls the write back, but cloudsync_payload_apply still returns the number of column changes it processed.

Solution: Apply the full grant set from JWT Claims → Required Grants. To pinpoint which object is missing, re-run the apply as a superuser or raise log verbosity and inspect the server log for permission denied entries preceded by the cloudsync_payload_apply call.

Debugging

-- Check current auth context
SELECT auth.uid();

-- Inspect a specific row's ownership
SELECT id, user_id FROM documents WHERE id = 'problematic-pk';

-- Temporarily disable RLS to inspect all data
ALTER TABLE documents DISABLE ROW LEVEL SECURITY;
-- ... inspect ...
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;