Heroku Database Connection Properties

Question

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?

Answer 1

Add or edit the following line in your postgresql.conf file :

listen_addresses = '*'

Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:

TYPE DATABASE USER CIDR-ADDRESS  METHOD

host  all  all 0.0.0.0/0 md5

Restart postgresql service:

net stop postgresql-9.0 & net start postgresql-9.0 

(version should be based on your installation) -- On windows (run cmd as an administrator).

sudo service start postgresql -- On linux (or according to your linux distribution.) 

Answer 2

For those who might be using Spring Boot and having the configuration provided through the DATABASE_URL environment property (not system property), the suffix can be added to the property:

?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

and passed through with a slight modification to the config bean:

@Bean
public BasicDataSource dataSource() throws URISyntaxException {

    URI dbUri = new URI(System.getenv("DATABASE_URL"));
    String username = dbUri.getUserInfo().split(":")[0];
    String password = dbUri.getUserInfo().split(":")[1];

    StringBuilder dbUrl = new StringBuilder(128);
    dbUrl.append("jdbc:postgresql://")
            .append(dbUri.getHost()).append(":")
            .append(dbUri.getPort())
            .append(dbUri.getPath());

    String query = dbUri.getQuery();
    if (null != query && !query.isEmpty()) {
        dbUrl.append("?").append(query);
    }

    BasicDataSource basicDataSource = new BasicDataSource();
    basicDataSource.setUrl(dbUrl.toString());
    basicDataSource.setUsername(username);
    basicDataSource.setPassword(password);

    return basicDataSource;
}