https://supabase.com/ logo
Join Discord
Powered by
I tried that too but when I make the id field of m...
# sql
e
I tried that too but when I make the id field of my public users a foreign key I cannot delete the auth.user anymore
g
You actually have to alter the table to allow the cascade on delete.
Copy code
ALTER TABLE public.scores
DROP CONSTRAINT scores_gid_fkey,
ADD CONSTRAINT scores_gid_fkey
   FOREIGN KEY (gid)
   REFERENCES games(gid)
   ON DELETE CASCADE;
The foreign key would be from your public.users to auth.users. Auth.users would not have a foreign key.
e
That would work if I would delete the public.users record, right? Then it would do a cascade delete on auth.users
But I'm deleting the auth.users record and I want the publuc.users record to follow.
g
Yes. I think (but maybe confused without checking) that the fk from public.users has the delete cascade on it. Then when you delete auth.users row the fk's pointing to it also get deleted.
e
I'll give it it a try. Not a big fan of how they implemented this auth.users thing.
g
I don't actually have my public users set up that way. I have a trigger on auth.users delete like you and then mark the public user row as guest and clear private info where needed, but leave the public.users row so posts associated with it stay. But I have a somewhat community app and the posts are public info, just have the author change from the real one to fake if they go away.
e
For me the reason to have a public.users table is because I have an non public app where only an admin can create (and delete) users/logins.
But you cannot query the auth.users app but I need to do that in my app. So I have no choice than to create an extra users table that I can query the way I want.
*cannot query the auth.users table
Anyway, thanks for your help
g
You can query the auth.users with an rpc call and a security definer function if needed.
s
Hi Gary, I thought I would ask you about security definers
I have a public.list_admins rpc function that does some lookup work in the auth.users table
I set public.list_admins with security definer so that it can access auth.users
But I don’t want this function to be accessible by anyone, except by a server which uses the service_key
What would be your recommendation to achieve this ?
I did revoke all from authenticated and anon to the function
But I am not sure this is safe, should I also modify ownership ? Such as alter owner to service_role ?
It’s not quite clear in my mind who the owner should be. Sounds like either service_role or supabase_auth_admin, but also I do believe that if I create the function using the UI, ownership will be given to supabase_admin while if I create it via a migration script in the CLI, it will be given to postgres
What’s your view on this ?
g
@sraffray I would think removing "execution" grant to the users would work (I don't mess with them much) but you should be able to test that quickly. I would not mess with ownership leave at supabase_admin, postgres is probably not enough to access auth schema (but I'm not sure). I just check auth.role() in my function and return null if not a valid role for use. I also tend to put my security definer function in a different schema when I can. In this case that is of little value as you need to call it thru the API.
s
Ok thanks so revoke execution from both anon and authenticated would be good enough, and then alter owner to supabase_admin. Will run some quick tests and confirm here
Not sure who the default owner of the public schema is though, is it supabase_admin ? and in that case does ownership trickles down to the rpc function by default ? In which case I don’t need to alter owner on the function i suppose ?
g
If you create the function in the sql editor or the function tab in the dashboard it is supabase_admin creating/owner.
PreviousNext
Powered by