like-gold•5mo ago

Supabase Authentication Trigger & Refine App Integration Question

Hi everyone, I hope you're all doing well. I am encountering a challenge with Supabase Authentication in my Refine apps and would appreciate your guidance. Problem: I have two separate apps, a Merchant app, and a Consumer app, both utilizing Refine. Each app has its own user profile table (merchant_profiles for the Merchant app and consumer_profiles for the Consumer app). However, both apps share the same auth.users table in Supabase. When a user logs into the Merchant app, I need to copy information from auth.users to merchant_profiles, and similarly for the Consumer app. Question: How can I create a trigger in Supabase to automatically copy information from auth.users to the respective profile tables upon login? Additionally, how would I handle this in the authentication solution with Refine? Note: I am using Google Authentication service also.

11 Replies

kapa.ai•5mo ago

Hi @Marcos Monteiro. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- Based on the information provided in the extracts, you can create a trigger in Supabase to automatically copy information from auth.users to the respective profile tables upon login. Here's an example of how you can create a trigger for the public.users table:

-- Create a table for public users  
create table users (  
 id uuid references auth.users not null primary key,  
 updated_at timestamp with time zone,  
 username text unique,  
 full_name text,  
 avatar_url text  
);  
  
-- This trigger automatically creates a public.users entry when a new user signs up via Supabase Auth.  
-- See <https://supabase.com/docs/guides/auth/managing-user-data#using-triggers> for more details.  
create or replace function public.handle_new_public_user()  
returns trigger as $$  
begin  
 insert into public.users (id, full_name, avatar_url)
 values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');  
 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_public_user();

-- Create a table for public users  
create table users (  
 id uuid references auth.users not null primary key,  
 updated_at timestamp with time zone,  
 username text unique,  
 full_name text,  
 avatar_url text  
);  
  
-- This trigger automatically creates a public.users entry when a new user signs up via Supabase Auth.  
-- See <https://supabase.com/docs/guides/auth/managing-user-data#using-triggers> for more details.  
create or replace function public.handle_new_public_user()  
returns trigger as $$  
begin  
 insert into public.users (id, full_name, avatar_url)
 values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');  
 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_public_user();

