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


Для установки ProFTPD с MySQL-аутентификацией и поддержкой протокола SFTP, вам необходимо обновить систему. Эта инструкция предназначена для ОС Ubuntu 10.04, но может быть легко модифицирована под CentOS.

И так, устанавливаем джентльменский набор:

apt-get install mysql mysql-server mysql-client libmysql++-dev libssl-dev build-essential libncurses-dev

Добавляем группу ftpgroup и пользователя ftpuser на сервер:

groupadd -g 2001 ftpgroup
useradd -u 2001 -s /sbin/false -d /bin/null -c "proftpd user" -g ftpgroup ftpuser

Запускаем MySQL:

/etc/init.d/mysqld start

Подключаемся к серверу MySQL:

mysqladmin -root -p password 'yourpassword'
mysql -uroot -p

Создаем базу данных для proftpd:

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;


Качаем, собираем, и устанавливаем proftpd:

wget ftp://ftp1.at.proftpd.org/distrib/source/proftpd-1.3.5rc1.tar.gz
tar jxf proftpd-1.3.5rc1.tar.gz
cd proftpd-1.3.5rc1.tar.gz
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

Создаем скрипт запуска для proftpd

nano /etc/ini.d/proftpd

Скрипт:

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

Создадим для ключ для сервера SFTP

ssh-keygen -t rsa
ssh-keygen -t dsa

Правим proftpd.conf:

nano /etc/proftpd/proftpd.conf

Добавьте следующие строки в конец:

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


Включаем нужные нам модули в modules.conf:

nano /etc/proftpd/modules.conf

Раскомментировав следующие строки

LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

Запускаем proftpd сервер и проверяем его, подключив к нему через ваш FTP клиент. В моем случае, имя пользователя является exampleuser а пароль secret.

/etc/init.d/proftpd start

Для регулярных отчетов по пользователям необходимо включить модуль Logrotate и дергать его через cron. Делаем скрипт в config.php:

nano /usr/src/ftpan/config.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";

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 {
$size = number_format($size, 2);
$size .= ' KB';
}
else
{
if($size / 1024 {
$size = number_format($size / 1024, 2);
$size .= ' MB';
}
else if ($size / 1024 / 1024 {
$size = number_format($size / 1024 / 1024, 2);
$size .= ' GB';
}
}
return $size;
}
?>

Затем создаем скрипт, который будет читать файл журнала и заполнять базу данных


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 str_log("error inserting query: $user $stamp");

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

И наконец, нужно создать сценарий CSV для получения данных из базы и отправления его по почте, а также сохранения его в папке.

nano /usr/src/ftpan/generate-csv.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 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 $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";
?>

В заключении нам нужно, чтобы наши журнал очищался. Для этого, отредактировать (или создать) файл proftpd-basic:

nano /etc/logrotate.d/proftpd-basic

Добавить следующие правила:

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


Добавим правило в cron

crontab -e

Добавляем:


1 0 * * * /usr/sbin/logrotate /etc/logrotate.conf

Оцените блог: 
Средняя: 1.7 (235 оценки)