[教學]Proftpd + MySQL

[tvirus]

ProFTPD + MySQL + Quota in Fedora 8,9,10
至 http://www.proftpd.org/ 下載新版的ProFTPD

確認有安裝mysql-devel,gcc,gcc-c++,make

cd /usr/src
wget ftp://ftp.proftpd.org/distrib/source...d-1.3.2.tar.gz
tar fxvz proftpd-1.3.2.tar.gz
cd proftpd-1.3.2
./configure --with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql --with-includes=/usr/include/mysql --with-libraries=/usr/lib/mysql --enable-nls
make
make install

預設的設定檔位置在 /usr/local/etc/proftpd.conf

PS:如果要讓proftpd不使用unicode,可把unix系統設為big5後才編譯,也記得在configure時不要加--enable-nls
修改/etc/sysconfig/i18n
將LANG="zh_TW.UTF-8"
改成LANG="zh_TW.BIG5"再重開機
編譯完就可以改回來了 XD
PS:改成BIG5以後,能夠不再改就不要改,因為,有遇過客戶端軟體造成的怪異問題
這個對於只會用IE開FTP的人有用.(說穿了,就是全部統一使用非Unicode,就算你用Smartftp或是FileZilla來連也一樣,雖然FileZilla會嘗試用UTF8處理...)
或者是
在proftpd.conf裡設定UseUTF8 off
新版的proftpd.conf(1.3.2rc1)是設UseEncoding off 或 UseEncoding cp950 cp950
(在1.3.2正式版上未測試)

MySQL資料表內容 內有Sample資料 資料庫名稱帳號密碼均暫定為proftpd,請依需要修改proftpd.conf裡的SQL連線帳號密碼
原始的資料庫建立,可參照doc\howto\SQL.html
=========================================================
##與官方建議相同
CREATE TABLE `groups` (
`groupname` varchar(30) NOT NULL,
`gid` int(11) NOT NULL,
`members` varchar(255) default NULL
) ;

##與官方建議近似
CREATE TABLE `quotalimits` (
`name` varchar(30) NOT NULL default '',
`quota_type` enum('user','group','class','all') NOT NULL default 'user',
`per_session` enum('false','true') NOT NULL default 'false' COMMENT 'Total size or Everytime login',
`limit_type` enum('soft','hard') NOT NULL default 'hard' COMMENT 'Can upload the last file when Over quota',
`bytes_in_avail` bigint(20) NOT NULL default '0',
`bytes_out_avail` bigint(20) NOT NULL default '0',
`bytes_xfer_avail` bigint(20) 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',
PRIMARY KEY (`name`)
) ;

##與官方建議近似
CREATE TABLE `quotatallies` (
`name` varchar(30) NOT NULL,
`quota_type` enum('user','group','class','all') NOT NULL,
`bytes_in_used` bigint(20) NOT NULL,
`bytes_out_used` bigint(20) NOT NULL,
`bytes_xfer_used` bigint(20) NOT NULL,
`files_in_used` int(10) unsigned NOT NULL,
`files_out_used` int(10) unsigned NOT NULL,
`files_xfer_used` int(10) unsigned NOT NULL
) ;

##與官方建議多了crtime(建立時間)及usertype(後續要透過php建立使用者類別用)及定義userid不可相同,此三項可用可不用
CREATE TABLE `users` (
`userid` varchar(30) NOT NULL,
`passwd` varchar(80) NOT NULL,
`uid` int(11) default NULL,
`gid` int(11) default NULL,
`homedir` varchar(255) NOT NULL,
`shell` varchar(255) default NULL,
`crtime` datetime default NULL,
`usertype` enum('guest','member') NOT NULL default 'guest',
UNIQUE KEY `userid` (`userid`)
) ;

##依據官方文件做出來的資料表
CREATE TABLE filehistory (
command varchar(128) NOT NULL,
`directory` varchar(128) NOT NULL,
`file` varchar(256) NOT NULL,
`size` int(10) unsigned NOT NULL,
`user` varchar(64) NOT NULL,
ip varchar(256) NOT NULL,
`time` datetime NOT NULL,
`serial` bigint(20) unsigned NOT NULL auto_increment,
PRIMARY KEY (`serial`)
) ;
=========================================================



