Dashboard errors when managing users

[TheOtherBrian1]

PROBLEM

Receiving the following or similar error messages in the Dashboard when managing users.

... Database error
... Error sending

Or, receiving a comparable 500 error from the Auth REST API:

Database error ...

SOLUTION 1 (trigger related)

Check if the auth schema contains any triggers in the Dashboard's trigger section. Remove all triggers by dropping their functions with a CASCADE modifier:

DROP FUNCTION <function name>() CASCADE;

-- If you'd prefer, you can drop the trigger alone with the following query: 
-- DROP TRIGGER <trigger_name> on auth.<table_name>;

Then recreate the functions with a security definer modifier before recreating the triggers.

The SQL Editor contains a template for User Management. Within it, there is a working example of how to setup triggers with security definers that may be worth referencing:

create table profiles (
  id uuid references auth.users on delete cascade not null primary key,
  updated_at timestamp with time zone,
  username text unique,
  full_name text,
  avatar_url text,
  website text,

  constraint username_length check (char_length(username) >= 3)
);

create function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (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_user();

EXPLANATION

One of the most common design patterns in Supabase is to add a trigger to the auth.users table. The database role managing authentication (supabase_auth_admin) only has the necessary permissions it needs to perform its duties. So, when a trigger operated by the supabase_auth_admin interacts outside the auth schema, it causes a permission error.

A security definer function retains the privileges of the database user that created it. As long as it is the postgres role, your auth triggers should be able to engage with outside tables.

SOLUTION 2 (constraint related)

If you did not create a trigger, check if you created a foreign/primary key relationship between the auth.users table and another table. If you did, then ALTER the behavior of the relationship and recreate it with a less restrictive constraint.

[MattSolvesGames]

new.raw_app_meta_data ->> 'custom_id' isn't returning anything except null values for me even though I can see on the auth.users row that the user has the metadata set.

CREATE TABLE users (
    id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL PRIMARY KEY,
    custom_id VARCHAR(17)
);

CREATE FUNCTION handle_new_user() RETURNS TRIGGER LANGUAGE plpgsql SECURITY definer SET search_path = '' AS $$
BEGIN
    INSERT INTO public.users(id, custom_id) VALUES (new.id, new.raw_app_meta_data ->> 'custom_id');
    RETURN new;
END;
$$;

CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION handle_new_user();

Here's what I see on the auth.users entry:

  "raw_app_meta_data": {
    "provider": "email",
    "custom_id": "73849099371620888",
    "providers": [
      "email"
    ]
  },

Yet on the new row created in public.users, the column is null, which was giving me a 500 error since I had specified a NOT NULL constraint. As far as I can tell, I'm doing everything exactly like the documentation and other discussions have said. Is the metadata not getting created with the rest of the row or something?

[TheOtherBrian1]

I don't believe custom_id is a default value on the raw_app_meta_data column. You can add it with an auth_hook.

Maybe you were thinking of the raw_user_meta_data column, which can be modified through a Supabase-Client?

@MattSolvesGames For your case, just for the debugging period, I recommend placing a RAISE LOG keyword in your function. It will allow you to inspect the values in the trigger to ensure they meet your needs:

-- The REPLACE keyword overwrites the previous function if it already exists
CREATE OR REPLACE FUNCTION handle_new_user() 
RETURNS TRIGGER 
LANGUAGE plpgsql 
SECURITY definer SET search_path = '' 
AS $$
BEGIN
    -- record new row to Postgres Logs | Remove when you're done debugging
    RAISE LOG 'logged handle_new_user: %', (select to_jsonb(new.*));

    INSERT INTO public.users(id, custom_id) 
    VALUES (new.id, new.raw_app_meta_data ->> 'custom_id');

    RETURN new;
END;
$$;

Once triggered, you can then inspect the new row value in the Postgres Logs. Just make sure to filter by the term "logged handle_new_user":

Alternatively, you can filter the advanced Log Explorer for errors from the supabase_auth_admin role (guide here). The log manager digests logs in batches, so it may take a minute after executing the function before it can be searched.

In either case, the output isn't going to be the most readable, so you should run it through a JSON Beautifier.

[MattSolvesGames]

@TheOtherBrian1 Huh; there was more to my comment than got posted; I guess my edit didn't go through--the purpose was supposed to be to mention to others finding this thread that there's currently a bug where raw_app_meta_data is actually added to the row after it's created, unlike with raw_user_meta_data. Therefore, if you're trying to pass app metadata (like my custom_id property) added during creation to a trigger, it won't work and you'll always get null back. It appears this is being tracked here; I just wanted to mention it here for others since it's more likely they'll stumble on this thread than that one if they aren't specifically searching for it. Thanks for the helpful debugging tips for the future though ♥