Write Secure Supabase RLS Policies with AI in 20 Minutes

Problem: RLS Policies Are Hard to Get Right

You're building a multi-tenant app with Supabase and need to secure user data. Row Level Security policies look simple but break in production when users see each other's data or can't access their own.

You'll learn:

How to use AI to generate secure RLS policies
Testing patterns that catch permission bugs before production
Common policy mistakes and how to avoid them

Time: 20 min | Level: Intermediate

Why RLS Is Critical

Without Row Level Security, your Postgres database returns ALL rows to ANY authenticated user. Your API key in the frontend gives full database access unless RLS policies explicitly restrict it.

Common symptoms:

Users see data from other organizations
Admin dashboards expose all user data
Delete operations fail silently
Policies work in SQL editor but not in app

Solution

Step 1: Set Up Your AI Assistant for RLS

Use Claude, ChatGPT, or your preferred AI with this prompt pattern:

I'm writing Supabase RLS policies for a [type] application.

Table: users
Columns: id (uuid), email (text), org_id (uuid), role (text)

Auth context: Supabase provides auth.uid() and auth.jwt()

Generate policies for:
1. Users can read their own row
2. Users can update their own email
3. Admins (role='admin') can read all users in their org

Include:
- Policy names following convention
- SELECT, INSERT, UPDATE, DELETE as needed
- Comments explaining the logic

Why this works: AI needs your schema, auth context, and specific requirements. Generic prompts produce insecure policies.

Step 2: Generate Your First Policy

Let's secure a projects table where users should only see their own projects:

Schema:

CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  user_id uuid REFERENCES auth.users(id),
  created_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

AI Prompt:

Generate Supabase RLS policy for projects table:
- Columns: id, name, user_id, created_at
- Requirement: Users can only CRUD their own projects
- Auth: auth.uid() returns current user's UUID

AI Output:

-- Policy: Users can select their own projects
CREATE POLICY "users_select_own_projects"
ON projects
FOR SELECT
TO authenticated
USING (user_id = auth.uid());

-- Policy: Users can insert projects for themselves
CREATE POLICY "users_insert_own_projects"
ON projects
FOR INSERT
TO authenticated
WITH CHECK (user_id = auth.uid());

-- Policy: Users can update their own projects
CREATE POLICY "users_update_own_projects"
ON projects
FOR UPDATE
TO authenticated
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());

-- Policy: Users can delete their own projects
CREATE POLICY "users_delete_own_projects"
ON projects
FOR DELETE
TO authenticated
USING (user_id = auth.uid());

Expected: Four policies created, one per operation. Run this in Supabase SQL Editor.

Step 3: Test Your Policies Before Production

Create a test user and verify policies work:

-- Create test function to simulate different users
CREATE OR REPLACE FUNCTION test_as_user(test_user_id uuid)
RETURNS void AS $$
BEGIN
  -- Temporarily set auth context
  PERFORM set_config('request.jwt.claims', 
    json_build_object('sub', test_user_id)::text, 
    true);
END;
$$ LANGUAGE plpgsql;

-- Test SELECT policy
SELECT test_as_user('user-123-uuid');
SELECT * FROM projects; -- Should only return user-123's projects

-- Test INSERT policy
INSERT INTO projects (name, user_id) 
VALUES ('Test Project', 'user-123-uuid'); -- Should succeed

INSERT INTO projects (name, user_id) 
VALUES ('Sneaky Project', 'user-456-uuid'); -- Should FAIL

You should see: Second INSERT fails with permission denied. This confirms users can't create projects for others.

If it fails:

All projects visible: RLS not enabled. Run ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
Can't insert own data: Check WITH CHECK clause matches your insert data
Test function errors: Ensure you have appropriate privileges

Step 4: Handle Multi-Tenant Scenarios with AI

Most apps need organization-level access. Update your prompt:

AI Prompt:

Update policies for multi-tenant:

Table: projects
Columns: id, name, org_id (uuid), created_by (uuid)

New requirements:
- Users can read ALL projects in their organization
- Only project creator can update/delete
- org_id comes from users.org_id via join

Auth: auth.uid() for current user

AI Output:

-- Policy: Users can select projects in their org
CREATE POLICY "users_select_org_projects"
ON projects
FOR SELECT
TO authenticated
USING (
  org_id IN (
    SELECT org_id FROM users WHERE id = auth.uid()
  )
);

-- Policy: Users can insert projects in their org
CREATE POLICY "users_insert_org_projects"
ON projects
FOR INSERT
TO authenticated
WITH CHECK (
  org_id IN (
    SELECT org_id FROM users WHERE id = auth.uid()
  )
);

