Knex Heroku Error: self signed certificate

Question

I keep getting this error:

Error: self signed certificate

When running this command in the terminal:

knex migrate:latest --env production

My knexfile.js

       require('dotenv').config(); 
module.exports = {
      development: {
        client: "pg",
        connection: {
          host: "localhost",
          database: "my-movies"
        }
      },

      production: {
        client: "pg",
        connection: process.env.DATABASE_URL
      }
    };

My .env file:

DATABASE_URL=<my_database_url>?ssl=true

Heroku app info:

Addons:         heroku-postgresql:hobby-dev
Auto Cert Mgmt: false
Dynos:
Git URL:        https://git.heroku.com/path-name.git
Owner:          [email protected]
Region:         us
Repo Size:      0 B
Slug Size:      0 B
Stack:          heroku-18
Web URL:        https://my-appname.herokuapp.com/

I've tried putting a key value pair in the production in the knexfile of ssl: true and I get the same error. I've done it this way in the past many, many times and have never had this issue. Wondering if Heroku has changed anything but while searching their docs I couldn't find anything.

Answer 1

The following config at knexfile.js worked for me.

...
production: {
    client: 'postgresql',
    connection: { 
        connectionString: process.env.DATABASE_URL,
        ssl: { rejectUnauthorized: false }
    }
}
...

where the DATABASE_URL is what you get by running heroku config --yourAppName

Answer 2

This is due to a breaking change in pg@^8 (2020/02/25) cf. this heroku help forum.

You can get the full pg@^8 announcement but here is the relevant passage:

Now we will use the default ssl options to tls.connect which includes rejectUnauthorized being enabled. This means your connection attempt may fail if you are using a self-signed cert.

And it seems heroku is using self-signed certificates somewhere.

possible solutions:

• downgrade to pg@^7
• instruct pg@^8 to ignore problematic certificates ssl: { rejectUnauthorized: false } (see announcement linked above)
• find a way to download and trust the certificate instructions