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

Client fails on createEphemeral #37

Closed
zhangandyx opened this issue May 18, 2016 · 54 comments
Closed

Client fails on createEphemeral #37

zhangandyx opened this issue May 18, 2016 · 54 comments

Comments

@zhangandyx
Copy link

@zhangandyx zhangandyx commented May 18, 2016

I'm getting an odd failure from following these instructions:

https://github.com/GoogleCloudPlatform/cloudsql-proxy#to-use-from-kubernetes

2016/05/17 23:59:41 Listening on 127.0.0.1:3306 for <project>:<region>:<instance>
2016/05/17 23:59:41 Ready for new connections
2016/05/17 23:59:51 New connection for "<project>:<region>:<instance>"
2016/05/17 23:59:52 couldn't connect to "<project>:<region>:<instance>": ensure that the account has access to "<project>:<region>:<instance>" (and make sure there's no typo in that name). Error during createEphemeral for <project>:<region>:<instance>: googleapi: Error 403: The client is not authorized to make this request., notAuthorized

Looking at the code, createEphemeral is failing, but I'm at a loss as to what is going on with the randomized key that's being created there.

@Carrotman42
Copy link
Contributor

@Carrotman42 Carrotman42 commented May 18, 2016

@zhangandyx
Copy link
Author

@zhangandyx zhangandyx commented May 18, 2016

Good catch! Apparently I was mixing them up depending on the context. Thanks!

@zhangandyx zhangandyx closed this May 18, 2016
@zhangandyx
Copy link
Author

@zhangandyx zhangandyx commented May 18, 2016

Actually, mind if I send in a PR that will do this check?

@Carrotman42
Copy link
Contributor

@Carrotman42 Carrotman42 commented May 19, 2016

@thuandt
Copy link

@thuandt thuandt commented Jun 9, 2016

I have same problem. I'm sure project:region:cloudsql-instance is right format.
My instance is second gen. MySQL 5.6

cloud_sql_proxy -dir=/tmp/cloudsql -instances=project:region:cloudsql-instance=tcp:3306 -credential_file=CloudSQL.json
2016/06/09 23:00:17 Listening on 127.0.0.1:3306 for project:region:cloudsql-instance
2016/06/09 23:00:17 Ready for new connections
2016/06/09 23:00:22 New connection for "project:region:cloudsql-instance"
2016/06/09 23:00:24 couldn't connect to "project:region:cloudsql-instance": ensure that the account has access to "project:region:cloudsql-instance" (and make sure there's no typo in that name). Error during createEphemeral for project:region:cloudsql-instance: googleapi: Error 403: The client is not authorized to make this request., notAuthorized

@Carrotman42
Copy link
Contributor

@Carrotman42 Carrotman42 commented Jun 15, 2016

@thuandt Is the service account associated with 'CloudSQL.json' added to the project which contains the Cloud SQL instance you want to connect to? Does it have an "editor" level privilege in that project?

@thuandt
Copy link

@thuandt thuandt commented Jun 15, 2016

@Carrotman42 I have both but it's still return this error.
After I delete 2nd Gen cluster and create another, its work like charm. So I think it's problem in Google CloudSQL service.

Thanks for your help.

@smichaku
Copy link

@smichaku smichaku commented Sep 15, 2016

I have the same problem as @thuandt ...

@Carrotman42
Copy link
Contributor

@Carrotman42 Carrotman42 commented Sep 15, 2016

@smichaku
Copy link

@smichaku smichaku commented Sep 19, 2016

@Carrotman42 - I sent the info you requested to cloud-sql@google.com. Still no reply.

@Carrotman42
Copy link
Contributor

@Carrotman42 Carrotman42 commented Sep 19, 2016

Apologies, I was off work this weekend. I've replied now, let's continue the discussion over email.

@robbertvancaem
Copy link

@robbertvancaem robbertvancaem commented Feb 20, 2017

Hi @Carrotman42 and @smichaku , just wondering, did you manage to overcome this? I'm looking at the same error right now. I already tried to re-generate my credentials by using a 'Cloud SQL Admin' Service Account but no cigar :(

@smichaku
Copy link

@smichaku smichaku commented Feb 21, 2017

