Node.js, PostgreSQL error: no pg_hba.conf entry for host

Question

I am following this article ((http://nodeexamples.com/2012/09/21/connecting-to-a-postgresql-database-from-node-js-using-the-pg-module/). I have already deployed my app to heroku and currently using express, node.js, to try and connect to a PostgresSQL database in Heroku that I just installed. I get to the very end of the article and I use the command

node myfile.js

I get this error

error: no pg_hba.conf entry for host "...", user "...", database "...", ... 

How do I go about creating one and where in my app directory should I put it?

Below is the entire error message. I changed the strings for IP address, user, and database but it looks basically just like it.

events.js:72
    throw er; // Unhandled 'error' event
          ^
error: no pg_hba.conf entry for host "00.000.000.00", user "username", database "databasename", SSL off
at Connection.parseE (/Users/user/workspace/MyApp/app/node_modules/pg/lib/connection.js:526:11)
at Connection.parseMessage (/Users/user/workspace/MyApp/app/node_modules/pg/lib/connection.js:356:17)
at Socket.<anonymous> (/Users/user/workspace/MyApp/app/node_modules/pg/lib/connection.js:105:22)
at Socket.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:748:14)
at Socket.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:410:10)
at emitReadable (_stream_readable.js:406:5)
at readableAddChunk (_stream_readable.js:168:9)
at Socket.Readable.push (_stream_readable.js:130:10)

---

Edit: I did some more research and found that the 'pg_hba.conf' file is in my

/usr/local/var/postgres 

and I added this line into the 'pg_hba.conf' file

# TYPE  DATABASE        USER            ADDRESS                 METHOD
 host   all             all                                     trust

also tried

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host   all             all              0.0.0.0/0               md5

but it keeps saying there is no entry for my host, user, database, etc... is my 'pg_hba.conf' syntax wrong in any way?

Openmouthed

pg_hba.conf – Chequered

you'll want to edit /etc/postgresql/X.Y/main/pg_hba.conf. you'll need to add a line that looks like: "host mydatabase myuser 0.0.0.0/0 md5". @Chequered provided you a link with full documentation on that file so you should refer to that first. – Neiman

@Neiman Thank you for your answer. Where is the /etc/ directory? – Openmouthed

it's in the root directory, assuming you're running on linux. – Neiman

Oh, are you saying you deployed this to heroku and you saw this error? or you tried to run this on your own box? If you're doing this on heroku you'll likely need to first provision your database. First you should walk through this guide: devcenter.heroku.com/articles/…. Then you'll need to modify your code to connect using heroku's environment variables: devcenter.heroku.com/articles/…. They should take care of the pg_hba.conf modifications for you if you follow their guides. – Neiman

@Neiman I have already deployed this to heroku and now trying to connect to heroku's PostgreSQL database. I'll definitely check out those links and let you know how it goes. Thanks! – Openmouthed

Regarding the second link though, doesn't the work that I already did following the article I've attached above take care of connecting already? – Openmouthed

Also when I call 'heroku addons | grep POSTGRES' as stated in the first link I already have a database connected 'heroku-postgresql:dev HEROKU_POSTGRESQL_MYDB' I just want to add the 'pg_hba.conf' file – Openmouthed

can you post the entire error? not the credentials, but the entire error string verbatim. – Neiman

^^I've just updated the question – Openmouthed

try changing that bottom line to: "host all all 0.0.0.0/0 md5" – Neiman

Still the same issue, no change – Openmouthed

I also faced this issue when using Heroku Postgres offering. For latest answer, see the latest comments on issue github.com/typeorm/typeorm/issues/278 – Aboulia

Answer 1

Change your connection code to use ssl. Following your linked example:

var conString = "pg://admin:guest@localhost:5432/Employees";
var client = new pg.Client(conString);
client.connect();

becomes:

var client = new pg.Client({
    user: "admin",
    password: "guest",
    database: "Employees",
    port: 5432,
    host: "localhost",
    ssl: true
}); 
client.connect();

https://github.com/brianc/node-postgres/wiki/Client#new-clientobject-config--client

Neiman

that's an error with your application code. i'd open a new question on that issue, it's not at all related to the original question. you should post all of you application code in the new question too. – Neiman

