Virtual Hosting with PureFTPd and MySQL on Debian

November 7, 2007

Necessary Information

In this HowTo my Server has the follow configurations:
Hostname: ftp01.server.com
IP-Adress: 192.168.1.20
Installing necessary stuff

With this command we can Install all the stuff we need:

apt-get install mysql-server mysql-client libmysqlclient15-dev phpmyadmin apache2 pure-ftpd-mysql

Configuring security stuff

First of all we need to create a password for the MySQL User root.

mysqladmin -u root password OWNPW

REMEMBER TO CHANGE OWNPW with your own password!

Then we need to create a own group for the FTP daemon:

groupadd -g 2001 grftp
useradd -u 2001 -s /bin/false -d /bin/null -c “ftpd user” -g grftp usftp

Create a MySQL Database

Now we can create a MySQL Database. If you like you can do this stuff with phpMyAdmin but I like to do it in the shell.

Log in to MySQL:

mysql -u root -p

Create Database and User:
CREATE DATABASE ftp;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON ftp.* TO ‘ftp’@'localhost’ IDENTIFIED BY ‘ownftppass’;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON ftp.* TO ftp’@'localhost.localdomain’ IDENTIFIED BY ‘ ownftppass ‘;

FLUSH PRIVILEGES;

USE ftp;

REMEMBER TO CHANGE OWNFTPPASS with your own password!

Now we can create the 1 and only table we need:

CREATE TABLE ftp_user (
User varchar(16) NOT NULL default ”,
status enum(’0′,’1′) NOT NULL default ‘0′,
Password varchar(64) NOT NULL default ”,
Uid varchar(11) NOT NULL default ‘-1′,
Gid varchar(11) NOT NULL default ‘-1′,
Dir varchar(128) NOT NULL default ”,
ULBandwidth smallint(5) NOT NULL default ‘0′,
DLBandwidth smallint(5) NOT NULL default ‘0′,
comment tinytext NOT NULL,
ipaccess varchar(15) NOT NULL default ‘*’,
QuotaSize smallint(5) NOT NULL default ‘0′,
QuotaFiles int(11) NOT NULL default 0,
PRIMARY KEY (User),
UNIQUE KEY User (User)
) TYPE=MyISAM;

quit;

Configuring the FTP daemon

The only configuration File we need to change is /etc/pure-ftpd/db/mysql.conf .

First of all let us back up the original file:

cp /etc/pure-ftpd/db/mysql.conf /etc/pure-ftpd/db/mysql.conf_bkp

Now open /etc/pure-ftpd/db/mysql.conf . with your favourite editor. Expl:

vi /etc/pure-ftpd/db/mysql.conf

And let it look like this:

MYSQLSocket /var/run/mysqld/mysqld.sock
MYSQLServer localhost
#MYSQLPort 3306
MYSQLUser ftp
MYSQLPassword ownftppass
MYSQLDatabase ftp
#MYSQLCrypt md5, cleartext, crypt() or password() - md5 is VERY RECOMMENDABLE uppon cleartext
MYSQLCrypt md5
MYSQLGetPW SELECT Password FROM ftp_user WHERE User=”\L” AND status=”1″ AND (ipaccess = “*” OR ipaccess LIKE “\R”)
MYSQLGetUID SELECT Uid FROM ftp_user WHERE User=”\L” AND status=”1″ AND (ipaccess = “*” OR ipaccess LIKE “\R”)
MYSQLGetGID SELECT Gid FROM ftp_user WHERE User=”\L”AND status=”1″ AND (ipaccess = “*” OR ipaccess LIKE “\R”)
MYSQLGetDir SELECT Dir FROM ftp_user WHERE User=”\L”AND status=”1″ AND (ipaccess = “*” OR ipaccess LIKE “\R”)
MySQLGetBandwidthUL SELECT ULBandwidth FROM ftp_user WHERE User=”\L”AND status=”1″ AND (ipaccess = “*” OR ipaccess LIKE “\R”)
MySQLGetBandwidthDL SELECT DLBandwidth FROM ftp_user WHERE User=”\L”AND status=”1″ AND (ipaccess = “*” OR ipaccess LIKE “\R”)
MySQLGetQTASZ SELECT QuotaSize FROM ftp_user WHERE User=”\L”AND status=”1″ AND (ipaccess = “*” OR ipaccess LIKE “\R”)
MySQLGetQTAFS SELECT QuotaFiles FROM ftp_user WHERE User=”\L”AND status=”1″ AND (ipaccess = “*” OR ipaccess LIKE “\R”)

REMEMBER TO CHANGE THE MYSQLPassword option with the right Password you put on â€œCreate MySQL Database #2â€!
Make the FTP daemon secure

First we want to make every user chroot so any user can browse the own directory but not browse directory outside his own home directory.

We can make this with one simple command:

echo “yes” > /etc/pure-ftpd/conf/ChrootEveryone

If you like you can confirgure pureFTP to create a own home directory on the first login if not exist. You can do this with this command:

echo “yes” > /etc/pure-ftpd/conf/CreateHomeDir

Configure the service

Actually the pureFTP daemon is controlled by inetd but we would like to let it run standalone. So open the /etc/default/pure-ftpd-common File:

vi /etc/default/pure-ftpd-common

And edit the option STANDALONE_OR_INETD to standalone.

Last but not least we need to restart all the stuff:

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

Test the hole System

mysql -u root â€“p

Select the Database:

USE pureftpd;

Create a new User:

INSERT INTO `ftp_user` (`User`, `status`, `Password`, `Uid`, `Gid`, `Dir`, `ULBandwidth`, `DLBandwidth`, `comment`, `ipaccess`, `QuotaSize`, `QuotaFiles`) VALUES (’ftptest’, ‘1′, MD5(’testpw’), ‘2001′, ‘2001′, ‘/home/ftptest’, ‘100′, ‘100′, ”, ‘*’, ‘50′, ‘0′);

Close Connection:

quit;

Now you can test with a FTP Client you like to access the ftp server with the following logindates:
Username: ftptest
Password: testpw

Have fun!
dsmcg.ch

Submit to: