Get started with modular databases in minutes. Install prerequisites and deploy your first module.

Quickstart: Getting Up and Running

Set up Node.js, PostgreSQL, and pgpm. Everything you need to get started with modular database development.

Prerequisites

Experience the power of modular databases by installing a pre-built module and deploying it to PostgreSQL—no SQL writing required.

Spinning Up a Modular Database in 2 Minutes

Learn to organize PostgreSQL projects with pgpm workspaces and reusable database modules.

Modular PostgreSQL Development with Database Packages

Understand how pgpm workspaces bring npm-style modularity to PostgreSQL development.

pgpm Workspaces: A New Way to Organize PostgreSQL Projects

Set up your first pgpm workspace with proper configuration and structure.

Initializing a Workspace for Modular PostgreSQL Development

Build your first database module with deploy, revert, and verify scripts.

Creating Your First PostgreSQL Database Module

Write and run tests for your database module with watch mode for rapid feedback.

Testing Your Database Module

Master module dependencies, cross-module references, and automatic dependency resolution.

Managing Database Package Dependencies in a Workspace

Master the workflow for adding, organizing, and managing database changes with pgpm.

Authoring Database Changes

Learn the fundamental workflow for adding database changes using the pgpm add command and the three-file pattern for safe, reversible migrations.

Adding Your First Database Change

Structure migrations using nested paths for maintainable schemas.

Change Naming Conventions

Understand the plan file format and how pgpm generates deployment plans from SQL dependencies.

Plan Files and Deployment Plans

Master end-to-end PostgreSQL testing with ephemeral databases, RLS testing, and CI/CD automation.

End-to-End PostgreSQL Testing with TypeScript

Create ephemeral PostgreSQL databases for testing with pgsql-test. Fast, isolated, and automatically cleaned up.

Spinning Up Temporary Testing Databases for Postgres

Test RLS policies by simulating different users and roles. Verify that your security policies actually work as intended.

How to Test Row-Level Security (RLS) in PostgreSQL

Test complex multi-user scenarios by switching between users within tests. Master the savepoint pattern to handle exceptions and aborted transactions when testing operations that should fail.

Advanced RLS Testing Scenarios - Simulating Many Users and Roles

Learn how to seed test databases with realistic data using loadJson, loadSql, and loadCsv methods for comprehensive testing scenarios.

Seeding PostgreSQL Test Databases

TypeScript-native testing for Supabase with modern workflows.

Supabase Testing

Introducing Supabase Test Suite: TypeScript-native testing for Supabase with isolated databases, RLS testing, and instant feedback loops.

Supabase Testing for the Modern Developer

Get started with supabase-test: install dependencies, configure local Supabase, and write your first isolated database test with automatic rollbacks.

Setting Up Local Supabase Database Testing

Create your first Supabase test with supabase-test's getConnections(), understand test isolation, and verify your setup with simple database queries.

Writing Your First Supabase Test in TypeScript

Test Supabase RLS policies by simulating authenticated users and anonymous access. Verify your security policies work as intended with supabase-test.

Testing Row-Level Security Policies in Supabase

Learn how to seed Supabase test databases with auth.users, realistic data using loadJson, loadSql, and loadCsv methods for comprehensive testing.

Seeding Supabase Test Databases

Set up GitHub Actions CI/CD pipeline to automatically test your Supabase database with every commit. Learn how to configure workflows, start Supabase, and run tests.

Running Supabase Tests in GitHub Actions

Automate Supabase testing in GitHub Actions: provision local Supabase stack, run tests in CI, and optimize for fast feedback loops.

Supabase Troubleshooting

Common issues and solutions for pgpm, PostgreSQL, and testing.

Troubleshooting

Quick fixes for common pgpm, PostgreSQL, and testing issues.

Common Issues and Solutions


> **Previously:** In [How to Test Row-Level Security (RLS) in PostgreSQL](./testing-rls-policies), we tested basic RLS policies with single users. Now let's test more complex scenarios with multiple users and malicious attempts.

