Allow providing SSL CA certificates to graphile-migrate CLI

[foxfriends]

Feature description

I am looking for a way to provide a path to additional SLL certificate(s) for the database connection strings.

GRAPHILE_SSL_CA_CERT=./some-cert-path graphile-migrate migrate 

Or via the config file, passing the options along to the underlying pg-pool:

{
  "pgopts": { "ssl": { "ca": "path-to-cert" } }
}

Motivating example

I am attempting to use graphile-migrate (2.0.0-rc.2) to migrate a DigitalOcean managed database, which as previously recognized uses self-signed certificates.

Supporting development

I [tick all that apply]:

• [ ] am interested in building this feature myself
• [ ] am interested in collaborating on building this feature
• [x] am willing to help testing this feature before it's released
• [ ] am willing to write a test-driven test suite for this feature (before it exists)
• [ ] am a Graphile sponsor ❤️
• [ ] have an active support or consultancy contract with Graphile

[foxfriends]

The error when attempting to use graphile-migrate without providing a certificate:

Error: self-signed certificate in certificate chain
    at /home/.../node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async withClient (/home/.../node_modules/graphile-migrate/dist/pgReal.js:79:26)
    at async _migrate (/home/.../node_modules/graphile-migrate/dist/commands/migrate.js:18:5)
    at async handler (/home/.../node_modules/graphile-migrate/dist/commands/migrate.js:64:9)
    at async Object.newHandler [as handler] (/home/.../node_modules/graphile-migrate/dist/cli.js:21:20) {
  code: 'SELF_SIGNED_CERT_IN_CHAIN'
}

[benjie]

Did you manage to find a solution to this? I imagine something like the ?ssl&sslrootcert=/path/to/cert.pem style connection string might work? Here's some instructions from Worker but the concept is essentially the same https://worker.graphile.org/docs/connection-string#tcp--ssl

[foxfriends]

My solution... I switched to drizzle-kit for migrations instead sorry. I forget if this was the only issue (it was very much blocking my ability to go to prod) or if there was a second issue as well.

[benjie]

No worries; you should use whatever migration framework makes you productive!

[foxfriends]

This one is the one I've been most impressed by though, if it gets more active development again I would like to return :)

[benjie]

Well I hope it's as easy as adding the query string above, but this is mostly a concern of the pg library we use rather than Migrate itself. We may need to provide an easier way to pass the options through, though!