Cloudsql-proxy: CloudSQL proxy stops working after removing a GAE service

[vochicong]

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.

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"   
}

I believe that internally GAE use Cloud SQL proxy for the connection.
So trying to connect from Cloud Shell using Cloud SQL proxy, I see the error similar to #37.

$ 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 at https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/37#issuecomment-297265064 .

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!

[Carrotman42]

Are you using App Engine Flex? App Engine Standard does /not/ use the Cloud SQL Proxy under the covers (App Engine Flex does indeed), so you'll have to elaborate your situation a little more if you are using App Engine Standard for me to help out.

Can you explain what you mean by "after removing a service in App Engine"? What is a "service" in App Engine? Do you mean a service account as described [here[(https://cloud.google.com/compute/docs/access/service-accounts)? And do you mean that you're removing the service account from your Google Cloud Platform project in the "IAM & Administration -> IAM" menu on the Cloud Console? Otherwise, please explain how you're removing the service account from App Engine. (and if you don't mean "service account", what do you mean?)

As for the Cloud SQL error log you are showing, I think it is a red herring. That message will only occur after a successful connection to the database has been made via the Cloud SQL Proxy; it does not occur for any database request which resulted in the Proxy printing out that 403 message (since in that case, no connection has been made to the database).

Let's try to get the Proxy working on Cloud Shell first; that will authenticate as your google account (the one you are logged into when you access the Cloud Console). Can you verify that:

1) Executing this command in the Cloud Shell shows your email address:

   curl -H Metadata-Flavor:Google metadata/computeMetadata/v1/instance/service-accounts/default/email; echo

2) That email address shows up under the as having at least the Cloud SQL Client permission on this webpage (swap in your PROJECTID in the URL):

  https://pantheon.corp.google.com/iam-admin/iam/project?project=PROJECTID

[vochicong]

Thanks, @Carrotman42 .

It was somehow urgent, so I had to create a new GCP project, set up GAE and CloudSQL from scratch. Things work like a charm in the new project.

Regarding your questions, I'm using GAE Flex, not GAE Standard.

"Service" refers to https://cloud.google.com/appengine/docs/flexible/ruby/an-overview-of-app-engine. I removed one of them using gcloud app services delete.

The command curl -H Metadata-Flavor:Google metadata/computeMetadata/v1/instance/service-accounts/default/email; echo shows my email address, but the link https://pantheon.corp.google.com/iam-admin/iam/project?project=PROJECTID doesn't work with my corporate email.

Thanks again!

[Carrotman42]

If the IAM link doesn't work for you, then you may not have the appropriate permissions to access the Cloud SQL database from your account (and transitively, the Cloud Shell will not have the appropriate permissions either). You'll have to ask someone in your corporation to check that you have appropriate access to Cloud SQL databases by making sure you have the "Cloud SQL Client" role.

Looks like you found a workaround by recreating the project. I don't know what was originally wrong but I'm happy to hear you got things working. Let me know if you'd like more help figuring out what was wrong previously. I suspect it's possible that your App Engine Flex app did not have the appropriate permissions to access the Cloud SQL database either, but you'll have to access that IAM link to be sure.

[vochicong]

If the IAM link doesn't work for you, then you may not have the appropriate permissions to access the Cloud SQL database
I have full permissions. The IAM link requires an @gmail.com email, while we use our company emails.

I wish Google supports would carefully investigate my case to see what happened in the old project. The AWS guys are very supportive in a case like this, they would go online and help me to investigate the case.

I like GCP very much, but feel as it is a DIY platform, not so friendly as AWS.
If GCP is more open I may do more the investigation by myself.

[Carrotman42]

Apologies if I'm not being as helpful as you'd like. I am happy to investigate what happened on the old project, but often customers are not willing to continue the back and forth required for root-cause investigation so I wanted to let you move on if you didn't care. It sounds like you care, so let's keep investigating.

(also, FYI: I'm not Google support, I'm a Cloud SQL engineer. But I play the support role for this github project since I've written most of the code for the Proxy)

Can you explain what you mean about having a "company email"? How are you using the "company email" to interact with AppEngine Flex, and are you using the Cloud Console or a CLI like gcloud? You should be able to access the Cloud Console page for that IAM link I posted with the same authentication as you're using to interact with GCP to delete/create new Flex services, Cloud SQL databases, etc.

[Carrotman42]

Monthly ping. If you'd like to help figure out what happened here I'm also still happy to help out. Otherwise I'll close out the issue (but you're welcome to open up another issue any time!)

[vochicong]

Thank you. I'm sorry currently I don't have enough time for this. Can we
close this issue for now?
On Sat, Jul 29, 29 Heisei at 2:53 Kevin Malachowski <
[email protected]> wrote:

Monthly ping. If you'd like to help figure out what happened here I'm also
still happy to help out. Otherwise I'll close out the issue (but you're
welcome to open up another issue any time!)

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/98#issuecomment-318747039,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAHg5w_qBD4sLFiAQNdP6jx1LL3GiKmWks5sSjw2gaJpZM4N5VqX
.