End Point

News

Welcome to End Point's blog

Ongoing observations by End Point people.

Protecting and auditing your secure PostgreSQL data

PostgreSQL functions can be written in many languages. These languages fall into two categories, 'trusted' and 'untrusted'. Trusted languages cannot do things "outside of the database", such as writing to local files, opening sockets, sending email, connecting to other systems, etc. Two such languages are PL/pgSQL and and PL/Perl. For "untrusted" languages, such as PL/PerlU, all bets are off, and they have no limitations placed on what they can do. Untrusted languages can be very powerful, and sometimes dangerous.

One of the reasons untrusted languages can be considered dangerous is that they can cause side effects outside of the normal transactional flow that cannot be rolled back. If your function writes to local disk, and the transaction then rolls back, the changes on disk are still there. Working around this is extremely difficult, as there is no way to detect when a transaction has rolled back at the level where you could, for example, undo your local disk changes.

However, there are times when this effect can be very useful. For example, in a recent thread on the PostgreSQL "general" mailing list (aka pgsql-general), somebody asked for a way to audit SELECT queries into a logging table that would survive someone doing a ROLLBACK. In other words, if you had a function named weapon_details() and wanted to have that function log all requests to it by inserting to a table, a user could simply run the query, read the data, and then rollback to thwart the auditing:

BEGIN;

SELECT weapon_details('BFG 9000'); -- also inserts to an audit table

ROLLBACK;                          -- inserts to the audit table are now gone!

Certainly there are other ways to track who is using this query, the most obvious being by enabling full Postgres logging (by setting log_statement = 'all' in your postgresql.conf file.) However, extracting that information from logs is no fun, so let's find a way to make that INSERT stick, even if the surrounding function was rolled back.

Stepping back for one second, we can see there are actually two problems here: restricting access to the data, and logging that access somewhere. The ultimate access restriction is to simply force everyone to go through your custom interface. However, in this example, we will assume that someone has psql access and needs to be able to run ad hoc SQL queries, as well as be able to BEGIN, ROLLBACK, COMMIT, etc.

Let's assume we have a table with some Very Important Data inside of it. Further, let's establish that regular users can only see some of that data, and that we need to know who asked for what data, and when. For this example, we will create a normal user named Alice:

postgres=> CREATE USER alice;
CREATE ROLE

We need a way to tell which rows are suitable for people like Alice to view. We will set up a quick classification scheme using the nifty ENUM feature of PostgreSQL:

postgres=> CREATE TYPE classification AS ENUM (
 'unclassified',
 'restricted',
 'confidential',
 'secret',
 'top secret'
);
CREATE TYPE

Next, as a superuser, we create the table containing sensitive information, and populate it:

postgres=> CREATE TABLE weapon (
  id              SERIAL          PRIMARY KEY,
  name            TEXT            NOT NULL,
  cost            TEXT            NOT NULL,
  security_level  CLASSIFICATION  NOT NULL,
  description     TEXT            NOT NULL DEFAULT 'a fine weapon'
);
NOTICE:  CREATE TABLE will create implicit sequence "weapon_id_seq" for serial column "weapon.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "weapon_pkey" for table "weapon"
CREATE TABLE

postgres=> INSERT INTO weapon (name,cost,security_level) VALUES
 ('Crowbar',  10,  'unclassified'),
 ('M9',  200,  'restricted'),
 ('M16A2',  300,  'restricted'),
 ('M4A1',  400,  'restricted'),
 ('FGM-148 Javelin',  700,  'confidential'),
 ('Pulse Rifle',  50000,  'secret'),
 ('Zero Point Energy Field Manipulator',  'unknown',  'top secret');
INSERT 0 7

We don't want anyone but ourselves to be able to access this table, so for safety, we make some explicit revocations. We'll examine the permissions before and after we do this:

postgres=> \dp weapon
                          Access privileges
 Schema |  Name  | Type  | Access privileges | Column access privileges 
--------+--------+-------+-------------------+--------------------------
 public | weapon | table |                   | 

postgres=> REVOKE ALL ON TABLE weapon FROM public;
REVOKE

postgres=> \dp weapon
                               Access privileges
 Schema |  Name  | Type  |     Access privileges     | Column access privileges 
--------+--------+-------+---------------------------+--------------------------
 public | weapon | table | postgres=arwdDxt/postgres | 

As you can see, what the REVOKE really does is remove the implicit "no permission" and grant explicit permissions to only the postgres user to view or modify the table. Let's confirm that Alice cannot do anything with that table:

postgres=> \c postgres alice
You are now connected to database "postgres" as user "alice".
postgres=> postgres=> SELECT * FROM weapon;
ERROR:  permission denied for relation weapon
postgres=> postgres=> UPDATE weapon SET id = id;
ERROR:  permission denied for relation weapon

Alice does need to have access to parts of this table, so we will create a "wrapper function" that will query the table for us and return some results. By declaring this function as SECURITY DEFINER, it will run as if the person who created the function invoked it - in this case, the postgres user. For this example, we'll be letting Alice see the "cost and description" of exactly one item at a time. Further, we are not going to let her (or anyone else using this function) view certain items. Only those items classified as "confidential" or lower can be viewed (i.e. "confidential", "restricted", or "unclassified"). Here's the first version of our function:

postgres=> CREATE LANGUAGE plperlu;
CREATE LANGUAGE

postgres=> CREATE OR REPLACE FUNCTION weapon_details(TEXT)
RETURNS TABLE (name TEXT, cost TEXT, description TEXT)
LANGUAGE plperlu
SECURITY DEFINER
AS $bc$

use strict;
use warnings;

## The item they are looking for
my $name = shift;
## We will be nice and ignore the case and any whitespace
$name =~ s{^\s*(\S+)\s*$}{lc $1}e;

## What is the maximum security_level that people who are 
## calling this function can view?
my $seclevel = 'confidential';

## Query the table and pull back the matching row
## We need to differentiate between "not found" and "not allowed",
## by comparing a passed-in level to the security_level for that row.
my $SQL = q{
SELECT name,cost,description,
  CASE WHEN security_level <= $1 THEN 1 ELSE 0 END AS allowed
FROM weapon
WHERE LOWER(name) = $2};

## Run the query, pull back the first row, as well as the allowed column value
my $sth = spi_prepare($SQL, 'CLASSIFICATION', 'TEXT');
my $rv = spi_exec_prepared($sth, $seclevel, $name);
my $row = $rv->{rows}[0];
my $allowed = delete $row->{allowed};

## Did we find anything? If not, simply return undef
if (! $rv->{processed}) {
   return undef;
}

## Throw an exception if we are not allowed to view this row
if (! $allowed) {
   die qq{Sorry, you are not allowed to view information on that weapon!\n};
}

## Return the requested data
return_next($row);

$bc$;
CREATE FUNCTION

The above should be fairly self-explanatory. We are using PL/Perl's built-in database access functions, such as spi_prepare, to do the actual querying. Let's confirm that this works as it should for Alice:

postgres=> \c postgres alice
You are now connected to database "postgres" as user "alice".

postgres=> SELECT * FROM weapon_details('crowbar');
  name   | cost |  description  
---------+------+---------------
 Crowbar | 10   | a fine weapon
(1 row)

postgres=> SELECT * FROM weapon_details('anvil');
 name | cost | description 
------+------+-------------
(0 rows)

postgres=> SELECT * FROM weapon_details('pulse rifle');
ERROR:  Sorry, you are not allowed to view information on that weapon!
CONTEXT:  PL/Perl function "weapon_details"

Now that we have solved the restricted access problem, let's move on the auditing. We will create a simple table to hold information about who accessed what and when:

postgres=> CREATE TABLE data_audit (
  tablename TEXT         NOT NULL,
  arguments TEXT             NULL,
  results   INTEGER          NULL,
  status    TEXT         NOT NULL  DEFAULT 'normal',
  username  TEXT         NOT NULL  DEFAULT session_user,
  txntime   TIMESTAMPTZ  NOT NULL  DEFAULT now(),
  realtime  TIMESTAMPTZ  NOT NULL  DEFAULT clock_timestamp()
);
CREATE TABLE

The 'tablename' column simply records which table they are getting data from. The 'arguments' is a free-form field describing what they were looking for. The 'results' column shows how many matching rows were found. The 'status' column will be used primarily to log unusual requests, such as the case where Alice looks for a forbidden item. The 'username' column records the name of the user doing the searching. Because we are using functions with SECURITY DEFINER set, this needs to be session_user, not current_user, as the latter will switch to 'postgres' within the function, and we want to log the real caller (e.g. 'alice'). The final two columns tell us then the current transaction started, and the exact time when an entry was made inside of this table. As a first attempt, we'll have our function do some simple inserts to this new data_audit table:

postgres=> CREATE OR REPLACE FUNCTION weapon_details(TEXT)
RETURNS TABLE (name TEXT, cost TEXT, description TEXT)
LANGUAGE plperlu
SECURITY DEFINER
AS $bc$

use strict;
use warnings;

## The item they are looking for
my $name = shift;
## We will be nice and ignore the case and any whitespace
$name =~ s{^\s*(\S+)\s*$}{lc $1}e;

