How to add an SSL certificate (ca-cert) to node.js environment variables in order to connect to Digital Ocean Postgres Managed Database?

Question

I am currently using node-postgres to create my pool. This is my current code:

const { Pool } = require('pg')

const pgPool = new Pool({
  user: process.env.PGUSER,
  password: process.env.PGPASSWORD,
  host: process.env.PGHOST,
  database: process.env.PGDATABASE,
  port: process.env.PGPORT,
  ssl: {
    rejectUnauthorized: true,
    // Would like to add line below
    // ca: process.env.CACERT,
},
})

I found another post where they read in the cert using 'fs' which can be seen below.

const config = {
database: 'database-name',
host: 'host-or-ip',
user: 'username',
password: 'password',
port: 1234,
// this object will be passed to the TLSSocket constructor
ssl: {
  ca: fs.readFileSync('/path/to/digitalOcean/certificate.crt').toString()
 }
}

I am unable to do that as I am using git to deploy my application. Specifically Digital Oceans new App Platform. I have attempted reaching out to them with no success. I would prefer not to commit my certificate in my source control. I see a lot of posts of people suggesting to set

ssl : { rejectUnauthorized: false}

That is not the approach I want to take. My code does work with that but I want it to be secure.

Any help is appreciated thanks.

Countable

Answer 1

Alright I finally was able to figure it out. I think the issue was multiline and just unfamiliarity with dotenv for my local developing environment.

I was able to get it all working with my code like this. It also worked with the fs.readFileSync() but I didn't want to commit that to my source control.

const { Pool } = require('pg')
const fs = require('fs')

const pgPool = new Pool({
user: process.env.PGUSER,
password: process.env.PGPASSWORD,
host: process.env.PGHOST,
database: process.env.PGDATABASE,
port: process.env.PGPORT,
ssl: {
    rejectUnauthorized: true,
    // ca: fs.readFileSync(
    //     `${process.cwd()}/cert/ca-certificate.crt`.toString()
    // ),
    ca: process.env.CA_CERT,
},
})
.on('connect', () => {
    console.log('connected to the database!')
})
.on('error', (err) => {
    console.log('error connecting to database ', err)
})

Now in my config.env I had to make it look like this:

CA_CERT="-----BEGIN CERTIFICATE-----\nVALUES HERE WITH NO SPACES AND A \n 
AFTER EACH LINE\n-----END CERTIFICATE-----"

I had to keep it as a single line string to have it work. But I was finally to connect with

{rejectUnauthorized:true} 

For the digital ocean app platform environment variable, I copied everything including the double quotes and pasted it in there. Seems to work great. I do not think you will be able to have this setting set to true with their $7 development database though. I had to upgrade to the managed one in order to find any CA cert to download.

Countable

While using app platform I had to manually upload the app_spec.yaml for this to work but if finally did – Mozellamozelle

@Alejandro, I followed this method to a T but I'm still getting:: Error: self signed certificate in certificate chain. I am deploying on digital ocean app platform and the logs is showing me the certificate is present with all the \n but DB still won't connect – Eyestrain

Is there any other way to this, my app is nestJs + Postgres + typeorm – Eyestrain

I can't remember what I did to make it work but try uploading the app_spec.yaml manually. You can try setting the CA_CERT to a value with double quotes and without them if that doesn't work. Also make sure you have the variable available with the scope set to RUN_TIME. Mine is set to secret so I can't actually confirm which of the options work. I can however confirm I only had to do it once. I am using a Production Managed DB from DO, and the project also works with NestJS, Postgres and TypeORM. – Mozellamozelle

Mah man, this was exactly what I was looking for. How is this not in the docs?? – Jago

Answer 2

This was due to the DO certificate formatting issue for me. If you copy and paste the cert from texfile, it adds whitespace before each line. If you are facing issues while reading it from the env or dotenv files, ensure there is no whitespace. @Petey indicated this by adding \n, which gave me a clue that it was a malformed string and not an incorrect cert.

Below is an example of the .env file.

With whitespace doesn't work

POSTGRES_CA="-----BEGIN CERTIFICATE-----
          MIIEQTCCAqmgAwIBAgIUG9FCIwnnJpmHO0iG5diqtueJgGkwDQYJKoZIhvcNAQEM
          BQAwOjE4MDYGA1UEAwwvYjRkNGJlMDQtOTg3Ni00NTEwLTliNjQtZjk2ODc5MmFm
          NWYxIFByb2plY3QgQ0EwHhcNMjQwNDA2MTk1NTM5WhcNMzQwNDA0MTk1NTM5WjA6
          MTgwNgYDVQQDDC9iNGQ0YmUwNC05ODc2LTQ1MTAtOWI2NC1mOTY4NzkyYWY1ZjEg
          Z1rOPFzpnX0Yf086KV4ghVCe3Z26xfxIX/SJA+uGtzJkbFV3EN69oISRIwL/bO9J
          AaaYybJhx4jeLpqOQz82ZSC0nqAhRZ+3f2BiIacjGHXhiaGxxw==
          -----END CERTIFICATE-----"

Without Whitespace does

Tip: If you paste the cert content in a new line, most IDEs won't add the whitespace by default. It's easier to arrange it that way rather than removing the whitespace manually!

POSTGRES_CA="-----BEGIN CERTIFICATE-----
MIIEQTCCAqmgAwIBAgIUG9FCIwnnJpmHO0iG5diqtueJgGkwDQYJKoZIhvcNAQEM
BQAwOjE4MDYGA1UEAwwvYjRkNGJlMDQtOTg3Ni00NTEwLTliNjQtZjk2ODc5MmFm
NWYxIFByb2plY3QgQ0EwHhcNMjQwNDA2MTk1NTM5WhcNMzQwNDA0MTk1NTM5WjA6
MTgwNgYDVQQDDC9iNGQ0YmUwNC05ODc2LTQ1MTAtOWI2NC1mOTY4NzkyYWY1ZjEg
Z1rOPFzpnX0Yf086KV4ghVCe3Z26xfxIX/SJA+uGtzJkbFV3EN69oISRIwL/bO9J
AaaYybJhx4jeLpqOQz82ZSC0nqAhRZ+3f2BiIacjGHXhiaGxxw==
-----END CERTIFICATE-----"

Then it's straightforward to add using the env variable

ssl: {
    ca: process.env.POSTGRES_CA,
    rejectUnauthorized: true,
},

Affable