vsftpd with mysql backend on Debian Squeeze

Hi there
Yesterday I had to set up an ftp server using a mysql database as a storage for the user credentials. The first barrier was to choose the optimal ftp server. Actually I didn’t search long for comparizons between the different options. I just took the one, I empirically thought to be the best. vsftpd is said to be very secure and I had much less problems with it than with proftpd.
I installed it per aptitude. Because we will also need a mysql server and the pam module for mysql later, you can install it all at once:

apt-get install vsftpd mysql-server libpam-mysql

The mysql server will ask you for the mysql root password. vsftpd and the mysql server will be running after you have installed them.

In the next step, you will create a mysql user table. Start the mysql client per:bashmysql -u root -p and type in the root password, you have set before.

The following queries must be executed now:
The first one creates the database and changes the workspace

CREATE DATABASE IF NOT EXISTS system;
USE system;

The second one creates a table for the credentials of ftp users.

CREATE TABLE ftpusers(
  id int(11) NOT NULL auto_increment,
  username varchar(64) NOT NULL,
  password varchar(64) NOT NULL,
  updated timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(id)
);

Only the fields username and password are really necessary, but you should consider to create a field id for a numeric unique id for every user. This id will always be the same for a single user and shall never be changed. This is good design. The field updated is just useful to detect users, who don’t change their passwords.

If you don’t want to access your table with the root user, you should consider to create an other, less privileged user. I did it per SQL in the console:

GRANT SELECT ON system.ftpusers TO [email protected] IDENTIFIED BY 'verygoodpassword';

For testing purposes, we should create a temporary user:

INSERT INTO ftpusers(username, password) VALUES ('testuser', ENCRYPT('testpw'));

If you don’t trust ENCRYPT enough, you could use another algorithm (AES_ENCRYPT is suggested by mysql as the most secure one). Anyways, you should keep your choice in mind for later.

For the next step, we have to configure our vsftpd that he can authentificate against the mysql database via pam. You have to open your /etc/vsftpd.conf and edit/create the following properties:

# our pam file: we'll create this later.
pam_service_name=vsftpd.virtual
# jail the ftp user for security reasons
chroot_local_user=YES
# map external users to a local one
guest_enable=YES
# every external user will be mapped to the local user ftp
guest_username=ftp
# define an alias for virtual users
user_sub_token=$USER
# this alias is now used for a individual home directory for every user
local_root=/var/www/$USER
# give all guests the rights of a local user (This makes sense, because a guest is already mapped to a local user.)
virtual_use_local_privs=YES

By the way: It’s a good idea to specify the nopriv user. Do it by creating a new system user with no privileges:

adduser --system --no-create-home ftpsecure

Then you have to set the config property nopriv_user to ftpsecure.

Here is my whole vsftp config as it is running now:

listen=YES
anonymous_enable=NO
local_enable=YES
write_enable=YES
file_open_mode=0770
local_umask=0007
chmod_enable=YES
dirmessage_enable=YES
use_localtime=YES
xferlog_enable=YES
connect_from_port_20=YES
chown_uploads=YES
chown_username=www-data
nopriv_user=ftpsecure
ftpd_banner=Willkommen bei Bla Blub.
secure_chroot_dir=/var/run/vsftpd/empty
rsa_cert_file=/etc/ssl/private/vsftpd.pem
tcp_wrappers=YES
hide_ids=YES

pam_service_name=vsftpd.virtual
chroot_local_user=YES
guest_enable=YES
guest_username=ftp
user_sub_token=$USER
local_root=/var/www/$USER
virtual_use_local_privs=YES

As I wrote before, the pam_service_name still points to nowhere. Therefore, we’re going to create a file /etc/pam.d/vsftpd.virtual and fill it with the following three lines:

#%PAM-1.0
auth	required	pam_mysql.so user=vsftpd passwd=verygoodpassword host=localhost db=system table=ftpusers usercolumn=username passwdcolumn=password crypt=1
account	required	pam_mysql.so user=vsftpd passwd=verygoodpassword host=localhost db=system table=ftpusers usercolumn=username passwdcolumn=password crypt=1

This means, that we verify against the existence and the authorization of a user in the database named system. The number for the attribute crypt defines the encryption type (0 for no encryption, 1 for crypt, 2 for the mysql PASSWORD function and 3 for MD5).

Now, everything should be settled. You just have to restart your ftp-server with

/etc/init.d/vsftpd restart

I hope it did work and thank you for reading.