heroku Postgres - sequelize : no pg_hba.conf entry for host

Question

I am trying to run migrations on my Nodejs application hosted on Heroku using the Heroku free Postgres database.

I am using Sequelize as my ORM.This is my configuration for the production connection.

const dotenv = require('dotenv');

dotenv.config();

module.exports = {
  production: {
    use_env_variable: 'DATABASE_URL',
    dialect: process.env.DIALECT,
    protocol: process.env.DIALECT,
  }
}

When I use the above configuration, I get the following error: no pg_hba.conf entry for host "000.000.000.0", user "yyyyyyyyyyyyyy", database "xxxxxxxxxxxxx", SSL off

However when I add the options below to the config, I get a self-signed certificate error.

dialectOptions: {
ssl: true
}

Please, how do I resolve this?

Answer 1

Change your dialectOptions to:

dialectOptions: {
    ssl: {
        rejectUnauthorized: false
    }
}

Answer 2

As explained in this related answer, setting rejectUnauthorized: false is a bad idea because it allows you to create non-encrypted connections to your database and can, thus, expose you to MITM attacks (man-in-the-middle attacks).

A better solution is to give your Postgres client the CA that you want it to use. In my case it was a CA used by AWS RDS for the North Virginia region (us-east-1). I downloaded the CA from this AWS page, placed it in the same directory as the file I wanted to use to connect to the DB and then modified my config to:

{
  ...
  dialectOptions: {
    ssl: {
      require: true,
      ca: fs.readFileSync(`${__dirname}/us-east-1-bundle.pem`),
    },
  },
}