The mod_sql module is an authentication and logging module for ProFTPD. It is comprised of a front end module (mod_sql) and backend database-specific modules (mod_sql_mysql, mod_sql_postgres). The front end module leaves the specifics of handling database connections to the backend modules.

The most current version of mod_sql is distributed with ProFTPD.

Thanks

• Johnie Ingram <johnie at netgod.net> for the original mod_sqlpw.
• TJ Saunders and Jesse Sipprell for dealing with my inane questions.
• John Morrissey for mod_ldap, which lit the way, here and there.
• Zeev Suraski <bourbon at bourbon.netvision.net.il> for the Apache mod_auth_mysql module, which informed the SQLAuthTypes directive.
• Avalon from IRC for doc-fixes and suggestions.
• and many others who've suggested various enhancements or security fixes.

Directives

• SQLAuthenticate
• SQLAuthTypes
• SQLBackend
• SQLConnectInfo
• SQLDefaultGID
• SQLDefaultHomedir
• SQLDefaultUID
• SQLEngine
• SQLGroupInfo
• SQLGroupWhereClause
• SQLLog
• SQLLogFile
• SQLMinID
• SQLMinUserGID
• SQLMinUserUID
• SQLNamedQuery
• SQLNegativeCache
• SQLOptions
• SQLRatios
• SQLRatioStats
• SQLShowInfo
• SQLUserInfo
• SQLUserWhereClause

SQLAuthenticate

The SQLAuthenticate directive configures mod_sql's authentication behavior, controlling whether to provide user and/or group information during authentication, and how that provisioning is performed. The parameters may appear in any order.

The available parameter values are:

• on
  Shorthand for SQLAuthenticate users groups userset groupset.
  


• off
  Disables all mod_sql authentication functions.
  


• users
  If present, mod_sql will do user lookups. If not present, mod_sql will do no user lookups at all, including the {set|get|end}pwent() calls (see below).
  


• groups
  If present, mod_sql will do group lookups. If not present, mod_sql will do no group lookups at all, including the {set|get|end}grent() calls (see below).
  


• userset[fast]
  If present, mod_sql will process the potentially expensive {set|get|end}pwent() calls. If not present, mod_sql will not process these calls. Adding the suffix "fast" tells mod_sql to process the users as a single large query, rather than making a query per user. This may significantly reduce the number of queries against the database at the expense of increased memory use. This parameter will have no effect if "users" is not specified.
  


• groupset[fast]
  If present, mod_sql will process the potentially expensive {set|get|end}grent() calls. If not present, mod_sql will not process these calls. Adding the suffix "fast" tells mod_sql to process the groups as a single large query, rather than making a query per group. This may significantly reduce the number of queries against the database at the expense of increased memory use. This parameter will have no effect if "groups" is not specified.
  

The SQLLog and SQLShowInfo directives will always be processed by mod_sql. The SQLAuthenticate directive only affects the user and group lookup/authentication portions of the module.

Turning off (i.e. by not including) the userset or groupset parameters affects the functionality of mod_sql. Not allowing these lookups may remove the ability to control access or control functionality by group membership, depending on your other authentication handlers and the data available to them. At the same time, choosing not to do these lookups may dramatically speed login for many large sites.

The "fast" suffix is not appropriate for every site. Normally, mod_sql will retrieve a list of users and groups, and get information from the database on a per-user or per-group basis. This is query intensive: it requires (nn + 1) queries, where n is the number of users or groups to lookup. By choosing "fast" lookups, mod_sql will make a single SELECT query to get information from the database.

In exchange for the radical reduction in the number of queries, the single query will increase the memory consumption of the process; all group or user information will be read at once rather than in discrete chunks.

Group Table Structure
Normally mod_sql allows multiple group members per row, and multiple rows per group. If you use the "fast" option for groupset, you must use only one row per group. For example, normally mod_sql treats the following three tables in exactly the same way:

|--------------------------------------------------|
|  GROUPNAME  |  GID  |  MEMBERS                   |
|--------------------------------------------------|
| group1      | 1000  | naomi                      |
| group1      | 1000  | priscilla                  |
| group1      | 1000  | gertrude                   |
|--------------------------------------------------|

|--------------------------------------------------|
|  GROUPNAME  |  GID  |  MEMBERS                   |
|--------------------------------------------------|
| group1      | 1000  | naomi, priscilla           |
| group1      | 1000  | gertrude                   |
|--------------------------------------------------|

|--------------------------------------------------|
|  GROUPNAME  |  GID  |  MEMBERS                   |
|--------------------------------------------------|
| group1      | 1000  | naomi, priscilla, gertrude |
|--------------------------------------------------|

