Virtual Hosting With vsftpd And MySQL On Debian Etch

Version 1.0
Author: Falko Timme
Last edited 06/23/2007

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 tutorial is based on Debian Etch (Debian 4.0). You should already have set up a basic Debian Etch system, as described in the first six chapters of this tutorial: http://www.howtoforge.com/perfect_setup_debian_etch

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

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

mysqladmin -u root password yourrootsqlpassword

Then check with

netstat -tap | grep mysql

on which addresses MySQL is listening. If the output looks like this:

tcp        0      0 localhost.localdo:mysql *:*                     LISTEN     2713/mysqld

which means MySQL is listening on localhost.localdomain only, then you're safe with the password you set before. But if the output looks like this:

tcp        0      0 *:mysql *:*                     LISTEN     2713/mysqld

you should set a MySQL password for your hostname, too, because otherwise anybody can access your database and modify data:

mysqladmin -h server1.example.com -u root password yourrootsqlpassword

 

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';
FLUSH PRIVILEGES;

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!):

USE vsftpd;

CREATE TABLE `accounts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) NOT NULL ,
`pass` VARCHAR( 50 ) NOT NULL ,
UNIQUE (
`username`
)
) ENGINE = MYISAM ;

quit;

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.

Share this page:

15 Comment(s)

Add comment

Comments

From: xyz at: 2012-06-08 15:11:20

# sudo apt-get install vsftpd libpam-mysql mysql-server mysql-client phpmyadmin
E: Could not get lock /var/lib/dpkg/lock - open (11: Resource temporarily unavailable)
E: Unable to lock the administration directory (/var/lib/dpkg/), is another process using it?

while i wrote dis command is gives me error..how to solve it?plz giv me suggestion.

From: Alberto at: 2009-04-21 05:10:36

sorry for my bad english.

i had the same problem, but a could fix it.

in /etc/pam.d/vsftpd, the encryption maybe be wrong, I use the crypt=0 and start to work.

 again, sorry for my bad english.

 

Ó en español.

En el archivo /etc/pam.d/vsftpd, en vez de usar crypt=2, usa crypt=0, al parecer existe un error con la encriptacion de las contraseñas.

De igual forma, o en mi caso, tampoco utilize la funcion de "password" en la instruccion:

INSERT INTO accounts (username, pass) VALUES('testuser', PASSWORD('secret'));

quedando:

INSERT INTO accounts (username, pass) VALUES('testuser', 'secret'); 

Talvez por que se realiza una doble encriptacion: cuando se recive la contraseña y PAM la encripta y verifica con mysql, la cual  ya se encuentra encriptada... solo una idea.

espero ayude a alguien, disculpen si no lo posteo en su totalidad en ingles, pero la verdad soy un poco malo para ello.

Excelente tutorial y saludos desde Monterrey, Mexico.

From: gunavara at: 2009-04-13 16:26:54

Hi, i did everything exactly how it was written but i keep getting "login failed" when i try to log in with the testuser and the password secret.. any ideas ?

From: at: 2007-09-27 18:09:57

You may want to change the following:

 CREATE TABLE `accounts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) NOT NULL ,
`pass` VARCHAR( 50 ) NOT NULL ,
UNIQUE (
`username`
)
) ENGINE = MYISAM ;

To:

