This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature: System Administration

Set up a virtual FTP server with pam-mysql

By Cunpeng Wang on February 08, 2008 (4:00:02 PM)

Share    Print    Comments   

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.

Share    Print    Comments   

Comments

on Set up a virtual FTP server with pam-mysql

Note: Comments are owned by the poster. We are not responsible for their content.

Why not just setup OpenSSH + PAM + <database (my PostgreSQL bias shines through though)>?

Posted by: Anonymous [ip: 169.233.27.26] on February 08, 2008 07:00 PM
It's all in the title.

#

Re: Why not just setup OpenSSH + PAM + <database (my PostgreSQL bias shines through though)>?

Posted by: Anonymous [ip: 202.108.130.138] on February 13, 2008 02:57 AM
What do you want? This article is about a ftp server. Maybe you want to authenticate your OS user against DB.

#

What?

Posted by: Anonymous [ip: 72.185.251.177] on February 08, 2008 10:09 PM
"Now all your FTP users store their files in a central MySQL database...." Are you saying that the actual files being uploaded and downloaded are in the dbase as well? I don't think you are but that's what that sentence says to me.

#

Re: What?

Posted by: Anonymous [ip: 202.108.130.138] on February 13, 2008 03:01 AM
Not exactly, I think this sentence should be "Now all your FTP users are stored in a central MySQL database"

#

Re: What?

Posted by: Anonymous [ip: 202.108.130.138] on February 13, 2008 03:08 AM
Not exactly, I think this sentence should be "Now all your FTP users' ID are stored in a central MySQL database"

#

Set up a virtual FTP server with pam-mysql

Posted by: Anonymous [ip: 134.11.111.43] on February 11, 2008 10:45 PM
I think there are some step missing from this guide. There was no setup step for the user repository to transfer files to and from.

#

Set up a virtual FTP server with pam-mysql

Posted by: Anonymous [ip: 203.2.120.51] on February 13, 2008 01:46 AM
At the risk of sounding pedantic, I am not sure what is virtual about this. Its a bog stock ftp server authenticating off mysqld. "configuring pam_mysql" might be a more appropriate title. As for:
"..because virtual users can access only the FTP server's resources, not the OS's", this is a little spurious. If the only resource being redirected is authentication, you can quite easily argue that using a complex application such as mysql for authentication actually introduces more vectors for security threats. and:
"..also supports some special characters, such as @, that FTP itself doesn't support..". There seems to be a real confusion between FTP and pam_unix. You could just as easily (and perhaps more manageably) be using LDAP or AD as you backend auth mechanism. It has nothing to do with FTP.
Its still all very worthy, it just seems a little confused.

#

Set up a virtual FTP server with pam-mysql

Posted by: Anonymous [ip: 202.108.130.138] on February 13, 2008 03:13 AM
It is really useful. I successfully set up my own virtual ftp server by following this guide.

#

Servidor FTP multiusuari amb VSFTPD + PAM + MySQL

Posted by: Anonymous [ip: 87.111.32.12] on February 14, 2008 10:15 AM

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya