[BUG] Postgres SSL connection complains about /root/.postgresql

[mmomjian]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

I am using version 0.15.5 of both the LSIO and the SpeedtestTracker docker images. When using the LSIO Docker container, I get the following error:

[2024-02-19 17:37:16] production.ERROR: SQLSTATE[08006] [7] connection to server at "postgres.domain" (100.64.x.x), port 5430 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied

The directory /root/.postgresql doesn't exist in either Docker image as far as I can tell. In the original Docker image, it connects fine. I think somewhere along the line sslmode is changing from prefer to verify-ca. link

Expected Behavior

The Docker container should establish an SSL connection even without certificate files - most people use SSL for encryption not authentication in the setting of a Docker container / server.

Steps To Reproduce

1. Establish a Postgres database mode with required SSL connection
2. Try to connect to it using the LSIO Docker image
3. I tried DB_SSLMODE=require and DB_SSLMODE=no-verify, the same issue persists

Environment

- OS: Debian 12
- How docker service was installed: official repos

CPU architecture

x86-64

Docker creation

speedtest-tracker:
        container_name: speedtest-tracker
        hostname: speedtest-tracker
        ports:
            - '15781:443'
        networks:
            docknet_teal:
                ipv4_address: 172.28.0.81
        environment:
            TZ: ${tz}
            PUID: '17683'
            PGID: '17683'
            DB_CONNECTION: 'pgsql'
            DB_HOST: ${pghost2}
            DB_PORT: ${pgport}
            DB_DATABASE: ${pgspeeddb}
            DB_USERNAME: ${pgspeeduser}
            DB_PASSWORD: ${pgspeedpw}
            FORCE_HTTPS: 'true'
        volumes:
            - '${dockerdata}/speedtest-tracker:/config'
            - '${ssl}/fullchain.pem:/etc/ssl/web/ssl.crt:ro'
            - '${ssl}/privkey.pem:/etc/ssl/web/ssl.key:ro'
#        image: 'ghcr.io/alexjustesen/speedtest-tracker:v0.15.5'
        image: lscr.io/linuxserver/speedtest-tracker:v0.15.5-ls4

Container logs

[2024-02-19 17:44:33] production.ERROR: SQLSTATE[08006] [7] connection to server at "postgres.host" (100.64.x.x), port 5430 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied
connection to server at "postgres.host" (100.64.x.x), port 5430 failed: FATAL:  no pg_hba.conf entry for host "172.28.0.81", user "speedtesttracker", database "speedtesttracker", no encryption (Connection: pgsql, SQL: select * from information_schema.tables where table_catalog = speedtesttracker and table_schema = public and table_name = migrations and table_type = 'BASE TABLE') {"exception":"[object] (Illuminate\\Database\\QueryException(code: 7): SQLSTATE[08006] [7] connection to server at \"postgres.host\" (100.6.x.x), port 5430 failed: could not open certificate file \"/root/.postgresql/postgresql.crt\": Permission denied
connection to server at \"postgres.host\" (100.64.x.x), port 5430 failed: FATAL:  no pg_hba.conf entry for host \"172.28.0.81\", user \"speedtesttracker\", database \"speedtesttracker\", no encryption (Connection: pgsql, SQL: select * from information_schema.tables where table_catalog = speedtesttracker and table_schema = public and table_name = migrations and table_type = 'BASE TABLE') at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:829)
[stacktrace]
#0 /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(783): Illuminate\\Database\\Connection->runQueryCallback()
#1 /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(414): Illuminate\\Database\\Connection->run()
#2 /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(401): Illuminate\\Database\\Connection->select()
#3 /app/www/vendor/laravel/framework/src/Illuminate/Database/Schema/PostgresBuilder.php(51): Illuminate\\Database\\Connection->selectFromWriteConnection()
#4 /app/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/DatabaseMigrationRepository.php(184): Illuminate\\Database\\Schema\\PostgresBuilder->hasTable()

[github-actions]

Thanks for opening your first issue here! Be sure to follow the relevant issue templates, or risk having this issue marked as invalid.

[thespad]

Can you try setting an environment variable of PGSSLMODE=prefer and see if that makes a difference? I'm curious as to where it's picking up the setting from as we don't touch it and the default in the app code is prefer.

[mmomjian]

@thespad That didn't help. I tried changing PGID and PUID to 0 and now I get this error. It seems that it gets past the DB-setup point. My guess is that the issue is not necessarily that it is trying to use a cert when connecting, and more that it is encountering a fatal error when trying to read the /root/.postgresql file during bootstrap.

User UID:    0
User GID:    0
───────────────────────────────────────

using keys found in /config/keys
Waiting for DB to be available
[custom-init] No custom files found, skipping...
[ls.io-init] done.
[19-Feb-2024 13:03:17] ERROR: [pool www] please specify user and group other than root
[19-Feb-2024 13:03:17] ERROR: FPM initialization failed

[thespad]

In which case can you try setting HOME=/config and see if that makes a difference?

You'll have to revert the PUID/PGID as the way we set things up nginx won't allow workers to run as root.

[mmomjian]

Setting HOME with the original PGID/PUID results in:

───────────────────────────────────────
GID/UID
───────────────────────────────────────

User UID:    17683
User GID:    17683
───────────────────────────────────────

using keys found in /config/keys
Waiting for DB to be available
[custom-init] No custom files found, skipping...
[ls.io-init] done.

It just hangs there, for a few minutes, never gets past that point.

[thespad]

Well, it won't "get further" per se, the [ls.io-init] done. indicates that init has completed. e.g.

speedtest-tracker  | ───────────────────────────────────────
speedtest-tracker  | GID/UID
speedtest-tracker  | ───────────────────────────────────────
speedtest-tracker  | 
speedtest-tracker  | User UID:    1000
speedtest-tracker  | User GID:    1000
speedtest-tracker  | ───────────────────────────────────────
speedtest-tracker  | 
speedtest-tracker  | using keys found in /config/keys
speedtest-tracker  | Waiting for DB to be available
speedtest-tracker  | [custom-init] No custom files found, skipping...
speedtest-tracker  | [ls.io-init] done.

You'll only see further output if something causes the app to write to stdout/stderr

[mmomjian]

@thespad OK, thanks for the help. In that case, HOME=/config fixes the Postgres problem.

The reason I thought it was still broken was that I was getting a 500 error from my reverse proxy, so I thought it was still dead. I am using the volume mount /path/to/directory/web:/etc/ssl/web in the original Docker container, and it seems that this (possibly) due to a change in the webserver, these SSL certs are no longer used with the LSIO image? For now it is working if I disable SSL verification on my reverse proxy but is there a new location to which I can mount my own SSL certs? Thanks much.

[thespad]

Yeah the certs have moved becasue of how we rig nginx - /config/keys

I'll put together a PR to change the default HOME location to /config as it won't cause any issues for people not using TLS with postgres.

[mmomjian]

Excellent, thank you. I moved the bind-mount to /config/keys and that fixed the SSL problem. Much appreciated.