PostgreSQL RLS Policies — A Friendly Introduction

Photo by Tim Evans on Unsplash

The Problem

When writing applications, one task you’ll face is preventing data leaks. How do you make sure your users only see what they’re supposed to? It wouldn’t be great if I logged into my email app and saw all my boss’ emails (or it might be if you’re good at your job 😉).

One common way of securing your data is having an access control layer between your API and your database. This could be anything from access policies enforced by your ORM to a separate microservice that makes judicious use of whereclauses when querying your database.

For the most part, this works. But what happens if one of your engineers forgets a where clause or access requirements change and you suddenly have to go and find a million different places to change your access levels? Having a separate access control layer can also add complexity to your code base — depending on how you implement it, you may now have to maintain an additional module with its own set of concerns, design patterns, idioms, and so on.

Introducing Postgres’ row-level security, or RLS policies. RLS policies are access policies that you define at the database level, removing the need for an external service and keeping latency to a minimum.

How do RLS policies work?

In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands

— PostgreSQL documentation

When you query a table protected by RLS policies, Postgres will validate each row the query returns against the policies on the table. Any rows that do not pass the check will be stripped from the response.

For example, if we had the following table:

create table animal (
  id bigint generated by default
  as identity primary key, -- Autoincrementing pkey
  species text not null,
  name text not null
);
alter table animal enable row level security;

insert into animal (species, name) values
  ('lion', 'Alex'),
  ('zebra', 'Marty'),
  ('giraffe', 'Melman'),
  ('hippo', 'Gloria');

I could then apply an RLS policy to the table that only allows me to view data on lions.

create policy "allow anyone to read lions"
on animal
for select
as permissive
to authenticated
using (species = 'lion');

Now, if I run select * from animal as a user in the authenticated group:

 id | species | name
----+---------+--------
 1  | lion    | Alex

Great! We’ve managed to restrict the data I have access to. But that create policy statement is a little confusing. Let’s deconstruct it 🤓:

create policy "allow anyone to read lions"

This one’s easy! We’re creating a new policy called allow anyone to read lions. You can call your policies whatever you like, but it’s worth being descriptive.

on animal

Again, quite straightforward — this policy is for the animal table. Each policy can apply to only one table.

for select

This part of the query decides when the policy will run. In this case, we want records to be checked against the policy when we use a select statement.

The other available options are insert, update, delete and all. The first three cover their respective statements, and all will apply to all the CRUD operations against the table.

If you use merge statements in your code, policies for any of the CRUD operations may be applied depending on what the merge statement is doing.

as permissive

This is where things get interesting. There are two ways a policy can be applied to a table — as a permissive policy or as a restrictive policy. Policies are permissive by default.

Each does roughly as you’d expect — permissive policies define the set of records you’re permitted to read, whereas restrictive policies will restrict you from reading a particular record, even if there exists a permissive policy that grants you access to said record.

You have to have at least one permissive policy to be able to query the table. A table with only restrictive policies will not allow any queries at all.

Permissive policies are applied using a boolean “OR”, so you only need permission from one permissive policy to be able to query a certain row. Restrictive policies, on the other hand, are applied using a boolean “AND” — you have to pass all restrictive policies for every row you want to be able to read.

The overall logic looks something like this:

(perm_pol_1 OR perm_pol_2 OR ... OR perm_pol_n) AND rest_pol_1 AND rest_pol_2 AND ... AND rest_pol_m

I don’t often find myself reaching for a restrictive policy, preferring to use use a slightly more complex permissive policy instead, as it keeps things closer together and easier to reason about (and faster, though that’s a topic for the next article 😉).

Moving on:

to authenticated

The to portion of create policy defines the roles the policy applies to. In this case, I’m using an example group called authenticated, but this could be any group you like, or one of the Postgres defaults: PUBLIC, CURRENT_ROLE, CURRENT_USER, SESSION_USER. These are based on the user creating the RLS policy, not the user querying! It’s best to target a specific role.

Beware, however, that any role with the bypassrls attribute will completely ignore any RLS policies you have defined!

using (species = 'lion');

Finally, the good part! This is where the action happens. The using clause contains a boolean expression that each of our rows is checked against.

There are two available check clauses: using and with check.

