Spring boot and GCP - Connecting Cloud SQL instance with spring-cloud-gcp-starter-sql-postgresql tries SSL and it delays to start

Question

I have a Spring boot project that should connect with an instance of Cloud SQL with spring-cloud-gcp-starter-sql-postgresql in order to avoid the explicit use of an IP in the project.

So far, It connects well but it delayed a lot (around 30 seconds to start) because it tries to connect via SSL socket and after a lot of tries, it connects.

In the logs there is a line that says:

2020-02-19 00:10:09.809  INFO 6779 --- [           main] o.s.c.g.a.s.GcpCloudSqlAutoConfiguration : Default POSTGRESQL JdbcUrl provider. Connecting to jdbc:postgresql://google/test?socketFactory=com.google.cloud.sql.postgres.SocketFactory&cloudSqlInstance=XXXXXX:us-central1:test&useSSL=false with driver org.postgresql.Driver

As long as I know, the parameter useSSL=false won't work for postgresql. The correct one is ssl=false but when I try to overwrite the JDBC Url with the application.yml, It prints the following log:

2020-02-19 00:10:09.816  WARN 6779 --- [           main] o.s.c.g.a.s.GcpCloudSqlAutoConfiguration : Ignoring provided spring.datasource.url. Overwriting it based on the spring.cloud.gcp.sql.instance-connection-name.

I suspect that the delay is because of the SSL connection. So I have two questions:

How can I avoid the use of SSL connection? Since I am not setting a JDBC URL explicitly, I cannot use the ssl=false in the parameter.
I suspect that is delaying because the SSL Client certificate is not set. If this is the case, how can I set it? I already have the .pem but I don't know how to implement it

I add my application.yml with configurations and the mentioned log:

application.yml

spring:
  cloud:
    gcp:
      project-id: xxxxxxx
      config
      sql:
        instance-connection-name: xxxxxxx:us-central1:test
        database-name: test
        enabled: true
  datasource:
    username: test
    password: 123456
    initialization-mode: always


  jpa:
    hibernate:
      ddl-auto: update

The credentials are set in a environment variable "GOOGLE_APPLICATION_CREDENTIALS" calling the service account json

Log:

