Virtual Hosting With vsftpd And MySQL On Debian Squeeze
Author: Falko Timme
Follow me on Twitter
Last edited 08/20/2012
Vsftpd is one of the most secure and fastest FTP servers for Linux. Usually vsftpd is configured to work with system users. This document describes how to install a vsftpd 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.
For the administration of the MySQL database you can use web based tools like phpMyAdmin which will also be installed in this howto. phpMyAdmin is a comfortable graphical interface which means you do not have to mess around with the command line.
This howto is meant as a practical guide; it does not cover the theoretical backgrounds. They are treated in a lot of other documents in the web.
This document comes without warranty of any kind! I want to say that this is not the only way of setting up such a system. There are many ways of achieving this goal but this is the way I take. I do not issue any guarantee that this will work for you!
1 Preliminary Note
In this tutorial I use the hostname server1.example.com with the IP address 192.168.0.100. These settings might differ for you, so you have to replace them where appropriate.
2 Install vsftpd, MySQL And phpMyAdmin
Vsftpd has no built-in MySQL support, therefore we must use PAM to authenticate against the MySQL database. So we install libpam-mysql in addition to vsftpd, MySQL, and phpMyAdmin:
apt-get install vsftpd libpam-mysql mysql-server mysql-client phpmyadmin
You will be asked the following questions:
New password for the MySQL "root" user: <-- yourrootsqlpassword
Repeat password for the MySQL "root" user: <-- yourrootsqlpassword
Web server to reconfigure automatically: <-- apache2
Configure database for phpmyadmin with dbconfig-common? <-- No
3 Create The MySQL Database For vsftpd
Now we create a database called vsftpd and a MySQL user named vsftpd which the vsftpd daemon will use later on to connect to the vsftpd database:
mysql -u root -p
CREATE DATABASE vsftpd;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON vsftpd.* TO 'vsftpd'@'localhost' IDENTIFIED BY 'ftpdpass';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON vsftpd.* TO 'vsftpd'@'localhost.localdomain' IDENTIFIED BY 'ftpdpass';
Replace the string ftpdpass with whatever password you want to use for the MySQL user vsftpd. Still on the MySQL shell, we create the database table we need (yes, there is only one table!):
CREATE TABLE `accounts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) NOT NULL ,
`pass` VARCHAR( 50 ) NOT NULL ,
) ENGINE = MYISAM ;
As you may have noticed, with the quit; command we have left the MySQL shell and are back on the Linux shell.
BTW, (I'm assuming that the hostname of your ftp server system is server1.example.com) you can access phpMyAdmin under http://server1.example.com/phpmyadmin/ (you can also use the IP address instead of server1.example.com) in a browser and log in as the user vsftpd. Then you can have a look at the database. Later on you can use phpMyAdmin to administrate your vsftpd server.
4 Configure vsftpd
First we create a non-privileged user called vsftpd (with the homedir /home/vsftpd) belonging to the group nogroup. We will run vsftpd under this user, and the FTP directories of our virtual users will be in the /home/vsftpd directory (e.g. /home/vsftpd/user1, /home/vsftpd/user2, etc.).
useradd --home /home/vsftpd --gid nogroup -m --shell /bin/false vsftpd
Then we make a backup of the original /etc/vsftpd.conf file and create our own:
cp /etc/vsftpd.conf /etc/vsftpd.conf_orig
cat /dev/null > /etc/vsftpd.conf
The file should have the following contents:
listen=YES anonymous_enable=NO local_enable=YES write_enable=YES local_umask=022 dirmessage_enable=YES xferlog_enable=YES connect_from_port_20=YES nopriv_user=vsftpd chroot_local_user=YES secure_chroot_dir=/var/run/vsftpd pam_service_name=vsftpd rsa_cert_file=/etc/ssl/certs/vsftpd.pem guest_enable=YES guest_username=vsftpd local_root=/home/vsftpd/$USER user_sub_token=$USER virtual_use_local_privs=YES user_config_dir=/etc/vsftpd_user_conf
The configuration options are explained on http://vsftpd.beasts.org/vsftpd_conf.html. The important options for our virtual setup are chroot_local_user, guest_enable, guest_username, user_sub_token, local_root, and virtual_use_local_privs.
With the user_config_dir option you can specify a directory for per-user configuration files that override parts of the global settings. This is totally optional and up to you if you want to use this feature. However, we should create that directory now:
Now we must configure PAM so that it uses the MySQL database to authenticate our virtual FTP users instead of /etc/passwd and /etc/shadow. The PAM configuration for vsftpd is in /etc/pam.d/vsftpd. We make a backup of the original file and create a new one like this:
cp /etc/pam.d/vsftpd /etc/pam.d/vsftpd_orig
cat /dev/null > /etc/pam.d/vsftpd
auth required pam_mysql.so user=vsftpd passwd=ftpdpass host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=pass crypt=2 account required pam_mysql.so user=vsftpd passwd=ftpdpass host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=pass crypt=2
Please make sure that you replace the MySQL password with your own one!
Afterwards, we restart vsftpd:
5 Create The First Virtual User
To populate the database you can use the MySQL shell:
mysql -u root -p
Now we create the virtual user testuser with the password secret (which will be stored encrypted using MySQL's PASSWORD function):
INSERT INTO accounts (username, pass) VALUES('testuser', PASSWORD('secret'));
testuser's homedir is /home/vsftpd/testuser; unfortunately vsftpd doesn't create that directory automatically if it doesn't exist. Therefore we create it manually now and make it owned by the vsftpd user and the nogroup group:
chown vsftpd:nogroup /home/vsftpd/testuser
Now open your FTP client program on your work station (something like FileZilla or FireFTP) and try to connect. As hostname you use server1.example.com (or the IP address of the system), the username is testuser, and the password is secret.
If you are able to connect - congratulations! If not, something went wrong.
6 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://server1.example.com/phpmyadmin/) to administrate the vsftpd database.
Whenever you create or modify a user, make sure that you use MySQL's PASSWORD function to encrypt that user's password. Also, when you create a new virtual user, please don't forget to create that user's homedir on the shell, as shown at the end of the previous chapter.