Really freakin’ simple mysql virtual hosting pure-ftpd

This article will guide you through installing pure-ftpd configured for virtual hosts using mysql as the authentication mechanism. It assumes you have a functioning Linux server running mysql already.

If you’re lucky enough to use Ubuntu, you can simply run

apt-get install pure-ftpd-mysql

First of we’ll create a table in mysql
`id` int(10) unsigned NOT NULL auto_increment,
`userid` varchar(32) NOT NULL default ”,
`passwd` varchar(32) NOT NULL default ”,
`uid` smallint(6) NOT NULL default ‘1002’,
`gid` smallint(6) NOT NULL default ‘1002’,
`homedir` varchar(255) NOT NULL,
`shell` varchar(16) NOT NULL default ‘/sbin/nologin’,
`count` int(11) NOT NULL default ‘0’,
`accessed` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`modified` datetime NOT NULL default ‘0000-00-00 00:00:00′,
UNIQUE KEY `userid` (`userid`)
Put some data in the table to, you’ll need to put in a userid, passwd and a homedir.
Take note of the uid & gid fields, these will need to match the file system owner of the homedir if your FTP is to succesfully upload files.

I have a user called virtual, with a uid and gid of 1002 which owns all the files under /home/virtualhosts. It does the job fine.

Next create a mysql user that can read that table, remember the password, you’ll need it next.

Once pure-ftpd is installed, edit /etc/pure-ftpd/db/mysql.conf
MYSQLPort 3306
MYSQLSocket /var/run/mysqld/mysqld.sock

MYSQLCrypt cleartext

MYSQLGetPW SELECT passwd FROM ftpuser WHERE userid=”L”
MYSQLGetUID SELECT uid FROM ftpuser WHERE userid=”L”
#MYSQLDefaultUID 1000
MYSQLGetGID SELECT gid FROM ftpuser WHERE userid=”L”
#MYSQLDefaultGID 1000
MYSQLGetDir SELECT homedir FROM ftpuser WHERE userid=”L”

# MySQLGetQTAFS SELECT QuotaFiles FROM users WHERE User=”L”

# MySQLGetRatioUL SELECT ULRatio FROM users WHERE User=”L”
# MySQLGetRatioDL SELECT DLRatio FROM users WHERE User=”L”

# MySQLGetBandwidthUL SELECT ULBandwidth FROM users WHERE User=”L”
# MySQLGetBandwidthDL SELECT DLBandwidth FROM users WHERE User=”L”

# MySQLTransactions On
Some information on that default config file, at a minimum you will need to change the following values, MYSQLUser, MYSQLPassword and MYSQLDatabase so that the server can read your virtual user database in mysql.

I’ve left some options commented out incase you’re interested in using them, such as ratios,bandwidth throttling and quotaing, all of which are outside the scope of this article.

You can change MYSQLCrypt from “ClearText” if you don’t wish your passwords to be stored in clear text.
Valid values are : “cleartext”, “crypt”, “md5” and “password”. See the docs for more information.

Finally, you can turn on Transactions if you wish. Again, see the docs for more information.

One last configuration file, we need to make sure the virtual users are chroot’ed to their virtual home directory.
echo Yes > /etc/pure-ftpd/conf/ChrootEveryone
Now start the server,
/etc/init.d/pure-ftpd-mysql start
You have my guarantee* it will work.
Well, probably!

Author: Kieran Barnes

Kieran is a PHP developer with 15 years commercial experience. Specialist in WordPress, CakePHP, CubeCart and all things PHP.

1 thought on “Really freakin’ simple mysql virtual hosting pure-ftpd”

Leave a Reply

Your email address will not be published. Required fields are marked *