## What is the maximum security_level that people who are 
## calling this function can view?
my $seclevel = 'confidential';

## Query the table and pull back the matching row
## We need to differentiate between "not found" and "not allowed",
## by comparing a passed-in level to the security_level for that row.
my $SQL = q{
SELECT name,cost,description,
  CASE WHEN security_level <= $1 THEN 1 ELSE 0 END AS allowed
FROM weapon
WHERE LOWER(name) = $2};

## Run the query, pull back the first row, as well as the allowed column value
my $sth = spi_prepare($SQL, 'CLASSIFICATION', 'TEXT');
my $rv = spi_exec_prepared($sth, $seclevel, $name);
my $row = $rv->{rows}[0];
my $allowed = delete $row->{allowed};

## Log this request
$SQL = 'INSERT INTO data_audit(tablename,arguments,results,status)
  VALUES ($1,$2,$3,$4)';
my $status = $rv->{rows}[0] ? $allowed ? 'normal' : 'forbidden' : 'na';
$sth = spi_prepare($SQL, 'TEXT', 'TEXT', 'INTEGER', 'TEXT');
spi_exec_prepared($sth, 'weapon', $name, $rv->{processed}, $status);


## Did we find anything? If not, simply return undef
if (! $rv->{processed}) {
   return undef;
}

## Throw an exception if we are not allowed to view this row
if (! $allowed) {
   die qq{Sorry, you are not allowed to view information on that weapon!\n};
}

## Return the requested data
return_next($row);

$bc$;

However, this fails the case pointed out in the original poster's email about viewing the data within a transaction that is then rolled back. It also fails to work at all when a forbidden item is requested, as that insert is rolled back by the die() call:

postgres=> \c postgres alice
You are now connected to database "postgres" as user "alice".

postgres=> SELECT * FROM weapon_details('crowbar');
  name   | cost |  description  
---------+------+---------------
 Crowbar | 10   | a fine weapon
(1 row)

postgres=> SELECT * FROM weapon_details('pulse rifle');
ERROR:  Sorry, you are not allowed to view information on that weapon!
CONTEXT:  PL/Perl function "weapon_details"

postgres=> BEGIN;
BEGIN
postgres=> SELECT * FROM weapon_details('m9');
 name | cost |  description  
------+------+---------------
 M9   | 200  | a fine weapon
(1 row)
postgres=> ROLLBACK;
ROLLBACK

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=> SELECT * FROM data_audit \x \g
Expanded display is on.
-[ RECORD 1 ]----------------------------
tablename | weapon
arguments | crowbar
results   | 1
status    | normal
username  | alice
txntime   | 2012-01-30 17:37:39.497491-05
realtime  | 2012-01-30 17:37:39.545891-05

How do we get around this? We need a way to commit something that will survive the surrounding transaction's rollback. The closest thing Postgres has to such a thing at the moment is to connect back to the database with a new and entirely separate connection. Two such popular ways to do so are with the dblink program and the PL/PerlU language. Obviously, we are going to focus on the latter, but all of this could be done with dblink as well. Here are the additional steps to connect back to the database, do the insert, and then leave again:

postgres=> CREATE OR REPLACE FUNCTION weapon_details(TEXT)
RETURNS TABLE (name TEXT, cost TEXT, description TEXT)
LANGUAGE plperlu
SECURITY DEFINER
VOLATILE
AS $bc$
use strict;
use warnings;
>use DBI;

## The item they are looking for
my $name = shift;
## We will be nice and ignore the case and any whitespace
$name =~ s{^\s*(\S+)\s*$}{lc $1}e;

## What is the maximum security_level that people who are 
## calling this function can view?
my $seclevel = 'confidential';

## Query the table and pull back the matching row
## We need to differentiate between "not found" and "not allowed",
## by comparing a passed-in level to the security_level for that row.
my $SQL = q{
SELECT name,cost,description,
  CASE WHEN security_level <= $1 THEN 1 ELSE 0 END AS allowed
FROM weapon
WHERE LOWER(name) = $2};

## Run the query, pull back the first row, as well as the allowed column value
my $sth = spi_prepare($SQL, 'CLASSIFICATION', 'TEXT');
my $rv = spi_exec_prepared($sth, $seclevel, $name);
my $row = $rv->{rows}[0];
my $allowed = defined $row ? delete $row->{allowed} : 1;

## Log this request
$SQL = 'INSERT INTO data_audit(username,tablename,arguments,results,status)
  VALUES (?,?,?,?,?)';
my $status = $rv->{rows}[0] ? $allowed ? 'normal' : 'forbidden' : 'na';
my $dbh = DBI->connect('dbi:Pg:service=auditor', '', '',
  {AutoCommit=>0, RaiseError=>1, PrintError=>0});
$sth = $dbh->prepare($SQL);
my $user = spi_exec_query('SELECT session_user')->{rows}[0]{session_user};
$sth->execute($user, 'weapon', $name, $rv->{processed}, $status);
$dbh->commit();

## Did we find anything? If not, simply return undef
if (! $rv->{processed}) {
   return undef;
}

## Throw an exception if we are not allowed to view this row
if (! $allowed) {
   die qq{Sorry, you are not allowed to view information on that weapon!\n};
}

## Return the requested data
return_next($row);

$bc$;
CREATE FUNCTION

Note that because we are making external changes, we marked the function as VOLATILE, which ensures that it will always be run every time it is called, and not cached in any form. We are also using a Postgres service file with the 'db:Pg:service=auditor'. This means that the connection information (username, password, database) is contained in an external file. This is not only tidier than hard-coding those values into this function, but safer as well, as the function itself can be viewed by Alice. Finally, note that we are passing the 'username' directly into the function this time, as we have a brand new connection which is no longer linked to the 'alice' user, so we have to derive it ourselves from "SELECT session_user" and then pass it along.

Once this new function is in place, and we re-run the same queries as we did before, we see three entries in our audit table:

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
Expanded display is on.
-[ RECORD 1 ]----------------------------
tablename | weapon
arguments | crowbar
results   | 1
status    | normal
username  | alice
txntime   | 2012-01-30 17:56:01.544557-05
realtime  | 2012-01-30 17:56:01.54569-05
-[ RECORD 2 ]----------------------------
tablename | weapon
arguments | pulse rifle
results   | 1
status    | forbidden
username  | alice
txntime   | 2012-01-30 17:56:01.559532-05
realtime  | 2012-01-30 17:56:01.561225-05
-[ RECORD 3 ]----------------------------
tablename | weapon
arguments | m9
results   | 1
status    | normal
username  | alice
txntime   | 2012-01-30 17:56:01.573335-05
realtime  | 2012-01-30 17:56:01.574989-05

So that's the basic premise of how to solve the auditing problem. For an actual production script, you would probably want to cache the database connection by sticking things inside of the special %_SHARED hash available to PL/Perl and Pl/PerlU. Note that each user gets their own version of that hash, so Alice will not be able to create a function and have access to the same %_SHARED hash that the postgres user has access to. It's probably a good idea to simply not let users like Alice use the language at all. Indeed, that's the default when we do the CREATE LANGUAGE call as above:

postgres=>  \c postgres alice
You are now connected to database "postgres" as user "alice".

postgres=> CREATE FUNCTION showplatform()
RETURNS TEXT
LANGUAGE plperlu
AS $bc$
  return $^O;
$bc$;
ERROR:  permission denied for language plperlu

Further refinements to the actual script might include refactoring the logging bits to a separate function, writing some of the auditing data to a file on the local disk, recording the actual results returned to the user, and sending the data to another Postgres server entirely. For that matter, as we are using DBI, you could send it to other place entirely - such as a MySQL, Oracle, or DB2 database!

Another place for improvement would be associating each user with a security_level classification, such that any user could run the function and only see things at or below their level, rather than hard-coding the level as "confidential" as we have done here. Another nice refinement might be to always return undef (no matches) for items marked "top secret", to prevent the very existence of a top secret weapon from being deduced. :)

Linux unshare -m for per-process private filesystem mount points

Private mount points with unshare

Linux offers some pretty interesting features that are either new, borrowed, obscure, experimental, or any combination of those qualities. One such feature that is interesting is the unshare() function, which the unshare(2) man page says “allows a process to disassociate parts of its execution context that are currently being shared with other processes. Part of the execution context, such as the mount namespace, is shared implicitly when a new process is created using fork(2) or vfork(2)”.

I’m going to talk here about one option to unshare: per-process private filesystem mount points, also described as mount namespaces. This Linux kernel feature has been around for a few years and is easily accessible in the userland command unshare(1) in util-linux-ng 2.17 or newer (which is now simply util-linux again without the "ng" distinction because the fork took over mainline development).

Running `unshare -m` gives the calling process a private copy of its mount namespace, and also unshares file system attributes so that it no longer shares its root directory, current directory, or umask attributes with any other process.

Yes, completely private mount points for each process. Isn’t that interesting and strange?

A demonstration

Here’s a demonstration on an Ubuntu 11.04 system. In one terminal:

