通过 Cloud SQL Auth Proxy 连接时是否需要密码？

Question

简短的回答是，不需要密码。

我做了什么：

创建服务帐号
--credentials-file通过传递标志，授予 Cloud SQL Auth Proxy 对服务帐号的访问权限
已授权roles/cloudsql.client通过向服务帐号授予 Cloud SQL 客户端角色 ()，建立从 Cloud SQL Auth Proxy 到 Cloud SQL 实例的连接

缺少的内容：

IAM 数据库身份验证默认禁用在 Cloud SQL 实例上，您需要设置cloudsql.iam_authentication=on
服务账户应被授予 Cloud SQL Instance User 角色 ( roles/cloudsql.instanceUser)，哪个包括cloudsql.instances.login许可，它允许一个人登录数据库
服务帐户应该有匹配的pg用户
cloud-sql-proxy 需求--auto-iam-authn，使其使用 IAM 数据库身份验证

您可能还希望通过为两个角色添加以下条件来限制对特定 Cloud SQL 实例的访问：

resource.name == 'projects/PROJECT_ID/instances/INSTANCE_NAME'
&& resource.service == 'sqladmin.googleapis.com'

请注意，该postgres用户不能用于 IAM 数据库身份验证，因为数据库用户名必须与服务帐户名匹配。

最终结果：

docker-compose.yml：

services:
  app:
    build: .
    command: sleep infinity
    init: true
    volumes:
      - .:/app
    depends_on:
      - proxy

  proxy:
    image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.6.0
    command:
      --address 0.0.0.0
      --credentials-file /credentials.json
      --auto-iam-authn
      PROJECT_ID:europe-central2:myinstance
    volumes:
      - ./credentials.json:/credentials.json

Dockerfile：

FROM google/cloud-sdk:435.0.1-alpine
RUN apk add terraform postgresql15-client
WORKDIR /app

main.tf：

provider "google" {
  project = "PROJECT_ID"
}

resource "google_sql_database_instance" "test-auth" {
  # deletion_protection = false
  name = "myinstance"
  region = "europe-central2"
  database_version = "POSTGRES_11"
  settings {
    tier = "db-f1-micro"
    database_flags {
      name = "cloudsql.iam_authentication"
      value = "on"
    }
  }
}

resource "google_sql_user" "test-auth-built-in" {
  name = "postgres"
  instance = google_sql_database_instance.test-auth.name
  password = 1
}

resource "google_service_account" "test-auth" {
  account_id = "myaccount"
}

resource "google_project_iam_member" "test-auth-client" {
  project = "PROJECT_ID"
  role = "roles/cloudsql.client"
  member = "serviceAccount:${google_service_account.test-auth.email}"
  condition {
    title = "myinstance"
    expression = "resource.name == 'projects/PROJECT_ID/instances/myinstance' && resource.service == 'sqladmin.googleapis.com'"
  }
}

resource "google_project_iam_member" "test-auth-instance-user" {
  project = "PROJECT_ID"
  role = "roles/cloudsql.instanceUser"
  member = "serviceAccount:${google_service_account.test-auth.email}"
  condition {
    title = "myinstance"
    expression = "resource.name == 'projects/PROJECT_ID/instances/myinstance' && resource.service == 'sqladmin.googleapis.com'"
  }
}

resource "google_sql_user" "test-auth-iam" {
  name = "myaccount@PROJECT_ID.iam"
  instance = google_sql_database_instance.test-auth.name
  type = "CLOUD_IAM_SERVICE_ACCOUNT"
}

// replace PROJECT_ID
// uncomment deletion_protection if you're going to destroy the resources after experimenting
$ touch credentials.json
$ docker compose up -d
$ docker compose exec app gcloud auth application-default login
$ docker compose exec app terraform init
$ docker compose exec app terraform apply
// create the service account key
$ mv ... credentials.json
$ docker compose up -d
$ docker compose exec app psql -h proxy -U myaccount@PROJECT_ID.iam postgres

您可以使用日志来找出问题所在。

如果你忘记了启用 IAM 数据库身份验证在一个实例上（cloudsql.iam_authentication）：

$ gcloud logging read "resource.type=cloudsql_database" --project=PROJECT_ID --limit=10
...
logName: projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
resource:
  labels:
    database_id: PROJECT_ID:myinstance
    project_id: PROJECT_ID
    region: europe-central2
  type: cloudsql_database
severity: INFO
textPayload: |-
  2023-08-19 00:33:39.798 UTC [857]: [2-1] db=postgres,user=myaccount@PROJECT_ID.iam DETAIL:  Cloud SQL IAM authentication not enabled
  Connection matched pg_hba.conf line 21: "local   all           +cloudsqliamserviceaccount         cloudsql-iam-svc-acct"
---
logName: projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
resource:
  labels:
    database_id: PROJECT_ID:myinstance
    project_id: PROJECT_ID
    region: europe-central2
  type: cloudsql_database
severity: ALERT
textPayload: '2023-08-19 00:33:39.798 UTC [857]: [1-1] db=postgres,user=myaccount@PROJECT_ID.iam
  FATAL:  Cloud SQL IAM service account authentication failed for user "myaccount@PROJECT_ID.iam"'
...

如果你忘记了授予Cloud SQL 实例用户角色到您的服务帐户：

$ gcloud logging read "resource.type=cloudsql_database" --project=PROJECT_ID --limit=10
...
logName: projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
resource:
  labels:
    database_id: PROJECT_ID:myinstance
    project_id: PROJECT_ID
    region: europe-central2
  type: cloudsql_database
severity: INFO
textPayload: |-
  2023-08-19 00:39:21.656 UTC [991]: [2-1] db=postgres,user=myaccount@PROJECT_ID.iam DETAIL:  Not authorized to access resource. Possibly missing permission cloudsql.instances.login on resource projects/PROJECT_ID/instances/myinstance.
  Connection matched pg_hba.conf line 21: "local   all           +cloudsqliamserviceaccount         cloudsql-iam-svc-acct"
---
logName: projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
resource:
  labels:
    database_id: PROJECT_ID:myinstance
    project_id: PROJECT_ID
    region: europe-central2
  type: cloudsql_database
severity: ALERT
textPayload: '2023-08-19 00:39:21.656 UTC [991]: [1-1] db=postgres,user=myaccount@PROJECT_ID.iam
  FATAL:  Cloud SQL IAM service account authentication failed for user "myaccount@PROJECT_ID.iam"'
...

有关详细信息，请参阅本概述。

Answer 1