This module is contained in the mod_sqlpw.c file, and is not compiled in
by default. It provides for authentication and logging via an external
SQL database (e.g. mod_mysql).

Context: server config, virtual host, directory, .htaccess

@heading SQL Authentication Directives

@subheading @anchor{SQLUserTable} SQLUserTable

@format
Syntax: SQLUserTable tablename
Default: users
Context: server config
@end format

Specifies the table used to look up the other information, defaults to
@samp{users}.

@subheading @anchor{SQLUsernameField} SQLUsernameField

@format
Syntax: SQLUsernameField fieldname
Default: userid
Context: server config
@end format

Specifies the name of the username field, defaults to @samp{userid}.
This is used in a WHERE clause for all other operations.

@subheading @anchor{SQLPasswordField} SQLPasswordField

@format
Syntax: SQLPasswordField fieldname
Context: server config
Default: none
@end format

Using this directive activates SQL authentication functions, if a
database driver is configured.  It specifies which field holds the
password, and has no default.

@subheading @anchor{SQLEmptyPasswords} SQLEmptyPasswords

@format
Syntax: SQLEmptyPasswords bool
Default: off
Context: server config
@end format

Specifies whether an empty (non-NULL but zero-length) password is
acceped from the database.  Default is no, and truly NULL passwords
are never accepted.  If the retrieved password is empty then whatever
password the user typed is accepted as valid, but the module logs a
warning at debug level 4.

@subheading @anchor{SQLPlaintextPasswords} SQLPlaintextPasswords

@format
Syntax: SQLPlainTextPasswords
Default: off
Context: server config
@end format

Specifies whether the two passwords should be compared as plaintext.
Default is no -- passwords must be UNIX DES-encrypted (the default).
Setting this does not turn off other tests.

@subheading @anchor{SQLEncryptedPasswords} SQLEncryptedPasswords

@format
Syntax: SQLEncryptedPasswords
Default: on
Context: server config
@end format

