Trying to connect to production Pgsql DB but got connection time out (0x0000274C/10060)

Question

Note: My database works because I am able to migrate and query from within the application.

So I've setup my application and database on digital ocean's new "App" feature. And now I am trying to connect to the production database but I'm getting connection timeout error. Below are the details I inputed, but maybe I am missing my SSL cert ? If yes, how do I go about getting this ? Because I believe digital ocean automatically setup the SSL for me..

Database : postgresql Tool used to connect : TablePlus

UPDATE

My site is SSL'ed

Checked directly on DO, and there's no certificate stored on my account

Answer 1

So after a few days I gave up and swap to using Droplets. I feel Apps Platform is cool for quick prototypes but I don't have much control in what I want can do in it. I don't think I could even run sudo in the console when I tried.

Here are a few other links I ran through that may or may not help others that was in similar situation if you were using a dev database on digital ocean. And also my support tickets solutions that I received.

Links

1. https://www.digitalocean.com/community/questions/cannot-connect-with-dev-database-due-to-ssl-issue?answer=67513

2. https://docs.digitalocean.com/products/databases/postgresql/how-to/connect

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

DO Support Replied

I understand that you are trying to connect to the database with an SSL certificate. Firstly, I want to let you know that you have attached the dev database to your app "mysite" and not the production database. However, you should be able to add the following env variable to store the SSL certificate:

KEYS: CA_CERT VALUES: ${mysitedb.CA_CERT}

Once you add the above env variable then you should be able to use the "CA_CERT" variable to fetch the SSL certificate in your app. Additionally, you can view the SSL certificate by running the following command in the console:

echo $CA_CERT

Thanks for getting back to us. This output would be intended. To use the contents of that cert you would need to save the env variable to a file or convert it from string using a method within your application. You can then specify that file in your configuration and use the certificate in your connection to the database.

An example of how to do this can be found here: How to add an SSL certificate (ca-cert) to node.js environment variables in order to connect to Digital Ocean Postgres Managed Database?

Let us know if you have any questions.

Thank you for getting back to us!

From the screenshot, I see you are using the incorrect port number 5432. You have to use port number 25060 to connect to the database.

Regarding the SSL certificate, one thing to note here is that the database is managed by DigitalOcean and it is not possible to generate clients key (private key) and certificate (public key) via cloud panel. That is the reason, you will need to generate those on your local machine or from whichever client you plan to establish a connection to the database.

Here is an example of how to use SSL on a client like Navicat, you will need to download or have OpenSSL installed on your operating system which you will use to generate Client Key File and Client Certificate File which will be referred to as private and public keys respectively

You need the below three files to connect

• Client Key File
• Client Certificate File
• CA Certificate File

https://www2.navicat.com/manual/online_manual/en/navicat/linux_manual/SSLSettings.html

As a guide to establishing a connection

• Generate Client Key File and Client Certificate File from your client or the local machine
• Copy the certificate from app console to any .crt file and pass that file to connect to database.

Here is how to use the OpenSSL to generate the Client Key File and Client Certificate File: https://knowledge.digicert.com/solution/SO27347.html

openssl req -x509 -newkey rsa:2048 -keyout client-key.pem -out client-cert.pem -days 3650 -nodes -subj '/CN=localhost'

For the Client Key File, Client Certificate, and CA Certificate choose the directory location you saved them and click on "Test" button in your client to test the connection.

Please let us know if you have any additional questions, and have a wonderful day!