Nodejs Prisma Connect Db in Sslmode

建议点击 查看原文 查看最新内容。

原文链接: https://typonotes.com/posts/2024/07/19/nodejs-prisma-connect-db-in-sslmode/

Prisma connect Heroku Database with SSL

PrivateLink: https://devcenter.heroku.com/articles/heroku-postgres-via-privatelink
External Access with Certs and Key

0. Pre-Test

0.1. Test the Certificate

(1) Using TablePlus to test the SSL Certificates.

It works.

(2) Using psql command to test

1
psql "postgres://${UserName}:${DumpPass123}@{DB_HOST}:5432/{DB_Name}?sslmode=require&sslrootcert=root.crt&sslkey=postgresql.key&sslcert=postgresql.crt"

0.2. Check the Prisma Docs

From the Prisma’s docs - Configuring an SSL Connection , it supports PCKS12 only.

sslmode=(disable|prefer|require)
sslcert=/some/path/ca.pem
sslidentity=/some/path/cert.p12
sslpassword={dump_pass} : provide while generating the cert.p12
sslaccept=(strict|accept_invalid_certs) : accept_invalid_certs is required if using the self-signed certficate.

1. download the ssl key and crts

download the certificates files
add your ip into whitelist

2. convert crt to pem

https://stackoverflow.com/questions/4691699/how-to-convert-crt-to-pem

openssl x509 -in postgresql.crt -out postgresql.crt.pem -outform PEM

3. convert crt and pem to p12

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

openssl-p12.sh

openssl pkcs12 -export -out postgresql.p12 -inkey ./postgresql.key -in ./postgresql.crt.pem

4. connect to db

1
DATABASE_URL="postgres://{UserName}:{Password}@{your-db-host.example.com}:5432/{db_name}?sslmode=require&sslidentity=/app/bundle/postgresql.p12&sslpassword=dumypass&sslcert=/app/bundle/postgresql.crt.pem&sslaccept=accept_invalid_certs"

if use sslaccept=strict, you’ll get the following errors, cause they’re self-signed certificate

PrismaClientInitializationError: Error opening a TLS connection: error:0A000086:SSL routines:tls_post_process_server_certificate:certificate verify failed:../deps/openssl/openssl/ssl/statem/statem_clnt.c:1897: (self-signed certificate in certificate chain)

4.1. (Error) don’t support MAC

when the app is runnig in MacOS(Apple M1 Chip), you’ll get the following error. It may be caused by lacking some libraries.

PrismaClientInitializationError: Error opening a TLS connection: MAC verification failed during PKCS12 import (wrong password?)

4.1. (Solution) Try to run it in Linux.

run it in linux container. like below.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
version: '3.1'

services:
  web:
    network_mode: host
    image: node:20.14.0-alpine
    command:
      - tail
      - -f
      - /dev/null

    restart: always
    environment:
      - NGINX_HOST=foobar.com
      - NGINX_PORT=80
    volumes: 
      - ./:/app
    # ports:
    #   - 20080:80

文章目录

Nodejs Prisma Connect DB in Sslmode