External Postgresql with SSL does not connect properly

[tka85]

In a docker-compose I have these env vars setup (db string values are confirmed to be correct with psql connecting properly using them on CLI):

- BASE_URL=https://example.com
- TRUST_PROXY=0
- DATABASE_URL=postgresql://${PLANKA_DB_USER}:${PLANKA_DB_PASS}@${PLANKA_DB_HOST}:${PLANKA_DB_PORT}/${PLANKA_DB_NAME}?ssl=true&sslmode=require
- SECRET_KEY=${PLANKA_DB_PASS}

My SSL cert is from letsencrypt. Not self-signed. It is mounted correctly into pg container and I have another webapp that is connecting to this postgresql over SSL correctly.

On postgresql (v14) side I see logs:

2023-08-18 05:42:49.680 UTC [242]: [2-1] user=planka,db=planka_db FATAL:  pg_hba.conf rejects connection for host "XXX.XXX.XXX.XXX", user "planka", database "planka_db", no encryption
2023-08-18 05:42:56.256 UTC [243]: [1-1] user=[unknown],db=[unknown] LOG:  connection received: host=XXX.XXX.XXX.XXX port=56372
2023-08-18 05:42:57.914 UTC [243]: [2-1] user=planka,db=planka_db LOG:  connection authenticated: identity="planka" method=scram-sha-256 (/xxx/xxx//pg_hba.conf:100)
2023-08-18 05:42:57.915 UTC [243]: [3-1] user=planka,db=planka_db LOG:  connection authorized: user=planka database=planka_db SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)

And on Planka side:

kanbanana    | debug: Automatically setting the NODE_ENV environment variable to "production".
kanbanana    | debug: 
kanbanana    | 2023-08-18 05:49:15 [E] A hook (`orm`) failed to load!
kanbanana    | 2023-08-18 05:49:15 [E] Failed to lift app: `getConnection` failed ("failed").  Could not acquire a connection to the database using the specified manager.
kanbanana    | Additional data:
kanbanana    | 
kanbanana    | 
kanbanana    | {
kanbanana    |   error: error: pg_hba.conf rejects connection for host "XXX.XXX.XXX.XXX", user "planka", database "planka_db", no encryption
kanbanana    |       at Parser.parseErrorMessage (/app/node_modules/pg-protocol/dist/parser.js:287:98)
kanbanana    |       at Parser.handlePacket (/app/node_modules/pg-protocol/dist/parser.js:126:29)
kanbanana    |       at Parser.parse (/app/node_modules/pg-protocol/dist/parser.js:39:38)
kanbanana    |       at Socket.<anonymous> (/app/node_modules/pg-protocol/dist/index.js:11:42)
kanbanana    |       at Socket.emit (node:events:513:28)
kanbanana    |       at addChunk (node:internal/streams/readable:324:12)
kanbanana    |       at readableAddChunk (node:internal/streams/readable:297:9)
kanbanana    |       at Readable.push (node:internal/streams/readable:234:10)
kanbanana    |       at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
kanbanana    |     length: 172,
kanbanana    |     severity: 'FATAL',
kanbanana    |     code: '28000',
kanbanana    |     detail: undefined,
kanbanana    |     hint: undefined,
kanbanana    |     position: undefined,
kanbanana    |     internalPosition: undefined,
kanbanana    |     internalQuery: undefined,
kanbanana    |     where: undefined,
kanbanana    |     schema: undefined,
kanbanana    |     table: undefined,
kanbanana    |     column: undefined,
kanbanana    |     dataType: undefined,
kanbanana    |     constraint: undefined,
kanbanana    |     file: 'auth.c',
kanbanana    |     line: '477',
kanbanana    |     routine: 'ClientAuthentication'
kanbanana    |   },
kanbanana    |   meta: undefined
kanbanana    | }
kanbanana    | 2023-08-18 05:49:15 [E] More details (raw):
kanbanana exited with code 0

It seems that the ?ssl=true&sslmode=require is not honored at first as Pg rejects the non-secure connection attempt. But then there is an SSL connection attempt but fails on the Planka side.

Am I missing something?

[tka85]

I switched the pg_hba.conf entry from scram-sha-256 to md5 just in case planka was using some older version that couldn't do the exchanged required by the newer scram auth method, but still the same problem. Initially planka tries to connect via unencrypted connection which is immediately rejected by pg_hba.conf and then it tries SSL but it disconnects immediately.

This is te pg_hba entry:

hostssl    planka_db    planka    xxx.xxx.xxx.xxx/32    md5

The password is correct. I tried connecting from same source host to same destination host and db via psql and it connects fine.

I could really use some pointers here. Anyone else got the planka working over SSL connection to Postgres?

[meltyshev]

I checked the .env.sample file and it has PGSSLMODE and KNEX_REJECT_UNAUTHORIZED_SSL_CERTIFICATE variables (they should also be in docker-compose.yml). Have you tried to set values to them? You can also try to uncomment ssl: true in server/config/env/production.js. I haven't tested it, I thought everything should work with ?ssl=true&sslmode=require...

[tka85]

Sorry, can no longer check this and don't want to hold the issue open if no one else is interested. Moved on to another solution.