2020-02-19 00:10:09.692  INFO 6779 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2020-02-19 00:10:09.699  INFO 6779 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2020-02-19 00:10:09.699  INFO 6779 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.30]
2020-02-19 00:10:09.767  INFO 6779 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2020-02-19 00:10:09.767  INFO 6779 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 790 ms
2020-02-19 00:10:09.809  INFO 6779 --- [           main] o.s.c.g.a.s.GcpCloudSqlAutoConfiguration : Default POSTGRESQL JdbcUrl provider. Connecting to jdbc:postgresql://google/test?socketFactory=com.google.cloud.sql.postgres.SocketFactory&cloudSqlInstance=XXXXXXX:us-central1:test&useSSL=false with driver org.postgresql.Driver
2020-02-19 00:10:09.816  WARN 6779 --- [           main] o.s.c.g.a.s.GcpCloudSqlAutoConfiguration : Ignoring provided spring.datasource.url. Overwriting it based on the spring.cloud.gcp.sql.instance-connection-name.
2020-02-19 00:10:09.885  INFO 6779 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2020-02-19 00:10:09.940  INFO 6779 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.4.10.Final}
2020-02-19 00:10:10.039  INFO 6779 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
2020-02-19 00:10:10.109  INFO 6779 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-02-19 00:10:10.193  INFO 6779 --- [           main] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:10.193  INFO 6779 --- [           main] c.g.cloud.sql.core.CoreSocketFactory     : First Cloud SQL connection, generating RSA key pair.
2020-02-19 00:10:13.690  INFO 6779 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-02-19 00:10:13.711  INFO 6779 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL10Dialect
2020-02-19 00:10:13.791  INFO 6779 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:15.042  INFO 6779 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:16.333  INFO 6779 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:17.653  INFO 6779 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:19.314  INFO 6779 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:20.643  INFO 6779 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:21.938  INFO 6779 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:23.227  INFO 6779 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:24.561  INFO 6779 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : Connecting to Cloud SQL instance [XXXXXXX:us-central1:test] via SSL socket.
2020-02-19 00:10:35.164  INFO 6779 --- [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2020-02-19 00:10:35.173  INFO 6779 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2020-02-19 00:10:35.294  WARN 6779 --- [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2020-02-19 00:10:35.507  INFO 6779 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2020-02-19 00:10:35.611  INFO 6779 --- [           main] o.s.c.g.core.DefaultCredentialsProvider  : Default credentials provider for service account cloud-code@XXXXXXX.iam.gserviceaccount.com
2020-02-19 00:10:35.612  INFO 6779 --- [           main] o.s.c.g.core.DefaultCredentialsProvider  : Scopes in use by default credentials: [https://www.googleapis.com/auth/pubsub, https://www.googleapis.com/auth/spanner.admin, https://www.googleapis.com/auth/spanner.data, https://www.googleapis.com/auth/datastore, https://www.googleapis.com/auth/sqlservice.admin, https://www.googleapis.com/auth/devstorage.read_only, https://www.googleapis.com/auth/devstorage.read_write, https://www.googleapis.com/auth/cloudruntimeconfig, https://www.googleapis.com/auth/trace.append, https://www.googleapis.com/auth/cloud-platform, https://www.googleapis.com/auth/cloud-vision, https://www.googleapis.com/auth/bigquery]
2020-02-19 00:10:35.612  INFO 6779 --- [           main] o.s.c.g.a.c.GcpContextAutoConfiguration  : The default project ID is XXXXXXX
2020-02-19 00:10:35.730  INFO 6779 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2020-02-19 00:10:35.733  INFO 6779 --- [           main] c.r.c.CloudSqlTestApplication            : Started CloudSqlTestApplication in 27.027 seconds (JVM running for 27.464)

Also, just in case it helps, i am adding my pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.ramonparis</groupId>
    <artifactId>cloud-sql-test</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>cloud-sql-test</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <spring-cloud.version>Hoxton.SR1</spring-cloud.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-gcp-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.2.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-gcp-starter-sql-postgresql</artifactId>
        </dependency>
        <dependency>
           ........
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Could you please specify where you are connecting from to the Cloud SQL instance (GAE, Compute Engine, external application)? Is it within the Google Cloud or from an external app? Additionally by accessing your Cloud SQL instance -> Connections -> SSL you can manipulate those certificates in case you want to set a new one or download it. As far as i've seen for all the connections you do not need to specify useSSL=false, do you get the same delay without specifying it? — Artemis Georgakopoulou, Feb 21 '20 at 10:30
From what kind of service are you connecting to your Cloud SQL according with the official documentation? https://cloud.google.com/sql/docs/postgres/connect-external-app — Harif Velarde, Aug 05 '20 at 22:03

Tiago Medici · Accepted Answer · 2020-08-15T07:56:51.633

there are several ways you can connect to Cloud SQL , in your case you should use a sockect

<dependency>
    <groupId>com.google.cloud.sql</groupId>
    <artifactId>postgres-socket-factory</artifactId>
    <version>1.0.15</version>
</dependency>

spring.datasource.url: jdbc:postgresql://google/cloudSqlInstance=${instance}&socketFactory=com.google.cloud.sql.postgres.SocketFactory

the other way is using a cloud_sql_proxy (more compless and i would indicate if you are using on GKE) Like :

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy

chmod +x cloud_sql_proxy

create a proxy user :
gcloud iam service-accounts create proxy-user --display-name "proxy-account-user"

gcloud projects add-iam-policy-binding [PROJECT_ID] --member \
serviceAccount:[SERVICE_ACCOUNT_EMAIL] --role roles/cloudsql.client

gcloud iam service-accounts keys create key.json --iam-account [SERVICE_ACCOUNT_EMAIL]

./cloud_sql_proxy -instances=[INSTANCE_CONNECTION_NAME]=tcp:5432 -credential_file=key.json &

now you are listenning to your instance db : check the video (www.youtube.com/watch?v=iKoaiH_xYB8)

Spring boot and GCP - Connecting Cloud SQL instance with spring-cloud-gcp-starter-sql-postgresql tries SSL and it delays to start

1 Answers1