search

getodk / central

ODK Central is a server that is easy to use, very fast, and stuffed with features that make data collection easier. Contribute and make the world a better place! ✨🗄✨
https://docs.getodk.org/central-intro/
Apache License 2.0
125 stars 151 forks source link

Support SSL between backend, db, frontent #96

Closed florianm closed 4 years ago

florianm commented 5 years ago

I've set up a fresh instance of ODK Central as K8s pods with a blank postgres11 db. The db apparently requires SSL.

I'm getting this error:

root@service-d47b965cd-d4td4:/usr/odk# odk-cmd --email florian.xxx@xxx user-create
prompt: password: xxx

Error: The server does not support SSL connections
    at Socket.<anonymous> (/usr/odk/node_modules/pg/lib/connection.js:86:33)
    at Object.onceWrapper (events.js:291:20)
    at Socket.emit (events.js:203:13)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:210:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:166:17)Problem [Error]: An unknown internal problem has occurred. Please try again later.
    at Object.unknown (/usr/odk/lib/util/problem.js:39:3)
    at postgresErrorToProblem (/usr/odk/lib/util/db.js:282:34)
    at tryCatcher (/usr/odk/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/usr/odk/node_modules/bluebird/js/release/promise.js:517:31)
    at Promise._settlePromise (/usr/odk/node_modules/bluebird/js/release/promise.js:574:18)
    at Promise._settlePromise0 (/usr/odk/node_modules/bluebird/js/release/promise.js:619:10)
    at Promise._settlePromises (/usr/odk/node_modules/bluebird/js/release/promise.js:695:18)
    at _drainQueueStep (/usr/odk/node_modules/bluebird/js/release/async.js:138:12)
    at _drainQueue (/usr/odk/node_modules/bluebird/js/release/async.js:131:9)
    at Async._drainQueues (/usr/odk/node_modules/bluebird/js/release/async.js:147:5)
    at Immediate.Async.drainQueues (/usr/odk/node_modules/bluebird/js/release/async.js:17:14)
    at processImmediate (internal/timers.js:439:21) {
  problemCode: 500.1,
  problemDetails: {
    error: Error: The server does not support SSL connections
        at Socket.<anonymous> (/usr/odk/node_modules/pg/lib/connection.js:86:33)
        at Object.onceWrapper (events.js:291:20)
        at Socket.emit (events.js:203:13)
        at addChunk (_stream_readable.js:294:12)
        at readableAddChunk (_stream_readable.js:275:11)
        at Socket.Readable.push (_stream_readable.js:210:10)
        at TCP.onStreamRead (internal/stream_base_commons.js:166:17)
  }
}

Is that SSL option something the backend could easily support? Not wanting to pile on unnecessary work, but probably required for moving postgres versions forward.

issa-tseng commented 5 years ago

are you sure you aren't attempting to connect to a non-SSL database with an SSL connect string?

given the PG client is complaining that "the server does not support SSL connections" i would assume that this means it's trying to connect with SSL to postgres but postgres isn't liking it.

florianm commented 5 years ago

The db is a plain postgres:9.6 running in the same cluster/project, no SSL anywhere in the config. The service container also has no SSL config. The only difference to my other, working, ODK Central instance is a slightly newer Docker image (both 0.6 though) and a different k8s cluster (only our infra folks could tell).

I did try connecting to a separately hosted (way gruntier) pg cluster with SSL, setting an env var 'SSLMODEREQUIRE=true' (IIRC), also no joy.

I guess since the images work fine in the docker-compose version, the error must be some kind of wrong config on my end. Complication: all config I can see/edit is exactly identical between working and non-working.

issa-tseng commented 5 years ago

okay let's step back.

clarification question: is it that you're running into this error for whatever reason and therefore your ask is SSL so the error goes away, or is it that you're trying to set up SSL between all these components and that's why you're getting this error?

florianm commented 5 years ago

issa(beercount++)

The latest master of ODK Central works again for me, so my "frontend can't find route to backend" issue is solved by rebuilding the docker images as per

git clone https://github.com/opendatakit/central
git submodule update -i
docker build . -f nginx.dockerfile -t dbcawa/odk_nginx
docker build . -f service.dockerfile -t dbcawa/odk_service
docker push dbcawa/odk_nginx
docker push dbcawa/odk_service

