What this usually means
Row-Level Security (RLS) is enabled on a table but the policies are either missing, too restrictive, incorrectly written, or conflicting. Supabase enforces RLS by default on all tables; without a proper policy, operations are blocked. Common issues include using `USING` when `WITH CHECK` is needed, incorrect role references, or policies that cause infinite recursion by querying the same table.
The first ten minutes — establish facts before touching code.
- 1Check if RLS is enabled: `SELECT relname, relrowsecurity FROM pg_class WHERE relname = 'your_table';`
- 2List all policies on the table: `SELECT * FROM pg_policies WHERE tablename = 'your_table';`
- 3Test as a specific user: `SET ROLE authenticated; SELECT * FROM your_table;`
- 4Enable RLS bypass for debugging: `ALTER TABLE your_table DISABLE ROW LEVEL SECURITY;` (temporary)
- 5Check Supabase logs in Dashboard > Logs for 'policy' or 'permission denied' messages
- 6Use `EXPLAIN ANALYZE` to see if RLS filters are applied: `EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table;`
The specific files, logs, configs, and dashboards that usually own this bug.
- searchSupabase Dashboard > SQL Editor for `pg_policies`
- searchSupabase Dashboard > Logs > Postgres logs (filter by 'policy')
- searchApplication code for authenticated user's JWT claims (role, user_id)
- searchMigration files for policy definitions (e.g., `supabase/migrations/*.sql`)
- search`auth.users` table to verify user exists and has correct role
- search`pg_policies` system catalog: `SELECT * FROM pg_policies WHERE tablename = 'your_table';`
- searchSupabase Dashboard > Authentication > Settings for JWT expiry and custom claims
Practical causes, not theory. These are the things you will actually find.
- warningMissing policy for a specific operation (e.g., SELECT exists but not INSERT)
- warningPolicy uses `USING` with `WITH CHECK` incorrectly (or vice versa)
- warningPolicy references `auth.uid()` but the user is not authenticated (anon key used)
- warningInfinite recursion: policy queries the same table it protects
- warningRole mismatch: policy applies to `authenticated` but user is `anon`
- warningJWT token missing required claims (e.g., `user_id` is null)
Concrete fix directions. Pick the one that matches your root cause.
- buildAdd missing policy: `CREATE POLICY "select_own" ON your_table FOR SELECT USING (user_id = auth.uid());`
- buildSplit `USING` and `WITH CHECK` for UPDATE/DELETE: `USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid())`
- buildBreak recursion by using `auth.uid()` directly instead of querying the same table
- buildGrant correct role to JWT: ensure Supabase Auth sets `role` claim to `authenticated`
- buildUse `auth.jwt()` to access custom claims: `current_setting('request.jwt.claims', true)::json->>'custom_claim'`
- buildTemporarily disable RLS to confirm the issue is RLS: `ALTER TABLE your_table DISABLE ROW LEVEL SECURITY;`
A fix you cannot prove is a guess. Close the loop.
- verifiedRun query as target user: `SET ROLE authenticated; SELECT * FROM your_table;`
- verifiedRe-enable RLS and test with the application's authenticated session
- verifiedCheck Supabase logs for 'policy returned error' or 'permission denied' after fix
- verifiedUse `EXPLAIN (ANALYZE, BUFFERS)` to confirm no RLS filter is unexpectedly filtering rows
- verifiedInsert a row and verify it appears immediately with a SELECT
Things that make this bug worse or harder to find.
- warningDisabling RLS in production without understanding the security implications
- warningWriting policies that rely on `auth.role()` instead of `auth.uid()` for row-level checks
- warningForgetting to add `WITH CHECK` for INSERT/UPDATE operations
- warningUsing `OR` in policies that might allow unintended access
- warningAssuming policies are applied in order (they are ORed together)
- warningNot testing with the exact JWT claims that the application sends
User Profiles Returning Empty Results After RLS Migration
Timeline
- 09:15Deploy new migration that enables RLS on `profiles` table
- 09:30User reports that their profile page shows empty data
- 09:32I check Supabase Logs: no errors, but queries return 0 rows
- 09:35Run `EXPLAIN ANALYZE SELECT * FROM profiles` as authenticated user: shows 'Filter: (user_id = auth.uid())'
- 09:40Check `auth.uid()` returns a UUID; but the policy uses `user_id = auth.uid()` — the column is named `id` not `user_id`
- 09:45Update policy from `USING (user_id = auth.uid())` to `USING (id = auth.uid())`
- 09:47Re-test: query returns correct rows
- 09:50Deploy fix; confirm with user that profile loads
We had just migrated a legacy Next.js app to Supabase. The old database had no RLS; we added it in a migration. The profiles table had a column `id` that matched the auth users' `id`. I wrote a policy: `CREATE POLICY "select own profile" ON profiles FOR SELECT USING (user_id = auth.uid());`. That's when the trouble started.
Users immediately reported empty profiles. The API returned 200 OK but with 0 rows. I checked the Supabase logs — nothing. I ran `EXPLAIN ANALYZE` and saw the filter `(user_id = auth.uid())`. That's when I realized: the column name was `id`, not `user_id`. The policy was comparing against a non-existent column, so it always evaluated to false.
I fixed the policy to use `id` and re-tested. Everything worked. The lesson: always double-check column names in policies. Also, use `EXPLAIN ANALYZE` early — it shows the exact filter being applied. A simple typo cost us 20 minutes of debugging.
Root cause
Policy referenced a non-existent column `user_id` instead of the actual column `id`, causing the filter to always return false.
The fix
Changed policy to `USING (id = auth.uid())`
The lesson
Always verify column names match the schema exactly. Use `EXPLAIN ANALYZE` to see what filter RLS applies.
RLS policies have two clauses: `USING` and `WITH CHECK`. `USING` controls which existing rows are visible for SELECT, UPDATE, DELETE. `WITH CHECK` controls which new rows can be inserted or which modified rows are allowed for UPDATE/INSERT. A common mistake is omitting `WITH CHECK` on INSERT policies, causing INSERTs to fail silently (or throw errors).
For UPDATE, both clauses are evaluated: `USING` on the old row, `WITH CHECK` on the new row. If you only specify `USING`, the UPDATE will pass the old row check but fail on the new row with a 'new row violates row-level security' error. Always include both for UPDATE policies.
When a policy queries the same table it protects, PostgreSQL can enter infinite recursion. For example: `CREATE POLICY ... USING (user_id IN (SELECT user_id FROM profiles WHERE ...))`. This causes a recursive check because the subquery itself is subject to RLS, triggering the policy again.
To fix, avoid querying the same table in the policy. Use `auth.uid()` or `auth.jwt()` directly. If you must reference the same table, use a security definer function that bypasses RLS, but that's rarely necessary.
The fastest way to test RLS as a specific user is `SET ROLE authenticated;` (or `anon`). Then run `SELECT * FROM your_table;` and see if rows appear. You can also combine with `EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table;` to see the filter applied. Look for 'Filter: (user_id = auth.uid())' in the output.
If you see 'Rows Removed by Filter: X' and the filter matches your policy, the policy is too restrictive. If you see no filter at all, RLS might be disabled or the policy might not exist for that operation.
Supabase Auth sets the JWT `role` claim to either `authenticated` or `anon`. Policies can target specific roles: `FOR SELECT TO authenticated`. If a user's JWT has role `anon` but the policy applies to `authenticated`, the query will be blocked. Check the JWT payload in Supabase Dashboard > Authentication > Users > JWT.
Also, `auth.uid()` returns the user's UUID from the JWT. If the JWT is expired or malformed, `auth.uid()` returns NULL, and any policy using it will fail. Always verify the JWT is fresh.
Frequently asked questions
Why does my SELECT query return 0 rows even though data exists?
RLS is likely blocking the query. Check that a SELECT policy exists for the user's role (authenticated/anon) and that the policy's USING clause evaluates to true. Use `SET ROLE authenticated; SELECT * FROM table;` to test. Also run `EXPLAIN ANALYZE` to see if a filter is being applied.
How do I see all policies on a table?
Run `SELECT * FROM pg_policies WHERE tablename = 'your_table';` in the SQL Editor. This shows policy name, command (SELECT/INSERT/etc.), role, and the USING/WITH CHECK expressions.
What does 'infinite recursion detected in policy' mean?
Your policy queries the same table it protects, causing an infinite loop. For example, a policy on `profiles` that includes a subquery on `profiles`. Use `auth.uid()` instead of querying the table. If you must reference the table, use a security definer function.
Can I temporarily disable RLS to test?
Yes, run `ALTER TABLE your_table DISABLE ROW LEVEL SECURITY;` to bypass RLS. Remember to re-enable with `ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;` after testing. Never leave RLS disabled in production.
Why does my INSERT work but SELECT returns nothing?
INSERT policies use `WITH CHECK` to validate the new row, not `USING`. Your INSERT may succeed, but if there's no SELECT policy, SELECT returns empty. Ensure you have both INSERT and SELECT policies.