mod_sqlcontrib/mod_sql.c,
contrib/mod_sql.h, contrib/mod_sql_mysql.c, and
contrib/mod_sql_postgres.c files for ProFTPD 1.2, and is not
compiled by default. Installation instructions are discussed
here.
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.
mod_sqlpw.
mod_ldap, which lit the way, here and
there.
mod_auth_mysql module, which informed the
SQLAuthTypes directive.
<VirtualHost>, <Global>
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:
SQLAuthenticate users groups userset groupset.mod_sql authentication functions.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).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).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.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 | |--------------------------------------------------|If you use the "fast" option,
mod_sql assumes that all
entries are structured like the last example.
<VirtualHost>, <Global>
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:
'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(3)
form.NULL values are not acceptable as empty
passwords. Be very careful if using this authentication method.'{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.For example:
SQLAuthTypes Crypt Emptyconfigures
mod_sql to first attempt to verify the password
using the Unix crypt(3) function, then, if that fails, determine
if the password in the database is empty (thus matching any given
password). If all of the configured authentication methods fail,
mod_sql will fail to authenticate the user.
Note: this directive deprecates the following configuration directives:
SQLEmptyPasswords
SQLEncryptedPasswords
SQLPlaintextPasswords
SQLScrambledPasswords
SQLSSLHashedPasswords
<VirtualHost>, <Global>
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 policy will be used.
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]hostname will default to a backend-specific hostname (which happens to be 'localhost' for both the MySQL and Postgres backends), and port will default to a backend-specific default port (3306 for the MySQL backend, 5432 for the Postgres backend).
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.From the PostgreSQL documentation:
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 30Backends may require different information in the connection-info field; check your backend module for more detailed information.
Note: this directive deprecates the following configuration directives:
MySQLInfo
PostgresInfo
PostgresPort
<VirtualHost>, <Global>
The SQLDefaultGID directive configures the default GID for users.
This value must be greater than any configured SQLMinUserGID.
See also: SQLMinUserGID
<VirtualHost>, <Global>
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
<VirtualHost>, <Global>
The SQLDefaultUID directive configures the default UID for users.
This value must be greater than any configured SQLMinUserUID.
See also: SQLMinUserUID
<VirtualHost>, <Global>, <Anonymous>
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>
<VirtualHost>, <Global>
The SQLGroupInfo directive configures the group table and fields
that hold group information. The parameters for this directive are
described below:
<VirtualHost>, <Global>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'"would be appended to every group-related query as the string:
" WHERE (LoginAllowed = 'true')"
<VirtualHost>, <Global>
The SQLHomedirOnDemand directive configures mod_sql
to automatically create a user's home directory if that directory does not
exist during the login process.
<VirtualHost>, <Global>
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'" userstogether, these replicate the deprecated "
SQLLoginCountField count" directive; if the current
user was "joe", this would translate into the query
"UPDATE users SET count=count+1 WHERE userid='joe'". This query
would run whenever a user was first authenticated.
SQLLog CWD updatedir SQLNamedQuery updatedir UPDATE "cwd='%d' where userid='%u'" userstogether these replicate the logging side of the deprecated "
SQLLogDirs cwd" directive; if the current user was
"joe" and the current working directory were /tmp, this
would translate into the query "UPDATE users SET cwd='/tmp' WHERE userid='joe'". This query would run whenever a user changed directories.
SQLLog RETR,STOR insertfileinfo SQLNamedQuery insertfileinfo INSERT "'%f', %b, '%u@%v', now()" filehistorywould log the name of any file stored or retrieved, the number of bytes transferred, the user and host doing the transfer, and the time of transfer (at least in MySQL). This would translate into a query like: "INSERT INTO filehistory VALUES ('somefile', 12345, 'joe@joe.org', '21-05-2001 20:01:00')"
<VirtualHost>, <Global>
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.
<VirtualHost>, <Global>
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
<VirtualHost>, <Global>
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.
<VirtualHost>, <Global>
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.
<VirtualHost>, <Global>
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}eLogFormat, this logs the content of environment variable
"FOOBAR". This is not bavailable in
mod_sql.%{format}t and %tmod_sql. Your database
undoubtedly provides another way to get the time; for example, MySQL
provides the now() function.LogFormat
meta-sequences:
%d%{n}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.FREEFORM queries which will be sent directly to the
database. The examples below show the way queries are built from the
arguments.
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'"creates a query named "count" which could be used by
SQLShowInfo to inform a user of their login count. The actual
query would look something like "SELECT count FROM users WHERE
userid='matilda'" for user "matilda".
SQLNamedQuery updatecount UPDATE "count=count+1 WHERE userid='%u'" userscreates a query named "updatecount" which could be used by
SQLLog to update a user login counter in the table
users. The actual query would look something like "UPDATE
users SET count=count+1 WHERE userid='persephone'" for user
"persephone".
SQLNamedQuery accesslog INSERT "now(), '%u'" accesslogcreates a query named "accesslog" which could be used by
SQLLog to track access times by clients. The actual query would
look something like "INSERT INTO accesslog VALUES (now(), 'pandora')"
for user "pandora". Note that this may be too simplistic for your
table structure, since most databases require data for all columns to be
provided in an INSERT statement of this form. See the following
FREEFORM query for an example of something which may suit your
needs better.
SQLNamedQuery accesslog FREEFORM "INSERT INTO accesslog(date, user) VALUES (now(), '%u')"creates a query named "accesslog" which could be used by
SQLLog to track access times by clients. The actual query would
look something like
"INSERT INTO accesslog(date, user) VALUES (now(), 'tilda')" for
user "tilda".
<VirtualHost>, <Global>
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.
<VirtualHost>, <Global>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}SQLNamedQuery
identified by "name". There is currently no way to retrieve more
than one value from the database at a time.SELECT or
-FREEFORM type queries, or references to queries which return a
NULL first value, will be replaced with the string "{null}".
For example:
SQLNamedQuery count SELECT "count from users where userid='%u'"
SQLShowInfo PASS "230" "You've logged on %{count} times, %u"
As long as the information is in the database, these two directives specify
that the user will be greeted with their login count each time they
successfully login. Note the use of the "230" numeric, which means
"User logged in, proceed". "230" is appropriate in this
case because the message will be sent immediately after their password has
been accepted and the session has started.
<VirtualHost>, <Global>
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:
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.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.SQLDefaultHomedir. If no home directory is set with
either directive, user authentication will be automatically turned off.
As of 1.2.9rc1, the SQLUserInfo directive accepts
an alternate syntax:
SQLUserInfo custom:/namewhere name refers to a configured SELECT
SQLNamedQuery.
This named query must return one row, and return the following columns,
in this order: username, passwd, uid, gid, homedir, shell. The
configured query may make use of the variables mentioned in the
SQLLog description. This syntax allows the administrator
a flexible way of constructing queries as needed. Note that if you want
use the given USER name, you should use the %U
variable, not %u; the latter requires the locally authenticated
user name, which is exactly what SQLUserInfo is meant to
provide.
See Also:
SQLLog,
SQLNamedQuery
<VirtualHost>, <Global>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'"would be appended to every user-related query as the string:
" WHERE (LoginAllowed = 'true')"
mod_sql module is distributed with ProFTPD. Simply
follow the normal steps for using third-party modules in proftpd:
./configure --with-modules=sql-module-optswhere the specific sql-module-opts depend on your database needs. For example, if using MySQL, sql-module-opts would be "mod_sql:mod_sql_mysql".
mod_sql is the main
SQL-processing engine, and mod_sql_mysql is the backend
sub-module that handles MySQL-specific details. If Postgres is your
database of choice, sql-module-opts would be
"mod_sql:mod_sql_postgres".
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