Postgres socket setup

[ambroisie]

Hello 👋,

I want to use the newly added PostgreSQL backend instead of SQLite, however I'm having an issue deploying it in my setup.

My configuration sets the following values:

HBOX_DATABASE_DRIVER = "postgres"
HBOX_DATABASE_HOST = "/run/postgresql"
HBOX_DATABASE_USERNAME = "homebox"
HBOX_DATABASE_DATABASE = "homebox"

This is similar to how I configure other services on my server to use a UNIX socket connection to their DB, and it works fine with those.

However Homebox is failing to connect to said socket, and I suspect the code to setup databaseURL to be at fault:

Apr 06 15:58:39 porthos systemd[1]: Started homebox.service.
Apr 06 15:58:39 porthos api[1941006]: 3:58PM ERR github.com/sysadminsmedia/homebox/backend/app/api/main.go:163 > failed creating schema resources error="querying server version dial unix /run/postgresql/.s.PGSQL.user=homebox: socket: address family not supported by protocol" driver=postgres url="host=/run/postgresql port= user=homebox password= dbname=homebox sslmode="
Apr 06 15:58:39 porthos api[1941006]: panic: querying server version dial unix /run/postgresql/.s.PGSQL.user=homebox: socket: address family not supported by protocol
Apr 06 15:58:39 porthos api[1941006]: goroutine 1 [running]:
Apr 06 15:58:39 porthos api[1941006]: main.main()
Apr 06 15:58:39 porthos api[1941006]:         github.com/sysadminsmedia/homebox/backend/app/api/main.go:84 +0x70

Notice that it mentions /run/postgresql/.s.PGSQL.user=homebox, rather than /run/postgresql (or /run/postgresql/.s.PGSQL.5432).

Did I misconfigure the service, or is there an issue with the code itself?

[101br03k]

By default if port is not set it is blank, could you try setting a port using HBOX_DATABASE_PORT (default for postgres should be 5432), and mayby also try setting a ssl mode (HBOX_DATABASE_SSL_MODE)?

[101br03k]

I think I overlooked it initially, should the host not be an ip adres ?(127.0.0.1 if same host)
Let me know if that does work, otherwise I will get one of the devs here for further assistance.
Also in the case that it does not work, please provide the full docker run command / compose file.

[ambroisie]

No, this is using a UNIX socket to connect, the host is supposed to be a path on the filesystem.

See e.g: this StackOverflow answer.

[tankerkiller125]

Digging in a bit, the current connection system does not support unix sockets

[ambroisie]

Urgh, I figured out my issue was with my systemd service definition not enabling AF_UNIX (see first commit in NixOS/nixpkgs#396915).

Also, I think the following patch would make it easier to configure and rely on default values for variables which aren't set (rather than needing to set e.g: port=5432).

diff --git a/backend/app/api/main.go b/backend/app/api/main.go
index dce3ed0..1e3a9b5 100644
--- a/backend/app/api/main.go
+++ b/backend/app/api/main.go
@@ -109,7 +109,24 @@ func run(cfg *config.Config) error {
        case "sqlite3":
                databaseURL = cfg.Database.SqlitePath
        case "postgres":
-               databaseURL = fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=%s", cfg.Database.Host, cfg.Database.Port, cfg.Database.Username, cfg.Database.Password, cfg.Database.Database, cfg.Database.SslMode)
+               if cfg.Database.Host != "" {
+                       databaseURL = fmt.Sprintf("%s host=%s", databaseURL, cfg.Database.Host)
+               }
+               if cfg.Database.Port != "" {
+                       databaseURL = fmt.Sprintf("%s port=%s", databaseURL, cfg.Database.Port)
+               }
+               if cfg.Database.Username != "" {
+                       databaseURL = fmt.Sprintf("%s user=%s", databaseURL, cfg.Database.Username)
+               }
+               if cfg.Database.Password != "" {
+                       databaseURL = fmt.Sprintf("%s password=%s", databaseURL, cfg.Database.Password)
+               }
+               if cfg.Database.Database != "" {
+                       databaseURL = fmt.Sprintf("%s dbname=%s", databaseURL, cfg.Database.Database)
+               }
+               if cfg.Database.SslMode != "" {
+                       databaseURL = fmt.Sprintf("%s sslmode=%s", databaseURL, cfg.Database.SslMode)
+               }
        default:
                log.Fatal().Str("driver", cfg.Database.Driver).Msg("unsupported database driver")
        }

[tankerkiller125]

We set defaults to make life as easy as possible for those just getting started with homebox and those who maybe don't have a vast knowledge of how these kinds of things work.