Basic iptables Firewall

The iptables software is a user space application for configuring packet filtering in the Linux kernel. iptables is used to set rules for packets that travel through a host’s network stack and at certain points, called hooks, the iptables rules are evaluated and actions, such as dropping a packet, can be executed. In this post I will draft a script to setup the iptables’ rules for a firewall and then set that script to be executed at boot time. Continue reading

Adding a Samba Share

This post will use Samba Version 4.1.6-Ubuntu (the version in use on Ubuntu 14.04 LTS Server at the time of this writing) to setup Samba (smb) shares from a drive attached to a server on a local network. Three shares will be created: dropbox, hub, and restricted which will correspond to directories on the attached drive with the same names (although the names of shares and directories can differ).

The dropbox share will allow any user to connect and read, add, and remove objects from it. The hub, will allow any user to connect and read files, but only listed users will have the ability to add and remove them. Finally the restricted share will only allow access and privileges to certain users.

Mount the Drive in the File System

First, find and mount the drive that will host the shares in the file system. Use lsblk to print the device list, then mount the device — in our example the device that represents our drive is /dev/sdb1.

> lsblk
NAME   MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda      8:0    0   149G  0 disk 
├─sda1   8:1    0 145.1G  0 part /
├─sda2   8:2    0     1K  0 part 
└─sda5   8:5    0   3.9G  0 part [SWAP]
sdb      8:16   0   1.8T  0 disk 
└─sdb1   8:17   0   1.8T  0 part 

> sudo mount /dev/sdb1 /media/share

To automatically mount this device, grab the UUID of the disk with blkid and add it to the /etc/fstab file (I’m using vim, but use whatever text editor you like). Once the fstab file has been edited correctly, the drive will be mounted automatically during system start up.

> sudo blkid
/dev/sda1: UUID="ed9feafe-6654-4173-9967-7b6fe43581b5" UUID_SUB="532b088c-66f9-4ca2-8664-d80ff7612891" TYPE="btrfs" 
/dev/sda5: UUID="678e7a38-6367-4305-929f-b96ae36d7329" TYPE="swap" 
/dev/sdb1: LABEL="Black" UUID="46E6328FE6327EED" TYPE="ntfs" 

> sudo vim /etc/fstab

Add the following line to fstab, save and close the file.

UUID=46E6328FE6327EED /media/share ntfs defaults 0 2

Creating the Share and Controlling Access

Before we begin editing the Samba configuration file, it’s a good idea to make a backup of the original. Also, access to the share is controlled by Samba, so we also need to create users within the Samba program.

> sudo cp /etc/samba/smb.conf /etc/samba/smb.conf.orig

> sudo smbpasswd -a [user]
New SMB password:
Retype new SMB password:
Added user [user].

The following are the definitions of the shares we’ve set out to create:

dropbox

Before defining our dropbox, we change the ownership of the /media/share/dropbox directory to facilitate the requirement that anyone can access/read/write/delete files and directories. In this case, I’ve chosen to set the nobody user and nogroup group as having ownership of the dropbox.

> sudo chown nobody:nogroup /media/share/dropbox

Now, we can create the definition of the share.

[dropbox]
 path = /media/share/dropbox
 browseable = yes
 read only = no
 guest ok = yes
 force user = nobody
 force group = nogroup
 force create mode = 664
 force directory mode = 775

The path directive tells where in the file system the share is located, and browseable determines whether or not the share will be advertised on the network (in the network neighborhood on Windows systems). By setting read only to ‘no’, we’ve declared that users who can access the share can write to it, and by setting guest ok to ‘yes’ we’re not restricting access.

Forcing the user and group to nobody and nogroup respectively we’re ensuring files and directories created in the dropbox will be owned by the unprivileged Posix user and group of the same corresponding names. Forcing create mode makes sure files are created with read and write privileges to users accessing the share, and force directory mode makes sure directories are created with read, write, and execute permissions.

hub

[hub]
 path = /media/share/hub
 browseable = yes
 read only = yes
 guest ok = yes
 write list = [user]
 force user = nobody
 force group = nogroup
 force create mode = 664
 force directory mode = 775

This share is a lot like the last one, but notice this share has been declared read only. This means that, although anybody can find and connect to the share, only those users listed in the write list can write new files and delete files within the share.

restricted

The restricted share will not be advertised on the network and only certain users will be allowed to access it. When these users do gain access they will have read and write permissions permissions on all files and full permissions on directories. Unix users that will have access to this share must be the user or in the group that the share forces users to use.

[restricted]
  path = /media/share/restricted
  browseable = no
  read only = no
  guest ok = no
  valid users = [user]
  force user = [user]
  force group = restricted
  force create mode = 664
  force directory mode = 775

 

Adding a PHP Program to WordPress

I recently switched my blog from my own programming to using WordPress. I have long recognized  WordPress as good a software project and it has stood the test of time. Not only has the software been around since 2003, it has continued to improve. The improvements to WordPress have allow the software to run more and more projects (although it’s still used, in my opinion, too often where it doesn’t fit right).

For my needs I thought it would work well. I’m running a business while attending university and keeping up with certifications, so I didn’t want to spend the time to make it easier to post to my blog — which had been on my to do list — so I just installed WordPress. The installation took a matter of minuets and after a bit of time looking though themes I quickly had my site up and running with the CMS and looking decent with a freely available theme.

The Problem

I wanted to add a table to my site that shows the books I’ve read. I wanted the book’s title to be displayed along with the author(s) and a picture of the book. I did not want to write out the HTML each time and the WordPress interface doesn’t make working with tables easy.

The Approach

