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 insert/uncomment the following lines:
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 AUTO_INCREMENT=22 ;
 
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 'ratemal';
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)
 
Here's some additional info about the sql options:
 
SQLAuthTypes
Decides if passwords are stored in an encrypted manner in the database (and which method/backend to use) or in plaintext: 
Crypt Crypt-Verschlüsselung
Plaintext Passwort wird als Klartext in der Datenbank hinterlegt.
Empty Leere Passwörter erlaubt
OpenSSL Angabe im Format '{digest-name}hashed-value'
Backend Authentifizierung mit Hilfe von mySQL's PASSWORD()-Funktion
 
SQLConnectInfo
These are your mysql credentials.
 
SQLUserInfo
This string contains the credentials from which the sql queries are built. First the table name, then its fields.
 
For more documentation on proftpd, please refer to www.proftpd.org/docs/