Intro to Postgres Row-Level Security

This is the second article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will introduce you to Row-Level Security policies, which are key to using a serverless platform. At first, you're going to think these are convoluted and difficult, but there are two very cool things about RLS that I'll point out below.

Setup for the examples

My application has users (called "profiles"), groups, members, events, and attendees. Groups can be private or public, and members can have a status of not approved, waiting approval, approved, or admin. Attendees similarly can be going, not going, or on the wait list. Here's the simplified table structure in diagram form:

A simple policy

Let's start with something easy. Let's make the public groups visible to everybody, including visitors who aren't signed in (i.e. are unauthenticated). We need to write a SELECT policy that is very similar to a WHERE clause:

CREATE POLICY "Groups: public groups viewable by everyone"
  ON groups
  FOR SELECT
  USING (is_public = TRUE);

This will make every row in the groups table visible if it has the is_public boolean set to true. Simple right? You'll see I've given it a descriptive name.

A word about workflow

At this point you may be asking, where I do put this policy? You can use Supabase's great admin UI, which is an excellent place to start playing with policies. In the Authentication section, click on Policies, find the table you want to work on, Enable RLS, and click New Policy. You can either use a template policy, or start from scratch. It will guide you through the options.

However, once you get deeper into writing policies the UI will become a hindrance. You'll eventually want to write these policies in a text file that's backed up by source control. By adding a DROP line before each CREATE, you can easily copy/paste the text into the Supabase UI's SQL Editor to run, edit, and repeat on the policies until they're working perfectly. Or you can connect directly to your database with psql and use the /i command to execute these files.

DROP POLICY IF EXISTS
  "Groups: public groups viewable by everyone" ON groups;

And while you're at it, you might as well add this statement to enable RLS for the table to make your setup repeatable:

ALTER TABLE groups ENABLE ROW LEVEL SECURITY;

By default, tables have no security at all. Anybody can view, insert, update, and delete rows. So definitely be sure to enable RLS for all of your tables, or set up a table policy.

Now for something more complicated

What about private groups? Let's add a policy so that approved members can view their private groups.

CREATE POLICY "Groups: viewable by approved members"
  ON groups FOR SELECT TO authenticated
  USING (EXISTS (SELECT 1 FROM members AS m
    WHERE groups.id = m.group_id
      AND auth.uid() = m.user_id
      AND m.level >= 'approved'));

It starts out the same as the previous policy, but here are the notable differences:

• TO authenticated means the user must be signed in.
• EXISTS is a SQL operator that turns a subquery into a boolean.
• And we have the subquery that looks for a membership record for the authenticated user and current group record with a member level at or above "approved".

The auth.uid() function is a helper that Supabase provides from their authentication system. It returns the current user's ID (which is an UUID).

The first super, awesome part of policies

And this is the first crazy cool part about policies is that when a user signs in and executes a SELECT * ON groups. They are going to get every public group, and every group they are a member of. And that's it! Automatically!! You can add a WHERE clause onto that, but the results will always be filtered by the policies. Your API is secure AND your database is secure. Doesn't matter how you change your logic in the future, or if you switch frontend stacks entirely. Cool, right?

(Although one thing to be careful of is views.)

Chaining policies

Now let's write a policy for events. An event should be viewable by anyone who can view the group.

CREATE POLICY "Events: viewable by anyone who can view the group"
  ON events FOR SELECT
  USING (EXISTS (SELECT 1 FROM groups AS g
    WHERE group_id = g.id));

That's it! The event policy just relies on the group policy. This makes writing policies much simpler than it might first appear. And that's the second very cool thing about policies. You can chain them together.

Conclusion

This article introduced you to Row-Level Security policies, and hopefully left you with the impression that they are not difficult to write.

In the next few articles, I'll cover writing policies for altering rows, how to handle some tricky situations, and finally how to test policies.

---

Photo by Erik Mclean on Unsplash

Comments

[Joost Helberg]

RLS is the best invention since sliced bread. From your datamodel I conclude that you are not using postgresql roles (login and group). Is there a reason to create these extra tables? Is there a reason to not rely on the postgresql authentication of end-users?

[Dave Parsons]

The Supabase platform doesn't seem to use postgresql authentication? I'm not entirely sure. They do provide two postgres roles, "anon" and "authenticated". And an "auth.uid()" helper function. A user is assigned one of those roles based on whether they've signed into the Supabase auth system. I've used the "authenticated" role where it makes sense and "auth.uid()" extensively, but otherwise postgres doesn't know a user's identity. At least on Supabase.