@robbertvancaem - The problem for me was that service account related to the credentials file I was using was not added to the project. Once I added it the problem was gone.

@abstrctn
Copy link

@abstrctn abstrctn commented Feb 21, 2017

I've also been seeing this error over the past several days (running the proxy within Google Kubernetes Engine):

couldn't connect to "project:region:cloudsql-instance": ensure that the account has access to "project:region:cloudsql-instance" (and make sure there's no typo in that name). Error during createEphemeral for project:region:cloudsql-instance: googleapi: Error 403: The client is not authorized to make this request., notAuthorized

What's odd is that it only crops up after the sql proxy instance has been running - successfully - for a while. Most recently, my service account had both roles/cloudsql.admin and roles/cloudsql.editor access on my project.

After the error began occurring, restarting the proxy didn't change anything. Then I added roles/cloudsql.client and restarted my proxy and consumer, and I was able to successfully connect.

However, it seems like cloudsql.admin should have all of the access that cloudsql.client does. Furthermore, I then removed cloudsql.client (so my roles were exactly as they had been before), restarted the proxy, and things continued to work.

I wonder if this error could be caused by something independent of the permissions of the service account?

@anurag-itsolvs
Copy link

@anurag-itsolvs anurag-itsolvs commented Feb 22, 2017

Thanks @ abstrctn. I also had same issue. I am wondering that if we are giving only "Cloud SQL admin" role then it should worked.

@pavel-kurnosov
Copy link

@pavel-kurnosov pavel-kurnosov commented Feb 27, 2017

After testing with different access rights this is what I found:
Cloud SQL admin - no access ( Error during createEphemeral for instance: googleapi: Error 403: The client is not authorized to make this request., notAuthorized). All Cloud SQL not works for same reason;

So far the only access that is working - project-owner. I think I tried project editor and it not working for same reason.

Does anyone know what is happened during last weeks, changes? Should I create separate ticket for that?

@robbertvancaem
Copy link

@robbertvancaem robbertvancaem commented Feb 28, 2017

In IAM, the Service Account related to the project only had access to the Cloud SQL with a role of Editor. It did not yet have access to the actual project where the Service Account was used. A bit confusing, but adding the Service Account as a Project Editor as well resolved all my issues. Thanks for the help guys :-)

@vadimberezniker
Copy link

@vadimberezniker vadimberezniker commented Mar 6, 2017

Some clarification: 'Cloud SQL Client' is the right role to use for Proxy access.
The initial release of the role was missing a permission which is why you might have run into issues when you first tried it. At this point, you should be able to switch back to using only the Cloud SQL Client role.

None of the other Cloud SQL roles include connectivity access, you always need Cloud SQL Client role if you plan to connect via the Proxy.

@pilwon
Copy link

@pilwon pilwon commented Mar 11, 2017

@Laixer @Carrotman42 Cloud SQL Client still gives us the same error message as of today. Also tried Cloud SQL Admin, Cloud SQL Editor, and Cloud SQL Viewer but none of them works except for Project Owner.

@vadimberezniker
Copy link

@vadimberezniker vadimberezniker commented Mar 11, 2017

@pilwon Can you share the command line you are using to run the proxy (feel free to mask out any instance names, etc) as well as the exact error message you see?

@pilwon
Copy link

@pilwon pilwon commented Mar 11, 2017

@Laixer Same as the above:

$ docker run -it --rm \
  -v <local-certificate-file-path>:/etc/ssl/certs/ca-certificates.crt \
  -v <credential-file-path>:/credential.json \
  -p 127.0.0.1:3306:3306 \
  gcr.io/cloudsql-docker/gce-proxy \
    /cloud_sql_proxy \
    -instances=<project-id>:<region-id>:<sql-instance-id>=tcp:0.0.0.0:3306 \
    -credential_file=/credential.json

2017/03/11 19:49:58 using credential file for authentication; email=<service-account-id>@<project-id>.iam.gserviceaccount.com
2017/03/11 19:49:58 Listening on 0.0.0.0:3306 for <project-id>:<region-id>:<sql-instance-id>
2017/03/11 19:49:58 Ready for new connections
2017/03/11 19:50:04 New connection for "<project-id>:<region-id>:<sql-instance-id>"
2017/03/11 19:50:04 couldn't connect to "<project-id>:<region-id>:<sql-instance-id>": ensure that the account has access to "<project-id>:<region-id>:<sql-instance-id>" (and make sure there's no typo in that name). Error during createEphemeral for <project-id>:<region-id>:<sql-instance-id>: googleapi: Error 403: The client is not authorized to make this request., notAuthorized
@DanCarTroy
Copy link

