Android Question Supabase table joins with auth.users

elitse

Member
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

Expert
Licensed User
Longtime User
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 Public.Users Table:
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.
Create Database function:
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:
B4X:
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.
 
Last edited:
Upvote 1

Alexander Stolte

Expert
Licensed User
Longtime User
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.
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…