% su -
Password:
# unshare -m /bin/bash
# secret_dir=`mktemp -d --tmpdir=/tmp`
# echo $secret_dir
/tmp/tmp.75xu4BfiCw
# mount -n -o size=1m -t tmpfs tmpfs $secret_dir
# df -hT
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/mapper/auge-root
              ext4    451G  355G   74G  83% /

There’s no system-wide sign of /tmp/tmp.* there thanks to mount -n which hides it. But it can be seen process-private here:

# grep /tmp /proc/mounts
tmpfs /tmp/tmp.75xu4BfiCw tmpfs rw,relatime,size=1024k 0 0
# cd $secret_dir
# ls -lFa
total 36
drwxrwxrwt  2 root root    40 2011-11-03 22:10 ./
drwxrwxrwt 21 root root 36864 2011-11-03 22:10 ../
# touch play-file
# mkdir play-dir
# ls -lFa
total 36
drwxrwxrwt  3 root root    80 2011-11-03 22:10 ./
drwxrwxrwt 21 root root 36864 2011-11-03 22:10 ../
drwxr-xr-x  2 root root    40 2011-11-03 22:10 play-dir/
-rw-r--r--  1 root root     0 2011-11-03 22:10 play-file

Afterward, in another terminal, and thus a separate process with no visibility into the above-shown terminal process’s private mount points:

% su -
Password:
# grep /tmp /proc/mounts
# cd /tmp/tmp.75xu4BfiCw
# ls -lFa
total 40
drwx------  2 root root  4096 2011-11-03 22:10 ./
drwxrwxrwt 21 root root 36864 2011-11-03 22:18 ../

It’s all secret!

Use cases

This feature makes it possible for us to create a private temporary filesystem that even other root-owned processes cannot see or browse through, raising the bar considerably for a naive attacker to get access to sensitive files or even see that they exist, at least when they’re not currently open and visible to e.g. lsof.

Of course a sophisticated attacker would presumably have a tool to troll through kernel memory looking for what they need. As always, assume that a sophisticated attacker who has access to the machine will sooner or later have anything they really want from it. But we’d might as well make it a challenge.

Another possible use of this feature is to have a process unmount a filesystem privately, perhaps to reduce the exposure of other files on a system to a running daemon if it is compromised.

/etc/mtab vs. /proc/mounts

Experimenting with this feature also drew my attention to differences in how popular Linux distributions expose mount points. There are actually traditionally two places that the list of mounts is stored on a Linux system.

First, the classic Unix /etc/mtab, which is in essence a materialized view. It is the reason that on the Ubuntu 11.04 example above we see the private mount point everywhere on the system, but it reported different disk sizes. The existence of the mount point was global in /etc/mtab but the sizes are determined dynamically and differ based on process’s view into the mount points themselves. The `mount -n` option tells mount to not put the new mount point into /etc/mtab. And this is what the df(1) command refers to. How repulsive that a file in the normally read-only /etc is written to so nonchalantly!

Second, the Linux-specific /proc/mounts, which is real-time, exact, and accurate, and can appear differently to each process. The mount invocation can’t hide anything from /proc/mounts. This is what you would think is the only place to look for mounts, but /etc/mtab is still used some places.

Ubuntu 11.04 still has both, with a separate /etc/mtab. Fedora 16 has done away with /etc/mtab entirely and made it merely a symlink to /proc/mounts, which makes sense, but that is a newer convention and leads to the surprising difference here.

Linux distributions and unshare

The unshare userland command in util-linux(-ng) comes with RHEL 6, Debian 6, Ubuntu 11.04, and Fedora 16, but not on the very common RHEL 5 or CentOS 5. Because we needed it on RHEL 5, I made a simple package that contains only the unshare(1) command and peacefully coexists with the older stock RHEL 5 util-linux. It’s called util-linux-unshare and here are the RPM downloads for RHEL 5:

I hope you’ve found this as interesting as I did!

Further reading

Our SoftLayer API tools

