How to setup Spring/Heroku/postgres SSL datasource

Question

I'm trying to create a datasource from my Heroku/Spring application to postgres.heroku.com postgres database. Here is my applicationContext.xml snippet.

<bean id="securityDataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="jdbc:postgresql://url/dstabase"/>
    <property name="username" value="user"/>
    <property name="password" value="password"/>
</bean>

But when I run the app, tomcat gives this error:

Cannot create PoolableConnectionFactory (FATAL: no pg_hba.conf entry for host "10.11.12.13", user "username", database "database", SSL off)

How can I configure it to use SSL? I need it in order to stablish a connection against postgres.heroku.com

Got to admit I've never used PostgreSQL or Heroku but am guessing the following should help: https://devcenter.heroku.com/articles/connecting-to-relational-databases-on-heroku-with-java#connecting-to-a-database-remotely — DB5, Aug 06 '12 at 18:55

DB5 · Accepted Answer · 2014-12-05T07:47:09.843

30

You need to extend your JDBC connection URL with the relevant ssl information.

Your JDBC connection URL will need to include the following:

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

So in your case the URL would be:

jdbc:postgresql://url/dstabase?ssl=true&amp;sslfactory=org.postgresql.ssl.NonValidatingFactory

Source for this info: https://devcenter.heroku.com/articles/connecting-to-relational-databases-on-heroku-with-java#connecting-to-a-database-remotely

edited Dec 05 '14 at 07:47

answered Aug 07 '12 at 09:21

DB5

13,553
7
66
71

this setting opens a SSL connection, but does not validate the certificates. If you have the chance to use certificates as described here: https://jdbc.postgresql.org/documentation/head/ssl-client.html – Markus Oct 05 '17 at 12:48
for me it worked using ...?ssl=true&&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory – womd Aug 07 '19 at 14:15

score 5 · Answer 2 · answered Oct 25 '17 at 13:25

5

Following worked for me:

jdbc:postgresql://url/databaseName?sslmode=require&amp;sslfactory=org.postgresql.ssl.NonValidatingFactory

I'd to change ssl=true to sslmode=require

answered Oct 25 '17 at 13:25

iaL

376
9
18

1

Thanks. This was helpful for me too...!! – Code Spark Oct 25 '17 at 13:26

score 4 · Answer 3 · answered Sep 01 '13 at 02:49

4

When connecting to Heroku Postgres with jdbc, you will also sometimes get an error unless you add the SSL parameters to the url:

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

answered Sep 01 '13 at 02:49

Rhino

101
8

score 3 · Answer 4 · answered Jul 12 '14 at 18:32

It worked for me:

  <bean id="securityDataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="org.postgresql.Driver"/>
        <property name="url" value="jdbc:postgresql://host:port/database?ssl=true&amp;sslfactory=org.postgresql.ssl.NonValidatingFactory"/>
        <property name="username" value="user"/>
        <property name="password" value="password"/>
    </bean>

The mistake I was doing was using an & instead of & (silly I know)

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

and yes the link definitely is helpful: https://devcenter.heroku.com/articles/connecting-to-relational-databases-on-heroku-with-java#connecting-to-a-database-remotely

AND Keep in mind: in this link, jdbc:postgres://host.. is used, which might not be suitable for you (use postgresql instead) if No suitable Sql driver error occurs

How to setup Spring/Heroku/postgres SSL datasource

4 Answers4

Linked