Cloudsql-proxy: Container Engine Permission Errors

[tutman96]

I have been having issues with the proxy on Container Engine. I have a Cloud SQL database setup using SSL and allowed ip of 0.0.0.0/0. I want to be able to connect to it with my containers via a service in Kubernetes. I don't want to put a cloudsql-proxy in every one of my pods. Therefore, I believe I need to use the proxy with a TCP port instead of the UNIX socket. I have successfully created the service, secret, and replication controller, however the proxy is giving me the error:

the default Compute Engine service account is not configured with sufficient permissions to access the Cloud SQL API from this VM. Please create a new VM with Cloud SQL access (scope) enabled under "Identity and API access". Alternatively, create a new "service account key" and specify it using the -credentials_file parameter

I have given it a credentials_file (the secret) and the service account has the Editor role assigned to it. I can run the proxy on my machine (OSX) with the credential file and it works perfectly. Below are my Kubernetes definitions:

Secret

apiVersion: v1
kind: Secret
metadata:
  name: sqlcreds
type: Opaque
data:
  file.json: "<base64encoded service key json file>"

Service

apiVersion: v1
kind: Service
metadata:
  name: mysql
  labels:
    app: mysql
spec:
  ports:
  - port: 3306
  selector:
    app: mysql
    role: proxy

Replication Controller

apiVersion: v1
kind: ReplicationController
metadata:
  name: mysql-proxy
  labels:
    app: mysql
    role: proxy
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: mysql
        role: proxy
    spec:
      volumes:
      - name: secret-volume
        secret:
          secretName: sqlcreds
      - name: ssl-certs
        hostPath:
          path: /etc/ssl/certs
      containers:
      - name: proxy
        image: b.gcr.io/cloudsql-docker/gce-proxy
        command: ["/cloud_sql_proxy", "-dir=/cloudsql", "-credential_file=/secret/file.json", "-instances=<redacted>=tcp:3306"]
        ports:
        - containerPort: 3306
        volumeMounts:
        - name: secret-volume
          mountPath: /secret/
        - name: ssl-certs
          mountPath: /etc/ssl/certs

I don't understand where I am going wrong here. I don't quite understand the '/etc/ssl/certs' directory, but I included it anyways. It seems to match your README however it is still getting the errors. I have read through the other issues here and there seems to be a couple of people reporting similar issues. Maybe the docker image hasn't been updated with the latest code? It says it was modified on Apr 18th if that helps.

[Carrotman42]

I believe this is https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/26. I haven't rolled out a new build yet, I will do so tomorrow.

As a workaround until then, you can use https://github.com/GoogleCloudPlatform/cloudsql-proxy/blob/master/cmd/cloud_sql_proxy/build.sh to build your own docker image and push it to your own repository.

Quick note: if you expect to just use the proxy, you shouldn't whitelist any IPs, and you don't need to enable SSL certs. The proxy is intentionally built to avoid having to do either of those.

Quick question though: why don't you want to run the Cloud SQL Proxy on each node? It will be less efficient to run the Proxy on a single node and route all data through it, but I suppose if you're expecting low traffic it's not necessarily a bad idea (as long as you set up your network so that the proxy doesn't accidentally open your database up to the internet). Is the footprint of the binary too large? Any thing I can do from an implementation point of view I can do change your mind?

[tutman96]

Thanks for the reply. I had seen that issue, I just figured it had been built. No problem though.
As for the architecture, we plan on going with the acclaimed "microservice" architecture. Each one of our microservices will be ran about 10 times and we will have 10-20 microservices. I would rather run this binary 200+ times. However, with the ReplicationController/Service route, I can dynamically scale the Proxy and Kubernetes will do its magic and do load balancing between them. I love the super small docker image that you provide and love how well it integrates with gcloud.

Question for you: how does the proxy handle the failover replicas? Will it just be seamless or what. I had initially planned on not using the Proxy but when I was experimenting, I found that the failover replica in Cloud SQL would have a different CA than the master. I didn't want to have to push multiple certs around and handle the failover myself. There isn't much documentation on doing failover with Cloud SQL and nothing I could find said that it would even have the same IP address.

[Carrotman42]

