Testing RLS Policies with pgsql-test

Test Row-Level Security policies by simulating different users and roles. Verify your security policies work correctly with isolated, transactional tests.

When to Apply

Use this skill when:

• Testing RLS policies for multi-tenant applications

• Verifying user isolation (users only see their own data)

• Testing role-based access (anonymous, authenticated, admin)

• Validating INSERT/UPDATE/DELETE policies

Setup

Install pgsql-test:

pnpm add -D pgsql-test

Configure Jest/Vitest with the test database.

Core Concepts

Two Database Clients

pgsql-test provides two clients:

Client Purpose

pg

Superuser client for setup/teardown (bypasses RLS)

db

User client for testing with RLS enforcement

Test Isolation

Each test runs in a transaction with savepoints:

• beforeEach() starts a savepoint

• afterEach() rolls back to savepoint

• Tests are completely isolated

Basic RLS Test Structure

import { getConnections, PgTestClient } from 'pgsql-test';

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

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

afterAll(async () => { await teardown(); });

beforeEach(async () => { await pg.beforeEach(); await db.beforeEach(); });

afterEach(async () => { await db.afterEach(); await pg.afterEach(); });

Setting User Context

Use setContext() to simulate different users:

// Simulate authenticated user db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': userId });

// Simulate anonymous user db.setContext({ role: 'anonymous' });

// Simulate admin db.setContext({ role: 'administrator', 'request.jwt.claim.sub': adminId });

Testing SELECT Policies

Verify users only see their own data:

it('users only see their own records', async () => { // Setup: Insert data as superuser await pg.query( INSERT INTO app.posts (id, title, owner_id) VALUES ('post-1', 'User 1 Post', $1), ('post-2', 'User 2 Post', $2) , [user1Id, user2Id]);

// Test: User 1 queries db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': user1Id });

const result = await db.query('SELECT * FROM app.posts');

expect(result.rows).toHaveLength(1); expect(result.rows[0].title).toBe('User 1 Post'); });

Testing INSERT Policies

Verify users can only insert their own data:

it('user can insert own record', async () => { db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': userId });

const result = await db.one( INSERT INTO app.posts (title, owner_id) VALUES ('My Post', $1) RETURNING id, title, owner_id , [userId]);

expect(result.title).toBe('My Post'); expect(result.owner_id).toBe(userId); });

it('user cannot insert for another user', async () => { db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': user1Id });

// Use savepoint pattern for expected failures const point = 'insert_other_user'; await db.savepoint(point);

await expect( db.query( INSERT INTO app.posts (title, owner_id) VALUES ('Hacked Post', $1) , [user2Id]) ).rejects.toThrow(/permission denied|violates row-level security/);

await db.rollback(point); });

Testing UPDATE Policies

it('user can update own record', async () => { // Setup await pg.query( INSERT INTO app.posts (id, title, owner_id) VALUES ('post-1', 'Original', $1) , [userId]);

// Test db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': userId });

const result = await db.one( UPDATE app.posts SET title = 'Updated' WHERE id = 'post-1' RETURNING title );

expect(result.title).toBe('Updated'); });

it('user cannot update another user record', async () => { await pg.query( INSERT INTO app.posts (id, title, owner_id) VALUES ('post-1', 'Original', $1) , [user2Id]);

db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': user1Id });

// Update returns no rows (RLS filters it out) const result = await db.query( UPDATE app.posts SET title = 'Hacked' WHERE id = 'post-1' RETURNING id );

expect(result.rows).toHaveLength(0); });

Testing DELETE Policies

it('user can delete own record', async () => { await pg.query( INSERT INTO app.posts (id, title, owner_id) VALUES ('post-1', 'To Delete', $1) , [userId]);

db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': userId });

await db.query(DELETE FROM app.posts WHERE id = 'post-1');

// Verify as superuser const result = await pg.query( SELECT * FROM app.posts WHERE id = 'post-1' ); expect(result.rows).toHaveLength(0); });

Testing Anonymous Access

it('anonymous users have read-only access', async () => { await pg.query( INSERT INTO app.public_posts (id, title) VALUES ('post-1', 'Public Post') );

db.setContext({ role: 'anonymous' });

// Can read public data const result = await db.query('SELECT * FROM app.public_posts'); expect(result.rows).toHaveLength(1);

// Cannot modify const point = 'anon_insert'; await db.savepoint(point); await expect( db.query(INSERT INTO app.public_posts (title) VALUES ('Hacked')) ).rejects.toThrow(/permission denied/); await db.rollback(point); });

Multi-User Scenarios

Test interactions between multiple users:

describe('multi-user isolation', () => { const alice = '550e8400-e29b-41d4-a716-446655440001'; const bob = '550e8400-e29b-41d4-a716-446655440002';

beforeEach(async () => { // Seed data for both users await pg.query( INSERT INTO app.posts (title, owner_id) VALUES ('Alice Post 1', $1), ('Alice Post 2', $1), ('Bob Post 1', $2) , [alice, bob]); });

it('alice sees only her posts', async () => { db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': alice });

const result = await db.query('SELECT title FROM app.posts ORDER BY title');
expect(result.rows).toHaveLength(2);
expect(result.rows.map(r => r.title)).toEqual(['Alice Post 1', 'Alice Post 2']);

});

it('bob sees only his posts', async () => { db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': bob });

const result = await db.query('SELECT title FROM app.posts');
expect(result.rows).toHaveLength(1);
expect(result.rows[0].title).toBe('Bob Post 1');

}); });

Handling Expected Failures

When testing operations that should fail, use the savepoint pattern to avoid "current transaction is aborted" errors:

it('rejects unauthorized access', async () => { db.setContext({ role: 'anonymous' });

const point = 'unauthorized_access'; await db.savepoint(point);

await expect( db.query('INSERT INTO app.private_data (secret) VALUES ($1)', ['hack']) ).rejects.toThrow(/permission denied/);

await db.rollback(point);

// Can continue using db connection const result = await db.query('SELECT 1 as ok'); expect(result.rows[0].ok).toBe(1); });

Watch Mode

Run tests in watch mode for rapid feedback:

pnpm test:watch

References

• Related skill: pgsql-test-exceptions for handling aborted transactions

• Related skill: pgsql-test-seeding for seeding test data

• Related skill: pgpm-testing for general test setup