Real-world security testing requires simulating multiple users attempting various operations—some legitimate, some malicious. You need to verify that users can't access or modify each other's data, even when actively trying to circumvent policies.

In this lesson, you'll test complex multi-user scenarios by switching between users within the same test. You'll also master an essential advanced technique: using savepoints to handle PostgreSQL's aborted transaction behavior when testing operations that should fail.

## Prerequisites

See [Prerequisites](/learn/quickstart/prerequisites). Requires: Complete [How to Test Row-Level Security (RLS) in PostgreSQL](./testing-rls-policies).

## Table of Contents

- [Testing Multi-User Data Isolation](#testing-multi-user-data-isolation)
- [Testing Cross-User Modification Attempts](#testing-cross-user-modification-attempts)
- [Testing User Switching Within a Test](#testing-user-switching-within-a-test)
- [Testing Batch Operations](#testing-batch-operations)
- [Advanced: Handling Exceptions and Aborted Transactions](#advanced-handling-exceptions-and-aborted-transactions)
  - [Malicious Insert Attempts](#malicious-insert-attempts)
  - [Anonymous vs Authenticated Access](#anonymous-vs-authenticated-access)

## Testing Multi-User Data Isolation

Let's test that multiple users can only see their own data.

Create `__tests__/multi-user.test.ts`:

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

const alice = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
const bob = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb';
const charlie = 'cccccccc-cccc-cccc-cccc-cccccccccccc';

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

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

  // Insert test data for multiple users as superuser
  await pg.query(`
    INSERT INTO pets.pets (name, species, age, owner_id) VALUES
    ('Alice Dog', 'dog', 3, $1),
    ('Alice Cat', 'cat', 2, $1),
    ('Bob Bird', 'bird', 1, $2),
    ('Charlie Fish', 'fish', 1, $3)
  `, [alice, bob, charlie]);
});

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

it('users only see all pets (not just their own)', async () => {
  // Alice can see all pets
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': alice
  });

  const aliceView = await db.query('SELECT name FROM pets.pets ORDER BY name');
  expect(aliceView.rows.length).toBe(4);

  // Bob can also see all pets
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': bob
  });

  const bobView = await db.query('SELECT name FROM pets.pets ORDER BY name');
  expect(bobView.rows.length).toBe(4);
});
```

## Testing Cross-User Modification Attempts

Now test that users cannot modify each other's data:

```typescript
it('users cannot update other users pets', async () => {
  // Bob tries to update Alice's dog
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': bob
  });

  const updateAttempt = await db.query(`
    UPDATE pets.pets
    SET name = 'Stolen Dog'
    WHERE name = 'Alice Dog'
  `);

  // No rows should be updated due to RLS
  expect(updateAttempt.rowCount).toBe(0);

  // Verify Alice's pet is unchanged
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': alice
  });

  const check = await db.query(`
    SELECT name FROM pets.pets WHERE owner_id = $1 AND species = 'dog'
  `, [alice]);

  expect(check.rows[0].name).toBe('Alice Dog');
});

