Connection intermittently made, rejects connection with password authentication failure notice

[traegerp]

OSX 10.11.4
PG 9.4.8
Using pg module.

var pg = require('pg');
var conString = "postgres://postgres:REDACTED@localhost/testDb";

pg.connect(conString, function(err, client, done) {
  if(err) {
    return console.error('error fetching client from pool', err);
  }
  client.query('SELECT $1::int AS number', ['1'], function(err, result) {
    //call `done()` to release the client back to the pool 
    done();

    if(err) {
      return console.error('error running query', err);
    }
    console.log(result.rows[0].number);
    //output: 1 
  });
});

I get the following error:

error fetching client from pool { error: password authentication failed for user "postgres"

Then if I run this code with node - say - 10-15 times, it'll work once out of those attempts.

I've reinstalled postgres and made sure the passwords match. I tried performing an alter user for the password. I tried killing all open connections with pg_terminate_backend(pid). Still fails. Any help would be appreciated. Everything was working with this prior to upgrading to El Capitan, is El Capitan not supported?

[traegerp]

Also tried altering the connection string to postgres://[username]:[password]@[host]:[port]/[databasename] format with no avail by adding the 5432 port which I confirmed via pgadminIII it is using.

[langpavel]

do you connect to postgres from command line simply by typing psql to console (without password)? Try keep connection string empty or null. Do you try change username in postgres? Code looks OK for me except if you receive error you should call done(err) (in very beginning of client.query callback) - this should prevent client to be returned to pool, instead it should be destroyed.

[langpavel]

And if I write this code, I will write it as this:

var pg = require('pg');
var conString = 'postgres://postgres:REDACTED@localhost/testDb';

pg.connect(conString, function(err, client, done) {
  if (err) {
    console.error('error fetching client from pool', err);
    return done && done(err); // not sure...
  }
  client.query('SELECT $1::int AS number', [1], function(err, result) {
    if (err) {
      console.error('error running query', err);
      return done(err);
    }
    console.log(result.rows[0].number);
    //output: 1 
    done();
  });
});

[traegerp]

I tried the above code, I get the same error message. I am able to connect to psql via pgadminIII and the SQL shell without any problem. What is weird to me is the fact that if I run this code 20 times or so, it'll eventually work but on every other attempt it says error fetching client from pool { error: password authentication failed for user "postgres"

[traegerp]

Here is the output from the console:

Patricks-iMac:pg traegerp$ node test.js
error fetching client from pool { error: password authentication failed for user "postgres"
at Connection.parseE (/Users/traegerp/Desktop/5/pg/node_modules/pg/lib/connection.js:539:11)
at Connection.parseMessage (/Users/traegerp/Desktop/5/pg/node_modules/pg/lib/connection.js:366:17)
at Socket. (/Users/traegerp/Desktop/5/pg/node_modules/pg/lib/connection.js:105:22)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:172:18)
at Socket.Readable.push (_stream_readable.js:130:10)
at TCP.onread (net.js:542:20)
name: 'error',
length: 97,
severity: 'FATAL',
code: '28P01',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'auth.c',
line: '285',
routine: 'auth_failed' }

then just so happens on my 10th try this time:

Patricks-iMac:pg traegerp$ node test.js
1

I get the correct output. Why would it work some of the time and not others? I am beginning to think this is not a password issue because I am not changing anything in the connection string or the code. I am just executing it again and again until it works....

[langpavel]

weird.. try install pg-native and var pg = require('pg').native;

Try to change password, then create new user with different password...

I have my own wrapper here using this in background but not experiencing trouble.. You can also try @vitaly-t library pg-promise, both abstracting out possible invalid code structuring...

[traegerp]

I'll give that a try and report back to you on my findings

[langpavel]

Going sleep, good luck

[traegerp]

I am able to get pg-native to work, I am hoping though for a solution in which I am able to use the regular pg library as I do not want to have to re-write a large project to use pg-native. Any suggestions on the above issue? Due to the fact that pg-native works, the sql-shell works, and pgadminIII works, I am venturing the issue lies with the pg-module?

[vitaly-t]

@traegerp

I do not want to have to re-write a large project to use pg-native

One doesn't really have to change anything when switching over to pg-native, unless you are doing something very specific.

To your original problem - which versions of Node.js and node-postgres are you using? There was an authentication-related issue with 6.0.0 when it came out: #1000

[traegerp]

I am using 6.x for node. It looks like the pg module I am using may be older and does not have this update. I'll update pg to see if that fixes the issue as the issue you linked to seems very similar to what I am experiencing. Thanks!

[andywhite37]

I am also seeing this intermittent "password authentication failed for user..." error, starting as of a few days ago. About 1 in 10-20 attempts succeed, and the rest fail with this error. I am seeing it with the postgrator v2.8.0 tool. I believe this is using require("pg.js").

https://github.com/rickbergfalk/postgrator/blob/master/lib/create-common-client.js#L69-L101

I am on node 6.1.

We've had the database running for several years, and the credentials have not changed. Other tools and our apps are able to connect fine (psql, Postico, pgAdmin3, etc.).

Edit: sorry, looking at your #1000 issue, I will try updating pg to at least 4.5.5 to try the Buffer fix.

[brianc]

Looks like there might have been some confusion around versions & upgrading with node & pg versions - please feel free to open a new issue if this isn't fixed but AFAICT this looks like it's been resolved in y'all's environments? Sorry for any headache this has caused!

[FarhanAhmad212]

@traegerp : I am facing same problem. out 15-10 times one time i am able get data from Postgres db . how did you resolve this ...
i am using node v7.10.0 (Node 6+) and pg v4.5.5.
i will be very thankful to you........

[FarhanAhmad212]

it work after adding this
host all all 0.0.0.0/0 md5
in pg_hba.conf file.

[langpavel]

@FarhanAhmad212 This is very bad. You should try more secure way.

[AndreBaltazar8]

I encountered intermittent password authentication failure today, and it was due to my hostname resolving to multiple IPs with different databases (and different username/passwords). While debugging I used dns.lookup and found out that there were multiple IPs on that hostname.

Not sure if this was the OP's issue and I know this issue is old but I'm just messaging here as it might help someone debug if they ever find a similar situation.