MySQL connection failure Possibly missing permission cloudsql.instances.get

[Sinthana]

Hi 

I am trying to deploy a java application (Spring boot maven) using cloud run. This application needs a connect to MySQL database which is created in google cloud SQL. I have a created a IAM and Admin service account and provides roles like Cloud SQL Admin, Cloud SQL Client, Cloud SQL Editor, Cloud SQL Instance User, Storage Object Creator. After creating the service account I have generated the key file as json and using it in my java application . I am able to connect to the MySQL instance when running from my local machine. But when I try to deploy it through cloud run I am facing errors when building google image from the application. I tried below command.

gcloud builds submit --tag gcr.io/udhyamvolunteer-image/udhyamvolunteer

The error I am facing is:

Show More

{
"domain": "global",
"message": "boss::NOT_AUTHORIZED: Not authorized to access resource. Possibly missing permission cloudsql.instances.get on resource instances/testdb.",
"reason": "forbidden"
} 

[ms4446]

The error message you're encountering indicates a permissions issue related to accessing your Cloud SQL instance. The key message here is "Not authorized to access resource. Possibly missing permission cloudsql.instances.get on resource instances/testdb." This suggests that the service account used during the build process in Google Cloud Build does not have sufficient permissions to access the Cloud SQL instance.

Here are some steps to troubleshoot and resolve this issue:

1. Verify the Service Account in Cloud Build:

   • Ensure that the service account with the appropriate roles is being used by Cloud Build. You can check the service account in the Cloud Build settings or in your build configuration file. The gcloud config list command shows the current configuration of the gcloud CLI, but remember that the account used by Cloud Build might differ from your local setup.

2. Grant Required Permissions:

   • In the IAM & Admin console of your Google Cloud project, select the service account used by Cloud Build.
   • Add necessary roles such as "Cloud SQL Client", "Cloud SQL Editor", or "Cloud SQL Admin", depending on your application's needs. These roles should include the cloudsql.instances.get permission and any other permissions required for your application.

3. Check for Comprehensive Permissions:

   • Beyond cloudsql.instances.get, ensure that all necessary permissions for interacting with Cloud SQL and other Google Cloud services are granted to the service account.

4. Ensure Correct Environment Variables and Configuration:

   • Double-check that all environment variables and configurations related to the database connection and authentication are correctly set in your Cloud Run service and Dockerfile or Cloud Build configuration file.

5. Use Debugging Flags for Detailed Output:

   • Run the gcloud builds submit command with the --verbosity=debug flag to get more detailed information about the build process and pinpoint the source of the error.

6. Review Cloud Build Configuration File:

   • Make sure your Cloud Build configuration file is correctly set up, including all necessary steps, arguments, and the proper use of the service account.

7. Verify Network Configuration:

   • Check that your Cloud Run service and Cloud SQL instance are configured to communicate correctly, possibly involving VPC connectors or ensuring they are in the same network.

8. Analyze Logs for Additional Insights:

   • Review the logs from Cloud Build and Cloud Run for any additional error messages or warnings that could provide further insights.

9. Test Database Connection Separately:

   • If feasible, isolate and test the database connection part of your application separately to confirm that the issue is specifically with Cloud SQL access.

[sridharhari]

Cloud SQL connection with cloud version 3.5.1 with spring boot app worked fine with Service Account. Started throwing the following exception after upgrading to cloud version 5.1.0.

Not sure if any additional parameters required for the new version, please advice.

java.sql.SQLException: Access denied for user '//mysql-test-user'@'cloudsqlproxy~[ip-address]' (using password: YES)

[ms4446]

This often happens due to changes in how the new version handles database authentication. Here are some steps to resolve it:

1. Update Maven Dependencies:

• Remove any instances of spring-cloud-gcp-starter-sql-mysql from your dependencies.
• Add the following for version 5.1.0:

<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>spring-cloud-gcp-starter</artifactId>
    <version>5.1.0</version>
</dependency>

• If testing locally, also add:

<dependency>
    <groupId>com.google.cloud.sql</groupId>
    <artifactId>mysql-socket-factory-connector-j-8</artifactId>
    <version>1.15.1</version>
</dependency>

2. IAM Authentication:

• Ensure IAM authentication is enabled on your Cloud SQL instance.
• Update your database user to this format: SERVICE_ACCOUNT_EMAIL@YOUR_PROJECT_ID.iam.gserviceaccount.com and ensure it has the necessary privileges.

3. Configure application.properties:

• For Cloud Deployment:

