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

7 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, 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!


8 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/proftpd.conf and append the following directives to it:

vi /etc/proftpd/proftpd.conf

<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 READ WRITE>
             <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!)


9 References

Mandrake 10.1 - Proftpd + MySQL authentication + Quotas Howto:


10 Links

Share this page:

4 Comment(s)

Add comment


From: at: 2007-04-27 11:22:42

Dear falko!

Please make virtual_postfix_mysql_quota_courier how to for etch. The last mail how to was great.


From: at: 2008-01-27 17:56:03

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 

From: at: 2008-12-09 09:04:18

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

From: Spiffy at: 2009-02-08 19:18:11

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!