To install ProFTPD with MySQL-based authentication and SFTP support, you need to download the latest version of the source code and build it with customized options. But first, some prerequisites. (These instructions are for ubuntu but can easily be modified for CentOS).

Download the dependencies:

[sourcecode lang=”bash”]
apt-get install mysql mysql-server mysql-client libmysql++-dev libssl-dev build-essential libncurses-dev
[/sourcecode]

Setup the ftpgroup and ftpuser which will own all the files on the server. The virtual MySQL users will be mapped to these two:

[sourcecode lang=”bash”]
groupadd -g 2001 ftpgroup
useradd -u 2001 -s /sbin/false -d /bin/null -c “proftpd user” -g ftpgroup ftpuser
[/sourcecode]

Create mysql database structure required by proftpd

[sourcecode lang=”sql”]
/etc/init.d/mysqld start
mysqladmin -root -p password ‘yourpassword’
mysql -uroot -p

CREATE DATABASE ftp;
USE ftp;

CREATE TABLE ftpgroup (
groupname varchar(16) NOT NULL default ‘’,
gid smallint(6) NOT NULL default ‘5500’,
members varchar(16) NOT NULL default ‘’,
KEY groupname (groupname)
) TYPE=MyISAM COMMENT=’ProFTP group table’;

CREATE TABLE ftpquotalimits (
name varchar(30) default NULL,
quota_type enum(‘user’,’group’,’class’,’all’) NOT NULL default ‘user’,
per_session enum(‘false’,’true’) NOT NULL default ‘false’,
limit_type enum(‘soft’,’hard’) NOT NULL default ‘soft’,
bytes_in_avail int(10) unsigned NOT NULL default ‘0’,
bytes_out_avail int(10) unsigned NOT NULL default ‘0’,
bytes_xfer_avail int(10) unsigned NOT NULL default ‘0’,
files_in_avail int(10) unsigned NOT NULL default ‘0’,
files_out_avail int(10) unsigned NOT NULL default ‘0’,
files_xfer_avail int(10) unsigned NOT NULL default ‘0’
) TYPE=MyISAM;

CREATE TABLE ftpquotatallies (
name varchar(30) NOT NULL default ‘’,
quota_type enum(‘user’,’group’,’class’,’all’) NOT NULL default ‘user’,
bytes_in_used int(10) unsigned NOT NULL default ‘0’,
bytes_out_used int(10) unsigned NOT NULL default ‘0’,
bytes_xfer_used int(10) unsigned NOT NULL default ‘0’,
files_in_used int(10) unsigned NOT NULL default ‘0’,
files_out_used int(10) unsigned NOT NULL default ‘0’,
files_xfer_used int(10) unsigned NOT NULL default ‘0’
) TYPE=MyISAM;

