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.

JJD · Accepted Answer · 2015-07-27 17:34:23Z

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, 2015 at 17:34

JJD

52.7k63 gold badges218 silver badges352 bronze badges

answered Jan 1, 2015 at 16:16

jede

2,4612 gold badges21 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

saoyr5 Over a year ago

Agreed. This is the best answer as it kept my local and remote versions working with the least amount of code.

Standaa Over a year ago

It's indeed the most exact answer, as other solutions provided don't make use of process.env.DATABASE_URL

Steve Pascoe Over a year ago

Excellent! After hours, this was my solution. Thank you.

Will Lovett Over a year ago

Ran into this problem when I upgraded my postgresql instance devcenter.heroku.com/articles/…. PGSSLMODE was the fix.

Matt Sanders Over a year ago

This is a good discussion of the meanings of the settings available for PGSSLMODE, in case anyone else finds it helpful.

|

matt · Accepted Answer · 2012-06-28 20:45:17Z

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, 2012 at 20:45

matt

3574 silver badges5 bronze badges

4 Comments

Jérôme Verstrynge Over a year ago

One does not need native binding to run SSL anymore.

timborden Over a year ago

Worked for me...thanks! (if you are using Sequelize, add native: true to the new Sequelize options object)

standup75 Over a year ago

This requires npm i pg-native

Jon L. Over a year ago

devcenter.heroku.com/articles/… says to set pg.defaults.ssl = true; before connecting.

JJD · Accepted Answer · 2015-07-27 17:31:36Z

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, 2015 at 17:31

JJD

52.7k63 gold badges218 silver badges352 bronze badges

answered Jul 23, 2014 at 4:56

adrichman

1,23510 silver badges17 bronze badges

3 Comments

triniMahn Over a year ago

thanks for the knex example! Where did you find this information?

adrichman Over a year ago

not sure! but I see it's documented, so perhaps it was here: devcenter.heroku.com/articles/…

Putna Over a year ago

This also helped me with local nodejs development using heroku postgre db

Avinash · Accepted Answer · 2014-01-09 09:26:43Z

9

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();

answered Jan 9, 2014 at 9:26

Avinash

7797 silver badges18 bronze badges

Comments

Jeff Dickey · Accepted Answer · 2012-05-09 22:53:30Z

3

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

answered May 9, 2012 at 22:53

Jeff Dickey

5,0444 gold badges30 silver badges39 bronze badges

2 Comments

David Pelaez Over a year ago

Usually just forcing the port to 5572 of the one listed with heroku config would suffice.

Jake Over a year ago

"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.

Michael R · Accepted Answer · 2021-04-14 22:58:55Z

2

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

answered Apr 14, 2021 at 22:58

Michael R

1,6031 gold badge20 silver badges27 bronze badges

Comments

Nikita Kurnosov · Accepted Answer · 2022-05-27 20:48:32Z

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, 2022 at 20:48

Nikita Kurnosov

212 bronze badges

Comments

Gift · Accepted Answer · 2021-10-28 18:21:02Z

0

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

answered Oct 28, 2021 at 18:21

Gift

11 bronze badge

Collectives™ on Stack Overflow

Authentication error when connecting to Heroku PostgreSQL database

8 Answers 8

7 Comments

4 Comments

3 Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

7 Comments

4 Comments

3 Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related