Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can't connect to instance using SA impersonation and Cloud IAM authentication #2338

Closed
hsuabina opened this issue Nov 26, 2024 · 9 comments
Closed
Assignees
Labels
priority: p2 Moderately-important priority. Fix may not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.

Comments

@hsuabina
Copy link

hsuabina commented Nov 26, 2024

Bug Description

I'm trying to connect to a Cloud SQL Instance using the cloud-sql-proxy binary with service account impersonation and Cloud IAM authentication in the Cloud SQL side, but I'm unable to get it working.

Example code (or command)

I'm running this command to start the proxy:

cloud-sql-proxy --auto-iam-authn --impersonate-service-account <SA EMAIL> <INSTANCE> -p <PORT>

The output I get after start the proxy:

Impersonating service account with Application Default Credentials
[INSTANCE] Listening on 127.0.0.1:<PORT>
The proxy has started successfully and is ready for new connections!

Then in another terminal I try to connect to the instance with psql running this command:

psql -h 127.0.0.1 \
  -U '<SA EMAIL without the '.gserviceaccount.com' suffix> ' \
  --port <PORT> \
  --dbname=<DB>

Stacktrace

The `psql` throws this error:

psql: error: connection to server at "127.0.0.1", port <PORT> failed: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

And in the proxy's terminal I can see this error as well:

[<INSTANCE>] Accepted connection from 127.0.0.1:62188
[<INSTANCE>] failed to connect to instance: failed to get instance: Refresh error: failed to get instance metadata (connection name = "<INSTANCE>"): Get "https://sqladmin.googleapis.com/sql/v1beta4/projects/<PROJECT>/instances/<INSTANCE NAME>/connectSettings?alt=json&prettyPrint=false": impersonate: status code 404: {
  "error": {
    "code": 404,
    "message": "Account deleted: 281475683274111",
    "status": "NOT_FOUND"
  }
}

Steps to reproduce?

See examples above

Environment

  1. OS: MacOS Sonoma 14.6.1 (23G93)
  2. Cloud SQL Proxy version: 2.14.0+darwin.amd64

Additional Details

Something that I think could be relevant is that the principal I'm using to impersonate the service account has permissions to do so by being member of a Cloud Identity group. It's the group that has the permission to impersonate the service account and the service account what was granted the Cloud SQL Instance user role.

The same principal also has permissions to access the cloud sql instance directly without SA impersonation, because the group it belongs to also was granted the Cloud SQL Instance user role. When trying to connect this way, the connection works without issues.

The idea behind this approach is two support two ways of connecting to the instance:

  • When connecting directly with your principal you get reduced postgres permissions
  • When connecting by impersonating the service account you get elevated permissions
@hsuabina hsuabina added the type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns. label Nov 26, 2024
@hsuabina
Copy link
Author

Something I find strange is that the cloud-sql-proxy binary seems to connect normally even if I make up the SA EMAIL.

Running this command:

cloud-sql-proxy --auto-iam-authn --impersonate-service-account=dummy <INSTANCE> -p <PORT>

Still shows this output:

Impersonating service account with Application Default Credentials
[<INSTANCE>] Listening on 127.0.0.1:5437
The proxy has started successfully and is ready for new connections!

Obviously dummy is not a valid service account email :)

@jackwotherspoon jackwotherspoon added the priority: p2 Moderately-important priority. Fix may not be included in next release. label Nov 26, 2024
@jackwotherspoon
Copy link
Collaborator

Thanks for raising this issue @hsuabina 😄

Let me try and reproduce it, the fact --impersonate-service-account=dummy is starting up without an error seems strange to me, will play around and report back.

@hsuabina
Copy link
Author

hsuabina commented Nov 28, 2024

I've reviewed my setup and while I found an error in the permissions being granted to the SA I'm trying to connect with... fixing it didn't help. Right now I'm trying to simplify the problem and I'm using a credentials key file to authenticate the SA with cloud-sql-proxy.

So, basically I'm running with this command, which seems to work:

cloud-sql-proxy -c <PATH TO CREDENTIALS FILE> --auto-iam-authn <CLOUDSQL INSTANCE> -p <PORT>

2024/11/28 11:32:45 Authorizing with the credentials file at "<PATH TO CREDENTIALS FILE>"
2024/11/28 11:32:45 [<INSTANCE>] Listening on 127.0.0.1:<PORT>
2024/11/28 11:32:45 The proxy has started successfully and is ready for new connections!