SQLAuthTypes

The SQLAuthTypes directive specifies which authentication method are to be allowed, and their order of use. You must specify at least one authentication method.

The current supported authentication methods are:

• Backend
  Allows database-specific backend passwords. Not all backend databases support this option. For example, MySQL datatabases use this option to authenticate MySQL 'PASSWORD()' encrypted passwords. The Postgres backend, however, does nothing. Caveat: if your MySQL activity log is world-readable, the user password will be visible. You have been warned.
  


• Crypt
  Allows passwords in the database to be of Unix crypt(3) form.
  


• Empty
  Allows empty passwords in the database, which match against any password the user may give. The database field must be a truly empty string; NULL values are not acceptable as empty passwords. Be very careful if using this authentication method.
  


• OpenSSL
  Allows passwords in the database to be of the form '{digest-name}hashed-value', where hashed-value is the base64-encoded digest of the passsword. Only available if you define HAVE_OPENSSL when you compile proftpd and you link with OpenSSL's libcrypto library.
  


• Plaintext
  Allows passwords in the database to be in plaintext.
  

For example:

  SQLAuthTypes Crypt Empty

Note: this directive deprecates the following configuration directives:

• SQLEmptyPasswords
• SQLEncryptedPasswords
• SQLPlaintextPasswords
• SQLScrambledPasswords
• SQLSSLHashedPasswords

SQLBackend

In 1.3.0rc1, the mod_sql module gained the ability to be compiled with multiple backend modules supported, e.g. to have both mod_sql_mysql and mod_sql_postgres usable in the same proftpd daemon. The SQLBackend directive configures which of these different database backends should be used.

If there is only one backend module compiled in, the SQLBackend directive is not needed. If there are multiple backend modules compiled and no SQLBackend directive is specified, then mod_sql will default to using the first backend module listed. For instance, if you configured proftpd using a configure command such as:

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

You might have multiple <VirtualHost> sections which use different SQL backends, e.g.:

  <VirtualHost 1.2.3.4>
    SQLBackend mysql
    ...
  </VirtualHost>

  <VirtualHost 5.6.7.8>
    SQLBackend postgres
    ...
  </VirtualHost>

SQLConnectInfo

The SQLConnectInfo directive configures the information necessary to connect to the backend database. The connection-info parameter specifies the database, host, port, and other backend-specific information. The optional username and password parameters specify a username and password to use when connecting to the database. Both default to NULL, which the backend will treat in some backend-specific manner. If you specify a password, you must specify a username. If no SQLConnectInfo directive is specified, mod_sql will disable itself.

Any given database backend has the opportunity, though not necessarily the responsibility, to check for syntax errors in the connection-info field at server startup, but you should not expect semantic errors (i.e., cannot connect to the database) to be caught until mod_sql attempts to connect for a given host.

A given database connection is governed by a connection policy that specifies when a connection should be opened and when it should be closed. There are three options:

• PERSESSION
  Open a database connection at the start of the session and close the database connection at the end of the session.
  


• number (TIMED)
  Timed database connections that close themselves after number seconds of inactivity.
  

If the connection policy is any number greater than 0, it specifies the number of seconds that a connection will be held open without activity. After that many seconds of database inactivity, the connection to the database will be closed. As soon as database activity starts again, the connection will be opened and the timer will restart.

The MySQL and Postgres backends' connection-info is expected to be of the form:

  database[@hostname][:port]

From the MySQL documentation:

the value of host may be either a hostname or an IP address. If host is NULL or the string "localhost", a connection to the local host is assumed. If the OS supports sockets (Unix) or named pipes (Windows), they are used instead of TCP/IP to connect to the server.

If [the hostname] begins with a slash, it specifies Unix domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. The default is to connect to a Unix-domain socket in /tmp.

If you plan to use the TIMED connection policy, consider the effect of directives such as DefaultRoot on local socket communication: once a user has been chroot()ed, the local socket file will probably not be available within the chroot directory tree, and attempts to reopen communication will fail. One way around this may be to use hardlinks within the user's directory tree. PERSESSION connections are not affected by this because the database will be opened prior to the chroot() call, and held open for the life of the session. Network communications are not affected by this problem. For example, while localhost would not work for MySQL since the MySQL client library will try to use socket communications for that host, 127.0.0.1 will work (as long as your database is setup to accept these connections).

