# Supabase tables have explicit per-role GRANTs and no blanket anon re-grant

- **Pattern:** `ab-002653` (`database-design-operations.security-access.supabase-explicit-grants`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-10-30
- **Canonical URL:** https://auditbuffet.com/patterns/ab-002653
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-002653)

## Why it matters

From 2026-10-30 Supabase no longer exposes new `public` tables to the Data API automatically, so a migration that creates a table without an explicit `GRANT` to the role supabase-js uses fails with `42501 permission denied`. The tempting fix is a blanket `GRANT ... ON ALL TABLES IN SCHEMA public TO anon` or `ALTER DEFAULT PRIVILEGES ... GRANT ... TO anon`, which reopens every current and future table to unauthenticated callers and leaves row access resting on RLS alone, so one table with RLS forgotten becomes a public read/write endpoint. Per-table, per-role, least-privilege grants keep the table closed unless a developer opens it on purpose. This is OWASP A01 (Broken Access Control), CWE-732 and CWE-284.

## Severity rationale

High because a blanket anon re-grant silently turns every table with missing or weak RLS into an unauthenticated data endpoint, and the 42501 error pushes developers toward exactly that fix.

## Remediation

Grant each table to the roles that need it, per operation, and let RLS policies decide which rows. Never grant `anon` everything to make a 42501 go away.

```sql
create table public.notes (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users (id)
);
alter table public.notes enable row level security;

-- Least privilege: signed-in users only, no DELETE unless the app needs it.
grant select, insert, update on public.notes to authenticated;
-- service_role is for server-only code; grant only if it is used.
grant all on public.notes to service_role;

create policy "own rows" on public.notes
  for all to authenticated
  using (auth.uid() = user_id) with check (auth.uid() = user_id);
```

Remove any `grant ... on all tables in schema public to anon` and any `alter default privileges ... grant ... to anon`. If a table is meant to be public, grant `select` on that table to `anon` and mark it `-- INTENTIONALLY_PUBLIC: <reason>`.

## Detection

**The detection method for this pattern is part of AuditBuffet Pro.**

The full method (what to look for, pass and fail conditions, and how results get reported) ships inside the [Database Design & Operations Audit](https://auditbuffet.com/audits/database-design-operations). The pattern definition, severity rationale, and remediation remain openly licensed under CC-BY-4.0.

## External references

- cwe CWE-732
- cwe CWE-284
- owasp A01

Taxons: access-control

HTML version: https://auditbuffet.com/patterns/ab-002653