CREATE TABLE ftpuser (
id int(10) unsigned NOT NULL auto_increment,
userid varchar(32) NOT NULL default ‘’,
passwd varchar(32) NOT NULL default ‘’,
uid smallint(6) NOT NULL default ‘5500’,
gid smallint(6) NOT NULL default ‘5500’,
homedir varchar(255) NOT NULL default ‘’,
shell varchar(16) NOT NULL default ‘/sbin/nologin’,
count int(11) NOT NULL default ‘0’,
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 COMMENT=’ProFTP user table’;

CREATE TABLE IF NOT EXISTS xferlog (
username varchar(100) NOT NULL,
timestamp datetime NOT NULL,
bytes int(20) NOT NULL,
file varchar(255) NOT NULL,
direction varchar(1) NOT NULL,
ip varchar(20) NOT NULL,
KEY username (username)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

GRANT ALL PRIVILEGES ON FTP.* to proftpd@localhost identified by ‘yourpassword’;

INSERT INTO ftpgroup (groupname, gid, members) VALUES (‘ftpgroup’, 2001, ‘ftpuser’);
INSERT INTO ftpuser (id, userid, passwd, uid, gid, homedir, shell, count, accessed, modified) VALUES (1, ‘exampleuser’, ‘secret’, 2001, 2001, ‘/home/www.example.com’, ‘/sbin/nologin’, 0, ‘’, ‘’);

quit;
[/sourcecode]

Download, compile and install proftpd

[sourcecode lang=”bash”]
wget ftp://ftp1.at.proftpd.org/ProFTPD/distrib/source/proftpd-1.3.3c.tar.bz2
tar jxf proftpd-1.3.3c.tar.bz2
cd proftpd-1.3.3c
install_user=root install_group=root ./configure –with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql:mod_sftp –with-includes=/usr/include/mysql/ –with-libraries=/usr/lib/mysql/ –enable-timeout-linger –enable-timeout-stalled –sysconfdir=/etc –localstatedir=/var –prefix=/usr
make && make install
[/sourcecode]

Create the init scripts for proftpd in /etc/ini.d/proftpd if it doesn’t already exist.

[sourcecode lang=”bash”]
#!/bin/sh

BEGIN INIT INFO

Provides: proftpd

Required-Start: $syslog $local_fs $network

Required-Stop: $syslog $local_fs $network

Should-Start: $remote_fs $named

Should-Stop: $remote_fs $named

Default-Start: 2 3 4 5

Default-Stop: 0 1 6

Short-Description: Starts ProFTPD daemon

Description: This script runs the FTP service offered

by the ProFTPD daemon

END INIT INFO

Start the proftpd FTP daemon.

PATH=/bin:/usr/bin:/sbin:/usr/sbin
DAEMON=/usr/sbin/proftpd
NAME=proftpd

Defaults

RUN=”no”
OPTIONS=””

PIDFILE=grep -i 'pidfile' /etc/proftpd/proftpd.conf | sed -e 's/pidfile[t]+//i'
if [“x$PIDFILE” = “x”];
then
PIDFILE=/var/run/proftpd.pid
fi

Read config (will override defaults)

[-r /etc/default/proftpd] && . /etc/default/proftpd

trap “” 1
trap “” 15

test -f $DAEMON

exit 0

. /lib/lsb/init-functions

Servertype could be inetd|standalone|none.

In all cases check against inetd and xinetd support.

#
if ! egrep -qi “^[[:space:]]*ServerType.*standalone” /etc/proftpd/proftpd.conf
then
if [$(dpkg-divert –list xinetd|wc -l) -eq 1]
then
if egrep -qi “server[[:space:]]*=[[:space:]]*/usr/sbin/proftpd” /etc/xinetd.conf 2>/dev/null ||
egrep -qi “server[[:space:]]*=[[:space:]]*/usr/sbin/proftpd” /etc/xinetd.d/* 2>/dev/null
then
RUN=”no”
INETD=”yes”
else
if ! egrep -qi “^[[:space:]]*ServerType.*inetd” /etc/proftpd/proftpd.conf
then
RUN=”yes”
INETD=”no”
else
RUN=”no”
INETD=”no”
fi
fi
else
if egrep -qi “^ftp.*/usr/sbin/proftpd” /etc/inetd.conf 2>/dev/null
then
RUN=”no”
INETD=”yes”
else
if ! egrep -qi “^[[:space:]]*ServerType.*inetd” /etc/proftpd/proftpd.conf
then
RUN=”yes”
INETD=”no”
else
RUN=”no”
INETD=”no”
fi
fi
fi
fi

/var/run could be on a tmpfs

[! -d /var/run/proftpd] && mkdir /var/run/proftpd

start()
{
log_daemon_msg “Starting ftp server” “$NAME”

start-stop-daemon –start –quiet –pidfile “$PIDFILE” –oknodo –exec $DAEMON – $OPTIONS
if [$? != 0]; then
log_end_msg 1
exit 1
else
log_end_msg 0
fi
}

signal()
{

if [“$1” = “stop”]; then
SIGNAL=”TERM”
log_daemon_msg “Stopping ftp server” “$NAME”
else
if [“$1” = “reload”]; then
SIGNAL=”HUP”
log_daemon_msg “Reloading ftp server” “$NAME”
else
echo “ERR: wrong parameter given to signal()”
exit 1
fi
fi
if [-f “$PIDFILE”]; then
start-stop-daemon –stop –signal $SIGNAL –quiet –pidfile “$PIDFILE”
if [$? = 0]; then
log_end_msg 0
else
SIGNAL=”KILL”
start-stop-daemon –stop –signal $SIGNAL –quiet –pidfile “$PIDFILE”
if [$? != 0]; then
log_end_msg 1
[$2 != 0] || exit 0
else
log_end_msg 0
fi
fi
if [“$SIGNAL” = “KILL”]; then
rm -f “$PIDFILE”
fi
else
log_end_msg 0
fi
}

case “$1” in
start)
if [“x$RUN” = “xyes”] ; then
start
else
if [“x$INETD” = “xyes”] ; then
echo “ProFTPd is started from inetd/xinetd.”
else
echo “ProFTPd warning: cannot start neither in standalone nor in inetd/xinetd mode. Check your configuration.”
fi
fi
;;

force-start)
if [“x$INETD” = “xyes”] ; then
echo “Warning: ProFTPd is started from inetd/xinetd (trying to start anyway).”
fi
start
;;