Examples:

  # Connect to the database 'ftpusers' via the default port at host
  # 'foo.com'.  Use a NULL username and NULL password when connecting.
  # A connection policy of PERSESSION is used.
  SQLConnectInfo ftpusers@foo.com

  # Connect to the database 'ftpusers' via port 3000 at host 'localhost'.
  # Use the username 'admin' and a NULL password when connecting.
  # A connection policy of PERSESSION is used.
  SQLConnectInfo ftpusers:3000 admin

  # Connect to the database 'ftpusers' via port 3000 at host 'foo.com'.
  # Use the username 'admin' and password 'mypassword' when connecting.
  # A connection policy of PERSESSION is used.
  SQLConnectInfo ftpusers@foo.com:3000 admin mypassword

  # Connect to the database 'ftpusers' via port 3000 at host 'foo.com'.
  # Use a username of 'admin' and a password of 'mypassword' when
  # connecting.  A 30 second timer of connection inactivity is activated.
  SQLConnectInfo ftpusers@foo.com:3000 admin mypassword 30

Note: this directive deprecates the following configuration directives:

• MySQLInfo
• PostgresInfo
• PostgresPort

SQLDefaultGID

The SQLDefaultGID directive configures the default GID for users. This value must be greater than any configured SQLMinUserGID.

See also: SQLMinUserGID

SQLDefaultHomedir

The SQLDefaultHomedir directive configures a default home directory for all users authenticated with this module, overriding any (deprecated) SQLHomedirField directive. If no home directory is set with either directive, authentication fails. This directive does not change the data retrieved from the database: if you specify a home directory field to SQLUserInfo, that field's data will be returned as the user's home directory, whether that data is a legal directory, or an empty string, or NULL.

See also: SQLUserInfo

SQLDefaultUID

The SQLDefaultUID directive configures the default UID for users. This value must be greater than any configured SQLMinUserUID.

See also: SQLMinUserUID

SQLEngine

The SQLEngine directive is used to specify how mod_sql will operate. By default, SQLEngine is on, and mod_sql will operate as normal. Setting SQLEngine to off will effectively disable the module.

In addition to on and off, SQLEngine accepts two other values: auth and log. If you wish to use mod_sql for authentication and not for logging (via SQLLog), use auth. Conversely, to do only SQLLog-type logging, and no authentication, use log.

This directive can be used to have <Anonymous> sections that do not use mod_sql, e.g.:

  <Anonymous ~ftp>
    ...
    SQLEngine off
    ...
  </Anonymous>

SQLGroupInfo

The SQLGroupInfo directive configures the group table and fields that hold group information. The parameters for this directive are described below:

• grouptable
  Specifies the name of the table that holds group information.
  


• groupname
  Specifies the field in the group table that holds the group name.
  


• gid
  Specifies the field in the group table that holds the group's GID.
  


• members
  Specifies the field in the group table that holds the group members.
  

SQLGroupWhereClause

The directive is used to configure a WHERE clause that is added to every group query. The WHERE clause must contain all relevant punctuation, and must not contain a leading "and".

As an example of a possible use for this directive, imagine if your group table included a "LoginAllowed" field:

  SQLGroupWhereClause "LoginAllowed = 'true'"

  " WHERE (LoginAllowed = 'true')"

SQLLog

This directive is used to log information to a database table. Multiple SQLLog directives can be in effect for any command; for example, a user changing directories can trigger multiple logging statements.

The first parameter to SQLLog, the cmd-set, is a comma-separated (no spaces) list of FTP commands for which this log command will trigger. The list of commands is too long to list in entirety; commands include CWD, DELE, HELP, LIST, MKD, MODE, NLST, PASS, PASV, PORT and many more. For the complete list check the FTP RFCs. Normally mod_sql will log events after they have completed successfully; in the case of the QUIT command, mod_sql logs prior to the server's processing of the command. (Note, however, that the client may not issue a QUIT before logging out; in this case, use a command of EXIT rather than QUIT. EXIT is not a real FTP command, but it is used here to provide a means for having SQLLog work whenever a session ends.)

FTP commands in the command set will only be logged if they complete successfully. Prefixing any command with "ERR_" will cause logging to occur only if there was an error in the command's processing. To log both errors and successful completion of a given command X, therefore, you'll need both "X" and "ERR_X" in your cmd-set.

The special command "*" matches all FTP commands, while "ERR_*" matches all errors.

The second parameter is the name of a query defined by a SQLNamedQuery directive. The query must be an UPDATE, INSERT, or FREEFORM type query; explicit SELECT queries will not be processed.

The third parameter is optional. If you add "IGNORE_ERRORS" as the third parameter, SQLLog will not check for errors in the processing of the named query. Any value for this parameter other than the string "IGNORE_ERRORS" (case-insensitive) will not cause errors to be ignored.