proftpd.conf 設定內容 Sample
=========================================================
# This is a basic ProFTPD configuration file (rename it to
# 'proftpd.conf' for actual use. It establishes a single server
# and a single anonymous login. It assumes that you have a user/group
# "nobody" and "ftp" for normal operation and anon.

ServerName "MySQL FTPServer"
ServerType standalone
DefaultServer on
ServerIdent on "MySQL FTPServer"
SystemLog /var/log/proftpd_system.log
ServerLog /var/log/proftpd_server.log
LogFormat default "%a %u %t \"%r\" %s %b"
ExtendedLog /var/log/proftpd_detail.log DIRS,READ,WRITE default #只對目錄存取,讀取,寫入作記錄
TimesGMT off #傳說中的讓Log以系統時間記錄,但在1.3x版失效?
UseReverseDNS off #這兩行是加快FTP登入時間,一個是IP的DNS反查,另一個功能看的不是很懂...
IdentLookups off
MaxLoginAttempts 3 #最大單次連線時Client重試次數
AllowRetrieveRestart on #允許下載續傳
AllowOverwrite on # 這兩行是允許上傳續傳,有些危險
AllowStoreRestart on
# Port 21 is the standard FTP port.
Port 21

# Umask 022 is a good standard umask to prevent new dirs and files
# from being group and world writable.
Umask 000 #所以上傳後的檔案屬性為777,請依需要修改

# To prevent DoS attacks, set the maximum number of child processes
# to 30. If you need to allow more than 30 concurrent connections
# at once, simply increase this value. Note that this ONLY works
# in standalone mode, in inetd mode you should use an inetd server
# that allows you to limit maximum number of processes per service
# (such as xinetd).
MaxInstances 64

# Set the user and group under which the server will run.
User nobody
Group nobody

# To cause every FTP user to be "jailed" (chrooted) into their home
# directory, uncomment this line.
DefaultRoot ~

# Normally, we want files to be overwriteable.
AllowOverwrite on

# Bar use of SITE CHMOD by default
<Limit SITE_CHMOD>
AllowAll
</Limit>

SQLConnectInfo proftpd@localhost proftpd proftpd #MySQL的資料庫名稱,帳號,密碼
SQLUserInfo users userid passwd uid gid homedir shell
SQLAuthTypes Plaintext
#SQLLogFile /var/log/sql_proftpd.log # 注意,這個Log到最後會大到驚人,請自行決定是否使用..
#以下兩行是將建立刪除目錄,及讀寫檔案的記錄丟進MySQL記錄,如果不需要,就註解掉,前面的filehistory也就不用建了
SQLLog MKD,RMD,RETR,STOR,DELE insertfileinfo
SQLNamedQuery insertfileinfo INSERT "'%m','%d','%f', %b, '%u','%a', now(),''" filehistory

#SQLHomedirOnDemand on
CreateHome on
RequireValidShell off
SQLDefaultGID 99
SQLDefaultUID 99

<IfModule mod_quotatab.c>
QuotaEngine on
QuotaLog /var/log/proftpd_quota.log
QuotaDisplayUnits Mb
QuotaOptions ScanOnLogin
QuotaShowQuotas on
</IfModule>
<IfModule mod_quotatab_sql.c>
SQLNamedQuery get-quota-limit SELECT "* FROM quotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"
SQLNamedQuery get-quota-tally SELECT "* FROM quotatallies WHERE name = '%{0}' AND quota_type = '%{1}'"
SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, files_out_used = files_out_used + %{4}, files_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" quotatallies
SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" quotatallies

QuotaLock /var/lock/ftpd.quotatab.lock
QuotaLimitTable sql:/get-quota-limit
QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally
</IfModule>

=========================================================