Specifies whether the password in the database may be in UNIX crypt()
format.  Default is true, with this being the only check done.  A tool
for generating crypted password text may be found at
@url{ftp://ftp.linpeople.org/pub/People/lilo/source/makepasswd-1.07.tar.gz}.

@subheading @anchor{SQLAuthoritative} SQLAuthoritative

@format
Syntax: SQLAuthoritative
Context: server config
Default: off
@end format

Specifies whether authentication stops at mod_mysql, or whether other
possibilites (like standard UNIX logins) are tried.  Default is off --
others are tried.

@heading SQL Identity Directives

@subheading @anchor{SQLUidField} SQLUidField

@format
Syntax: SQLUidField fieldname
Context: server config
Default: none, UID is 65533
@end format

Specifes what field holds the uid number, for users authenticated with
this module.  Default is to use the compiled-in default 65533.  If the
retrieved uid is in the range reserved for admin accounts (0-9999),
65533 is used instead.

@subheading @anchor{SQLGidField} SQLGidField

@format
Syntax: SQLGidField
Context: server config
Default: none, GID is 65533
@end format

Specifes what field holds the gid number, for users authenticated with
this module.  Default is to use the compiled-in default 65533.  If the
retrieved gid is in the range reserved for admin accounts (0-9999),
65533 is used instead.

@subheading @anchor{SQLHomedirField} SQLHomedirField

@format
Syntax: SQLHomedirField field
Context: server config
Default: none, may be @emph{required} for proper operation
@end format

Specifies what field holds the home directory, for users authenticated
with this module.  The directory can also be defined for all users
using SQLHomedir.  If no homedir is set with either directive,
authentication is turned off.

@subheading @anchor{SQLHomedir} SQLHomedir

@format
Syntax: SQLHomeDir /path/to/virtual/site
Context: server config
Default: none, may be @emph{required} for proper operation
@end format

Specifies the homedir to use for all users authenticated with this
module, overriding any SQLHomedirField directive.  If no homedir is
set with either directive, authentication is turned off.

@heading SQL Logging Directives

All logging directives work independently of whether the user was
authenticated with this module.  This may cause confusion with records
for a unix-password user, if SQLAuthoritative is not set.

@subheading @anchor{SQLLogHosts} SQLLogHosts

@format
Syntax: SQLLogHosts on
Syntax: SQLLogHosts host-field ipaddr-field time-field
Context: server config
Default: off, or (@samp{fhost faddr ftime}) if fields unspecified.
@end format

Activates logging of host, IP, and last-login timestamp to the user
database.  (The time is inserted as @samp{now}).  This is done
immediately after a successful PASS command.

@subheading @anchor{SQLLoginCountField} SQLLoginCountField

@format
Syntax: SQLLoginCountField count-field
Context: server config
Default: none
@end format

Activates incrementation of a login count for the user, done with
@samp{set count = count + 1}.  This is done immediately after a
successful PASS command.

@subheading @anchor{SQLLogDirs} SQLLogDirs

@format
Syntax: SQLLogDirs on
Syntax: SQLLogDirs dir-field
Context: server config
Default: off, or @samp{fcdir} if field unspecified
@end format

Activates logging of the last directory the user changed to.  This is
done after every CHDIR command -- the initial login to "/" does not
count.  Also, the last dir is displayed during login in this format:

@example
230 "/pub/debian/Incoming" was last directory.
@end example

@subheading @anchor{SQLLogHits} SQLLogHits

@format
Syntax: SQLLogHits hit-table
Syntax: SQLLogHits hit-table pathname-field hits-field
Syntax: SQLLogHits hit-table filename-field hits-field dir-field
Context: server config
Default: off, or @samp{filename, hits} if fields unspecified
Compatibility: only available with the Debian package.
@end format

Activates logging of RETR commands on a file to a separate table for
this purpose.  This is done during an approved RETR command, whether
it completes or not.

If one argument is given, the @samp{hits} field is
incremented where the @samp{filename} field matches the pathname
requested for RETR.  If three are given, the 2 extra strings are used
for the pathname field and hits field.

If four arguments are given, the fourth string is used as the field
name for logging the directory.  In this case the real path is used
--- symbolic links are dereferenced and the path split into dir and
filename parts.

@subheading @anchor{SQLLogStats} SQLLogStats

@format
Syntax: SQLLogStats on
Syntax: SQLLogStats F-stor-field F-retr-field B-stor-field B-retr-field
Context: server config
Default: off, or @samp{fstor fretr bstor bretr"} if fields unspecified
@end format

Activates logging of upload/download statistics for this user.  This
is updated after every successfully completed STOR and RETR, in a way
that allows for concurrent transfers.

This directive is @emph{required} when using this module in
conjunction with mod_ratio.

@heading Database Setup

Here is a sample database setup, contributed by Olivier M. on the
proftpd list:

@example
mysql> show fields from proftp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(30) | YES  |     | NULL    |       |
| uid      | int(11)     | YES  |     | NULL    |       |
| gid      | int(11)     | YES  |     | NULL    |       |
| password | varchar(30) | YES  |     | NULL    |       |
| homedir  | varchar(50) | YES  |     | NULL    |       |
| count    | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
@end example

And example of a live database:

@example
mysql> select * from proftp;
+----------+------+------+----------+----------+-------+
| username | uid  | gid  | password | homedir  | count |
+----------+------+------+----------+----------+-------+
| oli      | 1000 | 1000 | test     | /home/om |     2 |
| oli2     | 1000 | 1000 | test     | /        |     1 |
+----------+------+------+----------+----------+-------+
@end example

Note that the module requires the uid and gid to be normal users (id >=
1000), not "system" users.

To use a MySQL database with this setup, add something like this to
proftpd.conf and restart proftpd:

@example
-----------------------------------------------------------------
MySQLInfo                       localhost test "" test
                                # HOST login password database
SQLUserTable                    proftp
SQLUsernameField                username
SQLUidField                     uid
SQLGidField                     gid
SQLPasswordField                password
SQLHomedirField                 homedir
SQLLoginCountField              count
SQLAuthoritative                on
SQLPlaintextPasswords           on
-----------------------------------------------------------------
@end example

Please report bugs or missing features in this module to Johnie Ingram
@email{johnie@@netgod.net}.