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

wget http://mesh.dl.sourceforge.net/sourceforge/proftpd-adm/proftpd_admin_v1.2.tar.gz

tar xzvf proftpd_admin_v1.2.tar.gz

rm proftpd_admin_v1.2.tar.gzmv 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