_perform_refresh shows `Cloud SQL Client` is not grated when actually it's `Service Usage Consumer` permission is missed

uriyyo commented 9 hours ago

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.

https://github.com/GoogleCloudPlatform/cloud-sql-python-connector/blob/d622575cab34c0dc85763076f7c404e7265c3f26/google/cloud/sql/connector/client.py#L258-L262

https://github.com/GoogleCloudPlatform/cloud-sql-python-connector/blob/d622575cab34c0dc85763076f7c404e7265c3f26/google/cloud/sql/connector/client.py#L128-L131

Here we override exception message: https://github.com/GoogleCloudPlatform/cloud-sql-python-connector/blob/d622575cab34c0dc85763076f7c404e7265c3f26/google/cloud/sql/connector/instance.py#L136-L137

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?

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

OS type and version: macOS 14.6.1
Python version: 3.12
Cloud SQL Python Connector version: 1.14.0

Additional Details

No response

jackwotherspoon commented 7 hours ago

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 commented 7 hours ago

Hi @jackwotherspoon,

We are using PostgreSQL instance together with Cloud Run Funciton

jackwotherspoon commented 7 hours ago

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 commented 4 hours ago

@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?

GoogleCloudPlatform / cloud-sql-python-connector