Authentication failed with hostname

[aarontropy]

Hi,
I'm having an odd (at least to me) authentication problem.

ubuntu version: 16.04
postgres version: 9.6
node version: 6.9.4
pg-promise version: 5.6.1
pg version: 5.1

I have a user/pass which I know is correct because I ALTER USER'd then checked locally with psql -U ... (turning off local trust). However authentication fails when using pg-promise, but not pg.

Test code:

const config = { 
  host: 'my.database.hostname.com',
  port: 5432,
  database: 'mydb',
  user: 'myuser',
  pass: 'myuserpass',
  poolSize: 10 
}
const PgPromise = require('pg-promise');
const pgp = PgPromise();
const db = pgp(config);

db.query('SELECT $1::int AS number', ['1'])
  .then(result => console.log(result.number) )
  .catch(err => console.error('error running query', err) );

When I run the code as above, I get an authentication error. From the postgres logs:

2017-03-18 00:35:21.638 UTC [4329] myuser@mydb FATAL:  password authentication failed for user "myuser"
2017-03-18 00:35:21.638 UTC [4329] myuser@mydb DETAIL:  Password does not match for user "myuser".
	Connection matched pg_hba.conf line 103: "host	all		all		all		md5"

And the js output:

error running query { error: password authentication failed for user "myuser"
    at Connection.parseE (/srv/services/accounting/node_modules/pg/lib/connection.js:539:11)
    at Connection.parseMessage (/srv/services/accounting/node_modules/pg/lib/connection.js:366:17)
    at Socket.<anonymous> (/srv/services/accounting/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:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    at TCP.onread (net.js:548:20)
  name: 'error',
  length: 114,
  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: '307',
  routine: 'auth_failed' }

HOWEVER, if I change my.database.hostname.com to localhost I connect without issue and everything is happy.

At first, I thought my problem might be related to brianc/node-postgres#1000, but I think I have the right package versions.

Then I thought that I just wasn't understanding postgres authentication and the pg_hba.conf. However, the pg_hba.conf line above really does look correct AND, when I run the test with the node-postgres package alone:

var pg = require('pg');
var conString = "postgres://myuser:myuser_pass@my.database.hostname.com/mydb";

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 connect just fine with either my.database.hostname.com or localhost

Am I missing something?

[vitaly-t]

When you run the pg directly, are you still using version 5.1 or a different version?

[aarontropy]

Same version.

[vitaly-t]

In that case it is hardly possible. This library passes the connection string directly into the pg without changes. See https://github.com/vitaly-t/pg-promise/blob/master/lib/connect.js#L11

Something else must be missing that you didn't disclose.

I can't think of single reason to even start looking into it. And it is certainly not reproducible.

You will have to diagnose the issue on your side a little better to see what is really going on.

[aarontropy]

OK, I will dig a little deeper. Thanks.

[vitaly-t]

I would start by adding:

console.log('CONNECTION:', ctx.cn);

right above this line: https://github.com/vitaly-t/pg-promise/blob/master/lib/connect.js#L11

...to see that the library executes precisely in the same way as you do separately with the pg module.

[vitaly-t]

Oh for cry-sake...

const config = { 
  host: 'my.database.hostname.com',
  port: 5432,
  database: 'mydb',
  user: 'myuser',
  pass: 'myuserpass',
  poolSize: 10 
}

The config expects password, not pass. Picks up correctly from the connection string, obviously, and ignores when connecting to the localhost, if it is not needed.

[aarontropy]

GAH! apologies. Thank you for taking a look.