Supabase•4mo ago

Performance issues with RLS

Hi, I am quite new with working with RLS, while I do have a background with Postgres I am having a hard time making queries run fast with RLS. I have to admit I have quite a complex app, but perhaps RLS is not meant to be as fast as I expected it to be. I for example have a function get_users_paginated, it gets 20 users at the same time, when having the RPC as invoker it took around 200ms on a loaded table 100k records. Doing some explain analyze showed me that each row was checking the invoking user context (cant do JWT for roles and stuff). When making a very fast function that does caching within the transaction I got it down to 60ms. However when making the RPC DEFINER and checking upfront I get like 11ms. Is this 49ms slower just a accepted cost of RLS. Or am I doing something wrong?

26 Replies

garyaustin•4mo ago

Please check the performance guide here: https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations In particular you don't seem to be wrapping your functions in selects. Also if you can use security definer functions with filters to avoid RLS on sub tables if that can be done helps. If you skim thru that doc you can see RLS can make huge performance impacts if not done correctly (or in some cases may have to be reworked).

IdrisOP•4mo ago

Hmm I did forget some select in this function, will messure if that makes a difference, since the function it calls is already heavily cached.

garyaustin•4mo ago

It will end up running the function on each row versus caching the result to compare.

IdrisOP•4mo ago

Ah so security definer RPCs with filters is accepted, and expected to have better performance. Would you say 49ms is a expected cost of RLS, ofcourse accepted depends on the app.

garyaustin•4mo ago

It makes a difference even on auth.uid() which is just looking at a JWT.

IdrisOP•4mo ago

Yeah true, will change this and messure again

garyaustin•4mo ago

Look at some of the times in the doc. RLS can be seconds of added time or hardly any at all. No easy way to just say what it will be. Some things may not help also depending on indexing, but rarely does anything in the guide hurt times.

IdrisOP•4mo ago

Yes true, a simple policy that just checks against a claim was instant for me. Thats why I put that one on top since its 95% of the case and postgres circuit breaks or statements

garyaustin•4mo ago

You are not guaranteed they will run in "order"

IdrisOP•4mo ago

Its quite well indexed, I notice that planning time is sometimes 2x the execution time. Since some policies get really complex. Ah forgot about that. In my testing cases it happened to be in order according to the path from analyze.

garyaustin•4mo ago

It is all up to what the analyzer decides.

IdrisOP•4mo ago

I also inlined most of the policies, just in case the functions are black boxes. So in most cases the missing select is not a issue since I dont call functions (only call functions when I get a infinte recursion). And with the inlined policies I also have like 3x slower queries.

garyaustin•4mo ago

If you can write your functions you use in RLS so that they are secure as a security definer (or put them in a schema that is safe) then that can be a big win. But you have to make sure you don' t depend on the table they are accessing RLS.

IdrisOP•4mo ago

And everything is really well indexes, no seq scans

garyaustin•4mo ago

Inlining can be much slower than a well written function as shown in the minimize join section. Surrounded by a select.

IdrisOP•4mo ago

Ahh okay thanks Well I am going to play around with this some more. Thanks!!

garyaustin•4mo ago

AI's are not much help with this. RLS in postgres is not used in the past like it gets used in Supabase (PostgREST). So not much history.

IdrisOP•4mo ago

Hey, I tried some of the stuff you told me and got the performance in simple cases to be 1ms which is a 10x compared to bypassrls. In more complex cases where someone is subject to groups and locations control, So they are in a group which is part of a subject in a location and therefore they have access, I got it down to 13ms which is 130x compared to bypass RLS. Would you say this is good and the accepted RLS cost. Or would you say even in these complex cases it can be lower. The RLS one is twice as slow as using a definer function that checks permissions at the start and rreturns the query. I When I do where id =... it is basically just 0.5ms slower, this is crazy haha So tldr, performance with specific eq is perfect. but for things like EXPLAIN ANALYZE SELECT * FROM api.subjects LIMIT 40; I go from 6 ms with definer and checking to 13 with RLS and invoker. (its indexed but due to the query and table size, the planner choose seq.)

