Skip to content

Instantly share code, notes, and snippets.

@zmts
Last active September 3, 2023 23:21
Show Gist options
  • Save zmts/f3306945e90914c4dedda5e0eaf1a279 to your computer and use it in GitHub Desktop.
Save zmts/f3306945e90914c4dedda5e0eaf1a279 to your computer and use it in GitHub Desktop.
Can't connect to PostgreSQL. SSL error with Nodejs/Knexjs (Digital ocean)

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'))
    }
  },
  ...
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment