`Service Usage Consumer` permission is required but missing from error message

[uriyyo]

Bug Description

When I try to connect to a Cloud SQL instance using the google.cloud.sql.connector.Connector I'm getting error:
Forbidden: Authenticated IAM principal does not seem authorized to make API request. Verify 'Cloud SQL Admin API' is enabled within your GCP project and 'Cloud SQL Client' role has been granted to IAM principal.

But I have already granted the Cloud SQL Client role to the service account. Actually, the issue is with the Service Usage Consumer permission. Under the hood, the google.cloud.sql.connector.Connector uses the Service Usage Consumer permission to get service metadata. So, you need to grant the Service Usage Consumer permission to the service account to fix this issue.

cloud-sql-python-connector/google/cloud/sql/connector/client.py

Lines 258 to 262 in d622575

	self._get_metadata(
	project,
	region,
	instance,
	)

cloud-sql-python-connector/google/cloud/sql/connector/client.py

Lines 128 to 131 in d622575

	resp = await self._client.get(url, headers=headers)
	if resp.status >= 500:
	resp = await retry_50x(self._client.get, url, headers=headers)
	resp.raise_for_status()

Here we override exception message:

cloud-sql-python-connector/google/cloud/sql/connector/instance.py

Lines 136 to 137 in d622575

	if e.status == 403:
	e.message = "Forbidden: Authenticated IAM principal does not seem authorized to make API request. Verify 'Cloud SQL Admin API' is enabled within your GCP project and 'Cloud SQL Client' role has been granted to IAM principal."

Because of this issue, the error message is misleading and confusing (I spent a lot of time debugging this issue 😅 ).

Example code (or command)

import pymysql.connections
import sqlalchemy
from google.cloud.sql.connector import Connector

connector = Connector()


def getconn() -> pymysql.connections.Connection:
    conn: pymysql.connections.Connection = connector.connect(
        "project:region:instance",
        "pymysql",
        user="my-user",
        password="my-password",
        db="my-db-name",
        enable_iam_auth=True,
    )
    return conn


pool = sqlalchemy.create_engine(
    "mysql+pymysql://",
    creator=getconn,
)

Stacktrace

No response

Steps to reproduce?

1. Try to connect to a Cloud SQL instance with SQL Admin API enabled and Cloud SQL Client role, but without Service Usage Consumer role.

Environment

1. OS type and version: macOS 14.6.1
2. Python version: 3.12
3. Cloud SQL Python Connector version: 1.14.0

Additional Details

No response

[jackwotherspoon]

Hi @uriyyo thanks for raising an issue on the Cloud SQL Python Connector! 😄

I am curious as to where you are deploying the application? Is it Cloud Run, GKE, et?.

I wonder if the Service Usage Consumer is a permission that is only required in a certain serverless environment.

[uriyyo]

Hi @jackwotherspoon,

We are using PostgreSQL instance together with Cloud Run Funciton

[jackwotherspoon]

We are using PostgreSQL instance together with Cloud Run Function

Thanks for the quick reply @uriyyo 😄

Let me try and quickly reproduce the issue in my own Cloud Run Function, then I will take a look at your PR, thanks again! 👏

[jackwotherspoon]

@uriyyo I have not been able to reproduce this issue yet. I am able to successfully connect with the Python Connector in a Cloud Run Function with purely the Cloud SQL Client role.

Is there any other part of your setup I should know about that may be causing the need for the Service Usage Consumer role? Is your Cloud Run Function in a different project than your Cloud SQL instance?

[uriyyo]

Hi @jackwotherspoon,

Sorry for long response. This issue happens when we connect to database from external instance that is not part
of GCP. We are using Cloud SQL Auth Proxy to connect to Cloud SQL instance.

In order to connect to SQL Instance we created separate service account and granted it Cloud SQL Client and Cloud SQL Instance User roles.
Also, we are using IAM database authentication on SQL instance side.

Here is steps to reproduce:

1. Create service account.
2. Grant only Cloud SQL Client and Cloud SQL Instance User roles to service account.
3. Create key for service account and download it.
4. Run python script and try to connect to Cloud SQL instance using service account.

Here is code snippet:

from google.cloud.sql.connector import Connector
from google.oauth2.service_account import Credentials
from sqlalchemy import create_engine, text

credentials = Credentials.from_service_account_file("service-account-key.json")
PROJECT_ID = "your-project-id"

connector = Connector(
    ip_type="public",
    quota_project=PROJECT_ID,
    credentials=credentials,
)


def getconn() -> Any:
    return connector.connect(
        "project:region:instance",
        "pg8000",
        user="service-account-email",
        db="db-name",
        enable_iam_auth=True,
    )


pool = create_engine(
    "postgresql+pg8000://",
    creator=getconn,
)

with pool.connect() as conn:
    conn.execute(text("SELECT 1"))

[jackwotherspoon]

Hi @uriyyo thanks for the detailed explanation, appreciate it.

So I think this now makes sense to me, connecting either to a Cloud SQL instance in a separate GCP project or from an external VM instance (not GCP) would indeed require the Service Usage Consumer IAM role.

Because you are needing to set the quota_project it makes sense that you need the Service Usage Consumer role as well to be able to consume quota from the project.

Service Usage Consumer role (docs):

Service Usage Consumer
(roles/serviceusage.serviceUsageConsumer)

Ability to inspect service states and operations, and consume quota and billing for a consumer project.

I am hoping to be able to work on #477 which should remove the need for the custom error message we are throwing currently about the IAM roles.

That being said I think your PR is worthwhile in the interim, I will take a look at it shortly. Thanks again for this.

[jackwotherspoon]

Hi @uriyyo, quick update from my side.

I have worked on properly exposing the actual Cloud SQL Admin API error responses to the end user in #1201

I believe once merged this should fix the issue here, I have not tested the service usage consumer error message path yet but I believe there should be one that would have pointed you in the right direction.

This is a better solution in my opinion then the custom error message I previously had implemented.