[
  {
    "QUERY PLAN": "Limit  (cost=0.00..36.72 rows=40 width=74) (actual time=3.033..13.340 rows=40 loops=1)"
  },
  {
    "QUERY PLAN": "  ->  Seq Scan on subjects  (cost=0.00..5784.00 rows=6300 width=74) (actual time=3.032..13.334 rows=40 loops=1)"
  },
  {
    "QUERY PLAN": "        Filter: ((SubPlan 1) OR (SubPlan 2) OR (SubPlan 3))"
  },
  {
    "QUERY PLAN": "        Rows Removed by Filter: 16"
  },
  {
    "QUERY PLAN": "        SubPlan 1"
  },
  {
    "QUERY PLAN": "          ->  Result  (cost=0.00..0.26 rows=1 width=1) (actual time=0.199..0.199 rows=1 loops=56)"
  },
  {
    "QUERY PLAN": "        SubPlan 2"
  },
  {
    "QUERY PLAN": "          ->  Result  (cost=0.00..0.26 rows=1 width=1) (actual time=0.034..0.034 rows=1 loops=16)"
  },
  {
    "QUERY PLAN": "        SubPlan 3"
  },
  {
    "QUERY PLAN": "          ->  Result  (cost=0.00..0.26 rows=1 width=1) (actual time=0.100..0.100 rows=1 loops=16)"
  },
  {
    "QUERY PLAN": "Planning Time: 0.583 ms"
  },
  {
    "QUERY PLAN": "Execution Time: 13.405 ms"
  }
]

[
  {
    "QUERY PLAN": "Limit  (cost=0.00..36.72 rows=40 width=74) (actual time=3.033..13.340 rows=40 loops=1)"
  },
  {
    "QUERY PLAN": "  ->  Seq Scan on subjects  (cost=0.00..5784.00 rows=6300 width=74) (actual time=3.032..13.334 rows=40 loops=1)"
  },
  {
    "QUERY PLAN": "        Filter: ((SubPlan 1) OR (SubPlan 2) OR (SubPlan 3))"
  },
  {
    "QUERY PLAN": "        Rows Removed by Filter: 16"
  },
  {
    "QUERY PLAN": "        SubPlan 1"
  },
  {
    "QUERY PLAN": "          ->  Result  (cost=0.00..0.26 rows=1 width=1) (actual time=0.199..0.199 rows=1 loops=56)"
  },
  {
    "QUERY PLAN": "        SubPlan 2"
  },
  {
    "QUERY PLAN": "          ->  Result  (cost=0.00..0.26 rows=1 width=1) (actual time=0.034..0.034 rows=1 loops=16)"
  },
  {
    "QUERY PLAN": "        SubPlan 3"
  },
  {
    "QUERY PLAN": "          ->  Result  (cost=0.00..0.26 rows=1 width=1) (actual time=0.100..0.100 rows=1 loops=16)"
  },
  {
    "QUERY PLAN": "Planning Time: 0.583 ms"
  },
  {
    "QUERY PLAN": "Execution Time: 13.405 ms"
  }
]

garyaustin•4mo ago

If you have a column in a table and can use a filter that is always the way to go as far as I know. Don't rely on RLS to filter if the column is on the main table. https://supabase.com/docs/guides/database/postgres/row-level-security#add-filters-to-every-query Anything with fetching the entire table (which includes order and limit) has to process the entire table in most cases. I basically just use the guidelines and if a large table do a quick check. If I don't see in 100msec or larger queries I move on during dev. Odds are I will end up changing something before production and need to change the RLS anyway. The guidelines tend to elimate the 10x or 100x cases and then you are debating over 2x on small numbers. If you know you will have a large table though it is worth checking out the performance in more detail. There are cases like with using an array returning function that the method will break down if the number of results is above 1000. In that case may need to think about redoing the approach to data organization. Here was a study I did on roles/permissions and showing some functions that work well. https://github.com/GaryAustin1/custom-properties

IdrisOP•4mo ago

Yeah thanks, honestly this is fine in this stage. I will just monitor and improve where needed. Thanks for helping Perhaps a good rule would be that I use RPC with definer for things where I just select many things from a table. And RLS for specific where eq etc Hey @garyaustin I assume functions that take a param for each row should not be called with select right?

