Prisma•14mo ago

SSL connection to supabase

Hello, I'm developing an application where the stack includes Prisma as an ORM and the database is hosted by supabase. The app is still in development, and I'm unsure how to make the connection between the development environment and the database be SSL encrypted. That, and how to enforce it also on a production environment.

As a disclaimer, I'm using: Prisma + Sveltekit + Supabase. I've also tried to put sslmode=require on the database string, but it fails to establish a connection

Nurul•10/23/24, 1:49 PM

Hello @Tomathy

If I understand correctly, when you added to the database connection string, you got an error. However, when you do not add parameter, you don't get any error. Correct?

Nurul•10/23/24, 1:50 PM

Can you share the error that you received?

TommathyOP•10/23/24, 1:59 PM

No sorry, Im figuring out things as I go so I'm a bit lost here

. Basically what's happening is I want to connect top my supabase database with SSL. I wasn't able to do it before, basically when i ran, say "db push" it attempted to connect to the database but would just continuously run without connecting and nothing would happen. Since then I have read the documentation and downloaded the certificate from supabase, but I'm still having difficulties figuring out what is needed and what isn't needed in the connection URL to always establish a SSL connection.

currently my string is looking like this:
postgresql://USER:PASSWORD@HOST:6543/postgres?pgbouncer=true&connection_limit=1&sslcert=../certificates/dbcert.crt
with the correct values where appropriate

Yetzederixx•10/23/24, 5:33 PM

https://www.prisma.io/docs/orm/overview/databases/postgresql#configuring-an-ssl-connection

PostgreSQL database connector | Prisma Documentation

This page explains how Prisma can connect to a PostgreSQL database using the PostgreSQL database connector.

Yetzederixx•10/23/24, 5:33 PM

make sure your cert is in the correct place is my guiss

Yetzederixx•10/23/24, 5:33 PM

guess even

Yetzederixx•10/23/24, 5:33 PM

so you may have to adjust your path

Yetzederixx•10/23/24, 5:34 PM

My recommendation is always, attempt to connect with the psql client, and in this case make sure you're in the prisma folder when you do and adjust the path until it works, then c&p that bad boy in your code

TommathyOP•10/23/24, 5:38 PM

thank you! Would i need any more arguments in the url than those? Or are those enough? Cause i see that there are options for sslidentity=<PATH> and sslpassword=<PASSWORD> and im not sure if those are required to establish a sll connection

Yetzederixx•10/23/24, 5:50 PM

No, the cert replaces the identity stuff to my knowledge

Yetzederixx•10/23/24, 5:51 PM

it's basically a ssl cert without a password like you typically do to a cloud rig for remote access

TommathyOP•10/23/24, 6:40 PM

ah fair, thought the connection used the cert as an encryption key for sending the credentials.

Yetzederixx•10/23/24, 7:41 PM

The paranoid will have a pg user/password, ssl cert with password, which is fine, but not used a lot in my experience. Frankly if someone can get the cert, they've breached enough of your system that you have to assume everything is compromised anyway. So there's more to be gained shoring up security elsewhere I guess.

TommathyOP•10/23/24, 8:26 PM

wow, thanks that makes sense. Thank you for the answers

TommathyOP•10/23/24, 8:35 PM

I put the cert in the ./prisma folder, to be as straight forward as possible "sslcert=dbcert.crt" on the URl, but all im gettingis a connection not establishing (the terminal gets stuck at this):

Yetzederixx•10/23/24, 9:50 PM

have you tried using the psql command line tool?

TommathyOP•10/24/24, 1:24 PM

i figured out the problem... I was reaching for the wrong port on supabase. Since im not in a serverless enviornment i should reach for the session mode which uses a different port than the transaction mode in supabase. I changed the port and everything worked fine -_-

Thanks for all the help anyways