@DanCarTroy DanCarTroy commented Mar 27, 2017

Really useful chat! It helped me a lot with an issue I was having.

@pilwon
Copy link

@pilwon pilwon commented Mar 29, 2017

@Laixer Can we reopen this issue until the it is fully resolved?

@samuelsmithhk
Copy link

@samuelsmithhk samuelsmithhk commented Apr 12, 2017

Just putting my voice out there, I was following this: https://cloud.google.com/sql/docs/mysql/connect-external-app#proxy

And was receiving the same error on the recommended role, only worked when I changed to Project Owner role.

@chees
Copy link

@chees chees commented Apr 21, 2017

I just ran into this as well. On our dev GCP project it works fine. On our production GCP project I have 2 service accounts with both the same roles and everything. One of them works, the other does not (it gives the same errors as in this ticket).

So either it has to do with the name or there's some weird race condition going on somewhere?

Anyway, I think this issue should be reopened.

@vadimberezniker
Copy link

@vadimberezniker vadimberezniker commented Apr 21, 2017

We have been unable to reproduce this problem.

For anyone that is running into this problem, can you send an email to cloud-sql@google.com referencing this issue.

If possible, please include (1) examples where you are running into the problem (2) examples where you are not running into the problem (3) instance names (4) service account names (5) assigned IAM roles (6) proxy logs (7) any other information that you think could help track down this problem

@Carrotman42
Copy link
Contributor

@Carrotman42 Carrotman42 commented Apr 21, 2017

Re-opening the issue as we try to track down the root cause. Please follow the advice in @Laixer's comment above if you run into the issue.

@Carrotman42 Carrotman42 reopened this Apr 21, 2017
@chees
Copy link

@chees chees commented Apr 24, 2017

FYI: I sent all details to cloud-sql@google.com

@vadimberezniker
Copy link

@vadimberezniker vadimberezniker commented Apr 26, 2017

@chees Can you try removing the Cloud SQL Client role from the service account that is not working, click Save, then add it back and click Save and let us know if that works.

@chees
Copy link

@chees chees commented Apr 26, 2017

Thanks to the cloud-sql@google.com people it's now working for me. The steps that fix it are:

1.- Go to the Cloud Project IAM page (Left Menu > IAM & Admin > IAM)
2.- Look for the row with the robot account that is having the issue.
3.- It should say "Cloud SQL Client" on the role column.
4.- Click on it to open the dropdown
5.- Click on the 'Cloud SQL Client' role that is checked to uncheck it.
6.- Click the blue save button above.
7.- Verify that the service account is removed from the IAM page.
8.- Click the Add button above in the same page.
9.- Enter the service account email address and select the Cloud SQL Client role
10.- Click Add
11.- The service account should appear again in the the list.

With this we are removing and then adding again the permissions for the service account.

It seems that you may have encountered a bug in our backend that is triggered when there is an old service account with the same name. We are investigating the issue and will work on a fix.

Maybe this can help somebody who still runs into this before the root cause gets fixed.

@MartinVandersteen
Copy link

@MartinVandersteen MartinVandersteen commented Apr 27, 2017

I can confirm that the steps @chees just mentioned worked for me!! THANK GOD, a day lost but I'm GLAD it finally works. THANKS! ( plz fix so less people will throw themselves out of the window )

@bluemalkin
Copy link

@bluemalkin bluemalkin commented May 10, 2017

Confirming @chees steps fixed the issue too, glad I found this post as I was stuck on this for a while. This really needs to be fixed asap !

@vochicong
Copy link

@vochicong vochicong commented Jun 14, 2017

Hi! after removing a service in App Engine (GAE), my GAE app can't connect to the Cloud SQL instance in the same project anymore.

When connecting using Cloud Shell and Cloud SQL proxy, I see the same error as you have mentioned above.