using is used to determine which rows are visible to the user when the operation is performed. It’s used in select, updateand delete clauses. In the case of update or delete, if you use Postgres triggers as part of your application, the using clause will be checked after any before triggers have run against the relevant rows.

with check is used to check that the results of update and insert statement are up to snuff. These policies will run after the action, but will prevent the action being committed if they fail. In the case of update, you can use with check in combination with using to create some really powerful policies.

You can think of the flow like this:

# update animal set name = 'Alexa' where species = 'lion'

START TRANSACTION

Before trigger runs

`using` clause check runs
If `using` fails, ROLLBACK

UPDATE

`with check` clause check runs
If `with check` clause check fails, ROLLBACK

COMMIT

You can use any boolean expression in your using and with check clauses including function invocations. The only requirement is that the expression returns a boolean value.

For example, you could replace the above policy with the following:

create or replace function can_read_animal_using(a animal)
returns boolean
as $$
  select a.species = 'lion'
$$
;

create policy "allow animal reads"
on animal
for select
as permissive
to authenticated
using (can_read_animal_using(animal));

Rather than using a simple inline policy, we are now passing the animal record that the query is being run against to a function called can_read_animal_using. This function checks that the animal’s species is a lion — returning true in the case that it is, false otherwise.

There are many good reasons for adopting a pattern of using functions rather than inline policies, and we’ll dive deeper into this in another article. For now though, let’s take a look at how you might implement a useful set of RLS policies this in a production system.

RLS in production with Supabase

I’m going to be using a Postgres database hosted on Supabase as an example. Supabase provides authentication and its client libraries will set some variables in your database that make it really easy to see which user is making a particular query. Supabase leans heavily on RLS policies for user access control, and they have their own guide on the topic.

Supabase databases come pre-built with a schema called auth. The auth schema contains a function called uid() which returns the current user’s unique ID as defined in the auth.users table.

Let’s pretend we’re making a social recipe-sharing app. Users can create recipes which are then visible to all of our other users. However, users should only be able to insert recipes that they authored, and shouldn’t be able to update or delete other users’ recipes.

Note that our policies will only apply to authenticated users. Anonymous users (not signed in) should be able to read all recipes, but not do anything else.

As a first step, let’s define our table:

create table recipe (
  id bigint generated by default
    as identity primary key,
  author uuid not null references auth.users(id),
  title text not null,
  content text not null
);
-- RLS is not enabled by default. Make sure to set it
-- when creating new tables!
alter table recipe enable row level security;

Our first policy will allow all users to read all recipes:

create policy "allow anyone to read recipes"
on recipe
for select
to authenticated
as permissive
using (true);

create policy "allow anyone to read recipes"
on recipe
for select
to anon -- Allow anonymous users to read recipes
as permissive
using (true);

However, only the recipe owner should be able to create, update or delete recipes. Whilst we’re at it, let’s also make sure that users can’t insert recipes with no content! Even though we’ve defined the column as not null, a user could still insert an empty string, which we don’t want.

create policy "allow authenticated users to insert recipes"
on recipe
for insert
to authenticated -- No anon this time
as permissive
-- The first check ensures that users can't insert recipes
-- while masquerading as other users
using (recipe.author = auth.uid() and length(recipe.content) > 0);

create policy "allow users to update their recipes"
on recipe
for update
to authenticated
as permissive
using (recipe.author = auth.uid())
-- Note the use of 'with check' rather than 'using' here
with check (recipe.author = auth.uid() and length(recipe.content) > 0);

create policy "allow users to delete their recipes"
on recipe
for delete
to authenticated
as permissive
using (recipe.author = auth.uid());

Now we have the rules we want: anyone can read recipes, but only the recipe author can actually manage (update, delete) the recipe.

One of the really nice properties of RLS policies is that they enforce a separation of concerns between the security of your database and your business logic. A good example of this is that I wanted to update all of my recipe titles at the same time, I could simply do the following:

update recipe set title = 'Your favourite!'

And only my recipes would be updated!

Neat, right?

As all of this happens at the database level, there’s no need to worry about writing where clauses everywhere or complicating your codebase with security concerns that really belong with Postgres.

I hope this has been a useful introduction to Postgres RLS policies. We make extensive use of Supabase and row-level security at Pluto, so we’ve picked up a lot of useful tips, tricks and best practices for keeping your RLS policies readable, maintainable and performant. Expect more articles soon!

Stay secure out there,

Isaac