Testing Row-Level Security and Extensions with PGlite

Previously: In How to Set Up PGlite for End-to-End Testing, we bootstrapped a PGlite workspace, deployed a module, and wrote basic tests. Now let's add RLS policies and test them by switching user contexts—then wire up a WASM extension and run everything in CI.

Row-Level Security (RLS) is central to multi-tenant Postgres, and it behaves identically in PGlite and on a server. pglite-test creates the standard app roles for you, so you can switch into authenticated and verify policies without any manual setup.

In this lesson, you'll add RLS policies to your pets schema, test them across users, and add pgvector similarity search—all in-process.

Why Test RLS in PGlite?

Because it's the fastest way to prove your policies are correct. No server means no setup latency—each test spins up a fresh database in milliseconds, seeds data as the superuser, and switches into an app role to verify access. You write normal SQL; setContext() handles the role and JWT claims.

Adding RLS Policies

Add a change that introduces a user_id column and RLS policies, depending on the pets_table from the previous lesson:

pgpm add pets_rls --requires pets_table

Edit deploy/pets_rls.sql:

-- Deploy pets_rls
-- requires: pets_table

-- Add owner column
ALTER TABLE app.pets
  ADD COLUMN user_id TEXT NOT NULL;

-- Enable RLS
ALTER TABLE app.pets ENABLE ROW LEVEL SECURITY;

-- Grant permissions to the app role
GRANT USAGE ON SCHEMA app TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.pets TO authenticated;

-- A row is visible/mutable only by the authenticated user that owns it
CREATE POLICY pets_select ON app.pets
  FOR SELECT USING (user_id = current_setting('jwt.claims.user_id', true));

CREATE POLICY pets_insert ON app.pets
  FOR INSERT WITH CHECK (user_id = current_setting('jwt.claims.user_id', true));

CREATE POLICY pets_update ON app.pets
  FOR UPDATE USING (user_id = current_setting('jwt.claims.user_id', true));

CREATE POLICY pets_delete ON app.pets
  FOR DELETE USING (user_id = current_setting('jwt.claims.user_id', true));

App Roles Are Ready

On a real server, roles like authenticated are bootstrapped when you provision the database. PGlite has no such step—it boots as a lone superuser—so pglite-test creates the same standard roles (anonymous, authenticated, administrator) for you before seeding. That means db.setContext({ role: 'authenticated' }) just works, with no manual CREATE ROLE.

Bringing your own roles? Pass pglite: { roles: false } to getConnections() to boot a clean superuser-only instance and define your own roles/users in extensionSql. See the pglite-test docs for the pattern.

Testing RLS

Create __tests__/pets-rls.test.ts:

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

const user1 = '11111111-1111-1111-1111-111111111111';
const user2 = '22222222-2222-2222-2222-222222222222';

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

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

  // Seed as superuser (bypasses RLS by not switching role)
  await pg.query(
    `INSERT INTO app.pets (name, species, user_id)
     VALUES ('User 1 Pet', 'dog', $1), ('User 2 Pet', 'cat', $2)`,
    [user1, user2]
  );
});

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

it('an authenticated user sees only their own pets', async () => {
  db.setContext({ role: 'authenticated', 'jwt.claims.user_id': user1 });

  const mine = await db.many<{ name: string }>('SELECT name FROM app.pets');
  expect(mine).toHaveLength(1);
  expect(mine[0].name).toBe('User 1 Pet');
});

it('a user cannot insert a row they do not own (WITH CHECK)', async () => {
  db.setContext({ role: 'authenticated', 'jwt.claims.user_id': user1 });

  await expect(
    db.one(
      `INSERT INTO app.pets (name, species, user_id) VALUES ($1, $2, $3) RETURNING id`,
      ['Not Mine', 'fish', user2]
    )
  ).rejects.toThrow();
});

Key pattern: seed with pg (superuser, bypasses RLS), isolate with pg.beforeEach()/afterEach(), and switch users with db.setContext(). In PGlite the pg and db clients share one in-process session, so hooks work exactly as they do with pgsql-test.

Context Switching Between Users

The power of the model is seamless context switching—each block authenticates as a different user:

it('users cannot update each other\u2019s pets', async () => {
  // user1 tries to adopt user2's pet
  db.setContext({ role: 'authenticated', 'jwt.claims.user_id': user1 });
  await db.query(`UPDATE app.pets SET species = 'hacked' WHERE user_id = $1`, [user2]);

  // verify user2's row is untouched
  db.setContext({ role: 'authenticated', 'jwt.claims.user_id': user2 });
  const theirs = await db.one<{ species: string }>('SELECT species FROM app.pets');
  expect(theirs.species).not.toBe('hacked');
});

