Virtual Hosting With Proftpd And MySQL (Incl. Quota) - Page 3

5.1 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 to administrate the ftp database.

Whenever you create a new user, you only have to create entries in the tables ftpquotalimits and ftpuser so I 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/ 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!

6 Anonymous FTP

If you want to create an anonymous ftp account (an ftp account that everybody can login to without a password), you can do it like this:

First we create a user and group with the name anonymous_ftp. The user has the home directory /home/anonymous_ftp:

groupadd -g 2002 anonymous_ftp
useradd -u 2002 -s /bin/false -d /home/anonymous_ftp -m -c "Anonymous FTP User" -g anonymous_ftp anonymous_ftp

(Replace 2002 with a group-/userid that is free on your system.)

Then we create the directory /home/anonymous_ftp/incoming which will allow anonymous users to upload files:

mkdir /home/anonymous_ftp/incoming
chown anonymous_ftp:nogroup /home/anonymous_ftp/incoming

And finally, open /etc/proftpd.conf and append the following directives to it:

<Anonymous ~anonymous_ftp>
User anonymous_ftp
Group nogroup
# We want clients to be able to login with "anonymous" as well as "ftp"
UserAlias anonymous anonymous_ftp
# Cosmetic changes, all files belongs to ftp user
DirFakeUser on anonymous_ftp
DirFakeGroup on anonymous_ftp

RequireValidShell off

# Limit the maximum number of anonymous logins
MaxClients 10

# We want 'welcome.msg' displayed at login, and '.message' displayed
# in each newly chdired directory.
DisplayLogin welcome.msg
DisplayFirstChdir .message

# Limit WRITE everywhere in the anonymous chroot
<Directory *>
<Limit WRITE>

# Uncomment this if you're brave.
<Directory incoming>
# Umask 022 is a good standard umask to prevent new files and dirs
# (second parm) from being group and world writable.
Umask 022 022
<Limit STOR>


Finally restart Proftpd:

/etc/init.d/proftpd restart

Now anonymous users can login, and they can download files from /home/anonymous_ftp, but uploads are limited to /home/anonymous_ftp/incoming (and once a file is uploaded into /home/anonymous_ftp/incoming, it cannot be read nor downloaded from there; the server admin has to move it into /home/anonymous_ftp first to make it available to others).

(Please note: You can only have one anonymous ftp account per IP address!)


Mandrake 10.1 - Proftpd + MySQL authentication + Quotas Howto:





Share this page:

12 Comment(s)

Add comment


From: nonsparker at: 2006-09-30 21:54:03

I was struggling with setting up an ftp server and this how-to made it very easy. I love this site and I hope you guys keep it up.

From: Rautamiekka at: 2012-04-09 22:37:49

This guide is old, meaning parts don't work anymore or are deprecated.

1) The SQL codes aren't working.

2) At least SQLHomedirOnDemand is deprecated.

3) The packages have other names.

From: DavidW at: 2006-10-18 15:03:38

Martin Mrajca's contribution to add a banner with report of the user quota to the LIST command is useful, but his solution suffers from two flaws. If the quota is 0 (signified by bytes_in_avail = 0), it reports nonsense, also it uses more database queries than are necessary.

These two flaws are fixed by using the following instead:

SQLNamedQuery userquota SELECT "IF ((SELECT (@availmbytes:=ROUND((`bytes_in_avail`/1048576),2)) FROM `ftpquotalimits` WHERE `name`='%u') = 0, \"No user quota applies.\", CONCAT(\"User quota: Used \", (SELECT (@usedmbytes:=ROUND((`bytes_in_used`/1048576),2)) FROM `ftpquotatallies` WHERE `name`='%u'), \"MB from \", @availmbytes, \"MB. You have \", ROUND(@availmbytes-@usedmbytes,2), \"MB available space.\"))"

SQLShowInfo   LIST    "226" "%{userquota}"


This makes use of MySQL user defined variables to store the results of the two queries, so that the results can be reused. This works for me with MySQL 5.0; I think it should work against MySQL 4.x but I'm not promising.

From: at: 2007-02-25 18:15:59

While running through this howto, I noticed two things that needed rectification in Ubuntu Edgy (6.10)

Firstly, you will need to uncomment the PostgreSQL module in /etc/proftpd/modules.conf when using MySQL. There is some silent conflict there that immediately disconnects users without even attempting to connect to MySQL when the PostgreSQL module is loaded alongside the PostgreSQL module.

Secondly,  there is the following warning:

 "SQLAuthenticate: use of * in SQLAuthenticate has been deprecated.  Use AuthOrder for setting authoritativeness"

 To rectify this, simply comment out that line, and add a line like the following:

AuthOrder mod_sql.c

Which will only allow logins for users in the MySQL database. See this link on AuthOrder for more information:

Great HowTO, by the way :) 

From: Anonymous at: 2005-11-10 07:35:49

First of all, nice article! Shows you how one should do it the right way! :)

I would like to point out that there's a nice webbased admin tool for doing just what you need, like adding users, chaning passwords, etc.. It's in an early stage, but more then usefull. (there used to be a tool like myftpadmin, but this project seems to have died..)

Here's PPMy :


From: Anonymous at: 2005-12-03 23:01:20

thank you for this quite good article. it's easy to understand and it seems to be complete...and finally, it really works :) kay (

From: Anonymous at: 2005-12-13 04:40:40

YAY thanks for the easy to follow guide :)



From: Anonymous at: 2005-12-27 16:58:28

It is extremely useful ! Thanks alot!

From: Anonymous at: 2006-08-15 02:47:40

I tested ISPConfig in VMWare and I saw that when you upload files into incoming directory, those files automatically get "hidden". That's good to avoid overwriting incoming files.

You will get that if you limit read permissions in incoming directory:
chmod 331 /home/anonymous_ftp/incoming

Thank you for this guide :) 

From: Conny at: 2008-12-28 16:49:08

No useful for me, I followed the guide and replaced the passwords when needed but I get this when I restart proftpd.

Fatal: unknown configuration directive 'SQLConnectInfo' on line 41 of '/etc/proftpd/proftpd.conf'

  Fatal: unknown configuration directive 'SQLAuthenticate' on line 37 of '/etc/proftpd/proftpd.conf'

  Fatal: unknown configuration directive 'SQLUserInfo' on line 45 of '/etc/proftpd/proftpd.conf'


Seems like everyting in the config file fails :-/



From: Conny at: 2008-12-28 17:17:25

As I wrote before i had errors starting proftpd, don't forget to uncomment the sql modules in /etc/proftpd/modules.conf

But I still have problems connecting with the 'exampleuser' and passwd 'secret'.


I will reboot the system and try again.


From: Jon at: 2011-09-22 01:16:25

I couldn't get this setup to work but found this one, which is similar and it worked: