SQLMesh arbitrarily restricts GCP Postgres automatic IAM authentication

[Ant59]

The GCP CloudSQL Connector library used by this project (https://pypi.org/project/cloud-sql-python-connector/) provides Automatic IAM Database Authentication to use the ADC (Application Default Credentials) to authenticate. This allows clients to authenticate using local credentials whether they are a human running on a local machine or a service account running e.g. in a GKE cluster.

However, SQLMesh arbitrarily restricts this functionality. When setting up the GCP Postgres gateway, I set enable_iam_auth as true (per https://sqlmesh.readthedocs.io/en/stable/integrations/engines/gcp-postgres/#connection-options), but SQLMesh demands that the user field is also filled out, eliminating the underlying connector library's functionality for ADC-based authentication.

[simon-pactum]

demands that the user field is also filled out

Isn't this a limitation of how cloudsql IAM auth works? Even though it authenticates on IAM level as some identity, it still expects the user to be specified on the database connection

---

This is how I do it, maybe it's useful for you too:

I've been taking the user from environment variable in the gateway connection config:

user: {{ env_var('DWH_DATABASE_USER') }}

And the the local environment, however it's managed, makes sure to set that to the right value, e.g.:

export DWH_DATABASE_USER=$(gcloud config get-value account)