[SSL] Connecting... error: connection requires a valid client certificate

[andreafspeziale]

Hello guys and thanks for your hard work.

I've managed to create a local development environment which uses PSQL and self-signed certificates.
(Some hints here)

My docker-compose

version: '3.8'

services:
  psql:
    image: bitnami/postgresql:latest
    ports:
      - 5432:5432
    volumes:
      - psql-data:/bitnami/postgresql
      - ./certs/ca.crt:/opt/bitnami/postgresql/certs/ca.crt
      - ./server/server.crt:/opt/bitnami/postgresql/certs/server.crt
      - ./server/server.key:/opt/bitnami/postgresql/certs/server.key

    environment:
      BITNAMI_DEBUG: 'true'
      # basic
      POSTGRESQL_USERNAME: my_user
      POSTGRESQL_PASSWORD: password123
      POSTGRESQL_DATABASE: my_database
      # ssl
      POSTGRESQL_ENABLE_TLS: 'yes'
      POSTGRESQL_TLS_CERT_FILE: /opt/bitnami/postgresql/certs/server.crt
      POSTGRESQL_TLS_KEY_FILE: /opt/bitnami/postgresql/certs/server.key
      POSTGRESQL_TLS_CA_FILE: /opt/bitnami/postgresql/certs/ca.crt

volumes:
  psql-data:

My snippet code:

import pgp from 'pg-promise'
import fs from 'fs'
import path from 'path'

// eslint-disable-next-line @typescript-eslint/no-floating-promises
;(async (): Promise<void> => {
  try {
    const HOST = 'localhost'
    const PORT = 5432
    const USER = 'my_user'
    const DATABASE = 'my_database'

    const dbClient = pgp()({
      host: HOST,
      port: PORT,
      database: DATABASE,
      user: USER,
      ssl: {
        rejectUnauthorized: false,
        ca: fs.readFileSync(path.join(__dirname, '/client/ca.crt')).toString(),
        cert: fs
          .readFileSync(path.join(__dirname, '/client/client.crt'))
          .toString(),
        key: fs
          .readFileSync(path.join(__dirname, '/client/client.key'))
          .toString(),
      },
    })

    console.log('Connecting...')

    const connection = await dbClient.connect()

    console.log('Connected')

    console.log('Terminating...')

    connection.done()

    console.log('Terminated')

    process.exit(0)
  } catch (error) {
    // eslint-disable-next-line no-console
    console.error(error)
    process.exit(1)
  }
})()

The problem is that even If I'm able to successfully verify the certs:

openssl verify -CAfile src/client/ca.crt -purpose sslclient src/client/client.crt => src/client/client.crt: OK

and even If I'm able to successfully connect to the PSQL server by command line:

psql 'host=localhost port=5432 dbname=my_database user=my_user sslmode=verify-full sslrootcert=./certs/ca.crt sslkey=./src/client/client.key sslcert=./src/client/client.crt'
psql (13.1, server 11.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

my_database=>

I have the following error running the upper code snippet:

[nodemon] starting `ts-node src/index.ts`
Connecting...
error: connection requires a valid client certificate
    at Parser.parseErrorMessage (/Users/andrea/Repository/***/psql-ssl-poc/node_modules/pg-protocol/src/parser.ts:357:11)
    at Parser.handlePacket (/Users/andrea/Repository/***/psql-ssl-poc/node_modules/pg-protocol/src/parser.ts:186:21)
    at Parser.parse (/Users/andrea/Repository/***/psql-ssl-poc/node_modules/pg-protocol/src/parser.ts:101:30)
    at TLSSocket.<anonymous> (/Users/andrea/Repository/***/psql-ssl-poc/node_modules/pg-protocol/src/index.ts:7:48)
    at TLSSocket.emit (events.js:314:20)
    at TLSSocket.EventEmitter.emit (domain.js:486:12)
    at addChunk (_stream_readable.js:304:12)
    at readableAddChunk (_stream_readable.js:280:9)
    at TLSSocket.Readable.push (_stream_readable.js:219:10)
    at TLSWrap.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 109,
  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: '385',
  routine: 'ClientAuthentication'
}
[nodemon] app crashed - waiting for file changes before starting...

Thanks in advance!

[charmander]

Which version of pg are you using, via pg-promise?

Note: it’s probably best not to set rejectUnauthorized: false if you’re aiming for sslmode=verify-full.

[andreafspeziale]

Thanks @charmander, I thought it was conflicting by checking a self signed cert. I will change it to true but the error still persists.

Here is my package.json

{
  "name": "psql-ssl-poc",
  "version": "1.0.0",
  "description": "Testing SSL connection with local PSQL instance",
  "main": "index.js",
  "scripts": {
    "start": "nodemon --watch 'src/**/*.ts' --exec 'ts-node' src/index.ts"
  },
  "keywords": [
    "psql",
    "ssl",
    "ts"
  ],
  "author": "Andrea Speziale",
  "license": "ISC",
  "devDependencies": {
    "@types/node": "^14.14.6",
    "nodemon": "^2.0.6",
    "ts-node": "^9.0.0",
    "typescript": "^4.0.5"
  },
  "dependencies": {
    "pg-promise": "^10.7.1"
  }
}

[charmander]

"pg-promise": "^10.7.1"

Some of the versions of pg-promise this can match depend on pg 8.4.1, which is affected by #2392. You need pg 8.5.0 or later, meaning pg-promise 10.7.4 or later. Can you check your package-lock.json for the specific versions in use?

[andreafspeziale]

Thanks for the hint! In fact it is actually using:

"pg": {
      "version": "8.4.1",
      "resolved": "https://registry.npmjs.org/pg/-/pg-8.4.1.tgz",
      "integrity": "sha512-NRsH0aGMXmX1z8Dd0iaPCxWUw4ffu+lIAmGm+sTCwuDDWkpEgRCAHZYDwqaNhC5hG5DRMOjSUFasMWhvcmLN1A==",
      "requires": {
        "buffer-writer": "2.0.0",
        "packet-reader": "1.0.0",
        "pg-connection-string": "^2.4.0",
        "pg-pool": "^3.2.1",
        "pg-protocol": "^1.3.0",
        "pg-types": "^2.1.0",
        "pgpass": "1.x"
      }
    },

I've updated it in my POC and now it is:

"pg": {
      "version": "8.5.1",
      "resolved": "https://registry.npmjs.org/pg/-/pg-8.5.1.tgz",
      "integrity": "sha512-9wm3yX9lCfjvA98ybCyw2pADUivyNWT/yIP4ZcDVpMN0og70BUWYEGXPCTAQdGTAqnytfRADb7NERrY1qxhIqw==",
      "requires": {
        "buffer-writer": "2.0.0",
        "packet-reader": "1.0.0",
        "pg-connection-string": "^2.4.0",
        "pg-pool": "^3.2.2",
        "pg-protocol": "^1.4.0",
        "pg-types": "^2.1.0",
        "pgpass": "1.x"
      }
    },

and I'm super happy because it is perfectly working.

Thank you so much @charmander I believe we can consider this issue closed.