Support custom SSL certificates in the connection string

[aputinski]

I read through several issues referencing SSL (#134, #109), and they seem to indicate it was implemented, but even after specifying all the correct ssl related query parameters in my connection string (which I have verified connects using psql, I get the error: FATAL: connection requires a valid client certificate.

I'm using prisma2@2.0.0-preview-11

[emolr]

@aputinski
Maybe i'm asking the wrong question, but are you trying to connect via the standalone Prisma Studio application?
I'm having issues connecting through that.

But i'm able to connect to my Heroku posgres database using the ?sslmode=prefer in my datasource url without any issues in the schema.prisma file.

[pantharshit00]

@aputinski Can you please share your connection string? Please redact any sensitive information in that and share that with us.

@emolr Standalone studio still requires much work right now. Please report any issues that you find here: https://github.com/prisma/studio

[aputinski]

@pantharshit00 postgres://postgres:<PASSWORD>@<HOST>?sslmode=verify-ca&sslcert=<CLIENT_CERT_PATH>&sslkey=<CLIENT_KEY_PATH>&sslrootcert=<ROOT_CERT_PATH>

[pantharshit00]

We don't support sslrootcert and sslcert parameters. The parameters that we support can be found here: https://github.com/prisma/prisma-query/blob/87ebf0d09c086d7b0a1fbc6141decc75fadb97e1/src/connector/postgres.rs#L95

I am marking this as a feature request with a change in the title.

[aputinski]

Thanks for the update. Really enjoying prisma2! This is blocker for using Google Cloud SQL on Zeit because I can't whitelist IPs and I can't use the Google Cloud SQL Proxy.

[malekjaroslav]

Related: #525

[pimeys]

It's not super simple to implement, some concerns:

• In which format the certificate is? There are quite many of them and sometimes we should convert between them to get the connector to work.
• The TLS connector supports pem certs quite easily.
• Needs to be tested with a database requiring a custom certificate.

2-3 days for work, one for testing.

[janpio]

@aputinski Can you provide some more information re the questions @pimeys posted? Especially Google Cloud SQL on Zeit would probably be interesting.

[aputinski]

@pimeys @janpio Looks like Google Cloud SQL uses PEM certificates.

[pimeys]

There are some limitations what we can do if we don't want to statically link to OpenSSL.

One way is to download the root cert, client cert and client key from Google, and convert the client cert and key into a pkcs12 database, protected with a password. Doable with openssl command line client or what I heard also with OSX system tools.

On Linux it should require the user to do:

openssl pkcs12 -export -out client-identity.p12 -inkey client-key.pem -in client-cert.pem

Then the connection string could look like this:

postgres://postgres:<PASSWORD>@<HOST>?sslmode=verify-ca&sslidentity=<PKCS12_PATH>&sslpassword=<PKCS12_PASSWORD>&sslrootcert=<ROOT_CERT_PATH>

This requires product input and writing instructions for all supported platforms.

[pimeys]

The SSL params required in this feature:

• sslmode=(disable|prefer|require): Disable: do not use TLS. Prefer: prefer TLS if possible, accept plain text connections. Require: require TLS or fail if not possible. Default: prefer
• sslcert=<PATH>: path the the server certificate, in Google's case this is the server-ca.pem.
• sslidentity=<PATH>: path to the PKCS12 certificate database created from client cert and key.
• sslpassword=<PASSWORD>: the PKCS12 password.
• sslaccept=(strict|accept_invalid_certs): If set to strict, any missing value in the certificate will lead to an error. In Google's case, especially if the database doesn't have a domain name, the certificate might miss the domain/IP address, causing an error when connecting. Set this value to accept_invalid_certs to bypass this check. Please be aware of the security consequences of this setting.

[pimeys]

The corresponding commit in prisma-query: prisma/quaint@b0607ce.

The corresponding commit in prisma-engine: prisma/prisma-engines@2b78c35

This will be now available in the next alpha, or if interested in testing right now, by pulling and compiling the prisma-engine manually.

Tested with Google SQL, disabling access without a certificate.