Certificate Path when connecting to database via SSL

[GuruCharan94]

Hi, I am unable to connect to Postgres when I set --storage.sqlConnectionSsl to true.
I am guessing (and I may be wrong here) that the solution is related to this stackoverflow answer

If that is the case, how do I specify the certificate path?

FWIW, The lighthouse server and Postgres is running on Azure.

[patrickhulce]

Ah lhci doesn't currently expose the manual certificate options if it's self-signed, sorry! That's a good feature request though 👍

[mcr85]

Hi, any plans to expose SSL cert configuration for database connection?

[patrickhulce]

It already is.

[albertoal]

When upgrading my LHCI server from 0.3.7 to 0.4.1 and using --storage.sqlConnectionSsl I was getting this error:

(node:16) UnhandledPromiseRejectionWarning: SequelizeConnectionError: no pg_hba.conf entry for host "private IP - redacted", user "*******", database "***", SSL off

My Postgres DB requires SSL on all the connections.

Adding ?ssl=true to the connection string didn't help either.

Found this SO answer which advised to also require SSL on sqlDialectOptions so implemented it as follows on my LHCI server:

  storage: {
    storageMethod: 'sql',
    sqlDialect: 'postgres',
    sqlConnectionSsl: true,
    sqlConnectionUrl: process.env.DB_CONN_URL,
    sqlDialectOptions: {
      ssl: {
        require: true,
      },
    }

After adding the above, my server worked fine.

Do you want me to add this use case to the docs @patrickhulce ? It would seem that --storage.sqlConnectionSsl doesn't seem to work on Postgres servers where SSL is enforced.

[patrickhulce]

Hm, we didn't change anything in the SQL options between those versions that I'm aware of. Are you positive that you didn't update the postgres dependency at the same time?

Happy to add this to the docs though once we figure out why the support change!

[albertoal]

Thanks Patrick, looking at the package-lock.json diff, I only see sequelize going from^4.42.0 to ^4.44.3 but didn't touched pg at all. I don't have a lot of time to test this locally but should be able to repro locally assuming the local PG instance requires SSL. Unfortunately when using the default postgres Docker container and my code above I was getting this error:

SequelizeConnectionError: The server does not support SSL connections

Our cloud-based Postgres enforces SSL connections and that this was the only way I could get it to work. Thanks!

[patrickhulce]

Very odd that it would stop working then, perhaps it's a change in sequelize behavior in those versions?

Either way happy to add what you found to the docs. Thanks for sharing!