'Self signed certificate' error during query the Heroku hosted Postgres database from the Node.js application
My Node.js app is able to work with local Postgres database via npm pg
module.
I can connect to the Heroku hosted Postgres database (free Hobby Dev plan) via command line with heroku pg:psql
command as well.
But when my Node.js app is trying to query to Heroku hosted Postgres database I am receiving an self signed certificate
error.
Here is the output with self signed certificate
error:
(node:2100) UnhandledPromiseRejectionWarning: Error: self signed certificate
at TLSSocket.onConnectSecure (_tls_wrap.js:1051:34)
at TLSSocket.emit (events.js:189:13)
at TLSSocket._finishInit (_tls_wrap.js:633:8)
(node:2100) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:2100) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
D:\MY\DEV\PROJECTS\AdsSubscribeBot\test.js:57
if (err) throw err;
^
Error: Connection terminated unexpectedly
at Connection.con.once (D:\MY\DEV\PROJECTS\AdsSubscribeBot\node_modules\pg\lib\client.js:264:9)
at Object.onceWrapper (events.js:277:13)
at Connection.emit (events.js:189:13)
at Socket.<anonymous> (D:\MY\DEV\PROJECTS\AdsSubscribeBot\node_modules\pg\lib\connection.js:76:10)
at Socket.emit (events.js:194:15)
at TCP._handle.close (net.js:597:12)
Simpliest way to reproduce this error is to try use the sample code to connecting in Node.js from Heroku devcenter: https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js
Here is the sample of the code that causes self signed certificate
error:
const connectionString = 'postgres://USERNAME:PASSWORD@HOST:PORT/DB_NAME';
const { Client } = require('pg');
const client = new Client({
connectionString: connectionString,
ssl: true
});
client.connect();
client.query('SELECT * FROM users;', (err, res) => {
if (err) throw err;
for (let row of res.rows) {
console.log(JSON.stringify(row));
}
client.end();
});
Maybe someone has faced the same issue and know the way how to solve it.
Thanks in advance for any help.
Check you pg config. It sounds like you are using pg 8 which deprecates
implicit disabling of certificate verification (as you have in your config where ssl is set to true but no ssl configuration is provided). Specify rejectUnauthorized: true
to require a valid CA or rejectUnauthorized: false
to explicitly opt out of MITM protection.
You can do so where you set up your pg config as follows
const client = new Client({
connectionString: connectionString,
ssl: { rejectUnauthorized: false }
})
If anyone is still seeing issues with this after appending the SSL object to the Client object and they are using a connection string. Make sure that you don't have an ssl parameter in the connection string. If you are working with Digital Ocean this parameter is included in the generated connection string.
This is how Digital Ocean formats their connection strings by default
postgres://USERNAME:PASSWORD@HOST:PORT/DB_NAME:25060/defaultdb?&sslmode=require
Below is a variation of the accepted answer using Knex.js. Tested on Heroku.
const parse = require('pg-connection-string').parse;
const pgconfig = parse('your-pg-connection-string');
pgconfig.ssl = { rejectUnauthorized: false };
const knex = Knex({
client: 'pg',
connection: pgconfig,
});
Thanks to @samkhan27. I just added ssl: { rejectUnauthorized: false }
My full code:
const db = new Sequelize(
process.env.DATABASE_URL ||
`postgres://postgres:w2w2@localhost:5432/${databaseName}`,
{
logging: false,
ssl: { rejectUnauthorized: false } //solved the problem with self signed sertificate
}
)
To get this to work, I had to add:
ssl: { rejectUnauthorized: false }
but also add this to the environment:
NODE_TLS_REJECT_UNAUTHORIZED=0