Hack #11. Use proftpd with a MySQL Authentication Source

Make sure that your database system’s OS is running as efficiently as possible with these tweaks.

proftpd is a powerful FTP daemon with a configuration syntax much like Apache. It has a whole slew of options not available in most FTP daemons, including ratios, virtual hosting, and a modularized design that allows people to write their own modules.

One such module is mod_sql , which allows proftpd to use a SQL database as its backend authentication source. Currently, mod_sql supports MySQL and PostgreSQL. This can be a good way to help lock down access to your server, as inbound users will authenticate against the database (and therefore not require an actual shell account on the server). In this hack, we’ll get proftpd authenticating against a MySQL database.

First, download and build the source to proftpd and mod_sql:

~$ bzcat proftpd-1.2.6.tar.bz2 | tar xf -
~/proftpd-1.2.6/contrib$ tar zvxf ../../mod_sql-4.08.tar.gz 
~/proftpd-1.2.6/contrib$ cd ..
~/proftpd-1.2.6$ ./configure --with-modules=mod_sql:mod_sql_mysql \
--with-includes=/usr/local/mysql/include/ \
--with-libraries=/usr/local/mysql/lib/

Tip

Substitute the path to your MySQL install, if it isn’t in /usr/local/mysql/.

Now, build the code and install it:

rob@catlin:~/proftpd-1.2.6$ make && sudo make install

Next, create a database for proftpd to use (assuming that you already have MySQL up and running):

$ mysqladmin create proftpd

Then, permit read-only access to it from proftpd:

$ mysql -e "grant select on proftpd.* to proftpd@localhost \
            identified by 'secret';"

Create two tables in the database, with this schema:

CREATE TABLE users (
userid varchar(30) NOT NULL default '',
password varchar(30) NOT NULL default '',
uid int(11) default NULL,
gid int(11) default NULL,
homedir varchar(255) default NULL,
shell varchar(255) default NULL,
UNIQUE KEY uid (uid),
UNIQUE KEY userid (userid)
) TYPE=MyISAM;

CREATE TABLE groups (
groupname varchar(30) NOT NULL default '',
gid int(11) NOT NULL default '0',
members varchar(255) default NULL
) TYPE=MyISAM;

One quick way to create the tables is to save this schema to a file called proftpd.schema and run a command like mysql proftpd < proftpd.schema.

Now, you need to tell proftpd to use this database for authentication. Add the following lines to your /usr/local/etc/proftpd.conf file:

SQLConnectInfo proftpd proftpd secret
SQLAuthTypes crypt backend
SQLMinUserGID 111
SQLMinUserUID 111

The SQLConnectInfo line takes the form database user password. You could also specify a database on another host (even on another port) with something like this:

SQLConnectInfo proftpd@dbhost:5678 somebody somepassword

The SQLAuthTypes line lets you create users with passwords stored in the standard Unix crypt format, or MySQL’s PASSWORD() function. Be warned that if you’re using mod_sql’s logging facilities, the password might be exposed in plain text, so keep those logs private.

The SQLAuthTypes line as specified won’t allow blank passwords; if you need that functionality, also include the empty keyword. The SQLMinUserGID and SQLMinUserUID lines specify the minimum group and user ID that proftpd will permit on login. It’s a good idea to make this greater than 0 (to prohibit root logins), but it should be as low as you need to allow proper permissions in the filesystem. On this system, we have a user and a group called www, with both the user ID (UID) and the group ID (GID) set to 111. As we’ll want web developers to be able to log in with these permissions, we’ll need to set the minimum values to 111.

Finally, you’re ready to create users in the database. The following line creates the user jimbo, with effective user rights as www/www, and dumps him in the /usr/local/apache/htdocs directory at login:

mysql -e "insert into users values ('jimbo',PASSWORD('sHHH'),'111', \
  '111', '/usr/local/apache/htdocs','/bin/bash');" proftpd

The password for jimbo is encrypted with MySQL’s PASSWORD() function before being stored. The /bin/bash line is passed to proftpd to pass proftpd’s RequireValidShell directive. It has no bearing on granting actual shell access to the user jimbo.

At this point, you should be able to fire up proftpd and log in as user jimbo, with a password of sHHH. If you are having trouble getting connected, try running proftpd in the foreground with debugging on, like this:

# proftpd -n -d 5

Watch the messages as you attempt to connect, and you should be able to track down the problem. In my experience, it’s almost always due to a failure to set something properly in proftpd.conf, usually regarding permissions.

The mod_sql module can do far more than I’ve shown here; it can connect to existing MySQL databases with arbitrary table names, log all activity to the database, modify its user lookups with an arbitrary WHERE clause, and much more.

See Also

Rob Flickenger