Setting Up ProFtpd with MySQL

Prerequisites : MySQL 5 & PHP 5
Download proftpd source
wget ftp://ftp-stud.fht-esslingen.de/pub/Mirrors/ftp.proftpd.net/distrib/source/proftpd-1.3.1.tar.gz
tar xzvf proftpd-1.3.1.tar.gz
cd proftpd-1.3.1

Configure using:
install_user=root install_group=wheel ./configure --with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql --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

Create the /etc/init.d/proftpd as

#!/bin/sh
# $Id: proftpd.init,v 1.1 2004/02/26 17:54:30 thias Exp $
#
# 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: /etc/proftp.conf
# pidfile: /var/run/proftpd.pid

# 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/sbin/proftpd ] || exit 0

RETVAL=0

prog="proftpd"

start() {
echo -n $"Starting $prog: "
daemon proftpd
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
;;
condrestart)
if [ -f /var/lock/subsys/proftpd ]; then
stop
start
fi
;;
reload)
echo -n $"Re-reading $prog configuration: "
killproc proftpd -HUP
RETVAL=$?
echo
;;
*)
echo "Usage: $prog {start|stop|restart|reload|condrestart|status}"
exit 1
esac

exit $RETVAL

chmod 755 /etc/init.d/proftpd

Create /etc/proftpd.conf file as

ServerName "FTP Server"
ServerType standalone
ServerIdent on "Servers identifying string"
DeferWelcome on
DefaultServer on

DisplayLogin .welcome # Textfile to display on login
DisplayConnect .connect # Textfile to display on connection
DisplayChdir .firstchdir # Textfile to display on first changedir

UseReverseDNS off
IdentLookups off

Port 21
Umask 002
MaxInstances 15
MaxClientsPerHost 3 "Only ٪m connections per host allowed"
MaxClients 30 "Only ٪m total simultanious logins allowed"
MaxHostsPerUser 1

User nobody
Group nobody

ScoreboardFile /var/proftpd/proftpd.scoreboard

AllowStoreRestart on
AllowRetrieveRestart on
RequireValidShell off
PathDenyFilter "\\.ftp)|\\.ht)[a-z]+$"
DefaultRoot ~
DenyFilter \*.*/

# Some logging formats
LogFormat default "٪h ٪l ٪u ٪t \"٪r\" ٪s ٪b"
LogFormat auth "٪v [٪P] ٪h ٪t \"٪r\" ٪s"
LogFormat write "٪h ٪l ٪u ٪t \"٪r\" ٪s ٪b"

# Define log-files to use
TransferLog /var/log/proftpd/proftpd.xferlog
ExtendedLog /var/log/proftpd/proftpd.access_log WRITE,READ write
ExtendedLog /var/log/proftpd/proftpd.auth_log AUTH auth
ExtendedLog /var/log/proftpd/proftpd.paranoid_log ALL default
SQLLogFile /var/log/proftpd/proftpd.mysql

# Set up authentication via SQL
# ===========
AuthOrder mod_sql.c
SQLAuthTypes Backend
#===========================
# CHANGE THE FOLLOWING LINE TO REFLECT YOUR INSTALLATION
#===========================
SQLConnectInfo proftpd_admin@localhost proftpd proftpd
#===========================
SQLUserInfo usertable userid passwd uid gid homedir shell
SQLGroupInfo grouptable groupname gid members
SQLUserWhereClause "disabled=0 and (NOW()<=expiration or expiration=-1 or expiration=0)"

# Log the user logging in
SQLLog PASS counter
SQLNamedQuery counter UPDATE "lastlogin=now(), count=count+1 WHERE userid='٪u'" usertable

# logout log
SQLLog EXIT time_logout
SQLNamedQuery time_logout UPDATE "lastlogout=now() WHERE userid='٪u'" usertable

# display last login time when PASS command is given
SQLNamedQuery login_time SELECT "lastlogin from usertable where userid='٪u'"
SQLShowInfo PASS "230" "Last login was: ٪{login_time}"

# xfer Log in mysql
SQLLog RETR,STOR transfer1
SQLNamedQuery transfer1 INSERT "'٪u', '٪f', '٪b', '٪h', '٪a', '٪m', '٪T', now(), 'c', NULL" xfer_stat
SQLLOG ERR_RETR,ERR_STOR transfer2
SQLNamedQuery transfer2 INSERT "'٪u', '٪f', '٪b', '٪h', '٪a', '٪m', '٪T', now(), 'i', NULL" xfer_stat

# Normally, we want users to do a few things.
<Global>
AllowOverwrite yes
<Limit ALL SITE_CHMOD>
AllowAll
</Limit>
</Global>

mkdir /var/log/proftpd/
Create /etc/logrotate.d/proftpd as
/var/log/proftpd/*log {
compress
missingok
postrotate
test -f /var/lock/subsys/proftpd && /usr/bin/killall -HUP proftpd
endscript
}
/var/log/proftpd/proftpd.mysql {
compress
missingok
postrotate
test -f /var/lock/subsys/proftpd && /usr/bin/killall -HUP proftpd
endscript
}

Dowload proFTPd Administrator (http://proftpd-adm.sourceforge.net/)

cd /var/www/html
tar xzvf proftpd_admin_v1.2.tar.gz
rm proftpd_admin_v1.2.tar.gz
mv proftpd_admin_v1.2/ proftpd_admin/

cd proftpd_admin

edit misc/database_structure_mysql
and change the last 3 lines according to the credentials you have set in /etc/proftpd.conf and then :
mysql -u root -p < misc/database_structure_mysql/db_structure.sql
chmod o+w /var/www/html/configuration.xml
Edit misc/user_script/create_user.sh to
#!/bin/bash
USER=$1
USER_ID=$2
GROUP_ID=$3

mkdir -p --mode=775 /ftp/$USER
chown $USER_ID.$GROUP_ID /ftp/$USER

Then :

chown -R root.root /var/www/html/proftpd_admin/misc/user_script
chmod 700 -R /var/www/html/proftpd_admin/misc/user_script

edit /etc/sudoers files and add the following 2 lines at the end

# Cmnd alias specification
Cmnd_Alias CREATE_USER = /var/www/html/proftpd_admin/misc/user_script/create_user.sh

# User privilege specification
apache ALL=(ALL) NOPASSWD: CREATE_USER

Also make sure that Defaults requiretty is commented out

Securing Installation of Proftpd Admin

vi /etc/httpd/conf.d/dirSecurity.conf

# Secure proftpd_directory both by ip and password
<Directory "/var/www/html/proftpd_admin">
deny from all
Order deny,allow
Allow from <any safe IP>
AuthType Basic
AuthUserFile /etc/httpd/conf/proftpd.htpasswd
AuthName Administration
require valid-user
satisfy All

</Directory>

Then execute

htpasswd -b -c /etc/httpd/conf/proftpd.htpasswd proftp <password>

/etc/init.d/httpd restart
/etc/init.d/mysqld restart
/etc/init.d/proftpd start

And point your browser to http://<server-ip>/proftpd-admin and supply the username & password you created.
Go to the Configure Tab and then the ProFTPd tab and set

Create user command: sudo /var/www/html/proftpd_admin/misc/user_script/create_user.sh