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
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):
CREATE TABLE users (
User VARCHAR(16) BINARY NOT NULL,
Password VARCHAR(64) BINARY NOT NULL,
Uid INT(11) NOT NULL default '-1',
Gid INT(11) NOT NULL default '-1',
Dir VARCHAR(128) BINARY NOT NULL,
PRIMARY KEY (User)
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:
MYSQLGetPW SELECT Password FROM users WHERE User="\L"
MYSQLGetUID SELECT Uid FROM users WHERE User="\L"
MYSQLGetGID SELECT Gid 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.