self signed certificate in certificate chain

[xiaoshimimi]

Node.js version: 14.17.0
pg version: 8.6.0

other related packages:

        "pg-connection-string": "^2.5.0",
        "pg-pool": "^3.3.0",
        "pg-protocol": "^1.5.0",

code:

const options = {
  connectionString: 'postgres://username:password@host:port/dbname?sslmode=verify-full',
  connectionTimeoutMillis: 50000,
  query_timeout: 50000,
  ssl: {
    ca: <loaded from file>,
    rejectUnauthorized: false
  }
}

const client = new pg.Client(options)
try {
    await client.connect()
  } catch(err) {
    if (err) {
      console.error('failed to connect to pg client', err)
      process.exit(1)
    }
  }

output is as following:

failed to connect to pg client Error: self signed certificate in certificate chain
    at TLSSocket.onConnectSecure (_tls_wrap.js:1507:34)
    at TLSSocket.emit (events.js:376:20)
    at TLSSocket._finishInit (_tls_wrap.js:932:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:706:12) {
  code: 'SELF_SIGNED_CERT_IN_CHAIN'
}

workable with NODE_TLS_REJECT_UNAUTHORIZED=0, but it is not suggested to program security.

[xiaoshimimi]

I saw the document requires cert/key for connection, but it is not reasonable to ask for cert/key when client trying to connect postgresql. cert/key is used in server to setup ssl. client side only need a ca.

link: https://node-postgres.com/features/ssl

ssl: {
    rejectUnauthorized: false,
    ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
    key: fs.readFileSync('/path/to/client-key/postgresql.key').toString(),
    cert: fs.readFileSync('/path/to/client-certificates/postgresql.crt').toString(),
  },

[charmander]

@xiaoshimimi Only ca is required for verify-full. key and cert are for client certificate authentication. You definitely shouldn’t specify both rejectUnauthorized: false and ca. Do other PostgreSQL clients (e.g. psql) connect successfully with sslmode=verify-full and the same CA certificate?

[NicholasIoanJones]

I've been struggling with this for a couple of days.
I updated pg 8.3.0 -> 8.6.0 and this self-sign error occurred.
May or may not be related but I also started getting SSL not supported errors running tests against my local database.
Both use sslmode=require

I reverted to 8.3.0 and both errors still occur (scratches head).

My database is hosted in a secure environment and they use self-signed certificates which means I need to be able to connect over SSL without certificate verification, even though that is not ideal.
It is really hard to debug because I cannot connect to that database outside of building and deploying a container for the node app.
The local issue is resolved however when I force revert to pg-connection-string 2.3.0 in yarn.lock. Looks like the upgrade of node-postgres caused the upgrade to pg-connection-string 2.5.0 which doesn't revert just by going back to 8.3.0 - understandable I guess. Unfortunately a secondary error on the database means I can't prove that on my app for a couple of hours.

Is there something in 2.5.0 which has explicitly caused these changes?

[NicholasIoanJones]

I think I understand the problem I'm experiencing and just looking for guidance on the correct solution @charmander

I'm using sslmode=require in my connection strings and in the js connecting to the pool I specify
ssl: (CONFIG.config_environment !== 'development') ? { rejectUnauthorized: false } : false

Looking at the change to pg-connection-string in 8.6.0 it seems that require now forces ssl:true and ignores any other parameters passed in (such as rejectUnauthorized)
That explains my symptoms

1. local tests get 'database does not support SSL' (true but never used to be a problem. I'm not sure how the connection-string parsing and config passed to Pool(configuration are meant to work together, but note that ssl is true if (config.ssl === 'true' || config.ssl === '1') but is only false if config.ssl === '0' [not checking config.ssl ==='false'])
2. production environments fail due to the self signed database certs (because the js parameters appear to be ignored of sslmode=require
   I also see that there is now a sslmode=no-verify which seems to do what I need in production.

My app is now working with pg-connection-string regressed to 2.3.0 but I don't want to get locked in so my question is:
Are these changes working as designed ( in which case I will use no-verify and disable as appropriate ) or are these impacts in fact bugs which will be corrected ( in which case I will stay locked in to 2.3.0 pending a fix).

Thanks for any guidance

[charmander]

@NicholasIoanJones If all you need to specify is rejectUnauthorized: false, you can omit ssl from the configuration object and switch between sslmode=no-verify in production and no sslmode in development.

[xiaoshimimi]

@xiaoshimimi Only ca is required for verify-full. key and cert are for client certificate authentication. You definitely shouldn’t specify both rejectUnauthorized: false and ca. Do other PostgreSQL clients (e.g. psql) connect successfully with sslmode=verify-full and the same CA certificate?

@charmander Yes, I connect successfully with same ca with pgAdmin4 client.

I also tried to remove "rejectUnauthorized: false", still same result.

[charmander]

@xiaoshimimi Oh, I forgot about this bug: try removing sslmode from your connection string and leaving only the ssl: { ca: … } in the configuration object.

[xiaoshimimi]

@charmander Cool~ it works. Thank you ~ But, you said that is a bug. so maybe we still need sslmode in the connection string in future release? or adding sslmode=xxx in connection string is a bug?

[charmander]

@xiaoshimimi You won’t need to add sslmode in a future release; specifying ssl is perfectly supported. The bug is that sslmode from the connection string isn’t merged with ssl and overwrites it instead.

[xiaoshimimi]

@charmander Great~ it works! is this a work around?

[lornajane]

I'm also running into this, using PostgreSQL as a service, and it includes a ?sslmode=require in its connection params, which means that my code always fails since I can't pass in the cert.

[pantelis-karamolegkos]

I am trying to connect to an AWS RDS instance that forces SSL (rds.force_ssl = 1) in its parameter group.

Here is the code

const { Client } = require('pg')
const fs = require('fs')

const client = new Client({
  client: 'postgresql',
  connectionString: process.env.DATABASE_URL,
  ssl: { 
    ca: fs.readFileSync('global-bundle.pem').toString(),
},
})

client.connect((err) => {
  if (err) {
    console.error('connection error', err.stack)
  } else {
    console.log('connected')
  }
})

I then do

export DATABASE_URL=postgres://myuser:mypassword@myrds.123456789.us-east-1.rds.amazonaws.com/postgres?ssl=true

run the above code which fails with

connection error Error: self signed certificate in certificate chain
    at TLSSocket.onConnectSecure (node:_tls_wrap:1530:34)
    at TLSSocket.emit (node:events:526:28)
    at TLSSocket._finishInit (node:_tls_wrap:944:8)
    at TLSWrap.ssl.onhandshakedone (node:_tls_wrap:725:12)