and running them in K8s as described here.

My remaining issue is to try and connect to an external db. We've got a dedicated Postgres 11 cluster which is tuned and resourced way better than my own little sandbox. Our corporate IT much prefers me to use that instance to let WA turtle programs dump some 200GB of attachments into.

So my remaining issue is to use a postgres 11 db that requires SSL from the service backend. The frontend's routing to the backend is just fine as is.

As the vanilla setup works now, this "SSL to db" business is a nice to have and should go to the bottom of the priorities. Again, thanks for your support and patience.

issa-tseng commented 5 years ago

from my reading of the error message the central-backend service is already trying to use SSL to connect to the database, and the database is the one that doesn't allow that.

Error: The server does not support SSL connections - remember that from the context of libpg, it is the client and the database is the server.

as for the route to backend stuff, i'm not sure. sounds like a transient issue in your networking. we didn't change anything there. in this case, remember that the "frontend" is just your web browser. it's nginx that's seeking the backend in those logs. if it thinks there is no route, then there is a hostfile issue, or a gateway issue, or something else networky. is 10.244.3.80 local to the nginx host? next time this happens, log into nginx and trace that route and you may get some clues.

issa-tseng commented 5 years ago

(i do see that you've done some investigation on this line of thinking, but i'm still pretty sure the problem is more in that direction.)

issa-tseng commented 5 years ago

i dug up the source of the error message and indeed it occurs as a result of a response from the database that it does not like SSL:

https://github.com/brianc/node-postgres/blob/master/lib/connection.js#L88

florianm commented 5 years ago

(i do see that you've done some investigation on this line of thinking, but i'm still pretty sure the problem is more in that direction.)

The "route to host" problem was solved by building and restarting the two images.

Thanks for the SSL/pg client tip, chasing up with IT now.

matthew-white commented 5 years ago

I think I saw a comment that mentioned the nginx.conf in central-frontend, so I also wanted to note that that file is only intended for local development. The nginx.conf in central-frontend doesn't do everything that the configuration in the central repo does; for example, it doesn't support request-body gzip decompression. This doesn't sound like the source of the issue here, but in case you weren't already, I wanted to recommend that you use the NGINX configuration in the central repo as the basis of your work.

florianm commented 5 years ago

@issa-tseng @matthew-white retrying while still jet lagged:

On reloading the service pod, this happens:

wait-for-it.sh: waiting 15 seconds for postgres:5432
wait-for-it.sh: timeout occurred after waiting 15 seconds for postgres:5432
running migrations..

Unhandled rejection error: SSL connection is required. Please specify SSL options and retry.

at Connection.parseE (/usr/odk/node_modules/pg/lib/connection.js:545:11)
at Connection.parseMessage (/usr/odk/node_modules/pg/lib/connection.js:370:19)
at Socket.<anonymous> (/usr/odk/node_modules/pg/lib/connection.js:113:22)
at Socket.emit (events.js:203:13)
at addChunk (_stream_readable.js:294:12)
at readableAddChunk (_stream_readable.js:275:11)
at Socket.Readable.push (_stream_readable.js:210:10)
at TCP.onStreamRead (internal/stream_base_commons.js:166:17)
starting cron..
starting server.

Do I read above error correctly that the db complains that it requires SSL? How can I tell ODK Central to use SSL when accessing the db?

issa-tseng commented 4 years ago

okay, so now i think we agree that postgres wants central to use ssl but it is not. this makes more sense.

i think most likely all you need to do is set the correct configuration in default.database in config.json.template. i don't know exactly that that would be but if you use a connection string instead of an object i think you'll be able to specify whatever the standard ssl-ish connection string would be. or there may be a config key you can add to the existing object to specify ssl/secure. report back if you figure it out!

we use knexjs.org under the covers, but that just uses the nodejs pg client library under its covers.

florianm commented 4 years ago

@issa-tseng knowing that you use knex.js and node-postgres helped me to find the docs about SSL mode at https://node-postgres.com/features/ssl.

Config just needed the addition of "ssl": {"rejectUnauthorized": false}:

 "database": {
      "host": "DBHOST",
      "user": "USERNAME",
      "password": "PASSWORD",
      "database": "DBNAME",
      "ssl": {"rejectUnauthorized": false}
    },
...

Thanks for the help Issa!