Access token from gcloud auth application-default login expires after inactivity and isn't auto-refreshed by Cloud SQL Proxy

[rvishruth]

Bug Description

The Cloud SQL Proxy fails to automatically refresh the OAuth 2.0 access token obtained via gcloud auth application-default login after a period of inactivity. This leads to authentication errors when trying query the database, with the following error:

googleapi: Error 401: Request had invalid authentication credentials.

This issue seems to occur only after prolonged periods (6-12 hours) of inactivity.

Expected behavior: The Cloud SQL Proxy should automatically refresh the access token as needed. (See: https://cloud.google.com/sql/docs/mysql/sql-proxy#benefits_of_the)

Example code (or command)

N/A

Stacktrace

date 10:44:56 [<instance-connection-name>] Accepted connection from 127.0.0.1:52346
date 10:44:56 [<instance-connection-name>] failed to connect to instance: failed to get instance: Refresh error: failed to get instance metadata (connection name = "<instance-connection-name>"): googleapi: Error 401: Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.
Details:
[
  {
    "@type": "type.googleapis.com/google.rpc.ErrorInfo",
    "metadata": {
      "method": "google.cloud.sql.v1beta4.SqlConnectService.GetConnectSettings",
      "service": "sqladmin.googleapis.com"
    },
    "reason": "ACCESS_TOKEN_TYPE_UNSUPPORTED"
  }
]

More details:
Reason: authError, Message: Invalid Credentials
date 10:44:56 [<instance-connection-name>] Accepted connection from 127.0.0.1:52347
date 10:44:56 [<instance-connection-name>] failed to connect to instance: failed to get instance: Refresh error: failed to get instance metadata (connection name = "<instance-connection-name>"): googleapi: Error 401: Request had invalid authentication credentials.

Steps to reproduce?

1. Authenticate using gcloud auth application-default login.
2. Start the Cloud SQL Proxy using ./cloud-sql-proxy.x64.exe --config-file config.toml
3. Leave the proxy inactive for several hours.
4. Attempt to reconnect and observe that the proxy encounters a 401 error due to expired credentials.

Environment

1. OS type and version: Windows 11 Home
2. Cloud SQL Proxy version (./cloud-sql-proxy --version): cloud-sql-proxy version 2.11.4+windows.amd64
3. Proxy invocation command (for example, ./cloud-sql-proxy --port 5432 INSTANCE_CONNECTION_NAME): ./cloud-sql-proxy.x64.exe --config-file config.toml

config.toml

# use instance-connection-name-0, instance-connection-name-1, etc.
# for multiple instances
instance-connection-name = "redacted"
auto-iam-authn = true
debug = true
debug-logs = true
port = "5432"

Additional Details

N/A

[jackwotherspoon]

Hi @rvishruth Thanks for raising an issue on the Cloud SQL Proxy 😄 I can hopefully help shed some clarity for you on your issue.

Setting ADC via gcloud auth application-default login should work, but maybe something in your environment or Org policy constraints is limiting the number of times the token can be refreshed?

I will try and reproduce this on my end, in the meantime after running the login command you should see a Credentials saved to file: ...path.

If you inspect the file at the given path do you see a refresh_token field? Maybe your credentials are being set in a way where they are not able to be refreshed and thus expire...

[jackwotherspoon]

@rvishruth another thing I wonder if you could try for me is once you see a 401 error if you could try re-starting the Proxy.

If the Proxy successfully restarts and successfully accepts new connections than it would point at an issue with the Proxy, if it restarts with 401's and requires gcloud auth application-default login to be ran again then it would point at an auth issue outside of the Proxy.

[rvishruth]

Hey @jackwotherspoon! Thank you for getting back to me!

Setting ADC via gcloud auth application-default login should work, but maybe something in your environment or Org policy constraints is limiting the number of times the token can be refreshed?

We haven't changed any defaults on Google cloud here. But I'll check this and update the thread.

If you inspect the file at the given path do you see a refresh_token field? Maybe your credentials are being set in a way where they are not able to be refreshed and thus expire...

I do see a refresh token field. Here's the redacted credentiaals.json

{
  "account": "",
  "client_id": "<redacted_string>.apps.googleusercontent.com",
  "client_secret": "<redacted_secret_string>",
  "quota_project_id": "<redacted_project_name>",
  "refresh_token": "<redacted_token_string>",
  "type": "authorized_user",
  "universe_domain": "googleapis.com"
}

@rvishruth another thing I wonder if you could try for me is once you see a 401 error if you could try re-starting the Proxy.

You're right! Seems to be an something related to how many times you can refresh the token/how long a session is valid - https://support.google.com/a/answer/9368756. If I try to restart the Proxy, I get this error, which gets resolved after I do gcloud auth application-default login

2024/09/11 10:19:00 Authorizing with Application Default Credentials
2024/09/11 10:19:00 [PROJECT_ID:REGION:INSTANCE_NAME] Connection info added to cache
2024/09/11 10:19:00 [PROJECT_ID:REGION:INSTANCE_NAME] Connection info refresh operation started
2024/09/11 10:19:00 [PROJECT_ID:REGION:INSTANCE_NAME] Listening on 127.0.0.1:5432
2024/09/11 10:19:00 The proxy has started successfully and is ready for new connections!
2024/09/11 10:19:00 Enabling pprof endpoints at localhost:9091
2024/09/11 10:19:01 [PROJECT_ID:REGION:INSTANCE_NAME] Connection info refresh operation failed, err = failed to get instance: Refresh error: failed to get instance metadata (connection name = "PROJECT_ID:REGION:INSTANCE_NAME"): Get "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/connectSettings?alt=json&prettyPrint=false": auth: cannot fetch token: 400
Response: {
  "error": "invalid_grant",
  "error_description": "reauth related error (invalid_rapt)",
  "error_uri": "https://support.google.com/a/answer/9368756",
  "error_subtype": "invalid_rapt"
}
2024/09/11 10:19:01 [PROJECT_ID:REGION:INSTANCE_NAME] Connection info refresh operation scheduled immediately
2024/09/11 10:19:01 [PROJECT_ID:REGION:INSTANCE_NAME] Removing connection info from cache: failed to get instance: Refresh error: failed to get instance metadata (connection name = "PROJECT_ID:REGION:INSTANCE_NAME"): Get "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/connectSettings?alt=json&prettyPrint=false": auth: cannot fetch token: 400
Response: {
  "error": "invalid_grant",
  "error_description": "reauth related error (invalid_rapt)",
  "error_uri": "https://support.google.com/a/answer/9368756",
  "error_subtype": "invalid_rapt"
}
2024/09/11 10:19:03 Sending SIGINT signal for proxy to shutdown.

What's the recommend way to maintain long lived connections that don't expire?

[jackwotherspoon]

@rvishruth Thanks for the additional details! Based on your response this is indeed an auth issue and not related to the Proxy itself. The Proxy sources Application Default Credentials (ADC) from the environment. If these credentials are invalid and not able to be refreshed then the Proxy will not work as expected.

You could try following the instructions in the link you provided, specifically this section:

You can also check the Exempt trusted apps box to exempt trusted apps from reauthentication. (Trusted apps are marked as Trusted on the App access control page. For more details, see Preparing for broad rollout below

For a local deployment, while not ideal but probably the easiest path forward would be downloading a long-lived service account key json file and setting the GOOGLE_APPLICATION_CREDENTIALS env variable to its path. (more info)

Otherwise you could try to alter your Org policies to allow longer living user credentials.

[enocom]

FWIW I stared the Proxy at ~9am my time, connected, let it sit, and now at ~4pm it's still working just fine. So I suspect this has to do with an organization policy setting as @jackwotherspoon said.

[rvishruth]

Got it, thanks for the suggestions @jackwotherspoon!

That makes sense @enocom! I think it usually takes a little longer to expire for me but I agree with you that its an organization setting!

[jackwotherspoon]

@rvishruth I am going to close this issue out as the root cause seems to be an auth issue in your environment and is not to do with the inner workings of the Cloud SQL Proxy. (feel free to re-open if you have any findings that suggest otherwise 😄)

Thanks again for raising this and hopefully if other users run into this auth issue in the future this thread will provide insights.

Have a great day,
Jack