CREATE TABLE `accounts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) BINARY NOT NULL ,
`pass` VARCHAR( 50 ) NOT NULL ,
UNIQUE (
`username`
)
) ENGINE = MYISAM ;

To prevent this: 

 Connected to localhost (127.0.0.1).
220 (vsFTPd 2.0.5)
Name (localhost:nunya): testuser
331 Please specify the password.
Password:
230 Login successful.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> quit
221 Goodbye.

Connected to localhost (127.0.0.1).
220 (vsFTPd 2.0.5)
Name (localhost:bizniss): Testuser
331 Please specify the password.
Password:
500 OOPS: cannot change directory:/home/virtual/Testuser
Login failed.
ftp>

From: gniady at: 2009-03-25 14:11:58

HI. I was also need do symbolic links to outside directory. I found solution on that link: http://www.ducea.com/2006/07/27/allowing-ftp-access-to-files-outside-the-home-directory-chroot/

Solution is mount external directory on home dir. sorry for my bad english :)

From: fuzionhead at: 2009-02-08 23:41:05

This was a good tutorial.  I got it working with Ubuntu 8.10 server. I also used kabewm's suggestion (in comments).  No problem.   I also added a couple more fields to the table: Lastname, Firstname, comments, and email.  Just for user tracking purposes...

 The one thing that I had hoped to do but so far have been unable, is to have the user default to their home dir or a folder off of their home dir (like /home/user/www).  I tried symbolic links and that didn't work and I'm not seeing an option to follow sym links in the man page for vsftpd.conf.  If I find the solution, I will post it here.

From: Sanjeev at: 2009-03-15 23:50:52

Hi:

 

Thanks for the tutorial. I followed through the steps to setup a ftp server with virtual user support on my ubuntu desktop machine. After completing the steps, when I start/restart the server, it gives me

" * Starting FTP server: vsftpd                                           [ OK ] "

message, but

"ps -ef | grep vsftpd | grep -v grep"

gives me no output. Also, I fail to login to the ftp server (obviously).

My guess is that vsftpd is not starting up due to some reason. Can someone please tell me which log to look into to see what might be the trouble? I have checked and rechecked my config, nothing seems to be wrong :-(.

Please help!

From: noqqe at: 2010-02-24 12:13:58

if you like to use fail2ban, too,  you got to change the standard vsftpd-fail2ban configuration.

jail.local:

[vsftpd]

enabled  = true
port     = ftp
filter   = vsftpd
logpath  = /var/log/vsftpd.log
maxretry = 3

and  change the standard filter for vsftpd

/etc/fail2ban/filter.d/vsftpd:

# Old Entry for pam auth.log
# failregex = vsftpd: \(pam_unix\) authentication failure; .* rhost=<HOST>(?:\s+user=\S*)?\s*$

# New failregex for mysqlauth in vsftpd.log
failregex = .* FAIL LOGIN: Client \"<HOST>\"$

From: Anonymous at: 2010-02-28 16:56:53

Hello,

thank you for this how to. t's the first tath I create an ftp server and I use linux. I configured the ftp and work very well. My question is: if I want a user "pippo" can read only folder pippo, how can apply this?


From: Planet.Admon.org at: 2010-04-20 07:23:10

As a famous ftp daemon, it's really a pity that it has poor logs generated, only show you lots of 530 error. Even with the help of strace, you cannot see more details errors as well ...

From: Fran Quero at: 2011-03-02 22:39:02

Great HowTo. Tested and working in Debian Squeeze stable.

Regards

From: at: 2011-10-27 14:35:18

Thank you for creating this great tutorial.

Generally everything written in this tutorial went smooth except that I was not able to logon with an ftp-client.

After studying some logfiles I found some entries in  /var/log/auth.log:

vsftpd: PAM unable to dlopen(/lib/security/pam_mysql.so): /lib/security/pam_mysq
vsftpd: PAM adding faulty module: /lib/security/pam_mysql.so

Since my ftp machine (ubuntu 10.04) was actually set up from scratch the pam_mysql.so  module was missing and I installed it using:

sudo aptitude install libpam-mysql

Finally I think I had to restart vsftpd - but I'm not sure if this was really necessary.

Maybe that could help other users to solve their problems.

From: Lilltonka at: 2012-05-28 13:42:27

Hi, and thank's for a great guide, used it on my Squeeze installation successfully.

 However, i'm having trouble getting TLS to work with this setup. Anyone know how would i go about to set it up with the above setup?

 

Thanks again!

From: KarolinaJ at: 2012-07-18 17:35:36

First of all, thank you for great tutorial, but I have a problem and don't know how to solve it. 

I finished installing my server, I checked everything and seems like there wasn't any mistakes, but when I try to connect to ftp via explorer, it can not connet to server.

It writes:  "Windows can not acces this folder. Make sure that you typed the file name correctly and that you have permission to acces this folder. A connection with servr can not be estableshed." 

My configuration file is exactly the same like in this tutorial. This problem appears only when vsftpd.conf command  anonymous_enable is set to NO

 Do you have any ideas what could be wrong and what should I check?? 

From: Norbert Crettol at: 2013-08-23 15:24:31

Thank you for this clear and complete instructions.

I've just run into a problem: if you've a # in your mysql user password, the rest of the line in /etc/pam.d/vsftpd will be ignored.

Example:

auth required pam_mysql.so user=vsftpd passwd=My#Passwd123 host=localhost db=ftpusers table=accounts usercolumn=username passwdcolumn=passwd crypt=2
account required pam_mysql.so user=vsftpd passwd=My#Passwd123 host=localhost db=ftpusers table=accounts usercolumn=username passwdcolumn=passwd crypt=2

will fail with

vsftpd: pam_mysql - required option "db" is not set

in /var/log/auth.log

Changing the vsftpd@localhost password in mysql with another without any # solves the problem.

 Norbert