Unable to connect using SSL

[siddhsql]

We have a Postgres 14 server that requires TLS to connect. we are able to connect to it using psql. Example:

PGPASSWORD=$(gcloud sql generate-login-token) psql \
"sslmode=verify-ca \
sslrootcert=/Users/xxx/keyfiles/dev-01.pem \
sslcert=/Users/xxx/keyfiles/dev-01-client-cert.pem \
sslkey=/Users/xxx/keyfiles/dev-01-client-key.pem \
hostaddr=x.x.x.xxx \
user=xxx \
dbname=xxx"

psql (14.7, server 14.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

xxx=>

but when we try to do that same thing using (as per the documentation):

PGUSER='xxx' \
PGHOSTADDR=x.x.x.x \
PGPASSWORD=xxx \
PGDATABASE=tutorial \
PGPORT=5432 \
PGSSLMODE=verify-ca \
PGSSLCERT=/Users/xxx/keyfiles/dev-01-client-cert.pem \
PGSSLKEY=/Users/xxx/keyfiles/dev-01-client-key.pem \
PGSSLROOTCERT=/Users/xxx/keyfiles/dev-01.pem \
node dist/app.js

where in app.js we have:

const client = new Client()

we get this error:

ERROR:  Error: The server does not support SSL connections
    at Socket.<anonymous> (/Users/xxxx/node/pg-test/node_modules/pg/lib/connection.js:76:37)
    at Object.onceWrapper (node:events:628:26)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

we are using version 8.10.0 of pg. Is this a bug in this library or is there something wrong we are doing? thanks.

[siddhsql]

its definitely a bug in this library. when we also set HOST in addition to HOSTADDR the error went away. However:

1. documentation does not say that HOST needs to be set IN ADDITION to HOSTADDR. here is the doc:

PGHOSTADDR behaves the same as the hostaddr connection parameter. This can be set instead of or in addition to PGHOST to avoid DNS lookup overhead.

2. we are getting a new error now :(

 Error: unable to verify the first certificate
    at TLSSocket.onConnectSecure (node:_tls_wrap:1540:34)
    at TLSSocket.emit (node:events:513:28)
    at TLSSocket._finishInit (node:_tls_wrap:959:8)
    at ssl.onhandshakedone (node:_tls_wrap:743:12) {
  code: 'UNABLE_TO_VERIFY_LEAF_SIGNATURE'
}

[charmander]

This library doesn’t support PGHOSTADDR, and the JS driver doesn’t support PGSSLCERT/PGSSLKEY/PGSSLROOTCERT environment variables. You need to pass them manually in the ssl property of the configuration object for now.

[dapeleg-dn]

Seems to duplicate https://github.com/brianc/node-postgres/issues/2723

[dapeleg-dn]

PR suggested: https://github.com/brianc/node-postgres/pull/2994

[dapeleg-dn]

PR is ready. Waiting for a maintainer to review and approve.

[laubonghaudoi]

@dapeleg-dn Will this PR fix https://github.com/brianc/node-postgres/issues/2558 ?

[adaboese]

My connection string looks as follows:

postgresql://postgres:XXX@0.0.0.0:5432/aimd?sslmode=require

What do I need to change to make this work?

also getting UNABLE_TO_VERIFY_LEAF_SIGNATURE error

[Zambonilli]

The original poster's problem is that GCP Cloud SQL generates a self signed certificate that does not include localhost as a CN but instead the randomly generated DNS name. However, to connect to the GCP Cloud SQL you use an IP and localhost is returned as the domain by Cloud SQL in the TLS dance.

So, in GCP using Cloud SQL, you end up with a valid CA but invalid CN and node-postgres treats the sslmode verify-ca the same as require or verify-full per https://github.com/brianc/node-postgres/blob/master/packages/pg/lib/connection-parameters.js#L27.

So, right now the only option is to use the no-verify sslmode or explicitly set rejectUnauthorized.

[Arthur-xu]

Hi team, I have same issue use pg to connect aws rds with ssl.

psql engine: 15.3 node: 20 pg: "8.11.3",

import { Pool } from 'pg';
 var pool1 = new Pool({
    connectionString: 'postgres://user:password@custom-doman-cname:port/db?ssl=true&sslmode=verify-ca&sslrootcert=./ca.pem'
  })

or 
new Pool({
            host: config.dbHost,
            database: config.dbName,
            user: config.dbUser,
            password: config.dbPassword,
            max: config.dbMaxConnections,
            ssl: {
              rejectUnauthorized: true,
              ca: fs.readFileSync(`${certPath}`).toString(),
            },
          })

None of them work at all. We will always hit error for connection string because seems like sslmode doesn't work.

Hostname/IP does not match certificate's altnames

Should it a bug? we do need to specifiy sslmode=verify-ca. Meanwhile, if i use psql or prisma/typeorm, it works well

[brianc]

Hey @Arthur-xu - sorry you're hitting that issue. It's not likely to be a bug with node-postgres but rather w/ your configuration, environment, version of node or something else. Node-postgres passes the ssl configuration [directly] to node's tls.connect method. Can you connect w/ tls.connect to the postgres instance at the port and host? Just make a script using tls.connect and see if it works? I'm happy to dig into this more but i'm unable to repro that on my side or really test since I don't have access to your env.

[Arthur-xu]

Hey @Arthur-xu - sorry you're hitting that issue. It's not likely to be a bug with node-postgres but rather w/ your configuration, environment, version of node or something else. Node-postgres passes the ssl configuration [directly] to node's tls.connect method. Can you connect w/ tls.connect to the postgres instance at the port and host? Just make a script using tls.connect and see if it works? I'm happy to dig into this more but i'm unable to repro that on my side or really test since I don't have access to your env.

Thank u @brianc , I changed rejectUnauthorized to false it works but its behavior becomes don't verify anything.