How to connect to a Heroku PostgreSQL hobby instance

[XuluWarrior]

I've been struggling to connect TablePlus to my PostgreSQL hobby instance.
My understanding is that Heroku require connections via SSL but the server certificate is self signed.

When connecting via knex/pg, I add ?ssl=true&ssl=no-verify to the URL and it connects successfully.

Is there an equivalent in TablePlus?

[huyphams]

The default setting will work.

[XuluWarrior]

What do you mean the default setting will work? I have tried multiple times to get this to work. If it had worked, I wouldn't have opened this issue.

When I create a new connection from the URL provided by Heroku the default SSL mode is PREFERRED. If I test the connection then I get the error

SSL error: certificate verify failed
FATAL:  no pg_hba.conf entry for host "82.1.102.65"

If I change the SSL mode to REQUIRED then I get

SSL error: certificate verify failed

My understanding is that in order to connect I need to force SSL but disable verification. Which works when I connect using knex.

[XuluWarrior]

@huyphams As I can't get this to work with the default setting, should I be recreating this issue as a bug?

[huyphams]

I hope this help, here is my Heroku PostgreSQL example.

[XuluWarrior]

Is that a hobby instance or a paid tier instance?
I see that the SSL keys section is all green. If this is hobby instance does that mean that you've imported the keys/certificates at some point?

When I create a connection from the URL I get

[huyphams]

I don't think that the error is a TablePlus issue, please read this: https://dba.stackexchange.com/questions/83984/connect-to-postgresql-server-fatal-no-pg-hba-conf-entry-for-host

[XuluWarrior]

This is the error you get with hobby instances when SSL isn't forced. i.e. when TablePlus is configured with PREFERRED rather than REQUIRED

This explains that the no pg_hba.conf error is expected when a connection is made without SSL
https://help.heroku.com/DR0TTWWD/seeing-fatal-no-pg_hba-conf-entry-errors-in-postgres

If TablePlus is configured to REQUIRED then the error is

SSL error: certificate verify failed

As I stated previously I need to be able to force SSL but disable certificate verification. With my knex/pg code I use ?ssl=true&ssl=no-verify which connects happily.

The following discuss the issues with connecting with self-signed certificates

https://stackoverflow.com/questions/61097695/self-signed-certificate-error-during-query-the-heroku-hosted-postgres-database

brianc/node-postgres#2009

[XuluWarrior]

I hope this help, here is my Heroku PostgreSQL example.

It's not clear in your example if you are connecting to a hobby instance or not.
And whether you have provided your own certificates or relying on Heroku's self-signed ones.

[XuluWarrior]

As we are still discussing this, would it be possible to re-open this issue?
There may be other's who wish to offer some input as to a possible resolution.

[huyphams]

It's really weird that the PostgreSQL official document didn't mention the no-verify options.
https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-SSLMODE

but it just works on NodeJS

PGSSLMODE=no-verify

Let me read the NodeJS Pg package source code and see how they handle it.

[huyphams]

Could you show me a screenshot of your connection form @XuluWarrior ?