When trying to use multischema with Prisma and Supabase, can't migrate

Question

I'm learning to use Supabase w/ Postgres in Supabase with the following schema:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
  schemas   = ["public", "auth"]
}

enum MedicalCategory {
  GENERAL
  DENTIST
  DERMATOLOGIST
  GYNECOLOGIST
  PEDIATRICIAN
  PSYCHIATRIST
  SURGEON
  UROLOGIST
  OTHER

  @@schema("public")
}

model patients {
  id         String  @id @db.Uuid
  first_name String?
  last_name  String?
  email      String?
  users      users   @relation(fields: [id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@schema("public")
}

When trying to push this, I get the following error:

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
db error: ERROR: cannot drop table auth.users because other objects depend on it
DETAIL: constraint buckets_owner_fkey on table storage.buckets depends on table auth.users
constraint objects_owner_fkey on table storage.objects depends on table auth.users
HINT: Use DROP ... CASCADE to drop the dependent objects too.
   0: sql_schema_connector::best_effort_reset
           with namespaces=Some(Namespaces("public", ["auth"]))
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:341
   1: schema_core::state::Reset
             at schema-engine/core/src/state.rs:433

Obviously I can't drop the auth.users (for fun, I tried pulling the db schema, dropping the users table, rebuild the table using the prisma schema - didn't work), so what's the issue?

TIA!

Muhammad Afaq Nasir · Answer 1 · 2023-06-05T18:58:16.737

Error: P3016

I encountered the same error in my production environment after struggling for two days. Eventually, I discovered that the error was caused by a change in the login user of PostgreSQL. To resolve this, I performed the following steps:

Dumped the database data. Changed the owner of the database. Restored the database with the same owner that was used to create the table. These actions resolved the issue and the error no longer occurred.

Because in PostgreSQL, the concept of table ownership is crucial for managing permissions and access control. The table owner has certain privileges and rights related to the table, such as the ability to modify the table structure, insert data, or delete records.

By default, the user who creates a table becomes its owner. The owner has full control over the table and can grant or revoke permissions to other users. However, if the ownership of a table is changed to a different user, it can potentially lead to issues like the error you encountered (Error: P3016).

In your case, when you changed the login user of PostgreSQL from the database owner, it caused a conflict with the ownership of the existing tables. Dumping the database data, changing the owner, and restoring the database with the same owner resolved this conflict, ensuring consistency between the table ownership and the login user.

It's important to note that PostgreSQL's access control system allows for granular control over permissions, and table ownership is just one aspect of it. By carefully managing table ownership and granting appropriate permissions to users, you can maintain a secure and well-structured database environment.

When trying to use multischema with Prisma and Supabase, can't migrate

1 Answers1