Postgres RLS gotchas & performance

Row Level Security (RLS) is a great feature available in Postgres. It is a nice abstraction layer that simplifies all your queries and implements authorization. However when performance issues arise everybody’s favourite thing to blame is RLS and the general consensus is very mixed. When you first encounter a problem caused by RLS, it can get very frustrating or just flat out confusing. Most people just end up dropping RLS and moving on which is honestly fair. From my experience the feature is solid enough where most of these issues can be solved or small workarounds can be implemented instead but I will say though even getting it right, there is always a performance cost so why use it?

Currently I work on a system where we shamelessly rely on Postgres to do a lot of the heavy lifting. This includes RLS where it basically handles making sure that only the rows the user has access too are visible and nothing else. Best way to think of RLS are just like where clauses applied implicitly at the query level. Which is very nice as it simplifies things a lot... but also sounds very scary, especially when you first hear about it!

In this article I will go over all the gotchas and challenges I encountered along the way when implementing RLS myself as I am confident you will face them with the the out-of-box solution.

Multiple permissive policies

Simple one that prevents the planner from using indexes properly. More than one permissive policy (which is the default and probably why it got me) adds ORs to all your queries, meaning you won't be able to efficiently use your indexes or multiple scans will be required. From my experience this usually results in the planner just giving up and doing a sequential scan.

You should aim to have only one permissive policy for a single table or select/update/delete operation, or make use of restrictive policies which is the equivalent of adding AND to your queries. If you still have a scenario where you need to have multiple permissive policies, you will just have to accept the planner not directly using your indexes.

create table project (tenant_id uuid not null, id uuid not null);

-- permissive is the default
create policy project_admin on project for
select
  to my_user using (has_permission (array['admin']));

create policy project_user on project for
select
  to my_user using (has_permission (array['view-projects']));

Do not do this as your queries will effectively look like this:

select
  *
from
  project
where
  (has_permission (array['admin']))
  or (has_permission (array['view-projects']));

To to fix this you can just create database roles for each one you have but to avoid multiple permissive policies:

create role my_admin_role;

create role my_user_role;

create policy project_admin on project for
select
  to my_admin_role using (has_permission (array['admin']));

create policy project_user on project for
select
  to my_user_role using (has_permission (array['view-projects']));

Must surround policies with brackets (select ...)

Simple one, anytime you have a policy with a constant expression, you need to surround it in brackets so it gets evaluated first. Otherwise the planner will have no option but to evaluate on every row so doing this will create a “InitPlan”:

create policy project_select on project for
select
  to my_user using (
    (
      select
        has_permission (array['view-projects'])
    )
  );

Queries in policies

You might be thinking that we don't need to add the redundant column as we can use a query to get the tenant_id value from the parent relationship table. Although possible with a few tricks, I have found this too confusing to put in the policy itself and simply adding the redundant column is the easiest. I have also shown an example of a restrictive policy below:

create policy project_tenant on project as restrictive
for all
  to my_user using (tenant_id = get_tenant_id ());

create policy project_select on project for
select
  to my_user using (team_id = any (get_access_team_ids ()));

If you need to check for a array of values you can create a function with the stable security definer leakproof keywords that returns back the array of values. You need to specify all of these or it will be evaluated for every row.

create function get_access_team_ids () returns uuid[] as $$
  select array(
    select
      team_user.team_id
    from
      team_user
    where
      team_user.user_id = current_user_id ()
  );
$$ stable security definer leakproof parallel safe;

Redundancy and multi-column indexes

In our case we needed to support a "multi-tenant" style architecture. What this means for our RLS policies is every table now must have a tenant_id column. The problem with this is that it has a cascade effect on everything you do where now all indexes, including foreign keys, primary keys, constraints should probably include this column. We had tables where two columns always needed to be checked, so this added more columns to our btree indexes as it must always be available. Not including these columns will result in the planner not reliably choosing the index.

This is not too big of a problem other than the added complexity and overall larger index sizes. For our case not creating multi-column indexes bricked our cascade deletes so this was the downside of supporting that, but the same thing applies to queries.

create table project (tenant_id uuid not null, team_id uuid not null, id uuid not null);

create policy project_select on project for
select
  to my_user using (
    tenant_id = get_tenant_id ()
    and team_id = any (get_access_team_ids ())
  );

create index project_tenant_team_id_idx on project (tenant_id, team_id, id);

-- OR
alter table project
add primary key (tenant_id, team_id, id);

Since the policy always adds tenant_id and team_id to the query, we should construct our indexes to include these or we risk the planner not choosing a index for the query. I personally found it easiest to just make it the primary key as strange as it seems, as long as we make sure not to duplicate the id column for different tenant_id and team_id combinations.

Functions and stable parallel safe leakproof

These keywords are like public static void main from Java - you just have to add it. Any of these emitted just completely prevents you from using features of Postgres. Not knowing this will most likely just confuse the hell out of you and leave you scratching your head as it would seem like the planner does not choose to do things it should be able to do!

create function get_tenant_id () returns uuid as $$
  select current_setting('my.tenant_id', true)::uuid;
$$ stable parallel safe leakproof;

stable

Even if you know it wouldn't actually do much harm to have the function run every row, I would recommended just adding it anyways if you are able to. The planner just can't come up with a good execution plan most likely because of the cost of running the function for every row is too high.

parallel safe

A simple one to remember to add as functions as this is not the default. Not adding this prevents the planner from ever using parallelism. If you use a function in your policy that you suspect is not parallel safe, I would re-think it or just accept that this as a flaw and make sure not to break it as not having parallel queries you just loose out on performance.

leakproof

This one is super annoying, and from what I can see is not really documented. If you emit this keyword the planner just entirely prevents you from using features of indexes, for all the major ones like gist, gin, and btree. Many operators commonly used to directly query indexes directly can't be used since the underlying functions are not leakproof.

Only place I can see in the PostgreSQL docs:

For example, an index scan cannot be selected for queries on security barrier views (or tables with row-level security policies) if an operator used in the WHERE clause is associated with the operator family of the index, but its underlying function is not marked as LEAKPROOF.

The psql program's \dAo+ meta-command is useful to list operator families and determine which of their operators are marked as leakproof.

Running psql \dAo+ btree will show you all the operators and functions that implement them. You can see which ones are marked as leakproof or not in the last column.

For example trying to use a btree index for text search you will see it will just filter out the rows instead of using it in the scan as a condition.

create index my_table_value_idx on my_table using btree (value);

select
  count(*)
from
  my_table
where
  my_table.value like 'Sant%';

First you have to run this:

alter function textlike (text, text) leakproof;

Now this does look like we are introducing a security risk but most people just disable RLS at this point so doing this just brings you to the default security level for out-of-the-box postgres. I have found reasons such as timing attacks and functions that throw errors are not leakproof which I'm sure are very good reasons but in my opinion this is a over-engineered feature of RLS and should just be opt-in, as it just makes it frustrating to use for an extra security benefit.

Common operators:

• timestampz range operations
• All enum operations
• numeric range operations
• json/jsonb range and path queries
• text similar & like operators
• tsvector operators