Auth / supabase_auth_admin permissions: best practice for deleting user?

[anngbaum]

Not sure if this has changed, but we're running into some unexpected behavior with the deleteUser API.

we have this set up with an edge function that authenticates the user, and then uses the service role key to all call admin.deleteUser().

the main issue is that we have a number of cascade deletes and triggers that fire after a user gets deleted, which cause delete user to fail unexpectedly.

you can reproduce this pretty trivially with:

a profiles table that has a foreign key referencing auth.users that cascade deletes

create table
  public.profiles (
    id uuid not null,
    created_at timestamp with time zone not null default now(),
    name text null,
    constraint profiles_pkey primary key (id),
    constraint profiles_id_fkey foreign key (id) references auth.users (id) on update cascade on delete cascade
  ) tablespace pg_default;

an items table with a profile_id column that references the profiles table and cascade deletes

create table
  public.items (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    profile_id uuid null default gen_random_uuid (),
    item_name text null,
    constraint items_pkey primary key (id),
    constraint items_profile_id_fkey foreign key (profile_id) references profiles (id) on update cascade on delete cascade
  ) tablespace pg_default;

a trigger on the items table that updates a total_item_count table with the number of saves per item.

 create table
  public.total_item_count (
    item text not null,
    total_saves numeric not null default '0'::numeric,
    constraint total_item_count_pkey primary key (item)
  ) tablespace pg_default;

CREATE OR REPLACE FUNCTION public.update_item_count()
RETURNS TRIGGER AS $$
DECLARE 
 updated_item text;
 new_count numeric;
BEGIN
SELECT COALESCE(NEW.item_name, OLD.item_name) INTO updated_item;

SELECT COUNT(DISTINCT items.profile_id) INTO new_count
    FROM items
    WHERE items.item_name = updated_item;

INSERT INTO total_item_count (item, total_saves)
VALUES(updated_item, new_count)
ON CONFLICT (item) DO
   UPDATE
   SET total_saves = new_count;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

create trigger update_item_count
after insert
or delete on items for each row
execute function update_item_count ();

the first error we encountered was that we actually had to specify the schema on the trigger. I think this is because the auth function is being called from the auth schema. As written above, we got an error on user deletion:
relation "items" does not exist

we were able to correct that by modifying the trigger to be FROM public.items instead of FROM items. not the biggest deal, but it took some time to debug and resolve.

the second error I'm less certain how to resolve is that even with the correction, we hit a permissions error:
permission denied for table items

this is all taking place from supabase_auth_admin.

should supabase_auth_admin have the same postgres level user permissions by default? should we go through and add RLS policies as needed to specific tables? or should we separately run everything that needs to happen in the public table schema from a different function entirely?

would love to get some suggestions here for best practices – and for quicker reference you can also reproduce this by simply deleting a user from the dashboard as it seems to use the same APIs.

[GaryAustin1]

supabase_auth_admin is a "weak" role. It does not even have RLS bypass. It is not granted to public. Which is why the user management guides all show using a security definer function for triggers.

edit: Even in auth-hooks they show having to grant it access to a table as in the case of the auth-hooks function it is not security definer:

[anngbaum]

got it, thank you!

you're right that the User Management guide does use a security definer function for triggers, which I clearly overlooked.

I think I missed that because this trigger was not on the auth.users table but rather a basic public schema table, so it worked fine for "normal" deletions/insertions (i.e. initiated by users).

perhaps this is worth a quick mention in the Postgres Triggers guide to just point out that any triggers potentially cascaded by auth.users deletes need to have:

1. schema specification for referenced tables
2. security definer permissions