Supabase Row-Level Security

RLS policies for user-scoped data, admin bypass, and testing

What You’ll Build

After following this guide, you will have a working implementation of supabase row-level security in your project. Secure your Supabase database with Row-Level Security (RLS) so users can only access their own data. RLS policies run at the database level — even if your API has bugs, unauthorized data access is blocked. Essential for any Supabase app.

Use Cases & Problems Solved

• Set up a production-ready database layer with type-safe queries
• Manage schema changes through migrations without data loss
• Avoid raw SQL injection risks and inconsistent data access patterns

Prerequisites

• Supabase project with Authentication enabled
• Tables created in Supabase

Step-by-Step Implementation

Enable RLS and create policies

The following snippet shows how to enable rls and create policies. Copy this into your project and adjust the values for your environment.

-- Enable RLS on your table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Users can only read their own posts
CREATE POLICY "Users read own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

-- Users can only insert posts as themselves
CREATE POLICY "Users insert own posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Users can update only their own posts
CREATE POLICY "Users update own posts"
  ON posts FOR UPDATE
  USING (auth.uid() = user_id);

-- Admin bypass — admins can do anything
CREATE POLICY "Admins full access"
  ON posts FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
    )
  );

Query from your app (RLS is automatic)

The following snippet shows how to query from your app (rls is automatic). Copy this into your project and adjust the values for your environment.

// This automatically filters to only the logged-in user's posts!
const { data: myPosts } = await supabase
  .from('posts')
  .select('*');

// Insert — RLS ensures user_id matches auth.uid()
const { data, error } = await supabase
  .from('posts')
  .insert({ title: 'My Post', content: 'Hello', user_id: user.id });

⚠️ Don’t Do This

-- Table without RLS: ANYONE with the anon key can read ALL rows!
CREATE TABLE secrets (
  id UUID DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id),
  secret_data TEXT
);
-- No ALTER TABLE ... ENABLE ROW LEVEL SECURITY!

CREATE TABLE secrets ( ... );
ALTER TABLE secrets ENABLE ROW LEVEL SECURITY;
-- Now NO rows are accessible until you create explicit policies

Testing

Verify your implementation with these tests:

// __tests__/supabase-row-level-security.test.ts
import { describe, it, expect } from 'vitest';

describe('Supabase Row-Level Security', () => {
  it('should initialize without errors', () => {
    // Test that the setup completes successfully
    expect(() => setup()).not.toThrow();
  });

  it('should handle the primary use case', async () => {
    const result = await execute();
    expect(result).toBeDefined();
    expect(result.success).toBe(true);
  });

  it('should handle edge cases', async () => {
    // Test with empty/null input
    const result = await execute(null);
    expect(result.error).toBeDefined();
  });
});

Verification

# In Supabase SQL Editor:
SELECT * FROM posts; -- as service_role: sees all
# In your app (as logged-in user): should only see own posts
# Try inserting with wrong user_id: should get policy violation error