Building and Integrating a Small Office Intranet

I designed an e-mail gateway for our company back in 2001, and it's still the system we use today. I wrote about it in a previous Linux Journal article in the December 2001 issue. The system has been modified tremendously since then, but it still operates in the same basic way. It's simply a store, scan and forward agent. Because this all takes place on our Linux server, our Windows users were unable to see or retrieve false positives or have any control over their SpamAssassin whitelists. We solved this by building a set of CGI scripts to let our users modify their SpamAssassin preferences file and release their false positives from the spam trap on their own, using the intranet as the interface.

Users launch the mail management scripts from their My Intranet section on our home page (Figure 2). They choose which day's mail they want to view from a drop-down box and click a button to activate the selfserv.cgi script. There is no user identity information passed to the script, because it will obtain that information from an SSC lookup. After we do the initial SSC lookup, we call the getempinfo.pl script again to get the current user's e-mail address, like this:

##: Get this user's email address
open(GETEMPINFO,"-|","getempinfo.pl",$cn,"mail");
my $searchstring=<GETEMPINFO>;
close(GETEMPINFO);

The $searchstring variable then becomes the base of the regular expression we use to search the /spam directory for spam belonging to this user. As the mail attribute coming from Active Directory is something typed in by human hands, we must do another check to make sure we aren't falling victim to typos:

##: Make sure this email address is valid
unless($searchstring=~/^[a-z]*\@domain\.com$/) {
  print STDOUT "Content-Type: text/plain\n\n";
  print STDOUT "Access Denied: Your identity on \
    the network can't be verified.\n";
  return(0);
}

If these checks are successful, the script responds by showing users the requested day's spam in a table format with a list of option links on the side of each item (Figure 3). Users then can use the option links to have the script release the spam, whitelist its sender, blacklist its sender, produce a SpamAssasin report or simply display it as plain text. The script looks up the user's SSC information each time it's called and before any action is performed so that it knows whether or not to allow that action. I won't get into more detail here, because the functions of this script consist mostly of just moving files around in response to users' requests. I do want to mention the whitelist and blacklist options though.

Figure 3. Options for Handling Trapped Spam

SpamAssassin holds its per-user configuration data in a file named .spamassassin/user_prefs.cf in each user's home directory. In a normal setup, where Linux is your main mail server, this is fine, but in our case, it won't work. Our Linux server is merely a scanning gateway that relays mail in and out, so it has no awareness of our users or their e-mail accounts. To solve this, we have to cheat a little. SpamAssassin's main configuration file is named /etc/mail/spamassassin/local.cf, and it reads this file every time it's started. It doesn't read only that file though. It actually reads all files in the /etc/mail/spamassasssin directory that have a .cf extension. We can use this to our advantage and have our CGI script create files in this directory for each user's whitelist in a $cn_prefs.cf format. We have a cron job that restarts spamd every hour anyway to free memory, so this works out fine. The most important thing to remember if you use this method is that you have to do strict syntax checking to make sure users aren't whitelisting things like *@hotmail.com or using any other SpamAssassin directives. Even though these files have the appearance of private preference files to users, they actually are global to SpamAssassin, because they reside in the main config directory.

Our firm uses a time and billing system called CPAS. This software package holds all of our client and billing information as well as information used by our marketing manager to assemble mass mail-outs to our clients. We wanted to give our users access to this information to do some rudimentary data mining without having to contact administration every time. Because CPAS stores its information in a Microsoft SQL Server database, we had to use a piece of software called FreeTDS and the DBD::Sybase package from CPAN to interface to it from our Perl CGI scripts.

Four steps are involved in setting this up. The first thing to do is grab the latest FreeTDS package from the Internet and unpack the tarball. Next, cd into the unpacked directory, and execute the following commands:

> ./configure --prefix=/usr/local/freetds
> make
> su -c 'make install'

This sets up FreeTDS in its own directory, so it's easier for the Sybase module to find later. Next, we go into CPAN and get the DBD::Sybase package. Become root and execute the following commands:

> perl -MCPAN -e shell
> install DBD::Sybase

Feel free to force the install if some of the tests fail—that is pretty common according to the package's author. At this point, the software is installed, but we have to set up the FreeTDS configuration file. This file holds information about the databases to which you will be connecting. The configuration file is well documented, and you should be able to figure out the syntax easily. Here is a sample server entry:

[JACKSON5]
    host = jackson5.domain.com
    port = 1433
    tds version = 4.2

Once FreeTDS is configured, you can access your database from your CGI scripts through the familiar DBI interface in Perl. Here is an example connection to a database called concerts running on a Windows server named JACKSON5:

#!/usr/bin/perl -w
use DBI; $ENV{'SYBASE'} = '/usr/local/freetds';
$dbh = DBI->connect('dbi:Sybase:server=JACKSON5', 'username', 'password')
    or die 'connect';
$dbh->do("use concerts");

Notice that you have to put the location of your FreeTDS installation in an environment variable before you attempt a connection. The environment variable tells DBD::Sybase where to find the FreeTDS libraries. After that, you simply perform your queries as usual using DBI. If you are used to working with MySQL, I suggest you study up on the syntax used by Microsoft SQL Server. Some of it is very different from what you are used to.