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