Setting Row-Level-Security (RLS) with Functions on Supabase

RLS functions can be a nifty way to reuse code in the PostgreSQL world to keep your database secure.

In this article, I’ll discuss them with the following example project and goals:

• Multiple organizations use the app
• Their employees have access to their organization data
• There are levels of access within an organization. Roles are: Organization Admin or User
• We have Platform Admin access level which allows them to perform statements on almost all tables.

What is Row-Level-Security (RLS)?

From the Supabase docs:

Postgres Row Level Security (RLS) is a feature of Postgres that allows you to control which users are permitted to perform SELECT/INSERT/UPDATE/DELETE statements on specific rows within tables and views.

Let’s say our example consists of schools, their teachers and their students. School 1, Sunnydale High School, cannot access the data from School 2, the Neptune High School, and so on. RLS allows us to restrict their access based on their membership and user role by using the user id generated by the Authentication process.

If you’re completely new to RLS, I suggest reading the Supabase doc linked above before continuing, or keep it open to help fill in your knowledge gap.

Supabase Authentication

We have two basic authentication roles:

anon: an anonymous request (the user is not logged in)
authenticated: an authenticated request (the user is logged in)

If a user is logged in, we will receive their user_id when we call auth.uid() in our RLS policy.

Why use RLS Functions?

As with functions elsewhere, one big advantage of using RLS functions is to write reusable code, thus minimizing what you have to write.

A common example: a user can do three actions out of the four available (SELECT/INSERT/UPDATE/DELETE). Unfortunately you will need to write three policies, one for each action. Instead of writing the logic three times, you invoke the reusable function.

Besides, if something changes in the logic, that change will be immediately “propagated” too all policies evoking that function.

Implementing a RLS Function

Step 1 - Enable RLS

RLS is not automatically enabled by default on Postgres (Supabase is a Postgres database — Why you should always enable RLS). To enable it via SQL editor:

ALTER TABLE "public"."schools" ENABLE ROW LEVEL SECURITY;

😳 I have in the past set policies and forgot to enable the RLS, so always make sure it’s active!

Step 2 - Basic Police

I have a schools table and I want all authenticated users to be able to read (equivalent to SELECT*) the data on this table, which includes Name, Address, Principal etc.

CREATE POLICY "Authenticated users can select" 
ON "public"."schools" 
FOR SELECT
  TO authenticated 
	USING (TRUE);

You probably know this already, but in case you need a refresher: SELECT = read, INSERT = create, UPDATE and DELETE, well, those are obvious.

Simple, no?

To complicate things a bit — I have a table called school_teacher_link (a many-to-many table), linking teachers to their school. Teachers should only have access to their school’s data.

**Example of a many-to-many table**:


```sql
CREATE TABLE
  public.school_teacher_link (
    teacher_id UUID NOT NULL,
    school_id UUID NULL,
    CONSTRAINT school_teacher_link_teacher_id_fkey FOREIGN KEY (teacher_id) REFERENCES auth.users (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT school_teacher_link_school_id_fkey FOREIGN KEY (school_id) REFERENCES organizations (id) ON UPDATE CASCADE ON DELETE CASCADE
  ) TABLESPACE pg_default;

CREATE UNIQUE INDEX school_teacher_link_pk ON public.organizations_members USING btree (user_id, organization_id);

alter table "public"."school_teacher_link" add constraint "organizations_members_pk" PRIMARY KEY using index "organizations_members_pk";
```


**Example of a table with data from multiple schools**:


```sql
CREATE TABLE
public.class_schedule (
	id UUID NOT NULL
	school_id UUID NOT NULL,
	subject TEXT NOT NULL,
	start_time TIMESTAMP WITH TIME ZONE NOT NULL,
// etc
)
```


_With RLS, we will use the_ _`school_id`_ _and the table_ _`school_teacher_link`_ _to find out whether a given teacher has access to a specific_ _`class_schedule`_ _row._

Step 3: Custom Policy

I need to consider the different roles:

• Teacher - User level access
• Principal - Organization admin access
• Client who helps multiple schools troubleshoot and maintain their data - Platform admin access

To simplify this example, we’re keeping to 3 levels of access.

