It seems that none of the following work inside the trigger 😦 -

new.raw_user_meta_data->>'avatar_url'

-

new.raw_user_meta_data->>'full_name'

-

uuid_generate_v4()

-

uuid_generate_v4()::text

weirdly,

random()::text

works though any way to generate the same random value for 2 columns when inserting a value via sql?

All of the above works, I have them in a trigger I created

table

sql
create schema if not exists mindwiki;

create table if not exists mindwiki.profiles
(
    user_id uuid primary key references auth.users default auth.uid(),
    uid uuid not null unique default uuid_generate_v4(),
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone not null default now(),
    email citext not null unique default auth.email(),
    is_public boolean default true,

    username citext unique check (char_length(username) >= 3),
    full_name text,
    avatar_url text
);

No RLS was enabled trigger

sql
create or replace function public.handle_new_user()
 returns trigger as
$$
begin
 insert into mindwiki.profiles (username, full_name, avatar_url)
 values (null, 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_user();

After this, signup/magic-link gives the error:

Inviting user failed: Database error saving new user

That's because your username field is null

nope. setting the value from

null

to

'trial'

gets the same error. The current auth.users table and mindwiki.profiles table have no entries

your function need to know about the

mindwiki

schema, it doesn't know about it by default, it only knows of the

public

schema

doesn't work still 😦

sql

create or replace function public.handle_new_user()
    returns trigger
    language plpgsql
    security definer set search_path = public, mindwiki
as
$$
begin
    insert into mindwiki.profiles (username, full_name, avatar_url)
    values ('trial', new.raw_user_meta_data ->> 'full_name', new.raw_user_meta_data ->> 'avatar_url');
    return new;
end;
$$;

Tried both

search_path = public, mindwiki

and

search_path = mindwiki, public

I have no idea what is going wrong in your case then

I don't want to export internal serial values like

id

to the users. Someone here told me to create a new schema and put all the tables in there, and then create views in the public schema 😅 I just tried to put the table in the public schema but that didn't work either

sql

create table if not exists public.profiles
(
    user_id    uuid primary key references auth.users   default auth.uid(),
    uid        uuid                     not null unique default uuid_generate_v4(),
    created_at timestamp with time zone                 default now() not null,
    updated_at timestamp with time zone not null        default now(),
    email      citext                   not null unique default auth.email(),
    is_public  boolean                                  default true,

    username   citext unique check (char_length(username) >= 3),
    full_name  text,
    avatar_url text
);


create or replace function public.handle_new_user()
    returns trigger
    language plpgsql
    security definer set search_path = public
as
$$
begin
    insert into profiles (username, full_name, avatar_url)
    values ('trial', new.raw_user_meta_data ->> 'full_name', new.raw_user_meta_data ->> 'avatar_url');
    return new;
end;
$$;

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

Each time you paste here there are space in your

new.raw_user...

before the arrow and after, is that how it is when you create the function?

Getting rid of the

default auth.uid()

,

default auth.email()

and removing the spaces after & before the arrow operators worked! My IDE was automatically adding those spaces 😢 I had tested the

auth.uid()

via the

set role authenticated;

method and it worked there.

haha, happy its working for you

moving back the table to mindwiki schema works as well.