using bash to make inserting a large number of UFW rules faster and easier.

A blacklist is an old fashioned way to secure a server.  Many people will say yes to blocking ports, and yes to resources like fail2ban, but balk at something so primitive as a blacklist.  And yet they are used by major companies.

The other day, I tried logging into Hulu on my VPN.  I wasn’t trying anything suspicious.  I just left my VPN logged into an American IP address and Hulu said (paraphrase) “Sorry, no VPNs allowed.”  How did they do that.  From what I can tell, the Internet’s best guess is that they used a blacklist.  That’s powerful.

I am not going to delve into whether or not every blacklist fits every use case, but I am going to talk about some ways to ease the implementation of it.

Before I continue, let me say that I drew very heavily on the resources that we available to me, such as stackoverflow, serverfault, askubuntu, and github.  There was simply too much information to draw an accurate bibliography, so let me say, thank you IT community.  Much of the information I found was either dated or not relevant to my environment so let me make a few notes about that right now.

These steps involved Ubuntu Server 18.04.  They used the default installation of UFW, and the edits were made with the default installation of Vim.  I used it to deny about thirty thousand IPv4 addresses in iptables format.

So, let’s get down the nitty gritty.

First, there is a handy line of bash for inserting UFW rules via commandline, and I am going show that, but it didn’t work for me.

while read line; do sudo ufw insert 1 deny from $line; done < IP_addresses

This is good for a smaller file, but for thirty thousand, it was going to take days. So instead, I opted to edit /etc/ufw/user.rules. Keep in mind that you have separate IPv6 from IPv4. Only IPv4 goes in user.rules while IPv6 goes in its corresponding file. Keep also in mind that a mere typo can cause hours of recovery as you are editing config files here.

So that brings me to this genius bit of code that I found somewhere on the net and apologies for a vague citation but it’s a blur at this point.

for line in `cat my_input.txt`; do echo "-A ufw-before-input -s $line -j DROP" >> my_output.out ; done

The programmer who gave us this bit of bash was plugging it directly into commandline. That gave the output of this.

-A ufw-before-input -s a.b.c.d -j DROP
-A ufw-before-input -s e.f.g.h -j DROP

That was an excellent breakthrough for me, but unfortunately, the config file in user.rules has strict input rules, and every time I restarted the service, it deleted my changes. Someone on one of the Q&A websites mentioned that UFW requires rules to be in a particular format, like this

### tuple ### deny any any any a.b.c.d in
-A ufw-before-input -s a.b.c.d -j DROP

### tuple ### deny any any any e.f.g.h in
-A ufw-before-input -s e.f.g.h -j DROP

That’s a lot of typing if you don’t do bash!

So basing it on the code above, this is what I did.

for line in `cat my_input.txt`; do printf "### tuple ### deny any any any $line in\n-A ufw-before-input -s $line -j DROP\n\n" >> my_output.out ; done

I used printf instead of echo because as pointed out somewhere on the net, it plays nicer with “\n”.

So this line of bash formats a txt file very quickly, and then I just take the contents of the text, and insert it into user.rules with vim. But that is beyond the scope of this post. I can’t say this enough. Careful with this one folks. You can get locked out of your server with this. Have a contingency plan.

I wanted to hang on to this bit of code, so I made it into a simple bash app.

Open a new file with


Then copy the code above.

Then write your file, and make it executable.

chmod 700

Upload it to github and you have it forever.

using a floating IP for your VPN connection on digital ocean + OpenVPN

So first off, a major correction.  In my previous post, I discussed Static IPs.  I seem to have been a bit confused about this.  I am not using OpenVPN as an originator of static public IP addresses.  That is outside the scope of my use case.

What I have done instead is I have used a floating IP address to provide predictability.  Floating addresses on DigitalOcean are free if that are attached to a droplet.  I am sure there similar rig for Elastic IPs on AWS.  This is what worked for me.  (Because using a floating IP doesn’t work out of the box).

So I used the following sources:

In the comments, Cellu on October 13, 2019 had the most relevant information for me.  But I kind merged it into the tutorial from digital ocean found here.

In particular, the following passage was apropos.

When you have the interface associated with your default route, open the /etc/ufw/before.rules file to add the relevant configuration:

sudo nano /etc/ufw/before.rules

# rules.before
# Rules that should be run before the ufw command line added rules. Custom
# rules should be added to one of these chains:
#   ufw-before-input
#   ufw-before-output
#   ufw-before-forward

