Question

Where should i put a-certificate.crt file?

Posted October 14, 2020 2k views
Node.jsDigitalOceanPostgreSQLDatabasesDigitalOcean Managed PostgreSQL Database

I had trouble connecting to digitalocean database in node js and was getting errors such as:

Error: self signed certificate in certificate chain.

or

error: no pg_hba.conf entry for host "x.x.x.x", user "------", database "------", SSL off

but then I added ca-certificate.crt file into my node.js app newly created ssl folder and wrote this

const pg = require('pg')
const fs = require('fs')

const pool = new pg.Pool({
    user: "-----",
    password: "--------",
    host: "-----------",
    port: 25060,
    database: "----",
    ssl: true,
    ssl: {
      ca: fs.readFileSync('../ssl/ca-certificate.crt'),
      rejectUnauthorized: true,
    }

module.exports = pool;

This line fixed all those errors and now I can connect to the database

ca: fs.readFileSync('../ssl/ca-certificate.crt'),

but I feel it’s not very common practice to have ca-certificate.crt file in my app folder so I would like to know how to handle this?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
3 answers

Hi there @beuu,

I have not tested this with NodeJS, but I’ve done it with other frameworks and PostgreSQL. What you can do is to add the certificate at:

~/.postgresql/root.crt

Then make sure that the file has secure permissions:

  • chmod 600 ~/.postgresql/root.crt

That way the connection to your PostgreSQL server would default to using that certificate.

Let me know how it goes!
Regards,
Bobby

  • I have a node app on the App Platform, how would I do this for that? I don’t think I can ssh into the server right?

    • Hi there 👋,

      I believe that the initial question was not referring to the App platform at the time but just a regular Linux server.

      With the App Platform, you could refer to the SSL cerificate with the CA_CERT environment variable. So there would be no need to manually download the certificate and then add it to your version control.

      For more information about the available environment variables you could take a look at the official documentation here:

      https://www.digitalocean.com/docs/app-platform/how-to/use-environment-variables/

      Regards,
      Bobby

      • That worked! Thank you SO much. That was taking me hours. …I should’ve read the docs more closely…😅

      • Hello @bobbyiliev,

        I’m still hitting this Self signed certificate error even if I’m referencing the CA_CERT of the database.

        Would you mind looking at my config below and help me find where my issue is coming from please?
        Thanks

          - key: DATABASE_URL
            scope: RUN_AND_BUILD_TIME
            value: ${<DATABASE_NAME>.DATABASE_URL}
          - key: DB_SSL_CA
            scope: RUN_AND_BUILD_TIME
            value: ${<DATABASE_NAME>.CA_CERT}
        
        
        PG connection:
        
        new pg.Pool({
          connectionString: process.env.DATABASE_URL,
          ssl: {
            rejectUnauthorized: true,
            ca: process.env.DB_SSL_CA
          }
        })
        
      • Hello,

        I have the same issue with MongoDB.
        We use mongoose to connect to DigitalOcean MongoDB (DATABASE_URL env variable from DigitalOcean).
        But mongoose doesn’t have any property to set TLS CRT content (CA_CERT env variable from DigitalOcean), we decided to create a CRT file on the fly and then use it to connect to DB, then delete the created file.

        Could you please give advice, is it an acceptable approach?

        const filenameToCreate = 'test.crt';
        fs.writeFileSync(filenameToCreate, process.env.CA_CERT);
        mongoose.connect(dbConnectionUri, {
          useNewUrlParser: true,
          useUnifiedTopology: true,
          tlsCAFile: filenameToCreate
        }).then((ers) => {
          app.listen(port, () => console.log(`server is listening on ${port}`));
        }).catch(err => {
          console.log(err);
        }).finally(() => {
          fs.unlinkSync(filenameToCreate);
        })
        

The certificates should be put in a folder dedicated to certificates and key files. An example location would be /usr/local/ssl/crt/. All of your certificates need to be in the same folder. Save the changes to the file once you are finished.

+1 for this
I am running a node app on App Platform and I’m unsure how to get it on the server. Other than putting it in my repo, which, as @beuu said, seems like a bad idea.