Error: "permission denied for schema public" when trying to use a trigger function on a table created by Prisma

[lumenwrites]

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?

Full log:

{
  "file": "/var/log/postgresql/postgresql.csv",
  "host": "db-fnyscckcxemaipmzxyna",
  "parsed": [
    {
      "application_name": null,
      "backend_type": "client backend",
      "command_tag": "INSERT",
      "connection_from": "10.109.146.15:36245",
      "context": "SQL statement \"insert into public.profile(id, email)\n  values(new.id, new.email)\"\nPL/pgSQL function public.create_profile_for_user() line 2 at SQL statement",
      "database_name": "postgres",
      "detail": null,
      "error_severity": "ERROR",
      "hint": null,
      "internal_query": null,
      "internal_query_pos": null,
      "leader_pid": null,
      "location": null,
      "process_id": 174801,
      "query": "INSERT INTO \"users\" (\"aud\", \"banned_until\", \"confirmation_sent_at\", \"confirmation_token\", \"created_at\", \"email\", \"email_change\", \"email_change_confirm_status\", \"email_change_sent_at\", \"email_change_token_current\", \"email_change_token_new\", \"email_confirmed_at\", \"encrypted_password\", \"id\", \"instance_id\", \"invited_at\", \"is_super_admin\", \"last_sign_in_at\", \"phone\", \"phone_change\", \"phone_change_sent_at\", \"phone_change_token\", \"phone_confirmed_at\", \"raw_app_meta_data\", \"raw_user_meta_data\", \"reauthentication_sent_at\", \"reauthentication_token\", \"recovery_sent_at\", \"recovery_token\", \"role\", \"updated_at\") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31)",
      "query_id": -3536511579343214000,
      "query_pos": null,
      "session_id": "6296afae.2aad1",
      "session_line_num": 119,
      "session_start_time": "2022-06-01 00:15:42 UTC",
      "sql_state_code": "42501",
      "timestamp": "2022-06-01 00:38:38.498 UTC",
      "transaction_id": 1740,
      "user_name": "supabase_auth_admin",
      "virtual_transaction_id": "5/12728441"
    }
  ],
  "parsed_from": null,
  "project": "fnyscckcxemaipmzxyna",
  "source_type": "file"
}

[GaryAustin1]

I don't know much about Prisma, but when Supabase dashboard creates a table it does it as user supabase_admin. Prisma is user postgres.
Then this code applies to tables create by supabase_admin:

-- These are required so that the users receive grants whenever "supabase_admin" creates tables/function
alter default privileges for user supabase_admin in schema public grant all
    on sequences to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema public grant all
    on tables to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema public grant all
    on functions to postgres, anon, authenticated, service_role;

So my guess is your table is missing a grant that anon/authenticated/service_role need to access your table.

[lumenwrites]

Thank you! Not sure if it was the issue, but I just recreated the whole project, remigrated the database, resetup the function and the trigger, and double checked that the function is created as a security definer. Now it works.

[lumenwrites]

Recreated the whole project, remigrated the database, resetup the function and the trigger, and double checked that the function is created as a security definer. Now it works.

[GCinellu]

I had the same issue and I found out that the issue was given by creating the database with the postgres user.

Ideally, you want to give createdb privileges to the user and let it create the database, instead of using the postgres user to create both, and than manually assigning privileges.

If you try running \l in the psql console, you might see in the last column that postgres is listed as owner.

If this is the case (as it was in my instance), the solution is to access the psql cli and run the following command:

ALTER DATABASE <database_name> OWNER TO <your_application_user>;

[LukerSpringtree]

How I can find the <your_application_user> in supabase?
<database_name> is the project name?

[remusris]

Had a similar bug,

I was able to fix it with this

GRANT USAGE ON SCHEMA public TO anon, authenticated, service_role;
GRANT ALL ON ALL TABLES IN SCHEMA public TO anon, authenticated, service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA public TO anon, authenticated, service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON ROUTINES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;

from here

https://supabase.com/docs/guides/api/using-custom-schemas