In this tutorial we will learn about how to configure vsftpd server with virtual user and mysql authentication in backend in CentOS 6 and Red Hat 6.

Note: SELINUX and IPTABLES are disabled

Features:

vsftp server with chroot

mysql authentication

virtual users in mysql (Not in local system)

Server Details:

Operating System:CentOS release 6.4 (Final)
arch: i686
IP Address: 10.0.0.18

Requirements:

vsftpd server

mysql server

pam_mysql

Follow the given below steps to setup the vsftpd server with mysql authentication

Step 1: Install vsftpd and mysql server

yum install vsftpd mysql-server 

After install mysql-server. Restart the mysql-server and set the new password for mysql user root. (Bydefault root has no password)
There are other things which you should clear after installing mysql-server hence I prefer below given procedure

/etc/init.d/mysqld restart

Hit the command /usr/bin/mysql_secure_installation and follow the instruction
Note: mysql’s root does not have password hence when you get question “New password:”, only hit enter without typing.

[root@ftpserver ~]# /usr/bin/mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


[root@ftpserver ~]# 

Step 2: Install pam_mysql which is dependency for providing mysql authentication. Here we will use EPEL repo.
Get the latest epel repo file from http://dl.fedoraproject.org/pub/epel/ depends upon your Operating system architecture(i686,x86_64 etc.)

Till writing this post epel-release-6-8.noarch.rpm was latest,use latest repo it changes with time

rpm -ivh http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm

Installing pam_mysql

yum install pam_mysql

Step 3: Creating Database and table in mysql server.

Database Name:vsftpd
Table Name: accounts
new mysql user created: vsftpd
new mysql user password set: P@ssw0rd

Note: In CentOS 6.4 I have MySQL Server version: 5.1.69 and its bydefualt storage engine is INNODB

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

mysql> GRANT SELECT ON vsftpd.* TO 'vsftpd'@'localhost' IDENTIFIED BY 'P@ssw0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> use vsftpd
Database changed
mysql> CREATE TABLE `accounts` (
    -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    -> `username` VARCHAR( 40 ) NOT NULL ,
    -> `passwd` VARCHAR( 50 ) NOT NULL ,
    -> UNIQUE ( `username` )
    -> ) ENGINE = INNODB ;
Query OK, 0 rows affected (0.06 sec)

mysql> exit

Setting vsftpd server

Create a user vsftpd in local system with nologin shell. By default in CentOS or Red Hat it will create its home directory in /home/vsftpd. The vsftpd will be the member of group called users

useradd -G users -s /sbin/nologin  vsftpd

Now we will configure vsftpd.conf file
First take the backup of conf file.And paste the content as it is given in below method

[root@ftpserver ~]# cp -v /etc/vsftpd/vsftpd.conf   /etc/vsftpd/vsftpd.conf.orig.bak
`/etc/vsftpd/vsftpd.conf' -> `/etc/vsftpd/vsftpd.conf.orig.bak'
[root@ftpserver ~]#

[root@ftpserver ~]# > /etc/vsftpd/vsftpd.conf

[root@ftpserver ~]# vi /etc/vsftpd/vsftpd.conf

anonymous_enable=NO
local_enable=YES
write_enable=YES
local_umask=022
dirmessage_enable=YES
xferlog_enable=YES
connect_from_port_20=YES
nopriv_user=vsftpd
chroot_local_user=YES
listen=YES
pam_service_name=vsftpd
userlist_enable=YES
tcp_wrappers=YES
userlist_deny=yes
guest_enable=YES
guest_username=vsftpd
local_root=/home/vsftpd/$USER
user_sub_token=$USER
virtual_use_local_privs=YES
user_config_dir=/etc/vsftpd/vsftpd_user_conf
force_local_data_ssl=NO
force_local_logins_ssl=NO
pasv_enable=YES
pasv_min_port=10080
pasv_max_port=10100

Step 4 Setting pam_mysql

[root@ftpserver ~]# cp -p /etc/pam.d/vsftpd /etc/pam.d/vsftpd.orig

[root@ftpserver ~]# > /etc/pam.d/vsftpd

[root@ftpserver ~]# vi /etc/pam.d/vsftpd

#%PAM-1.0
session     optional     pam_keyinit.so     force revoke
auth required pam_mysql.so user=vsftpd passwd=P@ssw0rd host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=passwd crypt=3
account required pam_mysql.so user=vsftpd passwd=P@ssw0rd host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=passwd crypt=3

Step 5: Creating your first vsftpd user for access

Login into mysql-server : mysql -u root -p
Connect to database after this: use vsftpd;
Insert username and password: INSERT INTO accounts (username, passwd) VALUES('sharad', md5('pass123'));

Here, we hae set username: sharad and password: pass123

[root@ftpserver pam.d]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> 
mysql> 
mysql> use vsftpd
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO accounts (username, passwd) VALUES('sharad', md5('pass123'));
Query OK, 1 row affected (0.02 sec)

mysql> show tables;
 
+------------------+
| Tables_in_vsftpd |
+------------------+
| accounts         |
+------------------+
1 row in set (0.00 sec)

mysql> select * from accounts;
+----+----------+----------------------------------+
| id | username | passwd                           |
+----+----------+----------------------------------+
|  1 | sharad   | 5ebe2294ecd0e0f08eab7690d2a6ee69 |
+----+----------+----------------------------------+
1 row in set (0.00 sec)

mysql> exit

Create ftp user home directory in /home/vsftpd
Here, we have ftp username sharad, hence we will create home directory sharad in /etc/vsftpd

mkdir /home/vsftpd/sharad
chmod 700 /home/vsftpd/sharad
chown vsftpd:users /home/vsftpd/sharad

Note:To create new user, follow the step 5 again and replace the username and password as per your requirement

Step 6: Now restart the vsftpd server once.

/etc/init.d/vsftpd restart

Extra settings (Optional)

Do you want to set particular settings for particular ftp user ? Follow the given below example
In this example, we will give different privileges to HR department of company for its users

Create a directory called ftpusers-conf in /etc/vsftpd.

mkdir -p /etc/vsftpd/ftpusers-conf

Create new directories in /home
HR-dept represents the directory for HR department
joe represents joe as user of HR department

mkdir -p /home/HR-dept/joe

change permission,ownership and group of directory

chmod 700 /home/HR-dept/joe
chown vsftpd:users /home/HR-dept/joe

Login into mysql server and create new ftp user called joe and set its password

mysql -u root -p

use vsftpd;

INSERT INTO accounts (username, passwd) VALUES(joe'', md5('pass123'));

exit

Now create a configuration file for user called joe

vi /etc/vsftpd/ftpusers-conf/joe

dirlist_enable=YES
download_enable=YES
local_root=/home/HR-dept/joe
write_enable=YES

Restart the vsftpd server

/etc/init.d/vsftpd restart

Connect to ftp server

Now you can connect to FTP server by using ftp client like Filezilla , through terminal by using command ftp ip-address-ftp-server
Through web browser, in url type , ftp://ip-address-ftp-server
Replace ip-address-ftp-server with your ftp server ip address

My FTP server IP Address is 10.0.0.18
hence ,
ftp://10.0.0.18 (In Web browser)
ftp 10.0.0.18 (In terminal)