Cloud SQL Proxy and Insufficient Permission

Question

I'm trying to deploy my Spring Boot/JHipster app on Google's Container Engine (GKE). I've figured out most things, but I'm having a problem with my database instance (a PostgreSQL instance running on Google Cloud SQL, with the Google SQL Proxy).

I've followed the instructions here and here to set up my app.

1. I've set up my PostreSQL instance in cloud, and created my app's database and user.
2. I've created an SQL service with Cloud SQL Client role -- I grabbed the JSON key, and used it to create my cloudsql-instance-credentials. I've also created my cloudsql-db-credentials.
3. I've added the additional bits to my deployment yaml file. I've basically cloned the yaml file from this GitHub sample and replaced all the references to wordpress with my own Docker image (hosted in the Google Container Registry). I've also updated the proxy block, like so:

deployment.yaml snippet:

  - image: gcr.io/cloudsql-docker/gce-proxy:1.09
      name: cloudsql-proxy
          command: ["/cloud_sql_proxy", "--dir=/cloudsql",
                    "-instances=[my-project]:us-central1:[my-sql-instance-id]=tcp:5432",
                    "-credential_file=/secrets/cloudsql/credentials.json"]

Lastly, I've updated my Spring Boot configuration yaml file, like so:

datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:postgresql://google/[my-database]?socketFactory=com.google.cloud.sql.postgres.SocketFactory&socketFactoryArg=[my-project]:us-central1:[my-sql-instance-id]
    username: ${DB_USER}
    password: ${DB_PASSWORD}

When I kubectl create my deployment, the image deploys, but it fails to launch the app. Here's the salient bit from my log:

Caused by: java.lang.RuntimeException: Unable to retrieve information about Cloud SQL instance [[my-project]:us-central1:[my-sql-instance-id]]
    at com.google.cloud.sql.core.SslSocketFactory.obtainInstanceMetadata(SslSocketFactory.java:411)
    at com.google.cloud.sql.core.SslSocketFactory.fetchInstanceSslInfo(SslSocketFactory.java:284)
    at com.google.cloud.sql.core.SslSocketFactory.getInstanceSslInfo(SslSocketFactory.java:264)
    at com.google.cloud.sql.core.SslSocketFactory.createAndConfigureSocket(SslSocketFactory.java:183)
    at com.google.cloud.sql.core.SslSocketFactory.create(SslSocketFactory.java:152)
    at com.google.cloud.sql.postgres.SocketFactory.createSocket(SocketFactory.java:50)
    at org.postgresql.core.PGStream.<init>(PGStream.java:60)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:144)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:52)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:216)
    at org.postgresql.Driver.makeConnection(Driver.java:404)
    at org.postgresql.Driver.connect(Driver.java:272)
    ... 37 common frames omitted
Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 403 Forbidden
{
  "code" : 403,
  "errors" : [ {
    "domain" : "global",
    "message" : "Insufficient Permission",
    "reason" : "insufficientPermissions"
  } ],
  "message" : "Insufficient Permission"
}
    at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:113)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:40)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:321)
    at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1065)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:419)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:352)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:469)
    at com.google.cloud.sql.core.SslSocketFactory.obtainInstanceMetadata(SslSocketFactory.java:372)
    ... 48 common frames omitted

This "Insufficient Permission" error pops up a lot on StackOverflow, but I haven't found a question that's quite the same scenario as mine. It seems like a generic OAuth-level error. I feel like I've double-checked my set-up against the instructions a few times, and I'm not sure where I can look for any additional clues.

Any ideas?

Update:

Thanks to Vadim's pointer, I've managed to get past the "Insufficient Permission" problem. Sadly, my app still fails on boot-up, when it tries to establish a connection to the database (specifically, when Liquibase tries to start connecting to the DB to run migration scripts).

My new error is at the socket level in the driver:

liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: The connection attempt failed.
    at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:390)
    at io.github.jhipster.config.liquibase.AsyncSpringLiquibase.initDb(AsyncSpringLiquibase.java:82)
    at io.github.jhipster.config.liquibase.AsyncSpringLiquibase.afterPropertiesSet(AsyncSpringLiquibase.java:72)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624)
    ... 24 common frames omitted
Caused by: org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:272)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:52)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:216)
    at org.postgresql.Driver.makeConnection(Driver.java:404)
    at org.postgresql.Driver.connect(Driver.java:272)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:86)
    at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:71)
    at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:385)
    ... 28 common frames omitted
Caused by: java.net.SocketException: already connected
    at java.net.Socket.connect(Socket.java:569)
    at sun.security.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:673)
    at org.postgresql.core.PGStream.<init>(PGStream.java:61)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:144)
    ... 37 common frames omitted

Answer 1

Vadim solved the problem that I asked about, but the second problem -- the socket already connected problem -- was eventually figured out by one of my co-workers.

The root of the socket problem relates to the datasource configuration. It turns out that I'm mixing and matching two different mechanisms for accessing the Cloud SQL environment.

1. Using the Cloud SQL Proxy; and
2. Using a Google Socket Factory

Because I've successfully configured the Cloud SQL Proxy, I don't need that weird JDBC URL in my Spring Boot environment. I can connect using 127.0.0.1:5432, like so:

datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:postgresql://127.0.0.1:5432/[my-database-name]
    username: ${DB_USER}
    password: ${DB_PASSWORD}

Now that I've replaced my JDBC URL, my app connects successfully.

Answer 2

This is a little old, but using the Cloud SQL Proxy is not the solution.

I had the same issue with java.net.SocketException: already connected when using the Google Socket Factory with Spring Boot.

I was able to fix this after upgrading the postgresql driver to latest (org.postgresql:postgresql:42.2.1 at the time of the writing).