# NAT table rules
# Allow traffic from OpenVPN client to wlp11s0 (change to the interface you discovered!)

# Don't delete these required lines, otherwise there will be errors
. . .

UFW rules are typically added using the ufw command. Rules listed in the before.rules file, though, are read and put into place before the conventional UFW rules are loaded. Towards the top of the file, add the highlighted lines below. This will set the default policy for the POSTROUTING chain in the nat table and masquerade any traffic coming from the VPN. Remember to replace wlp11s0 in the -A POSTROUTING line below with the interface you found in the above command:

While the above ufw conf worked out the box, it displayed my public IP address as the droplet address, even though there was a floating IP address attached to the droplet.  I remedied this by adding the following line to /etc/openvpn/server.conf

local anchor-ip

In this case, anchor IP is an actual ip address, not the word.  You can find it following the instruction here:,a%20Floating%20IP%20to%20it.

After you set the anchor-ip in server.conf, restart openvpn

systemctl restart openvpn@server

Then rather than adjusting the variables using iptables CLI, I added the line suggested by Cellu to the before.rules file in /etc/ufw/before.rules

Here it is again, with my edit.

# rules.before
# Rules that should be run before the ufw command line added rules. Custom
# rules should be added to one of these chains:
#   ufw-before-input
#   ufw-before-output
#   ufw-before-forward

# NAT table rules
# This is my edit based on Cellu's iptables commands.
# Change anchor-ip to the anchor IP of the droplet.
# I also changed /8 to /24 on the next line.
-A POSTROUTING -s -j SNAT --to-source anchor-ip
# Allow traffic from OpenVPN client to wlp11s0 (change to the interface you discovered!)

# Don't delete these required lines, otherwise there will be errors
. . .

Next restart UFW by rebooting the machine or by running the following.

sudo ufw disable

sudo ufw enable

an excellent tutorial on hosting OpenVPN

An excellent tutorial on OpenVPN from DigitalOcean:

Really, where would I be without DigitalOcean?  Best documentation on the Web.

Why host OpenVPN?  Is using a VPN more secure? No.  It redistributes risk, but the risk is always there.  Whether or not the risk is appropriate to your use case is a judgment call, but there are situations where your information is more private on a VPN if you are using public wifi, (but not more private to a VPN provider).  I don’t find that self-hosting your VPN removes that liability.  The Internet is gooey.  Every step you take leaves a track.

In this post, I would like to tell you about my use case, and some obstacles I encountered while setting up the VPN.

First, for this project, I’ve returned to Digital Ocean.  They provided this glorious tutorial, and they provide excellent hosting at competitive and predictable prices.  That decision was a no brainer for me.  Keep in mind, however, that I am more accustomed to AWS.

So for me, my goal is not foremost to hide from hackers (although isn’t that everyone’s goal?)  What I am looking for is a static IP.  Why do I need a static IP?  I need a “key” to unlock my firewall.  And my ISP keeps changing my keys.  So on Ubuntu, one it’s greatest features UFW, Uncomplicated Firewall.  It is a very good and easy to use interface for iptables, and I have to used it to stop brute force attacks in their tracks several times.  The code is so simple.  One line of code will change your life whether you are on Ubuntu desktop or Ubuntu server.

sudo ufw allow {}

Having a predictable IP Address makes this much easier to use, especially on AWS where you do not have console access to your VMs in the control panel.  Now, granted, you do have security groups on AWS which is a kind of meta-firewall.  But every now then, the firewall has go down for some practical reason, or simply because there’s a lot of hands in the cookie jar, or what have you.  Having UFW turned on and activated, allowing SSH access on from my IP address, is a really good failsafe.  But if my ISP changes my IP address, I get locked out, and it’s a pain in the ass to recover.

That is why I find OpenVPN so powerful.  It has already solved my problem, for just a day’s work.  VPN companies haven’t made it easy for me to acquire a static IP.  I haven’t really been able to find a good, affordable solution anywhere.  This has allowed me to roll with the punches.

I encountered a few problems while implementing this.  I wasn’t able to get inline certs to work, and I will to sort that out later.  Also, that meta-firewall on DigitalOcean threw me for a loop.  I wasn’t used to this format, and I boneheadedly forgot to attach my firewall to droplet.  Once I did that, I’m up and running.

This is how I locked it down.

I am using the meta-firewall on DigitalOcean to only allow access to my local network for all ports, except by necessity I have to allow the two VMs to communicate with eachother.  Is it invincible.  No.  But it’s pretty good.  DigitalOcean, I am one satisfied customer.

