大网数据广告活动
服务器运维问答

Passwordless user for Google Cloud SQL (MySQL) using Cloud SQL Proxy?

· 52CE 我爱测

The documentation About the Cloud SQL Proxy contains a line, “[B]ecause the proxy always connects from a hostname that cannot be accessed except by the proxy, you can create a user account that can be used only by the proxy. The …

问题原文

原帖链接 · yonran · CC BY-SA 3.0

The documentation About the Cloud SQL Proxy contains a line, “[B]ecause the proxy always connects from a hostname that cannot be accessed except by the proxy, you can create a user account that can be used only by the proxy. The advantage of doing this is that you can specify this account without a password without compromising the security of your instance or your data.”

Is there any practical way of connecting a MySQL client through the Google Cloud Proxy which is authenticated using a Service Account that is only allowed to access a specific MySQL user?

One potential way to restrict access when using the Cloud SQL Proxy is to create a MySQL user account with Cloud SQL Proxy IP address: '[USER_NAME]'@'cloudsqlproxy~[IP_ADDRESS]'. But this would not work for a service account that runs on multiple cloud VMs. This is basically authentication of the MySQL user by IP address instead of by Service Account.

A second way to restrict a Service Account to a MySQL user might be to use a MySQL user account '[USER_NAME]'@'cloudsqlproxy~%' and rely on IAM to restrict access. The Service Account running the Cloud SQL Client requires the roles/cloudsql.client role, which is equivalent to the cloudsql.instances.connect and cloudsql.instances.get permissions. Unfortunately, if you grant multiple Service Accounts this role, then it looks like there is no way to restrict them from using each other’s MySQL user.

A third potential way to enable passwordless authentication might be to use client certificates (gcloud sql ssl-certs create [CERT_NAME] client-key.pem --instance [INSTANCE_NAME]). But client private keys are even harder to manage than passwords, since they are all invalidated at the same time in under 1 year when you refresh the server certificate: “When you refresh your server cerficate, you must also generate new client certificates; the existing client certificates are revoked.” This makes them basically impossible to use in production.

Basically, it seems like the Cloud SQL Proxy does not enable authenticating to a specific MySQL user via an IAM Service Account. Is this correct?

已采纳解答

原帖链接 · yonran · CC BY-SA 4.0

It’s ironic that the so-called “Auth proxy” hasn’t provided database user authentication until now. Google Cloud SQL has finally added support for passwordless IAM login, where cloud-sql-proxy (or the golang, java, or python connector) authenticates the IAM user or service account and then connects to the Auth proxy server in front of MySQL with an ephemeral client certificate.

For Postgres, Cloud SQL added manual IAM Authentication in 2020 (Cloud SQL Release Notes: 2020-10-12), and they added automatic IAM Authentication and cloud-sql-proxy support in 2021 -enable_iam_login(cloud-sql-proxy changelog: 1.20.0 2021-02-24, Cloud SQL Release Notes: 2021-04-06, generally available on 2021-08-30), though that flag is replaced with --auto-iam-authn in 2.0.0.

For MySQL, Cloud SQL added manual IAM Authentication in 2021 (Cloud SQL Release Notes: 2021-07-08). Automatic IAM Authentication is not documented yet, but it was unlocked in golang cloud.google.com/go/cloudsqlconn 1.1.0 and cloud-sql-proxy 2.0.0-preview.4 (2022-12-12) with --auto-iam-authn.

Passwordless IAM authentication on Google Cloud SQL MySQL requires:

  • roles/cloudsql.instanceUser role, or equivalently cloudsql.instances.get and cloudsql.instances.login permissions (MySQL IAM roles)

  • --cloudsql_iam_authentication=on on the MySQL server (Instance configuration)

  • User created with type set for IAM authentication: gloud sql users create --type=cloud_iam_user --instance=MY_INSTANCE USERNAME@DOMAIN, or gloud sql users create --type=cloud_iam_service_account --instance=MY_INSTANCE [email omitted]

  • Either manual or automatic IAM authentication

    • Manual IAM authentication: requires the username to be the local-part of the email address, the password to be set to the result of gcloud sql generate-login-token, --enable-cleartext-plugin, and requires connecting using a certificate generated by gcloud sql ssl client-certs create (Log in with manual IAM database authentication)
    • Automatic IAM authentication: requires generating an ephemeral client certificate generateEphemeralCert with access_token set to the result of gcloud auth print-access-token, and requires connecting with the Cloud SQL Proxy protocol (port 3307) (it does not work as a client certificate on port 3306). This is all automated by cloud-sql-proxy or one of the language connectors. Then you connect to cloud-sql-proxy with the username set to the local-part of the email address, and the password is ignored. Example:
    # Download v2.0.0-preview.4 or higher
    wget https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.0.0-preview.4/cloud-sql-proxy.linux.amd64
    chmod +x cloud-sql-proxy.linux.amd64
    sudo mv cloud-sql-proxy.linux.amd64 /usr/local/bin/cloud-sql-proxy
    
    cloud-sql-proxy --port=3306 --auto-iam-authn $(gcloud sql instances describe MY_INSTANCE --format='value(connectionName)') &
    mysql --user=MY_USERNAME --password= --protocol=tcp --host=127.0.0.1 --port=3306 --ssl-mode=disabled
    

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2017-11-03)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0 / CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。