PureFTPd with MySQL on FreeBSD 8.2

Hi all,

In this post we'll see how to hook up PureFTPd to MySQL so we don't have to use the PureDB database that comes with PureFTPd.

Prerequisites:

The previous post onPureFTPd.
MySQL needs to be setup before proceeding

This post has been partly based on this one, this one and this one. Respect.

1. You need to make sure that PureFTPd is compiled with MySQL support! To do that:

cd /usr/ports/ftp/pure-ftpd ;make rmconfig; time make install clean

The 'make rmconfig' part removes any previously used options when building the port - we start fresh.

2. Take the defaults plus options MYSQL, TLS, PERUSERLIMITS, THROTTLING and LARGEFILE

3. For MySQL just take the default option: OPENSSL

Compiling time for those was about 12 minutes.

4. Rehash:

rehash

5. Make sure MySQL is set to start on boot:

echo 'mysql_enable="YES"' >> /etc/rc.conf

6. Make sure MySQL is running:

/usr/local/etc/rc.d/mysql-server start

7. Login to MySQL:

mysql

8. Create a database for PureFTPd:

mysql> create database pureftpd;
Query OK, 1 row affected (0.00 sec)

9. Use the database:

mysql> use pureftpd;
Database changed

10. Create the table to hold all the necessary fields:

CREATE TABLE users (
User varchar(16) NOT NULL default '',
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',
QuotaSize smallint(5) NOT NULL default '0',
QuotaFiles int(11) NOT NULL default 0,
PRIMARY KEY (User),
UNIQUE KEY User (User)
);

11. Add a user:

mysql> INSERT INTO users (User, Password, Uid, Gid, Dir, ULBandwidth, DLBandwidth, QuotaSize, QuotaFiles) VALUES ('weirdmysql', MD5('weirdmysqlpass'), '1001', '1001', '/usr/home/vftp/weirdmysql', '', '', '100', '');                   Query OK, 1 row affected, 2 warnings (0.00 sec)

The above will add user weirdmysql, status 1 (enabled), with password weirdmysqlpass, UID 1001, GID 1001, home dir: /usr/home/vftp/weirdmysql no upload limit, no download limit, no comment, no IP limitations, 100MB quota size, no upload number of files limit.

12. Add a MySQL user for the pureftpd database - in this case the user is called pureftpd, has access only from localhost and is identified by the password 'weirdbricks'

mysql> GRANT ALL ON pureftpd.* TO pureftpd@localhost IDENTIFIED BY 'weirdbricks';
Query OK, 0 rows affected (0.00 sec)

13. Now exit MySQL and go to the PureFTPd configuration directory:

cd /usr/local/etc/

14. Copy the pureftpd-mysql-conf.sample file to pureftpd-mysql.conf so that PureFTPd can use it:

cp pureftpd-mysql.conf.sample pureftpd-mysql.conf

15. Before we take a look at the pureftpd-mysql.conf file it's important to notice that pureftpd-mysql.conf doesn't replace pure-ftpd.conf, it merely holds the necessary MySQL settings. Now, let's take a look at the pureftd-mysql.conf file

ee pureftpd-mysql.conf

16. Let's examine this line by line:

Line 12:

# MYSQLServer     127.0.0.1

In our case our server is running locally on 127.0.0.1 - change this to:

MYSQLServer     127.0.0.1

17. Line 17:

# MYSQLPort       3306

The MySQL port MySQL listens to - in our case 3306, so we just uncomment the line:

MYSQLPort       3306

18. Line 28 - we change this to the MySQL user we created on step 12 pureftpd

MYSQLUser       root

to:

MYSQLUser       pureftpd

19. Line 32 - we change the password to the MySQL user we created on step 12

MYSQLPassword   rootpw

to:

MYSQLPassword   weirdbricks

20. Line 37 - this is the database PureFTPd will use - we used the exact same name so you can leave it as is:

MYSQLDatabase   pureftpd

21. Line 45 - This sets how the passwords are stored - in our case it's md5

MYSQLCrypt      cleartext

to:

MYSQLCrypt      md5

22. Line 63 - This is the MySQL command that will get the user password, this doesn't need to be changed since the MySQL table we created is actually called 'users'.

MYSQLGetPW      SELECT Password FROM users WHERE User='L'

23. Line 68 - This picks the UID (user ID) - leave as is

MYSQLGetUID     SELECT Uid FROM users WHERE User='L'

24. Line 78 - This picks the GID (group ID) - leave as is

MYSQLGetGID     SELECT Gid FROM users WHERE User='L'

25. Line 88 - This picks the user home dir - leave as is

MYSQLGetDir     SELECT Dir FROM users WHERE User='L'

26. Line 94 - This picks the number of files - quota files - it needs to be uncommented

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

to:

MySQLGetQTAFS  SELECT QuotaFiles FROM users WHERE User='L'

27. Line 101 - This picks the quota size (how many MB the user is allowed to upload - needs to be uncommented:

# MySQLGetQTASZ  SELECT QuotaSize FROM users WHERE User='L'

to:

MySQLGetQTASZ  SELECT QuotaSize FROM users WHERE User='L'

28. Lines 114-115 - select the Upload/Download ratio need to be uncommented:

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

to:

MySQLGetBandwidthUL SELECT ULBandwidth FROM users WHERE User='L'
MySQLGetBandwidthDL SELECT DLBandwidth FROM users WHERE User='L'

29. Exit and save

30. Edit pure-ftpd.conf to let it know we're going to be using MySQL instead of PureDB:

Go to line 116:

# MySQLConfigFile               /etc/pureftpd-mysql.conf

Change it to:

MySQLConfigFile               /usr/local/etc/pureftpd-mysql.conf

31. Now go to line 126:

PureDB                        /usr/local/etc/pureftpd.pdb

Comment it out:

#PureDB                        /usr/local/etc/pureftpd.pdb

32. Exit and save

33. Restart PureFTPd

/usr/local/etc/rc.d/pure-ftpd restart

34.Use FileZilla and try logging in as weirdmysql with password weirdpass

Don't be surprised if you get an error the first time you try, you might need a couple of attempts at the configuration to get it right:

35. Let's have a look at the logs:

tail -f /var/log/xferlog