using bash to create an easy-to-use shell menu to organize multiple remote MySQL and SSH hosts

Have you ever met one of those guys to whom Linux itself is as much an Object Oriented Language as an operating system?  And you think, “Wow, I am good with Linux, but this guy is savant.”  Well, he uses bash.  (Edit: Down with patriarchy… Linuxbabe is one of those guys).

Bash is a short dictionary of keys words that can be stitched together into shell commands.  In other words, it is like the language of Linux Shell.  It can be integrated into other languages and shell commands, making it a very powerful tool. With regex you can do almost anything.

With the help of the Ubuntu community, I have made a bash script that allows the user to select a remote MySQL host from a numbered menu, and then connect to it just by selecting a number.

My code is based on an example by Dennis Williamson at AskUbuntu.  You can find it here:

This tutorial will proceed to cover the following:

  1. How to open and save your shell script.
  2. How to adapt Williamson’s code to turn it into the script above.
  3. How to make your code executable.
  4. How to alias your code with a snazzy name so that you can access it from shell by typing in something like “Databaser.”

So basically, I’m doing this on Ubuntu 18.04.. Open terminal and use the text editor of your choice. I use Vim. Nano is fine. Here is Williamson’s code:

# Bash Menu Script Example

PS3='Please enter your choice: '
options=("Option 1" "Option 2" "Option 3" "Quit")
select opt in "${options[@]}"
   case $opt in
        "Option 1")
            echo "you chose choice 1"
        "Option 2")
            echo "you chose choice 2"
        "Option 3")
            echo "you chose choice $REPLY which is $opt"
        *) echo "invalid option $REPLY";;

Open a new file with your text editor and give a name like Make sure it ends in sh

Then, where Williamson code says echo “you chose 1” add the path to the executable that you want to fire when the user presses 1. A note about that.

Like Windows, Linux has executable files, but they’re not always as easy to spot for an untrained eye. With Windows, you see .exe, and you know it’s an executable. With linux, you may have to run some commands to find out for sure. With mysql, this what it looks like in my setup. The executable is simply called “mysql” and it is located in “/usr/bin/local”

Some systems allow you to discover the path belonging to an executable by running the keyword “which” if the executable is global or if the path is set.

$ which mysql

To execute a file that hasn’t had the path set, you can fall into its directory, and then type this into shell:

$ ./ 

or you can use the complete path:

$ /path/to/file/ 

note that when you are merely calling the file from within its directory, you add a ./ first. But when you execute the file using the complete path, you do not.

We are going to use the complete path, because you can use that anywhere in the file system.

Find the complete path for both mysql and ssh by typing into terminal

which ssh


 which mysql

Then replace “you chose 1” etc with the path that you want a shortcut to.

# Bash Menu Script Example

PS3='Please enter your choice: '
options=('' '' 'vbox@lan' 'nevermind')
select opt in "${options[@]}"
        case $opt in
                 \/usr/bin/mysql -h -P 3306 -u eljefe -p'mypassword'
                /usr/bin/ssh eljefe@
            *) echo "invalid option $REPLY";;

COLUMNS=12 makes the options all appear in one column.

this creates this:

It’s time to make the file executable.  This is very easy in Unix based systems suchs as Linux or macOs.  Just do this.

$ chmod +x /path/to/file/

Now your code is executable.  You can run it by doing

$ /path/to/file/

Exporting create procedure to text file in MySQL

So there’s this handy dandy code:

mysql› show create procedure myProcedure; 

That will output the recipe with which myProcedure was created.  But what if you want to export 1200 lines of code to a text file.  You do that from terminal.

$ mysql -h -P 3306 -u myUser -p -e "show create procedure myDatabase.myProcedure" ›› myExport.txt 

So in this case, -h is the host, you can put an IP address, domain, or AWS dns there, -P is the port, -u is the user, lowercase -p is the password (leave it blank to be prompted to enter it), then -e says run this query, and >> says put the contents in this file.  Note that you will have to add the database name to avoid the no database selected error.

If you are just connecting to a local MySQL server, leave the hostname out and port out, and use sudo credentials, like this:

$ sudo mysql -u myUser -p -e "show create procedure myDatabase.myProcedure" ›› myExport.txt 

Open the file with your Vim editor.

That brings up something quasi like this.

create procedure myProcedure\nbegin\nselect * from myTable\nend

All of the \n’s can be a problem if you are exporting thousands of lines of code.  For that, Vim to the rescue.  Type into your Vim editor:

