Dashboard errors when managing users

Last edited: 1/18/2025

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 ...

Screenshot 2024-02-13 at 10 59 17 PM

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:


_10
DROP FUNCTION <function name>() CASCADE;
_10
_10
-- If you'd prefer, you can drop the trigger alone with the following query:
_10
-- 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:


_23
create table profiles (
_23
id uuid references auth.users on delete cascade not null primary key,
_23
updated_at timestamp with time zone,
_23
username text unique,
_23
full_name text,
_23
avatar_url text,
_23
website text,
_23
_23
constraint username_length check (char_length(username) >= 3)
_23
);
_23
_23
create function public.handle_new_user()
_23
returns trigger as $$
_23
begin
_23
insert into public.profiles (id, full_name, avatar_url)
_23
values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
_23
return new;
_23
end;
_23
$$ language plpgsql security definer;
_23
_23
create trigger on_auth_user_created
_23
after insert on auth.users
_23
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.

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.