Compiling with mod_sql
To compile proftpd with the mod_sql SQL module, you will need to have the libraries and header files of a SQL database installed; the mysql.h and libmysqlclient.a files for MySQL, the libpq-fe.h and libpq.a files for Postgres.

mod_sql is the module that provides a generic interface between the proftpd daemon and the underlying SQL database(s); mod_sql relies on backend modules to handle the database-specific operations. For MySQL database, the backend module is mod_sql_mysql; mod_sql_postgres is to be used when dealing with Postgres databases. Then, to build a proftpd daemon to use mod_sql, use the --with-modules option of the configure script, specifying both mod_sql and the backed module, e.g.:

  ./configure --with-modules=mod_sql:mod_sql_mysql

  /usr/local/mysql/include/mysql/mysql.h

  /usr/local/mysql/lib/mysql/libmysqlclient.a

  ./configure \
    --with-modules=mod_sql:mod_sql_mysql \
    --with-includes=/usr/local/mysql/include/mysql \
    --with-libraries=/usr/local/mysql/lib/mysql

mod_sql is capable of using OpenSSL for different ways of encrypting passwords stored in database tables. The configure options for building an OpenSSL-capable mod_sql might look something like this:

  CFLAGS=-DHAVE_OPENSSL LIBS=-lcrypto ./configure \
    --with-modules=mod_sql:mod_sql_mysql \
    --with-includes=/usr/local/mysql/include/mysql:/usr/local/openssl/include \
    --with-libraries=/usr/local/mysql/lib/mysql:/usr/local/openssl/lib

Configuring mod_sql
Now that a proftpd daemon has been compiled for SQL support, you can begin the task of configuring it so that mod_sql can access your SQL database tables. At a very minimum, mod_sql assumes that it has access to a table with two columns, one for user names, the other for passwords. For more complete functionality, tables providing full user and group information are needed. The full information that can be provided is described below.

User Information Table₁

Column	Type	Required?	Duplicates?	Null?	Purpose
userid	text	yes	no	no	user's login
passwd	text	yes	yes	no	user's password
uid	number	yes	no2	yes	user's UID
gid	number	no	yes	yes	user's GID
home3	text	no	yes	yes	user's home
shell4	text	no	yes	yes	user's shell

Notes:

1. The user table MUST exist.

2. Nothing in proftpd or mod_sql prevents you from using duplicate UIDs, from given multiple users the same UID. Hower, this is should be done only if you are certain you know what you are doing.

3. See the SQLDefaultHomedir and SQLUserInfo configuration directives.

4. See the RequireValidShell configuration directive.

Group Information Table₁

Column	Type	Required?	Null?	Purpose
groupname	text	yes	no	group's name
gid	number	yes	no	group's GID
members2	text	yes	yes	group's members

Notes:

1. mod_sql will normally concatenate all matching group rows; you can have multiple rows for each group with only one member per group, or have a single row with multiple groups, or a mixing of the two. However, if you use the fast option for groupset of the SQLAuthenticate directive, you may not have multiple rows per group.

2. Multiple members per group are formatted as comma-separated names (no contained whitespace) in the text for this column.

The two SQL statements below should work for any ANSI SQL compliant databases, and are known to work for MySQL and PostgreSQL. They both fully specify the tables as described above, with reasonable defaults for field length and data type. More stringent definitions are suggested: if you plan on keeping home directory or shell information in the database, those fields could be defined as NOT NULL, or even UNIQUE for home directory. Similarly, if you plan on being able to use the groupsetfast argument to the SQLAuthenticate directive, you should create both the groupname and gid fields as UNIQUE.

To create a user table:

  CREATE TABLE users (
    userid VARCHAR(30) NOT NULL UNIQUE,
    passwd VARCHAR(80) NOT NULL,
    uid INTEGER UNIQUE,
    gid INTEGER,
    homedir VARCHAR(255),
    shell VARCHAR(255)
  )

  CREATE TABLE groups (
    groupname VARCHAR(30) NOT NULL,
    gid INTEGER NOT NULL,
    members VARCHAR(255)
  )

The key configuration directives for mod_sql are:

• SQLConnectInfo, for setting the information for connecting to the database server
• SQLAuthenticate, for controlling how the module will perform its authentication lookups
• SQLAuthTypes, for defining which authentication methods to use
• SQLUserInfo, for modifying the names of the columns using which the module will lookup values from the user table
• SQLGroupInfo, for modifying the names of the columns using which the module will lookup values from the group table

  http://www.castaglia.org/proftpd/modules/mod_sql.html

Frequently Asked Questions
Whenever questions arise about problems using mod_sql, the first place to look will be the server debugging output and in a SQLLogFile. mod_sql is very verbose with its debugging information, and you can see everything it is doing. However, there are times when there is too much information in the debugging output, and you are unsure of how to remedy the problem. These are some of the frequent questions.

Question: Why is proftpd only looking in my SQL tables when a user logs in?
Answer: You probably configured mod_sql to be "authoritative" in your SQLAuthenticate setting by using the * option. Conversely, if you actually want proftpd to only authenticate SQL-defined users, the * "authoritative" suffix is what you would want to use.