proftpd.init (通常需放在/etc/rc.d/init.d中當做系統啟動用)
PS:FC8的proftpd啟動script修改版
=========================================================
#!/bin/sh
#
# proftpd This shell script takes care of starting and stopping
# proftpd.
#
# chkconfig: - 80 30
# description: ProFTPd is an enhanced FTP server with a focus towards \
# simplicity, security, and ease of configuration. \
# It features a very Apache-like configuration syntax, \
# and a highly customizable server infrastructure, \
# including support for multiple 'virtual' FTP servers, \
# anonymous FTP, and permission-based directory visibility.
# processname: proftpd
# config: /usr/local/etc/proftpd.conf
# pidfile: /var/run/proftpd.pid

### BEGIN INIT INFO
# Provides: proftpd ftpserver
# Required-Start: $local_fs $network $named $remote_fs
# Required-Stop: $local_fs $network $named $remote_fs
# Short-Description: ProFTPd FTP Server
# Description: ProFTPd is an enhanced FTP server with a focus towards
# simplicity, security, and ease of configuration.
# It features a very Apache-like configuration syntax,
# and a highly customizable server infrastructure,
# including support for multiple 'virtual' FTP servers,
# anonymous FTP, and permission-based directory visibility.
### END INIT INFO

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0

[ -x /usr/local/sbin/proftpd ] || exit 0

RETVAL=0

prog="proftpd"

start() {
echo -n $"Starting $prog: "
daemon /usr/local/sbin/proftpd 2>/dev/null
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/proftpd
}

stop() {
echo -n $"Shutting down $prog: "
killproc proftpd
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/proftpd
}

# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status proftpd
RETVAL=$?
;;
restart)
stop
start
;;
try-restart|condrestart)
if [ -f /var/lock/subsys/proftpd ]; then
stop
start
fi
;;
reload|force-reload)
echo -n $"Re-reading $prog configuration: "
killproc proftpd -HUP
RETVAL=$?
echo
;;
*)
echo "Usage: $prog {start|stop|restart|try-restart|reload|status}"
exit 2
esac

exit $RETVAL
=========================================================

SQLLog多增加CLNT指令及記錄(Client端軟體的名稱及版本記錄)
結果對Filezilla無效= =
有效的目前有Smartftp,FlashFXP 3.x
(1)
修改source的include/ftp.h
在C_NOOP下
增加一行
#define C_CLNT "CLNT" /* Returns 200 and does nothing */
(2)
修改source的modules/mod_core.c
增加以下六行(我是加在core_noop這塊之下)
MODRET core_clnt(cmd_rec *cmd) {
char *dir;
dir = pr_fs_decode_path(cmd->tmp_pool, cmd->arg);
pr_response_add(R_200, _("CLNT command successful"));
return PR_HANDLED(cmd);
}
(3)
再修改static int core_init(void) { }
裡面多加兩行
pr_help_add(C_CLNT, "(no operation)", TRUE);
pr_feat_add("CLNT");
(4)
再修改static cmdtable core_cmdtab[] = { }
裡面多加一行(我是加在core_noop下)
{ CMD, C_CLNT, G_DIRS, core_clnt, TRUE, FALSE, CL_DIRS },

增加的部份我習慣放在NOOP的附近(兩個功用相近嘛)

(5)
修改source的contrib/mod_sql.c
在
if (strcmp(cmd->argv[0], C_CDUP) == 0 ||
strcmp(cmd->argv[0], C_CWD) == 0 ||
strcmp(cmd->argv[0], C_MKD) == 0 ||
strcmp(cmd->argv[0], C_RMD) == 0 ||
strcmp(cmd->argv[0], C_XCWD) == 0 ||
strcmp(cmd->argv[0], C_XCUP) == 0 ||
strcmp(cmd->argv[0], C_XMKD) == 0 ||
strcmp(cmd->argv[0], C_XRMD) == 0) {
裡面多加一行(我是加在C_CWD的下一行)
strcmp(cmd->argv[0], C_CLNT) == 0 ||


=========================================================
修正FTP Client登入後看到的檔案時區均為GMT而非系統時間
cp /usr/share/zoneinfo/ROC /usr/share/zoneinfo/CST
重啟proftpd就搞定 :P
感謝藍色小鋪的ku3提供的好方法!