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:

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:

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?

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.

@lumen how was the function created? Was it setup as a security definer?

lumen (2022-06-01)

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.

you can help i stuck in this cenario

@selique Have you setup any functions? If so they need to be done as an admin and set as "security definer"

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.

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;