Home / server / Questions / 778842
In Process
Dxx
Asked: 2016-05-25 16:14:14 +0800 CST

Google Cloud SQL authorize Auto Scale Instance Groups

  • 772

I have an instance group set up with Auto Scaling and Load Balancing. I'm also using Google Cloud SQL for the MySQL server.

Whenever instance group scales up and adds another instance, the instance gets a new IP address. The problem is that this instance no longer has access to the Google SQL instance (since the SQL instance requires authorized networks to be pre-configured). What can I do about this?

I'm currently accepting most IPs to my SQL server by adding the following IPs to the Authorized IPs in the SQL manager: 100.0.0.0/6 104.0.0.0/5 112.0.0.0/4

google-cloud-platform google google-compute-engine google-cloud-sql

3 Answers

  1. As already mentioned by @justbeez, I believe the best way to go is using second generation instances and Cloud SQL Proxy. In case this is not possible, first generation instances can be setup to only allow SSL connections and whitelisting any origin as explained here.

    A more complex approach would be to create an instance template so that a startup-script runs at boot time and authorizes the instance IP address via gcloud. The IP could be removed in the same way at shutdown time.

    The IP address of the GCE VM can be obtained from the metadata server

    curl "http://metadata.google.internal/computeMetadata/v1/instance/network-interfaces/0/access-configs/0/external-ip" -H "Metadata-Flavor: Google"
    • 6

  2. If you're using a Second Generation Cloud SQL instance, you may want to consider using the Cloud SQL Proxy connection method: https://cloud.google.com/sql/docs/mysql-connect-proxy

    In short, you install Cloud SQL proxy locally in your instances and it authenticates via a service account. You then connect to it similarly to how you would connect using a UNIX or TCP socket.

    This requires a little setup, but doesn't require any IP whitelisting. Once you have it configured, you would just add that version to whatever auto-scaling template you're using.

    Note: If you happen to be using Docker containers, they have a version of Cloud SQL Proxy wrapped in a Docker image that you can add to your pods as a service: https://cloud.google.com/sql/docs/mysql-connect-docker

    (The only other option I can think of is to use the API to edit the whitelist in response to the autoscaling events.)

    • 1

  3. Ok, I don't post much, but this problem cost me a lost of time and effort. I hope this info helps folks who face the same problem:

    Using the Cloud SQL Proxy seems to be the way GCP is pushing everyone to overcome this hurdle. However, keep in mind that once you go down that path, you'll be coupled to GCP. Your application will have to have direct knowledge of a GCP-specific component to be able to communicate with the DB -- something that is only necessary for the application to run in GCP, and nowhere else.

    Due to this undesirable consequence, I chose to follow @Carlos's "more complicated" approach. The advantage of this approach is that it isolates the logic necessary to communicate with the SQL instance, and associates it with GCP-specific configurations instead of the application itself.

    The following code is the startup script I wrote to allow an auto-starting instance template to gain authorization to a SQL instance. It runs inside GCP's Container-Optimized OS, which does not have gcloud installed.

    #!/bin/bash
PROJECT=<your project>
DB=<your db name>

METADATA=http://metadata.google.internal/computeMetadata/v1

# Get the external IP of this instance
EXTERNAL_IP=$(curl -s "$METADATA/instance/network-interfaces/0/access-configs/0/external-ip" -H "Metadata-Flavor: Google")

# Get access to call the SQL API
curl -s --header "Authorization: Bearer $ACCESS_TOKEN" -X GET https://www.googleapis.com/sql/v1beta4/projects/$PROJECT/instances/$DB?fields=settings/ipConfiguration/authorizedNetworks/value | grep value | awk -F\" '{print $4}' && \
SVC_ACCT=$METADATA/instance/service-accounts/default && \
ACCESS_TOKEN=$(curl -H 'Metadata-Flavor: Google' $SVC_ACCT/token | cut -d'"' -f 4)

# Get the IPs that are authorized to the database
EXISTING_IPS=$(curl -s --header "Authorization: Bearer $ACCESS_TOKEN" -X GET https://www.googleapis.com/sql/v1beta4/projects/$PROJECT/instances/$DB?fields=settings/ipConfiguration/authorizedNetworks/value | grep -B1 -A1 value | tr -d '\n')

# If the $EXTERNAL_IP is already authorized, then there's nothing to do
[ -n "$(echo $EXISTING_IPS | grep $EXTERNAL_IP)" ] && exit 0

# If the $EXISTING_IPS is not empty, prepend a comma
[ -n "$EXISTING_IPS" ] && EXISTING_IPS=", $EXISTING_IPS"

# Patch the database settings with the new authorized IPs
curl -s --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data "{\"settings\":{\"ipConfiguration\":{\"authorizedNetworks\":[{\"value\":\"$EXTERNAL_IP\"}$EXISTING_IPS]}}}" \
     -X PATCH \
     https://www.googleapis.com/sql/v1beta4/projects/$PROJECT/instances/$DB

# Dump the database settings for visual verification
curl -s --header "Authorization: Bearer $ACCESS_TOKEN" -X GET https://www.googleapis.com/sql/v1beta4/projects/$PROJECT/instances/$DB?fields=settings

    Update: This solution is available on my public GitHub repo.

    • 0