How to install Virtual Hosting FTP server with PureFTPd and MySQL

From Cosmin's Wiki

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.

Environment

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

Prerequisites

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;
 
> CREATE TABLE users 
(
	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:

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"
MYSQLGetUID     SELECT Uid FROM users WHERE User="\L"
MYSQLGetGID     SELECT Gid 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');
>quit;

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.