Creating an Admin Account in Supabase

To manage user roles effectively, especially if you want to have admin privileges, you'll need to run a couple of SQL commands in Supabase and create your admin user. Here's how you do it, continuing from where we left off:

1. Run SQL Functions in Supabase

• In your Supabase project, open the SQL editor from the dashboard.

• Run the following SQL commands to create a function and trigger that automatically inserts new users into your public.users table and assigns them the default 'USER' role:

create function public.handle_new_user()
returns trigger as $$
begin
  insert into public.users (id)
  values (new.id);
  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();

Execute the scripts by running them in the SQL editor.

2. Create an Admin User

• Once the function and trigger are in place, navigate back to the 'Authentication' tab on your Supabase dashboard.

• Click on 'Users' and then on 'Create New User'.

• Enter your desired admin email and password.

• Ensure that 'User is confirmed' is checked so that the user can access the system immediately without going through the email confirmation process.

3. Assign Admin Role

• After creating your user, a new record will appear in the users table.

• Locate this new user record, which should have been automatically created via the trigger you set up.

• Modify the user's role to 'ADMIN' by updating the user's role field within the users table. Look for a dropdown or enum field where you can change the role from 'USER' to 'ADMIN'.

By following these steps, you're setting up a solid foundation for role-based access control in your application, which is crucial for maintaining security and proper management of your user base.

As always, ensure that you're using secure practices when dealing with database modifications and user roles. Keep sensitive information confidential, and limit admin access to trusted individuals only.