Cloud SQL代理和权限不足 [英] Cloud SQL Proxy and Insufficient Permission

问题描述

我正在尝试在Google的Container Engine(GKE)上部署Spring Boot/JHipster应用程序.我已经弄清楚了大多数事情，但是我的数据库实例(使用Google SQL代理在Google Cloud SQL上运行的PostgreSQL实例)遇到了问题.

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

我已按照此处和<请在href ="https://cloud.google.com/sql/docs/postgres/connect-external-app#java" rel ="nofollow noreferrer">此处设置我的应用.

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

1. 我已经在云中设置了PostreSQL实例，并创建了应用程序的数据库和用户.
2. 我已经创建了具有Cloud SQL Client角色的SQL服务-我抓到JSON密钥，并用它来创建cloudsql-instance-credentials.我还创建了cloudsql-db-credentials.
3. 我已将其他位添加到我的部署yaml文件中.我基本上已经从此GitHub示例中克隆了yaml文件.并将所有对wordpress的引用替换为我自己的Docker映像(托管在Google容器注册表中).我还更新了代理块，如下所示:

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代码段:

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

最后，我已经更新了Spring Boot配置yaml文件，如下所示:

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}

当我kubectl create进行部署时，将部署映像，但无法启动应用程序.这是我的日志中最重要的部分:

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

此权限不足"错误在StackOverflow上弹出很多，但我还没有找到与我的情况完全相同的问题.看来是一般的OAuth级错误.我觉得我已经根据说明对设置进行了多次检查，而且不确定在哪里可以找到其他线索.

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.

有什么想法吗?

更新:

借助Vadim的指针，我设法解决了权限不足"问题.可悲的是，当我的应用程序尝试建立与数据库的连接时(特别是当Liquibase尝试开始连接至数据库以运行迁移脚本时)，在启动时仍然失败.

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

推荐答案

Vadim解决了我所问的问题，但是第二个问题-套接字已连接的问题-最终由我的一位同事解决了

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.

套接字问题的根源与数据源配置有关.事实证明，我正在混合和匹配两种不同的机制来访问Cloud SQL环境.

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. 使用Cloud SQL代理；和
2. 使用Google套接字工厂

因为我已经成功配置了Cloud SQL代理，所以在Spring Boot环境中不需要怪异的JDBC URL.我可以使用127.0.0.1:5432进行连接，如下所示:

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}

现在，我已替换了JDBC URL，我的应用程序已成功连接.

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

这篇关于Cloud SQL代理和权限不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！