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 [email protected] 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 (
mysql> INSERT INTO users VALUES (
mysql> INSERT INTO users VALUES (
mysql> INSERT INTO groups VALUES (

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: [email protected] 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


Tagged on:             

2 thoughts on “ProFTPD + MySQL

  • May 16, 2012 at 23:26

    I have only one question, what is the best way to assign www-data permissions to files , with the simple fact of uploading a file.

    • May 17, 2012 at 21:32

      Hi! changing the gid of the users, when I insert a new group in the database in this tutorial i used gid 500 in the insert command, you can choose the same gid that www-data in /etc/group file in your system. I hope it is useful.


Leave a Reply


Get every new post delivered to your Inbox

Join other followers: