Heroku/Postgres "ClientAuthentication" Error with Node and node-postgres

Question

I'm in the early stages of setting up an app for development on Heroku using Postgres. A simple version of the problem code in NodeJS is:

const {Client} = require('pg');
const client = new Client({connectionString: 'postgres://{username}:{password}@{host}:5432/{dbname}'});
client.connect();
const result = client.query('SELECT now()');
client.end();

connectionString being a copy of the string provided from the data.heroku.com's credentials pane, checked and rechecked. I can:

• connect using that connection string from a number of different local apps (e.g., DataGrip)
• use that code (with a different connection string) to connect to a local version of Postgres running in a Docker image

I can't:

• connect from a Node app on my local machine
• connect from a Node app deployed to Heroku

When the code fails against the remote db, node-postgres throws this error:

{
        "length": 168,
        "name": "error",
        "severity": "FATAL",
        "code": "28000",
        "file": "auth.c",
        "line": "496",
        "routine": "ClientAuthentication"
    }

Using Node v14.15.1, node-postgres ("pg") 8.5.1

Update: Probably also worth mentioning that I can't find a way to get this connection to fail from within Java… the slip is definitely somewhere in Node <-> node-postgres <-> [underlying postgres driver] <-> Heroku (i.e., the db is fine, the connection is fine)… but where?

Answer 1

With some assistance from Heroku support, I was able to find the solution based on https://help.heroku.com/MDM23G46/why-am-i-getting-an-error-when-i-upgrade-to-pg-8. "Solution 1" did not change anything for me, but "Solution 2" (using pg-connection-string) did work.

Fwiw it's worth, I'm also wrapping the SSL with a condition based on the NODE_ENV environment variable, so that the code will work both with the Heroku Postgres instance, but also with the local dev version of that same db. I.e.:

  // centralizing the logic for this so the 'useLocalDb' can be reused
  // in multiple functions in the module (in case the logic needs to change
  // later, among other reasons)
  const useLocalDb = process.env.USE_LOCAL_DB;
  
  // a short version of the working connection code
  const {Pool} = require('pg');
  const {parse} = require('pg-connection-string')
  const config = parse(process.env.DATABASE_URL)
  if (!useLocalDb) {
    config.ssl = {
      rejectUnauthorized: false
    }
  }
  const pool = new Pool(config);