I can't connect in GCP by sql auth proxy from cloudrun to cloud sql by springboot application. Somebody have an example stepbystep?

Question

I can configure connection from local machine using sql auth proxy but I can't from cloud run springboot application with hikaricp to a cloud sql with public ip using unix socket. Somebody have a complete example? I read in gcp doc that I don't need to create a docker for sql auth proxy for connection from cloud run to cloud sql.It's created when you define db connection in configuration of cloud run.

POM.xml added below dependency:

    <dependency>
        <groupId>com.google.cloud.sql</groupId>
        <artifactId>mysql-socket-factory</artifactId>
        <version>1.3.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

I set it and next I defined in application the follow application properties (by classic jdbc works fine adding my ip in cloud sql to allow connection but I need to use sql auth proxy by unix socket to avoid to add a vpc, I will not explain reasone too long and boring) for hikariCP:

spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql:///DBSCHEMA
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.socketFactory=com.google.cloud.sql.mysql.SocketFactory
spring.datasource.cloudSqlInstance=GCP_PROJECT:GCP_REGION:DBNAME
spring.datasource.ipTypes=PUBLIC

logging.level.com.zaxxer.hikari=TRACE

Somebody can help me with some example that works fine? If in your example created docker for sql auth proxy please explain it. In internet I can't find resource clearly talking about this configuration.

Thanks a lot.

Answer 1

Cloud Run sets a unix socket, so you need to use a path that uses that unixSocketPath.

jdbc:mysql:///<DATABASE_NAME>?unixSocketPath=</PATH/TO/UNIX/SOCKET>&cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=<MYSQL_USER_NAME>&password=<MYSQL_USER_PASSWORD>

a Cloud Run unixsocket path looks like:

/cloudsql/{connection_id}

the {connection_id} may have this format: GCP_PROJECT:GCP_REGION:DBNAME

so essentially in your connectionString, you should have:

?unixSocketPath=/cloudsql/myproject:us-central1:my_instance

Answer 2

SOLUTION:

1. Create json key for service account tipical name:

   GCP_PROJECT_ID-GCP_ID_SERVICE_ACCOUNT-compute_developer_gserviceaccount_com

   and save json key file in assets directory (under root of java project) where you have entrypoint.sh.

2. In DockerFile add line for json service account key:

   RUN export GOOGLE_APPLICATION_CREDENTIALS=assets/GCP_PROJECT_ID-GCP_ID_SERVICE_ACCOUNT-compute_developer_gserviceaccount_com-GCP_KEY.json

3. pom.xml add below:

Used for sql auth proxy unix socket

<dependency>
    <groupId>com.google.cloud.sql</groupId>
    <artifactId>mysql-socket-factory</artifactId>
    <version>1.3.0</version>
</dependency>
<dependency>
    <groupId>com.google.api-client</groupId>
    <artifactId>google-api-client</artifactId>
    <version>1.32.1</version>
</dependency>

Connector mysql:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

4. In application.properties or application-test.properties (if you use spring profile to deploy in TEST GCP cloud run):

   spring.datasource.url=jdbc:mysql://google/DB_SCHEMA?cloudSqlInstance=GCP_PROJECT_ID:GCP_REGION:DB_ISTANCE&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false

   spring.datasource.username=*****

   spring.datasource.password=*****

5. DEPLOY SOLUTION

I will use gitlab variables and I will pass url,username and password but this is other work...

N.B.: com.google.cloud.sql.mysql.SocketFactory tell to use unix socket! Cloud Run when start it start sql auth proxy if in cloud run configuration in connection section set database (if in same project you will see a drop down list in alternative if it's in other project you will add manually GCP_PROJECT:GCP_REGION:DB). You can define multiple DB to connect but it's another job ;)