stop)
if [“x$RUN” = “xyes”] ; then
signal stop 0
else
if [“x$INETD” = “xyes”] ; then
echo “ProFTPd is started from inetd/xinetd.”
else
echo “ProFTPd warning: cannot start neither in standalone nor in inetd/xinetd mode. Check your configuration.”
fi
fi
;;

force-stop)
if [“x$INETD” = “xyes”] ; then
echo “Warning: ProFTPd is started from inetd/xinetd (trying to kill anyway).”
fi
signal stop 0
;;

reload)
signal reload 0
;;

force-reload|restart)
if [“x$RUN” = “xyes”] ; then
signal stop 1
sleep 2
start
else
if [“x$INETD” = “xyes”] ; then
echo “ProFTPd is started from inetd/xinetd.”
else
echo “ProFTPd warning: cannot start neither in standalone nor in inetd/xinetd mode. Check your configuration.”
fi
fi
;;

status)
if [“x$INETD” = “xyes”] ; then
echo “ProFTPd is started from inetd/xinetd.”
exit 0
else
if [-f “$PIDFILE”]; then
pid=$(cat $PIDFILE)
else
pid=”x”
fi
if [pidof proftpd|grep "$pid"|wc -l -ne 0] ; then
echo “ProFTPd is started in standalone mode, currently running.”
exit 0
else
echo “ProFTPd is started in standalone mode, currently not running.”
exit 3
fi
fi
;;

check-config)
$DAEMON -t >/dev/null && echo “ProFTPd configuration OK” && exit 0
exit 1
;;

*)
echo “Usage: /etc/init.d/$NAME {start|status|force-start|stop|force-stop|reload|restart|force-reload|check-config}”
exit 1
;;
esac

exit 0
[/sourcecode]

Create the server keys for use with SFTP

[sourcecode lang=”bash”]
ssh-keygen -t rsa
ssh-keygen -t dsa
[/sourcecode]

Edit /etc/proftpd/proftpd.conf and append the following to the end:

[sourcecode lang=”bash”]
SFTPEngine on # use SFTP instead of FTP
Port 2222
SFTPHostKey /root/.ssh/id_rsa
SFTPHostKey /root/.ssh/id_dsa

DefaultRoot ~ # Jail the FTP users to within their homedir
SQLAuthTypes Plaintext Crypt
SQLAuthenticate users* groups*

Connection string in the format database@host user password

SQLConnectInfo ftp@localhost root yourpassword
SQLUserInfo ftpuser userid passwd uid gid homedir shell
SQLGroupInfo ftpgroup groupname gid members
SQLMinID 500
CreateHome on

SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE “count=count+1, accessed=now() WHERE userid=’%u’” ftpuser
SQLLog STOR,DELE modified
SQLNamedQuery modified UPDATE “modified=now() WHERE userid=’%u’” ftpuser

RootLogin off
RequireValidShell off

IMPORTANT: required for bandwidth accounting

TransferLog /var/log/proftpd/xferlog
SystemLog /var/log/proftpd/proftpd.log
[/sourcecode]

Enable the required modules by uncommenting the following lines in /etc/proftpd/modules.conf.

[sourcecode lang=”bash”]
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
[/sourcecode]

Start the proftpd server and test it out by connecting to it through your FTP client. In this example, the username is exampleuser and password is secret.

[sourcecode lang=”bash”]
/etc/init.d/proftpd start
[/sourcecode]