We do a lot of our hosting at SoftLayer, which seems to be one of the hosts with the most servers in the world -- they claim to have over 100,000 servers as of last month. More important for us than sheer size are many other fine attributes that SoftLayer has, in no particular order:

  • a strong track record of reliability
  • responsive support
  • datacenters around the U.S. and some in Europe and Asia
  • solid power backup
  • well-connected redundant networks with multiple 10 Gbps uplinks
  • gigabit Ethernet pipes all the way to the Internet
  • first-class IPv6 support
  • an internal private network with no data transfer charge
  • Red Hat Enterprise Linux offered at no extra charge
  • diverse dedicated server offerings at many price & performance points
  • some disk partitioning options (though more flexibility here would be nice, especially with LVM for the /boot and / filesystems)
  • fully automated provisioning, without salesman & quote hassles for standard offerings
  • 3000 GB data transfer per month included standard with most servers
  • month-to-month contracts
  • reasonable prices (though we can of course always use lower prices, we'll take quality over cheapness for most of our hosting needs!)
  • no arbitrary port blocks (some other providers rate-limit incoming TCP connections on port 22 to slow down ssh dictionary attacks, while others forbid IRC, etc.)
  • a web service API for monitoring and controlling many aspects of our account via REST/JSON or SOAP

(No, they're not paying me for writing this! But they really have nice offerings.)

It is this last item, the SoftLayer API, that I want to elaborate on here.

The SoftLayer Development Network features API information and documentation and once you have an API account set up in the management website (quick and easy to do), you can start automating all sorts of tasks, from provisioning new hosts, monitoring your upcoming invoice or other accounting information, and much more.

I've released as open source two scripts we use: One is for managing secondary DNS domains in SoftLayer's DNS servers, from a primary name server running BIND 9. The other is a Nagios check script for monitoring monthly data transfer used and alerting when over a set threshold or over the monthly allotment.

See the GitHub repository of endpoint-softlayer-api if they would be useful to you, or to use as a starting point to interface with other SoftLayer APIs.

MySQL replication monitoring on Ubuntu 10.04 with Nagios and NRPE

If you're using MySQL replication, then you're probably counting on it for some fairly important need. Monitoring via something like Nagios is generally considered a best practice. This article assumes you've already got your Nagios server setup and your intention is to add a Ubuntu 10.04 NRPE client. This article also assumes the Ubuntu 10.04 NRPE client is your MySQL replication master, not the slave. The OS of the slave does not matter.

Getting the Nagios NRPE client setup on Ubuntu 10.04

At first it wasn't clear what packages would be appropriate packages to install. I was initially misled by the naming of the nrpe package, but I found the correct packages to be:

sudo apt-get install nagios-nrpe-server nagios-plugins

The NRPE configuration is stored in /etc/nagios/nrpe.cfg, while the plugins are installed in /usr/lib/nagios/plugins/ (or lib64). The installation of this package will also create a user nagios which does not have login permissions. After the packages are installed the first step is to make sure that /etc/nagios/nrpe.cfg has some basic configuration.

Make sure you note the server port (defaults to 5666) and open it on any firewalls you have running. (I got hung up because I forgot I have both a software and hardware firewall running!) Also make sure the server_address directive is commented out; you wouldn't want to only listen locally in this situation. I recommend limiting incoming hosts by using your firewall of choice.

Choosing what NRPE commands you want to support

Further down in the configuration, you'll see lines like command[check_users]=/usr/lib/nagios/plugins/check_users -w 5 -c 10. These are the commands you plan to offer the Nagios server to monitor. Review the contents of /usr/lib/nagios/plugins/ to see what's available and feel free to add what you feel is appropriate. Well designed plugins should give you a usage if you execute them from the command line. Otherwise, you may need to open your favorite editor and dig in!

After verifying you've got your NRPE configuration completed and made sure to open the appropriate ports on your firewall(s), let's restart the NRPE service:

service nagios-nrpe-server restart

This would also be an appropriate time to confirm that the nagios-nrpe-server service is configured to start on boot. I prefer the chkconfig package to help with this task, so if you don't already have it installed:

sudo apt-get install chkconfig
chkconfig | grep nrpe

# You should see...
nagios-nrpe-server     on

# If you don't...
chkconfig nagios-nrpe-server on

Pre flight check - running check_nrpe

Before going any further, log into your Nagios server and run check_nrpe and make sure you can execute at least one of the commands you chose to support in nrpe.cfg. This way, if there are any issues, it is obvious now, while we've not started modifying your Nagios server configuration. The location of your check_nrpe binary may vary, but the syntax is the same:

check_nrpe -H host_of_new_nrpe_client -c command_name

If your command output something useful and expected, your on the right track. A common error you might see: Connection refused by host. Here's a quick checklist:

  • Did you start the nagios-nrpe-server service?
  • Run netstat -lunt on the NRPE client to make sure the service is listening on the right address and ports.
  • Did you open the appropriate ports on all your firewall(s)?
  • Is there NAT translation which needs configuration?

Adding the check_mysql_replication plugin

There is a lot of noise out there on Google for Nagios plugins which offer MySQL replication monitoring. I wrote the following one using ideas pulled from several existing plugins. It is designed to run on the MySQL master server, check the master's log position and then compare it to the slave's log position. If there is a difference in position, the alert is considered Critical. Additionally, it checks the slave's reported status, and if it is not "Waiting for master to send event", the alert is also considered critical. You can find the source for the plugin at my Github account under the project check_mysql_replication. Pull that source down into your plugins directory (/usr/lib/nagios/plugins/ (or lib64)) and make sure the permissions match the other plugins.

With the plugin now in place, add a command to your nrpe.cfg.

command[check_mysql_replication]=sudo /usr/lib/nagios/plugins/check_mysql_replication.sh -H 

At this point you may be saying, WAIT! How will the user running this command (nagios) have login credentials to the MySQL server? Thankfully we can create a home directory for that nagios user, and add a .my.cnf configuration with the appropriate credentials.

usermod -d /home/nagios nagios #set home directory
mkdir /home/nagios
chmod 755 /home/nagios
chown nagios:nagios /home/nagios

# create /home/nagios/.my.cnf with your preferred editor with the following:
[client]
user=example_replication_username
password=replication_password

chmod 600 /home/nagios/.my.cnf
chown nagios:nagios /home/nagios/.my.cnf

This would again be an appropriate place to run a pre flight check and run the check_nrpe from your Nagios server to make sure this configuration works as expected. But first we need to add this command to the sudoer's file.

nagios ALL= NOPASSWD: /usr/lib/nagios/plugins/check_mysql_replication.sh

Wrapping Up

At this point, you should run another check_nrpe command from your server and see the replication monitoring report. If not, go back and check these steps carefully. There are lots of gotchas and permissions and file ownership are easily overlooked. With this in place, just add the NRPE client using the existing templates you have for your Nagios servers and make sure the monitoring is reporting as expected.

Importing Data with RailsAdmin

Update #1: Read an update to this functionality here.

Update #2: This article was written in January of 2012, and the code related to the RailsAdmin actions no longer applies to the current release. Please make sure to read the RailsAdmin documentation regarding current action implementation.

I've blogged about RailsAdmin a few times lately. I've now used it for several projects, and have included it as a based for the Admin interface my recent released Ruby on Rails Ecommerce Engine (Piggybak). One thing that I found lacking in RailsAdmin is the ability to import data. However, it has come up in the RailsAdmin Google Group and it may be examined in the future. One problem with developing import functionality is that it's tightly coupled to the data and application logic, so building out generic import functionality may need more thought to allow for elegant extensibility.

For a recent ecommerce project using RailsAdmin and Piggybak, I was required to build out import functionality. The client preferred this method to writing a simple migration to migrate their data from a legacy app to the new app, because this import functionality would be reusable in the future. Here are the steps that I went through to add Import functionality:

#1: Create Controller

class CustomAdminController < RailsAdmin::MainController
  def import
    # TODO
  end
end

First, I created a custom admin controller for my application in the app/controllers/ directory that inherits from RailsAdmin::MainController. This RailsAdmin controller has several before filters to set the required RailsAdmin variables, and defines the correct layout.

#2: Add import route

match "/admin/:model_name/import" => "custom_admin#import" , :as => "import", :via => [:get, :post]
mount RailsAdmin::Engine => '/admin', :as => 'rails_admin'

In my routes file, I introduced a new named route for import to point to the new custom controller. This action will be a get or a post.

#3: Override Rails Admin View

Next, I copied over the RailsAdmin app/views/rails_admin/_model_links.html.haml view to my application to override RailsAdmin's view. I made the following addition to this file:

...
- can_import = authorized? :import, abstract_model

...
%li{:class => (params[:action] == 'import' && 'active')}= link_to "Import", main_app.import_path(model_name) if can_import

With this logic, the Import tab shows only if the user has import access on the model. Note that the named route for the import must be prefixed with "main_app.", because it belongs to the main application and not RailsAdmin.

#4: CanCan Settings

My application uses CanCan with RailsAdmin, so I leveraged CanCan to control which models are importable. The CanCan Ability class (app/models/ability.rb) was updated to contain the following, to allow exclude import on all models, and then allow import on several specific models.

if user && user.is_admin?
  cannot :import, :all
  can :import, [Book, SomeModel1, SomeModel2, SomeModel3]
end

I now see an Import tab in the admin:

#5: Create View

Next, I created a view for displaying the import form. Here's a generic example to display the set of fields that can be imported, and the form:

<h1>Import</h1>
<h2>Fields</h2>
<ul>
    <% @abstract_model::IMPORT_FIELDS.each do |attr| -%>
    <li><%= attr %></li>
    <% end -%>
</ul>

<%= form_tag "/admin/#{@abstract_model.to_param}/import", :multipart => true do |f| -%>
    <%= file_field_tag :file %>
    <%= submit_tag "Upload", :disable_with => "Uploading..." %>
<% end -%>

This will look something like this:

#6: Import Functionality

Finally, the code for the import looks something like this:

def import
  if request.post?
    response = { :errors => [], :success => [] }
    file = CSV.new(params[:file].tempfile)

    # Build map of attributes based on first row
    map {}
    file.readline.each_with_index { |key, i| map[key.to_sym] = i }

    file.each do |row|
      # Build hash of attributes
      new_attrs = @abstract_model.model::IMPORT_FIELDS.inject({}) { |hash, a| hash[a] = row[map[a]] if map[a] }

      # Instantiate object
      object = @abstract_model.model.new(new_attrs)

      # Additional special stuff here

      # Save
      if object.save
        response[:success] << "Created: #{object.title}"
      else
        response[:error] << "Failed to create: #{object.title}. Errors: #{object.errors.full_messages.join(', ')}."
      end
    end
  end
end

Note that a hash of keys and locations is created to map keys to the columns in the imported file. This allows for flexibility in column ordering of imported files. Later, I'd like to to re-examine the CSV documentation to identify if there is a more elegant way to handle this.

#7: View updates to show errors

Finally, I update my view to show both success and error messages, which looks sorta like this in the view:

Conclusion and Discussion

It was pretty straightforward to get this figured out. The only disadvantage I see to this method is that overriding the rails_admin view requires recopying or manual updates to the view over during upgrades of the gem. For example, if any part of the rails_admin view has changes, those changes must also be applied to the custom view. Everything else should be smooth sailing :)

In reality, my application has several additional complexities, which make it less suitable for generic application:

  • Several of the models include attached files via paperclip. Using open-uri, these files are retrieved and added to the objects.
  • Several of the models include relationships to existing models. The import functionality requires lookup of these associated models (e.g. an imported book belongs_to an existing author), and reports and error if the associated objects can not be found.
  • Several of the models require creation of a special nested object. This was model specific.
  • Because of this model specific behavior, the import method is moved out of the controller into model-specific class methods. For example, CompactDisc.import is different from Book.import which is different from Track.import. Pulling the import into a class method also makes for a skinnier controller here.

Read more about End Point's Ruby on Rails development and consulting services or contact us to help you out with a Rails project today!

Using Disqus and Ruby on Rails

Recently, I posted about how to import comments from a Ruby on Rails app to Disqus. This is a follow up to that post where I outline the implementation of Disqus in a Ruby on Rails site. Disqus provides what it calls Universal Code which can be added to any site. This universal code is just JavaScript, which asynchronously loads the Disqus thread based on one of two unique identifiers Disqus uses.

Disqus in a development environment

Before we get started, I'd recommend that you have two Disqus "sites"; one for development and one for production. This will allow you to see real content and experiment with how things will really behave once you're in production. Ideally, your development server would be publicly accessible to allow you to fully use the Disqus moderation interface, but it isn't required. Simply register another Disqus site, and make sure that you have your shortname configured by environment. Feel free to use whatever method you prefer for defining these kinds of application preferences. If you're looking for an easy way, considering checking out my article on Working with Constants in Ruby. It might look something like this:

# app/models/article.rb

DISQUS_SHORTNAME = Rails.env == "development" ? "dev_shortname".freeze : "production_shortname".freeze

Disqus Identifiers

Each time you load the universal code, you need to specify a few configuration variables so that the correct thread is loaded:

  • disqus_shortname: tells Disqus which website account (called a forum on Disqus) this system belongs to.
  • disqus_identifier: tells Disqus how to uniquely identify the current page.
  • disqus_url: tells Disqus the location of the page for permalinking purposes.
Let's create a Rails partial to set up these variables for us, so we can easily call up the appropriate comment thread.
# app/views/disqus/_thread.html.erb
# assumes you've passed in the local variable 'article' into this partial
# from http://docs.disqus.com/developers/universal/

<div id="disqus_thread"></div>
<script type="text/javascript">

    var disqus_shortname = '<%= Article::DISQUS_SHORTNAME %>';
    var disqus_identifier = '<%= article.id %>';
    var disqus_url = '<%= url_for(article, :only_path => false) %>';

    /* * * DON'T EDIT BELOW THIS LINE * * */
    (function() {
        var dsq = document.createElement('script'); dsq.type = 'text/javascript'; dsq.async = true;
        dsq.src = 'http://' + disqus_shortname + '.disqus.com/embed.js';
        (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
    })();
</script>

The above code will populate the div#disqus_thread with the correct content based on your disqus_identifier. By setting up a single partial that will always render your threads, it becomes very easy to adjust this code if needed.

Disqus Identifier Gotcha

We found during our testing a surprising and unexpected behavior in how Disqus associates a thread to a URL. In our application, the landing page was designed to show the newest article as well as the Disqus comments thread. We found that once a new article was posted, the comments from the previous article were still shown! It seems Disqus ignored the unique disqus_identifier we had specified and instead associated the thread with the landing page URL. In our case, a simple routing change allowed us to forward the user to the unique URL for that content and thread. In your case, there may not be such an easy work around, so be certain you include both the disqus_identifier and disqus_url JavaScript configuration variables above to minimize the assumptions Disqus will make. When at all possible, always use unique URLs for displaying Disqus comments.

Comment Counters

Often an index page will want to display a count of how many comments are in a particular thread. Disqus uses the same asynchronous approach to loading comment counts. Comment counts are shown by adding code such as the following where you want to display your count:

# HTML
<a href="http://example.com/article1.html#disqus_thread" 
   data-disqus-identifier="<%=@article.id%>">
This will be replaced by the comment count
</a>

# Rails helper
<%= link_to "This will be replaced by the comment count", 
    article_path(@article, :anchor => "disqus_thread"), 
    :"data-disqus-identifer" => @article.id %>

At first this seemed strange, but it is the exact same pattern used to display the thread. It would likely be best to remove the link text so nothing is shown until the comment count is loaded, but I felt for my example, having some meaning to the test would help understanding. Additionally, you'll need to add the following JavaScript to your page.

# app/view/disqus/_comment_count_javascript.html.erb
# from http://docs.disqus.com/developers/universal/
# add once per page, just above </body>

<script type="text/javascript">
   
    var disqus_shortname = '<%= Article::DISQUS_SHORTNAME %>';

    /* * * DON'T EDIT BELOW THIS LINE * * */
    (function () {
        var s = document.createElement('script'); s.async = true;
        s.type = 'text/javascript';
        s.src = 'http://' + disqus_shortname + '.disqus.com/count.js';
        (document.getElementsByTagName('HEAD')[0] || document.getElementsByTagName('BODY')[0]).appendChild(s);
    }());
</script>

Disqus recommends adding it just before the closing </body> tag. You only need to add this code ONCE per page, even if you're planning on showing multiple comment counts on a page. You will need this code on any page with a comment count, so I do recommend putting it in a partial. If you wanted, you could even include it in a layout.

Styling Comment Counts

Disqus provides extensive CSS documentation for its threads, but NONE for its comment counters. In our application, we had some very particular style requirements for these comment counts. I found that in Settings > Appearance, I could add HTML tags around the output of the comments.

This allowed me to style my comments as needed, although these fields are pretty small, so make sure to compress your HTML as much as possible.

Interchange loops using DBI Slice

One day I was reading through the documentation on search.cpan.org for the DBI module and ran across an attribute that you can use with selectall_arrayref() that creates the proper data structure to be used with Interchange's object.mv_results loop attribute. The attribute is called Slice which causes selectall_arrayref() to return an array of hashrefs instead of an array of arrays. To use this you have to be working in global Perl modules as Safe.pm will not let you use the selectall_arrayref() method.

An example of what you could use this for is an easy way to generate a list of items in the same category. Inside the module, you would do like this:

my $results = $dbh->selectall_arrayref(
  q{
    SELECT
      sku,
      description,
      price,
      thumb,
      category, 
      prod_group
    FROM
      products
    WHERE
      category = ?},
  { Slice => {} }, 
  $category
);
$::Tag->tmpn("product_list", $results);

In the actual HTML page, you would do this:

<table cellpadding=0 cellspacing=2 border=1>
<tr>
  <th>Image</th>
  <th>Description</th>
  <th>Product Group</th>
  <th>Category</th>
  <th>Price</th>
</tr>
[loop object.mv_results=`$Scratch->{product_list}` prefix=plist]
[list]
<tr>
  <td><a href="/cgi-bin/vlink/[plist-param sku].html"><img src="[plist-param thumb]"></a></td>
  <td>[plist-param description]</td>
  <td>[plist-param prod_group]</td>
  <td>[plist-param category]</td>
  <td>[plist-param price]</td>
</tr>
[/list]
[/loop]
</table>

We normally use this when writing ActionMaps and using some template as our setting for mv_nextpage.

ActiveRecord Callbacks for Order Processing in Ecommerce Applications

As I recently blogged about, I introduced a new Ruby on Rails Ecommerce Engine. The gem relies on RailsAdmin, a Ruby on Rails engine that provides a nice interface for managing data. Because the RailsAdmin gem drives order creation on the backend in the context of a standard but configurable CRUD interface, and because I didn't want to hack at the RailsAdmin controllers, much of the order processing logic leverages ActiveRecord callbacks for processing. In this blog article, I'll cover the process that happens when an order is saved.

Order Data Model

The first thing to note is the data model and the use of nested attributes. Here's how the order model relates to its associated models:

class Order < ActiveRecord::Base
  has_many :line_items, :inverse_of => :order
  has_many :payments, :inverse_of => :order
  has_many :shipments, :inverse_of => :order
  has_many :credits, :inverse_of => :order

  belongs_to :billing_address, :class_name => "Piggybak::Address"
  belongs_to :shipping_address, :class_name => "Piggybak::Address"
  belongs_to :user
  
  accepts_nested_attributes_for :billing_address, :allow_destroy => true
  accepts_nested_attributes_for :shipping_address, :allow_destroy => true
  accepts_nested_attributes_for :shipments, :allow_destroy => true
  accepts_nested_attributes_for :line_items, :allow_destroy => true
  accepts_nested_attributes_for :payments
end

An order has many line items, payments, shipments and credits. It belongs to [one] billing and [one] shipping address. It can accept nested attributes for the billing address, shipping address, multiple shipments, line items, and payments. It cannot destroy payments (they can only be marked as refunded). In terms of using ActiveRecord callbacks for an order save, this means that all the nested attributes will also be validated during the save. Validation fails if any nested model data is not valid.

Step #1: user enters data, and clicks submit

Step #2: before_validation

Using a before_validation ActiveRecord callback, a few things happen on the order:

  • Some order defaults are set
  • The order total is reset
  • The order total due is reset

Step #3: validation

This happens without a callback. This method will execute validation on both order attributes (email, phone) and nested element attributes (address fields, shipment information, payment information, line_item information).

Payments have a special validation step here. A custom validation method on the payment attributes is performed to confirm validity of the credit card:

validates_each :payment_method_id do |record, attr, value|
  if record.new_record?
    credit_card = ActiveMerchant::Billing::CreditCard.new(record.credit_card)
    
    if !credit_card.valid?
      credit_card.errors.each do |key, value|
        if value.any? && !["first_name", "last_name", "type"].include?(key)
          record.errors.add key, value
        end
      end
    end
  end
end

This bit of code uses ActiveMerchant's functionality to avoid reproducing business logic for credit card validation. The errors are added on the payment attributes (e.g. card_number, verification_code, expiration date) and presented to the user.

Step #4: after_validation

Next, the after_validation callback is used to update totals. It does a few things here:

  • Calculates shipping costs for new shipments only.
  • Calculates tax charge on the order.
  • Subtracts credits on the order, if they exist.
  • Calculates total_due, to be used by payment

While these calculations could be performed before_validation, after_validation is a bit more performance-friendly since tax and shipping calculations could in theory be expensive (e.g. shipping calculations could require calling an external API for real-time shipping lookup). These calculations are saved until after the order is confirmed to be valid.

Step #5: before_save part 1

Next, a before_save callback handles payment (credit card) processing. This must happen after validation has passed, and it can not happen after the order has saved because the user must be notified if it fails. If any before_save method returns false, the entire transaction fails. So in this case, after all validation has passed, and before the order saves, the payment must process successfully.

Examples of failures here include:

  • Credit card transaction denied for a number of reasons
  • Payment gateway down
  • Payment gateway API information incorrect

Step #6: before_save part 2

After the payment processes, another before_save method is called to update the status of the order based on the totals paid. I initially tried placing this in an after_save method, but you tend to experience infinite loops if you try to save inside and after_save callback :)

Step #7: Save

Finally, if everything's gone through, the order is saved.

Summary

As I mentioned above, the RailsAdmin controllers were not extended or overridden to handle backroom order processing. All of the order processing is represented in the Order model in these active record callbacks. This also allows for the frontend order processing controller to be fairly lightweight, which is a standard practice for writing clean MVC code.

Check out the full list of ActiveRecord callbacks here. And check out the Order model for Piggybak here.

Take a snapshot in Cucumber and sync it with Dropbox!

In a previous post I talked about running cucumber using capybara-webkit. In a recent project using this setup I noticed that I couldn't use capybara in connection with launchy to open up a page in the browser for debugging tests. The "save and open page" step is one that I used a lot when I was developing locally. But now that I'm developing on a server, I don't have any way save the page or open it for review.

The solution I found to this comes in two parts. First, create a "take a snapshot" cucumber step that drops a snapshot of the HTML and a PNG of the page in a temp directory. Second, add that temp directory to dropbox so that it gets synced to my desktop automatically when it is created.

Wait, seriously? Dropbox?

Yes, absolutely. Dropbox.

I often develop inside of my dropbox folder because A) all my code is automatically backed up, even with versions and B) because it's really simple to sync my code to other computers. I'll admit that one problem I had early on was that log files were using an awful amount of bandwidth getting copied up constantly, but I solved this by adding the log directory to an exclusions list. I'll show you how to do that below.

