One important thing to note here – that caused me a fair amount of hair pulling when I tried to use encrypted passwords – is that the password field shown in many howtos on the internet is much too short. This causes the hashed password to be quietly truncated by MySQL when saved.

This results in a somewhat misleading “No such user found” error to appear in the logs when using encrypted passwords.

To end all argument I’ve allowed passwords up to 128 chars, but this field could probably be a good deal shorter.

The user table looks much like /etc/passwd and is largely self explanatory. The uid & gid fields correspond to a system user in most cases, but since we’re using virtual users they can largely be ignored. Homedir points to a location which will serve as the user’s default directory. Shell is largely unused and can be set to /bin/false or similar.

Configuring ProFTP

Next, you need to make some changes to the ProFTP configuration files stored in /etc/proftpd. While doing this it is handy to run proftp in debug mode from the console:

proftpd -nd6

proftpd.conf

Make sure the AuthOrder line looks like:

AuthOrder mod_sql.c

Ensure that the following line is uncommented:

Include /etc/proftpd/sql.conf

For belts and braces I’ve included the following at the end, although I’m not entirely sure it’s strictly required:

<IfModule mod_auth_pam.c>
AuthPAM off
</IfModule>

Our users don’t need a valid shell, so:

RequireValidShell off

modules.conf

Make sure the following lines are uncommented:

LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

sql.conf

Set your SQL backend and ensure that authentication is turned on:

SQLBackend mysql
SQLEngine on
SQLAuthenticate on

Tell proftp how passwords are stored. You have a number of options here, but since I was using mysql’s PASSWORD function, I’ll defer to the backend.

SQLAuthTypes backend

Tell proftp how to connect to your database by providing the required connection details, ensure that the user has full access to these tables.