Question

Trouble connecting to Postgresql DigitalOcean Managed Database via Node.js app

Posted April 16, 2021 1.4k views
Node.jsPostgreSQL

I am using environment variables to connect to by database through a Node.js webapp. When a submit a request to my database I get a 504 Gateway Time-out error. I checked my pm2 logs, and I am getting the following error “no pg_hba.conf entry for host ”<myIP>“, user ”<myusername>“, database ”<mydatabase>“, SSL off.

Here are my environment variables

PGUSER=<myusername>
PGHOST=<myhost>
PGPASSWORD=<mypassword>
PGDATABASE=<mydatabase>
PGPORT=<myport>
PGSSLMODE=true
NODE_ENV=production

All of those are copied from the connection parameters provided to me by digital ocean. The managed database is SSL = require, but when I change PGSSLMODE to =require, I get the following pm2 log error "self signed certificate in certificate chain”.

I also tried setting the connection string provided to me as the environmental variable connection: to see if that would work, but that gave me the following error “connect ECONNREFUSED 127.0.0.1:5432”. My digital ocean managed database is not 5432, which I know is the default Postgres port, so it makes me think that it isn’t seeing that environment variable.

Any help is appreciated, and please let me know if you need more context!

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
1 answer

Hi there,

You would indeed need to set the SSL to required, but in order to fix the self-signed certificate error.

You will have to also download the .crt file for your Managed Cluster from your DigitalOcean control panel and add it to your server.

That way your Node application will use the CA certificate and you will not get the SSL error.

Let me know how it goes.
Regards,
Bobby

  • Thank you! What would be the best to add the .crt file to my server? I’m assuming I wouldn’t want to add it through Github because that would expose the data. Do I do it by following this tutorial, or is that wrong?
    https://www.digitalocean.com/community/tutorials/how-to-set-up-and-configure-a-certificate-authority-ca-on-ubuntu-20-04

    Thanks again for the help!

    by Jamon Camisso
    A private Certificate Authority that runs on Ubuntu 20.04 will enable you to configure, test, and run programs that require encrypted connections between a client and a server. Using a private CA, you can issue certificates for users, servers, or individual programs and services within your infrastructure. A private CA can help ensure that all your development, staging, and production environments use similar architectures and configurations.
    • Hi there,

      Yes indeed, it is not a good practice to add any sensitive information to Git.

      I believe that you could upload the certificate to:

      ~/.postgresql/root.crt
      

      And then make sure that the file permissions are secure.

      This is taken from the official documentation here. I’ve also used it for some Docker images as well.

      Let me know how it goes.
      Regards,
      Bobby

      • Hi bobbyiliev,

        Thank you again for the help! Sorry, I’m still a little confused by this. I don’t already have a .postgresql folder. in my user’s home directory. I tried the command la to show hidden files and didn’t see it with that either.

        I tried creating a .postgresql folder and then uploading the .crt file as the name root.crt to that folder. I then restarted postgresql and my server and it still didn’t work. What did I do incorrectly here?

        • Hi there,

          Yes indeed, the directory does not exist by default, you need to create it.

          Once you upload the CA file into that folder you need to make sure that you set secure permissions:

          chmod 700 ~/.postgresql/
          chmod 600 ~/.postgresql/root.crt
          

          Let me know how it goes after that!
          Regards,
          Bobby

          • Hi there,

            I tried this, restarted Postgres & my server and I am still getting the self signed certificate in certificate chain error messages in my pm2 logs. I also went into my postgresql.conf file, uncommented the sslcafile line and set it equal to ’~/.postgresql/root.crt’ to see if that would make it work, but it didn’t. Anything I might have still done wrong? If not any other ideas?

          • Hi there,

            Can you share your database connection string here after removing any sensitive information from there?

            Regards,
            Bobby

          • Hi Bobby,

            The connection string is postgresql://<username>:<password>@investment-tool-do-user-<numberString>-0.b.db.ondigitalocean.com:<port>/<databaseName>?sslmode=require

            Where I inserted <numberString> was a number string that I am not sure if it should be confidential or not, so I didn’t include it. Let me know if you need anything else!

          • Hi Bobby,

            Did that connection string help?

          • Hi there @ericCrab,

            Is your project open-source? If so could you share a link here so I could try to replicate this at my end.

            If not, I could suggest trying out this Node library here for your DB connections:

            https://github.com/brianc/node-postgres

            It comes with SSL support:

            https://node-postgres.com/features/ssl

            Regards,
            Bobby

          • Hi Bobby, sorry for the delay. Would you just need a connection to my github repo?

      • Also could I share it with you privately?

    • also I am currently using the node-postgres library. How can I fix the issue with that library?

      • Hi there,

        As far I can see in the documentation you can specify the SSL CA file in your connection string, for example:

        const config = {
          database: 'database-name',
          host: 'host-or-ip',
          // this object will be passed to the TLSSocket constructor
          ssl: {
            rejectUnauthorized: false,
            ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
          },
        }
        

        Let me know how it goes.
        Regards,
        Bobby

        • Hi Bobby,

          sorry for the delay! I have been working on some other projects. I just tried this with the config you mentioned in the last response and I still am getting self-signed certificate. Any other suggestions? Also, anything else I can do to make it easier to help?