Connecting to remote MySql from Google AppEngine

You've built your app and it does everything you dreamed of and more. It connects to a local MySql database and does all the good things, but now it's time to publish it for the masses. You carefully evaluate your options, review the value proposition for each hosting provider and the estimated monthly cost for running your app. You've decided to use an existing MySql database and deploy the application itself on Google's AppEngine service.

$ gcloud app deploy

You run the command from your terminal and it deploys perfectly... Oh, except the database won't connect. What the hell? It works perfectly from your local environment.

It's caught in the firewall!!

The server that's hosting your MySql database wouldn't last long without a firewall in place and that's exactly what's tripping up your application.

Assuming you have root access to the database server, you can add rules to the firewall to allow connections on port 3306, the default MySql port. That's all well and good, except the part where your new AppEngine service gets a new IP address every time you deploy it, or simply because it's a new day and you're busy running to fetch donuts or something equally important.

On Linux, you're probably running ConfigServer Security & Firewall or csf as your firewall. Rules are loaded from /etc/csf/csf.allow and can be single IP addresses or a range of addresses using CIDR addressing. Additionally you can specify tcp vs udp and the specific port you want to allow.

Hosting on a Static IP

If your application needed to connect to MySql port 3306 from a static IP address you could get away specifying just that one IP address, as follows:

# My Application hosted on IP: 123.45.67.89
tcp|in|d=3306|s=123.45.67.89
tcp|out|s=3306|d=123.45.67.89
# End of My Application

That's pretty simple but hosting on AppEngine doesn't get a static IP.

What addresses do AppEngine use?

Google provides a way to discover the range of IP addresses that could be used, but it ain't pretty. Using dig a linux DNS lookup tool, we can work it backwards from the published DNS name:

_cloud-netblocks.googleusercontent.com

The following command tells us what names we need to look at for IP ranges.

dig TXT _cloud-netblocks.googleusercontent.com @ns1.google.com +short 

The output is all munged together on one line and wrapped in double quotes.

• "v=spf1
• include:_cloud-netblocks1.googleusercontent.com
• include:_cloud-netblocks2.googleusercontent.com
• include:_cloud-netblocks3.googleusercontent.com
• include:_cloud-netblocks4.googleusercontent.com
• include:_cloud-netblocks5.googleusercontent.com
• ?all"

If we pick out each one of those domain names, we can get the IP subnets that each one represents.

 dig TXT _cloud-netblocks4.googleusercontent.com @ns1.google.com +short

• "v=spf1
• ip4:35.219.192.0/24
• ip4:35.220.0.0/14
• ip4:35.224.0.0/13
• ?all"

Using these ranges we can build rules to allow MySql Connections from any IP's within the possible IP address ranges for AppEngine services as follows.

tcp|in|d=3306|s=35.219.192.0/24
tcp|out|s=3306|d=35.219.192.0/24

That's all well and good but there's a lot of them (100+ !) and they won't stay the same forever, so we need a better way to update them.

If we can put them all into a single file, we can add them to the csf rules with an include statement like the ones at the top of our /etc/csf/csf.allow file.

Include /etc/csf/appengine.allow

Using the dig commands already covered and a bit of bash scripting, we can piece the whole thing together like so:

#!/bin/bash

netblocks=$(dig TXT _cloud-netblocks.googleusercontent.com @ns1.google.com +short)

now=$(date)
printf "########################################\n"
printf "# GCP AppEngine Rules\n"
printf "# %s\n" "$now"
printf "########################################\n"

for block in $netblocks; do
    if [[ $block == include:* ]]; then
        printf "# ${block#include:}\n"
        ipblocks=$(dig TXT ${block#include:} @ns1.google.com +short)
        # Inbound rules
        for ipblock in $ipblocks; do
            if [[ $ipblock == ip4:* ]]; then
                printf "tcp|in|d=3306|s=${ipblock:4}\n"
            fi
        done
        printf "\n"

        # Outbound rules
        for ipblock in $ipblocks; do
            if [[ $ipblock == ip4:* ]]; then
                printf "tcp|out|s=3306|d=${ipblock:4}\n"
            fi
        done
        printf "\n"
    fi
done

printf "########################################\n"
printf "# End of GCP AppEngine Rules\n"
printf "########################################\n"

I put the above script in:

/etc/csf/appengine.sh

Updated it with execute permissions:

chmod +x /etc/csf/appengine.sh

And added it to the bottom of my crontab:

crontab -e

Add the following line to schedule it to run every Monday morning at 4:10 am and restart csf. Any output should be discarded with > /dev/null 2>&1.

10 4 * * 1 /etc/csf/appengine.sh > /etc/csf/appengine.allow && csf --restart > /dev/null 2>&1

That should update my allow file every week & restart the firewall to use the new rules.

Now, when my AppEngine service get's a new IP address, or Google comes up with a new range of addresses - that may get assigned to my app, I don't need to worry about it. I can be assured that the new IP's will be allowed to connect to my database!

Thanks for reading & good luck with your AppEngine project!