Testing Row-Level Security Policies in Supabase

Dan Lynch

Nov 17, 2025

Previously: In Writing Your First Supabase Test in TypeScript, we created a pets table and wrote basic tests. Now let's add Row-Level Security policies and test them.

Row-Level Security (RLS) is the foundation of Supabase's security model. Testing RLS policies ensures your users can only access their own data. With supabase-test, you can simulate different users and roles to verify your security policies work correctly.

In this lesson, you'll add RLS policies to your pets table and test them by switching between user contexts.

Prerequisites

See Prerequisites. Requires: Complete Writing Your First Supabase Test in TypeScript.

Why Test RLS in Supabase?

Supabase uses RLS to secure multi-tenant applications. Users should only access their own data, and anonymous users should have limited permissions. Without testing, you risk:

Data leaks between users
Overly restrictive policies blocking legitimate access
Security vulnerabilities in production

supabase-test makes RLS testing simple with the setContext() method—switch between users and roles to verify your security policies.

Adding RLS to to your Supabase schema

Add RLS policies to your existing pets table:

pgpm add rls_pets --requires pets_app

Edit deploy/rls_pets.sql:

-- Deploy: rls_pets
-- requires: pets_app

-- Add owner_id column to track pet owners
ALTER TABLE pets.pets
  ADD COLUMN owner_id UUID;

-- Enable RLS on the pets table
ALTER TABLE pets.pets ENABLE ROW LEVEL SECURITY;

-- Grant schema usage to Supabase roles
GRANT USAGE ON SCHEMA pets TO anon;
GRANT USAGE ON SCHEMA pets TO authenticated;

-- Grant table permissions
GRANT SELECT ON TABLE pets.pets TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE pets.pets TO authenticated;

-- Enable RLS on both tables
ALTER TABLE pets.pets ENABLE ROW LEVEL SECURITY;

-- Create RLS policies for users table
-- Users can view their own data
CREATE POLICY "Users can view own data" ON pets.pets
    FOR SELECT USING (auth.uid() = owner_id);

-- Users can update their own data
CREATE POLICY "Users can update own data" ON pets.pets
    FOR UPDATE USING (auth.uid() = owner_id);

-- Users can insert their own data
CREATE POLICY "Users can insert own data" ON pets.pets
    FOR INSERT WITH CHECK (auth.uid() = owner_id);

-- Users can delete their own data
CREATE POLICY "Users can delete own data" ON pets.pets
    FOR DELETE USING (auth.uid() = owner_id);

-- Grant permissions to anon users
GRANT USAGE ON SCHEMA pets TO anon;
GRANT ALL ON pets.pets TO anon;

-- Grant permissions to authenticated users
GRANT USAGE ON SCHEMA pets TO authenticated;
GRANT ALL ON pets.pets TO authenticated;

-- Grant permissions to service role (for admin operations)
GRANT USAGE ON SCHEMA pets TO service_role;
GRANT ALL ON pets.pets TO service_role;

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_owner_id ON pets.pets(owner_id);

Understanding Supabase Test Clients

supabase-test provides two database clients, just like pgsql-test:

pg - Superuser client for setup/teardown operations
db - User client for testing with Supabase roles (anon, authenticated)

Use pg to insert test data as superuser. Use db with setContext() to test RLS policies.

Testing Supabase RLS Policies

Create __tests__/rls.test.ts:

import { 
  getConnections,
  PgTestClient,
  insertUser 
} from 'supabase-test';

let db: PgTestClient;
let pg: PgTestClient;
let teardown: () => Promise<void>;

let user1: any;
let user2: any;
let user3: any;

const users = [
  {
    id: '550e8400-e29b-41d4-a716-446655440001',
    email: 'tutorial1@example.com'
  },
  {
    id: '550e8400-e29b-41d4-a716-446655440002',
    email: 'tutorial2@example.com'
  },
  {
    id: '550e8400-e29b-41d4-a716-446655440003',
    email: 'tutorial3@example.com'
  }
];

beforeAll(async () => {
  ({ pg, db, teardown } = await getConnections());

  // insert users using pg PgTestClient to bypass RLS (required to insert into supabase auth.users)
  user1 = await insertUser(pg, users[0].email, users[0].id);
  user2 = await insertUser(pg, users[1].email, users[1].id);
  user3 = await insertUser(pg, users[2].email, users[2].id);
});

afterAll(async () => { await teardown(); });
beforeEach(async () => { await db.beforeEach(); });
afterEach(async () => { await db.afterEach(); });


it('should allow user to create their own user record', async () => {
  // set context to simulate authenticated user
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  // user can create their own pet
  const pet = await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id, name, breed, owner_id`,
    ['Fido', 'Labrador', user1.id]
  );

  expect(pet.name).toBe('Fido');
  expect(pet.breed).toBe('Labrador');
  expect(pet.owner_id).toBe(user1.id);
});

it('should prevent user1 from updating user2\'s record', async () => {
  // user2 creates a pet
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user2.id
  });

  const pet = await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id, name, breed, owner_id`,
    ['Buddy', 'Golden Retriever', user2.id]
  );

  expect(pet.owner_id).toBe(user2.id);

  // user1 tries to update user2's pet - should throw
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  await expect(
    db.one(
      `UPDATE pets.pets 
         SET name = $1 
         WHERE id = $2 
         RETURNING id, name, breed, owner_id`,
      ['Hacked Name', pet.id]
    )
  ).rejects.toThrow();
});

it('should allow users to see only their own data in list queries', async () => {
  // set context to user1
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  // create multiple users as admin
  await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id`,
    ['Fido', 'Labrador', user1.id]
  );

  // set context to user1
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user2.id
  });

  await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id`,
    ['Buddy', 'Golden Retriever', user2.id]
  );

  // set context to user1
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user3.id
  });

  await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id`,
    ['Rex', 'German Shepherd', user3.id]
  );

  // set context to user1
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  // user1 should only see their own record in a list query
  const allUsers = await db.many(
    `SELECT id, name, breed, owner_id FROM pets.pets ORDER BY name`
  );

  expect(allUsers.length).toBe(1);
  expect(allUsers[0].owner_id).toBe(user1.id);
  expect(allUsers[0].name).toBe('Fido');
  expect(allUsers[0].breed).toBe('Labrador');
});

The setContext() method sets the role and user context for the current transaction. All subsequent queries run with that authentication context.

Run the tests:

pnpm test

Key Takeaways

pg client - Use for superuser operations (setup/teardown)
db client - Use for testing with Supabase roles (anon, authenticated)
setContext() sets authentication context for the current transaction
Use pgpm add to add RLS policies as proper database changes
Test isolation ensures RLS context doesn't bleed between tests

What's Next

You've added RLS policies to your Supabase table and tested basic user access scenarios. Next, you'll learn how to seed Supabase test databases with auth.users and realistic data using efficient loading strategies.

Previous Lesson Next Lesson