1. What is the problem? Be very detailed.
In short: I need the slonik version of adding "ssl": {"rejectUnauthorized": false} to the db config.
I'm installing ODK Central 1.2 for the first time. My department requires to use a hosted database, which I access through the intranet. For reasons outside of my control, the access happens with SSL.
As per node-postgres docs on SSL, adding "ssl": {"rejectUnauthorized": false} to the database connection dict in files/service/config.json.template worked with knex, but doesn't seem to work with the new db driver slonik.
2. What app or server are you using and on what device and operating system? Include version numbers.
ODK Central master as of https://github.com/getodk/central/commit/e16b79530756bbd825f68275b3b6e53951409474
- SSL self-sign
- hosted PG database
3. What you have you tried to fix the problem?
What I haven't yet tried is using my own SSL certificates for the db, I'm waiting on our IT dept to provide me those.
My build steps:
# modify config
vim ~/central/files/service/config.json.template
vim docker-compose.yml
# build and restart docker-compose
docker-compose build && docker-compose restart
# docker-compose build && docker-compose stop && docker-compose up -d
# inspect logs
docker-compose logs -f service nginx
Attempt 1
No dice with ODK Central latest master (23 July 2021) using "database?ssl=true"
service_1 | (node:24) UnhandledPromiseRejectionWarning: error: database "DBNAME?ssl=true" does not exist
Attempt 2
Attempting ?ssl=1 as per https://github.com/gajus/slonik/issues/55#issuecomment-489398983
service_1 | (node:23) UnhandledPromiseRejectionWarning: error: database "DBNAME?ssl=1" does not exist
Attempt 3
Attempting what worked with knex before as per https://node-postgres.com/features/ssl
"database": "DBNAME",
"ssl": {"rejectUnauthorized": false}
Logs show no errors, however shell commands do:
docker-compose exec service odk-cmd --email EMAIL user-promote
ConnectionError: SSL connection is required. Please specify SSL options and retry.
at Object.createConnection (/usr/odk/node_modules/slonik/dist/src/factories/createConnection.js:54:23)
at processTicksAndRejections (internal/process/task_queues.js:85:5)
This indicates that "ssl": {"rejectUnauthorized": false} does not work any more with slonik - that's a regression for my use case.
Attempt 4
https://github.com/gajus/slonik/issues/159 refers to https://github.com/brianc/node-postgres/blob/f0bf3cda7b05be77e84c067a231bbb9db7c96c39/CHANGELOG.md#pg810
Add &ssl=no-verify option to connection string and PGSSLMODE=no-verify environment variable support for the pure JS driver. This is equivalent of passing { ssl: { rejectUnauthorized: false } } to the client/pool constructor.
With ssl=no-verify appended to the dbname, no errors in logs, but errors on shell commands:
docker-compose exec service odk-cmd --email EMAIL user-promote
ConnectionError: SSL connection is required. Please specify SSL options and retry.
at Object.createConnection (/usr/odk/node_modules/slonik/dist/src/factories/createConnection.js:54:23)
at processTicksAndRejections (internal/process/task_queues.js:85:5)
Attempt 5
Using environment variables. I assume I put PGSSLMODE=no-verify into the docker-compose file as environment variables to both service (I think that's the one that needs it) and nginx (for good measure).
Again, no errors in logs, but errors on shell commands.
docker-compose exec service odk-cmd --email EMAIL user-promote
ConnectionError: SSL connection is required. Please specify SSL options and retry.
at Object.createConnection (/usr/odk/node_modules/slonik/dist/src/factories/createConnection.js:54:23)
at processTicksAndRejections (internal/process/task_queues.js:85:5)
With the above, I'm fresh out of ideas. @issa @LN @Matthew_White any pointers would be highly welcome.
4. What steps can we take to reproduce the problem?
See above build steps.
5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.
