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?

Underthrust

Answer 1

It turns out that commands like heroku pg:bloat use the local installation of Postgres and psql under the hood. I recompiled my Postgres installation with ssl support (--with-openssl) and everything worked.

Underthrust

When installing Postgres with asdf-vm, you can add a file ~/.asdf-postgres-configure-options with the content export POSTGRES_EXTRA_CONFIGURE_OPTIONS="--with-openssl". This will then be used during asdf install. See here. – Louralourdes

Answer 2

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!

Janinejanis

Any idea why this doesn't work with pg-promise in Node.js? – Bogor

Answer 3

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

Gyrostabilizer

this resulted in an error for me 'Error: self signed certificate' any ideas why? – Ovenware

This one doesn't fix my issue with Python – Nola

Answer 4

The error basically says that you are trying to connect to a database instance without using SSL connection, but the server is setup to reject connection without SSL connection.

pg_hba.conf file resides in the server, and contains details of the allowed connections. In your case there's no matching record to be found in the file with the given details(under non-ssl connection).

You can fix this by forcing the connection to be follow SSL protocol. As Raj already mentioned, you need to modify your connection string to include the 'sslmode=require'. Here's a heroku documentation regarding the same. Check out the 'external connections' block in the doc.

Photoengraving

I'm not able to pass a string to the Heroku command, though that did fix the issue when I passed the connection string to my local psql command. – Underthrust

Answer 5

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,
  },
});

Psychopharmacology

Answer 6

You need to set sslmode=require in your connections string. An example for when the JDBC driver is:

String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + 
dbUri.getPath() + "?sslmode=require";

You can always toggle the ssl_mode in the config vars but I would suggest doing it in the connection string.

Cassidycassie

Answer 7

This works for Nodejs. Specify the PGSSLMODE config var: heroku config:set PGSSLMODE=no-verify you can do that through a terminal with the Heroku CLI.

Claustrophobia

Answer 8

I think on Heroku the SSL is not disabled but require, which can be the reason for this error.

Brusa

Answer 9

This helped resolve the issue in my case. Adding the ssl property in config.json (Sequelize)

{
  "production": {
    "ssl": {
      "require": true,
      "rejectUnauthorized": false
    },
  }
}

Morpheme

Answer 10

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 attack (man-in-the-middle attacks).

A better solution is to give your Postgre client the CA that you want it to use. In your case it might be a Heroku CA, but 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 create a connection and then modified my config to:

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

Sole

Answer 11

If you are using Sequelize as your ORM this is the configuration for the connection needed to solve this problem. Edit your database file (db.js): As explained in this answer by Heroku.

var connection = process.env.DATABASE_URL
isProduction ? connection  : connection = connectionString;

const sequelize = new Sequelize(connection,{
      logging: false,   //Loging disabled
      dialectOptions: {
        ssl:{
          require:true,
          rejectUnauthorized: false
        } 
      }
  });
  try {
      sequelize.authenticate();
      console.log('Database connected successfully!');
    } catch (error) {
      console.error('Unable to connect to the database:', error);
    }

also edit your config.json file as explained here

Melvamelvena

Answer 12

Appending ?sslmode=require to the connection URL worked for me.

L

This is already an answer: https://mcmap.net/q/211980/-heroku-postgres-quot-psql-fatal-no-pg_hba-conf-entry-for-host-quot – Underthrust