How do I authorise users with username in Supabase?

Supabase supports authorising users with an email address and a password, but how do I allow users to login with a username (instead of their email) and a password?

The SupabaseClient library allows registering and logging in with an email address, but doesn't provide an option to even have a username.

const { data, error } = await supabase.auth.signInWithPassword({
  email: 'email@example.com',
  password: 'password',
});

Solution

After looking everywhere, I found bits and pieces of the solution from several places but not a proper solution. So I decided to write this detailed solution which will hopefully be useful to other developers with the same usecase.

The Explanation

The closest solution I found was on this GitHub discussion. This solution had the right idea, but is flawed since the email address of every user would be exposed.

There are two approaches I came up with,

Server-side Request with SERVICE_KEY: Create a public.users table containing only usernames, with a foreign key reference to the auth.users table. This setup ensures that emails cannot be fetched using the ANON_KEY. You will then need to implement a server-side endpoint that takes in a username and password, fetches the email using the SERVICE_ROLE_KEY, uses that email to authenticate the user, all on the server side.
Deno Edge Function: Use a Deno edge function to perform the same task as the server-side approach. This method is particularly useful if you cannot execute server-side code directly.

Warning: The SERVICE_ROLE_KEY should never be exposed, therefore this logic has to be on the server side only.

Note: The approach I personally went with is using a Dyno function. The reason for this is because I prefer keeping Supabase related code with Supabase rather than with my frontend code (I use an SSR framework).

The Procedure

Step 1: PostgreSQL Setup

The first step is to create the table we're going to make expose, along with some triggers to keep it in sync with auth.users.

Creating the public.profiles table.

CREATE TABLE "public"."profiles" (
  "user_id" uuid PRIMARY KEY NOT NULL REFERENCES "auth"."users" ON DELETE CASCADE,
  "username" text UNIQUE NOT NULL
);

Enable row level security. This is required so anonymous users cannot tamper with this table.

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

Create a policy to allow anyone to read from this table. This depends on your use case, but for me anonymous users should be able to read this table.

CREATE POLICY "Enable read access for all users" ON "public"."profiles" FOR SELECT TO public USING (true);

Now where will these username's come from if users are not allowed to write to the table? Lucky for us, Supabase allows you to send some arbitrary metadata while registering a user. We will simply include the user's username in this data.

const { data, error } = await supabaseClient.auth.signUp({
  email: 'email@example.com',
  password: 'password',
  options: {
    data: {
      username: 'username'
    }
  }
});

When the user is created, this username will be stored in auth.users. We'll create a trigger to read the username from this table on every insertion. Refer Supabase docs.

CREATE FUNCTION "public"."handle_new_user"()
RETURNS trigger
AS $pga$
BEGIN
  INSERT INTO public.profiles (user_id, username)
  VALUES (new.id, new.raw_user_meta_data ->> 'username');
  return new;
END;
$pga$
VOLATILE
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = '';

CREATE TRIGGER "after_user_created"
  AFTER INSERT ON "auth"."users"
  FOR EACH ROW
  EXECUTE PROCEDURE "public"."handle_new_user"();

Note: You will also want to set a similar trigger AFTER UPDATE to update the record in public.profiles whenever a user has updated their profile.

Warning: Make sure you're testing your triggers properly, since Supabase will prevent a user from registering if the trigger fails. This is also useful to us since it ensures there are never duplicate usernames (The unique constraint on the username field will not allow adding a duplicate username, thereby failing the trigger as well as the registration process).

Step 2: Server side authentication

The supabaseClient.auth.signInWithPassword() function only takes in an email address and password, so we'll simply have to first fetch the email using the user's unique username, and try authenticating with it.

To do this we'll create a Deno edge function that takes in an identifier (can be either usernmame or password) and a password as JSON inputs (An example is commented at the bottom of the code below).

// This edge function handles user login requests.
// It accepts a JSON payload with an identifier (email or username) and password,
// retrieves the corresponding email if a username is provided, and attempts to sign in
// the user with Supabase authentication. It returns a JSON response indicating success
// or failure.

// Follow this setup guide to integrate the Deno language server with your editor:
// https://deno.land/manual/getting_started/setup_your_environment
// This enables autocomplete, go to definition, etc.

// Setup type definitions for built-in Supabase Runtime APIs
/// <reference types="https://esm.sh/@supabase/functions-js/src/edge-runtime.d.ts" />
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

const supabase = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
);

Deno.serve(async (req) => {
  try {
    const { identifier, password } = await req.json();
    if (typeof identifier !== 'string' || typeof password !== 'string') {
      throw new Error('Invalid login credentials');
    }

    const email = await getEmail(identifier);
    if (!email) {
      throw new Error('Invalid login credentials');
    }

    const { data, error } = await supabase.auth.signInWithPassword({ email, password });
    if (error) {
      throw error;
    }

    return jsonResponse(data, 200);
  } catch (error) {
    return jsonResponse({ error: error.message }, error.status ?? 400);
  }
});

/**
 * Retrieve email by identifier (email or username).
 * @param {string} identifier - The email or username.
 * @returns {Promise<string | undefined>} The email address or undefined if not found.
 */
async function getEmail(identifier: string): Promise<string | undefined> {
  if (identifier.includes('@')) {
    return identifier;
  }

  const { data: profile } = await supabase
    .from('profiles')
    .select('user_id')
    .eq('username', identifier)
    .single();

  if (!profile?.user_id) {
    return undefined;
  }

  const { data: user } = await supabase.auth.admin.getUserById(profile.user_id);
  return user.user!.email;
}

/**
 * Helper function to create JSON responses.
 * @param {object} body - The response body.
 * @param {number} status - The HTTP status code.
 * @returns {Response} The HTTP response.
 */
function jsonResponse(body: object, status: number): Response {
  return new Response(JSON.stringify(body), {
    headers: { 'Content-Type': 'application/json' },
    status
  });
}

/* To invoke locally:

  1. Run `supabase start` (see: https://supabase.com/docs/reference/cli/supabase-start)
  2. Make an HTTP request:

  curl -i --location --request POST 'http://127.0.0.1:54321/functions/v1/login' \
    --header 'Content-Type: application/json' \
    --data '{"identifier":"username","password":"password"}'

*/

Note: You will have to disable JWT validation for this function. See here and here for more info.

Returning the entire user session isn't necessary, you really only need the auth_token and the refresh_token. Supabase has a convenient function to set the user session on the client side with these two values.

await supabaseClient.auth.setSession({ access_token: 'access_token', refresh_token: 'refresh_token' })

And that's it, your client should now be securely authorised!