How to connect to a PostgreSQL database hosted on Heroku from your local machine using JDBC
Chances are if you try to connect to a PostgreSQL database that is hosted on Heroku via JDBC an exception might get thrown and break your app:
Exception in thread “main” java.sql.SQLException:
Cannot create PoolableConnectionFactory
(FATAL:
no pg_hba.conf entry for host “123.456.789.012”,
user “arnold”,
database “terminator”,
SSL off)The exception message is confusing a bit. It might deceive you into thinking that you have to modify a file named “pg_hba.conf”. But this file is only needed on the server side, not on the client side. So what’s the problem and how can you solve it?
The solution turns out to be fairly simple and it is documented in an article by Heroku aptly titled “Connecting to Relational Databases on Heroku with Java”:
If you’re using a Heroku Postgres database you can connect to it remotely for maintenance and debugging purposes. However doing so requires that you use an SSL connection. Your JDBC connection URL will need to include the URL param “sslmode=require”.
For example:
jdbc:postgresql://<host>:<port>/<dbname>?
sslmode=require&
user=<username>&
password=<password>If you do not add “sslmode=require” you will get a connection error.
When configuring a Spring Data Source, you can either append the parameter to the connection URL or configure it using “connectionProperties”:
<bean id=”dataSource” class=”...”>
<property name=”url” value=”jdbc:postgresql://...” />
<property name=”username” value=”userName” />
<property name=”password” value=”passWord” />
<property name=”driverClassName” value=”org.postgresql.Driver” />
<property name=”connectionProperties” value=”sslmode=require”/>
</bean>
Happy coding!
