Re: Can't connect to Postgres on GCP, except through DataGrip

Am 08.06.22 um 20:24 schrieb Ben Pracht:
> Hi,
> I'm developing a Spring Boot application that needs to connect to a
> GCP hosted Postgres Database. The only program capable of connecting
> is JetBrains DataGrip.
>
> The problem with this is that I won't know how to connect from a Java
> development program if I cannot even get my SQL client to work because
> SSL is so hard to work with.
>
> DataGrip:
> Reports the DB is Postgres 14.2. DataGrip used JDBC driver 42.3.3,
> JDBC4.2.
> Authentication is User & Password
> User: myuser-dev
> Password: hidden
> Database: mydatabase
> URL: jdbc:postgresql://xx.xx.xx.xx:5432/myuser-dev
> Connection type: default
> SSH tunne:l is unchecked
> Use SSL: is checked
> CA file: postgres-server-ca.pem
> Client certificate file: postgres-client-cert.pem
> Client key file: postgres-client-key.pem
> Mode: Require  (other choices are Verify-CA and Full Verification)
>
> DBeaver:
> Use SSL: is checked
> CA certificate: postgres-server-ca.pem
> Client Certificate: postgres-client-cert.pem
> Client Private Key: postgres-client-key.pem
> SSL mode: verify-ca (tried require)
> Driver: postgresql-42.3.6
>
> DBeaver gives me this irrating:
> "unable to find valid certification path to requested target"
> "SSL error: PKIX path building failed:
> sun.security.provider.certpath.SunCertPathBuilderException: unable to
> find valid certification path to requested target
>   PKIX path building failed:
> sun.security.provider.certpath.SunCertPathBuilderException: unable to
> find valid certification path to requested target
>   PKIX path building failed:
> sun.security.provider.certpath.SunCertPathBuilderException: unable to
> find valid certification path to requested target
>     unable to find valid certification path to requested target
>     unable to find valid certification path to requested target"
>
> Yes, I've tried copying the post gres-server-ca.pem file into the
> cacerts file in the JDK that DBeaver uses. It doesn't care. Nor do I
> think I should have to do anything like that. The idea that the SSL
> trust certificate signed by Google isn't a valid trusted cert is
> assinine. But I did it because someone is going to insist that's the
> problem. Btw, all the certs were generated /by GCP/. I have no idea
> what type these are. I just want to connect. I don't want to fight
> this thing.
>
> Thanks for your help.
> Ben
>
>
>
>
Hi,

better fight with it ;)

your postgres-server-ca.pem has to be imported into java truststore.
Mostly its the ca-certs file. Depends on used jre and os.  After copying
the pem file to correct folder you need to update the truststore file
(invoke update-ca-certificates). Or if you use your own truststore
simply import via keytool. If not check location of file:
<java_home>/lib/security/cacerts

best regards
Thomas