'Self signed certificate' error during query the Heroku hosted Postgres database from the Node.js application

Question

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.

The only workaround I have found is to set NODE_TLS_REJECT_UNAUTHORIZED=0 in your environment. I won't post it as an answer because it is a hack and insecure, but hopefully someone will post an actual solution at some point. — Rick Mogstad
– Rick Mogstad, Commented Apr 8, 2020 at 16:47
@RickMogstad Thanks, but yes it is a hack :) But I would like to know the reason. Unfortunately, I can't create a ticket to Heroku support team because I use a free plan... :) So the only option is to ask community. — akzhar
– akzhar, Commented Apr 9, 2020 at 11:46

samkhan27 · Accepted Answer · 2020-04-09 16:28:01Z

115

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

answered Apr 9, 2020 at 16:28

samkhan27

1,3101 gold badge8 silver badges7 bronze badges

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

5 Comments

Renato Pereira Over a year ago

I had to do something like this in order to work for both production and my local machine: ssl: this.isProduction() ? { rejectUnauthorized: false } : false

Freewalker Over a year ago

For me, only setting the Environment variable worked: PGSSLMODE=no-verify (see Heroku docs: devcenter.heroku.com/articles/…)

sathya seelan Over a year ago

Try using <code> const _connectionDetails = { host: process.env.PG_HOST, user: process.env.PG_USERNAME, password: process.env.PG_PASSWORD, database: process.env.PG_DB, ssl: {rejectUnauthorized: false} } const connection = knex({ client: 'pg', connection: _connectionDetails, debug: false//(process.env.APP_ENV !== 'production') });</code> for Knex pg connection

sçuçu Over a year ago

Should we also set requestCert option, sibling to the rejectUnauthorized, to true as well, since its doc says rejectUnauthorized has an effect only if requestCert is true and requestCert has default value of false.

Emilia Bopp Over a year ago

I find it amazing that pretty much every answer here suggests this insecure hack that opens the developer up to a man-in-the-middle attack.

Tom Söderlund · Accepted Answer · 2021-12-27 13:50:50Z

19

To get this to work, I had to add:

ssl: { rejectUnauthorized: false }

but also add this to the environment:

NODE_TLS_REJECT_UNAUTHORIZED=0

answered Dec 27, 2021 at 13:50

Tom Söderlund

4,7874 gold badges52 silver badges74 bronze badges

4 Comments

hdoitc Over a year ago

it's bad desicion...

Sergio Toledo Piza Over a year ago

@hdoitc it only worked for me now using this, why is it a bad decision?

Keith Davidson Over a year ago

@SergioToledoPiza it is insecure, as it is specifying to allow middle man attacks.

thisismydesign Over a year ago

This is what I needed on Heroku using NestJS and TypeORM

AvidDabbler · Accepted Answer · 2021-06-21 00:26:21Z

18

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

edited Jun 21, 2021 at 0:26

answered Jun 21, 2021 at 0:19

AvidDabbler

6618 silver badges21 bronze badges

3 Comments

Femi Over a year ago

Absolutely required: lost 3 hours of my life before I came across this.

bentedder Over a year ago

Ah. yes. I was using Heroku. But the same issue existed. I had ?ssl=true tacked on the end. Thanks!

Damien Over a year ago

Same issue migrating from Sequelize 6.7.0 to 6.12.0, removing the sslmode parameter solved the issue.

sebbarg · Accepted Answer · 2021-02-28 12:20:43Z

7

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

answered Feb 28, 2021 at 12:20

sebbarg

1311 silver badge7 bronze badges

2 Comments

nickang Over a year ago

No other methods I've tried works. This is the only one that works for me, but it did bring in yet another dependency... remember to install npm install --save pg-connection-string for this to work.

Matt Sanders Over a year ago

@nickang - were other methods not working because you had ssl or sslmode in your db connection string? stackoverflow.com/a/69994711/1006183

Axel · Accepted Answer · 2021-03-24 00:57:01Z

4

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
}

)

answered Mar 24, 2021 at 0:57

Axel

675 bronze badges

Collectives™ on Stack Overflow

'Self signed certificate' error during query the Heroku hosted Postgres database from the Node.js application

5 Answers 5

5 Comments

4 Comments

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

4 Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related