Okay, I just pushed version 1.05; this time, though, instead of updating latest I pushed it to a new tag (full path is b.gcr.io/cloudsql-docker/gce-proxy:1.05). After about a week or so I'll update the latest tag to point to the newest version. If you point your jobs to the new version they should start working; please let me know if something else isn't working and I'll be happy to debug it with you.

I'm very interested in how sharing the Cloud SQL Proxy among those microservices works out. Please let me know if you run into strange issues, or if you have feature requests which would make it easier to implement.

The proxy should correctly handle a failover operation: once the old master goes down, connections to the master will stop flowing through. After some amount of time (depending on several moving parts) the proxy will get a disconnect signal from the database instance and that failure will be propagated back to the program actually connected to the database (for example, the mysql command line program says "ERROR 2013 (HY000): Lost connection to MySQL server during query" in this scenario). The next time the app tries to use the Proxy to reconnect to the database (e.g. retrying the failed queries), the newly promoted instance will be used.

[tutman96]

Thanks @Carrotman42 . I have deployed the version that you pushed and it seems to work very well. I can scale the replication controller and everything works as expected. However, I can't seem to figure out the failover feature. I found this article: https://cloud.google.com/sql/docs/high-availability#test and tried to run the test, however I keep getting a 404.

So instead, I ran queries against the database and I simply just restarted the master instance. I kept getting connection timeouts and it doesn't look like the proxy failed anything over. I wasn't really expecting this as I just restarted the sql instance.

If this failover feature was supported in this proxy, it would work 100% the way I need it to. It might work, I just can't figure out how to test it.

[Carrotman42]

Regarding using the HA link you gave [0]: that is the way to test a failover action. A 404 can happen if you did not use a valid OAuth token (accessToken) or if the project or instance was incorrect. But, what I think is happening is that there's a documentation bug: that page should say that this is the format for the header; notice that there's a "Bearer" between "Authorization" and "accessToken"

curl --header 'Authorization: Bearer accessToken' \
--data '{"failoverContext":{"settingsVersion":"master-instance-settings-version"}}' \
-X POST \
https://www.googleapis.com/sql/v1beta4/projects/your-project-id/instance/master-instance-name/failover

Note that restarting the master instance will _not_ cause a failover to occur. As for the errors you saw: do you have the exact message that the proxy printed out? Timeout/connection closed errors should be expected (the proxy used to have a connection to the database, then the database exited and all the connections were closed/lost somehow). Eventually (after your client code attempts to reconnect to the proxy) the Proxy will attempt to reconnect to the database. Assuming the database started back up quickly after the restart there shouldn't be too much downtime.

What do you expect to happen during a failover? I can try to confirm/deny your expectations in regards to using the proxy.

[0] https://cloud.google.com/sql/docs/high-availability#test

[tutman96]

I can't appear to get a valid token then. I tried the "gcloud auth print-access-token" and I even tried creating an API Key with Google Cloud Console, however everything returns a 404. I won't hold you up any more on this and will close this issue.

As for the proxy, when I restarted the SQL server, the proxy was returning Instance <instance> closed connection. Seems like it is to be expected.

Thanks for all of your work again!

[Carrotman42]