For the monthly bandwidth accounting report, you need to enable logrotate and call it through cron. First, setup the database configurations and helper functions. This will be /usr/src/ftpan/config.php:

[sourcecode lang=”php”]
// Database credentials
$db_host = “localhost”;
$db_user = “username”;
$db_pass = “yourpassword”;
$db_sele = “ftp”;
$db_table = “xferlog”;
$db_user_table = “ftpuser”;
$TO_EMAIL = “admin@email.address.com”;

Then, create a script that will read a transfer log file and populate the database: /usr/src/ftpan/analyze-proftpd.php.

[sourcecode lang=”php”]
<?php
require_once(‘config.php’);
$verbose = 1;

// get the file name
$logfile = $argv[1];
str_log(“Processing log file: $logfile =================================================== “);
/// connect to the db
mysql_connect($db_host, $db_user, $db_pass);
@mysql_select_db($db_sele) or die( “Unable to access database”);

// open the logfile and process it line by line
$fp = fopen($logfile, ‘r’);
while ( ($inline = fgets($fp)) !== false) {
// process the file and insert all logs
$inline = trim($inline);

$tok = strtok($inline, “ “);
$weekday = $tok; $tok = strtok(“ “);
$month = $tok; $tok = strtok(“ “);
$day = $tok; $tok = strtok(“ “);
$timestamp = $tok; $tok = strtok(“ “);
$year = $tok; $tok = strtok(“ “);
$skip = $tok; $tok = strtok(“ “);
$ip = $tok; $tok = strtok(“ “);
$bytes = $tok; $tok = strtok(“ “);
$file = $tok; $tok = strtok(“ “);
$skip = $tok; $tok = strtok(“ “);
$skip = $tok; $tok = strtok(“ “);
$direction = $tok; $tok = strtok(“ “);
$skip = $tok; $tok = strtok(“ “);
$user= $tok; $tok = strtok(“ “);

$str_stamp = “$month $day $year $timestamp”;
$stamp = date(‘Y-m-d H:i:s’, strtotime($str_stamp));

str_log(“Retrieved data: $user $stamp $bytes $file $direction $ip”);

// select to make sure this exact record has not already been entered. (Just in case)
$query = “select * from $db_table where username = ‘$user’ AND timestamp=’$stamp’ AND bytes=’$bytes’ AND direction=’$direction’ AND ip=’$ip’;”;
$result = mysql_query($query);

if(mysql_num_rows($result) > 0){
str_log(“Log entry at stamp $stamp for user $user already exists. Skipping.”);
continue;
}

// if not, insert it
$query = “INSERT into $db_table (username, timestamp, bytes, file, direction, ip)
VALUES (‘$user’, ‘$stamp’, ‘$bytes’, ‘$file’, ‘$direction’, ‘$ip’); “;

$result = mysql_query($query);
echo mysql_error();

if($result < 1)
str_log(“error inserting query: $user $stamp”);

}
// (manually) call the generate CSV for the generation of CSV reports
?>
[/sourcecode]

Finally, create a CSV generation script that will get data from the database and send it through email as well as saving it in the folder. We call it /usr/src/ftpan/generate-csv.php.

[sourcecode lang=”php”]
<?php
require_once(‘config.php’);
$verbose = 1;

// select the data into a CSV and write to the file named as this stamp;
$date_now = date(‘Y-m-d’);
$this_month = date(‘m’);
$this_year = date(‘Y’);
// make sure this is changed to LAST month instead of this month when deploying : done
$date_begin = date(‘Y-m-d H:i:s’, mktime(0, 0, 0, $this_month -1, 1, $this_year));
$date_end = date(‘Y-m-d H:i:s’, mktime(11, 59, 59, $this_month, 0, $this_year));

str_log(“Processing on date: $date_now =======================================================”);
str_log($date_begin);
str_log($date_end);

$OUTFILE = “proftpd-usage-report-“ . $date_now . “.csv”;

$fo = fopen($OUTFILE, “w”);

mysql_connect($db_host, $db_user, $db_pass);
@mysql_select_db($db_sele) or die( “Unable to access database”);

// find the distinct usernames from the table
$query = “select userid, homedir from $db_user_table;”;
$result = mysql_query($query);

