Open in app

Sign in

Write

Sign in

Two Postgres tables exploring a new approach to RLS. Photo by Mylon Ollila on Unsplash

Supa RLS Exploration

Caleb Brewer
7 min readNov 11, 2023

--

I’ve already spent a lot of time exploring Postgres RLS with the goal of simplifying application development — now I’d like to thin out the stack a bit further with Supabase.

Our goal is to build out a comprehensive permission set to prove Supabase’s viability as a real-world capable backend-as-a-service. Personally, I hope it can replace Graphile as my application layer by supporting access patterns such as:

  1. Normalized access control lists
  2. Permission boundaries
  3. User groups

That’s honestly too much to cover in one go, so let’s focus on requirements for the first pattern for now:

1. Item Requirements

  1. Any authenticated user should be able to add items
  2. Any authenticated user should be able to select public items
  3. Users should be able to select, update, and delete items that they have a permission for

2. Item Permission Requirements

  1. Authors should have permissions for the items they create
  2. Any authenticated user should be able to add permissions for items they have a permission for

Throughout this article, I’ll refer to the Supabase GUI and my sample code. The Supabase SaaS product has a generous free tier which should make it easy and free to follow along there, or you can spin up my repository on your local machine, (which makes it easier to rapidly run tests and test tweaks).

The Database

Let’s first sketch out a simple database structure on which we can test Supabase’s functionality. This schema should offer us an unopinionated testing ground to flex Supabase’s features without focusing on the potential applications.

Tables

We’ll start with a couple of simple tables, on which we can start experimenting with some policies. To be really generic, I’ll just call them items and item_permissions, which are hopefully self-explanatory:

create table items (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default now(),
  content text,
  metadata jsonb not null default '{}',
  public boolean not null default false
);
alter table items enable row level security;
alter publication supabase_realtime add table items;

create table item_permissions (
  id uuid default gen_random_uuid() primary key,
  item_id uuid references items(id) on delete cascade,
  permitted uuid not null primary key
);
alter table item_permissions enable row level security;
alter publication supabase_realtime add table item_permissions;

There is some laziness here, like the metadata column on items since I don’t know what UI will be easiest for testing yet. There’s also some diligence:

  • Permissions are stored in a separate table, (which makes policies more complicated but scales better, as I’ve explored in the past)
  • Notably, adding both tables to the real-time publication enables one of Supabase’s coolest features, (real-time updates, which are optional)

Policies

The policy we’re all here to talk about is the one that restricts modifications to just users with permissions, (requirement 1.3):

create policy manage_item
on items
for all
to authenticated
using (
  exists(
    select item_id
    from item_permissions
    where items.id = item_id
    and permitted_id = auth.uid()
  )
)
with check (
  exists(
    select item_id
    from item_permissions
    where items.id = item_id
    and permitted_id = auth.uid()
  )
);

If you’re following along in the Supabase console go with the “For full customization” option in the New Policy menu for this one, then you just need to drop theexists(...) clause into both the USING and WITH CHECK expression fields. This one looks complex with an internal select and all, but it is simply checking the item_permissions table based on the current user.

To follow that up, here’s a super simple policy allowing everyone to to select public items, (requirement 1.2):

create policy select_public_item
on items
for select
to public
using (
  public = true
);

Note that in the above we’ve created this policy for public, unlike the others which are for authenticated users. Another for items is the permission for anyone authenticated to insert something new, (requirement 1.1):

create policy insert_items
on items
for insert
to authenticated
with check (true);

The “Enable insert access for authenticated users only” template in the Supabase GUI works fine for this:

The Supabase UI provides some convenient policy templates for those less familiar, (although using a migration file is probably more maintainable).

Next, the policy for adding permissions to an item is slightly more complicated, (requirement 2.2):

create policy insert_item_permissions
on item_permissions
for insert
to authenticated
with check (
  exists (
    select id
    from item_permissions
    where item_id = item_id
    and permitted_id = auth.uid()
  )
);

