How to install Virtual Hosting FTP server with PureFTPd and MySQL

From Cosmin's Wiki

Jump to: navigation, search

Home > Linux how to's > How to install Virtual Hosting FTP server with PureFTPd and MySQL

Quite often there appears the need of sharing, in a secure manner, some files over the web. Thus, the simplest way is to install a FTP server, create some user accounts on it (virtual ones, as we don't want to create users on the physical machine for each user) and start sharing files. Moreover, we want to be able to easily create/manage/delete such users - thus we will manage the users and their base directories in a MySQL database, including quota and upload/download bandwidth limits.

This article will show how to install PureFTPd using virtual users from a MySQL database.



This article has been written and tested on a Debian Lenny installation.


I will assume for the remainder of the article that you will already have Apache2 and MySQL installed on your machine. For reference on how to do this please follow the articles Installing Apache and PHP5 and How to install MySQL.

Install PureFTPd With MySQL Support

Under Debian (Lenny) there is a pre-configured pure-ftpd-mysql package available. Install it like this:

> aptitude install pure-ftpd-mysql

Then we create a ftp group (ftpgroup) and user (ftpuser) that all virtual users will be mapped to. Replace the group and userid 2000 with a number that is free on your system:

> groupadd -g 2001 ftpgroup
> useradd -u 2001 -s /bin/false -d /bin/null -c "pureftpd user" -g ftpgroup ftpuser

Create the MySQL database

> myqsl -uroot -p

Once in the mysql command prompt, just type the following:

>create DATABASE pureftpd;
> GRANT SELECT, INSERT, UPDATE, DELETE ON pureftpd.* TO 'pureftpd'@'localhost' IDENTIFIED BY 'ftpdpass';
> USE pureftpd;
	User varchar(16) NOT NULL,
	STATUS enum('0','1') NOT NULL DEFAULT '0',
	Password varchar(64) NOT NULL,
	Uid varchar(11) NOT NULL DEFAULT '-1',
	Gid varchar(11) NOT NULL DEFAULT '-1',
	Dir varchar(128) NOT NULL,
	ULBandwidth smallint(5) NOT NULL DEFAULT '0',
	DLBandwidth smallint(5) NOT NULL DEFAULT '0',
	comment tinytext NOT NULL DEFAULT '',
	ipaccess varchar(15) NOT NULL DEFAULT '*',
	QuotaSize smallint(5) NOT NULL DEFAULT 0,
	QuotaFiles int(11) NOT NULL DEFAULT 0,
	constraint pk_users PRIMARY KEY (User),
	constraint uk_users_user UNIQUE KEY (User)
) engine=myisam;

Description of the columns:

Column Description
user The name of the virtual PureFTPd user
status 0 or 1. 0 means the account is disabled, the user cannot login.
password The password of the virtual user. Make sure you use MySQL's encrypt function to save the password in encrypted form
uid The userid of the ftp user you created at the end of step two (e.g. 2000).
gid The groupid of the ftp group you created at the end of step two (e.g. 2000).
dir The home directory of the virtual PureFTPd 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.
ulbandwidth Upload bandwidth of the virtual user in KB/sec. (kilobytes per second). 0 means unlimited.
dlbandwidth Download bandwidth of the virtual user in KB/sec. (kilobytes per second). 0 means unlimited.
comment You can enter any comment here (e.g. for your internal administration) here. Normally you leave this field empty.
ipaccess Enter IP addresses here that are allowed to connect to this FTP account. * means any IP address is allowed to connect.
quotasize Storage space in MB (not KB, as in ULBandwidth and DLBandwidth!) the virtual user is allowed to use on the FTP server. 0 means unlimited.
quotafiles amount of files the virtual user is allowed to save on the FTP server. 0 means unlimited.

Configuring PureFTPd

Create a backup of the file /etc/pure-ftpd/db/mysql.conf:

> cd /etc/pure-ftpd/db
> cp mysql.conf mysql.conf.orig
> cat /dev/null > mysql.conf
> vim mysql.conf

and make it look like this:

MYSQLSocket      /var/run/mysqld/mysqld.sock
MYSQLUser       ftpuser
MYSQLPassword   ftppassword
MYSQLDatabase   pureftpd
MYSQLCrypt      crypt
MYSQLGetPW      SELECT Password FROM users WHERE User="\L"
MYSQLGetDir     SELECT Dir FROM users WHERE User="\L"
MySQLGetQTAFS  SELECT QuotaFiles FROM users WHERE User="\L"
MySQLGetQTASZ  SELECT QuotaSize FROM users WHERE User="\L"
MySQLGetBandwidthUL SELECT ULBandwidth FROM users WHERE User="\L"
MySQLGetBandwidthDL SELECT DLBandwidth FROM users WHERE User="\L"

Please note the change in the line of MYSQLPassword. We are using password() to encrypt each users password, rather than storing them in clear text.

Next create the file /etc/pure-ftpd/conf/ChrootEveryone which simply contains the string yes, and which will make PureFTPd chroot every virtual user in his home directory so he will not be able to browse directories and files outside his home directory

> echo "yes" > /etc/pure-ftpd/conf/ChrootEveryone

Also create the file /etc/pure-ftpd/conf/CreateHomeDir which again simply contains the string yes. This will make PureFTPd create a user's home directory when the user logs in and the home directory does not yet exist.

> echo "yes" > /etc/pure-ftpd/conf/CreateHomeDir

Now we must configure PureFTPd as a standalone daemon (it is currently controlled by inetd). To do this, we open /etc/default/pure-ftpd-common and change the value of the parameter STANDALONE_OR_INETD to standalone:

> vim /etc/default/pure-ftpd-common

Next, we modify /etc/inetd.conf and comment out the ftp line so that it looks like this:

#:STANDARD: These are standard services.
#ftp    stream  tcp nowait  root    /usr/sbin/tcpd /usr/sbin/proftpd

Now restart Inetd and PureFTPd:

> /etc/init.d/openbsd-inetd restart
> /etc/init.d/pure-ftpd-mysql restart

Testing the installation

Open the mysql command shell and add a new user:

> USE ftpuser;
> INSERT INTO users(User, STATUS, Password, Uid, Gid, Dir, comment) 
  VALUES ('cosmin', 1, encrypt('mypassword'), 2001, 2001, '/home/ftp/cosmin', 'my account');

Thus, for every user you want to grant access to the ftp server, all you have to do is create the corresponding entry in the 'users' table like above. That's it.