Upgrade of Heroku Postgres breaks loopback-connector-postgresql with SSL error

[JuergenSimon]

There is a show stopper problem that arises when using loopback-connector-postgresql in a Loopback3 application on Heroku. The problem is outlined here:

https://help.heroku.com/MDM23G46/why-am-i-getting-an-error-when-i-upgrade-to-pg-8

I have tried to use the following configuration for loopback-connector-postgresql:

const config = {
      connector: 'postgresql',
      user: process.env.PG_USER,
      password: process.env.PG_PASS,
      host: process.env.PG_HOST,
      port: process.env.PG_PORT,
      database: process.env.PG_DATABASE
};
if (process.env.NODE_ENV === 'production') {
    options.ssl = {
        rejectUnauthorized: false
    };
}

It seems that the ssl option does not work.

Steps to reproduce

• Create a loopback3 application using loopback-connector-postgres@5.0.1
• Deploy to Heroku
• Add Heroku Postgres
• Crash and burn

Current Behavior

The error you get on Heroku when trying to connect to Postgres from the loopback application looks like this:

Connection fails: Error: self signed certificate
It will be retried for the next request.
events.js:287
      throw er; // Unhandled 'error' event
      ^
Error: self signed certificate
    at TLSSocket.onConnectSecure (_tls_wrap.js:1474:34)
    at TLSSocket.emit (events.js:310:20)
    at TLSSocket.EventEmitter.emit (domain.js:482:12)
    at TLSSocket._finishInit (_tls_wrap.js:917:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:687:12)
Emitted 'error' event on DataSource instance at:
    at DataSource.postInit (/app/node_modules/loopback-datasource-juggler/lib/datasource.js:489:16)
    at PendingItem.callback (/app/node_modules/loopback-connector-postgresql/lib/postgresql.js:102:17)
    at /app/node_modules/pg-pool/index.js:237:23
    at Connection.connectingErrorHandler (/app/node_modules/pg/lib/client.js:213:14)
    at Connection.emit (events.js:310:20)
    at Connection.EventEmitter.emit (domain.js:482:12)
    at TLSSocket.reportStreamError (/app/node_modules/pg/lib/connection.js:57:10)
    at TLSSocket.emit (events.js:310:20)
    at TLSSocket.EventEmitter.emit (domain.js:482:12)
    at emitErrorNT (internal/streams/destroy.js:92:8)
    at emitErrorAndCloseNT (internal/streams/destroy.js:60:3)
    at processTicksAndRejections (internal/process/task_queues.js:84:21) {
  code: 'DEPTH_ZERO_SELF_SIGNED_CERT'

Expected Behavior

Postgres connection is established via SSL.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[danysz]

still happening

[garimaj9837]

Yes Facing the same issue

[Sulejman]

Also for the digital ocean, is there a way to connect using TLS/SSL certificate, all I could find was this commit which added possibility to have SSL parameter false.

[kleberalves]

Hello, friends.

The loopback-connector-postgresql 5+ does not implicity allow unauthorized connections as the 3.9.1 version. You need to configure...

I did it on my project LB4 in postgres.datasource.ts with loopback-connector-postgresql@5.3.0:

constructor(
@Inject('datasources.config.postgres', {
optional: true
})
dsConfig: any = config,
) {

dsConfig["url"] = process.env.POSTGRESQL;

const fs = require('fs');
dsConfig["ssl"] = {
  "rejectUnauthorized": true,
  "ca": fs.readFileSync(__dirname + "/ca-certificate.crt").toString()
}

super(dsConfig);

}

You can still ignore the unauthorized with false (rejectUnauthorized:false) but you will continue with security risks. I think its more better to download your cert file (from your database service) and setup you connection with rejectUnauthorized:true.

Reference: https://node-postgres.com/features/ssl

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.