'Connecting to Postgres Heroku DB through Intellij IDEA

I've been trying to connect to my postgres DB all day. I can do it through pgAdmin, but I can't connect through mySQLWorkbench to migrate it and also can't connect through Intellij IDEA. We'll start w/Intellij though...

So, when I try to connect through IntelliJ, I get this...

Connection to Sinthetics failed
java.sql.SQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

I take this to mean that I need a certificate, since I'm using SSL (because it's a postgres/heroku). So, I run my handy InstallCert like so...

java InstallCert ec2-54-243-235-169.compute-1.amazonaws.com:5432

And get this back:

Loading KeyStore /usr/lib/jvm/java-7-openjdk-amd64/jre/lib/security/cacerts...
Opening connection to ec2-54-243-235-169.compute-1.amazonaws.com:5432...
Starting SSL handshake...

javax.net.ssl.SSLHandshakeException: Remote host closed connection during handshake
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:953)
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1332)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1359)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1343)
    at InstallCert.main(InstallCert.java:87)
Caused by: java.io.EOFException: SSL peer shut down incorrectly
    at sun.security.ssl.InputRecord.read(InputRecord.java:482)
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:934)
    ... 4 more
Could not obtain server certificate chain

So why can't I get a cert for this, but it connects with no problem with pgAdmin!? Does anybody have a clue at all?

postgresqlsslherokuintellij-idea


Solution 1:[1]

To add new connection to your PostreSQL on Heroku in Intellij Idea you need to:

  1. Database Tool Window / New / Data Source / PostreSQL

  2. Put Host, Port, Database, User, Password that you find on https://postgres.heroku.com/databases

  3. Go to Advanced tab and put:

    • ssl: true
    • sslmode: verify-ca
    • sslfactory: org.postgresql.ssl.NonValidatingFactory

  4. Profit.

Solution 2:[2]

Create DB with Host, Port, Database, User, Password from data.heroku.com

Go to Advanced tab and put :

  • ssl: true
  • sslfactory: org.postgresql.ssl.NonValidatingFactory

If you get error like this:

Connection to xxx.amazonaws.com failed. [08006] The hostname xxx.amazonaws.com could not be verified by hostnameverifier PgjdbcHostnameVerifier

Just put this value to advanced tab too:

  • sslmode: verify-ca

Solution 3:[3]

I get the same issue, downgrading to a minor version of Postgre Driver worked for me.

PostgreDriverSettings:

PostgreDriverSettings

Solution 4:[4]

To connect I simply did this:

  1. Go to your app on Heroku in Settings
  2. Find the environment variable DATABASE_URL
  3. Copy it and paste somewhere on you computer

The value of DATABASE_URL is something like this:

postgres://this-is-theuser:this-is-a-very-long-long-long-password@ec2-34-756-294-13.eu-west-1.compute.amazonaws.com:5432/this-is-the-database

DO NOT COPY AND PASTE THIS VALUE DIRECTLY IN THE Url: FIELD IN PHPSTORM

Instead, put each value manually in the form letting PHPStorm itself build the URL and try to connect. It should work.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Arthur Kupriyanov
Solution 3 barbsan
Solution 4 Aerendir

Related Questions