Node.js, PostgreSQL error: no pg_hba.conf entry for host
node.jsPostgresqlExpressHerokunode.js Problem Overview
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?
node.js Solutions
Solution 1 - node.js
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
Solution 2 - node.js
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
}
}
Solution 3 - node.js
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
}
},
});
Solution 4 - node.js
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://...');
Solution 5 - node.js
Adding ?ssl=true
should work to the end of the uri.
var conString = "pg://admin:guest@localhost:5432/Employees?ssl=true";
Solution 6 - node.js
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.
Solution 7 - node.js
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
Solution 8 - node.js
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.
Solution 9 - node.js
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,
},
})
Solution 10 - node.js
const Pool = require("pg").Pool;
const proConfig = {
connectionString: process.env.DATABASE_URL,
ssl: {
rejectUnauthorized: false
}
}
const pool = new Pool(proConfig);
module.exports = pool;
Solution 11 - node.js
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
}
}
Solution 12 - node.js
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
Solution 13 - node.js
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();
Solution 14 - node.js
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();
Solution 15 - node.js
You have to add
ssl: true
in json connection
{
host: 'myHost.com',
user: 'myUser',
password: 'myPassword',
database: 'myDb',
port: 5432,
ssl: true
}
Solution 16 - node.js
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
Solution 17 - node.js
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