Error: self signed certificate in certificate chain when connecting to TLS enabled Postgres instance with sslmode=prefer

[NiklasRosenstein]

The bug

This is a follow up to #902. I'm connecting to a Postgres database that uses TLS. I don't want to verify the CA, but I want to ensure that Immich connects to the Postgres cluster with SSL.

The DB_URL I pass is postgresql://immich:password@postgres-main-primary.default.svc:5432/immich?sslmode=prefer. According to the Postgres documentation, prefer means that SSL will be used if available, but the CA will not be verified. Yet, Immich fails to start with the following error:

[Nest] 1  - 07/06/2023, 11:42:05 PM   ERROR [ExceptionHandler] self signed certificate in certificate chain
Error: self signed certificate in certificate chain
    at TLSSocket.onConnectSecure (node:_tls_wrap:1532:34)
    at TLSSocket.emit (node:events:527:28)
    at TLSSocket._finishInit (node:_tls_wrap:946:8)
    at TLSWrap.ssl.onhandshakedone (node:_tls_wrap:727:12)

The expected behaviour would be to use SSL but ignore the fact that the certificate is not trusted.

The OS that Immich Server is running on

Kubernetes, Ubuntu 22

Version of Immich Server

v1.53.0 v1.66.1

Version of Immich Mobile App

n/a

Platform with the issue

• Server
• Web
• Mobile

Your docker-compose.yml content

Using https://github.com/immich-app/immich-charts/ v0.1.2

Your .env content

See `DB_URL` above

Reproduction steps

1. Set up Postgres cluster with TLS with self-signed certificate
2. Try to connect Immich to it

Additional information

No response

[NiklasRosenstein]

I realized there's a comment about "not requiring a CA certificate" in #902, but I can't figure out how I would go about configuring this.

This should be enough to allow SSL connections without requiring a CA certificate:

  ssl: {
    require: true,
    rejectUnauthorized: false
  }

[someonewating]

Hi @NiklasRosenstein . Can you share how do you passed DB_URL to the container?

[NiklasRosenstein]

Hey @someonewating, what do you mean by "how"? As an environment variable to the Pod in Kubernetes is the first reasonable answer that comes to mind. 🙂

[someonewating]

Hi @NiklasRosenstein , I already solved the problem. I ask so because I was careless, I didn't notice the existing of DB_URL variable. Now everything is fine. Thanks.😀

[MichaelMitchellM]

I'm also running into this problem and I haven't found a way to make it work.
My postgres instance is created by the postgres-operator which creates a self-signed cert. I wonder if TypeORM is overriding the SSL preference supplied in the connection url?

[someonewating]

Hi @MichaelMitchellM , I solved the problem. Check this link https://documentation.immich.app/docs/install/environment-variables#database.
For your situation, I think you need to find a method to put your certificate into the container. For example, you can rebuild the image based on the Dockerfile.

[MichaelMitchellM]

Why does DB_URL with ?sslmode=prefer still require the CA be verified? I really don't want to try and extract the CA from postgres and insert it into immich when sslmode=prefer is supposed to work.

[jrasm91]

If you want to accept self signed certificate, use sslmode=no-verify

[NiklasRosenstein]

@jrasm91 sslmode=no-verify is not a standard PSQL option, there is only disable, allow, prefer, require, verify-ca, verify-full (see https://www.postgresql.org/docs/current/libpq-ssl.html#LIBPQ-SSL-SSLMODE-STATEMENTS).

[NiklasRosenstein]

But for some reason it works 👀

env:
  DB_URL: postgresql://${db_user}:${db_password}@${db_host}:${db_port}/${db_name}?sslmode=no-verify

[jrasm91]

We use this library which accepts that option and skips verification.

https://github.com/brianc/node-postgres/blob/master/packages/pg-connection-string

https://github.com/brianc/node-postgres/blob/master/packages/pg-connection-string/index.js#L102