Beginner’s Guide 📘 to Using Supabase with Clerk

User authentication is crucial for any web app, and while securing it properly is essential, it can also be time-consuming. That's why, for my app WhereNow, I chose Clerk to handle the heavy lifting, paired with a Supabase backend for a quick setup experience. But I ran into one challenge:

I didn’t know how to integrate Clerk and Supabase together

Thankfully, I found a solution with the help of the official Clerk YouTube tutorial and the Clerk docs. While these resources provide helpful instructions, they ask you to run a few SQL queries. If you're not familiar with PostgreSQL, it can be tricky to understand exactly what’s happening behind the scenes.

The last thing you want is to apply code without fully understanding it. That’s why I’ve created this guide to break down those SQL queries step-by-step, helping you integrate Clerk and Supabase with confidence. Please pair this article with the video tutorial and documentation mentioned above.

And with that, let’s get right into it.

---

Connecting to Clerk from Supabase using a DB Function

To use Clerk and Supabase together effectively, we need to connect the two services. Fortunately, Clerk provides a DB function that does exactly that.

This function acts as the bridge between Clerk’s authentication system and Supabase’s database, ensuring that user data is properly synced and accessible within your app.

Take at the function below, and let’s run through it line by line:

CREATE OR REPLACE FUNCTION requesting_user_id()
RETURNS TEXT AS $$
    SELECT NULLIF(
        current_setting('request.jwt.claims', true)::json->>'sub',
        ''
    )::text;
$$;

• CREATE OR REPLACE FUNCTION requesting_user_id()
  • Creates a new function called “requesting_user_id()”
  • Overwrites the function if it already exists
• RETURNS TEXT AS $$
  • Specifies the function will return a “TEXT” value
  • $$ - Used to start SQL block quotes. Sometimes more readable alternative to single quotes ‘’

• SELECT statement
  

  SELECT NULLIF(
      current_setting('request.jwt.claims', true)::json->>'sub',
      ''
  )::text;
  

  • NULLIF - Compares ‘sub’ and and empty string’’. If ‘sub’ is empty, ensures NULL is returned rather than an empty string
  • ::text - Type casts the result to TEXT to match how we are storing user_id’s in our Supabase tables

  • current_setting() - A PostgreSQL function that retrieves the value of a config setting

    • Refer to PostgreSQL's documentation
    • 'request.jwt.claims' - A Supabase setting that holding the JWT payload for the database request
    • true - Ensure NULL is returned if the setting doesn’t exist instead of an error

  • ::json - Converts the JWT to JSON format

  • ->> 'sub' - Gets the subject field from the JWT

    • sub is the unique identifier for the user, Clerk includes this in the JWT payload

• LANGUAGE SQL - Specifies the function is in SQL

• STABLE - Indicates that the function’s output remains the same for the same input, unless the underlying data changes

The requesting_user_id() function is an essential part of ensuring that your app’s authentication system works seamlessly between Clerk and Supabase. By extracting the sub field from Clerk’s JWT and using it in Supabase, we ensure that each user’s unique identifier is correctly linked to their account in the database. Understanding how this function works is key to making sure authentication runs smoothly and securely. With this function in place, we can get into configuring our table Row Level Security policies

---

Configuring RLS in Supabase for Clerk

Row Level Security (RLS) Policies are used to define the rules for accessing or modifying a SQL table’s data. Without these restrictions, anyone could read, edit, or delete our stored sensitive data - which is a major privacy risk.

We want to ensure that users can only access their own data when signed in. Thanks to the requesting_user_id()function we created to check the Clerk authentication, setting up RLS policies becomes much easier.

To restrict data access for authenticated users, we use a simple SQL check in our RLS policy to ensure that the current Clerk user_id matches the user_id in the Supabase table. This check prevents unauthorized users from accessing or modifying data.

requesting_user_id() = user_id

Clerk’s documentation lays out how to create your own RLS polcies pretty clearly. Please refer to Step 3 of the docs tutorial to create some for your own project.

Here is an query used in my app WhereNow that creates an RLS policy for the list_items table

CREATE POLICY "Select list_items policy" 
ON "public"."list_items" 
AS PERMISSIVE 
FOR SELECT 
USING (
  EXISTS (
    SELECT 1 
    FROM "public"."lists" 
    WHERE "lists"."id" = "list_items"."list_id"
    AND "lists"."user_id" = requesting_user_id()
  )
);

This query does the following:

• Creates a policy for the list_items table:
  • It applies to the list_items table in the public schema and defines how users can interact with it
• Defines the policy as "PERMISSIVE":
  • This means the policy allows access if the condition is met
• Applies the policy to SELECT operations:
  • This policy specifically affects SELECT queries on the list_items table
    • (Must create other policies for UPDATE , DELETE , and INSERT
• Defines contraints for selecting rows
  • USING specifies conditions in which users are allowed to perform a SELECT query on the list_items table
  • Two conditions:
    • The list_id foreign key in the list_items row must match a list in the id of a list in the lists table
    • The user_id associated with that list must match the requesting_user_id(), which is the ID of the currently authenticated user.
• Ensures users can only access their own list items:
  • By using the requesting_user_id() function, the policy guarantees that users can only view list_items associated with their own lists, ensuring privacy and data security

This query only applies to the SELECT operation, but you can apply similar policies for other operations to further safeguard your table’s data.

---

Wrapping Up: Streamlining Authentication and Security with Clerk and Supabase

In this post, we touched on how using Clerk for user authentication with a Supabase backend be both efficient and secure for your application. The main challenge was connecting these two services and understanding how to do so.

We walked through the process of creating a custom DB function that integrates Clerk’s JWT with Supabase to check that users are properly authenticated. Then, we implemented Row Level Security (RLS) policies to restrict access to sensitive information, ensuring that each user can only access their own data.

By understanding and implementing these steps, you can quickly set up a secure authentication system for your web application and focus more energy on building key features. Happy coding, and best of luck with your projects!

If you have any questions or comments, feel free to reach out - I’m always open to learning something new or lending a helping hand!