[Security][Critical] No Row Level Security on any application table — one accidental GRANT = total multi-tenant data breach

[bmersereau]

Severity: Critical (conditional)

File: backend/schema.sql:352-368 (and absence of RLS throughout the schema)
CWE: CWE-285 — Improper Authorization (defense-in-depth gap)
OWASP: A01:2021 — Broken Access Control

Description

The schema's entire authorization model for client roles is a single REVOKE ALL ... FROM anon, authenticated block:

revoke all on public.user_profiles from anon, authenticated;
revoke all on public.projects from anon, authenticated;
revoke all on public.documents from anon, authenticated;
revoke all on public.user_api_keys from anon, authenticated;
-- ... 13 more

There is no ALTER TABLE ... ENABLE ROW LEVEL SECURITY and no CREATE POLICY anywhere in schema.sql. Grep confirms: zero RLS in the entire file. The revoke is the only wall.

Impact

REVOKE ALL works as long as no future migration accidentally re-grants any privilege. A single line — GRANT SELECT ON public.documents TO authenticated; — in a migration (human or AI-authored), a hotfix, or a Supabase dashboard click instantly undoes the protection for that table. With RLS disabled (the Postgres default), the grant takes effect immediately with no restriction.

Once triggered:

• The frontend already holds a configured Supabase anon client (frontend/src/lib/supabase.ts) with each user's JWT.
• Any authenticated browser session calls supabase.from('documents').select('*') and receives every user's documents.
• Same for user_api_keys (encrypted under SHA-256-derived key, see [Security][Critical] Weak AES key derivation for user API key encryption (SHA-256, no KDF) #67 — directly brute-forceable offline once attacker has the rows).
• Same for chat_messages (contains permanent download tokens per [Security][High] Non-expiring download tokens allow persistent file access after access revocation #68 — every historical token becomes globally redeemable).

For a legal-document product, this is a complete multi-tenant breach of privileged matter across all users.

In an AI-assisted open-source codebase the trigger probability is materially elevated: AI agents commonly "fix" unexpected empty results by adding permissive GRANTs.

Fix

Append the following block to backend/schema.sql (or ship as a standalone migration). Idempotent — safe to re-run.

-- ---------------------------------------------------------------------------
-- Row Level Security (defense-in-depth second wall)
-- ---------------------------------------------------------------------------
-- Service role bypasses RLS; backend continues to function unchanged.
-- An accidental future GRANT to anon/authenticated is silently blocked by the
-- deny-all policy below.

do \$\$
declare
    tbl text;
    policy_name text;
begin
    for tbl in
        select table_name from information_schema.tables
        where table_schema = 'public' and table_type = 'BASE TABLE'
    loop
        execute format('alter table public.%I enable row level security', tbl);
        policy_name := 'deny_client_access_' || tbl;
        if not exists (
            select 1 from pg_policies
            where schemaname = 'public' and tablename = tbl and policyname = policy_name
        ) then
            execute format(
                'create policy %I on public.%I for all to anon, authenticated using (false) with check (false)',
                policy_name, tbl
            );
        end if;
    end loop;
end\$\$;

Validation

After applying:

-- Should return zero rows: every public base table has RLS enabled.
select table_name from information_schema.tables t
where table_schema = 'public' and table_type = 'BASE TABLE'
  and not exists (
    select 1 from pg_class c
    join pg_namespace n on n.oid = c.relnamespace
    where n.nspname = 'public' and c.relname = t.table_name and c.relrowsecurity
  );

-- Smoke test: a user with the authenticated role gets nothing from any table
-- via PostgREST. Backend (service role) is unaffected.

Residual risk

After fix: Low. Two independent walls (REVOKE + RLS using(false)) both have to fail for client-side data exposure. The only remaining bypass paths are at the database admin level (superuser, SECURITY DEFINER functions) which are outside this codebase's control surface.

Related

• [Security][Critical] SUPABASE_SECRET_KEY triple-reuse — single key leak = total system compromise #66 — Service-role key reuse. If both [Security][Critical] SUPABASE_SECRET_KEY triple-reuse — single key leak = total system compromise #66 and this issue are unmitigated, a single leaked service-role key bypasses both walls anyway. This issue protects against the much more common scenario (accidental grant, no key leak).
• [Security][Critical] Weak AES key derivation for user API key encryption (SHA-256, no KDF) #67 — Weak KDF for stored API keys. Combined with this issue triggering: instant mass-decrypt of all users' LLM credentials by any logged-in user.
• [Security][High] Non-expiring download tokens allow persistent file access after access revocation #68 — Non-expiring download tokens stored in chat_messages.content. Combined with this issue: every historical token becomes globally redeemable.

[chirag-mehta]

Generally - I won't recommend adding Row level security here. It's a very high tax for such an early project.

https://www.postgresql.org/message-id/MEXPR01MB1334F05EE1638E9188A9E69094470%40MEXPR01MB1334.ausprd01.prod.outlook.com

Please instead deal with it like any multi-tenant database would. By ensuring that all DB fetches use some form of tenant_id. It's significantly cleaner and faster. RLS is a huuuge technical tax imo that's worth it in very, very few cases.