Android Question Supabase table joins with auth.users

[elitse]

Can we do a join with a table in a schema that is not public? for example, join the auth.user with Public.user? If not, are there any work arounds? Thank you.

 

[Alexander Stolte]

Can we do a join with a table in a schema that is not public?

Unfortunately not, but there is a work around

are there any work arounds?

Yes.
For my Supachat example project I had to control a private.auth column.

The solution was to create a public.user table and fill it together with a database function when a new user creates an account.

Example:

create table
  public.users (
    id uuid not null,
    username text null,
    constraint users_pkey primary key (id)
  ) tablespace pg_default;

Create a new database function in the supabase dashboard.
In my example i have a json string in the raw_user_meta_data column with the username of the user. You can choose a other column.

begin
  insert into public.users (id, username)
  values (new.id, new.raw_user_meta_data ->> 'username');
  return new;
end;

Set the "Show advanced settings" to true and scroll to the bottom.
On the section "Type of security" select the "SECURITY DEFINER"
Read more on #4

Now we need to create a database trigger to call this function:

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

Now every time a new user registers he should write to the public.users table and get the user name from the auth table.

And now you can join your new public.users table.

 

[elitse]

Thanks!

 

[Alexander Stolte]

Important addition on the function creation:
Set the "Show advanced settings" to true and scroll to the bottom.
On the section "Type of security" select the "SECURITY DEFINER"

otherwise an error always occurs when a user is added because the function has no authorization to write to the table.