I’m a member of paperbackswap.com, and on their site they maintain a list of books that I’ve read. I can export this list as a CSV file. I thought it would be nice to place this CSV file in my site’s directory structure and have a PHP script parse the file and generate the desired table. This lead me to ask the following questions:

  1. How do I add a custom page to WordPress?
  2. How do I control that page with PHP?

Thanks to the user Adam Hopkinson on Stackoverflow, I had these questions answered and was ready to begin adding my custom PHP page to my WordPress site.

The Execution

First, I created a directory for my custom pages in the WordPress directory structure named my-pages. Then, I uploaded the CSV file from paperbackswap into this directory. I named the file readBooks.csv. The fields of the CSV file were: title, ISBN10, and ISBN13. I really don’t need the ISBNs for my purposes but it didn’t hurt to leave them there (I may use them in the future, and I can always leave those fields blank when adding to the list), but I was going to need a field for the book’s cover image. I decided that would become the first field of the CSVs.

Next, I created the new page by following Adam’s instructions. To make things easier, I linked to this page (which is in the WordPress themes directory) into the my-pages directory and named the page books.php; my directory looked like so:

$> ls -hal
lrwxrwxrwx 1 jason www-data   35 Oct 11 20:20 books.php -> ../wp-content/themes/vito/books.php
-rwxrwxr-x 1 jason www-data 4.1K Oct 13 02:03 readBooks.csv

The books.php page is a copy of my theme’s template, and contains its PHP code. I thought it would be too messy to insert my code directly into this template so I created a “controller” for it which contains the page specific PHP. Then I included the controller in the page so that I can call the function needed to print the books table. Here’s the head of books.php:

$> head books.php
<?php
/*
 * Template Name: books
 */

require_once(ABSPATH.'my-pages/booksController.php');
?>

At this point, I started drafting the code that would parse the CSV file and print my table. I found that it was advantageous to create a Book class that would serve as a constructor. I would give the new Book object’s constructor an associative array containing the fields parsed from the CSV file. The Book objects would also provide a __toString method which would return an HTML table row containing the given book’s data. The function that instantiates the Books would take care of generating the rest of the HTML table.

By now the my-pages directory structure looked like this:

ls -F
classes/  img/  booksController.php*  books.php@  readBooks.csv*

I added the classes directory to hold the definition of the Book class (and future classes, should I decide to create new pages) and the img directory to contain the book covers. With all this in place, when I want to add a book to the Books page, I simply upload the cover image to the img directory and enter the book’s details in the readBooks.csv file and voilà, the book would be added to the books table.

SQL Aggregation, Grouping, and Having

Aggregating

In SQL, the standard aggregation operators are SUM, MIN, MAX, and COUNT and we can apply them to the attributes of relations. For example we have a schema employee(eid, name, dob, hired_date, salary), which holds records on each of a company’s employees. We can COUNT and see how many employees the company has with the following query:

SELECT COUNT(*)
FROM employee;

The * is an argument to the count operator and means to count all tuples in the relation. With the DISTINCT operator we eliminate duplicates for the query result, so if we wanted to see if any of the employees had the same name we could compare the previous query with the following:

SELECT COUNT(DISTINCT name)
FROM employee;

If the first count is larger than the second, then the company has at least two employees with the same name. COUNT also does not count tuple attributes with NULL value, and AVG and SUM don’t factor them in when calculating either. We can use AVG to find the average salary of our employees and SUM to calculate the total amount paid for employee salaries with these queries:

SELECT AVG(salary)
FROM (employee);

SELECT SUM(salary)
FROM (employee);

Grouping

A grouping query is executed on a relation with the GROUP BY clause. GROUP BY is followed by an attribute list and the resulting tuples are grouped successively according to the list. To see how grouping works, let’s say that the company from our earlier examples is a shipping company and owns trucks. The company keeps track of truck purchases and their usage with the following relations: truck(tid, make, model, year, purch_year, purch_quarter, cost). In a simple example we could query the database and ask which years the company purchased trucks:

SELECT purch_year
FROM truck
GROUP BY purch_year;

GROUP BY would come after a WHERE clause, but in this case we didn’t need one so it was omitted. The result of this query would be a single attribute relation (a single column table) with values reflecting the years in which the company purchased at least one truck. If the company purchased more that one truck in that year that purchase would be aggregated with all others in that year.

Only the attributes that are listed in the GROUP BY clause can appear unaggregated in the SELECT clause. The next two queries would tell the company which years and in which quarters they purchased trucks and the total amount spent in each quarter.

SELECT purch_year, quarter
FROM truck
GROUP BY year, quarter
ORDER BY quarter, year;

SELECT purch_year, SUM(cost) as annualcost
FROM truck
GROUP BY purch_year;

The first query would return a relation of 2-tuples with the first element being purch_year and the second quarter (the result is also first sorted by quarter, then by year). The second query would also return a 2-tuple but the elements would be purch_year and annualcost which is the aggregated (by simple addition in this case) cost of trucks for each year.  Because cost is not in the GROUP BY clause, it must be aggregated.

The HAVING Clause

In our last set of queries, we asked for the costs of trucks purchased on an annual basis. Suppose for some reason the company wanted to find the average cost spent in a year only in years where every truck purchased in that year cost over $15,000. To ask this question we can use the HAVING clause.

SELECT purch_year, AVG(cost) as avgcost
FROM truck
GROUP BY purch_year
HAVING MIN(cost) > 15000;

The HAVING clause applies to each group. In this case, MIN is calculated on the attribute cost for each purchase year (purch_year) then compared to 15,000. If the minimum value for all costs in a purchase year are greater that 15,000 then the purch_year shows up in the query result (a 2-tuple (purch_year, avgcost)).