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?

Paul · Accepted Answer · 2021-03-07 10:32:14Z

5

Adding ssl: { rejectUnauthorized: false } when creating the client object fixed the issue for me. (source)

const client = new Client({
    connectionString: 'postgres://{username}:{password}@{host}:5432/{dbname}',
    ssl: { rejectUnauthorized: false }
});

Maybe try adding extra: { ssl: { rejectUnauthorized: false } } instead, if the other code does not fix anything (source)

answered Mar 7, 2021 at 10:32

Paul

3223 silver badges12 bronze badges

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

5 Comments

RiqueW Over a year ago

Hi, @Paul… unfortunately that doesn't work. Fwiw I can't get this connection fail if called from within Java, so I'm inclined to think that this isn't an SSL/cert issue. Come to think of it, that's a data point worth adding to the original post…

Paul Over a year ago

Hi @RiqueW. Do you mean JavaScript? Also, the string 'postgres://{username}:{password}@{host}:5432/{dbname}' is exactly the code you use, or you just wanted to hide the credentials?

RiqueW Over a year ago

Hey, @Paul… I mean Java itself, not Javascript, just a small utility program to test this connection. Interesting that when I say that I can connect from desktop clients, I think they're all using the JDBC driver, so there's that commonality.

RiqueW Over a year ago

And yeah, those are just obfuscated variables in the path. I'm initially using the Heroku provided URL,and have been playing with some small variations I thought might help, but so far: nothing

RiqueW Over a year ago

Hey @Paul… you're answer was _so _ close and ought to have worked, imo. I'm not sure why the Heroku support-based answer I've posted below using 'pg-connection-string' does the trick (but I'll take it for now)

RiqueW · Accepted Answer · 2021-03-10 14:35:15Z

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

answered Mar 10, 2021 at 14:35

RiqueW

2421 silver badge13 bronze badges

1 Comment

Tim Bogdanov Over a year ago

I don't understand where this would go in a node application. Could you offer some more explanation?

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related