Connection to Google Cloud PostgreSQL via JDBC wit

问题:

I can't connect Google Cloud PostgreSQL with JDBC.

Java Version : 1.8.0_162

PostgreSQL JDBC Version : 42.2.2

 try {
    Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
    System.out.println("Class not found");
}

String url = "jdbc:postgresql://ipaddresshere:5432/postgres";

Properties props = new Properties();
props.setProperty("user","postgres");
props.setProperty("password","passwordhere");

props.setProperty("ssl","true");
props.setProperty("sslcert","/Users/bguler/Desktop/GoogleCloudPGSSL/client-cert.pem");
props.setProperty("sslkey","/Users/bguler/Desktop/GoogleCloudPGSSL/client-key.pem");
props.setProperty("sslrootcert","/Users/bguler/Desktop/GoogleCloudPGSSL/server-ca.pem");

try {

    Connection conn = DriverManager.getConnection(url, props);
    System.out.println(conn);

} catch (SQLException ex) {

    System.out.println(ex.getMessage());

}

I tested certificate paths are right.

Its throw an error as ;

Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

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 can connect on terminal via psql without a problem ;

psql "sslrootcert=server-ca.pem \
  sslcert=client-cert.pem sslkey=client-key.pem \
  hostaddr=ipaddress \
  port=5432 \
  user=postgres dbname=postgres"

回答1:

If the certificate that is used by the Postgres server is not trusted by the Java default trust store you will need to add it.

First, convert your certificate in a DER format :

openssl x509 -outform der -in server-ca.pem -out server-ca.der

And after, import it in the keystore :

keytool -import -trustcacerts -alias your-alias -keystore cacerts -file server-ca.der

Alternatively, you could use Java System properties to change the trust store used by adding command line parameters:

-Djavax.net.ssl.trustStore=<path to your trusstore>.jks -Djavax.net.ssl.trustStorePassword=<your password>

It can also be helpful to put the Java SSL classes in debug by adding the following to the startup command line:

-Djavax.net.debug=ssl,handshake:verbose

回答2:

Java uses its own key store (instead of the system store used by psql) which does not know about this particular certificate and therefore does not trust it.

Try using the newest version of Java and/or check the documentation for the database instance. This should be a known problem.