Using MySQL to store your ProFTPD users, makes it much easier to manage them.
Just fire up you favorite MySQL client and add a new user.
Besides making it easy to manage users it also allows for some additional features.
I’ll be showing you how to implement the following:

  • Keeping a login counter
  • Tracking last login and last update activity
  • Creating temporary user accounts

If you need to set up ProFTPD, see my previous post.

Let’s get started

Setup local user and group

Create a local group:
sudo groupadd ftpgroup
You’ll need the group’s ID later on, you can get it by viewing /etc/group
cat /etc/group | grep ftpgroup
The output should be similar to the output below.
ftpgroup:x:1006:

Create a local user:
sudo useradd -s /bin/false -d /bin/null -c "ProFTPD user" -g ftpgroup ftpuser
We also need the user’s ID, which can be found in /etc/passwd
cat /etc/passwd | grep ftpuser
ftpuser:x:1005:1006:ProFTPD user:/bin/null:/bin/false

Installation and configuration

Install the ProFTPD MySQL module:
sudo aptitude install proftpd-mod-mysql

Update the ProFTPD configuration:
sudo vi /etc/proftpd/proftpd.conf
Un-comment the following line
Include /etc/proftpd/sql.conf

Update the modules config to enable the MySQL module:
sudo vi /etc/proftpd/modules.conf
Un-comment the following 2 lines
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

Update the SQL config:
sudo vi /etc/proftpd/sql.conf
The file should conain the following content, update / add where required
<IfModule mod_sql.c>
# Enable the SQL engine
SQLBackend mysql
SQLEngine on
SQLAuthenticate on
# Use a backend-crypted or a crypted password
SQLAuthTypes Backend Crypt
# Connection information
SQLConnectInfo ProFTPD@localhost ProFTPD password
# Describes both users/groups tables, we'll create them later
SQLUserInfo users userid passwd uid gid homedir shell
SQLGroupInfo groups groupname gid members
# Update count every time user logs in
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "login_count=login_count+1, last_accessed=NOW() WHERE userid='%u'" users
# Update modified everytime user uploads or deletes a file
SQLLog STOR,DELE modified
SQLNamedQuery modified UPDATE "last_modified=NOW() WHERE userid='%u'" users
# Make the sure the login account has not expired yet
SQLUserWhereClause "(login_expires IS NULL OR login_expires >= NOW())"
</IfModule>

MySQL setup

Login to your MySQL server using a client of choice.
We’ll need to create a database, user and 2 tables

Set up the database:
CREATE DATABASE `ProFTPD`;
GRANT SELECT, INSERT, UPDATE, DELETE ON `ProFTPD`.* TO 'ProFTPD'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Now make sure you select the correct database before you proceed.
USE `ProFTPD`;

Create the needed tables:
CREATE TABLE `groups` (
`groupname` VARCHAR(16) NOT NULL,
`gid` SMALLINT(6) NOT NULL DEFAULT '1006',
`members` VARCHAR(16) NOT NULL,
INDEX `groupname` (`groupname`)
) ENGINE=MyISAM;
Don’t forget to replace 1006 with the proper group ID

CREATE TABLE `users` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`userid` VARCHAR(32) NOT NULL,
`passwd` VARCHAR(41) NOT NULL,
`uid` SMALLINT(6) NOT NULL DEFAULT '1005',
`gid` SMALLINT(6) NOT NULL DEFAULT '1006',
`homedir` VARCHAR(255) NOT NULL,
`shell` VARCHAR(16) NOT NULL DEFAULT '/bin/bash',
`login_count` INT(11) NOT NULL DEFAULT '0',
`login_expires` DATETIME NULL DEFAULT NULL,
`last_accessed` DATETIME NULL DEFAULT NULL,
`last_modified` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `userid` (`userid`)
) ENGINE=MyISAM;
Don’t forget to replace 1005 and 1006 with the proper user ID and group ID

Insert the data:
INSERT INTO `groups` (`groupname`, `members`) VALUES ('ftpgroup', 'ftpuser');
INSERT INTO `users` (`userid`, `passwd`, `homedir`) VALUES ('username', PASSWORD('password'), '/var/www/virtual_hosts/example.com');

Check the config and restart the ProFTPD server:
sudo service proftpd check-config
“ProFTPd configuration OK”
sudo service proftpd restart

Now you should be able to login using the details provided above.

Credits go out to falko on howtoforge for the original tutorial