But then when trying to connect with psql I get this error:

psql -h 127.0.0.1 \
  --user '<SA email without .gserviceaccount.com suffix>' \
  --port <PORT> \
  --dbname=<DB NAME>

psql: error: connection to server at "127.0.0.1", port <PORT> failed: FATAL:  Cloud SQL IAM service account authentication failed for user "<SA email without .gserviceaccount.com suffix>"

The SA has the Cloud SQL Client role on the project so it should be able to connect to any Cloud SQL instance.

@jackwotherspoon
Copy link
Collaborator

The SA has the Cloud SQL Client role on the project so it should be able to connect to any Cloud SQL instance.

Does the SA also have the Cloud SQL Instance User IAM role? This is additionally required when using IAM AuthN.

Mentioned on our Log in using IAM Database Authentication. If we are missing it somewhere else, let me know and I will update our documentation accordingly.

@hsuabina
Copy link
Author

I had granted that role earlier instead of Cloud SQL Client as per the docs you referred. I think I got confused with the Cloud SQL IAM roles page. By reading the roles there is not so clear wether you only need Cloud SQL Instance User or also Cloud SQL Client.

Anyway, now the SA has both, yet I'm still unable to connect, same output.

@hsuabina
Copy link
Author

hsuabina commented Nov 28, 2024

Here is a summary of the setup, I'm also not redacting names in case I'm missing something:

  • Cloud SQL instance has flag cloudsql.iam_authentication=ON
  • Cloud SQL user for the SA is created with type IAM (service account)
  • SA has Cloud SQL Client and Cloud SQL Instance User roles on the same project than the Cloud SQL instance

Then, opening the proxy with this command:

cloud-sql-proxy --impersonate-service-account <SERVICE_ACCOUNT> -c secrets/prisma-alpha-gsa.json --auto-iam-authn <INSTANCE_CONNECTION_NAME> -p 5437

2024/11/28 16:25:51 Impersonating service account with the credentials file at "secrets/prisma-alpha-gsa.json"
2024/11/28 16:25:51 [INSTANCE_CONNECTION_NAME] Listening on 127.0.0.1:5437
2024/11/28 16:25:51 The proxy has started successfully and is ready for new connections!

And then trying to connect with psql:

psql -h 127.0.0.1 \
  --user 'prisma-alpha-gsa@octane11-alpha.iam.' \
  --port 5437 \
  --dbname=octane11

psql: error: connection to server at "127.0.0.1", port 5437 failed: FATAL:  password authentication failed for user "prisma-alpha-gsa@octane11-alpha.iam."

In the proxy logs, there is simply a connection that's closed immediately:

2024/11/28 16:26:07 [INSTANCE_CONNECTION_NAME] Accepted connection from 127.0.0.1:55573
2024/11/28 16:26:07 [INSTANCE_CONNECTION_NAME] client closed the connection

@jackwotherspoon
Copy link
Collaborator

Your service account IAM database user's username is not formatted properly, try removing the trailing dot "." from the end.

i.e. "my-sa@my-project.iam.gserviceaccount.com" -> "my-sa@my-project.iam" with no dot after "iam"

@hsuabina
Copy link
Author

Great, I was finally able to connect to the PG DB. Also, I was able to impersonate the SA without the credentials file as well.

For the record, these are the key steps I had to take:

  • Cloud SQL instance has flag cloudsql.iam_authentication=ON
  • Cloud SQL user for the SA is created with type IAM (service account)
  • SA has Cloud SQL Client and Cloud SQL Instance User roles on the same project than the Cloud SQL instance
  • Principal impersonating the service account is granted Service Account Token Creator for the service account impersonated (SA acting as a resource and not a member here)

Thanks for the help Jack, much appreciated!

I'm closing this issue.

@jackwotherspoon
Copy link
Collaborator

Glad you were able to get it working @hsuabina 👏 👍

Thanks for leaving the detailed steps for future users, they are super useful. I'll add a detailed section to our Google Cloud docs and README for service account impersonation to make life easier 😄

Have a great weekend!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
priority: p2 Moderately-important priority. Fix may not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.
Projects
None yet
Development

No branches or pull requests

2 participants