Manage Pure-FTPd Users with MySQL

A very nice thing about Pure-FTPd is its ability to connect to a database to access user accounts.

After you installed PureFTPd on Ubuntu or CentOS, you can proceed to connect it to MySQL (we will use MariaDB for this example but it’s pretty much the same thing).

First, let’s login to MySQL/MariaDB and create the database and user for Pure-FTPd. For this test the database will be called ftpd and the user vhosts:

mysql -u root -p
CREATE DATABASE ftpd;
DROP USER 'vhosts'@'localhost';
CREATE USER 'vhosts'@'localhost' IDENTIFIED BY 'password-to-change';
GRANT SELECT ON ftpd.* TO 'vhosts'@'localhost';
FLUSH PRIVILEGES;

Now, still in MySQL, let’s create a table in this database:

USE ftpd;
CREATE TABLE users ( user varchar(30) NOT NULL, password varchar(64)
NOT NULL, home varchar(128) NOT NULL, bandwidth_limit_upload
smallint(5) NOT NULL default 0, bandwidth_limit_download smallint(5)
NOT NULL default 0, ip_allow varchar(15) NOT NULL default 'any', quota
smallint(5) NOT NULL default '0', quota_files int(11) NOT NULL default
0, active enum('yes','no') NOT NULL default 'yes', PRIMARY KEY (user),
UNIQUE KEY User (user) ) ENGINE=MyISAM;

And add an FTP user called mrWeb which will have access to the folder /var/www (and then quit MySQL):

INSERT INTO users (user, password, home) VALUES ('mrWeb',MD5('other-password-to-change'), '/var/www');
quit;

Now, we will edit the configuration file and tell Pure-FTPd to load the extra file with MySQL-related setup. We will also backup the Pure-FTPd MySQL config file and replace it with a new file. There are minor differences between Ubuntu and CentOS

sudo vi /etc/pure-ftpd/pure-ftpd.conf 

Add this line to load the MySQL configuration (add the line for your Linux distro)

MySQLConfigFile              /etc/pure-ftpd/db/mysql.conf # Ubuntu 20.04
MySQLConfigFile              /etc/pure-ftpd/pureftpd-mysql.conf # CentOS 8

Now, let’s backup this file and configure it:

# Ubuntu 20.04
sudo mv /etc/pure-ftpd/db/mysql.conf /etc/pure-ftpd/db/mysql.conf.bak
sudo vi /etc/pure-ftpd/db/mysql.conf
# CentOS 8
sudo mv /etc/pure-ftpd/pureftpd-mysql.conf /etc/pure-ftpd/pureftpd-mysql.conf.bak
sudo vi /etc/pure-ftpd/pureftpd-mysql.conf

Change its contents to this (good thing you have a backup):

MYSQLServer     127.0.0.1
MYSQLPort       3306
MYSQLSocket     /var/lib/mysql/mysql.sock
MYSQLUser       vhosts
MYSQLPassword   password-to-change
MYSQLDatabase   ftpd
MYSQLCrypt      md5
MYSQLDefaultUID 2001
MYSQLDefaultGID 2001
MYSQLGetPW SELECT password FROM users WHERE user = "\L" AND active ="yes" AND (ip_allow = "any" OR ip_allow LIKE "\R")
MYSQLGetDir SELECT home FROM users WHERE user = "\L" AND active = "yes" AND (ip_allow = "any" OR ip_allow LIKE "\R")
MySQLGetBandwidthUL SELECT bandwidth_limit_upload FROM users WHERE user = "\L" AND active = "yes" AND (ip_allow = "any" OR ip_allow LIKE "\R")
MySQLGetBandwidthDL SELECT bandwidth_limit_download FROM users WHERE user = "\L" AND active = "yes" AND (ip_allow = "any" OR ip_allow LIKE "\R")
MySQLGetQTASZ SELECT quota FROM users WHERE user = "\L" AND active ="yes" AND (ip_allow = "any" OR ip_allow LIKE "\R")
MySQLGetQTAFS SELECT quota_files FROM users WHERE user = "\L" AND active = "yes" AND (ip_allow = "any" OR ip_allow LIKE "\R")

And restart the FTP server:

sudo systemctl restart pure-ftpd-mysql.service # Ubuntu 20.04
sudo systemctl restart pure-ftpd.service # CentOS 8

And now test from your favorite FTP client (I would bet this is FileZilla) using the username mrWeb and password you used instead of other-password-to-change 🙂

This tutorial is based on information I learned earlier from different sources but mostly from here. So, thank you, Falko Timme!