There is a new version of this tutorial available for Debian 5 (Lenny).

Virtual Hosting With Proftpd And MySQL (Incl. Quota) On Debian Etch - Page 2

This tutorial exists for these OS versions

On this page

  1. 5 Configure Proftpd
  2. 6 Populate The Database And Test

5 Configure Proftpd

Open /etc/proftpd/proftpd.conf and disable IPv6 by setting UseIPv6 to off:

vi /etc/proftpd/proftpd.conf
UseIPv6                         off

In the same file, add the following lines:

DefaultRoot ~

# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes            Plaintext Crypt
SQLAuthenticate         users groups

# used to connect to the database
# [email protected] database_user user_password
SQLConnectInfo  [email protected] 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 file
SQLLog  STOR,DELE modified
SQLNamedQuery 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!

Then open /etc/proftpd/modules.conf and comment out the LoadModule mod_sql_postgres.c line:

vi /etc/proftpd/modules.conf
#LoadModule mod_sql_postgres.c

Then restart Proftpd:

/etc/init.d/proftpd restart


6 Populate The Database And Test

To populate the database you can use the MySQL shell:

mysql -u root -p
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):

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 exampleuser with a quota of 15MB and the password secret (we are still in the MySQL shell):

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);
INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, 'exampleuser', 'secret', 2001, 2001, '/home/', '/sbin/nologin', 0, '', '');

(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 or gFTP on a Linux desktop) and try to connect. As hostname you use (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/ (exampleuser's home directory) has been automatically created, and it is owned by ftpuser and ftpgroup (the user/group we created at the end of step two):

server1:~# ls -l /home/
total 12
drwxr-xr-x 2 administrator administrator 4096 2007-04-23 14:25 administrator
drwxr-xr-x 2 ftp           nogroup       4096 2007-04-23 14:50 ftp
drwxr-xr-x 2 ftpuser       ftpgroup      4096 2007-04-23 15:15
Share this page:

Suggested articles

3 Comment(s)

Add comment



Debian comes with some standard users which dont't have a password (displayed as an asterix in /etc/shadow) like the user backup.

[CODE]# cat /etc/shadow /etc/passwd | grep backup

When de parameter SQLAuthTypes includes "Plaintext" the password (*) will be considered a real password and the user can login on your system without any problem using backup / * as username / password combination.

I had to discover this the wrong way by adding the user FTP and find unwanted files on my system within a few weeks time.

The solution:

- Change "SQLAuthTypes Plaintext Crypt" to "SQLAuthTypes Crypt" in the proftpd.conf
- Encrypt the passwords in de MySQL table where the users are stored
- /etc/init.d/proftpd restart 


You might want to add the following lines to your config, to prevent logging in of system accounts.( which happened to me)


SQLMinUserGID 100
SQLMinUserUID 500


gr. Jottie

By: Spiffy

One thing. If you are getting this error:

 421 Service not available, remote server has closed connection

Make sure that you comment out the following in /etc/proftpd/modules.conf:

#LoadModule mod_sql_postgres.c

It took me an hour to figure that one out!