Postgres Unit Socket Support

[98h398hrpohpoai]

Is it possible to add support for postgres unix sockets?
They generally tend to provide better security and performance.

It appears it was previously possible with #1557, however, this workaround no longer works as sequelize produces errors without a DATABASE_URL variable, and does not pass the socket through properly with it.

Passing through a socket via something like postgres://outline:password@/outline?host=/run/postgresql treats it as a tcp connection with the following error despite being supported syntax:
ERROR: connect ECONNREFUSED 127.0.0.1:5432

Edit: This is addressed more thoroughly below, but the error with my attempt above is not specifying the host and port despite providing the socket config.
Specifying these variables alters how the postgres connector searches for the socket in the case of non-standard configuration i.e. anything other than localhost:5432, which would be the container itself.

[tommoor]

Open to a PR to add this functionality

[98h398hrpohpoai]

My understanding of a PR is it already has to have completed functionality, no?
I've been trying to correct myself but haven't been able to do so.
I'm unclear why the DATABASE_URL doesn't parse the unix socket connection URI and instead reverts to a default tcp connection config.

[tommoor]

The DATABASE_URL is passed directly through to the pg library so I would expect anything that works there to also work. This is not a common setup, it's come up twice in the history of the project so unfortunately I don't have more knowledge to provide.

[98h398hrpohpoai]

As a follow-up, I've been playing with this a bit more and the following URI appears to work:
DATABASE_URL=postgresql://[pg_user]:[pg_pass]@[pg_host]:[pg_port]/[outlinedb]?host=[socket_location]

A fully functional example might look like:
DATABASE_URL=postgresql://outline:password@localhost/outlinedb?host=%2Fvar%2Frun%2Fpostgresql
OR
DATABASE_URL=postgresql://outline:password@172.17.0.1:5678/outlinedb?host=%2Ftmp%2Fpostgresql

• If using the standard port (5432), you should be able to omit pg_port and just write @[pg_host]/[outlinedb] with host being something like localhost or 172.17.0.1 if passing through a docker bridge.
• socket_location may require the forward slashes to have special formatting due to how they're interpreted e.g. host=%2Fvar%2Frun%2Fpostgresql
• No need to specify the socket file directly as the socket matching the port configuration will automatically attempt to load e.g: .s.PGSQL.5432
• Though implied, it's worth noting you will need to passthrough the postgres socket directory to the outline docker volume, e.g.
  -v /var/run/postgresql:/var/run/postgresql
  or something similar.
• If you have a non-standard port/socket combination e.g. /var/run/postgresql/.s.PGSQL.5678, then make sure to include pg_port as postgres will only try to load .s.PGSQL.5678 (or whatever you set) and fail otherwise.
• I don't use localhost so I can't speak directly to it, though see here for discussion troubleshooting issues with it: Can't connect to postgres database (same host) #5411

For completeness' sake, a redis socket can be implmented in a similar fashion using the following context:
REDIS_URL=unix:///run/redis/redis-server.sock