Authentication error when connecting to Heroku PostgreSQL database

Question

I'm developing a Node.js application using PostgreSQL and hosting on Heroku. My problem is that I get an authentication error like so:

14:32:05 web.1     | { [error: no pg_hba.conf entry for host "193.40.244.196", user "username", database "database_name", SSL off]
14:32:05 web.1     |   length: 168,
14:32:05 web.1     |   name: 'error',
14:32:05 web.1     |   severity: 'FATAL',
14:32:05 web.1     |   code: '28000',
14:32:05 web.1     |   detail: undefined,
14:32:05 web.1     |   hint: undefined,
14:32:05 web.1     |   position: undefined,
14:32:05 web.1     |   internalPosition: undefined,
14:32:05 web.1     |   internalQuery: undefined,
14:32:05 web.1     |   where: undefined,
14:32:05 web.1     |   file: 'auth.c',
14:32:05 web.1     |   line: '483',
14:32:05 web.1     |   routine: 'ClientAuthentication' }

It might be an SSL problem, but it shouldn't be as mentioned here. SSL should be supported out of the box. So I'm stumped and can only ask what might cause this error?

I'm not sure if I have to maybe edit the pg_hba.conf on my system, but I can't even find it.

score 48 · Answer 1 · edited Jul 27 '15 at 17:34

48

Solved it by setting PGSSLMODE (http://www.postgresql.org/docs/9.0/static/libpq-envars.html) on Heroku. It tells PostgreSQL to default to SSL.

$ heroku config:set PGSSLMODE=require

edited Jul 27 '15 at 17:34

JJD

50,076
60
203
339

answered Jan 01 '15 at 16:16

jede

2,385
2
20
17

Agreed. This is the best answer as it kept my local and remote versions working with the least amount of code. – saoyr5 Sep 10 '15 at 21:11
1

It's indeed the most exact answer, as other solutions provided don't make use of process.env.DATABASE_URL – Standaa - Remember Monica Apr 15 '16 at 12:14
Excellent! After hours, this was my solution. Thank you. – Steve Pascoe Aug 25 '16 at 05:16
Ran into this problem when I upgraded my postgresql instance https://devcenter.heroku.com/articles/upgrading-heroku-postgres-databases#upgrade-with-pg-upgrade. PGSSLMODE was the fix. – Will Lovett Sep 13 '16 at 21:19
Thanks a lot! This should be the answer. – Gersom Jul 20 '17 at 10:09
This is a [good discussion](https://ankane.org/postgres-sslmode-explained) of the meanings of the settings available for PGSSLMODE, in case anyone else finds it helpful. – Matt Sanders Mar 03 '20 at 21:01
@Carlos.V happy to help! :D – jede Mar 10 '21 at 13:37
For my use case, I had to set PGSSLMODE to no-verify as per Heroku's docs here: https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js – Manath Mar 20 '21 at 20:30

score 27 · Answer 2 · answered Jun 28 '12 at 20:45

27

node-postgres doesn't support SSL in it's javascript bindings, which you're using if you do:

var pg = require('pg');

To get SSL, you need to use the native binding by doing this:

var pg = require('pg').native;

You don't need to use SSL when your app is running inside Heroku, you only need to use SSL to connect remotely (when your app is running locally).

answered Jun 28 '12 at 20:45

matt

357
4
5

1

One does not need native binding to run SSL anymore. – Jérôme Verstrynge Jan 31 '14 at 17:36
2

Worked for me...thanks! (if you are using Sequelize, add `native: true` to the `new Sequelize` options object) – timborden Jun 24 '14 at 09:06
2

This requires `npm i pg-native` – standup75 Oct 05 '15 at 16:50
1

https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js says to set `pg.defaults.ssl = true;` before connecting. – Jon L. Jun 12 '17 at 22:00

score 19 · Answer 3 · edited Jul 27 '15 at 17:31

19

I added these params and can now connect to my heroku postgres instance from an outside server, specifically, in configuration of knex.js in a node express server:

var knex = require('knex')({
  client: 'postgres',
  connection: 'postgres://username:password@host:5432/yourdbname?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory'
});

edited Jul 27 '15 at 17:31

JJD

50,076
60
203
339

answered Jul 23 '14 at 04:56

adrichman

1,215
10
17

thanks for the knex example! Where did you find this information? – triniMahn Sep 07 '14 at 22:07
not sure! but I see it's documented, so perhaps it was here: https://devcenter.heroku.com/articles/connecting-to-relational-databases-on-heroku-with-java – adrichman Sep 08 '14 at 22:57
1

This also helped me with local nodejs development using heroku postgre db – Putna Jan 23 '15 at 19:58

score 9 · Answer 4 · answered Jan 09 '14 at 09:26

Ran into same issue. Just Enabled ssl=true in the db params.

var pg = require('pg');
var params = { host: 'heroku_hostname',user: 'username',password: 'password',database: 'database',ssl: true };
var client = new pg.Client(params);
client.connect();

score 3 · Accepted Answer · answered May 09 '12 at 22:53

3

Ran into this myself, it's a simple fix. Just connect over HTTPS instead

answered May 09 '12 at 22:53

Jeff Dickey

5,036
4
28
39

1

Usually just forcing the port to 5572 of the one listed with `heroku config` would suffice. – David Pelaez Feb 16 '13 at 00:18
17

"Just connect over HTTPS instead". Could you elaborate? Do you mean specify port 5572 as David Pelaez suggests? That doesn't work for me, nor does specifying ssl: true in the params as someone else suggested, nor does specifying both. I'm using the javascript bindings, not native. – Jake Mar 10 '14 at 15:23

score 2 · Answer 6 · answered Apr 14 '21 at 22:58

I too received the 'ClientAuthentication' no pg_hba.conf entry error in my Heroku PRODUCTION instance in April 2021. I was and continue to use the Plan "Hobby Dev" free account.

Heroku began rolling out changes to the platform in February 2021 that enforced SSL for all Heroku Postgres client connections. All Heroku Postgres client connections require SSL.

I fixed my PRODUCTION instance (where my NextJS App connected to the Heroku Postgres add-on) by adding the following config ssl: { rejectUnauthorized: false } when using node-postgres v8:

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

score 2 · Answer 7 · answered May 27 '22 at 20:48

2

Had the same issue. Here's what helped me.

new Sequelize(process.env.DATABASE_URL, {
    dialectOptions: {
      ssl: {
        require: true,
        rejectUnauthorized: false
      }
    }
  }

answered May 27 '22 at 20:48

Nikita Kurnosov

21
2

score 0 · Answer 8 · answered Oct 28 '21 at 18:21

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

I just resolved the issue by adding the ssl: { rejectUnautorized: false } to my database configuration. Also, run heroku config:set PGSSLMODE=no-verify -a appName

NOTE: "appName" should be the name of your hosted application

Authentication error when connecting to Heroku PostgreSQL database

8 Answers8

Linked