Beware this content is over 6 years old and considered stale. It may no longer be accurate and/or reflect the understanding of the author but remains here for reference only. Please keep this in mind as you use this content.

This blog post deals with setting up an Ubuntu 10.04 (Lucid Lynx) server to allow virtual FTP user accounts which authenticate against credentials stored in a MySQL database. It does this using a MySQL Pluggable Authentication Module (PAM) which works with vsftpd (Very Secure FTP Daemon).

As mentioned, I’ve tried this on Ubuntu 10.04 LTS Lucid Lynx, vsftpd (2.2.2), PAM (0.7~RC1-4build1), MySQL (5.1.41) so you may have varied success on other versions of the above.

Before you get going you need to have root privileges or at least be able run commands with sudo.

I’ve assumed you already have a working MySQL server installation. If not, install MySQL and get it running first before continuing. See here for more information or if your confident enough:

1
$ sudo apt-get install mysql-server

And then run through the configuration to get setup.

You’ll need to create a MySQL database to hold all your FTP usernames and passwords. This could be a pre-existing table that has other data in it.

The crucial point is that you create a user that has limited privileges on that database. This could be as restrictive as only permitting the user to issue SELECT commands. Make a note of their username and password as you’ll need that later.

Once you are ready, install the required packages.

1
$ sudo apt-get install vsftpd libpam-mysql

Next you need to create a system user that will be used for all virtual users.

1
2
3
4
5
6
7
$ adduser ftp_user
Adding user 'ftp_user' ...
Adding new group 'ftp_user' (1004) ...
Adding new user 'ftp_user' (1003) with group 'ftp_user' ...
Creating home directory '/home/ftp_user' ...
Copying files from '/etc/skel' ...
Enter new UNIX password:

We have created a user called ‘ftp_user’ with a home directory /home/ftp_user.

With vsftpd.conf we can specify that every virtual FTP user has their own folder within the system user ‘ftp_user’ home folder under their own FTP username. For example a virtual user ‘TestUser’ will need to have a folder at:

/home/ftp_user/TestUser

This behaviour can be changed by modifying the local_root setting in the /etc/vsftpd.conf.

Let’s add some content to this download area:

1
2
$ cp /etc/hosts /home/ftp_user/TestUser/hosts
$ chown -R ftp_user:ftp_user /home/ftpsite/hosts

Make a backup of the PAM vsftpd config file and then open it for editing.

1
$ sudo vi /etc/pam.d/vsftpd

You may see other commands already in this file. Remove / delete or comment every single other line.

What you’ll need is the database username and password of a MySQL user (you may have created that user already) that has privileges (at the very least SELECT) on the table that will store the FTP usernames and passwords.

During setup and testing it is recommended that you enable the PAM-MySQL SQL logging to help you diagnose any problems.

For that you’ll need to setup a table in your database so that PAM-MySQL can log these entries.

Remember, that whatever MySQL user you use to log into your database to authenticate users, that user will need to have INSERT permissions on the PAM-MySQL table that you will store the SQL log entries.

Then add the following lines:

1
2
3
auth required pam_mysql.so user={db_username} passwd={db_password} host={db_host} db={db_name} table={db_table} usercolumn={db_table_username_column} passwdcolumn={db_table_password_column} crypt=0 sqllog=true logtable=log_table logmsgcolumn=description logusercolumn=username logpidcolumn=pid loghostcolumn=host logtimecolumn=timestamp verbose=1 debug=1
account required pam_mysql.so user={db_username} passwd={db_password} host={db_host} db={db_name} table={db_table} usercolumn={db_table_username_column} passwdcolumn={db_table_password_column} crypt=0 sqllog=true logtable=log_table logmsgcolumn=description logusercolumn=username logpidcolumn=pid loghostcolumn=host logtimecolumn=timestamp verbose=1 debug=1

Each line deals with different points of the PAM authentication. For more information on these settings look here and here.

You’ll notice I’ve left some {placeholders} for you to replace the necessary fields to reflect your own MySQL database configuration.

You can change the settings for the log table name and the associated columns if you like.

If you are logging MySQL queries a successful authentication and login log entry should look like this:

1
2
{id} AUTHENTICATION SUCCESS TestUser {pid} {ip_address} {timestamp}
{id} QUERYING SUCCESS TestUser {pid} {ip_address} {timestamp}

The PAM-MySQL module supports different levels of password encryption as denoted by crypt=0, the default is 0 or no encryption, sending passwords in plain-text. It is highly recommended you get it working BEFORE playing around with the different types of encryption available as some may work differently or not at all depending on the version of MySQL you are running.

For more information on the PAM-MySQL crypt options, see here.

