Debugging Supabase RLS Issues: A Step-by-Step Guide

Row Level Security (RLS) is powerful but also a common source of frustration. You set up a policy, it works in development, then fails in production. Or you get cryptic "permission denied" errors with no clear cause.

This guide teaches you how to systematically debug RLS issues.

Understanding RLS Errors

Common Error Messages

"permission denied for schema public"

• RLS is enabled but no policy exists for this operation
• Solution: Create a policy for SELECT, INSERT, UPDATE, or DELETE

"new row violates row level security policy"

• INSERT or UPDATE failed because the new data violates the WITH CHECK condition
• Solution: Ensure the data matches the policy condition

"SELECT permission denied for table"

• RLS policy blocks SELECT access
• Solution: Check the USING condition in the SELECT policy

"UPDATE permission denied for table"

• RLS policy blocks UPDATE access
• Solution: Check the USING and WITH CHECK conditions

Step 1: Verify RLS is Enabled

First, check if RLS is actually enabled on the table:

-- Check if RLS is enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE tablename = 'posts';

-- Output:
-- tablename | rowsecurity
-- posts     | t (true = enabled, f = false = disabled)

If RLS is disabled, enable it:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Step 2: List All Policies

See what policies exist on the table:

-- List all policies on a table
SELECT * FROM pg_policies
WHERE tablename = 'posts'
ORDER BY policyname;

-- Output shows:
-- policyname | cmd | qual | with_check
-- "Users can view own posts" | SELECT | (auth.uid() = user_id) | NULL
-- "Users can create posts" | INSERT | NULL | (auth.uid() = user_id)

Key columns:

• cmd: Operation (SELECT, INSERT, UPDATE, DELETE)
• qual: USING condition (for SELECT/DELETE)
• with_check: WITH CHECK condition (for INSERT/UPDATE)

Step 3: Test Policies with SQL

Use the SQL Editor to test policies as different users:

-- Test as a specific user
SET ROLE authenticated;
SET request.jwt.claims = '{"sub":"user-id-123"}';

-- Try to select
SELECT * FROM posts;

-- If you get permission denied, the policy is blocking access
-- If you get results, the policy allows access

Testing Different Operations

Test SELECT:

SET ROLE authenticated;
SET request.jwt.claims = '{"sub":"user-id-123"}';
SELECT * FROM posts;

Test INSERT:

SET ROLE authenticated;
SET request.jwt.claims = '{"sub":"user-id-123"}';
INSERT INTO posts (title, content, user_id)
VALUES ('Test', 'Content', 'user-id-123');

Test UPDATE:

SET ROLE authenticated;
SET request.jwt.claims = '{"sub":"user-id-123"}';
UPDATE posts
SET title = 'Updated'
WHERE id = 'post-id-123';

Test DELETE:

SET ROLE authenticated;
SET request.jwt.claims = '{"sub":"user-id-123"}';
DELETE FROM posts WHERE id = 'post-id-123';

Step 4: Debug Policy Conditions

If a policy is blocking access, debug the condition:

-- Example policy
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

-- Debug: Check what auth.uid() returns
SELECT auth.uid();

-- Debug: Check the user_id in the data
SELECT id, user_id FROM posts LIMIT 5;

-- Debug: Check if they match
SELECT auth.uid() = user_id FROM posts LIMIT 5;

Common Condition Issues

Issue: auth.uid() is NULL

-- auth.uid() returns NULL if not authenticated
SELECT auth.uid(); -- Returns NULL

-- Solution: Make sure you're authenticated
-- In your app, check that the user is logged in

Issue: Column doesn't exist

-- ❌ Bad: Column doesn't exist
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = owner_id); -- Column is user_id, not owner_id

-- ✅ Good: Use correct column name
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

Issue: Type mismatch

-- ❌ Bad: Comparing UUID to TEXT
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid()::text = user_id::text);

-- ✅ Good: Ensure types match
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id); -- Both are UUID

Step 5: Use EXPLAIN to Understand Query Plans

Use EXPLAIN to see how PostgreSQL executes the query with RLS:

-- See the query plan with RLS
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM posts WHERE user_id = auth.uid();

-- Output shows:
-- Seq Scan on posts (cost=0.00..35.00 rows=1 width=100)
--   Filter: (user_id = auth.uid())
--   Rows Removed by Filter: 99

This shows PostgreSQL is filtering rows based on the RLS condition.

Step 6: Test in Your Application

