https://supabase.com/ logo
#help
Title
# help
l

lumen

06/01/2022, 1:13 AM
Error: "permission denied for schema public" when trying to use a trigger function on a table created by Prisma Hi! I'm trying to use Prisma with Supabase Auth. I have defined my own public
profile
table in Prisma, where I want to store all my custom data:
Copy code
model profile {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  email     String   @unique
  stripeCustomerId String? @unique
}
Now I want to create a trigger function that runs every time the user signs up with Supabase auth. It looks like this:
Copy code
begin
  insert into public.profile(id, email)
  values(new.id, new.email);

  return new;
end;
And I set up a trigger to run it every time a user is INSERTed into
auth.users
. Now when I try to sign up with Supabase Auth, I get an error "Database error saving new user", and when I look at Postgres logs, it says "permission denied for table profile". It worked perfectly fine when I created tables manually via Supabase UI, it stopped working when I tried to create tables with Prisma. What have I done wrong? How can I fix this?
n

Needle

06/01/2022, 1:13 AM
Hello @lumen! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.
j

jaitaiwan

06/01/2022, 1:27 AM
@lumen how was the function created? Was it setup as a security definer?
n

Needle

06/01/2022, 1:27 AM
lumen (2022-06-01)
l

lumen

06/01/2022, 1:39 AM
Just recreated the whole project, remigrated the database, resetup the function and the trigger, and double checked that it is created as a security definer. Still the same error.
Wait, sorry, it was a different error this time, unrelated, now that I fixed it, everything works.
s

selique

06/02/2022, 3:44 AM
you can help i stuck in this cenario
yes have trigger i use prisma to only create migrations (i dont have key foreign to id profile its copy id from auth.users) my client trigger error: "Error: Database error saving new user"
j

jaitaiwan

06/02/2022, 9:36 AM
@selique Have you setup any functions? If so they need to be done as an admin and set as "security definer"
f

ferpintado

06/02/2022, 2:48 PM
I noticed that Prisma ocasionally resets the table permission when you run
prisma migrate
so you would need to add the permission back to the table i.e.
Copy code
ALTER TABLE IF EXISTS public.profiles
    OWNER to postgres;

GRANT ALL ON TABLE public.profiles TO authenticated;

GRANT ALL ON TABLE public.profiles TO postgres;

GRANT ALL ON TABLE public.profiles TO anon;

GRANT ALL ON TABLE public.profiles TO service_role;
4 Views