Supabase: permission denied for schema public — Which Gate Failed

permission denied for schema public is Postgres error 42501, and it means the role your request authenticated as lacks USAGE on the schema — not on the table, and not on a row. It fires before table privileges are checked and long before any row-level security policy runs, so if you are reading this while editing RLS policies, you are editing the wrong gate. The usual cause is a migration that revoked schema privileges from anon and authenticated and never restored them.

Supabase grants those roles USAGE on public when a project is created. Something in your project took it away. The job is to find out which gate failed, because Postgres names it precisely and the four errors are routinely confused.

The four gates, and which error names each one

A query from a Supabase client passes through four independent checks in a fixed order. Each one has its own error, and the error tells you exactly how far you got:

Reading that table top to bottom is the whole debugging procedure. An empty result is the only one of the five that means RLS is working; the other four mean a grant is missing somewhere above RLS. This matters because the reflex when a Supabase query fails is to open the policy editor, and two of these five errors cannot be fixed there at all.

The row below public is worth internalising too: schema USAGE grants the right to look inside a schema. It grants nothing about the objects in it. Having USAGE on public and no SELECT on public.orders is a perfectly normal state, and it produces the second error, not the first.

Fixing it

Restore schema access first, then grant only the object privileges each role actually needs:

-- 1. The gate the error names
grant usage on schema public to anon, authenticated;

-- 2. Object privileges — these are NOT implied by step 1
grant select on all tables in schema public to anon, authenticated;
grant usage, select on all sequences in schema public to anon, authenticated;

-- 3. So that tables created LATER are reachable too
alter default privileges in schema public
  grant select on tables to anon, authenticated;

Step 3 is the one that gets skipped and causes the error to come back a month later for one new table. Default privileges apply to objects created after the statement runs, by the role that runs it — they do not retroactively fix anything, and they do not apply to tables created by a different role. If the error reappears for exactly one table, that is the reason.

Grant the narrowest set you can actually live with. grant all on everything to anon is how a read-only anonymous role ends up able to delete rows, with RLS as the only thing standing in the way.

The cause, nearly every time: an ORM migration

Prisma, Drizzle, TypeORM and hand-rolled migration runners all have a notion of "make the schema match my model", and several of them implement part of that by dropping and recreating the public schema or resetting its privileges. A fresh public schema has no grants for Supabase's roles, because those roles are a Supabase convention rather than a Postgres default.

The tell is that the error appeared immediately after a migration or a db push, affects every table at once, and reproduces for both anon and authenticated. A missing RLS policy never looks like that — it is per-table and per-command.

Two related confusions worth separating:

The auth schema is not the same problem. permission denied for schema auth is not fixed by granting usage on auth. The managed schemas are deliberately unreachable through the data API; the supported route to auth.users is a security definer function in a schema you own, which reads what it needs and returns only that.

Postgres 15 changed public itself. Since 15, the public schema no longer grants CREATE to PUBLIC by default. That changes who can create objects, not who can read them, so it causes "permission denied for schema public" on CREATE TABLE rather than on a SELECT. If the error is coming from a migration rather than from your app, this is the likelier cause.

What this storefront's schema shows about the ladder

This site runs Supabase for its commerce half: eight migrations in supabase/migrations/, four tables with RLS, and a deliberate posture at every one of the gates above. It has never hit the schema error — nothing here revokes schema usage — but it causes two of the others on purpose, which makes it a usable map of what each gate does.

Gate 3, RLS: six policies, all select.

