IAM Auth Login for CloudSQL (GCP, Postgres, Python)

[p-a-a-a-trick]

What feature or improvement would you like to see?

I have a GCP CloudSQL instance running postgres and I would like to connect to it through ADBC using IAM auth login (similar to the dbc.how "getting started" BigQuery method).

Right now, I can connect through SQL alchemy using this function:

import sqlalchemy
from google.cloud.sql.connector import Connector, IPTypes


def sa_eng_pg_iam(project, user, region="us-east1", pg_instance="01"):
    # 1. Initialize Cloud SQL Python Connector
    connector = Connector()

    # 2. Connection function
    def getconn():
        instance_str = f"{project}:{region}:pg-{project}-{pg_instance}"
        conn = connector.connect(
            instance_connection_string=instance_str,  # INSTANCE_CONNECTION_NAME
            driver="pg8000",
            user=user,  # IAM Database User
            db="postgres",
            enable_iam_auth=True,  # Enable IAM Auth
            ip_type=IPTypes.PUBLIC,  # Use IPTypes.PRIVATE for VPC
        )
        return conn

    # 3. Create SQLAlchemy pool
    pool = sqlalchemy.create_engine(
        "postgresql+pg8000://",
        creator=getconn,
    )
    return pool

From there I can return the sqlalchemy engine by using only my IAM user email after authing through gcloud blah blah blah.

It would be nice to have a similar method with ADBC.

[p-a-a-a-trick]

@ianmcook

[lidavidm]

It appears Google's Python library wraps certain Postgres (or MySQL/MSSQL) drivers and calls GCP APIs to get auth and other configs before effectively creating a regular connection and wrapping the driver. So unlike Redshift, which actually modifies the PostgreSQL protocol for its auth, in theory we can at least replicate this without forking libpq. That said I'm a little hesitant to bring the entire GCP SDK, and whatever build problems it has, into the project, but we can figure something out...