FTP authentication using MySQL backend

We’ve learned by now how to install and configure a FTP server using pure-ftpd. We’ve created a local username and managed to login to our FTP Server. In this article we will make additional authentication settings by adding a MySQL back-end. I will not focus on installing and configuring the FTP server because that part has been already covered in the previous article. We will start directly by installing and configuring our MySQL server and we’ll proceed with the configuration of our authentication mechanism.
If you are using the official CentOS repository, type yum install mysql mysql-server and wait for the installation to complete its operation:

Now we’ll need to configure the local firewall to allow MySQL port (3306) on incoming and outgoing connections:
You can verify if the rules were created successfully by typing iptables -L:
We can now start mysql daemon by typing /etc/init.d/mysqld start
The mysqld service must start automatically each time the server is restarted, type chkconfig mysqld on to set the startup mode to automatic on all runlevels:
Execute /usr/bin/mysql_secure_installation and follow all instructions in the wizard. The settings configured here will secure your MySQL server:
Now that a password has been set for the root user, type mysql -u root -p and press Enter. You will be prompted to type the root password:
You can now execute: SELECT User, Host, Password FROM mysql.user; and view all users within your MySQL server:

We will create a new database for our FTP server and then we’ll set permissions for a newly created user to the database. Type CREATE DATABASE ftpserver; to create the database and type show databases; afterwards to view the newly created database:

To create our database username, type the following command:
INSERT INTO mysql.user (User,Host,Password) VALUES(‘ftpuser’,’localhost’,PASSWORD(‘1qaz@WSX’));

Once you’ve created the user type FLUSH PRIVILEGES;
The permissions on our ftpserver database can be added using the following command:

GRANT ALL PRIVILEGES ON ftpserver .* to ftpuser@localhost; Permissions can be viewed by typing SHOW GRANTS FOR ftpuser;

Execute again FLUSH PRIVILEGES; This command has the following role (from MySQL.COM)
    Reloads the privileges from the grant tables in the mysql database.
    The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.”
We’ll need to create the tables for our database, to select the database type use ftpserver; Now we’ll need to populate our database using the following commands (taken from pure-ftpd website):

  Uid INT(11) NOT NULL default '-1',
  Gid INT(11) NOT NULL default '-1',

You can verify that the fields where created successfully by typing describe users;

Navigate to /etc/pure-ftpd and open pureftpd-mysql.conf using a text editor. You will need to make sure the following commands are entered in the configuration file:
#MYSQLPort       3306
MYSQLSocket     /tmp/mysql.sock
MYSQLUser       ftpuser
MYSQLPassword   1qaz@WSX
MYSQLDatabase   ftpserver
MYSQLCrypt      md5
MYSQLGetPW      SELECT Password FROM users WHERE User="\L"
MYSQLGetDir     SELECT Dir FROM users WHERE User="\L"
We’ll need to add a ftp user to our database by executing the following command:
INSERT INTO `users` (`User`, `Password`, `Uid`, `Gid`, `Dir`) VALUES (‘danftp’, md5(‘1qaz@WSX’), ‘1002’, ‘1003’, ‘/home/danftp’);
To verify that the user was created successfully, type SELECT * FROM users;
The MySQL configuration is done, we will need to modify the pure-ftpd configuration file. Navigate to /etc/pure-ftpd and open pure-ftpd.conf with VIM:
Add the following line MySQLConfigFile               /etc/pure-ftpd/pureftpd-mysql.conf
and comment  # UnixAuthentication            yes
Just need to restart the FTP Server daemon by typing /etc/init.d/pure-ftpd restart and we should be able to connect using our mysql user:

We’ve successfully configured our FTP Server to support MySQL for back-end authentication. If you think there are unclear things written in this article, please leave a comment and I will respond as soon as possible. Don’t forget to enjoy your day and stay tuned for the following articles from IT training day.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s