Feature Request: Service account impersonation for GCP state connections

[tanghyd]

Hello, I have been trying the service account impersonation feature released in v0.164.0 (see: #3946) and it appears to be working correctly with the bigquery connection - thank you!

However, it seems like service account impersonation is only be available for the GCP auth to BigQuery and not the GCP auth to the state database (GCP postgres running on CloudSQL). See this raised error from pydantic:

Error: 1 validation error for Config
gateways.bigquery.state_connection.impersonated_service_account
  Extra inputs are not permitted [type=extra_forbidden, input_value='<service-account>@<project-id>.iam.gserviceaccount.com', input_type=str]
    For further information visit https://errors.pydantic.dev/2.10/v/extra_forbidden

So: it seems like there is no way to use the same impersonated service account to query both the Bigquery database connection the GCP postgres state database connection.

The use case I have is that I would prefer to impersonate my "sqlmesh-runner" service account (with associated BigQuery/CloudSQL IAM roles) during the SQLMesh CI/CD github actions deployment process (which has other IAM roles related to deployment, etc.).

[tobymao]

we don't have plans to implement this, but we may accept a well crafted and tested pr if you need this

[amardeep]

Would love if you reconsider this and add support for service account impersonation for gcp postgres.

Mainly, I would like the tables etc. in the state db to be owned by a service account, and not by the gcp user who ran the sqlmesh commands like init or migrate.