Integrating Password + Email Sign-in for magic-link users with Supabase and PostgreSQL

In the realm of web development, the popularity of backend-as-a-service (BaaS) platforms has soared due to their convenience and time-saving abilities. One such platform that has garnered attention is Supabase, an open-source BaaS alternative. Supabase offers developers an array of features, including authentication, database management, and more. In this comprehensive guide, we will explore how to enable password + email sign-in for users who initially started with magic links in Supabase. This integration empowers users to effortlessly transition from magic links to password-based authentication.

Prerequisites

Before diving into this step-by-step tutorial, ensure you have the following prerequisites in place:

1. Deno: Deno is a secure runtime for JavaScript and TypeScript. Visit the official website for instructions on installing Deno.

2. Supabase Account: Create a free Supabase account if you haven't done so already. You'll require your Supabase URL and anonymous key for authentication.

3. PostgreSQL: Ensure you have PostgreSQL installed or set up a PostgreSQL database using Supabase.

To maximize your understanding of this tutorial, it's beneficial to have a basic grasp of Supabase, PostgreSQL, and HTTP requests.

Setting Up the Database

Let's begin by assuming you have one of the web app templates with a public.profile table. We'll specifically add a new column to the "profiles" table to indicate whether existing users are using magic links.

-- Setting the profiles table to indicate that existing users were using magic links
ALTER TABLE public.profiles 
ADD COLUMN auth_options text[] DEFAULT ARRAY['magiclink']::text[] NULL;

Implementing the Edge Function

The edge function plays a pivotal role in this integration as it verifies the user's authentication method (magic link or password) and updates the necessary information accordingly. Below is the Deno code for the edge function:

import { serve } from "https://deno.land/std@0.192.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

// CORS headers for allowing requests from any origin
const corsHeaders = {
  "Access-Control-Allow-Origin": "*",
  "Access-Control-Allow-Headers":
    "authorization, x-client-info, apikey, content-type",
};

serve(async (req) => {
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders });
  }

  // Creating a Supabase client with the provided authorization token
  const supabaseClient = createClient(
    Deno.env.get("SUPABASE_URL") ?? "",
    Deno.env.get("SUPABASE_ANON_KEY") ?? "",
    {
      global: { headers: { Authorization: req.headers.get("Authorization")! } },
      auth: {
        autoRefreshToken: false,
        persistSession: false,
        detectSessionInUrl: false
      }
    }
  );

  // Getting the user information from the provided authorization token
  const { data: { user }, error: userError } = await supabaseClient.auth.getUser();
  if (userError) {
    console.error("User error", userError);
    return new Response(JSON.stringify({ error: userError.message }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Getting the current authentication options for the user from the profiles table
  const { data: auth_options, error: authError } = await supabaseClient.from('profiles').select('auth_options').eq('id', user.id);
  if (authError) {
    console.error("Auth options error", authError);
    return new Response(JSON.stringify({ error: authError.message }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Checking if the user already has password-based authentication
  if (auth_options[0].auth_options.includes("password")) {
    console.error("Password update not allowed for users with password-based authentication");
    return new Response(JSON.stringify({ error: "Password update not allowed for users with password-based authentication" }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Creating a separate Supabase client with the service role key for administrative tasks
  const supabaseAdmin = createClient(
    Deno.env.get("SUPABASE_URL") ?? "",
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? "",
    {
      auth: {
        autoRefreshToken: false,
        persistSession: false,
        detectSessionInUrl: false
      }
    }
  );

  // Updating the user's password using the administrative client
  const { newPassword } = await req.json();
  const { error: updateError } = await supabaseAdmin.auth.admin.updateUserById(user.id, { password: newPassword });
  if (updateError) {
    console.error("Update error", updateError);
    return new Response(JSON.stringify({ error: updateError.message }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Updating the authentication options for the user in the profiles table
  const updatedAuthOptions = [...auth_options[0].auth_options];
  if (!updatedAuthOptions.includes("password")) {
    updatedAuthOptions.push("password");
  }

  const { error: updateAuthOptionsError } = await supabaseClient.from('profiles').update({ auth_options: updatedAuthOptions });
  if (updateAuthOptionsError) {
    console.error("Update auth options error", updateAuthOptionsError);
    return new Response(JSON.stringify({ error: updateAuthOptionsError.message }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Returning a success message if everything went smoothly
  return new Response(
    JSON.stringify({ message: "Password updated successfully" }),
    {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 200,
    },
  );
});

Testing the Integration

To ensure the seamless functioning of our integration, let's conduct some tests encompassing various scenarios:

1. Test with a user who has been using magic links and now wants to set a password.

2. Test with a user who already has a password set to ensure password updates are not allowed for such users.

Conclusion

Congratulations on successfully integrating password + email sign-in with Supabase and PostgreSQL! This integration provides users with the flexibility to switch from magic links to password-based authentication effortlessly, enhancing the overall user experience. By harnessing the power of Supabase's open-source backend-as-a-service capabilities and PostgreSQL's robustness, you can build secure and user-friendly applications more efficiently.