Set up a virtual FTP server with pam-mysql

Setting up a virtual File Transfer Protocol (FTP) server with a database back end offers many benefits. By using a database, you can store a large number of users centrally, so it’s easy to manage. It offers more security than traditional Unix OS authentication methods, because virtual users can access only the FTP server’s resources, not the OS’s. You can use the many Web tools that are available to easily install, configure, and manage the database back end. A virtual FTP server also supports some special characters, such as @, that FTP itself doesn’t support, which can come in handy if, for example, your company uses its employees’ email addresses for identity purposes.

pam-mysql is a popular pluggable authentication module (PAM) that allows you to authenticate against a MySQL database, which you need because vsftpd has no built-in MySQL support. I’ll show you how to use pam-mysql (using pam-mysql-0.5-1.i586.rpm as an example) to set up a virtual FTP server. All you need is a Linux distribution, a Web server, an FTP server, a MySQL client and server, PHP, and php-mysql, which adds support for MySQL to PHP.

For the Linux distribution, I’m using CentOS 4, a Red Hat clone. I’m also using Apache 2.0, MySQL 4.1, PHP 4.3.9, php-mysql 4.3.9, and vsftpd 2.0. For the MySQL administration program, I’m using MySQL-Admin (mysql-admin_3_4_0_full.zip). It’s easy to handle and provides a wide range of functions, such as the ability to edit datasets, table structure, and tables, as well as the ability to import and export content.

Installation and configuration

Installing CentOS and the packages is simple. After you’ve completed the installation, log in as the root user. Install pam-mysql with the command rpm -ivh pam_mysql-0.5-1.i586.rpm. For MySQL-Admin, unzip the compressed archive and copy all the contents to /var/www/html, which is the default Web page container. MySQL-Admin is a PHP program, so you need to use Apache and PHP to run and interpret it.

You need to start the Apache, vsftpd, and MySQL services before you begin configuring the virtual FTP server. As root, enter the following commands to start them up and ensure they can start every day:

# service (httpd | vsftpd | mysqld) start
# chkconfig --levels 2345 (httpd | vsftpd | mysqld) on

Access the MySQL server by issuing the mysql command without options; there is no password for the root user by default. Execute the following SQL statements to create a database and tables:

mysql> create database vsftpd;
mysql> use vsftpd;
mysql> create table users
-> id int AUTO_INCREMENT NOT NULL,
-> name char(128) binary NOT NULL,
-> passwd char(128) binary NOT NULL,
-> primary key(id)
-> );
mysql> create table logs (msg varchar(255),
-> user char(128),
-> pid int,
-> host char(128),
-> rhost char(128),
-> logtime timestamp
-> );

You’ve now created the FTP server’s vsftpd database, which contains two tables: users, which stores the FTP users, and logs, which stores the login messages. Now you can insert users into the users table — for example:

mysql>insert into users (name,passwd) values('tom@cn.oracle.com',password('foo'));
mysql> insert into users (name,passwd) values('jerry@us.sun.com',password('bar'));
mysql> select * from users;
+----+-------+-------------------------------------------+
| id | name | passwd |
+----+-------+-------------------------------------------+
| 1 | tom@cn.oracle.com | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
| 2 | jerry@us.sun.com | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |
+----+-------+-------------------------------------------+

As you can see, we’ve inserted two users into the users table: tom@cn.oracle.com with the encrypted password foo, and jerry@us.sun.com with the encrypted password bar. By default, there is no password for the root user. Because without a password the database is vulnerable, you need to set one up. Use the following command to set the password:

mysql>grant all on *.* to root@localhost Identified by "password";

Once you’ve configured MySQL, it’s time to configure the vsftpd’s PAM authentication file, /etc/pam.d/vsftpd. Add the following contents to the file:

auth required /lib/security/pam_mysql.so user=root passwd=1wdv5rdxcvb host=localhost db=vsftpd table=users usercolumn=name passwdcolumn=passwd crypt=2 sqllog=1 logtable=logs logmsgcolumn=msg logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=logtime
account required /lib/security/pam_mysql.so user=root passwd=password host=localhost db=vsftpd table=users usercolumn=name passwdcolumn=passwd crypt=2 sqllog=1 logtable=logs logmsgcolumn=msg logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=logtime

Note these important parameters of the pam-mysql PAM module:

• host: MySQL database server IP address or hostname. In the example, it’s localhost, which means it’s on the same server as vsftpd.
• db: Database name that stores the vsftpd server users. Here the database name is vsftpd.
• user: User who can access the vsftpd user database. In the example, it’s the root user.
• passwd: The vsftpd database password.
• table: Table that stores the vsftpd users. Here the table name is users.

MySQL-Admin is a simple, easy-to-configure, PHP-based administration tool for MySQL databases. Simply open your Web browser with your server’s URL (the example uses http://localhost), follow the prompts that the browser displays, and complete all the steps. When you’re done, you can access http://localhost to log in to the MySQL database server, where you can manipulate it by inserting, deleting, or updating users on demand.

Once you’ve issued the command service vsftpd start, the virtual FTP server is ready to use. If you want to stop or restart it, execute service vsftpd (stop|restart). Users can interact with the FTP server with any FTP client, just as they would with any other FTP server.

Now all your FTP users store their files in a central MySQL database. You can easily remove, update, or temporarily disable your FTP users without touching /etc/passwd. If you enable your FTP server to support email addresses as usernames, you can easily track what was downloaded by which users, which can be useful if you are a big company’s administrator.