$ cloud_sql_proxy -instances=$INSTANCE_CONNECTION_NAME=tcp:3306
2017/06/14 11:41:34 Listening on 127.0.0.1:3306 for "PROJECTID:REGION:CLOUDSQLNAME"
2017/06/14 11:41:34 Ready for new connections
2017/06/14 11:41:49 New connection for "\"PROJECTID:REGION:CLOUDSQLNAME\""
2017/06/14 11:41:49 couldn't connect to "\"PROJECTID:REGION:CLOUDSQLNAME\"": ensure that the account has access to "\"PROJECTID:REGION:CLOUDSQLNAME\"" (and make sure there's no typo in that name). Error during createEphemeral for "PROJECTID:REGION:CLOUDSQLNAME": go
ogleapi: Error 403: The client is not authorized to make this request., notAuthorized

The following did not help:

  • Deleting and/or re-creating the service
  • Re-creating the Cloud SQL instance
  • Trying a Cloud SQL Client role (for Cloud SQL proxy tool) as in @chees method

Note that conneting using gcloud beta sql connect CLOUDSQLNAME works well.

Please help me to connect through Cloud SQL Proxy and App Engine!
Thank you!

Added Cloud SQL error log :

{
 textPayload:  "2017-06-14T01:45:12.279122Z 7847 [Note] Aborted connection 7847 to db: 'CLOUDSQLNAME' user: 'DBUSERNAME' host: 'cloudsqlproxy~35.187.213.24' (Got an error reading communication packets)"   
 insertId:  "XXXXXXXXXXXXXXXXXXXXXXXXX"   
 resource: {
  type:  "cloudsql_database"    
  labels: {
   project_id:  "PROJECTID"     
   region:  "asia-northeast1"     
   database_id:  "PROJECTID:CLOUDSQLNAME"     
  }
 }
 timestamp:  "2017-06-14T01:45:12.279495Z"   
 severity:  "ERROR"   
 logName:  "projects/PROJECTID/logs/cloudsql.googleapis.com%2Fmysql.err"   
 receiveTimestamp:  "2017-06-14T01:45:18.614391452Z"   
}
@Carrotman42
Copy link
Contributor

@Carrotman42 Carrotman42 commented Jun 14, 2017

@Carrotman42
Copy link
Contributor

@Carrotman42 Carrotman42 commented Jun 14, 2017

Sorry all that this has yet to be fixed. I have again pinged the team which is culpable for this issue and will escalate as needed.

@pavel-kurnosov
Copy link

@pavel-kurnosov pavel-kurnosov commented Jun 15, 2017

I setup new env few days ago and I still have same problem with access rights, none of user rights from Cloud SQL Client role not working. Only editor gave proper access to allow create connection to DB. I don't know is it make any difference, but we use Postgres as DB.

@MansoorMajeed
Copy link

@MansoorMajeed MansoorMajeed commented Jun 16, 2017

I was playing around with kubernetes and cloud sql proxy and faced the same issue. The proxy works fine on one google account (with Cloud SQL client role itself), but it doesn't work on another one ( In the account where it doesn't work, I'm not the owner. I have the service account admin privilege and project editor privilege though).
I was trying to follow instructions mentioned by @chees but, the service account is not listed under IAM, it is listed only under service accounts.

@chees
Copy link

@chees chees commented Jun 16, 2017

@MansoorMajeed
Copy link

@MansoorMajeed MansoorMajeed commented Jun 19, 2017

@chees Thank you very much. The issue is resolved. Adding the service account under IAM roles fixed it. But that is kind of confusing, because when I created the service account, I assigned the role of "Cloud SQL Client".

@jfx41
Copy link

@jfx41 jfx41 commented Jul 12, 2017

So I am having the same issue, but removing the role under IAM and then adding Cloud SQL Client back to it didn't fix anything.

I went under IAM > Roles and created a new role that contained cloudsql.* and then assigned that to the service account and it worked. I wish I knew exactly which options I needed to choose to make it a "Client" role instead of adding everything.

Due to my lack of sleep due this issue, did I miss something? Or, should have removing the Cloud SQL Client from the service account and then adding it back fix it?

@jfx41
Copy link

@jfx41 jfx41 commented Jul 12, 2017