Question: Why does my SQL user not use the UID/GID I configured for her in my SQL table?
Answer: More than likely, you gave this user a UID that is below the default SQLMinUserUID (999), or a GID that is below the default SQLMinUserGID (999). Use the SQLMinUserUID, SQLMinUserGID, and/or SQLMinID configuration directives to set these limits lower as needed.

Question: Do I have to configure a real shell for my SQL-defined users?
Answer: No. The proftpd daemon only checks the shell for a user in order to provide compatibilty with other FTP daemons, which do the same check; proftpd itself does not spawn the shell. See the RequireValidShell configuration directive for turning this check off.

Question: How come my SQLLog QUIT is not run if the session aborts or dies?
Answer: Not all FTP clients are polite and issue the QUIT before the session ends. Perhaps their session is timed out, or dropped due to network problems. Use EXIT as the FTP command in your SQLLog directive, rather than QUIT, as mentioned in the SQLLog documentation.

Question: How can I make mod_sql go faster?
Answer: There are a couple of things you might try. First, if using a version of mod_sql from ProFTPD-1.2.7rc1 or later, make use of the SQLNegativeCache configuration directive.

Other forms of this question are "Why does mod_sql iterate through every user in the database?", or "Why is mod_sql so slow during logins?" Here's the reason: mod_sql is designed to handle all authentication functions that the daemon throws at it. This includes the functions that iterate through all users (setpwent(), getpwent(), endpwent()) and the functions that iterate through all groups (setgrent(), getgrent(), endgrent()).

When you see mod_sql iterating through all groups or users, it is doing so because it has been asked to do so by the daemon. Since there is no good way to keep an open query around without adding more to the various backend modules than we already have, mod_sql pre-caches all users when setpwent() is called, and pre-caches all groups when setgrent() is called. This allows the getpwent() and getgrent() calls to be simple, at the cost of more time during login.

In simple situations, these functions are never called. When you start limiting access to directories, files, or various FTP commands based on user or group, that is when the daemon needs to iterate through the users and groups to check permissions. A basic FTP server, including virtual and anonymous servers, will never make the (potentially, very) expensive user iteration calls, but may iterate through all groups.

The SQLAuthenticate directive provides a method to tune mod_sql; by default, mod_sql will handle the various *pwent() and *grent() calls. When SQLAuthenticate is told not to handle userset or groupset, mod_sql simply passes the request on to whatever authentication handlers exist in the system. Keep in mind that using SQLAuthenticate in this way means that the proftpd daemon is not using the same information to authenticate the user as it is to control the user's actions during their session.

For those of you who have used mod_sql in the past, these lookups should probably be set to off. Versions of mod_sql prior to 3.2.0 (or thereabouts) did not handle the {set|get|end}{pw|gr}ent functions at all, so by setting these lookups to off, you lose no functionality. Those of you new to mod_sql should to consider your needs: is the cost of iterating through every user stored in the database worth the ability to limit access based on users/groups from the database? If not, you will need to re-evaluate the way you are using the database, and where you should be storing your user/group information.

Question: When I use an SQLAuthTypes that includes "OpenSSL", what do the values in the database need to look like?
Answer: The value that mod_sql expects is the base64-encoded value of the digested password string. To get a list of the message digest algorithms supported by your OpenSSL installation, you can execute the following command:

  openssl list-message-digest-commands

  /bin/echo "{md5}"`/bin/echo -n "password" | openssl dgst -binary -md5 | openssl enc -base64`

Here's a quick and dirty example of generating database-ready strings using every digest algorithm supported by the installed OpenSSL:

  for c in `openssl list-message-digest-commands`; do
    /bin/echo "{$c}"`/bin/echo -n "password" | openssl dgst -binary -$c | openssl enc -base64`
  done

  {md2}8DiBqIxuORNfDsxg79YJuQ==
  {md4}ip0JPxT4cB3xdzKyuxgsdA==
  {md5}X03MO1qnZdYdgyfeuILPmQ==
  {mdc2}HA0SCu32vhW+exItsGK4lQ==
  {rmd160}LAjo9YhHUKe5n28vNC/GONsl/zE=
  {sha}gAclaL6zshAjJesgP20P+S9c744=
  {sha1}W6ph5Mm5Pz8GgiULbPgzG37mj9g=

Note: the {digest} prefix syntax is only needed for passwords that will be handled as OpenSSL-style passwords. This prefix is not needed for any of the other SQLAuthType types.

There are a lot of PHP applications which might want to generate passwords suitable for use with mod_sql. For example, to generate a base64-encoded MD5 password with PHP, which can then be read by mod_sql, use the following PHP code:

  // $password contains the cleartext password before
  $password = "{md5}".base64_encode(pack("H*", md5($password)));

  // $password now contains the encrypted, encoded password

  password = "{md5}" + base64.b64encode(md5.new("password").digest())

Question: Why do I see "error deleting scoreboard entry: Invalid argument"?
Answer: This log message almost always denotes use of mod_sql, and a problem in the mod_sql configuration. To debug the problem, define an SQLLogFile (making sure the path is not in a world-writable directory), to which mod_sql will write debugging information.

Question: I am trying to compile proftpd with MySQL support, and get the following error when I run make. Why?

  /usr/bin/ld: cannot find -lz
  collect2: ld returned 1 exit status
  make: *** [proftpd] Error 1