Understanding the Permission Issue
When you hit a permission denied error on the userroles table in Supabase, it usually comes down to one of a few common causes. Most of the time it is not a Supabase bug, it is a configuration problem. I have spent far too many hours tracking this down across a dozen different projects, and the pattern is always the same. The first thing you should check is whether Row Level Security is enabled on that table. If RLS is on and you have not configured any policies, every single query to that table will be blocked regardless of what your PostgreSQL user permissions say. This is the number one cause I see. Turn off RLS temporarily to confirm, then add a policy that matches your use case. The second thing is the auth schema. If your userroles table lives in a different schema than auth.users, you need to explicitly grant permissions on both schemas. I learned this the hard way on a project where I migrated user data from a legacy system into a custom schema. The application could read the users table fine, but every request to userroles failed with a permission error. The fix was a simple set of GRANT statements on the custom schema.
Here is what the SQL looks like when it is done correctly: GRANT USAGE ON SCHEMA public TO authenticated; GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.userroles TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.userroles TO service_role; Make sure you apply these through the Supabase dashboard or the SQL editor, not through your application code. Running DDL statements from your app risks race conditions during deployment.
Get the Full Details
A Specific Edge Case
Last year I was debugging a problem on a project where the userroles table was defined inside a postgis extension schema. The application was throwing permission denied errors even though the policies looked correct. What happened is that the postgis extension created its own namespace, and the RLS policies I wrote were attached to the table in the public schema, but the actual table being queried was in the postgis namespace. It took me about forty minutes of comparing the table OIDs to realize the table had been moved or replicated by the extension setup. The workaround was to reattach the policies to the correct table reference and then run ALTER TABLE to move the policies into the right context. One thing most people miss is that RLS policies are evaluated per-query, not per-session. If you have a complex query that joins the userroles table with three other tables, the policy is checked on every row from every table involved. This means a poorly written policy can make queries significantly slower, especially if your userroles table has thousands of rows and the policy does not use indexed columns for filtering. I have seen query times jump from under 50 milliseconds to over 3 seconds just because the RLS policy was using a subquery against a large table instead of a simple equality check on the user_id column. Another thing nobody warns you about is the difference between the service_role and anon roles in Supabase. If you are calling a stored function that inserts into userroles, and that function is marked SECURITY DEFINER, it runs with the privileges of the function owner, which bypasses RLS entirely. This is useful in some cases but dangerous in others because it means your RLS policies are silently ignored for anything going through that function. I once had a security audit flag a stored procedure that was inserting roles without any checks because the developer assumed RLS would protect it. It did not. The function owner had full write access and the policy was never evaluated.
Step by Step Fix
Check your RLS status first. Run SELECT relrowsecurity FROM pg_class WHERE relname = 'userroles'; in the Supabase SQL editor. If the result is false, RLS is off and the issue is something else. If it is true, move on to the next step. List your existing policies with SELECT * FROM pg_policies WHERE tablename = 'userroles'; This will show you what is already configured. If the list is empty, you need to create policies. If the list has policies but they still deny access, compare the USING clause against your actual query to find the mismatch. Test with the service_role key from your project settings. Use the Supabase client library with the service_role key to make a request. If this succeeds where the anon key fails, you know the issue is with the authenticated user policies, not the table permissions themselves.
If you are using PostgREST, check the database role mapping in your supabase.config.js or .env file. The role that PostgREST connects as needs explicit GRANT permissions on the table. A missing grant here causes the same permission denied error that looks identical to an RLS problem.
When This Approach Does Not Work
There are scenarios where fixing the permissions is not the answer. If your userroles table is part of a third-party integration or a managed extension, you may not have the ability to modify the schema or add policies. In those cases you need to work around the limitation by using views or stored procedures that the extension provides. I ran into this with a project that used the supabase-functions extension for audit logging. The underlying tables were not directly accessible, and the only way to interact with the data was through the provided RPC functions. Attempting to grant direct table access was impossible and would have been overwritten on the next deployment anyway. Another case is when you are dealing with a multi-tenant setup and the permission model is intentionally restrictive. Sometimes the permission denied error is doing exactly what it should be doing. I have seen teams spend hours debugging a permission issue only to realize their business logic was correctly blocking access to another tenant's data. Adding a policy that says PERMIT ALL would fix the error but introduce a serious security vulnerability. The Supabase dashboard does not give you a visual policy builder for complex conditions. If your userroles table requires role-based access control with nested conditions, you will eventually need to write raw SQL policies. There is no GUI workaround for this. Plan for that upfront if your data model is complex enough to warrant it.
Verification After Fixing
After applying your changes, test with the exact query that was failing before. Do not test with a simplified query because the permission behavior can differ between SELECT, INSERT, UPDATE, and DELETE. Each operation can have its own policy or inherit from a catch-all policy, and they do not always behave the same way. Check the Supabase logs in the dashboard under Database > Logs. If the policy evaluation is failing, you will see the denial there with the policy name and the reason. This log output is usually enough to identify the exact policy clause that is causing the block. Monitor query performance after enabling RLS on a busy table. A userroles table that handles frequent updates during user onboarding can become a bottleneck if the policies are not indexed properly. Add an index on any columns used in the USING clause of your policies. This typically reduces query latency by an order of magnitude in production environments.
