search

GoogleCloudPlatform / spring-cloud-gcp

New home for Spring Cloud GCP development starting with version 2.0.
Apache License 2.0
419 stars 313 forks source link

spring-cloud-gcp-starter-sql-postgresql - Connection issue #521

Closed OuesFa closed 3 years ago

OuesFa commented 3 years ago

Hello!

Following this guide, I'm trying to setup a simple test application to use a cloudsql postgresql instance using spring-cloud-gcp-starter-sql-postgresql When I start the app I get this error 

{"@timestamp":"2021-07-05T08:19:47.578Z","message":"HHH000342: Could not obtain connection to query metadata","logger_name":"org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator","thread_name":"main","level":"WARN","stack_trace":"org.postgresql.util.PSQLException: Something unusual has occurred to cause the driver to fail. Please report this exception.\n\tat org.postgresql.Driver.connect(Driver.java:285)\n\tat com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)\n\tat com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)\n\tat com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)\n\tat com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477)\n\tat com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:560)\n\tat com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)\n\tat com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)\n\tat org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)\n\tat org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180)\n\tat org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:68)\n\tat org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)\n\tat org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101)\n\tat org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263)\n\tat org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237)\n\tat org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)\n\tat org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152)\n\tat org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286)\n\tat org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243)\n\tat org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)\n\tat org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:176)\n\tat org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127)\n\tat org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1224)\n\tat org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1255)\n\tat org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)\n\tat org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)\n\tat org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409)\n\tat org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396)\n\tat org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)\n\tat org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)\n\tat org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)\n\tat org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)\n\tat org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)\n\tat org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1153)\n\tat org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:907)\n\tat org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:582)\n\tat org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144)\n\tat org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767)\n\tat org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759)\n\tat org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426)\n\tat org.springframework.boot.SpringApplication.run(SpringApplication.java:326)\n\tat org.springframework.boot.SpringApplication.run(SpringApplication.java:1311)\n\tat org.springframework.boot.SpringApplication.run(SpringApplication.java:1300)\n\tat com.example.demo.DemoApplication.main(DemoApplication.java:10)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\n\tat org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49)\n\tat org.springframework.boot.loader.Launcher.launch(Launcher.java:107)\n\tat org.springframework.boot.loader.Launcher.launch(Launcher.java:58)\n\tat org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:88)\nCaused by: java.lang.RuntimeException: my-project-...:europe-west4:demoapp] The Cloud SQL Instance does not exist or your account is not authorized to access it. Please verify the instance connection name and check the IAM permissions for project \"infra-preprod-8d01c452\" \n\tat com.google.cloud.sql.core.CloudSqlInstance.addExceptionContext(CloudSqlInstance.java:592)\n\tat com.google.cloud.sql.core.CloudSqlInstance.fetchMetadata(CloudSqlInstance.java:483)\n\tat com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)\n\tat com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)\n\tat com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)\n\tat java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)\n\tat java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)\n\tat java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)\n\tat java.base/java.lang.Thread.run(Thread.java:829)

Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 403 Forbidden
GET https://sqladmin.googleapis.com/sql/v1beta4/projects/.../instances/europe-west4~demoapp
{\n  \"code\" : 403,\n  \"errors\" : [ {\n    \"domain\" : \"global\",\n    \"message\" : \"The client is not authorized to make this request.\",\n    \"reason\" : \"notAuthorized\"\n  } ]
 \"message\" : \"The client is not authorized to make this request.\"}\n\tat com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146)\n\tat com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:118)\n\tat com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:37)\n\tat com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:428)\n\tat com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1111)\n\tat com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:514)\n\tat com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:455)\n\tat com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:565)\n\tat com.google.cloud.sql.core.CloudSqlInstance.fetchMetadata(CloudSqlInstance.java:438)\n\t... 9 common frames omitted\n","dd.service":"demo-app","dd.version":"1.20210702.153202-0f84c55"}

On CloudSql side I can see error logs of this kind 

{
  "protoPayload": {
    "@type": "type.googleapis.com/google.cloud.audit.AuditLog",
    "status": {
      "code": 7,
      "message": "Not authorized to access resource."
    },
    "authenticationInfo": {
      "serviceAccountDelegationInfo": [
        {}
      ],
      "principalSubject": "serviceAccount:my-project.svc.id.goog[sre/demo-app]"
    },
    "requestMetadata": {
      "callerIp": "gce-internal-ip",
      "requestAttributes": {
        "time": "2021-07-05T14:53:29.431265Z",
        "auth": {}
      },
      "destinationAttributes": {}
    },
    "serviceName": "cloudsql.googleapis.com",
    "methodName": "cloudsql.instances.get",
    "authorizationInfo": [
      {
        "resource": "instances/demoapp",
        "permission": "cloudsql.instances.get",
        "resourceAttributes": {}
      }
    ],
    "resourceName": "instances/demoapp",
    "request": {
      "@type": "type.googleapis.com/google.cloud.sql.v1beta4.SqlInstancesGetRequest",
      "project": "my-project",
      "instance": "europe-west4~demoapp"
    }
  },
  "insertId": "-nkxqlbe1tua5",
  "resource": {
    "type": "cloudsql_database",
    "labels": {
      "region": "europe-west4",
      "database_id": "my-project:demoapp",
      "project_id": "my-project"
    }
  },
  "timestamp": "2021-07-05T14:53:29.231390Z",
  "severity": "ERROR",
  "logName": "projects/my-project/logs/cloudaudit.googleapis.com%2Fdata_access",
  "receiveTimestamp": "2021-07-05T14:53:29.868349522Z"
}

Here is my properties file 

spring.cloud.gcp.sql.instance-connection-name=my-project:europe-...:demoapp
spring.cloud.gcp.sql.database-name=demoapp

spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.username=my-user
spring.datasource.password=my-pwd

Here is a sample of my build.gradle file

dependencies {
    ...
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.springframework.boot:spring-boot-starter-data-rest'
    compile group: 'com.google.cloud', name: 'spring-cloud-gcp-starter-sql-postgresql'
    runtime 'com.h2database:h2'
...
 }
dependencyManagement {
    imports {
        mavenBom "com.google.cloud:spring-cloud-gcp-dependencies:2.0.3"
    }
}

I tested my user/password and everything works well on the postgresql instance.

But seems that my credentials are not taken into account in my app. What am I missing here please ? Thanks for your help

elefeint commented 3 years ago

The error message "The client is not authorized to make this request" seems to indicate that the service account serviceAccount:my-project.svc.id.goog[sre/demo-app] does not have IAM access when invoking the instances.get method.

This documentation section on IAM authentication might help: https://cloud.google.com/sql/docs/postgres/authentication#user_and_service_account_administration

elefeint commented 3 years ago

The permission name is cloudsql.instances.get; the best role for this usecase is likely roles/cloudsql.client: https://cloud.google.com/iam/docs/understanding-roles#cloud-sql-roles

mpeddada1 commented 3 years ago

Closing. @OuesFa feel free to reopen if you are still not able to resolve this issue.