In addition I’ve included the verbose=1 and debug=1 flags to make the system log entries a whole lot more helpful. You can always come back and remove these once you have it working.

Once your PAM configuration for vsftpd is set you can continue on to editing the vsftpd configuration to tell vsftpd how you would like it to work.

I’ve included the configuration for /etc/vsftpd.conf that I used to get it working. You can try the one below and then tweak it to match your needs. For a detailed list of all the available options see an HTML version of the man page here.

As always backup your original vsftpd.conf and then open the file for editing.

1
$ sudo vi /etc/vsftpd.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# configuration for vsftpd.conf
anonymous_enable=NO
anon_upload_enable=NO
anon_mkdir_write_enable=NO
anon_other_write_enable=NO
#chown_upload_mode=0600
#chroot_list_enable=YES
#chroot_list_file=/etc/vsftpd/chroot_list
chroot_local_user=YES
#cmds_allowed=ABOR,CDUP,CWD,DELE,LIST,MDTM,MKD,NLST,NOOP,OPTS,PASS,PASV,PORT,PWD,QUIT,RETR,RMD,RNFR,RNTO,SITE,SIZE,STOR,TYPE,USER
#cmds_allowed=ABOR,CWD,DELE,LIST,MDTM,NOOP,PASS,PASV,PWD,RETR,RNFR,RNTO,SIZE,STOR,TYPE,QUIT
#cmds_denied=MKD,RMD,CDUP,HELP
#deny_file={*.mp3,*.mov,.private}
dirmessage_enable=NO
force_dot_files=NO
guest_enable=YES
guest_username=ftp_user
hide_ids=YES
listen=YES
listen_port=21
local_enable=YES
local_umask=022
local_root=/home/ftp_user/$USER
max_clients=100
max_login_fails=3
max_per_ip=2
#message_file=.message
pam_service_name=vsftpd
#pasv_min_port=30000
#pasv_max_port=30999
user_config_dir=/etc/vsftpd/user_conf
#use_localtime=NO
user_sub_token=$USER
vsftpd_log_file=/var/log/vsftpd.log
virtual_use_local_privs=YES
xferlog_enable=YES
xferlog_std_format=YES
write_enable=YES

Once you have made all these changes you’ll need to restart vsftpd.

$ service vsftpd restart

If you get an error message saying the process has not been found, it’s probably because vsftpd was not already running so try:

$ service vsftpd start

Finally, give the FTP account a quick test by launching another shell session. Assuming you have a username in your database to test with. Example below uses the username ‘TestUser’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$ ftp localhost 21
Connected to localhost (127.0.0.1).
220 ready
Name (localhost:aos): TestUser
331 Please specify the password.
Password:
230 Login successful. Have fun.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> pwd
257 "/"
ftp> ls
227 Entering Passive Mode (127,0,0,1,117,135)
150 Here comes the directory listing.
226 Transfer done (but failed to open directory).
ftp> size hosts
213 147
ftp>

If at this point it’s all working. Remember you’ll need to go back and make the whole setup more secure and performant. Removing the SQL logging options in /etc/pam.d/vsftpd and changing the options within /etc/vsftpd.conf to be more secure if need be.

Sources

Set up a virtual FTP server with pam-mysql
vsftpd Example with Virtual Users - NB. Tutorial based on flat file for authenticating users.
Virtual Hosting With vsftpd And MySQL On Debian Etch - NB. Tutorial based on Debian Etch.
Ubuntu 9.10: Virtual FTP Users - NB. Tutorial based on Ubuntu 9.10 (Karmic Koala)
Use MySQL to handle VSFTPD virtual users on Debian/Ubuntu System
Setup Virtual Users and Directories in VSFTPD

Troubleshooting

Troubleshooting PAM-MySQL can be quite frustrating. There are two way main way to do this.

The first way is to check the log files when you attempt to make an FTP connection using credentials that exist in your database.

1
$ sudo tail -f /var/log/auth.log

You should see a stream of calls to the pam_mysql service. You’ll be able to spot errors in your /etc/pam.d/vsftpd configuration file here.

If you have SQL logging enabled within /etc/pam.d/vsftpd you can troubleshoot the queries that are being executed by PAM-MySQL enabling MySQL logging by editing the my.cnf

1
$ sudo vi /etc/mysql/my.cnf

Find the part of the config file called ‘Logging and Replication’ (around line 70) and uncomment the lines relating to general_log_file and general_log.

You should return and comment these lines out as logging queries on a production site can have serious performance issues.

1
2
general_log_file = /var/log/mysql/mysql.log
general_log = 1

You can watch new log entries to the mysql log file using the following command.

1
$ sudo tail -f /var/log/mysql/mysql.log

Hope this helps someone. Log feedback or errata in the comments.