$ grep -n "create policy" supabase/migrations/*.sql
0001_commerce_init.sql:65:create policy "own profile"   on public.profiles          for select using ((select auth.uid()) = id);
0001_commerce_init.sql:66:create policy "own orders"    on public.orders            for select using ((select auth.uid()) = user_id);
0001_commerce_init.sql:67:create policy "own slots"     on public.entitlement_slots for select using ((select auth.uid()) = user_id);
0001_commerce_init.sql:68:create policy "own downloads" on public.download_events   for select using ((select auth.uid()) = user_id);
0008_refund_requests.sql:38:create policy "own deliveries"       on public.download_deliveries for select using ((select auth.uid()) = user_id);
0008_refund_requests.sql:39:create policy "own refund requests"  on public.refund_requests    for select using ((select auth.uid()) = user_id);

Six tables, six policies, every one for select. Not a single insert, update or delete policy exists anywhere in this schema, and the migration that creates the first four says why in a comment left for the next reader:

-- RLS: read-own only; NO write policies for anon/authenticated — all writes go
-- through server code using the service-role key (which bypasses RLS). R-2/R-13/R-3.

So any browser-side insert here produces the third error in the table above, every time, by design. That failure has its own post: new row violates row-level security policy for table.

Gate 4, function EXECUTE: revoked from the client roles on all four functions.

revoke execute on function public.create_order_with_slots(...) from anon, authenticated, public;
revoke execute on function public.refund_order(text) from anon, authenticated, public;
revoke execute on function public.downloads_in_last_hour(uuid) from anon, authenticated, public;
revoke execute on function
  public.record_download_within_limit(uuid, text, text, text, inet, text, int)
  from anon, authenticated, public;

All four are security definer with set search_path = public, pg_temp. security definer runs a function with its creator's privileges, so it can write to RLS-protected tables — that is the standard escape hatch, and it is also why EXECUTE has to be taken away from the roles a browser can reach. A signed-in user calling supabase.rpc("create_order_with_slots", …) gets permission denied for function before RLS is ever consulted. The pinned search_path closes the matching injection class: without it, the function resolves unqualified names against whatever path the caller sets.

This is the part of the ladder people forget exists. Three gates can be wide open and the fourth will still stop a request cold, and its error message does not contain the words "schema" or "policy".

And one gate that is a real trap: a second schema. 0001_commerce_init.sql puts extensions where they belong rather than in public:

-- extensions (R-9: dedicated schema, not public)
create extension if not exists citext with schema extensions;

Columns here are then typed email extensions.citext not null. That is correct practice — extensions in public are a known Supabase advisor warning — and it quietly adds a dependency: a role querying those tables needs USAGE on extensions as well as on public. Supabase grants it by default. A migration tool that resets grants takes it too, and then the error names extensions instead of public, for a schema you may not remember creating. The project currently reports 0 advisor lints, which is the check worth running after any grant surgery.

Verify, do not assume

After granting, confirm from the database rather than from the app. has_schema_privilege answers the exact question the error asked:

select
  r.rolname,
  has_schema_privilege(r.rolname, 'public', 'USAGE')      as public_usage,
  has_schema_privilege(r.rolname, 'extensions', 'USAGE')  as ext_usage
from pg_roles r
where r.rolname in ('anon', 'authenticated', 'service_role');

All three should be true on public. If service_role is false, stop and restore it before anything else — that is the role your server code uses, and src/lib/supabase/admin.ts here is built on the assumption that it bypasses RLS rather than that it has no privileges:

import "server-only";

/**
 * Service-role Supabase client — SERVER ONLY. Bypasses RLS. Use only in vetted
 * server code (webhook writes, signed-URL issuance). Never import from a Client
 * Component; the `server-only` guard makes such an import fail the build.
 */
export function createAdminClient() {
  return createSbClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,
    { auth: { persistSession: false, autoRefreshToken: false } },
  );
}

The import "server-only" line is the relevant detail for this topic: the fastest way to "fix" a permission error is to reach for the service-role key, and that key in a client bundle hands every visitor full database access. This guard turns that mistake into a build failure instead of a breach. Whatever grants you restore, restore them for the role that should be doing the work — see the service role key and where it may appear.

Troubleshooting

That last row is the fastest way to lose an afternoon. The service-role key bypasses RLS and holds broad privileges, so a query that works with it proves only that the table exists.

Frequently asked questions

Is this an RLS problem? No. The schema check runs before policies, so your policies never execute. If the error says schema, RLS is not involved. How RLS failures actually present covers the ones that are.

Can I just grant everything to anon to make it go away? It will go away, and you will have given unauthenticated visitors write access to every table with RLS as the only barrier. Grant usage on the schema, then select where it is needed, and keep writes on the server.

Why does it work locally but not in production? Different databases with different grant histories. Local supabase start creates a fresh project with the default grants intact; production has whatever your migrations did to it. Run the has_schema_privilege query against both.

Do I need to re-grant after every migration? No — only after one that touches schema ownership or privileges. If your tool does that routinely, put the grants in a final migration of their own so they are replayed in order; this project keeps each concern in its own numbered file in supabase/migrations/, which is also what makes the migration history auditable.

Templates in this post

ASoc Ignite (an AI-app marketing site), ASoc Iris (a computer-vision studio site) and ASoc Keystone (a mortgage-lender site with an eligibility checker) each ship a Next.js edition, ready for a Supabase backend wired the way this post describes.

Browse the full sets: Next.js landing page templates, Tailwind landing page templates.