![通过 Cloud SQL Auth Proxy 连接时是否需要密码?](https://linux22.com/image/788559/%E9%80%9A%E8%BF%87%20Cloud%20SQL%20Auth%20Proxy%20%E8%BF%9E%E6%8E%A5%E6%97%B6%E6%98%AF%E5%90%A6%E9%9C%80%E8%A6%81%E5%AF%86%E7%A0%81%EF%BC%9F.png)
看起来这是必要的,因为它要求我输入密码。但如果是这样,那么拥有 2 个凭证(凭证文件 + 密码)有什么意义呢?
如果没有,那么我遗漏了什么?
这文档对此并没有太多透露:
如果出现提示,请输入密码。
为了从本地机器进行测试,我做了以下操作:
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
--debug
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"
}
}
resource "google_sql_user" "test-auth" {
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" {
project = "PROJECT_ID"
role = "roles/cloudsql.client"
member = "serviceAccount:${google_service_account.test-auth.email}"
}
// 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 postgres
答案1
简短的回答是,不需要密码。
我做了什么:
- 创建服务帐号
--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"'
...
有关详细信息,请参阅本概述。