Normally, SQLLog directives are considered important enough that errors in their processing will cause mod_sql to abort the client session. References to non-existent named queries will not abort the client session, but may result in database corruption (in the sense that the expected database UPDATE or INSERT will not occur). Check your directives carefully.

Examples:

  SQLLog PASS updatecount
  SQLNamedQuery updatecount UPDATE "count=count+1 WHERE userid='%u'" users

  SQLLog CWD updatedir
  SQLNamedQuery updatedir UPDATE "cwd='%d' where userid='%u'" users

  SQLLog RETR,STOR insertfileinfo
  SQLNamedQuery insertfileinfo INSERT "'%f', %b, '%u@%v', now()" filehistory

SQLLogFile

The SQLLogFile directive is used to a specify a log file for mod_sql reporting and debugging, and can be done a per-server basis. The file parameter must be the full path to the file to use for logging. Note that this path must not be to a world-writeable directory and, unless AllowLogSymlinks is explicitly set to on (generally a bad idea), the path must not be a symbolic link.

If file is "none", no logging will be done at all; this setting can be used to override a SQLLogFile setting inherited from a <Global> context.

SQLMinID

SQLMinID is a quick way of setting both SQLMinUserGID and SQLMinUserUID. These values are checked whenever retrieving a user's GID or UID.

See also: SQLMinUserGID, SQLMinUserUID

SQLMinUserGID

SQLMinUserGID is checked whenever retrieving a user's GID. If the retrieved value for GID is less than the value of SQLMinUserGID, it is reported as the value of SQLDefaultGID.

SQLMinUserUID

SQLMinUserUID is checked whenever retrieving a user's UID. If the retrieved value for UID is less than the value of SQLMinUserUID, it is reported as the value of SQLDefaultUID.

SQLNamedQuery

SQLNamedQuery specifies a query and an identifier (name) for later use by SQLShowInfo and SQLLog.

It is strongly recommended that you read documentation on the LogFormat and ExtendedLog directives, as the meta-sequences available to SQLNamedQuery are largely equivalent.

The first parameter, name, should be unique across all named queries and must not contain spaces. The result of re-using a name is undefined.

The second parameter, type, is the type of query, either "SELECT", "UPDATE", "INSERT", or "FREEFORM". See the note below for information on FREEFORM type queries.

The third parameter is the substance of the database query itself; this should match the form of the second parameter. The meta-sequences accepted are exactly equivalent to the LogFormat directive except the following are not accepted:

• %{FOOBAR}e
  For LogFormat, this logs the content of environment variable "FOOBAR". In a SQLNamedQuery SQL statement, however, environment variables can be referenced using %{env:FOOBAR}.


• %{format}t and %t
  These two meta-sequences logged the local server time; they are not available in mod_sql. Your database undoubtedly provides another way to get the time; for example, MySQL provides the now() function.
  

• %d
  The current working directory or "-" if none.
  


• %{n}
  This meta-sequence is used internally by mod_sql and other third-party modules and patches to pass information to the database. Using this meta-sequence in anything other than an INSERT or UPDATE query is an error, and using this meta-sequence unless directed to by a third-party module or patch is also an error.
  

The fourth parameter, table, is only necessary for UPDATE or INSERT type queries, but is required for those types.

Note: FREEFORM queries are a necessary evil; the simplistic query semantics of the UPDATE, INSERT, and SELECT type queries do not sufficiently expose the capabilities of most backend databases. At the same time, using a FREEFORM query makes it impossible for mod_sql to check whether the query type is appropriate, making sure that a SELECT query is not used in a SQLLog directive, for instance. Wherever possible, it is recommended that a specific query type be used.

Examples:

  SQLNamedQuery count SELECT "count from users where userid='%u'"

  SQLNamedQuery updatecount UPDATE "count=count+1 WHERE userid='%u'" users

  SQLNamedQuery accesslog INSERT "now(), '%u'" accesslog

  SQLNamedQuery accesslog FREEFORM "INSERT INTO accesslog(date, user) VALUES (now(), '%u')"

SQLNegativeCache

SQLNegativeCache specifies whether or not to cache negative responses from SQL lookups when using SQL for UID/GID lookups. Depending on your SQL tables, there can be a significant delay when a directory listing is performed as the UIDs not in the SQL database are repeatedly looked up in an attempt to present usernames instead of UIDs in directory listings. With SQLNegativeCache set to on, negative ("not found") responses from SQL queries will be cached and speed will improve on directory listings that contain many users not present in the SQL database.

SQLOptions

