How to whitelist IP addresses for Database access

When working with Two Minute Reports, you might be required to whitelist IP addresses so that your database can accept connections from us. Without connecting to your database we can’t pull the data required for creating your reports.

We have created easy to use scripts and commands for your to quickly do the whitelisting. These scripts are dependant on the database types that you want to add to your Two Minute Reports account.

Please note we don’t allow or execute any other query apart from “SELECT” queries from Two Minute Reports. These scripts below enable only “SELECT” access to your database and are therefore safe. Queries that modify your database’s data cannot be executed from Two MInute reports.

Whitelist IP address in MySQL

To whitelist IP addresses and enable access to Two Minute Reports you can use these readymade scripts and commands that we have created for you. Choose one of the two method based on your choice of request origin, Google or Amazon that you selected while adding a MySQL data source in Two Minute Reports.

Google IPs

Since google does not have a single IP address, one has to whitelist a series of IP addresses that Google can use at any point in time to connect to your database. These IP addresses were provided by google here.

The following python script can be used to add the Google IPs automatically. Make sure to run this python script from an environment that has access to your database.

from sqlalchemy import create_engine

# Connection string that is used to connect to your database from your local system.
# - Replace this with your own credentials
conString = 'mysql+pymysql://databaseuser:databasepassword@localhost:portnumber/'
mysql = create_engine(conString)

# New credentials
# - Replace this with your own credentials
newDbUsername = "googlereader"
newDbPassword = "googlepassword-98@*!)#!@$"
yourDatabaseName = "my_app"

# Google IP addresses
# - Don't change
ips = ['64.18.0.0 - 255.255.240.0',
       '64.233.160.0 - 255.255.224.0',
       '66.102.0.0 - 255.255.240.0',
       '66.249.80.0 - 255.255.240.0',
       '72.14.192.0 - 255.255.192.0',
       '74.125.0.0 - 255.255.0.0',
       '173.194.0.0 - 255.255.0.0',
       '207.126.144.0 - 255.255.240.0',
       '209.85.128.0 - 255.255.128.0',
       '216.239.32.0 - 255.255.224.0']

for ip in ips:
    ip2 = ip.replace(' - ', '/')
    try:
        sql = "CREATE USER '"+newDbUsername+"'@'" + ip2 + \
            "' identified by '" + newDbPassword + "';"
        mysql.execute(sql)
    except:
        print(ip2)
    sql = "GRANT SELECT ON " + yourDatabaseName + \
        ".* TO '"+newDbUsername+"'@'" + ip2 + "';"
    mysql.execute(sql)

Credits: modified from here.

You can save this script as “script.py” and run it from your terminal by calling these command below.

# install sqlalchemy
pip3 install SQLAlchemy

# execute the python script
python3 script.py

Amazon IP

If you chose Amazon as your request origin, you need to whitelist just one IP address. Two Minute Reports’ servers are hosted by Amazon Web Services. Our IP address is 54.242.82.213, you can easily whitelist it using the following commands.

Step 1

Login to mysql console using the following command in your terminal

mysql -u userwithgrantacccess -p 

Step 2

Run these following commands from inside the mysql console, after modifying them as per your needs.

CREATE USER 'newDbUsername'@'54.242.82.213' identified by 'newDbPassword';"

GRANT SELECT ON yourDatabaseName.* TO 'newDbUsername'@'54.242.82.213';"

Hope these instructions helped you. Please don’t hesitate to contact us incase you are facing any difficulties.