it('users cannot delete other users pets', async () => {
  // Charlie tries to delete Bob's bird
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': charlie
  });

  const deleteAttempt = await db.query(`
    DELETE FROM pets.pets WHERE name = 'Bob Bird'
  `);

  expect(deleteAttempt.rowCount).toBe(0);

  // Verify Bob's pet still exists
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': bob
  });

  const check = await db.query(`
    SELECT name FROM pets.pets WHERE owner_id = $1
  `, [bob]);

  expect(check.rows.length).toBe(1);
  expect(check.rows[0].name).toBe('Bob Bird');
});
```

## Testing User Switching Within a Test

Test complex scenarios where users switch roles mid-test:

```typescript
it('handles rapid user switching correctly', async () => {
  // Alice adds a pet
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': alice
  });

  await db.query(`
    INSERT INTO pets.pets (name, species, owner_id)
    VALUES ('Alice Rabbit', 'rabbit', $1)
  `, [alice]);

  // Switch to Bob - should not see Alice's new pet in his owned pets
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': bob
  });

  const bobPets = await db.query(`
    SELECT name FROM pets.pets WHERE owner_id = $1
  `, [bob]);

  expect(bobPets.rows.length).toBe(1);
  expect(bobPets.rows[0].name).toBe('Bob Bird');

  // Switch to Charlie - should not see Alice's or Bob's pets in owned
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': charlie
  });

  const charliePets = await db.query(`
    SELECT name FROM pets.pets WHERE owner_id = $1
  `, [charlie]);

  expect(charliePets.rows.length).toBe(1);
  expect(charliePets.rows[0].name).toBe('Charlie Fish');

  // Switch back to Alice - should see her new rabbit
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': alice
  });

  const alicePets = await db.query(`
    SELECT name FROM pets.pets WHERE owner_id = $1 ORDER BY name
  `, [alice]);

  expect(alicePets.rows.length).toBe(3);
  expect(alicePets.rows.map(r => r.name)).toEqual(['Alice Cat', 'Alice Dog', 'Alice Rabbit']);
});
```

## Testing Batch Operations

Test that batch operations respect RLS for each row:

```typescript
it('batch updates only affect owned pets', async () => {
  // Bob tries to batch update all pets
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': bob
  });

  const batchUpdate = await db.query(`
    UPDATE pets.pets
    SET age = age + 1
    RETURNING name
  `);

  // Only Bob's pet should be updated
  expect(batchUpdate.rowCount).toBe(1);
  expect(batchUpdate.rows[0].name).toBe('Bob Bird');

  // Verify Alice's pets weren't updated
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': alice
  });

  const aliceDog = await db.query(`
    SELECT age FROM pets.pets WHERE name = 'Alice Dog'
  `);

  expect(aliceDog.rows[0].age).toBe(3); // Still original age
});

