Cutting down permissions to a minimum

[consideRatio]

I've been using the cloudsql-proxy for a while, and wonder if perhaps I've given it too much permissions? Google Cloud Console recommended that I cut down on my permissions to from all of the permissions of a "Cloud SQL Admin" to cloudsql.instances.connect and cloudsql.instances.get as given to the "Cloud SQL Client" role. Could this be enough? I only know that during the last 89 days, it has been enough, and my proxy has restarted in this time frame as well.

The permissions below are "Cloud SQL Admin" permissions that we would loose by downgrading to the "Cloud SQL Client" role, which was recommended by GCP platform based on me not using it anyhow:

cloudsql.backupRuns.create
cloudsql.backupRuns.delete
cloudsql.backupRuns.get
cloudsql.backupRuns.list
cloudsql.databases.create
cloudsql.databases.delete
cloudsql.databases.get
cloudsql.databases.list
cloudsql.databases.update
cloudsql.instances.addServerCa
cloudsql.instances.clone
cloudsql.instances.create
cloudsql.instances.delete
cloudsql.instances.demoteMaster
cloudsql.instances.export
cloudsql.instances.failover
cloudsql.instances.import
cloudsql.instances.list
cloudsql.instances.listServerCas
cloudsql.instances.promoteReplica
cloudsql.instances.resetSslConfig
cloudsql.instances.restart
cloudsql.instances.restoreBackup
cloudsql.instances.rotateServerCa
cloudsql.instances.startReplica
cloudsql.instances.stopReplica
cloudsql.instances.truncateLog
cloudsql.instances.update
cloudsql.sslCerts.create
cloudsql.sslCerts.createEphemeral
cloudsql.sslCerts.delete
cloudsql.sslCerts.get
cloudsql.sslCerts.list
cloudsql.users.create
cloudsql.users.delete
cloudsql.users.list
cloudsql.users.update
resourcemanager.projects.get
resourcemanager.projects.list
serviceusage.quotas.get
serviceusage.services.get
serviceusage.services.list

Reference

About the predefined roles "Cloud SQL Admin" and "Cloud SQL Client": https://cloud.google.com/sql/docs/mysql/project-access-control

[kurtisvg]

The Cloud SQL Client is a role specifically designed for use the with Cloud SQL proxy. Do you have a specific page where we are recommending the Cloud SQL Admin role over Cloud SQL Client? In generally we try to follow the principle of least privilege, and recommend only what the user needs.

[consideRatio]

The Cloud SQL Client is a role specifically designed for use the with Cloud SQL proxy.

@kurtisvg thanks for your response and clarification!

I'm not sure how it is recommended to be configured, but there is this section in the README.md of this repo that I don't fully understand:

When the proxy authenticates under the default service account of the Compute Engine VM it is running on the VM must have at least the sqlservice.admin API scope ("https://www.googleapis.com/auth/sqlservice.admin") and the associated project must have the SQL Admin API enabled. The default service account must also have at least WRITER/EDITOR priviledges to any projects of target SQL instances.

Hmmm... I figure we need to consider multiple service accounts, and these are the two I think of at the moment. Do I get this right, and does this cover the discussion?

1. The proxy's credentials. These I think should be only given the right to connect by having a Cloud SQL Client role.
2. The user of the proxy's credentials against the database. I'm thinking for example postgresql instance user and password.

[kurtisvg]

I think so, but let me clarify a bit (because I think we've overloaded the term "service account"). There are two different layers of auth you have to deal with:

1. Authorization to connect to the Cloud SQL instance (this is on the Cloud SQL side)
2. Authorization to access a database on the instance (this is on the database engine side)

No1 requires you (or a service account acting on your behalf) to have the correct IAM permissions (which is the Cloud SQL Client role or higher) in for the project you are trying to connect to.

No2 requires a valid database user (and usually password) and is handled between the database and application itself.

[consideRatio]

@kurtisvg thanks again for the clarification! Are these statements from this projects README.md file perhaps outdated then?

When the proxy authenticates under the default service account of the Compute Engine VM it is running on the VM must have at least the sqlservice.admin API scope ("https://www.googleapis.com/auth/sqlservice.admin") and the associated project must have the SQL Admin API enabled.

I'm thinking that the associated project may need to have the SQL administrative API enabled, but the requester should not need any admin permissions when interacting with it, only the Cloud SQL Client role's associated permissions.

[kurtisvg]

I think you are confusing the Cloud SQL Admin IAM role with the Cloud SQL Admin API.

The Cloud SQL Admin API is the name of the API that allows you to administer Cloud SQL resources in project. It does things like created, modify, delete Cloud SQL instances. This API needs to be enabled in your project.

The Cloud SQL Client and Cloud SQL Admin IAM roles give users certain permissions on the Cloud SQL Admin API. For example, the Cloud SQL Client gives the sqladmin.create and sqladmin.get permissions, which means a user with this IAM role can only use those two calls. The Cloud SQL Admin is more of an administrative role, which gives users more authority to delete and create new instances.

In order to use the Cloud SQL proxy, you need to have both enabled the Cloud SQL Admin API and have the correct IAM permissions (Cloud SQL Client or greater).

[consideRatio]

The default service account must also have at least WRITER/EDITOR priviledges to any projects of target SQL instances.

Pieces are falling into place! But, this sentence remains confusing. What is meant to be stated by this sentence? It sounds to me that it claims the GCP Service Account used by the proxy needs more priviledges than a Cloud SQL Client role can provide.

[kurtisvg]

I agree that sentence doesn't make sense - it's possible that it was added in before the Client role was added, and probably should be updated.

[consideRatio]

My understanding summarized

1. The cloudsql-proxy relies on an API called the Cloud SQL API (aka. sqladmin.googleapis.com in gcloud). Therefore, it needs to be enabled on the GCP project with a Cloud SQL instance running that the cloudsql-proxy is meant to proxy traffic to.
2. The cloudsql-proxy needs to use credentials that are coupled with the role Cloud SQL Client in the project where the Cloud SQL Instance is running. It will rely on this roles permissions called cloudsql.instances.connect and cloudsql.instances.get.

---

The current sentence in the README.md is:

When the proxy authenticates under the default service account of the Compute Engine VM it is running on the VM must have at least the sqlservice.admin API scope ("https://www.googleapis.com/auth/sqlservice.admin") and the associated project must have the SQL Admin API enabled. The default service account must also have at least WRITER/EDITOR priviledges to any projects of target SQL instances.

• The sqlservice.admin API scope relates to content here: https://cloud.google.com/sql/docs/mysql/admin-api, and I think it is irrelevant and wrong to discuss in the README.
• The "Cloud SQL API" needs to be enabled, not the "SQL Admin API".
• There is no need to have WRITER/EDITOR privileges for anything, only the "Cloud SQL Client" role and its associated permissions cloudsql.instances.connect and .get.

[kurtisvg]

• The sqlservice.admin API scope relates to content here: https://cloud.google.com/sql/docs/mysql/admin-api, and I think it is irrelevant and wrong to discuss in the README.

This refers to an the access scope required when using Compute service account.

• The "Cloud SQL API" needs to be enabled, not the "SQL Admin API".

These are both referring to the same thing, but it's "official" name is "Cloud SQL Admin API". I filed an internal bug for the resource you linked above to correct the title.

• There is no need to have WRITER/EDITOR privileges for anything, only the "Cloud SQL Client" role and its associated permissions cloudsql.instances.connect and .get.

These are the minimum required, but if you happen to have one of the other roles

As an aside, we have a new "how-to" page in the works from the docs side that will largely replace the instructions in the README.