(Source: Refine Blog (https://refine.dev/blog/refine-pixels-3#2-adding-tables-to-a-supabase-database)) You can modify this script to suit your needs for the merchant_profiles and consumer_profiles tables. As for handling this in the authentication solution with Refine, you can use the authProvider concept. The authProvider is a file created by the CLI that contains pre-defined functions using Supabase Auth API methods internally to perform authentication and authorization operations. This is where you set the complete authentication logic for the app. Here's an example of how the authProvider looks like:

import { AuthProvider } from "@refinedev/core";  
import { supabaseClient } from "utility";  
  
const authProvider: AuthProvider = {  
 login: async ({ email, password, providerName }) => {  
 // sign in with oauth  
 try {  
 if (providerName) {  
 const { data, error } = await supabaseClient.auth.signInWithOAuth({  
 provider: providerName,  
 });  
  
 if (error) {  
 return {  
 success: false,  
 error,  
 };  
 }  
  
 if (data?.url) {  
 return {  
 success: true,  
 };  
 }  
 }  
  
 // sign in with email and password  
 const { data, error } = await supabaseClient.auth.signInWithPassword({  
 email,  
 password,  
 });  
  
 if (error) {  
 return {  
 success: false,  
 error,  
 };  
 }  
  
 if (data?.user) {  
 return {  
 success: true,  
 };  
 }  
 } catch (error: any) {  
 return {  
 success: false,  
 error,  
 };  
 }  
  
 return {  
 success: false,  
 error: {  
 message: "Login failed",  
 name: "Invalid email or password",  
 },  
 };  
 },  
 register: async ({ email, password }) => {  
 try {  
 const { data, error } = await supabaseClient.auth.signUp({  
 email,  
 password,  
 });

import { AuthProvider } from "@refinedev/core";  
import { supabaseClient } from "utility";  
  
const authProvider: AuthProvider = {  
 login: async ({ email, password, providerName }) => {  
 // sign in with oauth  
 try {  
 if (providerName) {  
 const { data, error } = await supabaseClient.auth.signInWithOAuth({  
 provider: providerName,  
 });  
  
 if (error) {  
 return {  
 success: false,  
 error,  
 };  
 }  
  
 if (data?.url) {  
 return {  
 success: true,  
 };  
 }  
 }  
  
 // sign in with email and password  
 const { data, error } = await supabaseClient.auth.signInWithPassword({  
 email,  
 password,  
 });  
  
 if (error) {  
 return {  
 success: false,  
 error,  
 };  
 }  
  
 if (data?.user) {  
 return {  
 success: true,  
 };  
 }  
 } catch (error: any) {  
 return {  
 success: false,  
 error,  
 };  
 }  
  
 return {  
 success: false,  
 error: {  
 message: "Login failed",  
 name: "Invalid email or password",  
 },  
 };  
 },  
 register: async ({ email, password }) => {  
 try {  
 const { data, error } = await supabaseClient.auth.signUp({  
 email,  
 password,  
 });

(Source: Refine Docs (https://refine.dev/docs/data/packages/supabase#understanding-the-auth-provider)) You can modify the authProvider to suit your needs for the Merchant and Consumer apps. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•5mo ago

<@808673784302796848> kapa.ai is still learning and improving, please let us know how it did by reacting below

like-gold•5mo ago

For example, I am looking to create two types of users for an e-commerce site application. One is a user for the admin panel to manage the e-commerce site. The other is a general user who uses the e-commerce site. I know I can create one type of user using the authentication feature of supabase, but how can I implement the feature to create the other type of user? Or is there some way to create two types of users with supabase? @kapa.ai

kapa.ai•5mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- Supabase does not directly support multiple user types, but you can implement this feature by using roles and custom claims. You can create a role for each type of user you need. For example, you can create an 'admin' role for users who manage the e-commerce site and a 'general' role for users who use the e-commerce site. You can use Supabase Custom Claims to set and retrieve user roles for your app. These claims are stored in the auth.users.raw_app_meta_data field and are sent to the client with the access token. Here's how you can set up user roles with Supabase Custom Claims: 1. Install the custom claims SQL functions. You can find the install.sql script in the supabase-community/supabase-custom-claims (https://github.com/supabase-community/supabase-custom-claims/blob/main/install.sql) repository. 2. Bootstrap a claims_admin role for a first user using the Supabase SQL Editor. Only a user with a { claims_admin: true } claim can set claims data on others. 3. Your app can access the getter and setter functions via Supabase Remote Procedure Calls (RPCs) with the supabaseClient.rpc() method. (Source: Refine Blog (https://refine.dev/blog/refine-pixels-6#setting-up-user-roles-with-supabase-custom-claims)) In your Refine app, you can use the authProvider to handle authentication and authorization operations based on the user role. For example, you can restrict access to certain pages or features based on the user role. Please note that this approach requires a good understanding of SQL and Supabase. If you're not comfortable with these technologies, you might want to consider using a different backend service that supports multiple user types out of the box. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•5mo ago

<@808673784302796848> kapa.ai is still learning and improving, please let us know how it did by reacting below

like-gold•5mo ago

If I implement the Supabase Claims, for two roles manager and costumer. How should I change my authProvider in order to make a register and login according to the user roles? @kapa.ai

kapa.ai•5mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- To implement user roles in your authProvider, you would need to adjust the getPermissions() method to fetch the user role from Supabase Custom Claims. Here's an example of how you can adjust the getPermissions() method:

getPermissions: async () => {  
 try {  
 const { error } = await supabaseClient.auth.getUser();  
  
 if (error) {  
 console.error(error);  
 return;  
 }  
  
 const { data } = await supabaseClient.rpc("get_my_claim", {  
 claim: "role",  
 });  
  
 return data;  
 } catch (error: any) {  
 console.error(error);  
 return;  
 }  
},

getPermissions: async () => {  
 try {  
 const { error } = await supabaseClient.auth.getUser();  
  
 if (error) {  
 console.error(error);  
 return;  
 }  
  
 const { data } = await supabaseClient.rpc("get_my_claim", {  
 claim: "role",  
 });  
  
 return data;  
 } catch (error: any) {  
 console.error(error);  
 return;  
 }  
},

(Source: Refine Blog (https://refine.dev/blog/refine-pixels-6#refine-getpermissions-with-supabase-custom-claims)) In this example, the getPermissions() method uses the supabaseClient.rpc() method to call the get_my_claims SQL function remotely. This function retrieves the user role from Supabase Custom Claims. For the register method, you would need to set the user role after the user has been registered. Unfortunately, the information extracts do not provide a specific example of how to do this. However, you can use the supabaseClient.rpc() method to call a SQL function that sets the user role in Supabase Custom Claims. For the login method, you don't need to make any changes. The user role will be fetched by the getPermissions() method after the user has logged in. Please note that this approach requires a good understanding of SQL and Supabase. If you're not comfortable with these technologies, you might want to consider using a different backend service that supports multiple user types out of the box. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•5mo ago

<@808673784302796848> kapa.ai is still learning and improving, please let us know how it did by reacting below

like-gold•5mo ago

You changed the getPermission funtion, but how should I modify the register and login properties? @kapa.ai

kapa.ai•5mo ago

Let me check the knowledge base for you. One moment! :books: ---------------------- The register and login methods in your authProvider would need to be adjusted to handle the user roles. However, the information extracts do not provide specific examples of how to do this. Generally, for the register method, after successfully creating a new user, you would need to set the user role in Supabase Custom Claims. You can do this by calling a SQL function using the supabaseClient.rpc() method. Here's a general example of how you might adjust the register method:

register: async ({ email, password, role }) => {
  try {
    const { user, error } = await supabaseClient.auth.signUp({
      email,
      password,
    });

    if (error) {
      return {
        success: false,
        error,
      };
    }

    if (user) {
      // Set the user role in Supabase Custom Claims
      const { data, error } = await supabaseClient.rpc('set_user_role', {
        user_id: user.id,
        role,
      });

      if (error) {
        console.error(error);
        return {
          success: false,
          error,
        };
      }

      return {
        success: true,
      };
    }
  } catch (error: any) {
    return {
      success: false,
      error,
    };
  }

  return {
    success: false,
    error: {
      message: 'Registration failed',
      name: 'Invalid email or password',
    },
  };
},

register: async ({ email, password, role }) => {
  try {
    const { user, error } = await supabaseClient.auth.signUp({
      email,
      password,
    });

    if (error) {
      return {
        success: false,
        error,
      };
    }

    if (user) {
      // Set the user role in Supabase Custom Claims
      const { data, error } = await supabaseClient.rpc('set_user_role', {
        user_id: user.id,
        role,
      });

      if (error) {
        console.error(error);
        return {
          success: false,
          error,
        };
      }

      return {
        success: true,
      };
    }
  } catch (error: any) {
    return {
      success: false,
      error,
    };
  }

  return {
    success: false,
    error: {
      message: 'Registration failed',
      name: 'Invalid email or password',
    },
  };
},

In this example, the set_user_role SQL function is a custom function that you would need to create in your Supabase database. This function should take a user_id and a role as parameters and set the user role in Supabase Custom Claims. For the login method, you don't need to make any changes. After the user has logged in, the getPermissions() method will fetch the user role. Please note that this is a general example and might not work in your specific case. You would need to adjust the code to suit your needs. Also, this approach requires a good understanding of SQL and Supabase. If you're not comfortable with these technologies, you might want to consider using a different backend service that supports multiple user types out of the box. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•5mo ago

<@808673784302796848> kapa.ai is still learning and improving, please let us know how it did by reacting below