it('batch deletes only affect owned pets', async () => {
  // Charlie tries to delete all fish
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': charlie
  });

  const batchDelete = await db.query(`
    DELETE FROM pets.pets WHERE species = 'fish'
  `);

  // Only Charlie's fish should be deleted
  expect(batchDelete.rowCount).toBe(1);

  // Insert another fish owned by Alice
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': alice
  });

  await db.query(`
    INSERT INTO pets.pets (name, species, owner_id)
    VALUES ('Alice Goldfish', 'fish', $1)
  `, [alice]);

  // Verify Alice's fish still exists
  const aliceFish = await db.query(`
    SELECT name FROM pets.pets WHERE species = 'fish' AND owner_id = $1
  `, [alice]);

  expect(aliceFish.rows.length).toBe(1);
  expect(aliceFish.rows[0].name).toBe('Alice Goldfish');
});
```

## Advanced: Handling Exceptions and Aborted Transactions

This is the most important advanced concept when testing PostgreSQL with pgsql-test. Understanding PostgreSQL's transaction abort behavior is critical for writing reliable tests.

**The Problem**: When testing security policies, you often need to verify that certain operations fail—users shouldn't be able to insert data for other users, access restricted tables, or violate constraints. But naively testing exceptions breaks your test flow.

### Malicious Insert Attempts

Let's test that users can't insert data claiming to be owned by other users. Here's how you might initially write this test:

```typescript
it('users cannot insert pets for other users', async () => {
  // Bob tries to insert a pet for Alice
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': bob
  });

  await expect(
    db.query(`
      INSERT INTO pets.pets (name, species, owner_id)
      VALUES ('Fake Pet', 'snake', $1)
    `, [alice])
  ).rejects.toThrow(/new row violates row-level security policy/);

  // Verify Alice's pet count hasn't changed
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': alice
  });

  const count = await db.query(`
    SELECT COUNT(*) as total FROM pets.pets WHERE owner_id = $1
  `, [alice]);

  expect(parseInt(count.rows[0].total)).toBe(2); // Still just Dog and Cat
});
```

**This test will fail!** After the INSERT throws an error, any subsequent query on the same connection fails with:

```text
current transaction is aborted, commands ignored until end of transaction block
```

**Why does this happen?** When PostgreSQL encounters an error inside a transaction, it aborts the entire transaction. The connection remains in this aborted state, rejecting all further commands until you explicitly end the transaction.

This is fundamental PostgreSQL behavior: once a transaction encounters an error, it's poisoned. You can't run more queries—you can only rollback or commit (which becomes a rollback for aborted transactions).

**The Solution: Savepoints**

To test exceptions and continue querying afterward, create a savepoint before the failing operation, then roll back to it:

```typescript
it('users cannot insert pets for other users', async () => {
  // Bob tries to insert a pet for Alice
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': bob
  });

  const failurePoint = 'insert_other_user';
  await db.savepoint(failurePoint);

  await expect(
    db.query(`
      INSERT INTO pets.pets (name, species, owner_id)
      VALUES ('Fake Pet', 'snake', $1)
    `, [alice])
  ).rejects.toThrow(/new row violates row-level security policy/);

  await db.rollback(failurePoint);

  // Verify Alice's pet count hasn't changed
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': alice
  });

  const count = await db.query(`
    SELECT COUNT(*) as total FROM pets.pets WHERE owner_id = $1
  `, [alice]);

  expect(parseInt(count.rows[0].total)).toBe(2); // Still just Dog and Cat
});
```

The savepoint captures a safe restore point. When the INSERT fails, rolling back to the savepoint clears the error state, allowing subsequent queries to succeed.

**This pattern is essential whenever you test operations that should fail.** Without it, you can't verify the database state after a failed operation—and that verification is often the most important part of a security test.

### Anonymous vs Authenticated Access

Now let's apply the savepoint pattern to test anonymous users. Anonymous users should have very limited access compared to authenticated users.

When testing operations that should throw errors, we need to use savepoints:

```typescript
it('anonymous users cannot modify any data', async () => {
  db.setContext({ role: 'anonymous' });

  const insertPoint = 'anonymous_insert';
  await db.savepoint(insertPoint);

  await expect(
    db.query(`
      INSERT INTO pets.pets (name, species, owner_id)
      VALUES ('Anonymous Pet', 'hamster', $1)
    `, [alice])
  ).rejects.toThrow(/permission denied/);

  await db.rollback(insertPoint);

  const updatePoint = 'anonymous_update';
  await db.savepoint(updatePoint);
  await expect(
    db.query(`
      UPDATE pets.pets SET name = 'Hacked'
    `)
  ).rejects.toThrow(/permission denied/);
  await db.rollback(updatePoint);

  const deletePoint = 'anonymous_delete';
  await db.savepoint(deletePoint);
  await expect(
    db.query(`
      DELETE FROM pets.pets
    `)    
  ).rejects.toThrow(/permission denied/);
  await db.rollback(deletePoint);
});
```

## Key Takeaways

- **Savepoint pattern** - Essential for testing operations that should fail; prevents aborted transaction errors
- **PostgreSQL transaction behavior** - Errors abort the entire transaction until explicitly rolled back
- **Exception testing workflow** - Create savepoint, test failure, rollback to savepoint, verify state
- **Switch users within tests** to simulate complex multi-user scenarios
- **Test malicious attempts** - Users trying to modify other users' data
- **Verify batch operations** respect RLS for each individual row
- **Test rapid context switching** to ensure isolation between users
- **Anonymous vs authenticated** - Different permission levels
- **Use `pg` for setup only** - All test assertions use `db` with `setContext()`
- **RLS policies** should silently filter rather than error for queries

## What's Next

You've tested complex multi-user scenarios with RLS policies. In the next lesson, we'll explore seeding test databases with realistic data for more comprehensive testing scenarios.
