Can't connect to PostgreSQL. SSL error with Nodejs/Knexjs (Digital ocean)

knex_ssl.md

Can't connect to PostgreSQL. SSL error with Nodejs/Knexjs (Digital ocean)

Issues:

Case 1

{
  client: 'pg',
  connection: {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    charset: process.env.DB_CHARSET
  },
  ...
}

Fails with

{
  code: '08P01',
  message: 'SSL required'
}

Case 2

{
  client: 'pg',
  connection: {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    charset: process.env.DB_CHARSET,
    ssl: true
  },
  ...
}

Fails with

{
  code: 'SELF_SIGNED_CERT_IN_CHAIN',
  message: 'self signed certificate in certificate chain'
}

Solution 1 (not recommended)

Set NODE_TLS_REJECT_UNAUTHORIZED=0 as env variable

Solution 2

Get certificate from DO dashboard and provide it to knex config

{
  client: 'pg',
  connection: {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    charset: process.env.DB_CHARSET,
    ssl: {
      ca: fs.readFileSync(path.join(__dirname, '../ca-certificate.crt'))
    }
  },
  ...
}