1

I'm trying to connect my Spring Application with maven as build tool to a postgres instance on my GCP project.

I had setup a postgres instance, generated client-key.pem, client-cert.pem & server-ca.pem, turned on ssl on those instances and added all those certs to my locally run project (src/main/resources/ssl).

So here's some application.properties that I've tried to connect to gcp with ssl

1.

spring.datasource.url= jdbc:postgresql://[[DATABASEIP]]:[[DATABASEPORT]]/[[DATABASENAME]]?sslmode=verify-ca&sslrootcert=/ssl/server-ca.pem&sslcert=/ssl/client-cert.pem&sslkey=/ssl/client-key.pk8
spring.datasource.username=[[DB_USERNAME]]
spring.datasource.password=[[DB_PASSWORD]]

2.

spring.datasource.url=jdbc:postgresql:///[[DATABASENAME]]?cloudSqlInstance=[gcp-project-id]:[region]:[instance-name]&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=[[DB_USERNAME]]&password=[[DB_PASSWORD]]
spring.datasource.continue-on-error=true

3.

spring.cloud.gcp.sql.enabled=true
spring.cloud.gcp.sql.database-name=[[DATABASENAME]]
spring.cloud.gcp.datastore.host=[[DATABASEIP]]
spring.cloud.gcp.sql.instance-connection-name=[gcp-project-id]:[region]:[instance-name]
spring.datasource.continue-on-error=true

None of them work

I've tried to find a github repo where I can see how to properly setup Spring Application to connect to GCP, found some, but none provided connecting with SSL

github.com - spring-cloud-gcp-sql-postgres-sample

spring-cloud-gcp documentation

It's not even listed on how to connect to PostgreSQL on Spring Cloud Documentation.

I've make sure that instance is working fine, connected it with those certs from Dbeaver, it connects. Connected from my Spring project with SSL turned off, everythings running fine. But once enable SSL, even authorizing all networks to connect (0.0.0.0/0), it failed to connect from my Spring applications.

Can someone give me some hint if I'm missing something on my configs, or any hint where I should look.

I would really appreciate if someone can provide me with an example repositories of how to connect to GCP instance with SSL. Where should I put all of those certs? How I should configure my GCP Instance?

Thanks in advance.

Configure SSL Database Connection to PostgreSQL on Google Cloud Instance from a Spring Application with Maven as build tool

Anton Wijaya
  • 21
  • 3
  • 2
    What's your error? – guillaume blaquiere Apr 05 '23 at 16:25
  • 1st alternative I get this error `Caused by: org.postgresql.util.PSQLException: Could not open SSL root certificate file server-ca.pem. at org.postgresql.ssl.LibPQFactory.(LibPQFactory.java:151) ~[postgresql-42.5.4.jar:42.5.4] at org.postgresql.core.SocketFactoryFactory.getSslSocketFactory(SocketFactoryFactory.java:61) ~[postgresql-42.5.4.jar:42.5.4] ....` `Caused by: java.io.FileNotFoundException: server-ca.pem (The system cannot find the file specified) at java.base/java.io.FileInputStream.open0(Native Method) ~[na:na] .....` – Anton Wijaya Apr 06 '23 at 02:55
  • 2nd alternative I get this error `Caused by: org.postgresql.util.PSQLException: Something unusual has occurred to cause the driver to fail. Please report this exception.` `Caused by: java.lang.RuntimeException: Unable to obtain credentials to communicate with the Cloud SQL API` @guillaumeblaquiere `Caused by: java.io.IOException: The Application Default Credentials are not available. They are available if running in Google Compute Engine. Otherwise, the environment variable GOOGLE_APPLICATION_CREDENTIALS must be defined pointing to a file ....` – Anton Wijaya Apr 06 '23 at 03:04
  • @guillaumeblaquiere 3rd alternative I get this error `*************************** APPLICATION FAILED TO START *************************** Description: Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured. Reason: Failed to determine a suitable driver class` – Anton Wijaya Apr 06 '23 at 03:06
  • The first option seems the most relevant. But you have a "file not found" error. Are you sure about the path? – guillaume blaquiere Apr 06 '23 at 07:06
  • @guillaumeblaquiere I just tried cleaning up my properties, organizing multiples keys in my project and adjusted config with most sensible settings which is 1st options. This is the error I get `Caused by: org.postgresql.util.PSQLException: FATAL: connection requires a valid client certificate` `Suppressed: org.postgresql.util.PSQLException: FATAL: pg_hba.conf rejects connection for host "[DATABASEIP]]", user "[[DBUSERNAME]]", database "[[DBNAME]]", no encryption` – Anton Wijaya Apr 06 '23 at 08:00

1 Answers1

0

I was able to solve it by adding the following url and dependencies.

spring.datasource.url= jdbc:postgresql://[[DATABASEIP]]:[[DATABASEPORT]]/[[DATABASENAME]]?cloudSqlInstance=[gcp-project-id]:[region]:[instance-name]&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=[[DB_USERNAME]]&password=[[DB_PASSWORD]]

dependencies {
    // Add
    implementation("com.google.cloud.sql:postgres-socket-factory:X.XX.X")
}
Garhira-san
  • 1
  • 2