Hack 11 Use proftp 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 back-end 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.

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:

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 may 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 group called www, with both its uid and 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, we're ready to create users in the
database. This will create the user jimbo, with effective user rights
as www/www, and dump him in the
/usr/local/apache/htdocs/ directory at login:

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 source of difficulty. 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

The mod_sql home page at http://www.lastditcheffort.org/~aah/proftpd/mod_sql/