VSFTPD Virtual users configuration (with MySQL) CentOS 5.x / RHEL 5 - HowTO example

vsFTPd Virtual Users configuration with MySQL, CentOS example, How To Set Up VSFTPD virtual users,
Setup Virtual Users and Directories in VSFTPd on CentOS 5.x/6.x, RHEL 5/6 (in my case it was CentOS 5.4 x86 32bit).
( based on Virtual Hosting With vsftpd And MySQL On Debian Etch)


Someone might find this useful, so you don't have to lose a day or two for getting it work... (as I did)...

Advantages
  1. Storing users and passwords into one database is easier to maintain and you avoid having local accounts for all the users you might need to give them FTP access, so the security risk of hacking user accounts is minimized. All users are located in one directory with user specific settings if needed.
  2. MySQL protects databases with user specific permissions granted by MySQL root (a superuser for databases, giving them access, permissions to read, write, modify...)
So the MySQL superuser root should have its own MySQL password (not the same as account 'root') in case of exploits to mysql and hacking the local 'root' account to get access to the server ( some more MySQL basics )

REQUIREMENTS:

pam_mysql.so  library

You will need (if not already installed) VSFTPD and MySQL:

yum install vsftpd mysql-server

Then Start mysqld if not already:

service mysqld restart

and create root password for MySQL (if not already done):

mysqladmin -u root password yourrootsqlpassword

3 Create The MySQL Database For vsftpd

login to mysql:

mysql -u root -p

enter " yourrootsqlpassword " - Be aware: yourrootsqlpassword IS NOT your user's 'root' password and should be different.

Create database for users:

CREATE DATABASE vsftpd;
GRANT SELECT ON vsftpd.* TO 'vsftpd'@'localhost' IDENTIFIED BY 'vsftpdpassword';
FLUSH PRIVILEGES;

still in the MySQL shell, create the database table needed (there is only one table with usernames and passwords MD5 encrypted):

USE vsftpd;

