65

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.

akzhar
  • 653
  • 1
  • 5
  • 5
  • 2
    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 Apr 08 '20 at 16:47
  • 1
    @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 Apr 09 '20 at 11:46

5 Answers5

112

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 }
})
samkhan27
  • 1,280
  • 1
  • 6
  • 7
  • 6
    I had to do something like this in order to work for both production and my local machine: `ssl: this.isProduction() ? { rejectUnauthorized: false } : false` – Renato Pereira Dec 31 '20 at 06:12
  • 9
    For me, only setting the Environment variable worked: `PGSSLMODE=no-verify` (see Heroku docs: https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js) – Freewalker Mar 02 '21 at 05:27
  • Try using 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') }); for Knex pg connection – sathya seelan Mar 12 '21 at 09:41
  • 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`. – sçuçu Apr 22 '21 at 03:29
  • 3
    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. – Emilia Bopp Apr 14 '22 at 15:03
15

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

AvidDabbler
  • 551
  • 6
  • 19
  • 3
    Absolutely required: lost 3 hours of my life before I came across this. – Femi Oct 01 '21 at 14:37
  • 2
    Ah. yes. I was using Heroku. But the same issue existed. I had ?ssl=true tacked on the end. Thanks! – bentedder Nov 04 '21 at 17:40
  • 1
    Same issue migrating from Sequelize `6.7.0` to `6.12.0`, removing the `sslmode` parameter solved the issue. – Damien Dec 20 '21 at 18:15
12

To get this to work, I had to add:

ssl: { rejectUnauthorized: false }

but also add this to the environment:

NODE_TLS_REJECT_UNAUTHORIZED=0
Tom Söderlund
  • 4,743
  • 4
  • 45
  • 67
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,
});
sebbarg
  • 121
  • 1
  • 6
  • 1
    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. – nickang Jul 12 '21 at 15:27
  • @nickang - were other methods not working because you had `ssl` or `sslmode` in your db connection string? https://stackoverflow.com/a/69994711/1006183 – Matt Sanders Nov 16 '21 at 19:01
3

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
}

)

Axel
  • 57
  • 5