-- Policy: Only creators can update their projects
CREATE POLICY "creators_update_own_projects"
ON projects
FOR UPDATE
TO authenticated
USING (created_by = auth.uid())
WITH CHECK (
  org_id IN (
    SELECT org_id FROM users WHERE id = auth.uid()
  )
);

-- Policy: Only creators can delete their projects
CREATE POLICY "creators_delete_own_projects"
ON projects
FOR DELETE
TO authenticated
USING (created_by = auth.uid());

Why the subquery: RLS policies can't access other tables directly, so we use a subquery to get the user's org_id.

Step 5: Optimize Performance

Subqueries in RLS policies run on EVERY query. For production:

AI Prompt:

Optimize this policy for performance:

[paste your policy]

Options to consider:
- Security definer functions
- Cached lookups
- JWT claims (custom claims in auth token)

AI Output:

-- Add org_id to JWT claims (Supabase Dashboard → Auth → Settings)
-- Then use direct JWT access:

CREATE POLICY "users_select_org_projects_fast"
ON projects
FOR SELECT
TO authenticated
USING (
  org_id = (auth.jwt() -> 'app_metadata' ->> 'org_id')::uuid
);

Performance gain: Eliminates subquery on every request. Requires setting org_id in user metadata at signup.

Step 6: Debug Policy Conflicts

When multiple policies exist, Postgres uses OR logic (any TRUE policy grants access). Use AI to audit:

AI Prompt:

Review these policies for conflicts:

[paste all policies for the table]

Check for:
- Overlapping conditions
- Missing operations
- Security holes

Common issues AI catches:

Forgot to drop old policies before creating new ones
SELECT policy too permissive, DELETE policy too strict
Missing TO authenticated allows anonymous access

Verification

Test your policies with real app flow:

// In your app
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);

// Sign in as test user
await supabase.auth.signInWithPassword({
  email: 'test@example.com',
  password: 'test123'
});

// Try to read projects - should only see user's org
const { data, error } = await supabase
  .from('projects')
  .select('*');

console.log('Visible projects:', data.length); // Should match org count

// Try to read another org's project by ID
const { data: sneaky, error: sneakyError } = await supabase
  .from('projects')
  .select('*')
  .eq('id', 'other-org-project-uuid');

console.log('Sneaky access:', sneaky); // Should be empty array

You should see: Only authorized data returned. Unauthorized queries return empty arrays, not errors.

What You Learned

RLS policies need explicit rules for each operation (SELECT/INSERT/UPDATE/DELETE)
AI generates policies faster but you must test them thoroughly
Subqueries in policies impact performance - use JWT claims for production
Multiple policies use OR logic - be careful with overlapping rules

Common mistakes:

Forgetting to enable RLS: ALTER TABLE x ENABLE ROW LEVEL SECURITY;
Using USING without WITH CHECK on INSERT/UPDATE
Not testing policies with multiple user contexts
Assuming frontend validation is enough

Quick Reference: RLS Policy Template

-- Template for AI prompts
CREATE POLICY "policy_name"
ON table_name
FOR [SELECT | INSERT | UPDATE | DELETE]
TO [authenticated | anon | service_role]
USING (
  -- Condition for which rows are visible/affected
  -- Use auth.uid() for current user
  -- Use subqueries for joins
)
WITH CHECK (
  -- Condition for new/updated rows (INSERT/UPDATE only)
  -- Often same as USING clause
);

Policy naming convention: <role>_<operation>_<scope>_<table>

Examples:

users_select_own_projects
admins_delete_org_data
public_read_published_posts

AI Prompt Library

Audit Existing Policies

Audit these Supabase RLS policies for security issues:

[paste policies]

Check for:
1. Missing operations (no DELETE policy?)
2. Anonymous access holes
3. Overly permissive conditions
4. Performance concerns

Generate Test Cases

Generate SQL test cases for this RLS policy:

[paste policy]

Include:
- Test for authorized access (should succeed)
- Test for unauthorized access (should fail)
- Edge cases (null values, missing data)

Convert to Security Definer Function

Convert this RLS policy to use a security definer function:

[paste policy with subquery]

Requirements:
- Cache result for performance
- Maintain same security guarantees

Tested on Supabase (Postgres 15), @supabase/supabase-js 2.39+, Node.js 22.x

Security Note: Always test RLS policies with multiple user contexts before deploying. Use Supabase's "View as" feature in the dashboard or create test users with different roles/organizations.