spring.datasource.url=jdbc:mysql://google/${spring.cloud.gcp.sql.instance-connection-name}/${spring.cloud.gcp.sql.database-name}?useSSL=false
spring.datasource.username=SERVICE_ACCOUNT_EMAIL@YOUR_PROJECT_ID.iam.gserviceaccount.com
spring.cloud.gcp.sql.database-name=my_database_name
spring.cloud.gcp.sql.instance-connection-name=YOUR_PROJECT_ID:YOUR_REGION:YOUR_INSTANCE_NAME

(No password is needed with IAM authentication)

4. Permissions & Connectivity:

• Verify the service account has the Cloud SQL Client and Cloud SQL Editor roles (if needed).
• If using a private IP, set up a Serverless VPC Access connector for Cloud Run.

For Local Testing:

1. Use the Cloud SQL Auth Proxy:

   ./cloud_sql_proxy -instances=YOUR_PROJECT_ID:YOUR_REGION:YOUR_INSTANCE_NAME=tcp:3306
   

2. Update application.properties:

   spring.datasource.url=jdbc:mysql://127.0.0.1:3306/my_database_name
   

[Sinthana]

Hi @ms4446 

Is it possible to connect a spring boot application to MySQL database created on Google Cloud SQL using IAM Admin service account.

I need to deploy my java application to google cloud using cloud run.

1. I have created a MySQL instance in google cloud and created a database in it. Also added a user account to the database.

2. I have created IAM Admin service account and provide the ROLES Cloud SQL Admin, Cloud SQL Client, Cloud SQL Editor, Cloud SQL Instance User, Storage Object Creator.

3. I have generated the credentials.json file in google cloud and download the json file. 

4. I have added the credentials.json file in the src/main/resource folder of spring boot application.

5. I have added the below properties in the application.properties

spring.cloud.gcp.sql.database-name= my database name
spring.cloud.gcp.sql.instance-connection-name=<Project ID>:<region>:<instance name>
spring.cloud.gcp.project-id=Project ID
spring.cloud.gcp.credentials.location=file://credentials.json
spring.sql.init.mode=always
server.error.include-message=always

6. I have added the below dependencies

<dependency>

<groupId>com.google.cloud</groupId>

<artifactId>spring-cloud-gcp-starter</artifactId>

<version>4.1.1</version>

</dependency>

<dependency>

<groupId>com.google.cloud</groupId>

<artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>

<version>4.1.1</version>

</dependency>

<dependency>

<groupId>com.google.cloud.sql</groupId>

<artifactId>mysql-socket-factory-connector-j-8</artifactId>

<version>1.15.1</version>

</dependency>

<dependency>

<groupId>com.google.cloud</groupId>

<artifactId>google-cloud-storage</artifactId>

<version>2.0.0</version>

</dependency>

<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>

         <dependency>

            <groupId>com.google.cloud</groupId>

            <artifactId>spring-cloud-gcp-dependencies</artifactId>

            <version>${spring-cloud-gcp.version}</version>

            <type>pom</type>

            <scope>import</scope>

         </dependency>

      </dependencies>

   </dependencyManagement>

when I run the code in my local machine it throws me the error saying Access denied for user 'root'@'cloudsqlproxy~my-ip' (using password: NO). I feel like it is not using the credentials.json file and I am missing something. Not able to figure it out

[ms4446]

The error message "Access denied for user 'root'@'cloudsqlproxy~my-ip" 

could be due to several reasons, including incorrect credentials, the way the credentials are being used, or the configuration of your Spring Boot application. Let's go through some steps to troubleshoot and resolve this issue:

1. Correct Credentials: Ensure that the credentials provided in the credentials.json file are correct. This file should contain the service account key for the IAM Admin service account you created.

2. Service Account Permissions: Double-check that the IAM Admin service account has the necessary roles (Cloud SQL Admin, Cloud SQL Client, Cloud SQL Editor, Cloud SQL Instance User, Storage Object Creator) and that these roles are properly assigned.

3. Database User Authentication: The error mentions the user 'root'. Ensure that this user exists in your MySQL database and has the correct permissions. Also, verify that you are using the correct username and password in your application properties. If you're using Cloud SQL's IAM database authentication, ensure that it's configured correctly.

4. Use of credentials.json: Storing credentials.json in src/main/resources is generally not recommended for security reasons, especially when deploying to production. Instead, consider using environment variables or secret management services to handle credentials. For local testing, ensure that the path to credentials.json is correctly specified in spring.cloud.gcp.credentials.location.

5. Database Connection Properties: Verify that the database connection properties in application.properties are correct. The instance-connection-name should be in the format <Project ID>:<region>:<instance name>. Ensure that there are no typos or incorrect values.

