Node-postgres: AWS IAM Auth fails for Javascript version but not for native

[jhorowitz]

When I try to connect using require('pg'), auth fails with the error below. When I try to connect using require('pg').native, everything works great. I've reproduced this using both environment variables and programatic access.

I'm connecting my postgres RDS instance via a generated IAM password. To get an example of what the password looks like, if you have the aws cli installed, you can run aws rds generate-db-auth-token --hostname pg.example.com --port 5432 --region us-west-2 --username test. Otherwise, you can read about it in this short article.

Note:
I've replaced some personal information in the error such as DB name with REDACTED. I don't think any of the redacted info is important for diagnosing the issue, but if it is, I can reproduce the issue on a test instance and give a non-redacted error.

{ error: pg_hba.conf rejects connection for host "REDACTED", user "REDACTED", database "REDACTED", SSL off
    at Connection.parseE (/Users/joshuahorowitz/REDACTED/node_modules/pg/lib/connection.js:555:11)
    at Connection.parseMessage (/Users/joshuahorowitz/REDACTED/node_modules/pg/lib/connection.js:380:19)
    at Socket.<anonymous> (/Users/joshuahorowitz/REDACTED/node_modules/pg/lib/connection.js:120:22)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:601:20)
  name: 'error',
  length: 157,
  severity: 'FATAL',
  code: '28000',
  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: '427',
  routine: 'ClientAuthentication' }

[gdamjan]

I think you must enable SSL on the client connection, it seems your connection is "SSL off"

[jhorowitz]

There is literally no difference in the code aside from adding .native to the require import. Connecting with .native does not use SSL but works perfectly.

[sehrope]

The .native approach uses libpq under the covers which defaults to an SSL mode of "prefer" which will use SSL: https://www.postgresql.org/docs/current/libpq-ssl.html#LIBPQ-SSL-PROTECTION

With the pure-JS driver (i.e. regular pg), you need to explicitly include enabling ssl in your connection properties.

[jhorowitz]

I see. That makes sense. I've moved on to use a different authentication scheme for unrelated reasons so I'm not going to test it but that explains the issue perfectly. Thanks for the help! This was very enlightening.

I'd consider documenting this difference somewhere as many people will probably be switching to IAM auth as AWS works out the kinks.

[ekulabuhov]

Passing ssl: true resolves the problem. No need to pass actual certificates.

[madacol]

Very similar error for me

error: no pg_hba.conf entry for host "redacted", user "redacted", database "redacted", SSL off
    at Parser.parseErrorMessage (/home/redacted/node_modules/pg-protocol/dist/parser.js:241:15)
    at Parser.handlePacket (/home/redacted/node_modules/pg-protocol/dist/parser.js:89:29)
    at Parser.parse (/home/redacted/node_modules/pg-protocol/dist/parser.js:41:38)
    at Socket.<anonymous> (/home/redacted/node_modules/pg-protocol/dist/index.js:8:42)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:295:12)
    at readableAddChunk (_stream_readable.js:271:9)
    at Socket.Readable.push (_stream_readable.js:212:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

And when adding ssl: true the error is now Error: self signed certificate

But it works just fine with the .native approach, no error

[madacol]

I solved the Error: self signed certificate with:

ssl: {
    rejectUnauthorized: false
}

As explained in this thread https://github.com/brianc/node-postgres/issues/2009