After fixing the policy, test in your application:

// Test in your Next.js app
async function testRLS() {
  const supabase = createClient();

  // Get current user
  const { data: { user } } = await supabase.auth.getUser();
  console.log('Current user:', user?.id);

  // Try to select
  const { data, error } = await supabase
    .from('posts')
    .select('*');

  if (error) {
    console.error('RLS error:', error.message);
  } else {
    console.log('Success! Posts:', data);
  }
}

Common RLS Issues and Fixes

Issue 1: Policy References Wrong Column

-- ❌ Bad: Column name is wrong
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = owner_id); -- Should be user_id

-- ✅ Good: Use correct column
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

Issue 2: Missing Policy for Operation

-- ❌ Bad: Only SELECT policy, no INSERT policy
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

-- User can SELECT but cannot INSERT!

-- ✅ Good: Add INSERT policy
CREATE POLICY "Users can create posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

Issue 3: Complex Condition Fails

-- ❌ Bad: Complex condition with typo
CREATE POLICY "Users can view organization posts"
  ON posts FOR SELECT
  USING (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

-- If organization_members table doesn't exist, this fails!

-- ✅ Good: Verify table and columns exist
CREATE POLICY "Users can view organization posts"
  ON posts FOR SELECT
  USING (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

-- Test the subquery separately
SELECT organization_id FROM organization_members
WHERE user_id = auth.uid();

Issue 4: RLS Blocks Realtime

-- ❌ Bad: RLS policy blocks realtime subscriptions
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

-- Realtime subscriptions fail silently!

-- ✅ Good: Test RLS with SELECT first
SELECT * FROM posts WHERE auth.uid() = user_id;

-- If SELECT works, realtime will work

Issue 5: Multi-Tenant Access Issues

-- ❌ Bad: Doesn't check organization membership
CREATE POLICY "Users can view organization posts"
  ON posts FOR SELECT
  USING (organization_id = current_setting('app.organization_id')::uuid);

-- If organization_id setting is not set, this fails!

-- ✅ Good: Check organization membership
CREATE POLICY "Users can view organization posts"
  ON posts FOR SELECT
  USING (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

RLS Debugging Checklist

• ✅ RLS is enabled on the table
• ✅ Policy exists for the operation (SELECT, INSERT, UPDATE, DELETE)
• ✅ Policy condition references correct columns
• ✅ Column types match (UUID = UUID, not UUID = TEXT)
• ✅ auth.uid() returns the correct user ID
• ✅ Policy condition is logically correct
• ✅ Subqueries in policy return expected results
• ✅ User is authenticated (not NULL)
• ✅ Test with production data and user IDs
• ✅ Realtime subscriptions work (if using realtime)

Debugging Tools

Supabase Dashboard

1. Go to SQL Editor
2. Run test queries with different user IDs
3. Check Database → Logs for permission denied errors
4. View policies in Database → Policies

Application Logging

// Log RLS errors in your app
async function debugRLS() {
  const supabase = createClient();

  const { data, error } = await supabase
    .from('posts')
    .select('*');

  if (error) {
    console.error('RLS Error:', {
      message: error.message,
      code: error.code,
      details: error.details
    });
  }
}

PostgreSQL Logs

Enable query logging to see RLS policy evaluations:

-- Enable query logging
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_min_duration_statement = 0;

-- Reload configuration
SELECT pg_reload_conf();

-- View logs
SELECT * FROM pg_read_file('postgresql.log', 0, 1000000);

Best Practices

• ✅ Test policies thoroughly before production
• ✅ Use simple, clear policy conditions
• ✅ Document complex policies with comments
• ✅ Test with production data and user IDs
• ✅ Monitor logs for permission denied errors
• ✅ Use EXPLAIN to understand query plans
• ✅ Break complex policies into smaller parts
• ✅ Test all operations: SELECT, INSERT, UPDATE, DELETE

Related Articles

• Security Best Practices
• Building SaaS with Next.js and Supabase
• 10 Common Mistakes Building with Next.js and Supabase

Conclusion

RLS debugging requires systematic thinking. Start by verifying RLS is enabled, list all policies, test conditions with SQL, and use EXPLAIN to understand query plans. Most RLS issues stem from simple mistakes: wrong column names, missing policies, or incorrect conditions.

With these debugging techniques, you'll quickly identify and fix RLS issues. Remember: test thoroughly in development before deploying to production.

---

Originally published at https://iloveblogs.blog