Run the tests:

pnpm test

For faster feedback while writing tests, use watch mode:

pnpm test:watch

Adding an Extension: pgvector

PGlite ships extensions as WASM modules. To use one—pgvector for similarity search, for example—you register it at construction and let your migration provision it. First install the extension package:

pnpm add @electric-sql/pglite-pgvector

Add the extension to your module's .control requires line so pgpm knows about the dependency:

pgpm extension --add vector

Add a change that creates the vector table (pgpm strips CREATE EXTENSION from migrations, so we provision it out-of-band in the test):

-- Deploy documents_table
CREATE SCHEMA search;

CREATE TABLE search.documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content TEXT NOT NULL,
  embedding VECTOR(3)
);

One instance deploys the whole module

Here's the key thing about seed.pgpm(): every suite deploys your module's entire plan, not just the change under test. Once documents_table is in the module, every suite—including the RLS one—deploys it, so every suite must register the vector WASM extension. Rather than repeat that config, extract a small shared helper in __tests__/connect.ts:

import { vector } from '@electric-sql/pglite-pgvector';
import { getConnections } from 'pglite-test';

// Every suite deploys the full module, so register pgvector once and reuse it.
export const connect = () =>
  getConnections({
    pglite: {
      extensions: { vector },
      extensionSql: ['CREATE EXTENSION IF NOT EXISTS vector;']
    }
  });

Point your RLS suite at it too—swap await getConnections() for await connect() (importing connect from ./connect). Now write the vector test with the same helper:

import { PgTestClient } from 'pglite-test';

import { connect } from './connect';

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

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

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

it('ranks documents by cosine distance', async () => {
  await pg.query(
    `INSERT INTO search.documents (content, embedding) VALUES
       ('apple',  '[1, 0, 0]'),
       ('banana', '[0, 1, 0]'),
       ('cherry', '[0, 0, 1]')`
  );

  const nearest = await pg.one<{ content: string }>(
    `SELECT content FROM search.documents ORDER BY embedding <=> $1 LIMIT 1`,
    ['[0.9, 0.1, 0]']
  );

  expect(nearest.content).toBe('apple');
});

No native build, no server—the WASM extension loads in-process. (Loading a WASM extension on a cold runner is the slowest step, which is why the scaffold's jest.config.js sets a generous testTimeout—one place, no per-test timeouts to sprinkle around.)

Running in CI

Because there's no database to provision, CI is dramatically simpler than a server-based suite—no Postgres service, no Docker, no Supabase CLI. A complete workflow:

name: PGlite tests
on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    # No services. PGlite runs in-process (WASM), so tests need nothing but Node + pnpm.
    steps:
      - uses: actions/checkout@v4
      - uses: pnpm/action-setup@v4
        with:
          version: 10
      - uses: actions/setup-node@v4
        with:
          node-version: '22'
          cache: 'pnpm'
      - run: pnpm install --frozen-lockfile
      - run: pnpm test

That's the headline win: the entire job is install + test. When you bootstrap with pgpm init workspace --pglite, this workflow is already in the scaffold.

Real-World Example

Want to see PGlite tests running in production CI? Check out the pglite-test-suite repository—a complete reference with an RLS demo, a pgvector demo, and a services-free GitHub Actions workflow. Its PGlite vs. pgsql-test doc catalogs every deviation from server-based testing.

Key Takeaways

• RLS behaves identically in PGlite and on a server—setContext() sets role + JWT claims
• Standard app roles are created for you—authenticated and friends are ready without manual CREATE ROLE (opt out with roles: false to bring your own)
• pg seeds and isolates; db switches users—the same two-client model as pgsql-test
• Every suite deploys the whole module—so shared extension setup belongs in one helper (connect.ts), not repeated per file
• Extensions are WASM modules—register with extensions: { … }, provision with CREATE EXTENSION in extensionSql, and declare them with pgpm extension --add
• CI needs zero services—just pnpm install && pnpm test

What's Next

You've tested real migrations, RLS policies, and a WASM extension entirely in-process. The pattern scales: any pgpm module that deploys on a server deploys in PGlite, so you can adopt it for fast local iteration and services-free CI without changing your SQL.

For advanced scenarios and the full list of PGlite-specific configuration, see the pglite-test-suite reference and the pglite-test documentation.