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?
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!
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
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,
},
});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With