garyaustin•4mo ago

You should avoid those at all costs for large table selects. The "wrapping" select won't do anything and they will run on each row. If possible the guides show returning an array or list and comparing the row column to that.

IdrisOP•4mo ago

Hey @garyaustin how are you? I got a question, what do you think of patterns like these:

create policy "View task goals if you can view the task" on api.task_goals for
select
    to authenticated using (
        select
            exists (
                select
                    1
                from
                    api.tasks t
                where
                    t.id = task_goals.task_id
            )
    );

create policy "View task goals if you can view the task" on api.task_goals for
select
    to authenticated using (
        select
            exists (
                select
                    1
                from
                    api.tasks t
                where
                    t.id = task_goals.task_id
            )
    );

Considering that task_goals dont really have any columns you can determine access with but tasks does. Do you think the select here is usefull considering that a task often has multiple goals that are selected at the same time and would evaluate the same task id

garyaustin•4mo ago

https://supabase.com/docs/guides/database/postgres/row-level-security#minimize-joins You don't want task_goals.task_id being used in a query as then it has to run on every row if you can avoid it. The link shows using in (or any) to compare the main table column to a list/array which is faster. \

Row Level Security | Supabase Docs

Secure your data using Postgres Row Level Security.

IdrisOP•4mo ago

What would you do for something like this:

create table if not exists
    api.tasks (
        id bigint generated by default as identity primary key,
        name text not null check (
            char_length(name) >= 5
            and char_length(name) <= 50
        ),
        description text check (char_length(description) <= 120),
        deadline TIMESTAMPTZ not null,
        time_indication interval not null check (time_indication <= '5 hours'::interval),
        created_at TIMESTAMPTZ not null default now(),
        chapter_id bigint references api.chapters (id) on delete cascade,
        creator_user_id uuid references api.profiles (id) on delete cascade,
        deleted_at TIMESTAMPTZ,
        constraint task_owner_check check (
            (
                chapter_id is not null
                and creator_user_id is null
            )
            or (
                chapter_id is null
                and creator_user_id is not null
            )
        )
    );
create table
    api.task_goals (
        id bigint generated by default as identity primary key,
        task_id bigint not null references api.tasks (id) on delete cascade,
        text text not null check (
            length(text) >= 5
            and length(text) <= 100
        ),
        constraint unique_goal_per_task unique (task_id, text)
    );

create table if not exists
    api.tasks (
        id bigint generated by default as identity primary key,
        name text not null check (
            char_length(name) >= 5
            and char_length(name) <= 50
        ),
        description text check (char_length(description) <= 120),
        deadline TIMESTAMPTZ not null,
        time_indication interval not null check (time_indication <= '5 hours'::interval),
        created_at TIMESTAMPTZ not null default now(),
        chapter_id bigint references api.chapters (id) on delete cascade,
        creator_user_id uuid references api.profiles (id) on delete cascade,
        deleted_at TIMESTAMPTZ,
        constraint task_owner_check check (
            (
                chapter_id is not null
                and creator_user_id is null
            )
            or (
                chapter_id is null
                and creator_user_id is not null
            )
        )
    );
create table
    api.task_goals (
        id bigint generated by default as identity primary key,
        task_id bigint not null references api.tasks (id) on delete cascade,
        text text not null check (
            length(text) >= 5
            and length(text) <= 100
        ),
        constraint unique_goal_per_task unique (task_id, text)
    );

Like I dont really have something to work with in task_goals I use that most of the time but dont really see a way around it here Access here is determined by creator id or chapter id

garyaustin•4mo ago

I don't have the brain power today (bad head/chest cold) to work RLS in my head today.
All I can say is you want to organize RLS so that you compare a main table row to a result (even 100's of values array). You never want to be using the main column in a select join or passing to a function. This is mainly for select RLS on large tables to have to worry about it.

IdrisOP•4mo ago

Ah I am sorry. Thanks I will see what I can do with that!

Gaming

Programming

Performance issues with RLS

Did you find this page helpful?