Configuring Proftp Server with Mysql and Quota Support

Proftp Server with Mysql and Quota Support

This article describes how to install a Proftpd server that uses virtual users from a MySQL database instead of real system users. This is much more performant and allows to have thousands of ftp users on a single machine. In addition to that we will show the use of quota with this setup.

For the administration of the MySQL database you can use web based tools like phpMyAdmin. phpMyAdmin is a comfortable graphical interface which means you do not have to mess around with the command line.

Install MySQL And phpMyAdmin in Debian

#apt-get install mysql-server mysql-client libmysqlclient12-dev phpmyadmin

You will be asked a few questions:

Enable suExec? <-- Yes

Configuring mysql-server (Install Hints) <-- OK

Which web server would you like to reconfigure automatically? <-- apache, apache2

Do you want me to restart apache now? <-- Yes

Create a password for the MySQL user root (replace yourrootsqlpassword with the password you want to use)

#mysqladmin -u root password yourrootsqlpassword

Install Proftpd With MySQL support

If you want to install proftp without mysql support click here

#apt-get install proftpd-mysql

Run proftpd from inetd or standalone? <-- standalone

Then we create an ftp group ("ftpgroup") and user ("ftpuser") that all our virtual users will be mapped to. Replace the group- and userid 2001 with a number that is free on your system:

#groupadd -g 2001 ftpgroup

#useradd -u 2001 -s /bin/false -d /bin/null -c "proftpd user" -g ftpgroup ftpuser

Create The MySQL Database For Proftpd

Now we create a database called ftp and a MySQL user named proftpd which the proftpd daemon will use later on to connect to the ftp database:

#mysql -u root -p

mysql>create database ftp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost.localdomain' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Replace the string password with whatever password you want to use for the MySQL user proftpd. Still on the MySQL shell, we create the database tables we need

mysql> use ftp;

mysql>CREATE TABLE ftpgroup (
groupname varchar(16) NOT NULL default '',
gid smallint(6) NOT NULL default '5500',
members varchar(16) NOT NULL default '',
KEY groupname (groupname)
) TYPE=MyISAM COMMENT='ProFTP group table';

mysql>CREATE TABLE ftpquotalimits (
name varchar(30) default NULL,
quota_type enum('user','group','class','all') NOT NULL default 'user',
per_session enum('false','true') NOT NULL default 'false',
limit_type enum('soft','hard') NOT NULL default 'soft',
bytes_in_avail int(10) unsigned NOT NULL default '0',
bytes_out_avail int(10) unsigned NOT NULL default '0',
bytes_xfer_avail int(10) unsigned NOT NULL default '0',
files_in_avail int(10) unsigned NOT NULL default '0',
files_out_avail int(10) unsigned NOT NULL default '0',
files_xfer_avail int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

mysql>CREATE TABLE ftpquotatallies (
name varchar(30) NOT NULL default '',
quota_type enum('user','group','class','all') NOT NULL default 'user',
bytes_in_used int(10) unsigned NOT NULL default '0',
bytes_out_used int(10) unsigned NOT NULL default '0',
bytes_xfer_used int(10) unsigned NOT NULL default '0',
files_in_used int(10) unsigned NOT NULL default '0',
files_out_used int(10) unsigned NOT NULL default '0',
files_xfer_used int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

mysql>CREATE TABLE ftpuser (
id int(10) unsigned NOT NULL auto_increment,
userid varchar(32) NOT NULL default '',
passwd varchar(32) NOT NULL default '',
uid smallint(6) NOT NULL default '5500',
gid smallint(6) NOT NULL default '5500',
homedir varchar(255) NOT NULL default '',
shell varchar(16) NOT NULL default '/sbin/nologin',
count int(11) NOT NULL default '0',
accessed datetime NOT NULL default '0000-00-00 00:00:00',
modified datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id),
UNIQUE KEY userid (userid)
) TYPE=MyISAM COMMENT='ProFTP user table';

mysql>quit;

As you may have noticed, with the quit; command we have left the MySQL shell and are back on the Linux shell.

Configure Proftpd

DefaultRoot ~
# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes Plaintext Crypt
SQLAuthenticate users* groups*
# used to connect to the database
# databasename@host database_user user_password
SQLConnectInfo ftp@localhost proftpd password
# Here we tell ProFTPd the names of the database columns in the "usertable"
# we want it to interact with. Match the names with those in the db
SQLUserInfo ftpuser userid passwd uid gid homedir shell
# Here we tell ProFTPd the names of the database columns in the "grouptable"
# we want it to interact with. Again the names match with those in the db
SQLGroupInfo ftpgroup groupname gid members
# set min UID and GID - otherwise these are 999 each
SQLMinID 500
# create a user's home directory on demand if it doesn't exist
SQLHomedirOnDemand on
# Update count every time user logs in
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser
# Update modified everytime user uploads or deletes a fileSQLLog STOR,DELE modifiedSQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser
# User quotas
# ===========
QuotaEngine on
QuotaDirectoryTally on
QuotaDisplayUnits Mb
QuotaShowQuotas on
SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"
SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, files_out_used, files_xfer_used FROM ftpquotatallies WHERE name = '%{0}' AND quota_type = '%{1}'"
SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, files_out_used = files_out_used + %{4}, files_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" ftpquotatallies
SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" ftpquotatallies
QuotaLimitTable sql:/get-quota-limit
QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally
RootLogin off
RequireValidShell off

Make sure that you replace the string password with the real password for the MySQL user proftpd in the line SQLConnectInfo!

Restart Proftpd:

#/etc/init.d/proftpd restart

Populate The Database And Test

To populate the database you can use the MySQL shell:

#mysql -u root -p

mysql>USE ftp;

First we create an entry in the table ftpgroup. It contains the groupname, the groupid and the username of the ftp group/user we created at the end of step two (replace the groupid appropriately if you use another one than 2001):

mysql>INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 2001, 'ftpuser');

