An introduction to Postgres Row Level Security (RLS)

I was recently helping a Clerk user get their user data integrated with a Supabase database.

This process turned me onto Row Level Security, which is a Postgres feature that the integration leverages. It was my first time walking through Clerk’s integration with Supabase, so I had to dig deep into RLS to understand what it is and how it works. Instead of syncronizing user data with a specific table as I was expecting, the integration instead utilizes the Clerk user ID to prevent access to data in any table WITHOUT modifying any SQL queries.

In this article, I walk through what Row Level Security is and how to use it in your Postgres or Supabase database.

What is Postgres Row Level Security?

Row Level Security (RLS) is a way to restrict what a given user can read and write in a Postgres database. This is accomplished through policies. Policies created for select and delete statements effectively append to the where clause of the query to ensure the correct data is returned. Policies on statements that modify data like insert or update will check the query to make sure no violations occur, preventing data from being modified when it shouldn’t be.

How to enable row level security

RLS is enabled per table using the following query:

alter table orders enable row level security;

Once enabled, policies can be created on the table to apply the checks and filters. For instance, the following policy will restrict what can be selected from the orders table by filtering out any records where user_id does not match the currently logged in user:

create policy "select_by_user_id" on orders 
for select using (current_user() = user_id);

This means that when a select * from orders; is run, the policy will be evaluated and the resulting query is closer to what be executed instead:

select * from orders where id = current_user();

The syntax for policies created on data modification queries is similar but the using keyword is replaced with with check like so:

create policy "insert_by_user_id" on orders 
for insert with check (current_user() = user_id);

Policy types

You might come across examples with the as permissive or as restrictive keywords added. When applied, these change the way multiple policies are evaluated on a query.

Permissive policies are joined with an or operator, whereas restrictive policies are joined with an and operator. By default, all policies are permissive.

Given the following policies:

create policy "select_by_user_id" on orders 
for select using (current_user() = user_id);

create policy "select_recent_orders" on orders
for select using (created > NOW() - INTERVAL '7 days');

Running a select * query evaluates to the following:

select * from orders 
	where user_id = current_user() 
	or created > NOW() - INTERVAL '7 days';

These same policies can be set as restrictive:

create policy "select_by_user_id" on orders 
for select as restrictive using (current_user() = user_id);

create policy "select_recent_orders" on orders
for select as restrictive using (created > NOW() - INTERVAL '7 days');

And the resulting query would swap the or with an and:

select * from orders 
	where user_id = current_user() 
	and created > NOW() - INTERVAL '7 days';

In this example, the desired effect is probably restrictive, but thats entirely up to you as the designer of your database.