ProFTPD + MySQL Authentication + E-Mail Monitor Howto

Author: Stefano Radice <stefano@despammed.com>

Updated: 18 June 2005

Summary

This howto describes how to make a ProFTPD 1.2.x/MySQL system capable of sending alerts by e-mail when a user updates his/her FTP home directory. The typical scenario is a company using FTP as a means of sharing files with customers/suppliers when they are too big to be mailed. Being alerted when a supplier uploads that important file avoids the hassle of checking his home directory every 30 seconds ;-) The howto uses the same table names and conventions as Stephen Khoo's "ProFTPD + MySQL authentication + Quotas Howto".

1) The idea

The table ftpuser in the ftpdb database (or whatever you named it) contains the fields:
  • accessed
  • modified
The data type of these fields is datetime. ProFTPD automatically updates them to sysdate whenever the user "accesses" (i.e. starts a new session for directory listing or download) or "modifies" (i.e. uploads or deletes files) his/her FTP home. For the purpose described in the summary, the "modified" field is particularly interesting: monitoring its value will allow us to alert an admin whenever a user uploads or deletes files. As you'll see, it's very easy to adapt this howto to monitor the "accessed" field, if need be.

2) Prerequisites

  • ProFTPD with MYSQL authentication must be already set up and running on your system. Check Stephen's article here before going further if you don't know how to do this.  
  • An MTA such as Postfix or Sendmail should be up and running on your system, and capable to find/reach the MX for the domain on which the mailbox of the user to be alerted resides, if it's not localhost. If you don't understand this, you probably don't want to mess with this stuff ;-)
  • A fairly recent version of Perl, Perl-DBI and Perl-DBD-mysql must be present on your system. Perl is a powerful script language, that includes database support and is particularly fit for administrative tasks. You don't need any knowledge of Perl to set this up. The modules are most likely among your distribution's packages, so you probably don't need to compile them from source. Check your distribution's docs or www.perl.org for details.

3) The monitor table

Logon to MySQL and issue the following command:

 

CREATE TABLE ftpusermonitor (
  id int(10) unsigned NOT NULL,
  userid varchar(32) NOT NULL default '',
  accessed datetime NOT NULL default '0000-00-00 00:00:00',
  modified datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (id),
  UNIQUE KEY userid (userid)
) TYPE=MyISAM;

 

Whenever you add a user to the ftpuser table, also add a related row to the ftpusermonitor table:

 

INSERT INTO ftpusermonitor (id, userid) SELECT id, userid FROM ftpuser WHERE userid='myuser';

 

Note: if you have accounts you don't want to monitor, simply don't add them to the monitor table.

4) The script

Save to you favourite directory the script ftpmon.pl:

 

#! /usr/bin/perl

# ftpmon.pl
# Monitor updates to FTP directories
# Author: Stefano Radice <stefano@despammed.com>

use strict;
use DBI;

my $server = 'localhost';
my $db = 'mydb';
my $dblogin = 'mylogin';
my $dbpwd = 'mypassword';
my $ftpalert = 'some.user@somedomain.com';
my $dbquery ="SELECT a.id, a.userid, a.modified FROM ftpuser a INNER JOIN ftpusermonitor b ON a.id = b.id WHERE a.modified > b.modified";

my $dbh = DBI->connect("dbi:mysql:$db:$server", $dblogin, $dbpwd);
my $sth = $dbh->prepare($dbquery);
$sth->execute();

my @dbrow;

while(@dbrow=$sth->fetchrow_array()) {
       my $subject='FTP Update: ' . $dbrow[1];
       my @body=("The user has updated",
                 "his FTP home.");
       send_mail($ftpalert,
                 'FTPSERVER@ftp.somedomain.com',
                 $subject,
                 @body);
       my $dbupd=
       "UPDATE ftpusermonitor SET modified='". $dbrow[2] . "' WHERE id=" . $dbrow[0];    
       $dbh->do($dbupd);
}

$dbh->disconnect;

sub send_mail {
       my($to, $from, $subject, @body)=@_;
       my $sendmail="/usr/sbin/sendmail -t -oi -odq";
       open(MAIL, "|$sendmail") || die "Can't start sendmail: $!";
       print MAIL<<END_OF_HEADER;
From: $from
To: $to
Subject: $subject

END_OF_HEADER
       foreach (@body) {
               print MAIL "$_\n";
       }
       close(MAIL);
}

 

Make the script executable:

 

# chmod 700 ftpmon.pl

 

Note: To generate an alert when a mailbox is accessed instead of modified, just change the line:

my $dbquery ="SELECT a.id, a.userid, a.modified FROM ftpuser a INNER
JOIN ftpusermonitor b ON a.id = b.id WHERE a.modified > b.modified";

to

my $dbquery ="SELECT a.id, a.userid, a.accessed FROM ftpuser a INNER
JOIN ftpusermonitor b ON a.id = b.id WHERE a.accessed > b.accessed";

and the line:

my $dbupd= "UPDATE ftpusermonitor SET modified='". $dbrow[2] . "' WHERE id=" . $dbrow[0];

to

my $dbupd="UPDATE ftpusermonitor SET accessed='". $dbrow[2] . "' WHERE id=" . $dbrow[0];
You might also want to change $subject and @body

5) Schedule the job

Enter the following in your crontab:

 

*/5 * * * * /scripts/ftpmon.pl

 

The script will fire up every 5 mins and send an e-mail for each updated user. The script will also update the monitor table so that no further alert will be sent until next update. The whole process can take anywhere from a nanosecond to several minutes depending on number of users and system resources, but on most systems it should run pretty fast.

Have fun!

Stefano