ProFTPd with MySQL Backend on Debian


First of all we install the needed packages:

apt-get install proftpd proftpd-mysql mysql-server

The configuration files of the proftpd server are in /etc/proftpd.

First we edit the file /etc/proftpd/modules.conf and add the following lines as indicated:

LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

Then we should create the mysql database and the table that will contain the proftpd users:

CREATE DATABASE proftpd;
USE proftpd;
CREATE TABLE `ftpuser` (
  `id` int(3) NOT NULL auto_increment,
  `username` varchar(60) character set latin1 collate latin1_bin default NULL,
  `uid` int(11) default NULL,
  `gid` int(11) default NULL,
  `password` varchar(30) default NULL,
  `homedir` varchar(250) default NULL,
  `shell` varchar(60) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Then we need to grant access to this table if you're not connecting as root, in this example we grant access to the table proftpd.ftpuser to user 'ftp' using the password 'guesswhat'. Replace db.example.net with your mySQL server's name or IP or if you stay local, just use 127.0.0.1 instead, to fit your environment:

GRANT ALL ON proftpd.ftpuser TO `ftp`@`db.example.net` IDENTIFIED BY 'guesswhat';
FLUSH PRIVILEGES;

Ok, now that the mysql connection should work, let's change proftpd's main configuration file: /etc/proftpd/proftpd.conf

The last 9 lines are very important – please replace the mysql credentials to fit your environment.

You may also need to create the folder /var/log/proftpd.

Include /etc/proftpd/modules.conf		
UseIPv6 off
ServerName "ftp.example.net"
ServerType standalone
DeferWelcome off
MultilineRFC2228 on
DefaultServer on
ShowSymlinks on
TimeoutNoTransfer 600
TimeoutStalled 600
TimeoutIdle 1200
DisplayLogin welcome.msg
DisplayFirstChdir .message
ListOptions "-l"
DenyFilter *.*/
Port 21
MaxInstances 30
User proftpd
Group nogroup
Umask 022 022
AllowOverwrite on
DefaultRoot ~
TransferLog /var/log/proftpd/xferlog
SystemLog /var/log/proftpd/proftpd.log

<IfModule mod_tls.c>
TLSEngine off
</IfModule>

<IfModule mod_quota.c>
QuotaEngine on
</IfModule>

<IfModule mod_ratio.c>
Ratios on
</IfModule>

<IfModule mod_delay.c>
DelayEngine on
</IfModule>

<IfModule mod_ctrls.c>
ControlsEngine on
ControlsMaxClients 2
ControlsLog /var/log/proftpd/controls.log
ControlsInterval 5
ControlsSocket /var/run/proftpd/proftpd.sock
</IfModule>

<IfModule mod_ctrls_admin.c>
AdminControlsEngine on
</IfModule>

SQLAuthTypes Backend Plaintext Crypt
SQLAuthenticate users*
SQLConnectInfo proftpd@db.example.net ftp guesswhat
SQLDefaultGID 65534
SQLDefaultUID 65534
SQLMinUserGID 100
SQLMinUserUID 500
SQLUserInfo ftpuser username password uid gid homedir shell
SQLLOGFILE /var/log/proftpd/proftpd.sql.log

Now let's start up profttpd: /etc/init.d/proftpd restart

Then try to insert a new user to your database:

mysql −−user=ftp −−password=guesswhat −−host=db.example.net -e "INSERT INTO ftpuser (username,password,homedir,shell) VALUES ('testuser', ENCRYPT('testpasswort'), '/home/testuser', '/bin/bash');"

Then try to connect! (making sure that the home folder exists – in this case /home/testuser)

For more documentation on proftpd, please refer to www.proftpd.org/docs/