I am unable to connect to create a Resource to connect to my Postgres Database. I had 2 working connections this morning. I attempted to update the credentials on one of them, but when I try and test the connections I get the error " Unable to connect. Error: self signed certificate".
This page indicates I should tick "Use a self-signed certificate" checkbox, but in my case (using the Postgres connection type) that box is not present.
The MySQL connection type does have the "Use a self-signed certificate" checkbox and I've tried adding all the correct credentials using that type. However, I then get this message, "Unable to connect. Error: Could not establish a connection. Try checking your database firewall configuration and whitelisting Retool's IP Address".
There is no firewall in place and the database service I am using (Heroku) does not have "whitelisting of ip addresses".
To be clear, I had 2 connections that worked this morning. Now, even the one I didn't touch give the "Unable to connect. Error: self signed certificate" when I try testing or running.
Any help is appreciated.
Hey @Shawn-DoNation! It looks like you have an internal ticket open for this one, so we’ll make sure to follow up with the resolution here for anyone else running into the same problem
SOLVED. In case anyone runs into a similar issue.
In my case, I was trying to connect Heroku hosted Postgres database.
I was finally able to make the connection, by using a Beta feature in Heroku called "Enhanced Certificates".
More details can be found here and here.
I was then able to fill in the relevant connection details in ReTool and click the "Connect using SSL" checkbox. I did not need to fill in the SSL certificates. This seems to be negotiated with the Heroku Postgres endpoint. I was able to leave the "Skip TLS certificate validation" unticked. See screenshot.
Alternatively, I was able to get the full connection string from the Heroku console and add the credentials using the "Use a database connection string" link. I needed to add
?sslmode=verify-full to the end of the connection string to ensure SSL was used.
I hope this helps.
that’s fantastic, thank you for sharing!
I’ll add your solution to our internal ticket to help anyone else running into this.
Thanks for sharing, Shawn. I see in the article you mentioned that one needs a standard(or higher) plan to enable Enhanced certificates. Do you happen to know if it's possible in a hobby-dev plan?
I tried your solution and I ran into an error.
Hi Willy. I do recall reading that Enhanced certs will only work on Standard and higher plans so they won't work on a Hobby-dev plan.
I think I tried enabling Enhanced certs on a hobby-dev plan and got the same error.
I'm not sure what the solution would be in that case.
Sorry I can't be of more help.
No problem. Your original answer is very helpful, anyway. Thank you.
Thank you very much @Shawn-DoNation for the detailed solution.
However I don't get how you actually get the CA Cert/Client Key/Client Cert after enabling Enhanced Certificates on Heroku...
Heroku documentation looks pretty obscure to me.
I see my DATABASE_URL is now including the parameter sslrootcert=/etc/ssl/certs/ca-certificates.crt
Should I generate myself a certificate and drop it there? How can I actually download it to fill in the fields : CA Cert, Client Key, et Client Cert ?
Thank you very much (again) for your help!
Just to check, what does your Retool resource look like currently? Are you using the Enhances Certificates feature in Heroku?
Were you able to:
get the full connection string from the Heroku console and add the credentials using the "Use a database connection string" link. I needed to add ?sslmode=verify-full to the end of the connection string to ensure SSL was used.
HI Jerome. I just saw your post as I was just looking at this again for myself to create some internal documentation. This is what worked for me...
When I created the Resource in ReTool, I used the "Import from connection string" link and pasted in the uri and added
?sslmode=verify-full to the end of it.
When I clicked "Import" it filled in all of the connection details.
I had to tick the "Use SSL/TLS" checkbox.
And then I just clicked Test Connection and it worked. Note that I didn't actually have to put in any of the certificate details. I think ReTool works it out - see screenshot with more details.
I hope this helps.