Now we are done with the table ftpgroup. We do not have to create further entries here. Whenever you create a new virtual ftp user, you do this in the tables ftpquotalimits and ftpuser. So let us create our first user (we are still on the MySQL shell):

mysql>INSERT INTO `ftpquotalimits` (`name`, `quota_type`, `per_session`, `limit_type`, `bytes_in_avail`, `bytes_out_avail`, `bytes_xfer_avail`, `files_in_avail`, `files_out_avail`, `files_xfer_avail`) VALUES ('exampleuser', 'user', 'true', 'hard', 15728640, 0, 0, 0, 0, 0);

mysql>INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, 'exampleuser', 'secret', 2001, 2001, '/home/www.example.com', '/sbin/nologin', 0, '', '');
quit;

(Do not forget to replace the groud- and userid 2001 appropriately in the last INSERT statement if you are using other values than in this tutorial!)

Now open your FTP client program on your work station (something like WS_FTP or SmartFTP if you are on a Windows system) and try to connect. As hostname you use server1.example.com (or the IP address of the system), the username is exampleuser, and the password is secret.

If you are able to connect - congratulations! If not, something went wrong.

Now, if you run

ls -l /home

you should see that the directory /home/www.example.com (exampleuser's home directory) has been automatically created, and it belongs to ftpuser and ftpgroup (the user/group we created at the end of step two).

To leave the MySQL shell, type

mysql>quit;

Database Administration

For most people it is easier if they have a graphical front-end to MySQL; therefore you can also use phpMyAdmin (in this example under http://youripaddress/phpmyadmin/) to administrate the ftp database.

Whenever you create a new user, you only have to create entries in the tables ftpquotalimits and ftpuser so we will explain the columns of these tables here

ftpuser Table:

The important columns are these (the others are handled by MySQL or Proftpd automatically, so do not fill these manually!):

userid: The name of the virtual Proftpd user (e.g. exampleuser).

passwd: The unencrypted (i.e., clear-text) password of the user.

uid: The userid of the ftp user you created at the end of step two (e.g. 2001).

gid: The groupid of the ftp group you created at the end of step two (e.g. 2001).

homedir: The home directory of the virtual Proftpd user (e.g. /home/www.test.com). If it does not exist, it will be created when the new user logs in the first time via FTP. The virtual user will be jailed into this home directory, i.e., he cannot access other directories outside his home directory.

shell: It is ok if you fill in /sbin/nologin here by default.

ftpquotalimits Table:

The important columns are these (the others are handled by MySQL or Proftpd automatically, so do not fill these manually!):

name: The name of the virtual Proftpd user (e.g. exampleuser).

quota_type: user or group. Normally, we use user here.

per_session: true or false. true means the quota limits are valid only for a session. For example, if the user has a quota of 15 MB, and he has uploaded 15 MB during the current session, then he cannot upload anything more. But if he logs out and in again, he again has 15 MB available. false means, that the user has 15 MB at, no matter if he logs out and in again.

limit_type: hard or soft. A hard quota limit is a never-to-exceed limit, while a soft quota can be temporarily exceeded. Normally you use hard here.

bytes_in_avail: Upload limit in bytes (e.g. 15728640 for 15 MB). 0 means unlimited.

bytes_out_avail: Download limit in bytes. 0 means unlimited.

bytes_xfer_avail: Transfer limit in bytes. The sum of uploads and downloads a user is allowed to do. 0 means unlimited.

files_in_avail: Upload limit in files. 0 means unlimited.

files_out_avail: Download limit in files. 0 means unlimited.

files_xfer_avail: Tranfer limit in files. 0 means unlimited.

The ftpquotatallies table is used by Proftpd internally to manage quotas so you do not have to make entries there