Ok, so I just removed my custom Role and added Cloud SQL Viewer in addition to Cloud SQL Client and now it works.

I've stopped cloud_sql_proxy several times and restarted and it starts no problem. Hopefully, this might help someone else out.

@vadimberezniker
Copy link

@vadimberezniker vadimberezniker commented Jul 12, 2017

If you want to create a custom role, you need cloudsql.instances.connect and cloudsql.instances.get permissions. Those are the two permissions that Cloud SQL client has.

@jfx41
Copy link

@jfx41 jfx41 commented Jul 12, 2017

@Laixer Thank you very much. Is there something else that Cloud SQL Viewer has that would make it work? It seems like there is something else in addition to Client (at least in my case).

I am just glad adding Viewer fixed the problem and hopefully that helps someone else out. I wish I had the time to fiddle with the custom roles and figure out what Viewer added that made it work. Sadly, I don't think I'll be able to do it within my deadline.

Thanks again.

@vadimberezniker
Copy link

@vadimberezniker vadimberezniker commented Jul 12, 2017

Client role should be sufficient. Did you save between removing & re-adding the Client role?
The issue reported by others in this thread occurs when the name of a deleted service account is reused.
Are you using a service account and if so, do you recall if you have previously delete one with the same name?

@curioustortoise
Copy link

@curioustortoise curioustortoise commented Oct 23, 2017

I am also observing the client fails on CreateEphemeral. I'm new to this platform; just trying to set up the bookshelf tutorial described in cloud.google.com/sdk/docs.

I want to set up a Service Account that the SQL proxy may use to connect to the GC SQL Instance. However the Role 'Cloud SQL Client' is not on the drop down list of possible roles.
I appreciate some of this is beta code, so has this process changed? Please can someone point me to a description of the correct way to set up the SQL client

https://cloud.google.com/sql/docs/mysql/connect-admin-proxy#service-account
is the documentation I'm trying to follow

@vadimberezniker
Copy link

@vadimberezniker vadimberezniker commented Oct 23, 2017

If you can't see the role in the list, can you double check that the 'SQL' service is enabled in your project by visiting the following URL?
https://console.cloud.google.com/apis/api/sql-component.googleapis.com/overview
It should be enabled by default.

@warent
Copy link

@warent warent commented Feb 16, 2018

@chees That "11 step process" you posted on Apr 26, 2017 fixed it for me. Thank you so much. I lost like 4 hours on this, taking apart my fucking cluster and putting it back together again over and over.

@AgustinLado
Copy link

@AgustinLado AgustinLado commented Feb 21, 2018

I've had issues connecting the proxy to CloudSQL. The Client permission turned out to not be enough, as was the Viewer permission. I ended up using Admin, Client, Editor and Viewer and it finally worked.
I didn't follow (and didn't see until now) the 11 step process outlined above, just deleted and re-added service accounts like a madman. Next time if I have an issue I'll try it and report back.

@frankyaorenjie
Copy link

@frankyaorenjie frankyaorenjie commented Mar 7, 2018

@chees god damn it you save me man

@hfwang hfwang closed this Mar 30, 2018
@schweikert
Copy link

@schweikert schweikert commented Apr 10, 2018

@hfwang : Does the closing of this issue mean that it should be fixed? We are still having this problem, and I could solve it by following the steps that @chees wrote above.

@hfwang
Copy link
Contributor

@hfwang hfwang commented Apr 13, 2018

schweikert, I don't think there is a way for us to fix the issue on the Cloud SQL side... but I'm closing this issue since I'd like to make sure the open issues are cloudsql proxy issues, ideally issues that can be solved with a PR for the proxy.

@schweikert
Copy link

@schweikert schweikert commented Apr 13, 2018

Ok, I understand. github issues here are about the sql proxy code, and not about operational issues in GCP.

I have reported this now here: https://issuetracker.google.com/issues/77939928

@jerlam06
Copy link

@jerlam06 jerlam06 commented Mar 12, 2019

Tow years later, and @chees solution is still relevant ! Thanks a lot!

@terev
Copy link
Contributor

@terev terev commented Oct 30, 2019

That Cloud SQL Client role doesn't even have the permission in question. Is it no longer required when using this role?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
You can’t perform that action at this time.