Feb 8, 2021

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)...

  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 )


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:
GRANT SELECT ON vsftpd.* TO 'vsftpd'@'localhost' IDENTIFIED BY 'vsftpdpassword';
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` (
`username` VARCHAR( 30 ) NOT NULL ,
`pass` VARCHAR( 50 ) NOT NULL ,
UNIQUE ( `username` )
then you can

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
# Allow 'local' users with WRITE permissions (0755)

# if you want to LOG vsftpd activity then uncomment this log_ftp_protocol
# log_ftp_protocol=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_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).
# You may change the default value for timing out a data connection (in seconds).
# define a unique user on your system which the
# ftp server can use as a totally isolated and unprivileged user.



# here we use the authentication module for vsftpd to check users name and passw

# 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.

# here the vsftpd will allow the 'vsftpd' user to login into '/home/vsftpd/$USER directory


# PASV - passive ports for FTP (range 44000 - 44100 ; 100 PASV ports,
# check "how to enable Passive FTP in IPTABLES": here or here

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:
# full path to the directory where 'user1' will have access, change to your needs
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):
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)

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'));
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)
vsFTPd virtual users phpMyAdmin manage users

2. On the left side select 'vsftpd' database
vsFTPd virtual users phpMyAdmin manage users

3. then select table 'accounts'
vsFTPd virtual users phpMyAdmin manage users

4. On top select tab Browse once you have selected the table 'accounts'
vsFTPd virtual users phpMyAdmin manage users

5. you should see the list of users in the database:
vsFTPd virtual users phpMyAdmin manage users

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:
vsFTPd virtual users phpMyAdmin manage users

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

vsFTPd virtual users phpMyAdmin manage users

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'
vsFTPd virtual users phpMyAdmin manage users

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

9. To end phpMyAdmin session click Log Out / Exit :
vsFTPd virtual users phpMyAdmin manage users

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

Full post / more »»