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:[email protected]/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)

[RodeoAdrian]

Hello I have the same issue. I updated pg from 7.18.2 to 8.10.0 and I get the same error as @pantelis-karamolegkos

My code:

const db = new Client({
  use_env_variable: 'DATABASE_URL', // <--- Heroku env var
  logging: false,
  dialectOptions: {
    ssl: {
      require: true,
      rejectUnauthorized: false
    }
  }
})

trying to connect:

db.connect(err => {
  if (err) {
    logger.error(`Error connecting to database ${err.stack}`);
    process.exit(1);
  } else {
    ....
  }
});

I get the error Error connecting to database ${err.stack}

This seems like a bug @charmander , I've been looking for a solution for days, I even talked with heroku support and they are saying that pg package is the problem. I have the config as the documentations

[jetaggart]

@pantelis-karamolegkos I'm having this exact issue between App Runner and RDS. Did you figure anything out?

EDIT: I figured out that rds pg-15 forces SSL. You can turn this off on the database to go back to previous behavior but is not ideal. What's strange is SSL this seems to work fine from my localhost, it's only in the container running on app runner that I see the self-signed cert error.

[hikeeba]

Seeing the same issue when running in ECS against RDS PG15

[CaptainMack]

I'm having same issue as @jetaggart running RDS (pg-15) with ECS - I can connect fine from own computer, but node-pg in ECS container throws 'self signed certificate in certificate chain' error

[gregg-cbs]

I have this issue with Digital Ocean App Database .

[devfacet]

The following approach works for me on RDS (with default-postgres-15 option group which has ssl=1 and force_ssl=1):

1. Remove the sslmode from the connection string.
2. Download the RDS certificate bundle from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html

export const pool = new pg.Pool({
  connectionString: process.env.POSTGRES_URI,
  connectionTimeoutMillis: 20000,
  idleTimeoutMillis: 5000,
  max: 60,
  ssl: process.env.POSTGRES_SSL ? {
    ca: fs.readFileSync('path/to/global-bundle.pem').toString(),
  } : undefined,
})

[gregg-cbs]

The following approach works for me on RDS (with default-postgres-15 option group which has ssl=1 and force_ssl=1):

1. Remove the `sslmode` from the connection string.

2. Download the RDS certificate bundle from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html

export const pool = new pg.Pool({
  connectionString: process.env.POSTGRES_URI,
  connectionTimeoutMillis: 20000,
  idleTimeoutMillis: 5000,
  max: 60,
  ssl: process.env.POSTGRES_SSL ? {
    ca: fs.readFileSync('path/to/global-bundle.pem').toString(),
  } : undefined,
})

Do you know if using the certificate will speed the initial connection up?

Whenever my dev server reloads after file changes it takes long for the server to restart because the database connection is so slow. I got the connection to work without CA Cert but was wondering if it would make things faster.

[devfacet]

Do you know if using the certificate will speed the initial connection up?

It's pretty fast for me. I didn't test it without certificate.

[ksrtilak]

I am facing this same "Error: self signed certificate in certificate chain" with node-postgres version 8.7.0 when specifying sslmode in connection string as mentioned in the docs : connectionString: 'postgres://<user>:<password>@<host>:5432/<database>?sslmode=verify-full'

Please advise, as I need to specify sslmode.

PS: When I connect to a SSL enabled Postgres using Connection object (1) or Connection string without SSL mode(2), it works!

(1) Connection object way:

const connConfigs = { host: 'host', port: 5432, database: 'database', user: 'user' } connConfigs.ssl = { rejectUnauthorized: true, // sslmode: 'verify-full', // Is sslmode supported here? ca: fs.readFileSync('./ca.pem').toString(), key: fs.readFileSync('./key.pem').toString(), cert: fs.readFileSync('./cert.pem').toString() }

(2) Connection string way: (Not specifying sslmode)

connectionString: 'postgres://user:password@host:5432/database' connConfigs.ssl = { rejectUnauthorized: true, ca: fs.readFileSync('./ca.pem').toString(), key: fs.readFileSync('./key.pem').toString(), cert: fs.readFileSync('./cert.pem').toString() }

[anagpure28]

Error: self-signed certificate at TLSSocket.onConnectSecure (node:_tls_wrap:1674:34) at TLSSocket.emit (node:events:518:28) at TLSSocket._finishInit (node:_tls_wrap:1085:8) at ssl.onhandshakedone (node:_tls_wrap:871:12) { code: 'ESOCKET', command: 'CONN' } I'm facing the same issue while sending the emails

[aashirdigital]

r Email not sent 0|server | Error: self-signed certificate 0|server | at TLSSocket.onConnectSecure (node:_tls_wrap:1538:34) 0|server | at TLSSocket.emit (node:events:513:28) 0|server | at TLSSocket._finishInit (node:_tls_wrap:952:8) 0|server | at ssl.onhandshakedone (node:_tls_wrap:733:12) { 0|server | code: 'ESOCKET', 0|server | command: 'CONN' 0|server | }

I m facing issue while sending email. Hw to fix this anyone?