[Feature]: TLS encrypted database connections

[jcollie]

Feature detail

The current server code does not allow encrypted database connections to be configured. Managed PostgreSQL databases like Digital Ocean's require TLS encryption to connect.

Oct 30 15:46:19 cloud01 immich-server[56575]: [Nest] 2  - 10/30/2022, 8:46:19 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (3)...
Oct 30 15:46:19 cloud01 immich-server[56575]: error: no pg_hba.conf entry for host "X.X.X.X", user "immich", database "immich", no encryption
Oct 30 15:46:19 cloud01 immich-server[56575]:     at Parser.parseErrorMessage (/usr/src/app/node_modules/pg-protocol/dist/parser.js:287:98)
Oct 30 15:46:19 cloud01 immich-server[56575]:     at Parser.handlePacket (/usr/src/app/node_modules/pg-protocol/dist/parser.js:126:29)
Oct 30 15:46:19 cloud01 immich-server[56575]:     at Parser.parse (/usr/src/app/node_modules/pg-protocol/dist/parser.js:39:38)
Oct 30 15:46:19 cloud01 immich-server[56575]:     at Socket.<anonymous> (/usr/src/app/node_modules/pg-protocol/dist/index.js:11:42)
Oct 30 15:46:19 cloud01 immich-server[56575]:     at Socket.emit (node:events:527:28)
Oct 30 15:46:19 cloud01 immich-server[56575]:     at addChunk (node:internal/streams/readable:315:12)
Oct 30 15:46:19 cloud01 immich-server[56575]:     at readableAddChunk (node:internal/streams/readable:289:9)
Oct 30 15:46:19 cloud01 immich-server[56575]:     at Socket.Readable.push (node:internal/streams/readable:228:10)
Oct 30 15:46:19 cloud01 immich-server[56575]:     at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Platform

Server

[bo0tzz]

Creating an environment variable to set the postgres URL parameter should allow setting this (and more) without specific support on our part. The details of the connection URL format can be seen here. In TypeORM, the options we're currently using (host, username, etc) will override what is set in the URL. We need to test whether leaving those options blank is sufficient.

[jrasm91]

Although, we might also need a way to provide the CA certificate (see here).

[bo0tzz]

Based on the link I included above, I believe the URL parser should take paths for the TLS parameters and read those to the appropriate values for us.

[jrasm91]

Based on the link I included above, I believe the URL parser should take paths for the TLS parameters and read those to the appropriate values for us.

Oh, you're right. I didn't see/know the path was being included in the url, but that would definitely work.

[bt90]

It should be enough to pass sslmode=prefer in the connection URL.

[bt90]

brianc/node-postgres#2009

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

  ssl: {
    require: true,
    rejectUnauthorized: false
  }