devops

Restrict Google CloudSQL to a Kubernetes cluster

If you’re on the Google cloud and are using a SQL database it’s not unlikely that you’re using a Google CloudSQL instance or two. When creating a Cloud SQL instance you want to lock down the access so that only authorized networks can to connect to your instance. This is typically done by configuring the allowed hosts of the database instance. But if you want to lock down the database to only be accessible from a Kubernetes cluster running on Google Container Engine you need to manually maintain the allowed hosts list. The reason for is that your Kubernetes cluster doesn’t have a fixed IP range and cluster instances may come and go at any time. Also your cluster may be expanded with new machines that also needs to access the database. This places a huge burden for the operations team to keep track of and maintaining this.

The Workaround

Luckily it turns out that Jordi Collell has created a nice little app called cloudsqlip. This app can be deployed in a pod inside a Kubernetes cluster to monitor the nodes and maintain the Google CloudSQL allowed hosts list based on the current state of the cluster. Jordi was nice enough to publish the the app to DockerHub for everyone to use. So how do you use it? It’s really quite easy, here are the step-by-step instructions:

  1. Make sure that the “Cloud SQL Enabled” permission is set for your Google Container Engine cluster (you can check this by going to `Google Developers Console` > `Container Engine` > `Container clusters` > and check “Permissions”)
  2. Enable API Access control: Goto `Google Developers Console` > `API Manager` and search for “Google Cloud SQL API”. Click on `Google Cloud SQL API` and enable it (there’s no need to add credentials)
  3. Fork the github repository and customize the rc.yml file. Here’s an example:
    apiVersion: v1
    kind: ReplicationController
    metadata:
      labels:
        name: cloudsqlip
        version: "0.3"
      name: cloudsqlip
    spec:
      replicas: 1
      selector: 
        name: cloudsqlip
        version: v1
      template:
        metadata:
          labels:
            name: cloudsqlip
            version: v1
        spec:
          containers:
          - name: cloudsqlip
            image: jordic/cloudsqlip:0.3
            resources:
              limits:
                cpu: 10m
                memory: 50Mi
            command: 
            - /main
            - -db
            - cloudsqldbserver
            - -extra
            - 188.166.20.115/32
    

    Replace `cloudsqldbserver` with the name of your Google Cloud SQL instance name (for example if the instance id is `my-project:my-server` then the name should be `my-server`).
    Also note the parameter called `extra`. This is optional and it’s a way to tell `cloudsqlip` that it should not only allow the Kubernetes cluster to access the database but also some other network. For example you might want to put the subnet of your workplace here if you want to access the database from work. If you don’t need this just leave the last two lines out.

  4. Now all we need to do is to deploy the replication controller to Kubernetes:
    $ kubectl create -f rc.yml
    

    You can see that it’s up and running correctly by listing all pods (`kubectl get pods`):

    NAME                               READY     STATUS    RESTARTS   AGE
    cloudsqlip-ysps9                   1/1       Running   0          2s
    ....
    

    Watching the logs (`kubectl logs cloudsqlip-ysps9`) should give you something like this:

    A 2015-12-03 20:55:05.000 Updated [https://www.googleapis.com/sql/v1beta3/projects/my-project/instances/my-cluster].
    A 2015-12-03 20:55:05.000 Patching Cloud SQL instance... Patching Cloud SQL instance.../ Patching Cloud SQL instance...- Patching Cloud SQL instance...done.
    A 2015-12-03 20:55:05.000 {"project": "my-project", "instance": "my-cluster", "settings": {"ipConfiguration": {"authorizedNetworks": ["xxx.xxx.xx.xx/32", "yyy.yyy.yy.yyy/32", "yyy.yyy.yy.yyy/32"]}}}
    A 2015-12-03 20:55:05.000 2015/12/03 19:55:05 Gcloud stderr The following message will be used for the patch API method.
    A 2015-12-03 20:55:05.000 2015/12/03 19:55:05 Gcloud stdout
    A 2015-12-03 20:55:00.000 2015/12/03 19:55:00 Patching sql network: xxx.xxx.xx.xx/32,yyy.yyy.yy.yyy/32,yyy.yyy.yy.yyy/32,
    A 2015-12-03 20:55:00.000 2015/12/03 19:55:00 Node list changed
    A 2015-12-03 20:55:00.000 2015/12/03 19:55:00 Polling node list
    

And that’s it! The `cloudsqlip` pod will now poll the list of cluster nodes every 10 seconds and maintain the allowed hosts for you!

Note that you only need to deploy one instance of `cloudsqlip` per database in your cluster. In the future it might be possible to specify multiple databases (`-db`) when starting up the app.

Conclusion

This approach should be seen as a workaround until Google has a better solution to the problem, once they’ve fixed it there should be no need to run `cloudsqlip` in your cluster. But until then `cloudsqlip` is a nice little app that makes your life a bit easier when it comes to running Kubernetes on Google Container Engine with Cloud SQL.

5 thoughts on “Restrict Google CloudSQL to a Kubernetes cluster

  1. I do not even know how I ended up here, but I thought this
    post was great. I do not know who you are but certainly you are going
    to a famous blogger if you are not already 😉 Cheers!

    1. Thanks for your comment. I’ll look into this. But does it work for version 1 of cloudsql as well?

Leave a Reply

Your email address will not be published. Required fields are marked *