6. Cloud SQL Proxy: If you are using the Cloud SQL Proxy for local testing, ensure that it is running and configured correctly. The proxy provides a secure connection to your Cloud SQL instance for testing on your local machine.

7. Dependency Versions: Check that the versions of the dependencies you're using are compatible with each other and with the version of Spring Boot you're using.

8. Spring Boot Configuration: Review your Spring Boot configuration for any misconfigurations. Ensure that the database URL, username, and password (if required) are correctly set.

9. Logs and Debugging: Enable detailed logging for your Spring Boot application to get more insights into the connection process and the exact point of failure.

10. Testing in a Different Environment: If possible, test the connection to the Cloud SQL instance from a different environment (e.g., another machine or a CI/CD pipeline) to rule out local environment issues.

[sridharhari]

Hi,

Thank you for your detailed response.

I have followed the exact steps and I am seeing this error now.

Caused by: java.net.UnknownHostException: google

at java.base/java.net.InetAddress$CachedAddresses.get(InetAddress.java:801) ~[na:na]

at java.base/java.net.InetAddress.getAllByName0(InetAddress.java:1509) ~[na:na]

at java.base/java.net.InetAddress.getAllByName(InetAddress.java:1367) ~[na:na]

at java.base/java.net.InetAddress.getAllByName(InetAddress.java:1301) ~[na:na]

at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:130) ~[mysql-connector-j-8.3.0.jar!/:8.3.0]

at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:62) ~[mysql-connector-j-8.3.0.jar!/:8.3.0]

... 96 common frames omitted

Our configuration as follows

spring:

  cloud:

    gcp:

      sql:

        database-name: actual-database-name

        instance-connection-name: actual-instance-connection-name

  datasource:

    url: >-

      jdbc:mysql://google/actual-instance-connection-name/actual-database-name?useSSL=false

    username: service-account-email@project-id.iam.gserviceaccount.com

Note: I have removed the dependency spring-cloud-gcp-starter-sql-mysql and added spring-cloud-gcp-starter as suggested.

And updated spring.datasource.url and spring.datasource.username without password as suggested.

spring.cloud.gcp.sql.database-name and spring.cloud.gcp.sql.instance-connection-name remains the same as it was configured and needed for previous cloud version (3.5.1)

And IAM authentication is enabled on our Cloud SQL instance.

[ms4446]

The error "java.net.UnknownHostException: google" typically arises when your application is unable to resolve the hostname google during database connection establishment. This suggests that the SocketFactory responsible for creating the connection to Cloud SQL is not being properly initialized or recognized. Here are steps to debug and resolve the connections issues:

1. Database URL Configuration

• Correct JDBC URL Format: It's important to use the correct JDBC URL format for Cloud SQL connections. The format provided as jdbc:mysql://google/... can cause issues because "google" is not a valid hostname. Instead, use a URL formatted as follows, ensuring it explicitly specifies the cloud SQL instance and uses the correct socket factory if required by your setup:

  properties
  spring.datasource.url=jdbc:mysql:///actual-database-name?cloudSqlInstance=actual-instance-connection-name&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false

  This setup includes the socket factory explicitly. If Spring Cloud GCP 5.x documentation states that the socket factory is auto-configured, you can omit the socketFactory parameter.

2. SocketFactory Dependency

• Dependency Check: If the Spring Cloud GCP 5.x documentation confirms that the socket factory configuration is automatically managed, you do not need to include the mysql-socket-factory-connector-j-8 dependency. However, if auto-configuration is not mentioned, or if you encounter issues, including this dependency may still be necessary.

3. spring-cloud-gcp-starter Compatibility

• Verify Version Compatibility: Ensure that the spring-cloud-gcp-starter version you use is compatible with the Spring Cloud and Spring Boot versions in your project to avoid any conflicts.

4. IAM and Service Account Configuration

• IAM Roles: Make sure the IAM service account associated with your application has the necessary roles, such as Cloud SQL Client and Cloud SQL Editor if database management is required.

• IAM Authentication: Verify that IAM authentication is enabled and correctly configured for your Cloud SQL instance.

5. Local and Cloud Environment Configuration

• Local Testing: If testing locally, consider using the Cloud SQL Auth Proxy to simplify the connection process.

• Cloud Environment: For Cloud Run deployments, ensure you have correctly set up connectivity, such as through a Serverless VPC Access connector if using private IPs.

6. Troubleshooting and Logs

• Dependency Conflicts: Check for any conflicts among the dependencies in your Maven project.

• Log Analysis: Review the application logs carefully for any detailed error messages that can help diagnose the connection issues.