Understanding PostgreSQL RLS, and When to Use it

Somewhere around 3rd or 4th quarter 2023, I was starting to look into the requirements of a project I needed to start in the new year. I was trying to decide on which stack I wanted to use, including the database, which of course is a major part of the stack.

Now typically in the past I've used either MySQL or MS SQL Server, as that was either what was available to me, or what I was most familiar with. I was really leaning towards a cloud-based solution this time round though, and I'd been hearing so much buzz about Supabase, that I wanted to see if it made sense for me to use in my project.

One of the main things I saw talked about, one of Supabase's main selling points, was that its underlying DB is PostgreSQL, which allows restricting access via RLS, or Row Level Security.

But what exactly is RLS? And why is it so desirable?

What is PostgreSQL Row-Level Security?

Row-Level Security (RLS) in a database is a feature that allows you to restrict access to individual rows in a database table based on user-defined policies.

These policies are defined at the table level like standard table accesses, but they allow you to restrict which rows a user can access based on specific conditions. RLS applies to all queries that access the table, including SELECT, INSERT, UPDATE, and DELETE statements.

For my purposes, I'd be linking the access control via an authenticated user's ID or role, but you could use other conditions as well, like IP address, or even the time of day.

Why is PostgreSQL Row-Level Security such a big deal?

RLS is important because it allows you to restrict access to rows in a database table based on access policies. This adds an additional layer of security to your database by allowing you to implement access control at the granularity of individual rows rather than just at the table-level.

Some RLS Examples

Full details of PostgreSQL's RLS policies can be found here, but the basic format for creating a policy is as follows:

CREATE POLICY <policy_name>
  ON <table_name>
  FOR <action>
  [USING (<expression>)]
  [WITH CHECK (<expression>)];

Example 1: Bypassing RLS on a table when RLS is enabled

I've included this here just for the sake of demonstrating how the using clause works. In reality, you wouldn't enable RLS on a table in the first place if you were going to bypass it, but just to give you an idea of what the syntax would look like, here's an example:

CREATE POLICY bypass_rls_policy
  ON some_table
  FOR ALL
  USING (true);

So the USING clause essentially just needs to return a boolean value, and if it returns true, then access is granted.

But ok, that's a mostly pointless example, I just wanted to throw it in in case it helps to clarify the syntax.

Example 2: Restricting access to a table based on user ID

Suppose you have a table named tasks in your database, and you want to implement a security policy that allows users to access only their assigned tasks. You could do this by creating a policy that restricts access to rows in the tasks table based on the user's ID:

CREATE POLICY task_assignment_policy
  ON tasks
  FOR ALL
  USING (assigned_to = current_user_id());

This policy would allow users to access only their assigned tasks, and would prevent them from accessing tasks assigned to other users.

Note: In the above example, assigned_to would be a column in the tasks table, and current_user_id() would be a function you define that returns the ID of the currently logged-in user, which means it would of course need to be tied to the user's session.

Example 3: Restricting access to a table based on logged in status

Suppose you have an e-learning site, with course content in the course_content table. You have it setup so that anonymous (non-logged in) users can view the course selection (in a separate table), but they can't view the actual course content. You could do this by creating a policy that restricts access to rows in the courses table based on the user's logged-in status, for example:

CREATE POLICY course_visibility_policy
  ON courses
  FOR SELECT
  USING (is_logged_in());

So in this simple example, no comparison is being made. We're just checking to see if the user is logged in or not. If they are, then they can access the course content. If they're not logged in, then they can't. Super simple!

Note: In the above example, is_logged_in() would be a function you define that returns true if the user is logged in, and false if they are not.

When should you use PostgreSQL Row-Level Security?

This question is the main one I was asking myself when I was looking into Supabase. I mean, I understood the idea behind row-level security, but I couldn't figure out why I would actually need to use it.

This is because in the app I was going to be building, there would be a backend REST API responsible for handling all the security and data access. So while RLS could be used to restrict access to data in the database, the actual restrictions were going to be in the REST API.

It was then that it finally clicked for me, I think thanks to some reddit thread, that RLS is useful, a necessity really, when you're building a frontend-only app (no backend) that accesses the database directly. In that case, you don't have the option of implementing access restrictions in a backend API, so you need to do it via the database directly. And that's where RLS comes in and can be super powerful! 💡😄

Now of course there are other use cases for RLS, but this is the one that finally made it click for me, and made me realize why it's such a big deal.

In a nutshell

So basically, if you're building a frontend-only app, and you're using PostgreSQL as your database (whether through Supabase or not), then you should definitely be using RLS to restrict access to your data.

On the other hand, if you're building a full-stack app and have a backend of some sort, you can still use RLS, but it's not as big of a deal, and you can probably just implement the same restrictions in your backend API.

---

That's it for this post!

If you were as confused with the purpose of PostgreSQL row-level security as I initially was, I hope it helped to clarify things for you, and why you might want to implement it in your own app.

Comments

[Joost]

Rls is extremely important. All these incidents where staff can wrongfully access data because of terminated contracts, moved patients and related people can be prevented with rls. Thanks for the article!

[Christian Ledermann]

Another use case would be multi tenant apps, where no tenant should be able to see other tenants data. Having those restrictions on DB level avoids possible security issues in the app layer.

[Michael La Posta]

Great example!

Especially with the rise in cloud-based apps these days, that extra layer of security is a definite plus.