Heroku Postgres: "psql: FATAL: no pg_hba.conf entry for host"

Question

There are a number of Heroku CLI Postgres commands that all return the same error. For example:

$ heroku pg:bloat
psql: FATAL:  no pg_hba.conf entry for host "...", user "...", database "...", SSL off

At least one of these commands has worked in the past, but does not now.

The database appears to be working fine otherwise. I can access it via my application's interfaces.

I do not see a way to toggle SSL in the Heroku Postgres dashboard. What could be causing this and how could I fix it?

Answer 1

I got the error while connecting to Postgres from an external application. The fix is relative to the language that you use. In Java you need to chain the ?sslmode=require to query string, in NodeJS (my situation) you should add rejectUnauthorized: false as following -

const client = new Client({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});

Refer to https://devcenter.heroku.com/articles/heroku-postgresql for more details.

Enjoy!

Answer 2

I solved it by setting PGSSLMODE on Heroku to require. This tells Postres to default to SSL. On the command line this can be done via the following command.

heroku config:set PGSSLMODE=require

Answer 3

Adding ssl option in database configs fixed this issue on my end:

If you are using sequelize as your ORM

const config = {
  ...
  production: {
    use_env_variable: 'DATABASE_URL',
    dialect: 'postgresql',
    logging: false,
        dialectOptions: {
      ssl: {      /* <----- Add SSL option */
        require: true,
        rejectUnauthorized: false 
      }
    },
  },
  ...
}

If you are not using any ORM:

const pool = new Pool({
  connectionString:DATABASE_URL ,
  ssl: {    /* <----- Add SSL option */
    rejectUnauthorized: false,
  },
});