DEV Community: Mark Voorberg

Connecting to remote MySql from Google AppEngine

Mark Voorberg — Sat, 17 Oct 2020 03:11:02 +0000

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!

Socket.io with Apache on a CPanel VPS

Mark Voorberg — Tue, 30 Jun 2020 23:45:58 +0000

I've been running Node.js apps on my WHM/CPanel VPS for a little over a year now and proxying web requests through Apache has been working pretty well.

However, with my latest project, I'm using Socket.io to send messages to the front-end in real-time. I had everything working locally, but after deploying both the API and my front-end, the websockets were unable to connect.

After a bit of reading, it became clear that I need to use ProxyPass which isn't allowed in a .htaccess file. On a typical deployment you'd add it to a configuration file under the sites-available folder, but with WHM/CPanel, that option just isn't there.

On a cPanel/WHM server, the Apache configuration file is an httpd.conf that gets re-generated each time changes are made, so it's really not recommended to make changes to it manually.

Here's the notice at the bottom of the file:

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#   !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
#   DO NOT EDIT. AUTOMATICALLY GENERATED.  USE INCLUDE FILES IF YOU NEED TO MAKE A CHANGE
#   !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

That doesn't mean we can't override to the VirtualHost settings, we just need to make our changes in an include file.

Have a look at your httpd.conf by printing it to the terminal with:

$ cat /etc/httpd/conf/httpd.conf

Note the following lines after each VirtualHost, where myuser and mydomain.net match your CPanel account.

# To customize this VirtualHost use an include file at the following location
# Include "/etc/apache2/conf.d/userdata/ssl/2_4/myuser/api.mydomain.net/*.conf"

Notice that the path above includes ".../ssl/...".
There's a corresponding ".../std/..." for services that
don't require a secure connection. I won't recommend
running any services without SSL, so I didn't even look
at that configuration.

The folder listed above won't likely exist yet, but you can create it with:

$ mkdir -p /etc/apache2/conf.d/userdata/ssl/2_4/myuser/api.mydomain.net

Then create a new config file with:

$ cd /etc/apache2/conf.d/userdata/ssl/2_4/myuser/api.mydomain.net
$ touch custom.conf
$ nano custom.conf

The contents of the custom configuration will vary based on your need, but in my case, I needed to connect to a socket host running on port 7222 on the api.mydomain.net subdomain. The same subdomain also hosts my Node.js REST api, so I needed to split out the websocket traffic and upgrade it as needed.

FYI: I wasted a lot of time editing my custom.conf file in the domain root ".../myuser/mydomain.net", where it should have been in the folder that corresponds to my subdomain ".../myuser/api.mydomain.net".
It was a huge waste of time as I fiddled with settings that were not applied to the subdomain where I was testing my application. If you're making edits that don't appear to have any effect, confirm that your config file is in the folder for the domain you're testing against.

In broad strokes, I need to proxy any websocket traffic to localhost:7222. The contents of my custom.conf file is as follows:

ProxyPreserveHost On
RewriteEngine On

# Upgrade connections to WebSockets
RewriteCond %{HTTP:Upgrade} =websocket [NC]
RewriteRule /(.*) ws://localhost:7222/$1 [P,L]

# Redirect http to https
RewriteCond %{HTTPS} off
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

# Everything else forwards as HTTP to the node app.
ProxyPass / http://127.0.0.1:7222/
ProxyPassReverse / http://127.0.0.1:7222/

Once I felt that the rules were correct I saved the file and ran:

$ /scripts/verify_vhost_includes
> Testing
> /etc/apache2/conf.d/userdata/ssl/2_4/myuser/api.mydomain.net/custom.conf...ok

This confirmed that my changes weren't broken so badly that they'd prevent Apache from trying to load them. If that were to happen, and I didn't test it first, I could potentially prevent other websites on my host from being served.

This is only one of two VirtualHosts with a custom config file on my machine so I was happy to make sure we use all the existing include files:

$ /scripts/ensure_vhost_includes --all-users
> Built /etc/apache2/conf/httpd.conf OK

Alternatively, I could have used the following CPanel script to ensure that my httpd.conf contained the necessary Include statements.

$ /usr/local/cpanel/scripts/rebuildhttpdconf
> Built /etc/apache2/conf/httpd.conf OK

The only remaining step was to bounce Apache & test out my new VirtualHost Rewrite & ProxyPass rules.

To restart Apache, run the following:

$ /scripts/restartsrv_httpd

This should do a soft restart, which simply means that it will restart after it finishes serving any active connections.

After making these changes, my Angular app socket service was able to successfully connect to a Node.js app running behind my Apache webserver.