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
Log in to MySQL:
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
