17

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?

steel
  • 11,883
  • 7
  • 72
  • 109

12 Answers12

28

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!

Simon Borsky
  • 4,979
  • 2
  • 22
  • 20
13

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
Tim Ramsey
  • 987
  • 13
  • 24
7

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.

Kankan-0
  • 463
  • 3
  • 11
  • 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. – steel Feb 04 '20 at 17:48
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,
  },
});
Victor Karangwa
  • 1,679
  • 20
  • 16
4

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.

ggorlen
  • 44,755
  • 7
  • 76
  • 106
Raj Verma
  • 1,050
  • 1
  • 7
  • 19
4

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.

Gwamaka Charles
  • 1,479
  • 9
  • 12
2

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.

steel
  • 11,883
  • 7
  • 72
  • 109
  • 2
    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](https://github.com/smashedtoatoms/asdf-postgres/pull/17). – Raffael May 24 '20 at 13:36
1

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

gildniy
  • 3,528
  • 1
  • 33
  • 23
1

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

{
  "production": {
    "ssl": {
      "require": true,
      "rejectUnauthorized": false
    },
  }
}
ggorlen
  • 44,755
  • 7
  • 76
  • 106
aifodu
  • 254
  • 3
  • 2
1

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`),
    },
  },
}
Folusho Oladipo
  • 362
  • 3
  • 11
0

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

Isaac Borbon Miquirray
  • 49
  • 2
-1

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

Manish Kapoor
  • 488
  • 3
  • 16