CREATE TABLE `accounts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) NOT NULL ,
`pass` VARCHAR( 50 ) NOT NULL ,
UNIQUE ( `username` )
) ENGINE = MYISAM ;

then you can

exit;

4 Configure VSFTPD (Very Secure FTP server):

Create a non-privileged user called 'vsftpd' (with the homedir /home/vsftpd) belonging to the group 'users'. Vsftpd will run with this users privileges so risk to the system is minimized and the FTP directories of our virtual users will be in the '/home/vsftpd' directory (e.g. /home/vsftpd/user1, /home/vsftpd/user2, etc.) or as defined in VSFTPD PER USER config file.

useradd -G users -s /sbin/nologin -d /home/vsftpd  vsftpd

Then make VSFTP config settings (make a backup of the original /etc/vsftpd.conf file):

cp -v /etc/vsftpd/vsftpd.conf   /etc/vsftpd/vsftpd.conf-orig

and make our own needed changes:
First we empty the existing file and then open it for editing:

cat /dev/null > /etc/vsftpd/vsftpd.conf
vi /etc/vsftpd/vsftpd.conf

vsftpd.conf   configuration settings (copy this into file):

# No ANONYMOUS users allowed
anonymous_enable=NO
# Allow 'local' users with WRITE permissions (0755)
local_enable=YES
write_enable=YES
local_umask=022
dirmessage_enable=YES
xferlog_enable=YES

# if you want to LOG vsftpd activity then uncomment this log_ftp_protocol
# log_ftp_protocol=YES

connect_from_port_20=YES

# uncomment xferlog_file and xferlog_std_format if you DIDN'T use the line above
# with log_ftp_protocol - it must be excluding each other
# The name of log file when xferlog_enable=YES and xferlog_std_format=YES
# WARNING - changing this filename affects /etc/logrotate.d/vsftpd.log
#xferlog_file=/var/log/xferlog
#
# xferlog_std_format Switches between logging into vsftpd_log_file and xferlog_file files.
# NO writes to vsftpd_log_file, YES to xferlog_file
# xferlog_std_format=YES

#
# You may change the default value for timing out an idle session (in seconds).
#idle_session_timeout=600
#
# You may change the default value for timing out a data connection (in seconds).
#data_connection_timeout=120
#
# define a unique user on your system which the
# ftp server can use as a totally isolated and unprivileged user.
nopriv_user=vsftpd

chroot_local_user=YES

listen=YES

# here we use the authentication module for vsftpd to check users name and passw
pam_service_name=vsftpd
userlist_enable=YES
tcp_wrappers=YES

# If userlist_deny=YES (default), never allow users in this file
# /etc/vsftpd/user_list , and do not even prompt for a password.
# Note that the default vsftpd pam config also checks /etc/vsftpd/ftpusers
# for users that are denied.
userlist_deny=yes

# here the vsftpd will allow the 'vsftpd' user to login into '/home/vsftpd/$USER directory
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 - passive ports for FTP (range 44000 - 44100 ; 100 PASV ports,
# REMEMBER to OPEN FIREWALL FOR ALLOWING FTP Passive CONNECTIONS
# check "how to enable Passive FTP in IPTABLES": here or here

pasv_enable=YES
pasv_min_port=44000
pasv_max_port=44100

With the user_config_dir option you can specify a directory for per-user configuration files that override parts of the global settings. This is totally optional and up to you if you want to use this feature.
However, create that directory now:

mkdir /etc/vsftpd/vsftpd_user_conf

If you want to have for example: 'user1' to have different 'home dir' other than '/home/vsftpd/user1' then create

vsftpd PER USER configuration file:

vi /etc/vsftpd/vsftpd_user_conf/user1

with configuration settings in it:

dirlist_enable=YES
download_enable=YES
# full path to the directory where 'user1' will have access, change to your needs
local_root=/home/users/user1
write_enable=YES

The 'user1' directory must be created if you want the user to be able to login!

mkdir /home/users/user1

and giving 'user1' the permissions to read, write...:

chmod 700 /home/users/user1
chown vsftpd.users /home/users/user1

So now user1 has 'home dir' in '/home/users/user1' instead of '/home/vsftpd/user1' and it can be changed to whatever you need to in the Per user configuration file ...

Now you must configure PAM (Password Authentication) so that it uses the MySQL database to authenticate your virtual FTP users instead of /etc/passwd and /etc/shadow.
The PAM configuration for vsftpd is in /etc/pam.d/vsftpd.
Make a backup of the original file and create a new one like this:

cp /etc/pam.d/vsftpd /etc/pam.d/vsftpd-orig
cat /dev/null > /etc/pam.d/vsftpd
vi /etc/pam.d/vsftpd

the /etc/pam.d/vsftpd contents (note: this should be only 4 lines when you copy it):

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

AND MAKE SURE that you replace the MySQL 'vsftpdpassword' password with your own one used before in   3 Create The MySQL Database For vsftpd

Now comes that tricky part for CentOS to make it work !

You need pam_mysql.so library, which is not included in CentOS installation or is not YUM installable, so you have to install from RPM (or EPEL repository ... or whichever method you prefer).
 Find here (pbone.net) the RPM pam_mysql module to download it (use 'wget' is simple), at this time of writing it was 'pam_mysql-0.7-0.5.rc1.el5.kb.2.i386.rpm' (watch for the right version i386 or x86_64 if you have 64bit system)
and install it:

rpm -Uvh pam_mysql-0.7-0.5.rc1.el5.kb.2.i386.rpm

It should install without warnings or error... else ... I recommend you use search in google to make it work!

When installed, you should find it:

ls -al /lib/security/pam_m*
-rwxr-xr-x 1 root root 8024 Sep 4 00:51 /lib/security/pam_mail.so
-rwxr-xr-x 1 root root 15848 Sep 4 00:51 /lib/security/pam_mkhomedir.so
-rwxr-xr-x 1 root root 3892 Sep 4 00:51 /lib/security/pam_motd.so
-rwxr-xr-x 1 root root 36920 Feb 28 2008 /lib/security/pam_mysql.so

there it is in the last line in this example ! (you can have more, but should be in there)

This is critical for use virtual users auth with MySQL database

Now 5 Create The First Virtual User
Insert users to database you can use the MySQL shell:

mysql -u root -p

enter password ...

USE vsftpd;

use the database 'vsftpd'
Now create the virtual user 'user1' with the password 'secret' (which will be stored encrypted using MySQL's MD5 function):

INSERT INTO accounts (username, pass) VALUES('user1', md5('secret'));

You should now have one user in database:

mysql> select * from accounts;
+----+-----------+----------------------------------+
| id | username | pass |
+----+-----------+----------------------------------+
| 1 | user1 | 5ebe2294ecd0e0f08eab7690d2a6ee69 |
+----+-----------+----------------------------------+
1 rows in set (0.00 sec)

exit;

Now user1's homedir is '/home/vsftpd/user1' , unfortunately vsftpd doesn't create that directory automatically if it doesn't exist. Therefore create it manually now and make it owned by the vsftpd user and group 'users':

mkdir /home/vsftpd/user1
chown vsftpd:users /home/vsftpd/user1

Now restart/start VSFTPD

service vsftpd restart

and you should probably be able to login to your FTP server with some of the Windows clients like WS_FTP or SmartFTP or whatever you like...
if not ... I'm sorry, try read again.

How to add more users in the future when you need.. it's easy in 2 steps:

1. add new user ( e.g. 'user12' with passw 'secret12', you can use the full name with email address also if you want, like 'user12@example.com' ) :

mysql -u root -p
USE vsftpd;
INSERT INTO accounts (username, pass) VALUES('user12', md5('secret12'));
exit;

2. make new 'user12' home dir

mkdir /home/vsftpd/user12
chown vsftpd:users /home/vsftpd/user12

Or you can use phpMyAdmin if you have a website running on the server (just download from phpMyAdmin site and extract to a subdir at your site - for example /var/www/mysite/phpmyadmin):

1. Login with root account (for now only 'root' has the rights to create/modify the vsFTP table at this time - you can create another user with privileges to modify the tables)




2. On the left side select 'vsftpd' database




3. then select table 'accounts'




4. On top select tab Browse once you have selected the table 'accounts'




5. you should see the list of users in the database:




6. Now to ADD new user: select INSERT tab on top
- in the field 'username' type the user's name for login (ie. 'newuser')
- in the field 'pass' select MD5 from dropdown list and type password for 'newuser' <- type it in plain text as it will be saved as MD5 because of field type selected MD5) Don't enter anything else, just click GO to save 'newuser' to database:




7. you should see the lines:
Inserted rows: 1
Inserted row id: "some number in list order automatically assigned id"




8. To see if you have added 'newuser' click again top tab Browse and the list should show the name and MD5 password for 'newuser'


Then you can repeat the step 6. as many times you need to add users.



9. To end phpMyAdmin session click Log Out / Exit :

Hopefully this is it and I'd be happy to see any comments of success (or fails).
TY for reading it ...