access supabase auth tables from refine

how can i access auth table (users in my case) so i can create users list page

11 Replies

Hi @danyalutsevich. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- To access the Supabase auth users table and create a users list page in Refine, you need to follow a few steps: 1. First, you need to create a shadow table for the auth.users table in the public schema. This is because Supabase doesn't allow direct access to the auth.users table for security reasons. Here's how you can create this shadow table Create Your Easy Customizable Internal Issue Tracker With Refine and Supabase (https://refine.dev/blog/customizable-issue-tracker-with-refine-and-supabase/#2-adding-tables-to-a-supabase-database):

-- 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.
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.
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();

2. After creating the shadow table, you can add the users resource to your Refine app Create Your Easy Customizable Internal Issue Tracker With Refine and Supabase (https://refine.dev/blog/customizable-issue-tracker-with-refine-and-supabase/#add-resource):

import { Refine } from "@refinedev/core";

function App() {
  return (
    <Refine
      // ... other props
      resources={[
        {
          name: "users",
        },
        // ... other resources
      ]}
    />
  );
}

import { Refine } from "@refinedev/core";

function App() {
  return (
    <Refine
      // ... other props
      resources={[
        {
          name: "users",
        },
        // ... other resources
      ]}
    />
  );
}

3. Now you can create a list page for users. Here's an example of how you can create a simple users list page:

import React from "react";
import { useTable, List, Table } from "@refinedev/antd";
import { IUser } from "interfaces";

export const UserList: React.FC = () => {
  const { tableProps } = useTable<IUser>({
    resource: "users",
  });

  return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column dataIndex="id" title="ID" />
        <Table.Column dataIndex="username" title="Username" />
        <Table.Column dataIndex="full_name" title="Full Name" />
        <Table.Column dataIndex="avatar_url" title="Avatar URL" />
      </Table>
    </List>
  );
};

import React from "react";
import { useTable, List, Table } from "@refinedev/antd";
import { IUser } from "interfaces";

export const UserList: React.FC = () => {
  const { tableProps } = useTable<IUser>({
    resource: "users",
  });

  return (
    <List>
      <Table {...tableProps} rowKey="id">
        <Table.Column dataIndex="id" title="ID" />
        <Table.Column dataIndex="username" title="Username" />
        <Table.Column dataIndex="full_name" title="Full Name" />
        <Table.Column dataIndex="avatar_url" title="Avatar URL" />
      </Table>
    </List>
  );
};

Remember to define the IUser interface according to your users table structure. By following these steps, you should be able to access the Supabase auth users (via the shadow table) and create a users list page in your Refine application. The useTable hook will automatically fetch the data from your Supabase backend using the data provider you've set up Using meta to pass values to data provider (https://refine.dev/docs/data/packages/supabase/#using-meta-to-pass-values-to-data-provider). ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

unwilling-turquoiseOP•4w ago

@kapa.ai it does not work

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- I apologize that the previous solution didn't work for you. To better assist you, I'll need more specific information about what exactly isn't working. Could you please provide more details about the issue you're encountering? For example: 1. Are you getting any error messages? 2. Is the users list not displaying? 3. Are you having trouble setting up the shadow table? 4. Is there a specific part of the process that's not working as expected? Without more context, it's difficult to pinpoint the exact problem. If you can provide more details about what you've tried and where you're stuck, I'll be better equipped to help you troubleshoot the issue with accessing the Supabase auth tables from Refine. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

unwilling-turquoiseOP•4w ago

@kapa.ai it says that this resourse does not exits "relation "public.users" does not exist"

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- It seems the issue you're encountering is that the public.users table doesn't exist in your Supabase database. This error suggests that you haven't created the shadow table for the auth.users table as mentioned in the previous steps. To resolve this issue, you need to create the public.users table in your Supabase database. Here's how you can do it: 1. Log in to your Supabase project dashboard. 2. Go to the SQL Editor. 3. Run the following SQL script to create the public.users table and the necessary trigger Setting Up Supabase for Refine (https://refine.dev/blog/refine-pixels-3/#2-adding-tables-to-a-supabase-database):

-- Create a table for public users
create table public.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.
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 public.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.
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();

After running this script, the public.users table should be created, and the error you're seeing should be resolved. If you still encounter issues after creating the table, please let me know, and I'll be happy to help you further troubleshoot the problem. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

unwilling-turquoiseOP•4w ago

@kapa.ai what if auth.user was updated? will it update public user?

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- Based on the knowledge sources provided, there isn't specific information about automatically updating the public.users table when auth.users is updated. However, I can provide some insights based on the available information: 1. The trigger we created in the previous step only handles new user creation Setting Up Supabase for Refine (https://refine.dev/blog/refine-pixels-3/#2-adding-tables-to-a-supabase-database). It doesn't automatically update the public.users table when auth.users is updated. 2. To keep the public.users table in sync with auth.users updates, you would need to create an additional trigger or implement a custom solution. 3. One approach could be to create an AFTER UPDATE trigger on the auth.users table that updates the corresponding record in public.users. However, this would require additional database setup and permissions. 4. Alternatively, you could handle updates manually in your application logic. For example, when updating user information, you could use the Supabase client to update both tables. Here's an example of how you might manually update both tables:

const updateUser = async (userId, newData) => {
  const { data, error } = await supabaseClient
    .from('users')
    .update(newData)
    .eq('id', userId);

  if (error) {
    console.error('Error updating public.users:', error);
    return;
  }

  // Update auth.users if needed
  const { error: authError } = await supabaseClient.auth.updateUser(newData);

  if (authError) {
    console.error('Error updating auth.users:', authError);
  }
};

const updateUser = async (userId, newData) => {
  const { data, error } = await supabaseClient
    .from('users')
    .update(newData)
    .eq('id', userId);

  if (error) {
    console.error('Error updating public.users:', error);
    return;
  }

  // Update auth.users if needed
  const { error: authError } = await supabaseClient.auth.updateUser(newData);

  if (authError) {
    console.error('Error updating auth.users:', authError);
  }
};

Remember that directly updating auth.users might be restricted depending on your Supabase setup and permissions. For a more robust solution, you might want to consider implementing a custom sync mechanism or using Supabase's Row Level Security (RLS) policies to manage access to user data more effectively. If you need a specific solution for your use case, I recommend reaching out to the Refine community or Supabase support for more tailored advice, as the provided knowledge sources don't cover this specific scenario in detail. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

access supabase auth tables from refine

Did you find this page helpful?