Using Virtual Accounts with VSFTPD (Very Secure FTP server) and MySQL on CentOS 5

0.0.1. Advantages of virtual user accounts compared to local users

Storing usernames and passwords in a database is easy to maintain, even for local managers not familiar with Unix security models.
Aside of supporting initial sub-directory setup for new users, it avoids the need to share system root rights. Using sudo and a bit of scripting, one can avoid granting unrestricted system root rights to non-admins doing day to day administration of the system. Indeed, nothing prevents setting up a non-MySQL root user with permissions to modify just the vsftpd ACL database, permitting even greater security, although such methodology is outside the scope of this article.
It avoids granting general shell capable local accounts for users only needing FTP access. As such, less exposure is presented to potential cackers thus minimising the risk of any compromise. This is particularly important for protocols such as ftp that send plain text login credentials.
All user content are located down one directory tree, potentially with user specific ACL settings as needed. Backups and restores are simplified to a subset of a directory tree, compared to having local FTP only accounts scattered amongst shell accounts in the /home/ directory under the default model.

The security model of MySQL can protect its databases with user level ACL permissions; by convention, the MySQL account: root is use for superuser operations on that ACL database, giving them access, permissions to read, write, modify. We follow that convention, and in this example use the MySQL root account. That account should have its own separate password (not the same as general system account root ).

This provides some protection in case there are found to exist exploits to mysql to avoid exposing this potentially valuable credential out of the database, to leverage an escalation to the local system root account to get access to the server. Additionally, this protects against the case of there being found a method to reverse the password hash out of a backup copy of a MySQL database, or administrator error using a 'cleartext' password for the database account. A simple general rule is : Do not 'reuse' passwords between accounts with special rights

1. REQUIREMENTS -> library: pam_mysql.so

You will need (if not already installed) VSFTPD and MySQL. As system root, the following will install the packages from the standard CentOS archives:

# yum install vsftpd mysql-server

Then, start mysqld if not already running:

# /sbin/service mysqld restart

and establish a database root user password for MySQL (if not already done):

# mysqladmin -u root password 'yourrootsqlpassword'

Replace 'yourrootsqlpassword' with your password for MySQL root user (without ' quotes)

1.1. Create the MySQL Database for vsftpd:

The following no longer requires system root permissions. Log in to the MySQL command line shell:

$ mysql -u root -p

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

Now in the MySQL shell, create a database for virtual vsftpd users:

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

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

mysql> USE vsftpd;
mysql> 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 ;

Now you can leave the MySQL shell:

mysql> exit;

1.2. Configure VSFTPD:

Create anon-privilegeduser called 'vsftpd' (with the home directory /home/vsftpd ) belonging to thegroup 'users'. The vsftpd can run with this user's privileges to further reduce risk of a system. The FTP directories of our virtual users will be beneath the '/home/vsftpd/' directory (e.g./home/vsftpd/user1, /home/vsftpd/user2, etc.) or as defined in VSFTPDPER USER config file.

# useradd -G users -s /bin/false -d /home/vsftpd  vsftpd

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

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

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

# > /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
# here the vsftpd will allow the 'vsftpd' user to login into the 
# '/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

With the user_config_dir option you can specify a directory for per-user configuration files that to 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 authenticates against the MySQL database for your virtual FTP users, rather than the default 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 thus:

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

Query: Why the cat here? As done above, one can null a file more easily. Are there SELinux, or permissions reasons not to simply mv and touch a replacement? If so, why not say so and descibe the needed values?

the /etc/pam.d/vsftpd contents (note: this should be only 3 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 " Create The MySQL Database For vsftpd "

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

Query: Should this editorial remark be here?

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 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
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!

Query: Should this editorial remark be here?

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 ! (There may be more, but the listed files should be in there)

This is critical to permit virtual users to authenticate against MySQL database

Now Create The First Virtual User

Insert users to database you can use the MySQL shell:

$ mysql -u root -p

enter password ... then in SQL shell:

mysql> 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):

mysql> 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)
mysql> exit;

Note: the password hash is for illustration only, and may well vary

Now user1's homedir is /home/vsftpd/user1 . Unfortunately vsftpd doesn't create that directory automatically if it doesn't exist. Therefore one has to create it as root manually now and give it proper ownership by the vsftpd user and group 'users':

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

Now restart the VSFTPD

# /sbin/service vsftpd restart

and you should be able to login to this FTP server with any RFC compliant FTP client.

Note: The configuration file as noted above uses FTP on both ports 21 and 20 (for the control and data connections); some network setups (early Windows Internet Options about passive FTP; some NAT designs; some proxies) do not pass the second listed port. If so, the configuration file would need this amendment, and for the FTP server to be restated:

# connect_from_port_20=YES
connect_from_port_20=NO

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