Supporting Policies

You may have noticed that a couple of our policies have inner select statements on the item_permissions table, which also has RLS enabled. To ensure that works we essentially need a supporting policy that is inherently invoked by those inner selects:

create policy select_item_permissions
on item_permission
for select
to authenticated
using (
  permitted_id = auth.uid()
);

I would encourage you to try leaving this out to see the effect, (disable it in the GUI or drop it from the migration and rebuild the DB if you’re running locally). One more supporting policy is needed to support a common case for an annoying reason:

create policy select_item
on items
for select
to authenticated
using (
    not exists (
      select *
      from item_permissions
      where item_id = items.id
    )
);

This policy will allow someone to select an item that doesn’t have any permissions yet, i.e. during an insert … returning statement. This is needed because rows inserted by triggers aren’t made available to policies executed in the same transaction, as discussed by Carl Sverre on Stack Overflow. Obviously this can leak data if all of an item’s permissions are deleted…but orphaned items are an issue for more reasons than that.

There are a number of additional policies that you may find useful, (such as delete-related permissions to prevent that whole orphan scenario), so please refer to the sample code to dive deeper if you’d like.

A Useful Trigger

Since permissions are normalized to their own table, we need to make sure items are immediately accessible to the author, (requirement 2.1), so I like to add a trigger for convenience:

create or replace function insert_item_permission()
  returns trigger
  security definer
  as $$
begin
  insert into item_permissions (item_id, permitted_id) values (
    new.id,
    auth.uid()
  );
  return new;
end
$$ language plpgsql;

create trigger insert_item_permission_trigger
after insert
on items
for each row
execute procedure insert_permission();

There is one really critical piece here:

security definer allows us to execute that insert as an admin, (i.e. without row-level security). An alternative approach could be to write some policies that allow inserts under special conditions, but trust me — it gets messy quickly

To sum it up:

  1. Our insert_item_permissions policy only allows returning records for which a user has permission
  2. So we need to trigger the permission creation after inserting the new item

Lastly, an alternative if you’re not stoked about the complexity of triggering the author’s permission creation: you could also toss an owner_id column on the items table and add an additional policy to allow access based on that column…but then you have to worry about handling access in two policies, (and then appropriately handling them in your application layer). This simpler approach may be fine for applications without advanced access requirements, (such as sharing); however, it will become more complicated since that logic will be distributed throughout your application.

Bear in mind that my chosen method will also require additional triggers to avoid edge cases such as deleting the only permission for an item — but my goal here is to eventually support those advanced access requirements.

A Simple UI

This isn’t a React tutorial so I’m just going to show you what it looks like, the code is also present in the GitHub repo for your reference.

Simple visual demo of the Supabase setup

So what’s going on here? I have two browsers windows open to the simple UI and I start off my registering a separate account on both of them, (one user on the left, and a second on the right). From there I do the following:

  1. Click anywhere to create a new item (rendered as a box)
  2. Shift+click a box to make it public, then it will show up for the other user
  3. Drag a box around
  4. Click a box to delete it

That’s literally it, nothing fancy. It expresses the majority of our policies and hopefully gives you some ideas of what can be accomplished, with the added benefit of real-time fun on a low-code backend-as-a-service.

Appendix

Annoying things

A few other things that stuck out along the way:

  • on delete cascadecan’t be set in the Supabase UI, must be done via SQL
  • react-supabasedoesn’t support @supabase/supabase-js@2 yet, which led to some longer hours than anticipated building the “simple” UI
  • Supabase realtime emits delete to everyone, not just those with permission —I’m not sure how much this matters yet, but it will certain hurt performance when we go to scale

Some help from along the way

Postgres
Row Level Security
Supabase

--

--

Caleb Brewer

Written by Caleb Brewer

103 Followers

I build things by breaking them.

Help

Status

About

Careers

Blog

Privacy

Terms

Text to speech

Teams