Fixing an email confirmation problem in Supabase

There’s a special kind of frustration reserved for bugs that produce no errors. Your code runs; the logic executes as expected, but you aren’t getting the output you expect, and things aren’t functioning as they are supposed to. This was an issue I had with a column in my database. I was sending a confirmation email to users when they registered for a waitlist, but each time I add them to the database, and try sending them a confirmation email, it won’t work. In this article, I will discuss how I was able to fix this. Turns out the problem was actually quite simple but hard to decipher.

My goal was straightforward: Send the user a confirmation email when they are successfully added to the waitlist, and then update the email_status column in the database to reflect the status of the email delivery. The 3 possible delivery states are pending, delivered, and failed. This was simple enough in theory, but not in application. After the email was sent successfully, the email_status column stubbornly stayed on pending; no error was thrown, no warning, just silence. The issue wasn’t that the email wasn’t sending or even failed to send in some cases, but that the email_status column wasn’t reflecting the delivery status of the email. After days and almost a week of debugging this issue, I figured out the problem was a missing RLS Policy for UPDATE Operations.

RLS Policy in Supabase

If you are unfamiliar with how Supabase works, you must create something called an RLS (Row Level Security) Policy to be able to run specific operations on a table. Having these policies set up tells Supabase you are authorized to perform actions on a table to modify its data. These operations include updating a table, deleting data from a table, adding data to a table, etc.

The RLS policy is a security feature designed by Supabase to ensure no one can just run any kind of operation on a table, and it also gives developers the ability to control who is allowed to do what. I didn’t understand this early enough and kept thinking maybe something was wrong with my SQL query or the function that executes the Supabase operation. The RLS policy is deny-by-default, which means that if there’s no policy explicitly permitting an operation, that operation is silently blocked. So in my own case, my INSERT query worked because I had a policy for it set up, but my UPDATE query failed because there wasn’t any policy set up for that operation.

Here’s how you add an update policy in Supabase for users that are authenticated via SQL:

CREATE POLICY "Allow update for authenticated users"
  ON users
  FOR UPDATE
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

It’s the same format when you want to add a policy for other operations, like SELECT

CREATE POLICY "policy-name"
  ON "tablename"
  FOR SELECT
  USING true

In my own case though, I didn’t have any authenticated users since it was just a waitlist, so what I did was to set the value of USING and CHECK to true to allow the query pass. In a normal scenario, it is authenticated users that should be able to perform operations against your database and not just anyone.

CREATE POLICY "update email status"
  ON waitlist
  FOR UPDATE
  USING true
  WITH CHECK true;

You can also create policies from your Supabase dashboard if you don’t want to write any code; they make the process very easy. When you enter the dashboard, select the table editor, look for the 3 dots beside your table, select it, and choose the View Policies option to create or disable policies for that table.

Tip: Always ensure you have policies set up for every operation you intend to perform. SELECT, INSERT, UPDATE, and DELETE all need their own policies. Don’t assume 1 covers the rest.

With this set-up, I was able to resolve the issue, and now whatever the result of the email delivery was, the email_status column updates to reflect that, giving me the feedback I need to either resend the email or tell the user to try again later.

Conclusion

Supabase is a great service for building full-stack apps. I highly recommend it. You just have to understand how it works so you don’t get blocked by issues like RLS policy. In case you or anyone gets an issue similar to this, this is most likely the solution: a missing RLS policy not set up in your table. It’s easy to forget setting an RLS policy, especially if you are coming from a MySQL background. I never had to do this with MySQL, so it took some getting used to.

I am Oyinkansola, a front-end developer building engaging web experiences and performant websites for car dealerships, helping them turn online visitors into showroom customers. I also share insights and lessons learnt around projects I am building, as well as insights about front-end development. You can connect with me on X and LinkedIn. I love connecting with developers and tech people.