Supabase 42501 Permission Denied: Schema public & auth

A Supabase client — usually the JavaScript SDK — runs a plain select * from my_table right after a fresh deployment, or right after you added a new table, and gets this back:

ERROR: permission denied for schema public
SQL state: 42501

The behavior is the same across local dev, Vercel preview, and production environments. The role your client runs under has lost its grants on the public schema, and a handful of GRANT statements restore them. But which statements depends on reading the error precisely — so let's start with who is actually executing your query.

Which role actually runs your query

Under the hood, the Supabase JavaScript SDK is a thin HTTP client for PostgREST. PostgREST opens its database connection as the authenticator role, then runs SET ROLE to switch to the role encoded in your JWT — anon for the public API key, authenticated for a logged‑in user. (service_role is a separate role you only get with the service key, and it bypasses RLS — which is why server‑side code using that key never hits this error.) The query then executes as anon/authenticated, so any privilege those two roles are missing surfaces as the error you see.

By default those built‑in roles have USAGE on the public schema, but if you have altered the default RLS policies, disabled the public schema, or manually revoked privileges, the roles lose the ability to see any object inside public. A common trigger is running a migration that adds REVOKE ALL ON SCHEMA public FROM public; to lock down the schema, then forgetting to re‑grant the built‑in roles.

Reading the 42501 message precisely: schema vs table

When the SDK issues a simple SELECT, the database checks two things in order:

1. Does the role have USAGE on the schema? Without it, the role cannot even resolve a table name, and PostgreSQL raises exactly permission denied for schema public.
2. Does the role have SELECT (or other DML) privileges on the specific table? If the schema USAGE is present but the table grant is missing, you get a different error — permission denied for table <name> — not the schema one.

So the literal for schema public message specifically means the USAGE grant on the schema is what's missing.

A third, often‑confused failure mode is enabling Row‑Level Security (RLS) without a policy — that does not throw permission denied for schema public. With RLS on and no matching policy a SELECT simply returns zero rows (writes raise new row violates row-level security policy). Same felt symptom — "my client can't read the data" — different cause; each of these three cases gets its own section below.

Restoring the grants

Run this once in the Supabase SQL editor, or embed it in a migration script if you prefer automation:

-- Grant USAGE on the public schema to the built‑in roles
GRANT USAGE ON SCHEMA public TO anon;
GRANT USAGE ON SCHEMA public TO authenticated;

-- Grant SELECT/INSERT/UPDATE/DELETE on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO authenticated;

-- Ensure future tables inherit the same privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO anon;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO authenticated;

This restores both the ability to resolve objects inside public (USAGE) and the data‑access rights (SELECT, etc.) for the roles that the Supabase client actually runs under.

To apply it:

1. Open the Supabase dashboard, navigate to SQL editor.
2. Paste the block above. Adjust the list of privileges if you only need read‑only access (e.g., drop INSERT, UPDATE, DELETE).
3. Click Run. You should see a success message for each statement.
4. If you use a migration tool (e.g., supabase db push), add the same statements to a new migration file and deploy.

Reproduce the client's view with SET ROLE anon

The SQL editor runs as a privileged role, so to reproduce exactly what the client sees you have to impersonate the anon role with SET ROLE, run the query, then reset:

-- In the Supabase SQL editor (or psql), become the anon role and re-run:
set role anon;
select * from public.my_table limit 5;
reset role;

Expected output:

 id | name   | created_at
----+--------+----------------------------
  1 | Alice  | 2023-01-01 12:00:00+00
  2 | Bob    | 2023-01-02 13:30:00+00
(2 rows)

If you still see the permission denied for schema public error, double‑check that you ran the grants against the correct project and that you didn't accidentally create a new role in a later migration.

Getting "permission denied for table" instead

Sometimes you grant USAGE on the schema but forget to grant SELECT on a newly created table. The error is slightly different — permission denied for table <name> rather than for schema public — but the cause (a missing grant for the role) is the same. Fix it by adding:

GRANT SELECT ON TABLE public.new_table TO anon;
GRANT SELECT ON TABLE public.new_table TO authenticated;

Grants are fine but rows come back empty: RLS

If you have RLS enabled on a table, the role may have the right privileges but still be blocked by a policy. Enable RLS first, then add a policy that lets the role read the rows it should see — start permissive only to confirm the policy is the cause, then tighten it before production:

ALTER TABLE public.my_table ENABLE ROW LEVEL SECURITY;

-- Diagnostic only: USING (true) lets every row through. Replace it with a real
-- predicate (e.g. USING (auth.uid() = user_id)) before you ship.
CREATE POLICY allow_read ON public.my_table
  FOR SELECT USING (true);

Auditing grants after every migration

Supabase's default security model assumes you either keep the public schema open or explicitly grant the built‑in roles the rights they need. When you start tightening security, the first thing to audit is the USAGE privilege on public and the default privileges for future tables. A quick checklist that saves you from this error:

1. After any migration that touches schemas, run SELECT * FROM information_schema.role_table_grants WHERE grantee IN ('anon','authenticated'); to verify grants.
2. Keep a version‑controlled SQL file (e.g., grants.sql) that you run as part of every deployment pipeline.
3. If you use custom roles, add them to the grant list alongside anon and authenticated.

You can read more about systematic permission audits in our Supabase RLS policy design patterns guide: /guides/supabase-rls-policy-design-patterns. For a deeper dive on why queries become slow when permissions are mis‑configured, see /post/supabase-slow-queries-fix.

Related

• Create an enum column in Supabase – 2026 guide
• Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)
• Insert into multiple tables with one Supabase API call 2026
• Supabase RLS Policy Design Patterns
• PostgreSQL Migration Rollback: Production Fix Playbook
• Fix: Peer Authentication Failed for User "postgres"
• PostgreSQL DESCRIBE TABLE: The psql \d Equivalent

---

Originally published at https://www.iloveblogs.blog