The SQLOptions directive is used to tweak various optional behavior of mod_sql.

Example:

  SQLOptions noDisconnectOnError

The currently implemented options are:

• noDisconnectOnError
  

  By default, mod_sql will automatically disconnect the client whenever there is a database error. If this option is enabled, mod_sql will attempt to continue functioning despite database errors.

• useNormalizedGroupSchema
  

  If this option is enabled, then mod_sql, when retrieving all of the groups for a user, will use a SQL statement like:

    SELECT groupname, groupid, member FROM grouptable WHERE member = $userName
  

  rather than the statement it uses by default, which is:

    SELECT groupname, groupid, member FROM grouptable WHERE member = $userName OR
      member LIKE '%,$userName' OR member LIKE '$username,%' OR
      member LIKE '%,$userName,%'
  

  To put it another way, when this SQLOption is used, mod_sql will not treat the member column of the group table as a comma-delimited list of user names, but rather as a single user name. Thus, to have a user belong in multiple groups with this normalized schema, the group table would have individual rows for each user/group pair.

SQLRatios

SQLRatioStats

SQLShowInfo

This directive creates a message to be sent to the user after any successful command.

The first parameter, the cmd-set, is a comma separated (no spaces) list of FTP commands for which this log command will trigger. The list of commands is too long to list in entirety; commands include: CWD, DELE, HELP, LIST, MKD, MODE, NLST, PASS, PASV, PORT and many more. For the complete list check the FTP RFCs.

FTP commands in the command set will only be triggered if they complete successfully. Prefixing any command with "ERR_" will show information only if there was an error in command processing. To send a message on both errors and successfull completion of a given command X, therefore, you'll need both "X" and "ERR_X" in your cmd-set.

The special command "*" matches all FTP commands, while "ERR_*" matches all errors.

The second parameter, numeric, specifies the numeric value of the message returned to the FTP client. Do not choose a number blindly: message numbers may be parsed by clients. In most cases you will want to use 214, the "Help message" numeric. It specifies that the information is only meant to be human readable.

The third parameter, query-string, is exactly equivalent to the query-string parameter to the SQLLog directive, with one addition:

• %{name}
  The first return value from the SQLNamedQuery identified by "name". There is currently no way to retrieve more than one value from the database at a time.
  

  SQLNamedQuery count SELECT "count from users where userid='%u'"
  SQLShowInfo PASS "230" "You've logged on %{count} times, %u"

SQLUserInfo

The SQLUserInfo directive configures the user table and fields that hold user information. If you need to change any of these field names from the default, you must specify all of them, whether NULL or not. The parameters are described below:

• usertable
  Specifies the name of the table that holds user information.
  


• username
  Specifies the field in the user table that holds the username.
  


• passwd
  Specifies the field in the user table that holds the user's password.
  


• uid
  Specifies the field in the user table that holds the user's UID. When a UID is retrieved from the database it is checked against the value of SQLMinUserUID. If the field name is specified as "NULL" the database will not be queried for this value and the user's UID will be set to the value of SQLDefaultUID.
  


• gid
  Specifies the field in the user table that holds the user's GID. When a GID is retrieved from the database it is checked against the value of SQLMinUserGID. If the field name is specified as "NULL" the database will not be queried for this value and the user's GID will be set to the value of SQLDefaultGID.
  


• homedir
  Specifies the field in the user table that holds the user's home directory. If the fieldname is specified as "NULL" the database will not be queried for this value and the user's home directory will be set to the value of SQLDefaultHomedir. If no home directory is set with either directive, user authentication will be automatically turned off.
  


• shell
  Specifies the field in the user table that holds the user's shell. If the fieldname is specified as "NULL" the database will not be queried and the shell will be reported as an empty string ("").
  

As of 1.2.9rc1, the SQLUserInfo directive accepts an alternate syntax:

  SQLUserInfo custom:/name

See Also: SQLLog, SQLNamedQuery

SQLUserWhereClause

The directive is used to configure a WHERE clause that is added to every user query. The WHERE clause must contain all relevant punctuation, and must not contain a leading "and".

As an example of a possible use for this directive, imagine if your user table included a "LoginAllowed" field:

  SQLUserWhereClause "LoginAllowed = 'true'"

  " WHERE (LoginAllowed = 'true')"

Installation

  ./configure --with-modules=sql-module-opts

You will also need to tell configure how to find the database-specific libraries and header files:

  ./configure --with-modules=sql-module-opts \
    --with-includes=/path/to/db/header/file/dir \
    --with-libraries=/path/to/db/library/file/dir

Complete the build with the following standard commands:

  make
  make install