Advanced Postgres Row Level Security

This is the third 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.

In this article, I'll show how to write PostgreSQL Row-Level Policies (RLS) policies for modifying table rows, e.g. the CUD part of CRUD.

Setup for the examples

The second article in the series ("Intro to Postgres RLS") has more details on the database tables, but this is the entity-relationship diagram for the examples in this article:

The previous article also included two policies that will allow users to view public groups and the private groups of which they are an approved member.

Allowing insert

For my application, I want to allow any authenticated user to create a group.

CREATE POLICY "Groups: users can insert"
  ON groups FOR INSERT TO authenticated
  WITH CHECK (TRUE);

You'll notice these differences to the policies from the previous article:

• FOR INSERT makes this policy apply to insert operations (including upsert for non-existent records).
• WITH CHECK instead of USING. Insert and update policies use this boolean clause to check the content of the incoming data.
• TRUE indicates that every authenticated user can perform this operation.

Allowing update and delete

While any user can create groups, I only want the admins to be able to update and delete.

CREATE POLICY "Groups: admins can update, delete"
  ON groups FOR ALL TO authenticated
  USING (EXISTS (SELECT 1 FROM members AS m
    WHERE auth.uid() = m.user_id
      AND m.level >= 'admin'
      AND id = m.group_id))
  WITH CHECK (EXISTS (SELECT 1 FROM members AS m
    WHERE auth.uid() = m.user_id
      AND m.level >= 'admin'
      AND id = m.group_id));

The highlights of this policy are:

• FOR ALL will make this policy apply to all operations, select, insert, update, and delete. Select and insert are already more permissive than this rule, so it's easier to just use ALL to cover update and delete. The query planner should optimize out this check for select and insert. Note that this will only work with "permissive" rules, which is the default and what all these examples are using.
• There is a USING and WITH CHECK clause. The docs cover exactly which is used in which situation. For this policy, USING will determine which rows can be updated or deleted, and WITH CHECK will limit the new data for the updated row.

Yes, the two clauses are the same. An admin can modify their groups, but not change the group ID to some random value. An even better check for not modifying the group ID would be to use a "before update trigger", but that's a topic for a future article.

More complex policies

To see something more complex, let's move on to the "Members" table. Users should be able to insert and view their own member records (or memberships). Beyond that users should also be able to view members of their groups.

Let's start with the easiest one first, allowing users to view their memberships:

CREATE POLICY "Members: users can view their own"
  ON members FOR SELECT TO authenticated
  USING ((auth.uid() = user_id));

Nothing new here. Next up the insert policy:

CREATE POLICY "Members: users can insert their own"
  ON members FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id AND level = 'waiting');

Hopefully that looks straightforward too. Users can only insert records for themselves, and the membership level must be set to "waiting" so that an admin can approve them.

And let's also allow approved group members to see the other members of the group:

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

Hopefull that policy makes sense. Check that the user has a member record for the group with at least level "approved". Postgres will accept this policy without any hesitation. However, when a user tries to read the members for one of their groups they will see:

infinite recursion detected in policy for relation "members"

How to fix infinite recursion errors

Yikes! So what happened? First off, the SQL syntax is correct so that's why there was no error when the policy was created. It's only when the policy is executed that the problem occurs. (And thus tests for your policies are very important.) Postgres is trying to evaluate the policy by reading members records, which then invokes the policy and leads to recursion.

An easy way to solve this problem is to create a view, which is like a window into the database filtered through a query:

CREATE OR REPLACE VIEW my_memberships AS
  SELECT * FROM members AS m
    WHERE auth.uid() = m.user_id AND m.level >= 'approved';

This view by default will run in the security context of the user who creates it, which should be your database admin. (So be very careful with views.) This effectively pokes a hole through the RLS policies and returns all of the approved member rows for the current user, regardless of any policies. We can then use this view in the above policy:

CREATE POLICY "Members: viewable by approved members"
  ON members FOR SELECT TO authenticated
  USING (EXISTS (SELECT 1 FROM my_memberships AS m
    WHERE members.group_id = m.group_id));

This resolves the recursion and even simplifies the policy! Remember that EXISTS converts a query into a boolean, and SELECT 1 is just a simple way to return a value from a query since we don't care what that value is. The whole thing will evaluate to false if there are zero rows in the query, in this case no admin member records for the user.

Poking more holes in RLS (on purpose)

Everything is working great so far. But let's add a requirement that users can view the number of members in each group, regardless of whether they are a member. That gives people an idea of how popular a group is before joining. But if the user isn't a member of group 1, SELECT * FROM members WHERE members.group_id = 1 will always return zero rows. How do we solve this?

Let's write another view for this query:

CREATE OR REPLACE VIEW num_members AS
  SELECT g.id as group_id,
    sum(case when m.level >= 'approved' then 1 else 0 end) AS num_members
  FROM members AS m
  JOIN groups AS g ON m.group_id = g.id
  WHERE g.is_public
    OR g.id IN (select group_id from my_memberships)
  GROUP BY g.id;

This will count the number of approved members in all of the groups the user can access (via public groups or approved membership). Note that we had to spell out which groups the user can access instead of relying on the group policies we defined previously. That's because the view is executed with a higher security context which can always see all the groups.

Again, be very careful with these views. Make sure you only use them in very limited cases where more access is required.

Another way to poke a hole in the RLS policies is by writing a function with the security definer clause, which will be covered in a later article.

Conclusion

Things are starting to get more complex, but hopefully breaking down these policies bit by bit is making it easier to understand the flexibility and power of these policies. Let me know in the comments if anything needs more explanation.

With the basic concepts of RLS policies covered, the next article will cover how to test these policies on Supabase. Because nobody should leave data security to chance.

And kudos if you've noticed a chicken-and-egg problem with our admin records. Check out the solution to that in the article on triggers.