Back to Blog

42501 Permission Denied: The Supabase Custom Schema Gotcha

July 2, 2026

42501 Permission Denied: The Supabase Custom Schema Gotcha

A multi-tenant backend I'm building keeps its tables in a testing schema instead of public, selected via one config value:

# app/core/config.py
SUPABASE_SCHEMA: str = "public"  # set to "testing" in this project's .env

# app/core/database.py
supabase.schema(settings.SUPABASE_SCHEMA).table("tenants")...

The tables existed, RLS was off, the client was configured correctly — and every request still failed. Two separate errors, in order, each requiring its own fix.

Error 1: PGRST106 Invalid schema: testing

PostgREST only routes requests to schemas it's been explicitly told about. By default that's just public and graphql_public — anything else gets rejected before it ever touches Postgres.

Fix: Supabase dashboard → Project Settings → Data API → Exposed schemas → add testing to the list, save.

Error 2: 42501 permission denied for schema testing

Exposing the schema gets requests routed there, but Postgres still enforces its own privilege system independently of PostgREST. When a Supabase project is created, only the public schema gets automatic grants for the anon / authenticated / service_role roles. Any schema you create yourself starts with zero grants for those roles — PostgREST can now reach it, but Postgres refuses everyone once it does.

Fix: run this in the SQL Editor:

-- Let the roles use the schema at all
GRANT USAGE ON SCHEMA testing TO anon, authenticated, service_role;

-- Grant access to existing objects
GRANT ALL ON ALL TABLES IN SCHEMA testing TO anon, authenticated, service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA testing TO anon, authenticated, service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA testing TO anon, authenticated, service_role;

-- Make sure tables/sequences/routines created later inherit the same grants
ALTER DEFAULT PRIVILEGES IN SCHEMA testing
  GRANT ALL ON TABLES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA testing
  GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA testing
  GRANT ALL ON ROUTINES TO anon, authenticated, service_role;

Without the ALTER DEFAULT PRIVILEGES lines, this bites you again the next time you add a table — the grant only applies to what already existed at the moment you ran it.

If you're relying on RLS instead of coarse GRANT ALL, tighten these to the specific SELECT/INSERT/UPDATE/DELETE your policies expect. service_role bypasses RLS anyway, so it needs the grant regardless.

Why two separate failures for one setting

It's tempting to treat "expose the schema" and "grant permissions" as one step, but they're enforced by two different systems that don't know about each other:

  • PostgREST decides which schemas it will even route a request to. This is purely a Data API concern — a routing allowlist, checked before any SQL runs.
  • Postgres decides what an authenticated role is allowed to do once a query lands. This is the database's own privilege system, unrelated to how the request got there.

public ships with both already configured, so it's easy to forget either exists until you introduce a second schema and get walked through them one cryptic error at a time.

Wrap-up

PGRST106 means PostgREST doesn't know the schema exists — fix it in Data API → Exposed schemas. 42501 means PostgREST reached the schema fine but Postgres won't let the role touch it — fix it with GRANT and ALTER DEFAULT PRIVILEGES in the SQL Editor. Same feature, two independent gates, two independent fixes.

GitHub
LinkedIn
youtube