Testing Supabase Row Level Security

This is the fourth article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will cover how to test PostgreSQL Row-Level Security (RLS) policies. In particular, how to test them with the Supabase Javascript client library.

Setup for the examples

The second article in the series ("Intro to Postgres RLS") has more details on the database tables, but this is the entity-relationship diagram for the examples in this article:

That article plus the third article also contains the RLS policies for these tables.

Why test at all?

I'm not even going to answer that question. I'll just point you to this article on the top 12 most expensive data breaches. Nearly (or probably over) $5 billion total.

But why not use pgTAP? I don't know about you, but when a project's most simple example is 20 lines of code in an unfamiliar language I start looking for something easier. Plus most of the policies for my application are user-based, so I guess I'd need to fake out the auth.uid() Supabase helper.

Writing tests in Typescript

For my Svelte application I'm already writing tests in Typescript, so why not write the database tests that way as well? Turns out to be very easy to install and configure Vitest, a unit test runner for Vite (the frontend tooling that SvelteKit uses). Vitest shares the configuration from Vite, making things very easy to set up. I didn't need to set any additional configuration. Just install and add this command to the package.json scripts:

"testdb": "vitest run",

Here's a basic test for the public group view policy using Supabase's Javscript client library:

import {describe, expect, it} from 'vitest';
import {supabase} from './lib';

describe('anonymous user', () => {
  it('can only read public groups', async () => {
    const {data, error} = await supabase.from('groups').select('name').order('name');
    expect(error).toBeNull();
    const names = data!.map((entry) => entry.name);
    expect(names).toEqual(['Biking Group', 'Book Club', 'Morning Yoga']);
  });
});

Supabase is a global client created with the createClient() function from the supabase-js library. After creating some dummy test data, run the test with npm run testdb. Very easy.

Test for RLS violation

This will test that an anonymous user can't insert a group record:

// imports from above
import type {StorageError} from '@supabase/storage-js';
import type {PostgrestError} from '@supabase/supabase-js';

/** Verify row-level security error */
function rlsError(error: PostgrestError | StorageError | null) {
  return error?.message.startsWith('new row violates row-level security policy') || false;
}

describe('anonymous user', () => {
  // other tests
  it('cannot insert', async () => {
    const {error} = await supabase.from('groups').insert({
      name: 'Test group',
      is_public: true,
    });
    expect(rlsError(error)).toBe(true);
  });
});

The helper function verifies that the insert operation returns the expected row-level security violation.

Test for authenticated users

After creating some test users, the more complicated policies can be tested as well:

// imports
describe('authenticated user', () => {
  beforeEach(async () => {
    const {error} = await supabase.auth.signInWithPassword({
      email: 'sam@example.com',
      password: 'BadPassword',
    });
    expect(error).toBeNull();
  });

  it('can insert and delete', async () => {
    const {data, error} = await supabase
      .from('groups')
      .insert({
        name: 'Test group',
        description: 'Test description',
        location: 'Nowhere',
      })
      .select()
      .single();
    expect(error).toBeNull();
    expect(data.name).toBe('Test group');
  });
});

By setting up the test data with members at each level (e.g. waiting, approved, admin), each of the policies can easily be tested. You'll want to set up a separate database, and thus Supabase project, just for testing. You can use the Supabase service role to set up and clean up the databases before and after the tests. See the "API" section of "Settings" in the Supabase Admin UI.

Conclusion

With comprehensive tests in place, modifying and adding to the RLS policies is much easier and more reliable. Hopefully this overview provided a good start for you to write your own tests, but please leave comments if anything is unclear or you have questions.

With our RLS policies written and covered by tests, the next article will take a look at Postgres functions and how they are useful in our project.