The 'permission denied for table' error (code 42501) in Supabase means the PostgreSQL role making the request lacks GRANT permissions or RLS policies are blocking access. The most common causes are missing GRANT statements for the anon and authenticated roles, tables created by Prisma or raw SQL that skip default permissions, and direct access to restricted schemas like auth or vault. Fix it by granting permissions on your tables and writing proper RLS policies.
Fixing 'Permission Denied' Errors in Supabase
The permission denied error (PostgreSQL error code 42501) is one of the most common issues in Supabase. It occurs when the API request's PostgreSQL role lacks the necessary GRANT permissions on a table. This is different from RLS — GRANT controls whether a role can access a table at all, while RLS controls which specific rows the role can see. This tutorial explains each cause and provides SQL fixes you can run in the Dashboard SQL Editor.
Prerequisites
- A Supabase project where you are experiencing permission errors
- Access to the Supabase Dashboard SQL Editor
- Understanding of the two API roles: anon (unauthenticated) and authenticated (logged-in users)
- Basic SQL knowledge
Step-by-step guide
Identify the exact error and the affected table
Identify the exact error and the affected table
The permission denied error appears in different forms depending on where you encounter it. In the JS client, it comes back as an error object with code 42501. In the REST API, it appears as a JSON response with the error message. Open your browser's developer console or check the Supabase Dashboard logs to find the exact error message, which includes the table name and the operation that failed.
1// Example error from the JS client2// {3// "message": "permission denied for table profiles",4// "code": "42501",5// "hint": null,6// "details": null7// }89// Check in your code10const { data, error } = await supabase.from('profiles').select('*');11if (error) {12 console.error('Error code:', error.code); // "42501"13 console.error('Message:', error.message); // "permission denied for table profiles"14}Expected result: You can identify the specific table, operation (SELECT, INSERT, UPDATE, DELETE), and role (anon or authenticated) causing the error.
Grant table permissions to the API roles
Grant table permissions to the API roles
Supabase uses two PostgreSQL roles for API access: anon for unauthenticated requests and authenticated for logged-in users. Tables created through the Supabase Dashboard automatically get GRANT permissions for these roles, but tables created via Prisma, raw SQL, or migration tools may not. Run the GRANT command to fix this. You can grant specific operations or all operations depending on your needs.
1-- Grant all CRUD permissions to both API roles2GRANT SELECT, INSERT, UPDATE, DELETE3ON TABLE public.profiles4TO anon, authenticated;56-- If you only want authenticated users to have access7GRANT SELECT, INSERT, UPDATE, DELETE8ON TABLE public.profiles9TO authenticated;1011-- Grant read-only access to unauthenticated users12GRANT SELECT13ON TABLE public.profiles14TO anon;1516-- Grant permissions on ALL tables in public schema17GRANT SELECT, INSERT, UPDATE, DELETE18ON ALL TABLES IN SCHEMA public19TO anon, authenticated;2021-- Grant permissions on future tables too22ALTER DEFAULT PRIVILEGES IN SCHEMA public23GRANT SELECT, INSERT, UPDATE, DELETE24ON TABLES TO anon, authenticated;Expected result: The roles can now access the table. The 42501 error no longer appears for the granted operations.
Enable RLS and create policies for row-level access
Enable RLS and create policies for row-level access
After granting table-level permissions, enable Row Level Security to control which specific rows each user can access. Without RLS enabled, any user with GRANT permissions can access all rows — which is a security risk. With RLS enabled but no policies, all access is silently denied. You need both RLS enabled AND at least one policy for data to flow through the API.
1-- Enable RLS on the table2ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;34-- Allow users to read their own profile5CREATE POLICY "Users can read own profile"6ON public.profiles FOR SELECT7TO authenticated8USING ((SELECT auth.uid()) = id);910-- Allow users to update their own profile11CREATE POLICY "Users can update own profile"12ON public.profiles FOR UPDATE13TO authenticated14USING ((SELECT auth.uid()) = id)15WITH CHECK ((SELECT auth.uid()) = id);1617-- Allow users to insert their own profile18CREATE POLICY "Users can insert own profile"19ON public.profiles FOR INSERT20TO authenticated21WITH CHECK ((SELECT auth.uid()) = id);Expected result: Users can access only their own rows. The combination of GRANT permissions and RLS policies provides complete access control.
Fix Prisma-specific permission issues
Fix Prisma-specific permission issues
Prisma modifies the default search path and permissions on the public schema, which can cause 42501 errors even on tables that worked before. When Prisma runs migrations, it may revoke default privileges from the public schema. After running Prisma migrations, re-grant permissions to the Supabase API roles. Also make sure your Prisma schema only manages the public schema to avoid interfering with auth and storage schemas.
1-- Re-grant after Prisma migrations2GRANT USAGE ON SCHEMA public TO anon, authenticated;3GRANT SELECT, INSERT, UPDATE, DELETE4ON ALL TABLES IN SCHEMA public5TO anon, authenticated;6GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public7TO anon, authenticated;89-- Set default privileges for future Prisma-created tables10ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public11GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO anon, authenticated;12ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public13GRANT USAGE, SELECT ON SEQUENCES TO anon, authenticated;Expected result: Tables created by Prisma are accessible through the Supabase API with proper permissions.
Avoid accessing restricted schemas directly
Avoid accessing restricted schemas directly
The auth, vault, and storage schemas are internal to Supabase and not exposed through the auto-generated API. Trying to query auth.users directly from the client will always return a permission denied error. Instead, create a public table (like profiles) that references auth.users via a foreign key, and query that table through the API.
1-- WRONG: Trying to query auth.users directly from the API2// This will ALWAYS fail with permission denied:3// const { data } = await supabase.from('auth.users').select('*');45-- RIGHT: Create a public profiles table that references auth.users6CREATE TABLE public.profiles (7 id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,8 display_name TEXT,9 avatar_url TEXT,10 PRIMARY KEY (id)11);1213ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;1415CREATE POLICY "Public profiles are viewable by everyone"16ON public.profiles FOR SELECT17TO anon, authenticated18USING (true);Expected result: You query the public.profiles table instead of auth.users, and the API returns data without permission errors.
Complete working example
1-- Complete SQL script to fix permission denied errors in Supabase2-- Run this in the Supabase Dashboard SQL Editor34-- Step 1: Grant schema usage to API roles5GRANT USAGE ON SCHEMA public TO anon, authenticated;67-- Step 2: Grant table permissions for existing tables8GRANT SELECT, INSERT, UPDATE, DELETE9ON ALL TABLES IN SCHEMA public10TO anon, authenticated;1112-- Step 3: Grant sequence permissions (needed for auto-increment IDs)13GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public14TO anon, authenticated;1516-- Step 4: Set default privileges for future tables17ALTER DEFAULT PRIVILEGES IN SCHEMA public18GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO anon, authenticated;19ALTER DEFAULT PRIVILEGES IN SCHEMA public20GRANT USAGE, SELECT ON SEQUENCES TO anon, authenticated;2122-- Step 5: Enable RLS on all public tables (run per table)23-- Replace 'your_table' with each table name24ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;2526-- Step 6: Create RLS policies (example for profiles table)27CREATE POLICY "Users can read own profile"28ON public.profiles FOR SELECT29TO authenticated30USING ((SELECT auth.uid()) = id);3132CREATE POLICY "Users can update own profile"33ON public.profiles FOR UPDATE34TO authenticated35USING ((SELECT auth.uid()) = id)36WITH CHECK ((SELECT auth.uid()) = id);3738CREATE POLICY "Users can insert own profile"39ON public.profiles FOR INSERT40TO authenticated41WITH CHECK ((SELECT auth.uid()) = id);4243-- Step 7: Verify permissions are correct44SELECT grantee, table_name, privilege_type45FROM information_schema.role_table_grants46WHERE table_schema = 'public'47ORDER BY table_name, grantee;Common mistakes when fixing the "Permission Denied" Error in Supabase
Why it's a problem: Confusing GRANT permissions with RLS policies and only creating one of them
How to avoid: You need both: GRANT controls table-level access, and RLS controls row-level access. Without GRANT you get 42501 errors. Without RLS policies you get empty results.
Why it's a problem: Trying to query auth.users or other internal schemas directly from the client-side API
How to avoid: Create a public table that references auth.users via foreign key. The auth schema is not exposed through the auto-generated API by design.
Why it's a problem: Running Prisma migrations that revoke default privileges on the public schema without re-granting them
How to avoid: Add GRANT statements to a post-migration script. Configure Prisma to only manage schemas = ["public"] to avoid touching auth and storage schemas.
Best practices
- Always enable RLS on every table in the public schema, even if you think the data is not sensitive
- Use ALTER DEFAULT PRIVILEGES to automatically grant permissions on future tables
- Check the Dashboard Logs → API section to see the exact query and role that triggered permission errors
- Create a post-migration script that re-grants permissions after any schema change tool runs
- Never expose the service_role key to bypass permission issues — fix the underlying GRANT and RLS configuration instead
- Use the information_schema.role_table_grants view to audit current permissions across all tables
- Test API access as both anon and authenticated roles to verify permissions work for each use case
- Keep Prisma configured with schemas = ["public"] to prevent it from interfering with Supabase internal schemas
Still stuck?
Copy one of these prompts to get a personalized, step-by-step explanation.
I am getting a 'permission denied for table profiles' error (code 42501) in Supabase. The table was created by Prisma. Walk me through diagnosing and fixing the GRANT permissions, RLS policies, and any Prisma-specific issues step by step.
Generate the SQL statements I need to run in the Supabase SQL Editor to fix permission denied errors on my profiles table. Include GRANT statements for anon and authenticated roles, RLS policies for owner-only access, and default privileges for future tables.
Frequently asked questions
What is the difference between GRANT permissions and RLS policies?
GRANT controls whether a PostgreSQL role can access a table at all. RLS controls which specific rows the role can see within that table. You need both: GRANT on the table plus RLS policies for row-level filtering.
Why do tables created in the Dashboard work but tables created by Prisma do not?
The Supabase Dashboard automatically grants permissions to the anon and authenticated roles when creating tables. Prisma and other migration tools do not add these grants, so you must add them manually after migrations.
Can I use the service_role key to bypass permission errors?
Yes, the service_role key bypasses all RLS and permission checks. However, this is only safe in server-side code. Never use it in client-side code. Instead, fix the underlying GRANT and RLS configuration.
Why do I get permission denied for schema auth?
The auth schema is internal to Supabase and not exposed through the API. You cannot query auth.users directly. Create a public.profiles table with a foreign key to auth.users and query that instead.
How do I grant permissions on a table in a custom schema?
First expose the schema to the API in Dashboard → Settings → API → Schema. Then run GRANT USAGE ON SCHEMA your_schema TO anon, authenticated and grant table-level permissions as usual.
Will granting permissions make my data public?
No. GRANT only allows the role to attempt operations on the table. RLS policies still control which rows are accessible. With RLS enabled and no policies, all access returns empty results even with GRANT permissions.
Can RapidDev help resolve persistent permission issues in my Supabase project?
Yes. RapidDev can audit your Supabase project's permission configuration, fix GRANT and RLS issues, and set up a sustainable permissions management workflow that works with your migration tools.
Talk to an Expert
Our team has built 600+ apps. Get personalized help with your project.
Book a free consultation