[SECURITY] Tenant isolation not enforced at DB layer due to missing RLS policies

[ryanmcdonough]

Summary

Tenant isolation isn’t enforced at the database layer.

Most core tables rely on application logic for access control, and Row Level Security (RLS) is either missing or only partially defined.

Backend routes use createServerSupabase() with the service role key, which bypasses RLS. That’s fine for backend work, but it means the DB isn’t acting as a safety net.

If someone hits Supabase directly with a user JWT, there isn’t a consistent DB-level check stopping cross-tenant access.

Impact

Tenant isolation currently depends on application logic rather than being enforced by the database.

• Data confidentiality risk
  Users may be able to read data outside their tenancy via direct table access.

• Data integrity risk
  Users may insert, update, or delete rows they do not own.

• Boundary enforcement risk
  Access control sits in app code, not the DB.

• Regression risk
  Any missed auth check in the backend can turn into a cross-tenant issue.

Severity: High

Technical Details

• Backend uses service role credentials (SUPABASE_SECRET_KEY), which bypass RLS by design.
• RLS is only enabled on public.user_profiles in the baseline schema.
• Tenant-sensitive tables (e.g. projects, documents, chats, workflow/review tables) do not have a full RLS policy set.
• Foreign keys enforce relationships, not authorisation.

Reproduction Steps

1. Authenticate as User A using a normal user JWT (not service role).
2. Query tenant tables directly via Supabase (PostgREST or client SDK), for example:
   • projects
   • documents
   • chats
3. Attempt to access rows owned by User B.
4. Observe that access is not consistently blocked at the DB level (depends on grants).
5. Attempt write operations against rows outside User A’s scope.
6. Observe that these may succeed where no RLS policy blocks them.

Expected
DB rejects unauthorised cross-tenant reads and writes.

Actual
Access control depends on app logic and table grants, not enforced row policies.

Proposed Fix

Add a proper DB-side baseline for tenant isolation. Keep app checks, but don’t rely on them alone.

1. Enable RLS on all tenant-sensitive tables

At minimum:

• projects
• project_subfolders
• documents
• document_versions
• document_edits
• chats
• chat_messages
• workflow tables
• tabular review tables

2. Define consistent policies

• SELECT
  Allow:

  • Owner (user_id = auth.uid())
  • Shared users (via membership/access tables)

• INSERT
  Enforce:

  • user_id = auth.uid()
  • Parent access (e.g. must have access to project before inserting a document)

• UPDATE / DELETE

  • Owner-only by default
  • Or scoped via parent access where needed

3. Add migrations

• Create incremental migrations for existing environments
• Don’t rely on a one-shot schema

4. Add basic tests

Cover:

• Owner access (allowed)
• Shared user access (allowed where intended)
• Unrelated user (blocked)

Include both read and write paths.

Notes

Right now, tenant boundaries hold as long as the app gets auth right every time.
They should hold even if it doesn’t.