DEV Community

Faris Perwira
Faris Perwira

Posted on

Extending Supabase Auth Users Table

#supabase #postgres #javascript

Supabase logo

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

Extending

Here’s how we extend the auth.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
Enter fullscreen mode Exit fullscreen mode

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

CREATE FUNCTION public.handle_new_user()
RETURNS TRIGGER
SET 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();
Enter fullscreen mode Exit fullscreen mode

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`,
  },
});
Enter fullscreen mode Exit fullscreen mode

What’s Next?

You might consider implementing the RLS definitions for user_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 like Drizzle to generate the SQL scripts based on your schema.

References:

Top comments (0)