Question

Cannot connect to PostgreSQL db for local development

I am having trouble connecting to a Digital Ocean managed postgres database for local development, a NodeJS API built with Fastify.

I can connect to the database via psql with the connection string provided by the connection details provided in the Overview tab. When I try and connect my API to the database with the same connection string I get the following error:

no pg_hba.conf entry for host <my ip address>, user <username>, database <defaultdb>, SSL off

Even when I add my computer as a trusted source I still cannot connect.

I’ve downloaded the cert provided in the Overview tab, moved it to ~/.postgresql/postgresql.crt and used chmod 600 on the file. Then I get the following when trying to connect with psql:

psql: certificate present, but not private key file ~/.postgresql.key

And still get the no pg_hba.conf error from above when attempting to connect with the Node API I am attempting to develop.

I’ve tried everything I can think of. Pretty much every solution for this error on StackOverflow mentions changing the pg_hba.conf file, but I don’t think that is possible with a managed database.

Please let me know if I need to provide more detail on the problem I am having.

Subscribe
Share

Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

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.

Hi,

I have been going to the same issue trying to connect to managed PostgreSQL database from my NodeJS application. I am using Knex.js. I am getting various errors from having a self-signed certificate in chain if I include SSL or " no pg_hba.conf entry for host…SSL off" if I have rejectUnauthorized set to false. I have been trying to connect to my database for a full week and have yet to be able to do so.

Please help! Thank you!

Hi there @mitchellgritts,

Would you mind providing the exact connection string that you are using in your Node.js application? Make sure to hide any sensitive information like the password, username and etc.

I will try to replicate this at my end.

The first error that you’ve shared indicates that the SSL value is set to false.

Regards, Bobby