Did you correctly swap in 'your-project-id' and 'master-instance-name' into that URL I posted? Did you also try putting the "Bearer" in the header? (I'm working on fixing the docs right now)

[tutman96]

Yes I did. As it turns out, I had an invalid token. I got that working, verified it with a simple GET and all was golden. I executed the failover action, and it returned me an error saying that the master-instance-settings-version was invalid. I did a GET on the instance, found the value, swapped it in there and then got a successful operation response. I checked on the operation and it said it had failed:

{
  "name": "b28f7edb-b90c-45e7-99cc-aa91333011ef",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/<project>/operations/b28f7edb-b90c-45e7-99cc-aa91333011ef",
  "operationType": "FAILOVER",
  "error": {
    "kind": "sql#operationErrors",
    "errors": [
      {
        "kind": "sql#operationError",
        "code": "UNKNOWN"
      }
    ]
  },
  "insertTime": "2016-05-03T21:34:53.971Z",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/<project>/instances/<instance>",
  "endTime": "2016-05-03T21:34:56.005Z",
  "targetProject": "<project>",
  "kind": "sql#operation",
  "startTime": "2016-05-03T21:34:55.607Z",
  "status": "DONE",
  "targetId": "<instance>"
}

Now it won't let me run the failover command again as:

{
  "error": {
    "message": "The instance or operation is not in an appropriate state to handle the request.",
    "errors": [
      {
        "reason": "invalidState",
        "message": "The instance or operation is not in an appropriate state to handle the request.",
        "domain": "global"
      }
    ],
    "code": 409
  }
}

So yeah, don't know how to fix this one... I can open a support ticket if that is what is needed.

FYI: I notice now that every value that is supposed to be replaced in the docs is italic. That is really hard to see. Maybe you could make it stand out a little more (like with <> around it, or even bold). Some text to explain what "master-instance-settings-version" would be good as well. I thought that the JSON blob had to be verbatim to what the example shows.

[tutman96]

I figured it out. Looks like I had a bad replica created for my instance. It works now. I am not sure if the proxy did the failover correctly. Here is the log for it:

---- FAILOVER START ----
2016/05/03 22:05:38 Writing data to local connection on 10.32.0.3:3306 had error: write tcp 10.32.0.3:3306->10.32.0.9:42563: write: broken pipe
---- REPEATED ----
2016/05/03 22:06:12 Client closed local connection on 10.32.0.3:3306
2016/05/03 22:06:12 Writing data to local connection on 10.32.0.3:3306 had error: write tcp 10.32.0.3:3306->10.32.0.9:42777: write: broken pipe
2016/05/03 22:06:12 Writing data to local connection on 10.32.0.3:3306 had error: write tcp 10.32.0.3:3306->10.32.0.9:42779: write: broken pipe
2016/05/03 22:06:14 Client closed local connection on 10.32.0.3:3306
2016/05/03 22:06:37 couldn't connect to "<instance>": dial tcp 104.196.3.245:3307: getsockopt: connection timed out
2016/05/03 22:06:37 Client closed local connection on 10.32.0.3:3306
2016/05/03 22:06:38 Thottling refreshCfg(<instance>): it was only called 947.392245ms ago
2016/05/03 22:06:38 Client closed local connection on 10.32.0.3:3306
2016/05/03 22:06:38 Thottling refreshCfg(<instance>): it was only called 1.459423294s ago
2016/05/03 22:06:38 Writing data to local connection on 10.32.0.3:3306 had error: write tcp 10.32.0.3:3306->10.32.0.9:42543: write: broken pipe
2016/05/03 22:06:40 Thottling refreshCfg(<instance>): it was only called 2.739391887s ago
2016/05/03 22:06:40 Writing data to local connection on 10.32.0.3:3306 had error: write tcp 10.32.0.3:3306->10.32.0.9:42547: write: broken pipe
2016/05/03 22:06:40 Thottling refreshCfg(<instance>): it was only called 3.507420223s ago
2016/05/03 22:06:40 Writing data to local connection on 10.32.0.3:3306 had error: write tcp 10.32.0.3:3306->10.32.0.9:42555: write: broken pipe
2016/05/03 22:06:45 Thottling refreshCfg(<instance>): it was only called 8.499426368s ago
2016/05/03 22:06:45 Client closed local connection on 10.32.0.3:3306
2016/05/03 22:06:48 Thottling refreshCfg(<instance>): it was only called 11.444622353s ago
2016/05/03 22:06:48 Client closed local connection on 10.32.0.3:3306

And then everything stabilized. The failover operation completed and all my queries were going through.

[Carrotman42]

FYI: I filed an internal bug about your FYI. I agree that the italics are a little confusing.

Good to hear you figured things out. I'll keep this bug open to remind myself to release specific URLs of the static binaries.

Please don't hesitate to file other issues if/when you find them :)

[spheromak]

This worked for me using the 1.05 container. Note tho that the current :latest and this :1.05 change the arguments slightly from -credentials_file to -credential_file. Altho I think the docs and master are in line with the 1.05 container, the current as of today latest container was wanting -credentials_file

can we expect the 1.05 container to be released as latest soon ?
Also where might I get a list of all the tagged versions currently available?

Thanks for this awesome proxy btw!

[psamaan]

So I've been trying to launch the proxy image as its own Kubernetes deployment and interface with it via a service endpoint rather than having it run as a sidecar container (same thing @tutman96 was attempting), but it seems the proxy is listening on 127.0.0.1 which makes me unable to connect to it from outside of the pod and via the service. Is there a way to have it listen to 0.0.0.0?

[tutman96]

Yup, you just do a "-instances=<redacted>=tcp:0.0.0.0:3306" iirc.

[psamaan]

Yes that works, thank you!

[Carrotman42]

@spheromak sorry I missed your message until now.

Regarding -credential_file: some docs have been wrong before (e.g. https://github.com/GoogleCloudPlatform/cloudsql-proxy/commit/2b26f59fb7cc8b29a2541af3a21f6a7a6e68a4a9), but the code has always spelled the flag that way (see initial introduction of the flag: https://github.com/GoogleCloudPlatform/cloudsql-proxy/commit/c72e1cfd09283345d7886b71e11ed0f87d1ed1a5). I've double checked, and the image at `b.gcr.io/cloudsql-docker/gce-proxy:latest' does actually have that as the flag name (check --help).

I'll probably update the 'latest' tag to 1.05 some time next week. If you are relying on the 'latest' tag, though, I would like to strongly suggest you switch to locking to a specific version instead. I would hate to break any of your software at some random time because I pushed out a release.

There's no canonical list, but it is a standard Docker container repository so (according to this gist: https://gist.github.com/rjeczalik/99ced542c0663f05f94c) you should be able to list the images via the API. Anyway, there are three tags right now: "1.04" (I just added it), "1.05", and "latest" (which points to version 1.04). From now on, each new tag will have a corresponding release on this github repo.

Glad you're enjoying the Proxy :) If you have any more comments, bugs, or feature requests, please don't hesitate to file an issue!

[kuroneko25]

This may not be directly related but I currently have the proxy set up to run as a sidecar container. Is this the recommended approach or is there a reason that I should consider making it into a Service/DaemonSet?

[Carrotman42]

Sorry for the delay in answering this question. I was recently cleaning up browser windows and found this half-typed reply...

The optimal way to use the Proxy is to have one Proxy binary running per physical/virtual host, and use OS-level features to connect the proxy to other processes (binding directories, agreement on TCP ports, --instances, etc). This is due to caching/quota concerns. But, the Proxy is intended to be flexible. You really should use it however you need to, assuming that you've taken into account the potential bugs/overheads of running it in a different setup.

Others have set it up to run a single instance of the Proxy with every other individual program that needs it, even if that means having N proxies running at the same time on the same machine. I don't suggest doing it this way if possible since each Proxy process will have have some overhead, and duplicating this overhead for no good reason should be avoided. If N is small, though, it likely wouldn't make too much of a difference.

Still others have gone the other direction, having one Proxy process running for an entire group of machines. This is a setup I personally would not use. In this mode, data sent between a machine which needs Cloud SQL access and the machine which is running the Proxy will be /unencrypted/ and therefore (depending on your platform) may be subject to MIIM or traffic sniffing. In addition, care needs to be taken to ensure that the Proxy machine does not have a firewall allowing connections from outside of a trusted subnet.

[kuroneko25]

My understanding is that one instance of the Proxy is tied to a particular CloudSQL instance i.e. via -instances=project-id:zone:instance-id=tcp:3306. This won't work if I only run one Proxy instance on a node and I have pods on that node that need to talk to different CloudSQL instances?

[Carrotman42]

--instances takes a comma-separated list

On Sep 23, 2016 10:31 PM, "kuroneko25" [email protected] wrote:

My understanding is that one instance of the Proxy is tied to a particular
CloudSQL instance i.e. via -instances=project-id:zone:instance-id=tcp:3306.
This won't work if I only run one Proxy instance on a node and I have pods
on that node that need to talk to different CloudSQL instances?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/35#issuecomment-249346566,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAiy70_9kE3hW4Wsz5ZaM-Y3xFkZ7syJks5qtLWngaJpZM4IULbv
.

[kuroneko25]

Ah my bad for not reading the doc carefully :). How does the proxy figure out which instance to route a request to? Does this mean we'll need to use a different port for each instance?

[Carrotman42]

Yes, you can only bind a TCP port to one thing at a time on the same system
(in general, maybe for some obscure systems you could). If you use Unix
sockets instead, the Unix socket will be given the name of the cloud sql
instance (and is placed in the directory specified by -dir).

[Carrotman42]

This thread contains quite a few different issues... I'm going to close this since I don't think there's anything actually remaining.

If I'm incorrect, please feel free to file a new issue for tracking.