:%s /\\n/\r/g

You need two backslashes on n like \\n because the export has escaped the backslash already.  But you only use 1 backslash on \r because you want an actual line break, not to print the characters.  and the final /g allows you to search through one very long line of text.

This creates the following text file:


create procedure myProcedure
select * from myTable

To find your line breaks again it is a search for \n with one backslash.

How to filter blue light on a Raspberry Pi (Raspbian)

Don’t hate me because I’m obvious.

so I had a conundrum. I wanted a blue light filter like Night Light on Windows and ChromeOS or Night Shift on OSx. But nothing would do it on Raspberry Pi. I even tried to RDP into one of my other computers to use its night light, but apparently those features disappear through Chrome Desktop Connect. So here was my solution. It has limitations.

This only works for Chrome and Chromium Browser afaik.
This only works for what is viewed via browser.
If you view another desktop through your browser through Remote Desktop Connect, you can switch to fullscreen but you have to go through your chrome menu and not the Chrome Remote Desktop menu.

So it was Chrome Web Store to the rescue. I downloaded an app called Night Shift. There were several and this is the only one I’ve tried because it has worked very well. You can find it here.

It doesn’t change the desktop, theme, or terminal, but you can adjust those settings relative to each application.

deploying a laravel app on Ubuntu 18.04

$ sudo su
$ apt install php mysql-server apache2 composer git npm vim
$ cd /var/www
$ git clone https://your.repo
$ cd /var/www/your_repo
$ composer install

composer usually throws an error here because you have to install php dependencies. Scroll to the top of the error message. It may be something like, Laravel requires ext-dom or Laravel requires ext-curl.

Whenever you see ext, replace it with php, and then run:

$ apt install php-dom php-curl

After you install dependencies and composer finishes installing the php portion of your app, do this:

$ vim .env.example

in the .env.example

do this:

:sav .env

Then edit the following environment variables.


if you are running prod, make sure you do this:


additional dependency that must be installed

$ apt install php-mysql

then do this

$ php artisan key:generate
$ php artisan migrate
$ npm install
$ npm run dev

Oh yeah, very important. 90% of problems with Laravel deployment out of the box can be solved with this. Do this last.

$ chown -R www-data:www-data /path/to/app

and remember to exit root.

using bash to extract certain columns from a CSV

So here’s a handy dataset:

It contains a registry of medical providers (clinics, physicians, etc) and their National Provider ID called an NPI.  Really useful if you need to search for that information or if you need to add it to your datasets, for example, if you have the NPI but you don’t have the first name or last name, or vice versa.

Here’s the problem.  This freaking dataset is HUGE!  The CSV file that I need to match against my tables had over 100 columns, with something like 4 million rows.  That is 400,000,000 cells.  My ‘puter coughed and sputtered.  Here comes bash to save the day.

Check out this post a stackoverflow.

User702403 had a question, but for me, the question was the answer.

awk -F "," '{print $1 "," $2}' infile.csv > outfile.csv

To a bash noobie such as myself, this is gold. And it just about says it all. $1 will be the first column. $2 will be the next. Use for example awk -F "," '{print $1 "," $6 "," $7}' infile.csv > outfile.csv if you want print the first, 6th, and 7th columns. Fortunately, all of my needed columns were in the front but if you need to extract the 3006th column and you didn’t know which one it was, you could copy the first row into a spreadsheet and then pivot.

How a typo can break Laravel, and how bash can fix it

First off, thank you Nathan Long at StackExchange for saving my app.  Here’s the link to his sage answer:

This is the typo that broke my code:

php artisan app:name

Here is just pseudocode for a very specific label that I was applying to the app namespace.  However, I carelessly added the top level domain of the Website the app was on.  Namespace don’t like punctuation.  So artisan recursively renamed every occurrence of
use App\
namespace App
etc.  Bash to the rescue.

To change it back, I did the following from CLI, slightly modifying the StackExchange code:

find . -type f -name "*.*" -print0 | xargs -0 sed -i '' -e 's/'

I was able to do this because in this case was a very unique label. If I chose a more generic term, this could unleash utter destruction. Back your work up, folks!

get table size in megabytes, MySQL

Here’s an awesome snippet of code that I got from Thomas Eisenbarth at

SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "$your_database";

Basically, it will display all the tables in a database with disk usage in the next column. All you to do is change $your_database to the actual name of a database on your MySQL server.

Be sure to thank Thomas Eisenbarth by when you click on his link.