while($row = mysql_fetch_array($result)){
// for each user
$username = $row[‘userid’];
$homedir = $row[‘homedir’];

str_log(“Finding totals for $username”);

// select the total “in” in this month
$query2 = “select sum(bytes) from $db_table where username = ‘$username’
AND direction = ‘i’
AND timestamp >= ‘$date_begin’ AND timestamp <= ‘$date_end’;”;
str_log($query2);
$result2 = mysql_query($query2);

$row2 = mysql_fetch_array($result2);
$in_total = intval($row2[0]);
str_log(“In total was: $in_total”);

// select the total “out” in this month
$query2 = “select sum(bytes) from $db_table where username = ‘$username’
AND direction = ‘o’
AND timestamp >= ‘$date_begin’ AND timestamp <= ‘$date_end’;”;
$result2 = mysql_query($query2);

$row2 = mysql_fetch_array($result2);
$out_total = intval($row2[0]);
str_log(“In total was: $out_total”);

// set the total
$grand_total = byte_size($in_total + $out_total);
str_log(“Grand total was: $grand_total”);

//output to the CSV file
$outline = “$username, $homedir, $in_total, $out_total, $grand_total, $this_month, $this_year”;
str_log(“Gen: $outline”);
fwrite($fo, trim($outline) . “n”);
}
mysql_close();

$to = $TO_EMAIL;
$subject = ‘ProFTPd usage report’;
$random_hash = md5(date(‘r’, time()));
$headers = “From: admin@jasonn.comrnReply-To: admin@jasonn.com”;
$headers .= “rnContent-Type: multipart/mixed; boundary=”PHP-mixed-“.$random_hash.”””;
$attachment = chunk_split(base64_encode(file_get_contents($OUTFILE)));
ob_start(); //Turn on output buffering
?>
–PHP-mixed-<?php echo $random_hash; ?>
Content-Type: multipart/alternative; boundary=”PHP-alt-<?php echo $random_hash; ?>”

–PHP-alt-<?php echo $random_hash; ?>
Content-Type: text/plain; charset=”iso-8859-1”
Content-Transfer-Encoding: 7bit

Attached is the usage report CSV for proftpd server.

–PHP-alt-<?php echo $random_hash; ?>
Content-Type: text/html; charset=”iso-8859-1”
Content-Transfer-Encoding: 7bit

Attached is the usage report CSV for proftpd server.

–PHP-alt-<?php echo $random_hash; ?>–

–PHP-mixed-<?php echo $random_hash; ?>
Content-Type: application/zip; name=”<?php echo $OUTFILE; ?>”
Content-Transfer-Encoding: base64
Content-Disposition: attachment

<?php echo $attachment; ?>
–PHP-mixed-<?php echo $random_hash; ?>–

<?php
$message = ob_get_clean();
$mail_sent = @mail( $to, $subject, $message, $headers );
//if the message is sent successfully print “Mail sent”. Otherwise print “Mail failed”
echo $mail_sent ? “Mail sent” : “Mail failed”;
?>
[/sourcecode]

The final thing we need is to have our trasnfer log rotated and then these scripts called on the the last month’s logrotate. For that, edit (or create) the /etc/logrotate.d/proftpd-basic file and add the following rule to it:

[sourcecode lang=”bash”]
/var/log/proftpd/xferlog

/var/log/proftpd/xferreport

{
monthly
missingok
rotate 7
compress
delaycompress
# keep 1/2 year worth of logs
rotate 6
notifempty
create 640 root adm
sharedscripts
prerotate
endscript
postrotate
# reload could be not sufficient for all logs, a restart is safer
invoke-rc.d proftpd restart 2>/dev/null >/dev/null || true

call the analyzer and pass it the last log file

/usr/bin/php /usr/src/ftpan/analyze-proftpd.php /var/log/proftpd/xferlog.1 >> /usr/src/ftpan/analysis.log
/usr/bin/php /usr/src/ftpan/generate-csv.php >> /usr/src/ftpan/generation.log
endscript
}
[/sourcecode]

Logrotate should be called daily (our files will be rotated monthly but logrotate itself should be called on a daily basis). If it isn’t enabled already, enable it by adding the following line to crontab -e.

[sourcecode lang=”bash”]
1 0 * * * /usr/sbin/logrotate /etc/logrotate.conf
[/sourcecode]