SFTP ProFTPD сервер с поддержкой MySQL

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;

### 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

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

function log_error($str){
return;
}
function str_log($str){
global $verbose;
if($verbose > 0)
echo $str . "\n";
}
function byte_size($bytes){
$size = $bytes / 1024;
if($size < 1024)
{
$size = number_format($size, 2);
$size .= ' KB';
}
else
{
if($size / 1024 < 1024)
{
$size = number_format($size / 1024, 2);
$size .= ' MB';
}
else if ($size / 1024 / 1024 < 1024)
{
$size = number_format($size / 1024 / 1024, 2);
$size .= ' GB';
}
}
return $size;
}
?>

// 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
?>

// 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.com\r\nReply-To: admin@jasonn.com";
$headers .= "\r\nContent-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-
Content-Type: multipart/alternative; boundary="PHP-alt-"

--PHP-alt-
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Attached is the usage report CSV for proftpd server.

--PHP-alt-
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Attached is the usage report CSV for proftpd server.

--PHP-alt---

--PHP-mixed-
Content-Type: application/zip; name=""
Content-Transfer-Encoding: base64
Content-Disposition: attachment

--PHP-mixed---

$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";
?>

# 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
}