Step 1: Create a "take a snapshot" step.

The first thing we need to do is setup our take a snapshot step. For our app, it made the most sense to put this in web_steps.rb but you can add it to any of your step_definitions files. The step looks like this:

Then /take a snapshot/ do
  # save a html snapshot
  html_snapshot = save_page
  puts "Snapshot saved: \n#{html_snapshot}"

  # save a png snapshot
  png_snapshot = html_snapshot.gsub(/html$/, "png")
  page.driver.render png_snapshot
  puts "Snapshot saved: \n#{png_snapshot}"

end

The first line there is fairly simple. Capybara provides a save_page method by default which is going to save the page to tmp/capybara off the root of your app. The file will look something like this: capybara-20111228210921591550991.html. You can see the source code on the rdoc page for more information on how this works. If you want to customize the file name, you can do that by calling Capybara.save_page directly like this:

Capybara.save_page(body, 'my_custom_file.html')

This reveals what save_page is actually doing. Every cucumber step has available to it by default Capybara::Session. The source html of the current page is stored in Capybara::Session#body. The save_page method is just writing the source html to a file.

The next block of code saves the page as a PNG file. This comes from the capybara-webkit driver so this will only work if you are using that driver specifically. (You can explore the code on github to get more information, but basically it's calling the "Render" command on webkit and then storing the image as a PNG.)

All I'm doing here is changing out the html file extension for png so that the files will be easy to find. You can also pass width and height options if you'd like with a hash {:width => 1000, :height => 10}.

Step 2: Setup Dropbox.

I didn't know this until recently but you can run dropbox on a linux server without a UI. It is available as a binary for Ubuntu (.deb), Fedora (.rpm), Debian (.deb). You can also compile from source if you'd like. Since I'm doing my development on a server, however, I wanted a little bit more of an isolated installation and luckily Dropbox has the answer for me. It's called their command line installation and it works great. Here are the instructions from the web site:

For 32 bit:

cd ~ && wget -O - http://www.dropbox.com/download?plat=lnx.x86 | tar xzf -

or 64 bit:

cd ~ && wget -O - http://www.dropbox.com/download?plat=lnx.x86_64 | tar xzf -

You'll also need a small python script for working with the daemon. You can download it from the web site or from here.

The dropbox cli will walk you through a simple authentication process and then start downloading your dropbox folder in your ~/Dropbox directory when you run dropbox.py start for the first time. If your dropbox folder is like mine, this is going to download way more stuff than you'd probably want on your server so you'll need to add some exceptions. I created a folder in Dropbox for my screenshots called ~/Dropbox/cuke_snapshots and then excluded everything else. Here's how I did it with the dropbox.py file (I renamed dropbox.py to just dropbox for ease and clarity. It's also helpful to put it in a directory that's in your PATH):

cd ~/Dropbox
dropbox exlcude add Public Photos

That adds the Public and Photos folders to the exclusion list and Dropbox deletes them from the system for you. The nice thing is that you can continue to add folder names to the end of that command so you can get rid of stuff really quick. There are a bunch of options using the dropbox cli that make working with dropbox on the server very simple and flexible.

status       get current status of the dropboxd
help         provide help
puburl       get public url of a file in your dropbox
stop         stop dropboxd
running      return whether dropbox is running
start        start dropboxd
filestatus   get current sync status of one or more files
ls           list directory contents with current sync status
autostart    automatically start dropbox at login
exclude      ignores/excludes a directory from syncing
lansync      enables or disables LAN sync

The last step is to create a symbolic link from your app into your dropbox folder. I did this with the following command:

mkdir -p ~/Dropbox/cuke_snapshots/my_app/capybara
cd ~/rails_apps/my_app/tmp
rm -rf capybara   (if it already exists)
ln -s ~/Dropbox/cuke_snapshots/my_app/capybara

Of course there are many ways you could set this up, but this will get the job done.

Using "take a snapshot"

Once you have everything setup, all you need to do is call the cucumber step from within your scenarios. Here's a contrived example:

@javascript   # may not be needed if everything is using webkit-capybara
Scenario: A shopper wants to checkout
  When I go to the address step
  And I fill in the address information
  And I follow "Next"
  Then I should be on the delivery step
  And take a snapshot

When the scenario runs, it will drop the html and png file in your dropbox directory which will immediately be synced to your local machine. In my experience, by the time I open up the folder on my local machine, the file is there ready for inspection.

Introducing Piggybak: A Mountable Ruby on Rails Ecommerce Engine

Here at End Point, we work with a variety of open source solutions, both full-fledged ecommerce applications and smaller modular tools. In our work with open source ecommerce applications, we spend a significant amount of time building out custom features, whether that means developing custom shipping calculations, product personalization, accounting integration or custom inventory management.

There are advantages to working with a full-featured ecommerce platform. If you are starting from scratch, a full-featured platform can be a tool to quickly get product out the door and money in your pocket. But to do this, you must accept the assumptions that the application makes. And most generic, monolithic ecommerce platforms are created to satisfy many users.

Working with a large monolithic ecommerce platform has disadvantages, too:

  • Sometimes over-generic-izing a platform to satisfy the needs of many comes at the cost of code complexity, performance, or difficulty in customization.
  • Occasionally, the marketing of an ecommerce solution overpromises and underdelivers, leaving users with unrealistic expectations on what they get out of the box.
  • Customization on any of these platforms is not always simple, elegant, or cheap. And it may prevent easy maintenance or upgrades in the future. For example, building out customization A, B, and C on the platform today may make it difficult to upgrade later to use the added features X, Y, and Z.
  • Users of a platform rely heavily on the maintainers of the project. This can mean that users may not be able to keep up with a fast-moving platform, or even that a slow-moving platform doesn't stay up to date with ecommerce trends.

Full-featured ecommerce platforms do make sense for a some clients. Hosted ecommerce solutions (Shopify, Volusion) even make sense for a lot of people too. Here at End Point, we try to be realistic about the pros and cons of building off of an ecommerce platform, but balance that with available tools to develop something for our clients efficiently and pragmatically.

Enter Rails

I like Ruby, and Ruby on Rails a lot. With bundler, Rails has gotten particularly smart regarding managing dependencies. The Rails community also has a lot of great tools (gems) that can supplement an application. And a great MVC foundation plus include an improved way to manage assets (CSS, JavaScript, images) to help with performance and code organization. A few really great gems I've worked with are:

The other thing that's really cool about Ruby on Rails is Rails Engines. Engines allow you to wrap Rails applications into modular elements that can be and easily shared across applications. And the parent application has control over the mount point of an Engine, meaning that a parent application can mount another engine at "/blog", "/shop", "/foo", "/bar" without affecting the Engine's behavior.

I'm not trying to be a Rails fangirl here (I use WordPress for my personal website and still sometimes think Perl is the best tool for a job) :) But the ability to include and manage modular elements (gems, gems that are Engines) is great for building applications based on a few core modular elements.

My Story

Several weeks ago, I started putting together a prototype for a client for a Ruby on Rails ecommerce site. Their site has fairly custom needs with a complex data relationships (data model), complex search requirements, but relatively simple cart and checkout needs. I was certain that existing open source Ruby on Rails ecommerce platforms would require a significant amount of customization and would be a hassle to maintain. I also recently spent a good deal of time working with RailsAdmin on a non-ecommerce Rails project (blogged here).

Rather than try to fight against an existing framework, I developed a prototype website using RailsAdmin. My prototype quickly developed into an ecommerce Ruby on Rails Engine, which offers basic shopping cart functionality, but it doesn't try to solve every problem for everyone. Below are some details about this gem as a Rails Engine, called Piggybak:

Non-Tech Details

The Piggybak gem includes the following features:

  • Basic shopping cart functionality for adding, updating, or removing items in a cart
  • One page checkout, with AJAX for shipping and tax calculation
  • Checkout for registered users or guests
  • Configurable payment methods (via ActiveMerchant)
  • Configurable shipping and tax calculators
  • Admin interface for entering and managing orders

Here are a few screenshots:

Demo homepage. The primary Rails application includes all the code for defining product navigation. In this case, it displays featured products and product categories.



One page checkout



Admin dashboard: Note that distinction between the primary application and the Piggybak gem regarding left navigation.



Any item in the application can become sellable. This nested form displays in the admin for the sellable items.

The cart form added to the image page is driven by the Piggybak gem, but the rest of the page content is driven by the primary application. The gem doesn't make any decisions about what will be displayed on a standard product page. It only helps with generating the form to add something to the shopping cart.



The admin interface for editing an order. An order has one billing and shipping address. It can have many line items, shipments, and payments. All of these nested elements are controlled directly on the order edit page.

Tech Details

The Piggybak gem has the following dependencies:

  • Ruby on Rails 3.1+
  • RailsAdmin
  • Devise (which is a dependency of RailsAdmin)
  • CanCan (the parent application takes responsibility for defining authorization rules

This is certainly not a short list of dependencies, but if you are developing a new application on Rails, you are likely already using a couple of these tools. And I highly recommend using RailsAdmin :)

To get an idea of how the mountable solution works, these are the installation steps:

  • Add to Gemfile and install with bundler
  • Rake task for copying migrations and run migrations
  • Mount engine in the parent's appplication config/routes.rb

And then the following integration points are:

  • acts_as_variant is added inside any model in your application to become sellable. This affectively assigns a relationship between your model(s) and the variants table. A variant belongs_to an item through a polymorphic relationship, and a model has_one variant. The variants table has information for it's sku, price, quantity on hand, and shopping cart display name.
  • acts_as_orderer is added inside inside the user model in your application that owns orders (probably User).
  • <%= cart_form(@item) %> is a helper method that displays an add to cart form
  • <%= cart_link %> is a helper method that displays a link to the cart with the current number of items and total
  • <%= orders_link("Order History") %> is a helper method which links to a users orders page

Summary

Here at End Point, we've had a few internal discussions about building ecommerce solutions based on more modular components to address the disadvantages we've seen in working with large monolithic ecommerce platforms. Rails provides a strong base for stitching modular components together easily.


© Steph Skardal

My goal with this tool was to write a modular cart and checkout component that takes advantage of RailsAdmin's DSL to provide a nice Admin interface for nested forms for models that already existing in your application. It wasn't created to solve every ecommerce problem, or world peace. As it's name indicates, this gem piggybak's off of an existing Rails application.

It leaves the decision of all product finding methods and product navigation up to the developer, which means it might be great for:

  • A deal of the day site that controls items offered daily. Deal of the day sites often don't fit the standard mold of ecommerce so ecommerce platforms don't always suit them well. And they may need significant performance customization, which is not always a feature included in a generic ecommerce platform.
  • An ecommerce site with complex demands for search, where existing ecommerce solutions aren't easily integrated with those search solutions. A developer may build their own custom search solution and mount piggybak for handling cart and checkout only.
  • An ecommerce site with a complex data model, where multiple types of items with varied navigation. This gem gives the functionality to turn any item on a site into a sellable item (or variant).

Demo and more documentation is forthcoming. View the repository here. Contributions (see TODO) appreciated. As I work through this project for the client in the next few weeks, I'm sure that there'll be a few minor bugs to work out.



Some great press for College District

College District has been getting some positive press lately, the most recent being a Forbes article which talks about the success they have been seeing in the last few years.

College District is a company that sells collegiate merchandise to fans. They got their start focusing on the LSU Tigers at TigerDistrict.com and have branched out to teams such as the Oregon Ducks and Alabama Roll Tide.

We've been working with Jared Loftus @ College District for more then four and a half years. College District is running on a heavily modified Interchange system with some cool Postgres tricks. The system can support a nearly unlimited number of sites, running on 2 catalogs (1 for the admin, 1 for the front end) and 1 database. The key to the system is different schemas, fronted by views, that hide and expose records based on the database user that is connected. The great thing about this system is that Jared can choose to launch a new store within a day and be ready for sales, something he has taken advantage of in the past when a team is on fire and he sees an opportunity he can't pass up.

We are currently preparing for a re-launch of the College District site that will focus on crowd-sourced designs. Artists and fans will submit their designs, have them voted on, some will be chosen to be sold and the folks that have their designs chosen will get paid for their efforts. The goal here is to grow a community that guides what College District and the individual school sites ultimately sell.

With College District's quick growth we've also been helping them improve their order fulfillment process. This includes streamlining how orders are picked, packed and shipped. The introduction of bar code scanners will help with the accuracy and speed of the process.

We get a kick out of seeing our clients succeed, especially those that come to us with a clear vision and a good attitude, and then put the hard work in to make it happen. It's an exciting year ahead for College District and we'll be right there supporting them on the journey.

Ruby on Rails: Attributes Management through Rights

I've written a couple times about a large Rails project that a few End Pointers have been working on. The system has a fairly robust system for managing rights and accessibility. The basic data model for rights, users, groups, and roles is this:

In this data model, right_assignments belong to a single right, and belong to a subject, through a polymorphic relationship. The subject can be a Role, Group, or User. A User can have a role or belong to a group. This allows for the ability to assign a right to a user directly or through a group or role.

In our code, there is a simple method for grabbing a user's set of rights:

  class User < ActiveRecord::Base
    ...
    def all_rights
      rights = [self.rights +
                self.groups.collect { |g| g.allowed_rights } +
                self.roles.collect { |r| r.rights }]
      rights = rights.flatten.uniq.collect { |r| r.action }

      rights
    end 
    ...
  end

In the case of this data model, groups also have a boolean which specifies whether or not rights can be assigned to them. The allowed_rights method looks like this:

  class Group < ActiveRecord::Base
    ...
    def allowed_rights
      self.assignable_rights ? self.rights : []
    end 
    ...
  end

This additional layer of protection on the group rights is because groups represent a collection of users that have implied behavior attached to them (different from roles). A group may or may not have rights assigned only through a user with the ability to assign rights to groups.

The interesting part of this application is how the rights are used. In Rails, you can define the accessible attributes for an object on the fly. In our code, rights may translate to attributes that can be updated. For example, the following [extremely simplified] example demonstrates this:

  # controller
  def update # or create
    if @item.custom_save(params)
      # saved
    else
      # handle errors
    end
  end

  # Given an item, with attributes title, description, tags, origin
  class Item < ActiveRecord::Base
    ...
    def custom_save(parameters)
      self.accessible = self.attr_accessible_for(current_user)

      item.update_attributes(parameters)
    end

    def attr_accessible_for(user)
      attrs = [:title, :description]  # Defaults

      [:tags, :origin].each do |field|
        # check for if user can_set_(tags || origin)
        if current_user.all_rights.include?("can_set_#{field.to_s}")
          attrs << field
        end
      end
 
      attrs
    end
    ...
  end

The above block updates an existing item. The default accessible attributes are title and description, which means that anyone can set those values. Tags and origin can only be set if the user has the right that correspond to that attribute. Tags and origin will not be saved for a user without those rights, even if they are passed in the parameters. The obvious disadvantage with this method is that there's no exception handling when a user tries to submit parameters that they cannot set.

The method above is reused to define which fields are editable to the current user, with the code shown below. So in theory, a user submitting parameters that they can't edit would only be doing it through a [malicious] post not based on the view below.

  # controller
  def new
    @item = Item.new

    @accessible = attr_accessible_for(current_user)
  end

  # view
  <%= form_for @item do |f| %>
    <% [:title, :description, :tag, :origin].each do |field| %>
      <%= f.label field %>

      <% if @accessible.include?(field) %>
        <%= f.text_field %>
      <% else -%> 
        <%= @item.send(field) %>
      <% end -%>

    <% end -%>
  <% end -%>

The data model and rights management described in this article isn't novel, but applying it in this fashion is elegant and produces modular and reusable methods. The code shown here has been simplified for this blog post. In reality, there are a few additional complexities:

  • The application utilizes acl9 for access control, which is an additional layer of security that will prevent non-registered users from creating items, and will prohibit specific users from updating existing items.
  • The user's all_rights method utilizes Rails low-level caching, with appropriate cache invalidation when the user's rights, groups, or roles change. I've given a simple example of Rails low-level caching in this blog article.
  • The logic in attr_accessible_for is more complex and can be based on the object's values or parameters. For example, an item may have a boolean that indicates anyone can tag it. The attr_accessible_for method will incorporate additional logic to determine if the :tags field is editable.
  • The view handles different field types (checkbox, textarea, etc) and allows for overriding the field label.

Here are several articles related to the same large Ruby on Rails project, for your reading pleasure:

Or, read about our services and contact us to help with your next Rails project.

Automating removal of SSH key patterns

Every now and again, it becomes necessary to remove a user's SSH key from a system. At End Point, we'll often allow multiple developers into multiple user accounts, so cleaning up these keys can be cumbersome. I decided to write a shell script to brush up on those skills, make sure I completed my task comprehensively, and automate future work.

Initial Design and Dependencies

My plan for this script is to accept a single argument which would be used to search the system's authorized_keys files. If the pattern was found, it would offer you the opportunity to delete the line of the file on which the pattern was found.

I've always found mlocate to be very helpful; it makes finding files extremely fast and its usage is trivial. For this script, we'll use the output from locate to find all authorized_keys files in the system. Of course, we'll want to make sure that the mlocate.db has recently been updated. So let's show the user when the database was last updated and offer them a chance to update it.

mlocate_path="/var/lib/mlocate/mlocate.db"
if [ -r $mlocate_path ]
then
    echo -n "mlocate database last updated: "
    stat -c %y $mlocate_path
    echo -n "Do you want to update the locate database this script depends on? [y/n]: "
    read update_locate
    if [ "$update_locate" = "y" ]
    then
        echo "Updating locate database.  This may take a few minutes..."
        updatedb
        echo "Update complete."
    fi  
else
    echo "Cannot read the mlocate db path: $mlocate_path"
    exit 2
fi

First we define the path where we can find the mlocate database. Then we check to see if we can read that file. If we can't read the file, we let the user know and exit. If we can read the file, print the date and time it was last modified and offer the user a chance to update the database. While this is functional, it's pretty brittle. Let's make things a bit more flexible by letting locate tell us where its database is.

if
    mlocate_path=`locate -S`
then
    # locate -S command will output database path in following format:
    # Database /full/path/to/db: (more output)...
    mlocate_path=${mlocate_path%:*} #remove content after colon
    mlocate_path=${mlocate_path#'Database '*} #remove 'Database '
else
    echo "Couldn't run locate command.  Is mlocate installed?"
    exit 5
fi

Instead of hard-coding the path to the database, we collect the locate database details using the -S parameter. By using some string manipulation functions we can tease out the file path from the output.

Because we are going to offer to update the location database (as well as eventually manipulate authorized_keys files), it makes sense to check that we are root before proceeding. Additionally, let's check to see that we get a pattern from our user, and provide some usage guidance.

if [ ! `whoami` = "root" ]
then
    echo "Please run as root."
    exit 4
fi

if [ -z $1 ]
then
    echo "Usage: check_authorized_keys PATTERN"
    exit 3
fi

Checking and modifying authorized_keys for a pattern

With some prerequisites in place, we're finally ready to scan the system's authorized_keys files. Let's just start with the syntax for that loop.

for key_file in `locate authorized_keys`; do
    echo "Searching $key_file..."
done

We do not specify a dollar sign ($) in front of key_file when defining the loop, but once inside our loop we use the regular syntax. We use command substitution by placing a command around back quotes (`) around the output of the command we want to use. We're now scanning each file, but how do we find matching entries?

IFS=$'\n'
for matching_entry in `grep "$1" $key_file`; do
    IFS=' '
    echo "Found an entry in $key_file:"
    echo $matching_entry
done

For each $key_file, we now grep our user's pattern ($1) and store it in $matching_entry. We have to change the Input Field Seperator (IFS) to a new line, instead of the default space, in order to capture each grepped line in its entriety. (Thanks to Brian Miller for that one!)

With a matching entry found in a key file, it's time to finally offer the user a chance to remove the entry.

echo "Found an entry in $key_file:"
echo $matching_entry
echo -n "Remove entry? [y/n]: "
read remove_entry
if [ "$remove_entry" = "y" ]
then
    if [ ! -w $key_file ]
    then
        echo "Cannot write to $key_file."
        exit 1
    else
        sed -i "/$matching_entry/d" $key_file
        echo "Deleted."
    fi
else
    echo "Not deleted."
fi

We prompt the user if they want to delete the shown entry, verify we can write to the $key_file, and then delete the $matching entry. By using the -i option to the sed command, we are able to make modifications in place.

The Final Product

I'm sure there is a lot of room for improvement on this script and I'd welcome pull requests on the GitHub repo I setup for this little block of code. As always, be very careful when running automated scripts as root. Please test this script out on a non-production system before use.

#!/bin/bash

if [ ! `whoami` = "root" ]
then
    echo "Please run as root."
    exit 4
fi


if [ -z $1 ]
then
    echo "Usage: check_authorized_keys PATTERN"
    exit 3
fi

if
    mlocate_path=`locate -S`
then
    # locate -S command will output database path in following format:
    # Database /full/path/to/db: (more output)...
    mlocate_path=${mlocate_path%:*} #remove content after colon
    mlocate_path=${mlocate_path#'Database '*} #remove 'Database '
else
    echo "Couldn't run locate command.  Is mlocate installed?"
    exit 5
fi

if [ -r $mlocate_path ]
then
    echo -n "mlocate database last updated: "
    stat -c %y $mlocate_path
    echo -n "Do you want to update the locate database this script depends on? [y/n]: "
    read update_locate
    if [ "$update_locate" = "y" ]
    then
        echo "Updating locate database.  This may take a few minutes..."
        updatedb
        echo "Update complete."
        echo ""
    fi
else
    echo "Cannot read from $mlocate_path"
    exit 2
fi

for key_file in `locate authorized_keys`; do
    echo "Searching $key_file..."
    IFS=$'\n'
    for matching_entry in `grep "$1" $key_file`; do
    IFS=' '
        echo "Found an entry in $key_file:"
        echo $matching_entry
        echo -n "Remove entry? [y/n]: "
        read remove_entry
        if [ "$remove_entry" = "y" ]
        then
            if [ ! -w $key_file ]
            then
                echo "Cannot write to $key_file."
                exit 1
            else
                sed -i "/$matching_entry/d" $key_file
                echo "Deleted."
            fi
        else
            echo "Not deleted."
        fi
    done
done

echo "Search complete."

Interchange Search Caching with "Permanent More"

Most sites that use Interchange take advantage of Interchange's "more lists". These are built-in tools that support an Interchange "search" (either the search/scan action, or result of direct SQL via [query]) to make it very easy to paginate results. Under the hood, the more list is a drill-in to a cached "search object", so each page brings back a slice from the cache of the original search. There are extensive ways to modify the look and behavior of more lists and, with a bit of effort, they can be configured to meet design requirements.

Where more lists tend to fall short, however, is with respect to SEO. There are two primary SEO deficiencies that get business stakeholders' attention:

  • There is little control over the construction of the URLs for more lists. They leverage the scan actionmap and contain a hash key for the search object and numeric data to identify the slice and page location. They possess no intrinsic value in identifying the content they reference.
  • The search cache by default is ephemeral and session-specific. This means all those results beyond page 1 the search engine has cataloged will result in dead links for search users who try to land directly on the more-listed pages.

It is the latter issue that I wish to address because there is--and has been for some time now--a simple mechanism called "permanent more" to remedy the default behavior.

You can leverage "permanent more" by adding the boolean mv_more_permanent, or the shorthand pm, to your search conditions. E.g.:

Link:

    <a href="[area search="
        co=1
        sf=category
        se=Foo
        op=rm
        more=1
        ml=5
        pm=1
    "]">All Foos</a>

Loop:

    [loop search="
        co=1
        sf=category
        se=Foo
        op=rm
        more=1
        ml=5
        pm=1
    "]
    ...loop body with [more-list]...
    [/loop]

Query:

    [query
        list=1
        more=1
        ml=10
        pm=1
        sql="SELECT * FROM products WHERE category LIKE '%Foo%'"
    ]
    ...same as loop but with 10 matches/page...
    [/query]

If the initial search is defined with the "permanent more" setting, it will produce the following adjustments:

  • The hash key used to store and identify the search cache is deterministic based on the search conditions. Many searches for Interchange are category driven. Thus, all end users who wish to browse a category end up clicking identical links, which create duplicate search caches, belonging uniquely to them. With permanent more, they all share the same cache, with the same identifier. As long as the search conditions don't change, neither does the cache identifier. Even as the cache is refreshed with new executions of the search, the object remains in the same location. Thus, the results a search engine produced this morning reference links still valid now, tomorrow, or next week, provided they reference the same search conditions.
  • The cached search object has no session affinity. Any link referencing the cache with the correct hash key has access to the content.

Taken together, "permanent more" removes (for the most part, addressed later) dead links from more lists cataloged by search engines. There are, however, other benefits to "permanent more" beyond those intended as described above:

  • As stated in passing, standard Interchange search caching produces duplicate search objects for common search conditions. For a busy site, these caches can have an impact on storage. Typically, maintenance is implemented to clean up cache files for all such files whose age exceeds by some amount the session duration (standard is 48 hours). With permanent more, duplicate caches are eliminated. A cache location is reused by all users with the same search requirements, keeping data-storage requirements for caches to the minimum necessary. As searches change, ophaned caches can still easily be cleaned up as they will immediately start to age with no more access to them necessary for storage.
  • For the same reason that "permanent more" resolves search-engine links, it also resolves content management for individual sites using a reverse proxy for caching. Because most (and certainly the easiest) caching keys are based off of URL, the deterministic nature of the hash keys for "permanent more" allows assurance that the cached content in the proxy accurately reflects the search content over time, and that all users will hit the cached resource and not generate new, unique links with varying hash keys.

One shortcoming of "permanent more" to be aware of is the impact of changing data underneath the search. Even if search conditions do not change, the count and order of matching record sets may. So, e.g., enough products may be removed from a given category to cause the last page of a more list to become empty, which would cause any specific link into that page to become dead. More minor, but still a possibility, is the introduction or removal of products so that a particularly searched-for term has been "bumped" to another page within the search cache since the last time the search engine crawled the more lists. For searches backed by particularly volatile data, "permanent more" may not be sufficient to address search-engine or caching demands.

Finally, "permanent more" should be avoided for any search features that may cache data sensitive to an individual user. This is unlikely to happen as, under most circumstances, the configuration of the search itself will change based on the unique characteristics of the user executing the search (e.g., a username included in a query to review order history). However, it is still possible that context-sensitive information could be stored in the search object and, if so, all other users with access to the more lists would have access to that information.