Heroku Database Connection Properties

I'm trying to perform a relatively trivial task: I want to connect to a Heroku database. I have created the database and have been issued credentials from the Heroku site. However, when I try to connect to this database using anything besides the terminal 'heroku' command line client, I get fatal errors or cannot connect errors.

The two tools that I tried to connect with outside of the Heroku terminal application are: Navicat and IntelliJ.

The error that I receive in Navicat when trying to connect to the database is:

could not connect to server: Host is down
    Is the server running on host "ec2-107-21-112-215.compute-1.amazonaws.com" and accepting
    TCP/IP connections on port 5432?

My connection settings are as follows:

Connection Name Heroku Dev Test

Host Name/IP Address ec2-107-21-112-215.compute-1.amazonaws.com

Port 5432

Navicat doesn't even seem to be making an attempt to connect to that hostname.

When I try to connect with IntelliJ, using the full credentials, I get the following error:

java.sql.SQLException: FATAL: no pg_hba.conf entry for host "75.168.4.146", user "rphbqggxeokuxl", database "dc008iqk0rq4j5", SSL off

Again, I'm using the credentials that the Heroku application provides me with when accessing my database on their website.

Has anyone ran into this Heroku connection issue before?

Solution 1:

I also had the issue with the FATAL: no pg_hba.conf entry for host error message.

I solved the connection issue to my Heroku Postgres database by adding the following to my JDBC string: &ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory.

Example

jdbc:postgresql://host:port/database?user=username&password=secret&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

You will need the SSL option only if SSL is enabled for your Postgres database (which is the default).

Hint

If you want to check your database connection properties, then just run the following command with the Heroku Toolbelt: heroku pg:info --app your-heroko-appname (make sure that you have Postgres insalled to run this command in your terminal)

The pg:info command will also tell you that sslmode is set to require.

To test the database connection I recommend SQL Power Architect as it is the tool which I was using to check my solution.

Solution 2:

Heroku provides this information for connecting from external sources:

https://devcenter.heroku.com/articles/heroku-postgresql#external-connections-ingress

The second error message indicates PostgreSQL is not configured to accept the connection you're trying to make. Given the information Heroku provides, a good guess is that you're not connecting with SSL. Try to enable that on your connection.

Here are instructions for using SSL with Navicat: http://mirror.navicat.com/manual/online_manual/en/navicat/rv_manual/ClientCert.html.

This may be helpful in configuring Intellij to use SSL: https://devcenter.heroku.com/articles/connecting-to-relational-databases-on-heroku-with-java#connecting-to-a-database-remotely.

Solution 3:

IntelliJ -> Datasources and Drivers

After you've configured the host, database and user details under the General tab switch to the Advanced tab and ensure that you've added the following:

ssl = true
sslfactory = org.postgresql.ssl.NonValidatingFactory
sslmode = require

enter image description here

Solution 4:

You may consider setting a ENVIRONMENT CONFIG VARIABLE 'PGSSLMODE' to 'require' via Heroku's web interface or CLI.

Case: Postgres dB set up as a Heroku add-on and attached to app on a Heroku Dyno.

Heroku's instructions unfortunately leave out any mention of how to activate SSL, even though it is required for any dB tier starting with Standard-0 by default.

Follow all of the pg-copy or pg-upgrade steps (preferred approach depends on your version of Postgres) in Heroku instructions; however, before decommissioning the old database (if relevant) -- add the PGSSLMODE environment variable.

The instructions sufficiently cover how to promote the new database (and, consequently set the DATABASE_URL), so no changes/modifications to them should be required.

Solution 5:

Wanted to help others who might run into this. If you're supplying the Username and Password in seperate fields rather than on the command line, you need to use a ? between the database name and ssl=true and discard the first &

jdbc:postgresql://host:port/database?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

That's the command line that finally allowed me to connect to a PostgreSQL database using SQL Power Architect