Measuring RLS Policy Performance in Supabase

––– views

By Kenneth Cassel

RLS Policy Performance in Supabase, cover art

Recently we experienced some inserts were happening at a progressively slower pace in our application. (We make a tool that helps developers build and sell interactive programming courses). Inserting a new block type into a lesson was taking over 3 seconds!

After some investigation we discovered a Row Level Security policy was slowing down inserts on our lesson content table. We found a way to test RLS performance and narrow down the issue. We'll explore how to do the same for your application in this tutorial.

I tweeted a before and after if you'd like to visually see the speed improvement we were able to make.

How Row Level Security (RLS) and Supabase Work Together

RLS works with Supabase through a combination of the GoTrue Auth and PostgREST part of their system. (Supabase is comprised of a stack of open-source tools including PostgreSQL, PostgREST, and GoTrue).

PostgREST is a way to easily serve an API for your Postgres database. Supabase uses PostgREST for their auto-generated API, which can be used via one of the Supabase client libraries.

We use the combination of supabase-js + Supabase Auth + Row Level Security policies to authenticate and filter queries based on authenticated users.

This combination makes it easy to write client-side sql calls without manually filtering. RLS policies handle the filtering automatically based on who the authenticated user is.

Check out this code example from the Supabase docs to get an idea of how it simplifies your client code.

Instead of writing the query filter when you make the database call

const loggedInUserId = 'd0714948'
let { data, error } = await supabase
  .from('users')
  .select('user_id, name')
  .eq('user_id', loggedInUserId)

// console.log(data)
// => { id: 'd0714948', name: 'Jane' }

const loggedInUserId = 'd0714948'
let { data, error } = await supabase
  .from('users')
  .select('user_id, name')
  .eq('user_id', loggedInUserId)

// console.log(data)
// => { id: 'd0714948', name: 'Jane' }

let user = await supabase.from('users').select('user_id, name')

// console.log(data)
// Still => { id: 'd0714948', name: 'Jane' }

let user = await supabase.from('users').select('user_id, name')

// console.log(data)
// Still => { id: 'd0714948', name: 'Jane' }

If you're authenticated via Supabase Auth, a uuid representing your user account is passed in through a JWT to the PostgREST endpoint for your Supabase project on every call.

From there, the RLS policy for that table is applied, allowing you to only interact with the data the policy specifies you have access to.

In the example above, the RLS policy is setup to only allow a user to select their own data.

Why you can't test RLS inside the Supabase SQL editor

If you're experiencing slow Postgres queries, chances are you want to use the Postgres EXPLAIN command to dive deeper into what is causing the slow down.

The first thing we did when we noticed the slow insert was to test it out inside the Supabase sql editor in the browser.

We ran a query like this and got the following result.

explain (analyze, timing) insert into lesson_contents_v2(lesson_id, properties) values (20,
  '{
  "content": "<p>Testing insert</p>"
}')

explain (analyze, timing) insert into lesson_contents_v2(lesson_id, properties) values (20,
  '{
  "content": "<p>Testing insert</p>"
}')

| QUERY PLAN                                                                                                 |
| ---------------------------------------------------------------------------------------- |
| Insert on lesson_contents_v2  (cost=0.00..0.01 rows=1 width=136) (actual time=0.189..0.190 rows=0 loops=1) |
|   ->  Result  (cost=0.00..0.01 rows=1 width=136) (actual time=0.057..0.058 rows=1 loops=1)                 |
| Planning Time: 0.077 ms                                                                                    |
| Trigger for constraint lesson_id_fk: time=1.104 calls=1                                                    |
| Trigger for constraint lesson_contents_v2_lesson_id_fkey: time=0.160 calls=1                               |
| Trigger for constraint lesson_contents_v2_tutorial_id_fkey: time=0.104 calls=1                             |
| Execution Time: 1.697 ms                                                                                   |

| QUERY PLAN                                                                                                 |
| ---------------------------------------------------------------------------------------- |
| Insert on lesson_contents_v2  (cost=0.00..0.01 rows=1 width=136) (actual time=0.189..0.190 rows=0 loops=1) |
|   ->  Result  (cost=0.00..0.01 rows=1 width=136) (actual time=0.057..0.058 rows=1 loops=1)                 |
| Planning Time: 0.077 ms                                                                                    |
| Trigger for constraint lesson_id_fk: time=1.104 calls=1                                                    |
| Trigger for constraint lesson_contents_v2_lesson_id_fkey: time=0.160 calls=1                               |
| Trigger for constraint lesson_contents_v2_tutorial_id_fkey: time=0.104 calls=1                             |
| Execution Time: 1.697 ms                                                                                   |

Insert only took 1.697ms. It didn't have the same slowdown we were experiencing in prod. Remember, the query was taking over 3 seconds!

The reason why the slowdown didn't occur in the Supabase SQL editor is that RLS policies are disabled for the user supabase_admin. When you run queries inside the Supabase SQL editor, they are ran as the user supabase_admin

Running EXPLAIN as an authenticated user

To test your RLS policy performance, you'll have to run a query as an authenticated user. Before you can do this, you'll need to set the postgres user as a superuser.

Head to the web based sql editor and run the following command to make your postgres user a superuser.

alter user postgres with superuser;

alter user postgres with superuser;

Next, you need to connect to your database as the postgres user. You won't be able to run queries as the postgres user inside the web based sql editor. You can find your connection string details inside of your Supabase dashboard.

After you connect to your database outside of the Supabase UI as the postgres user, you're ready to run your commands as an authenticated user.

These two lines will let you run a query as any authenticated user who's uuid you set for request.jwt.claim.sub.

set local role authenticated;
set local request.jwt.claim.sub = '<user_id>';

set local role authenticated;
set local request.jwt.claim.sub = '<user_id>';

We can now run our same query from above and find out what is slowing it down.

begin;
set local role authenticated;
set local request.jwt.claim.sub = '<user_id>'; 

explain (analyze, timing) insert into lesson_contents_v2(lesson_id, properties) values (20,
  '{
  "content": "<p>Testing insert</p>"
}')
commit;

begin;
set local role authenticated;
set local request.jwt.claim.sub = '<user_id>'; 

explain (analyze, timing) insert into lesson_contents_v2(lesson_id, properties) values (20,
  '{
  "content": "<p>Testing insert</p>"
}')
commit;

From here, we pasted the resulting query plan into this visualizer tool called explain.dalibo.

Here's an example shot of the visualization you'll get.

In our case, we had an expensive RLS policy that included tallying up the total amount of lesson blocks in a lesson before allowing insert using a function called total_lesson_blocks_for_lesson(). It was slowing down inserts because it scanned every row in the lesson contents table before every insert. The function looked something like this:

select * from lesson_contents_v2 where lesson_id=$1
  then (select count(*)
  from lesson_contents_v2
  where lesson_id=$1
  group by lesson_id)

select * from lesson_contents_v2 where lesson_id=$1
  then (select count(*)
  from lesson_contents_v2
  where lesson_id=$1
  group by lesson_id)

We remedied the issue by removing the restriction for total lesson blocks from our RLS policies.

Ultimately we brought insert times at the database level down from 1.5s to just 3ms by making the change!

If you'd like to learn more Advanced Supabase Techniques, check out our course! Or if you want some more free content take a peak at how we manage Supabase Migrations in another tutorial I wrote.

If you have any feedback on this article, shoot me a dm on twitter, or email me at kenneth@slip.so.