ProFTPD + MySQL

Over FTP we can validate users stored in a database with MySQL, PostgreSQL, ODBC or SQLite. In this post we’ll configure proftpd a ftp server running in a Debian squeeze that permit authentication with virtual users stored in a MySQL database.





First we have to install the basic package proftpd, mysql server and o module of proftpd to allow the connection to the database:

# aptitude install proftpd mysql-server proftpd-mod-mysql

Now we have to connect to the database:

# mysql -u root -p

Create a new database named ftpusers:

mysql> create database ftpusers;

Grant all the permissions to the user proftpd:

mysql> grant all on ftpusers.* to proftpd@localhost identified by "PASSWORD"

Create the user table and group table and insert test users:

mysql> CREATE TABLE users ( userid varchar(12) NOT NULL, passwd varchar(100) NOT NULL, uid smallint(6) default NULL, gid smallint(6) default NULL, homedir varchar(50) default NULL, shell varchar(20) default '/bin/false');

mysql> CREATE TABLE groups ( groupname varchar(30) NOT NULL, gid int NOT NULL, members varchar(255));

mysql> INSERT INTO users VALUES ( 'usu1', PASSWORD('usu123'), 500, 500, '/var/www/web1', '/bin/false');

mysql> INSERT INTO users VALUES ( 'usu2', PASSWORD('usu123'), 501, 500, '/var/www/web1', '/bin/false');

mysql> INSERT INTO users VALUES ( 'usu1.2', PASSWORD('usu123'), 502, 500, '/var/www/web2', '/bin/false');

mysql> INSERT INTO groups VALUES ( 'ftpusers', 500, 'usu1,usu2,usu1.2');

To exit of the database run

mysql> quit

Edit the proftpd file configuration /etc/proftpd/proftpd.conf with your favourite text editor, uncomment the option DefaultRoot, RequireValidShell and PassivePorts. This options is not essential for the mysql configuration, but it’s recommended:



Add a directive AuthOrder with the value mod_sql.c, it’s used for the authentication with sql database:



Uncomment the entry Include for the file /etc/proftpd/sql.conf for the database configuration file:





Edit the file configuration /etc/proftpd/sql.conf, for the database configuration:

• SQLBackend: the DBMS used.


• SQLEngine: specify how mod_sql will operate, default on (allow user authentication).


• SQLAuthenticate on: configure the behavior of the authentication with the database.


• SQLLogFile: the log file of sql authentication.


• SQLMinUserUID and SQLMinUserGID: the minimum number of the uid and gid users.


• SQLAuthTypes: specify the encryption password of the database.


• SQLConnectInfo: database@machine user password


• SQLUserInfo and SQLGroupInfo: the rows of the tables that contains the users information.

Edit /etc/proftpd/modules.conf:

uncomment the directive LoadModule mod_sql_mysql.c and LoadModule mod_sql.c:



Create the group for the ftp users used in the database:

# groupadd -g 500 ftpusers

Create the tree directories of the ftp home directories with the correct permissions:

# mkdir -p /var/www/web1 # mkdir /var/www/web2 # echo web1 >/var/www/web1/fic1 # echo web2 > /var/www/web2/fic2 # chgrp -R ftpusers /var/www/ # chmod -R 775 /var/www/

Restart the proftpd service and test to connect with any user:

# service proftpd restart