Step 3.a: Optional) Create a reusable function for basic access control based on role

Let’s tackle the Client’s access first as their access is expansive and only dependent on their role.

In the Platform Admin (PA) case, I’ll need to repeat the access logic for most tables, so it’s wonderful that RLS Policies allow you to use functions.

// My policy is simple and quick

CREATE POLICY "PA can see, edit, update and delete" 
ON "public"."class_schedule" 
FOR ALL // SELECT, INSERT, UPDATE, DELETE
TO authenticated USING (
// auth.uid() will return the authenticated user's id
  public.is_user_allowed (auth.uid (), 'platform_admin')
);

Here’s the function that I can reuse on all tables:

CREATE
OR REPLACE FUNCTION public.is_user_allowed (user_id UUID, allowed_role public.role_type) 
RETURNS BOOLEAN LANGUAGE plpgsql 
SECURITY DEFINER AS $$
DECLARE
  is_user_allowed BOOLEAN;
BEGIN
  select exists (
    select 1
    from "public"."user_profiles"
    where id = user_id 
    and role_id = allowed_role 
  ) INTO is_user_allowed;
  return is_user_allowed;
END $$;

For role_id we use the ENUM role_type . You can read more about ENUM here.

So for all tables that the Platform Admin can have full access, I will simply paste this code (making sure to change the table name).

---

This function can be used for the other roles.

Example: I want a Principal to be able to access the other schools directory, but they shouldn’t be able to make any changes:

CREATE POLICY "Principal can select" 
ON "public"."schools" 
FOR SELECT // Changed to be select only
TO authenticated USING (
  public.is_user_allowed (auth.uid (), 'organization_admin')
);

Unfortunately, if I want to create a policy that allows a user to only SELECT, INSERT and UPDATE, I will need to create three policies since Postgres will only accept one at a time (ALL is considered one in this case).

Step 3.b: Optional) Create a reusable function for access based on user’s organization

What if I want to check if a given user can access a school’s specific data?


In the table `field_trip_schedule` , principals and teachers should only be able to interact with data related to their school. In this example, we have a column `school_id` ([one-to-many relationship](https://www.youtube.com/watch?v=5VrF9OVQ6rg)).


I create a reusable function:


```sql
CREATE
OR REPLACE FUNCTION public.get_user_schools (current_user_id UUID) 
RETURNS UUID[] 
LANGUAGE plpgsql 
SECURITY DEFINER AS $$
DECLARE 
  user_teams UUID[];
BEGIN
  SELECT ARRAY (
		SELECT team_id
    FROM teams_user_profiles_link
    WHERE user_id = current_user_id
) INTO user_teams;

  RETURN user_teams; // returns an array of UUID's
END $$;
```


_Here, I’m purposefully using schools plural because, in this example, a teacher might work in two different schools._


When I call `get_user_schools`, I also use one of Supabase’s helper function — our friend `auth.uid()`:


```sql
CREATE POLICY "Principals and teachers can see their school data" 
ON "public"."field_trip_schedule" 
FOR SELECT
TO authenticated 
USING (
		-- Allow selecting a field trip if its school_id is in the users' access list
    school_id = ANY (public.get_user_schools (auth.uid ()))
  );
```


---


In some cases, you will want to combine functions.


**Example**: Only principals should have access to the school budget table


```sql
CREATE POLICY "Principals can see their school budget" 
ON "public"."school_budget" 
FOR SELECT
TO authenticated 
USING (
    school_id = ANY (public.get_user_schools (auth.uid ()))
		AND public.is_user_allowed (auth.uid (), 'organization_admin')
  );
```

Keep in mind

If you use service_role to create your admin client, <u>RLS will be bypassed</u>. You will have to create your own verification system to ensure that only those with certain privileges can access the API route where the admin client is instantiated.

⚠️ **Never** **expose your** **`service_role`** **on the client side.**

Conclusion

RLS functions offer a significant advantage similar to functions elsewhere: the ability to write reusable code, reducing the overall amount of code you need to write. Hopefully, this article demystified RLS functions, particularly for those who find PostgreSQL intimidating.

I offered one potential solution, so if you have alternative approaches or insights on handling these scenarios, I'm eager to hear from you.