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 SQL Authentication Directives ============================= SQLUserTable ------------ Syntax: SQLUserTable tablename Default: users Context: server config Specifies the table used to look up the other information, defaults to `users'. SQLUsernameField ---------------- Syntax: SQLUsernameField fieldname Default: userid Context: server config Specifies the name of the username field, defaults to `userid'. This is used in a WHERE clause for all other operations. SQLPasswordField ---------------- Syntax: SQLPasswordField fieldname Context: server config Default: none Using this directive activates SQL authentication functions, if a database driver is configured. It specifies which field holds the password, and has no default. SQLEmptyPasswords ----------------- Syntax: SQLEmptyPasswords bool Default: off Context: server config 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. SQLPlaintextPasswords --------------------- Syntax: SQLPlainTextPasswords Default: off Context: server config 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. SQLEncryptedPasswords --------------------- Syntax: SQLEncryptedPasswords Default: on Context: server config 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 . SQLAuthoritative ---------------- Syntax: SQLAuthoritative Context: server config Default: off Specifies whether authentication stops at mod_mysql, or whether other possibilites (like standard UNIX logins) are tried. Default is off - others are tried. SQL Identity Directives ======================= SQLUidField ----------- Syntax: SQLUidField fieldname Context: server config Default: none, UID is 65533 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. SQLGidField ----------- Syntax: SQLGidField Context: server config Default: none, GID is 65533 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. SQLHomedirField --------------- Syntax: SQLHomedirField field Context: server config Default: none, may be _required_ for proper operation 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. SQLHomedir ---------- Syntax: SQLHomeDir /path/to/virtual/site Context: server config Default: none, may be _required_ for proper operation 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. 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. SQLLogHosts ----------- Syntax: SQLLogHosts on Syntax: SQLLogHosts host-field ipaddr-field time-field Context: server config Default: off, or (`fhost faddr ftime') if fields unspecified. Activates logging of host, IP, and last-login timestamp to the user database. (The time is inserted as `now'). This is done immediately after a successful PASS command. SQLLoginCountField ------------------ Syntax: SQLLoginCountField count-field Context: server config Default: none Activates incrementation of a login count for the user, done with `set count = count + 1'. This is done immediately after a successful PASS command. SQLLogDirs ---------- Syntax: SQLLogDirs on Syntax: SQLLogDirs dir-field Context: server config Default: off, or `fcdir' if field unspecified 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: 230 "/pub/debian/Incoming" was last directory. SQLLogHits ---------- 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 `filename, hits' if fields unspecified Compatibility: only available with the Debian package. 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 `hits' field is incremented where the `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. SQLLogStats ----------- Syntax: SQLLogStats on Syntax: SQLLogStats F-stor-field F-retr-field B-stor-field B-retr-field Context: server config Default: off, or `fstor fretr bstor bretr"' if fields unspecified 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 _required_ when using this module in conjunction with mod_ratio. Database Setup ============== Here is a sample database setup, contributed by Olivier M. on the proftpd list: 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 | | +----------+-------------+------+-----+---------+-------+ And example of a live database: mysql> select * from proftp; +----------+------+------+----------+----------+-------+ | username | uid | gid | password | homedir | count | +----------+------+------+----------+----------+-------+ | oli | 1000 | 1000 | test | /home/om | 2 | | oli2 | 1000 | 1000 | test | / | 1 | +----------+------+------+----------+----------+-------+ 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: ----------------------------------------------------------------- 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 ----------------------------------------------------------------- Please report bugs or missing features in this module to Johnie Ingram .