CODEWITHSUNDEEP
postgresqlherokuheroku-postgresheroku-clipostgresql-10
steel
steel

Reputation: 12550

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

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?

Upvotes: 20

Views: 31496

Answers (12)

Manish Kapoor
Manish Kapoor

Reputation: 536

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

Upvotes: -1

Isaac Borbon Miquirray
Isaac Borbon Miquirray

Reputation: 109

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

Upvotes: 1

Simon Borsky
Simon Borsky

Reputation: 5171

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!

Upvotes: 30

Folusho
Folusho

Reputation: 382

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

Upvotes: 1

Gwamaka Charles
Gwamaka Charles

Reputation: 1695

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.

Upvotes: 4

Tim Ramsey
Tim Ramsey

Reputation: 1135

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

Upvotes: 13

aifodu
aifodu

Reputation: 304

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

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

Upvotes: 2

Raj Verma
Raj Verma

Reputation: 1172

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.

Upvotes: 4

Victor Karangwa
Victor Karangwa

Reputation: 2216

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

Upvotes: 7

gildniy
gildniy

Reputation: 3943

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

Upvotes: 1

steel
steel

Reputation: 12550

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.

Upvotes: 2

Kankan-0
Kankan-0

Reputation: 463

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.

Upvotes: 7

Related Questions