Posted onJul 7

Extending Supabase Auth Users Table

Recently, I worked on a project that uses Supabase, and it has a Profile feature for users that required me to extend Supabase’sauth.users table. Extending means creating a new table that references theauth.users table. Why would we need to extend theauth.users table? Well… because Supabase doesn’t allow us to add new columns to theauth.users table.

Extending

Here’s how we extend theauth.users table. First, we need to create a new table to store the additional user data.

CREATE TYPE "public"."user_profile_role" AS ENUM('admin', 'user');CREATE TABLE "user_profiles" ( "id" uuid PRIMARY KEY NOT NULL REFERENCES auth.users ON DELETE CASCADE, "role" "user_profile_role", "first_name" varchar(255), "last_name" varchar(255), "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL);ALTER TABLE "user_profiles" ENABLE ROW LEVEL SECURITY

Second, we need to create a trigger that will execute a function responsible for creating auser_profiles record whenever a new user is created.

CREATE FUNCTION public.handle_new_user()RETURNS TRIGGERSET search_path = ''AS $$BEGIN    INSERT INTO public.user_profiles (id, role, first_name, last_name)    VALUES (      new.id,       CAST(new.raw_user_meta_data->>'role' AS public.user_profile_role),       new.raw_user_meta_data->>'first_name',       new.raw_user_meta_data->>'last_name'    );    RETURN new;END;$$ LANGUAGE plpgsql security definer;CREATE TRIGGER on_auth_user_created  AFTER INSERT ON auth.users  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

raw_user_meta_data is metadata that we send when calling Supabase’s signup function. Below is an example of how we can call the signup function:

const ROLE = {  ADMIN: "admin",  USER: "user",};const { error } = await supabase.auth.signUp({  email,  password,  options: {    data: {      role: ROLE.USER,      first_name: "Faris",      last_name: "Perwira"    },    emailRedirectTo: `${currentDomain}/auth/confirm`,  },});

What’s Next?

You might consider implementing theRLS definitions foruser_profiles table, which will make the table more secure. Writing all the SQL scripts for RLS definitions and table creation can be a lot of extra work. You might consider using an ORM likeDrizzle to generate the SQL scripts based on your schema.

References: