You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "p-a-a-a-trick (via GitHub)" <gi...@apache.org> on 2026/02/21 16:00:09 UTC
[I] IAM Auth Login for CloudSQL (GCP, Postgres, Python) [arrow-adbc]
p-a-a-a-trick opened a new issue, #4010:
URL: https://github.com/apache/arrow-adbc/issues/4010
### 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](https://docs.columnar.tech/dbc/getting_started/first_steps/#loading-using-a-driver)).
Right now, I can connect through SQL alchemy using this function:
```python
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] IAM Auth Login for CloudSQL (GCP, Postgres, Python) [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #4010:
URL: https://github.com/apache/arrow-adbc/issues/4010#issuecomment-3940445763
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...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] IAM Auth Login for CloudSQL (GCP, Postgres, Python) [arrow-adbc]
Posted by "p-a-a-a-trick (via GitHub)" <gi...@apache.org>.
p-a-a-a-trick commented on issue #4010:
URL: https://github.com/apache/arrow-adbc/issues/4010#issuecomment-3939011140
@ianmcook
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] IAM Auth Login for CloudSQL (GCP, Postgres, Python) [arrow-adbc]
Posted by "p-a-a-a-trick (via GitHub)" <gi...@apache.org>.
p-a-a-a-trick commented on issue #4010:
URL: https://github.com/apache/arrow-adbc/issues/4010#issuecomment-4414187250
This workaround is partially AI-generated, but it works for me. I wanted to let developers pick up the repo, pick a project and start rolling with it after a couple of incantations. These are the quickstart directions in my project (which is not open source, but I figured this could help someone with the same problem that I wanted to solve):
1. Clone the repo.
2. Create the env: `conda env create -f environment.yml`
3. Activate it: `conda activate <name>`
4. Install this package as editable: `pip install -e .`
5. Auth & Proxy: `source gauth.sh { PROJECT } { REGION } { INSTANCE }`
6. e.g. `source ./bin/gauth.sh project-a us-west1 pg-instance-01`. This will open a browser for you to log on to your google account
7. Test connections: `python3 tests/test_connections/test_connections.py`.
Auth is handled by google+ADBC, so no need for an env file or anything extra.
Need the following from conda-forge (might be missing something; sorry):
- google-auth
- google-cloud-sdk
- adbc-driver-postgresql
```bash
#!/bin/bash
# Usage: source ./bin/gauth.sh <project-id> <region> <instance>
export GCP_PROJECT="$1"
export GCP_REGION="$2"
export GCP_INSTANCE="$3"
if [ -z "$GCP_PROJECT" ] || [ -z "$GCP_REGION" ]; then
echo "❌ Usage: source bin/gauth.sh <project-id> <region>"
return 1 2>/dev/null || exit 1
fi
# 1. Identify User and Handle Auth
if ! gcloud config get-value account &>/dev/null; then
echo "🔑 No active account found. Please log in..."
gcloud auth login --update-adc
else
# Check if the token is actually valid; if not, force a login
if ! gcloud auth print-access-token &>/dev/null; then
echo "🔄 Session expired. Re-authenticating..."
gcloud auth login --update-adc
fi
fi
GCP_USER=$(gcloud config get-value account)
export GCP_USER
# 2. Locate/Download Proxy (Logic remains the same)
GCLOUD_BIN=$(which gcloud)
SDK_BIN_DIR=$(dirname "$GCLOUD_BIN")
PROXY_BIN="$SDK_BIN_DIR/cloud-sql-proxy"
if [ ! -f "$PROXY_BIN" ]; then
echo "📦 Downloading Cloud SQL Proxy..."
OS_NAME=$(uname -s | tr '[:upper:]' '[:lower:]')
ARCH=$(uname -m)
[[ "$ARCH" == "x86_64" ]] && ARCH="amd64"
[[ "$ARCH" == "arm64" || "$ARCH" == "aarch64" ]] && ARCH="arm64"
URL="https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.14.2/cloud-sql-proxy.${OS_NAME}.${ARCH}"
curl -L -o "$PROXY_BIN" "$URL" && chmod +x "$PROXY_BIN"
fi
# 3. Set Project
# We use --quiet here ONLY after we are sure we are logged in
gcloud config set project "$GCP_PROJECT" --quiet
gcloud config set compute/region "$GCP_REGION" --quiet
# 4. Cleanup and Start Proxy
pkill -f "cloud-sql-proxy" || true
sleep 1
echo "📡 Starting Proxy for $GCP_USER..."
"$PROXY_BIN" --address 127.0.0.1 --port 5432 --auto-iam-authn "$GCP_PROJECT:$GCP_REGION:$GCP_INSTANCE" > /tmp/cloud-sql-proxy.log 2>&1 &
disown
echo "✅ Environment variables exported:"
echo " GCP_PROJECT=$GCP_PROJECT"
echo " GCP_USER=$GCP_USER"
echo " GCP_REGION=$GCP_REGION"
```
connection:
```python
USER = subprocess.check_output(
["gcloud", "config", "get-value", "account"], text=True
).strip()
PROJECT = subprocess.check_output(
["gcloud", "config", "get-value", "project"], text=True
).strip()
REGION = subprocess.check_output(
["gcloud", "config", "get-value", "compute/region"], text=True
).strip()
def adbc_eng_iam():
# 1. Make sure user and project are not None
if not USER or not PROJECT:
raise OSError(
"gcloud CLI not configured. Run 'gcloud auth login' and 'gcloud config set project <ID>'."
)
# 2. Encode email user accounts
safe_user = urllib.parse.quote_plus(USER)
# 3. Connect
print(f"""
🔗 ADBC connecting to project
project : [{PROJECT}]
user : [{USER}]
region : [{REGION}]
""")
uri = f"postgresql://{safe_user}@127.0.0.1:5432/postgres"
return pg_adbc.connect(uri)
```
then usage:
`conn = adbc_eng_iam()`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org