Wayfair Tech Blog

MySQL Virtual Directories for Pure-FTPd

Here at Wayfair, we have thousands of suppliers we work with in order to provide our products to our customers. To automate the bulk of these interactions, we use Electronic Data Interchange (EDI), so that we can trade documents back and forth. FTP is still one of the predominant methods for transferring these documents, so we have had to build a robust FTP solution to handle this traffic.

Up until recently we had been using FileZilla Server as our FTP server for this application. We originally choose FileZilla over the more standard Pure-FTPd  due to its ability to allow  directory aliases. With proprietary software on the backend, we need to have FTP subfolders map to different parts of a file system per user. FileZilla worked flawlessly for a couple of years, and with a script that automatically added users to the XML config file. It was a fairly hands off and streamlined setup.

Unfortunately, as we scaled up the number of user accounts in the system into the thousands, Filezilla started to struggle/crash when making configuration file changes. This post details our switch to Pure-FTPd (an alternative to FileZilla that can integrate with MySQL to acquire and store user data) for this application. I will also address an enhancement that we developed to take further advantage of the Pure-FTPd/database functionality and to replace the functionality we lost by switching away from Filezilla.

FileZilla’s usage of a standard XML document to detail and parse all user information and relevant directory paths in the file system was a great initial win, but couldn’t provide that same level of scalability and manageability that a database would provide.  Pure-FTPd allows integration with either MySQL or PostgreSQL to gather user information and control logins based on a database table (among other access control methods). The configuration file allows the administrator to craft simple or complex queries to determine the login details.

Here were our general requirements for the migration:

  1. Management of all user information through MySQL databases
  2. Restricted access to anywhere in the file system beyond a user’s home directory
  3. Ability to indicate specific directories outside the home directory to which users should have access.  Each user should have their own set of directories available to them.
  4. Manage (3) with MySQL.

Point (1) was the motivating factor for the switch to Pure-FTPd.

Point (2) is handled through the Pure-FTPd configuration option to chroot (root at the home login directory) everyone upon logging in.

Point (3) would otherwise be prohibited by the answer to Point (2), except for Pure-FTPd’s Virtual Chroot option.  Using virtual chroot, a user is fully locked into his or her home directory on the surface, but the actual chroot() system routine is never called.  This allows users to follow symbolic links in their home directory and have the backend Pure-FTPd server send them to where the symbolic link points, since the user is not actually rooted.

Point (4): not feasible in original version.

Using the virtual chroot implementation as it stood, there was no way to leave the home directory path without a symbolic link to indicate where in the file system the user should go.  We did not want to have to manage thousands of symbolic links from a UNIX standpoint among all of our suppliers, as that would defeat the purpose of this switch, which was to ease management of the FTP server and increase our scalability. What we wanted was a database table that listed all paths outside the home directory for each user that they could access, and allow them to follow those paths without any symbolic link present.

The enhancement we coded for Pure-FTPd allows the MySQL (mimicked for PostgreSQL as well) configuration file to indicate the correct query that will grab the result set of virtual paths to follow based on the user logging in.  Each element of the result set should be a pair containing a virtual path (“/alias”) and an actual path (“/actual/path/to/directory”) to where the virtual path should point in the file system.  With some additional management of the virtual path along with the actual path in the virtual chroot code, a user is able to follow these paths just as if there were symbolic links specifying the path in the home directory, even though there are none.

In terms of security, there is no less security in the new system than there is for the standard MySQL table housing the user’s login information, and that risk is mitigated by standard MySQL security practices.

With this functionality added to Pure-FTPd we are able to automate the create of the user accounts and aliases in the database similar to the way we were generating the XML configuration file.

We look forward to getting this code tested in more environments and eventually rolled into the core codebase if the need is there. We currently have a pull request submitted if you wanted to test it out and send us your feedback.