Answer 2

const sequelize = new Sequelize({
  database: "DB",
  username: "root",
  password: "pass",
  host: "localhost",
  port: 5432,
  dialect: "postgres",
  dialectOptions: {
    ssl: {
      require: true, // This will help you. But you will see nwe error
      rejectUnauthorized: false // This line will fix new error
    }
  },
});

Scheldt

This is the correct solution as of pg version 8.5.1. github.com/typeorm/typeorm/issues/278 – Aboulia

You saved my day Victor! – Reborn

This solution has worked for me, with Sequelize ORM. You saved me. – Pliny

This worked for me! Thank you! Love the comments explaining why each option is required – Karisa

Thank you so much! This worked for me after spending hours – Boscage

Spending the whole day you saved my time bro. Thanks – Skive

Answer 3

Running on heroku:

We ran into this error while upgrading the pg database on heroku from hobby tier to standard-0. SSL is required, but we didnt set it in our config.

Include in config when initialize new Sequelize(...)

"dialect": "postgres",
"dialectOptions": {
  "ssl": true
}

This trick was, that the ssl option is wrapped in dialectOptions. found here: https://github.com/sequelize/sequelize/issues/956#issuecomment-147745033

Info by @Atish: Use

options: { 
  dialect: "postgres",
  native: true, # adding this maybe breaks on hobby dyno
  ssl: true, 
  dialectOptions: {
    ssl: true
  }
}

Malenamalet

Use options: { dialect:'postgres', native:true, ssl:true, dialectOptions: {ssl: true}' – Celesta

My experience, using Hobby tier Heroku pg: the app sequelize connection works without native:true, and adding this option breaks it. Sequelize CLI not working in either scenario as yet. – Burtis

@Burtis I have added your hint about the hobby dyno – Malenamalet

Answer 4

In the case sequelize ignores all of your efforts to turn ssl on, you can try to convince pg to enable ssl for all conncetions by default:

var pg = require('pg');
pg.defaults.ssl = true;

const Sequelize = require('sequelize');
const sequelize = new Sequelize('postgres://...');

Interdictory

Another option, slightly cleaner imo, is to do following in your sequelizeConfig: { "dialect":"postgres", "ssl":true, "dialectOptions":{ "ssl": {"require":true } }} github.com/sequelize/cli/issues/154#issuecomment-167960024 – Outsider

In my case this worked, but revealed a further error as described and resolved by this q/a: https://mcmap.net/q/262853/-sequelizeconnectionerror-self-signed-certificate – Burtis

Answer 5

Adding ?ssl=true should work to the end of the uri.

var conString = "pg://admin:guest@localhost:5432/Employees?ssl=true";

Piggish

Append ?sslmode=require instead of ?ssl=true Here are 2 references that talks about it 1) devcenter.heroku.com/articles/… 2) postgresql.org/docs/9.1/libpq-ssl.html – Whether

Answer 6

You can also use the ENVIRONMENT CONFIG VARIABLE 'PGSSLMODE' to 'require' via Heroku's web interface or CLI.

Case: Postgres dB set up as a Heroku add-on and attached to app on a Heroku Dyno.

Heroku provides some pretty good support on how to connect to one of its add-on databases; however, it unfortunately leaves out (or, I missed it) any mention of what do to enforce SSL since all Heroku dB tiers starting with Standard-0 enforces SSL by default.

Pylle

Thanks - had upgraded to Standard-0 and didn't see a warning that this would change SSL requirements, that caused a mild scare. – Lustig

Heroku recently send a warning saying it was going to force SSL. They included similar documentation to what was posted above. I followed those instructions and my app just crashed today, because of SSL issues. I added the PGSSLMODE config variable and everything is back to normal. Thanks! – Adnate

Answer 7

DialectOptions with SSL works, but you can also update the config var PGSSLMODE.

Alternatively, you can omit the ssl configuration object if you specify the PGSSLMODE config var: heroku config:set PGSSLMODE=no-verify.

See Heroku guide

Heroku announced this SSL change on their changelog here

Garner

Answer 8

While creating the pg client, this config fixed the issue for me. You can also read the detail doc written on the module's website here >>> https://node-postgres.com/features/ssl. Thank you.

new pg.client({
  connectionString,
  ssl: {
    rejectUnauthorized: false,
  },
})

Acrocarpous

great idea. I try it in the knex configuration file for the deploy branch but it didn't work. – Michaelmas

Answer 9

What worked for me was a combination of above answers and a comment(from @schybo)

let cloud_config = {
  username: process.env.DB_USERNAME,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOSTNAME,
  port: 5432,
  ssl: true,
  dialect: 'postgres',
  dialectOptions: {
    "ssl": {"require":true }
  }
};

Use both ssl: true, and dialectOptions: { "ssl": {"require":true }}

Comment on Sequelize issue which is also added to the docs.

Nagari

For me just needed dialectOptions – Vise

Answer 10

I faced the same issue again and again. The packages:

"pg": "^8.5.1",
"pg-hstore": "^2.3.3",
"sequelize": "^6.5.0",
"sequelize-cli": "^6.2.0"

I solved it by adding the following in the config.json file in the Sequelize.

"dialect": "postgres",
"dialectOptions": {
  "ssl": {
    "rejectUnauthorized": false
  }
}

Conk

Answer 11

const Pool = require("pg").Pool;

const proConfig = {
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
}

const pool = new Pool(proConfig);

module.exports = pool;

Melanism

Answer 12

Because node-Postgres enables SSL validation by default while free Heroku hosting doesn’t provide it automatically, you need to turn it off. disable SSL in Heroku:

heroku config:set PGSSLMODE=no-verify --app <app name>

https://dpletzke.medium.com/configuring-free-heroku-node-postgresql-hosting-with-knex-b0e97a05c6af

https://help.heroku.com/DR0TTWWD/seeing-fatal-no-pg_hba-conf-entry-errors-in-postgres

Michaelmas

Answer 13

If you are deploying Sails.js on Heroku, add the following to your database configuration:

ssl: {
  sslmode: 'require',
  rejectUnauthorized: false,
}

See this answer: https://mcmap.net/q/262854/-sailsjs-heroku-postgresql-error-self-signed-certificate

Leyva

Just wanted to know if there is any implication or security risk involved by adding the above snippet? @Leyva – Prorate

Answer 14

Just add a flag to the client initialisation:

Change

const conString = "pg://admin:guest@localhost:5432/Employees"
const client = new pg.Client(conString);
client.connect();

To

const conString = "pg://admin:guest@localhost:5432/Employees"
const client = new pg.Client({
  connectionString: process.env.DATABASE_URL,
  ssl: true
});
client.connect();

Clamor

Answer 15

setting ssl: true worked for me

let pg = require('pg');

let pgClient = new pg.Client({
    user: "admin",
    password: "guest",
    database: "Employees",
    port: 5432,
    host: "localhost",
    ssl: true
}); 
pgClient.connect();

Disaster

Answer 16

You have to add

ssl: true

in json connection

    {
        host: 'myHost.com',
        user: 'myUser',     
        password: 'myPassword',
        database: 'myDb',
        port: 5432,
        ssl: true
    }

Disbranch

Answer 17

just update your database/config.js file by allowing and requiring SSL

  production: {
    use_env_variable: 'DATABASE_URL',
    dialect: 'postgresql',
    ssl: true,
    dialectOptions: {
      ssl: { require: true },
    },
    logging: false,
  },

after this might run into another issue: nodejs - error self-signed certificate in If that's the case, add NODE_TLS_REJECT_UNAUTHORIZED='0' as an environment variable wherever you are running node or running node directly with NODE_TLS_REJECT_UNAUTHORIZED='0' node app.js

Dede

Answer 18

Mine was none of these solutions even though the error was the same, after trying all the above solutions for days to no avail I later figured out I added a semicolon (;) to the end of my connection string as in below;

DB_URL=pg://admin:guest@localhost:5432/Employees;

//instead of

DB_URL=pg://admin:guest@localhost:5432/Employees

the semicolon technically had added an extra character that isn't part of the configuration/authentication document hence wasn't recognized. A silly and avoidable mistake that can be made by anyone. Pay attention to details

Heaven