Migrate: Connection Failure on Digital Ocean Managed Database

[IanMitchell]

Bug description

I'm running into problems connecting to a new Digital Ocean PostgreSQL managed database. I'm seeing the following errors:

❯ DEBUG=* DATABASE_URL="<str>" npx prisma migrate up --experimental
Environment variables loaded from prisma/.env
Error: Error: undefined: Unknown error occured: Failure during a migration command: Connector error. (error: Error querying the database: Error querying the database: db error: ERROR: permission denied for database <dbname>

I deleted the database to try and go through the prompt to create it, and that gave some additional output:

A database with that name doesn't exist at <str>
 › Yes
Error: Error: Command failed with exit code 255: /node_modules/@prisma/cli/migration-engine-darwin cli --datasource <str>
Aug 30 10:52:07.140  INFO migration_engine: Starting migration engine CLI 
Aug 30 10:52:07.447 ERROR migration_engine::commands: Error querying the database: Error querying the database: db error: FATAL: pg_hba.conf rejects connection for host "<my ip>", user "<user>", database "template1", SSL on

{"is_panic":false,"message":"Error querying the database: Error querying the database: db error: FATAL: pg_hba.conf rejects connection for host \"<ip>\", user \"<user>\", database \"template1\", SSL on\n","backtrace":null}
    at o (node_modules/@prisma/cli/build/index.js:2:1286428)

My database is configured to allow all sources; however, template1 seems to be a system table that I don't have the ability to modify or access. I'm able to connect to the database I created using psql but I get the same connection error when I try to access the template1 table. I get an Unspecified GSS failure (not quite sure what this means, sorry!)

How to reproduce

1. Create a managed PostgreSQL instance on Digital Ocean
2. Edit trusted sources to allow all connections
3. Run DATABASE_URL="<str>" npx prisma migrate up --experimental using their provided connection string
4. Error will print

Prisma information

I'm on version 2.5.1. Happy to provide other information if it helps

Environment & setup

macOS, Node.js 14

[pantharshit00]

Hm, I am unable to reproduce this.

Most likely you password has an unescaped character. If possible can you tell me which special characters it has or try with another password.

[IanMitchell]

The names are all [a-zA-Z] characters / numbers - there is no special characters in the name, password, host, or database name. You're able to connect to the template1 db using psql command?

[IanMitchell]

My guess is adding defaultdb to this would fix the error: https://github.com/prisma/prisma-engines/blob/a0b427d77d040aa7a76ddeedfb63e269351d63d7/migration-engine/connectors/sql-migration-connector/src/flavour.rs#L315

Digital Ocean uses that database as the default, and users are unable to delete it: https://www.digitalocean.com/docs/databases/postgresql/how-to/manage-users-and-databases/

EDIT: Just confirmed with Digital Ocean support that they do not allow access to the template1 database

[Jolg42]

Thanks for the extra details! 💚

[albertoperdomo]

@pantharshit00 Can you please try reproducing it with DO again, please?
What's written down does make sense, but it's concerning that we can't repro.

[IanMitchell]

Is there a guide somewhere on compiling the engine and using it in an existing Node project? Trying to write this patch myself but having problems getting the engine to work with my local project

[janpio]

https://github.com/prisma/prisma-engines#building-prisma-engines @IanMitchell

[albertoperdomo]

@pantharshit00 you have not been able to repro. Can you please try again? Thanks so much!

[pantharshit00]

Hey @albertoperdomo

I was ignoring migrate repository due to internal prioritisation( I think you are already aware).

But I will investigate this one now though :)

[pantharshit00]

I am still unable to reproduce this. The migration went through pretty easily.

Are you using another user instead of default user which DO provided?

Prisma migrate is getting a bit of overhaul so the code that you pointed out might not be actually used in the current version cc @tomhoule

[IanMitchell]

Updating to v2.8.0 seemed to fix it - I still am unable to create a new database with the command (it fails if you replace defaultdb with a new db) but if the database exists it's able to run the migration now! Will close the ticket out, thanks

[janpio]

@IanMitchell If you still have _some_ problem, feel free to open a new issue for that with just a description of it - maybe we can do something about it or at least show a better error message with instructions.