mod_sql v.4.0
=======
mod_sql is an authentication and logging module for ProFTPD. It is
comprised of a front end module, mod_sql, and backend,
database-specific modules such as mod_sql_mysql or
mod_sql_postgres. mod_sql leaves the specifics of database handling to
the backend modules.
Please check the mod_sql homepage for any updates to the source or to
this file: http://www.lastditcheffort.org/~aah/proftpd/mod_sql/
Sections:
1. How do I use ProFTPD with mod_sql?
2. How do I setup my SQL database for use with mod_sql?
3. Setup Notes
4. Directives
5. Deprecated Directives
6. Thanks
7. Author
Appendices:
A. Example SQL for Table Creation
B. Using mod_sql in Third-Party Modules
C. Example Code for Hooking to mod_sql
=====================================
1. How do I use ProFTPD with mod_sql?
=====================================
The current supported version of mod_sql is distributed with ProFTPD.
Everything in this tarball belongs in the contrib subdirectory of the
ProFTPD source tree:
# cd contrib
# tar -xzvf <tarball-file-name>
To build ProFTPD with mod_sql support, call configure with the
following arguments (for example):
If you're using the MySQL backend:
./configure --with-modules=mod_sql:mod_sql_mysql
If you're using the Postgres backend:
./configure --with-modules=mod_sql:mod_sql_postgres
You will need to tell configure how to find your database-specific
libraries and header files; I use the MySQL backend and call configure
like this:
./configure --with-modules=mod_sql:mod_sql_mysql \
--with-includes=/usr/local/include \
--with-libraries=/usr/local/lib/mysql
If you use PostgreSQL, you'll need something like this:
./configure --with-modules=mod_sql:mod_sql_postgres \
--with-includes=/usr/local/include \
--with-libraries=/usr/local/lib
Now, read the Directives section below.
===================================================
2. How do I setup my database for use with mod_sql?
===================================================
I'm assuming you know how to use your database system.
At the minimum, mod_sql must be able to access a table with two
columns: userid and passwd. For full functionality you probably need
to do more, including creating another database table to hold group
information.
In the following table headings, 'COLUMN' is the column name, 'TYPE'
is the datatype expected (text or numeric), 'R' means "required
column", 'DUP' means 'duplicate values allowed', 'NULL?' means "NULL
values allowed". and 'USE' gives a *very* brief description of what
goes in there.
USERTABLE: This table *MUST* exist in the database.
COLUMN TYPE R DUP NULL? USE
---------------------------------------------------------
userid text Y N N user's login id
passwd text Y Y N user's password
uid num N N* Y user's uid
gid num N Y Y user's gid
homedir** text N Y Y user's homedir
shell*** text N Y Y user's shell
* while there is nothing in ProFTPD or mod_sql to stop you from
making this mistake, you should make sure you know what you're
doing if you use duplicate values for UIDs. While this may
superficially make administration easier, it lessens your ability
to make per-user security changes and it lessens your ability to
track a user's actions.
** see the description of the directives 'SQLDefaultHomedir' and
'SQLHomedirField'
*** see the description of the core directive 'RequireValidShell'
GROUPTABLE: For full functionality of this module, this table should
exist. If you elect *NOT* to create it, you *MUST* set the
'SQLAuthenticate' directive correctly (see the documentation).
COLUMN TYPE R NULL? USE
---------------------------------------------------------
groupname text Y N group's name
gid num Y N group's gid
members text Y Y group's members
Note: multiple members per group must be separated by commas and
should not include whitespace.
Note: Normally mod_sql will concatenate all matching group rows --
that is, you can have multiple rows for each group with only one
member per group, or you can have a single row with multiple groups,
or you can mix and match. If you elect to use the 'fast' option for
'groupset' in the 'SQLAuthenticate' directive, you may not have
multiple rows per group.
==============
3. Setup Notes
==============
SLOW LOGIN AND TIMEOUT PROBLEMS
A common question on the proftpd-users mailing list is "why does
mod_sql iterate through every user in the database?" Here's why.
mod_sql is designed to handle all authentication functions that
ProFTPD core 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).
If you see mod_sql iterating through all groups or users, it's doing
so because it's been asked to do so by the ProFTPD core. Since
there's 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 front-loading the time
cost.
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, ProFTPD 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 dumb-down
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 auth handlers
exist in the system. Keep in mind that using 'SQLAuthenticate' in this
way means ProFTPD 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) didn't handle the {set|get|end}{pw|gr}ent calls 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'll need to re-evaluate the way you're using the database, and
where you should be storing your user/group information.
=============
4. Directives
=============
Directives are listed here in alphabetical order. If you're just
starting with mod_sql, you'll probably be most interested in
'SQLConnectInfo', 'SQLAuthTypes', and the directives that control
which tables and fields to pull data from ('SQLUserInfo' and
'SQLGroupInfo'). Most directives have reasonable defaults; in
the most extreme case you'll only have to set two directives to make
this module work: 'SQLConnectInfo' and 'SQLAuthTypes'.
-------------------
SQLAuthenticate
Syntax: SQLAuthenticate on|off
or: SQLAuthenticate [users[*]] [groups[*]]
[userset[fast]] [groupset[fast]]
Default: on
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
SQLAuthenticate controls every aspect of mod_sql's behavior -- the
directive tells mod_sql what to authenticate (users and/or groups) and
how to authenticate them. These arguments can appear in any order.
The choices are as follows:
on:
mod_sql pretends you wrote 'SQLAuthenticate users groups userset
groupset'.
off:
mod_sql turns off all user and group lookups.
users[*]:
If present, mod_sql will do user lookups. Suffixing 'user' with an
asterisk ('users*') tells mod_sql to claim to be authoritative for
user lookups -- no other login handler will be checked for user
retrieval or user authentication. 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. Suffixing 'group' with an
asterisk ('groups*') tells mod_sql to claim to be authoritative for
group lookups -- no other auth module will be checked. 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 argument 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
argument will have no effect if 'groups' is not specified.
NOTES
'SQLLog' and 'SQLShowInfo' directives will always be processed by
mod_sql. The 'SQLAuthenticate' directive only affects the user and
group lookup portions of the module.
Turning off (not including) userset or groupset 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 auth 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 (n+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.
-------------------
SQLAuthTypes
Syntax: SQLAuthTypes [OpenSSL] [Crypt] [Backend] [Plaintext] [Empty]
Default: none
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Specifies the allowed authentication types and their check order.
Note: YOU MUST SPECIFY AT LEAST ONE AUTHENTICATION METHOD. If no
authentication methods are specified, mod_sql will not authenticate
users.
For example:
SQLAuthTypes Crypt Empty
means check whether the password in the database matches in UNIX
crypt() format; if that fails, check to see if the password in the
database is empty (matching ANY given password); if that fails,
mod_sql refuses to authenticate the user.
Current Types
Plaintext: allows passwords in the database to be in plaintext
OpenSSL: allows passwords in the database to be of the form
'{digestname}hashedvalue'. This check is only available if
you define 'HAVE_OPENSSL' when you compile proftd and you
link with the OpenSSL 'crypto' library.
Crypt: allows passwords in the database to be in UNIX crypt() form
Backend: a database-specific backend check function. Not all
backends support this. Specifically, the MySQL backend
uses this type to authenticate MySQL 'PASSWORD()' encrypted
passwords. The Postgres backend does nothing. CAREFUL.
If your mysql activity log is world-readable, the user
password WILL BE VISIBLE.
Empty: allows empty passwords in the database, which match against
ANYTHING the user types in. The database field must be a
truly empty string -- that is, NULL values are never
accepted. BE VERY CAREFUL WITH THIS AUTHTYPE.
-------------------
SQLConnectInfo
Syntax: SQLConnectInfo connection-info [username] [password] [policy]
Default: none
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Specifies connection information. Connection-info specifies the
database, host, port, and other backend-specific information.
username and password specify the username and password to connect as,
respectively. 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 is specified in the configuration file, mod_sql
will turn itself off - it won't get in the way of anything, but it
won't support any database features.
Any given backend has the opportunity (but not the responsibility) to
check for syntax errors in the connection-info field at proftpd
startup, but you shouldn't expect semantic errors (i.e., can't 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 3 options:
PERSESSION : open connection at the start of the session and
close connection at the end of the session
PERCALL : open connection prior to each database call and
close connection after each database call
# (TIMED) : timed connections that close themselves after
# seconds inactivity
If policy is not specified, if policy is not a number, if policy is
a number less than 1, or if policy is the string "PERSESSION", the
PERSESSION policy will be used.
If policy is the string "PERCALL" the PERCALL policy will be used.
If 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 backend connection-info is expected to be of
the form:
database[@hostname][:port]
hostname will default to a backend-specific hostname ('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).
MySQL: from the 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.'
PostgreSQL: from the 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.'
NOTE: If you plan to use either PERCALL or TIMED connections, 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-ed directory tree,
and attempts to re-open communication will fail. One way around this
may be to use hardlinks within the user's directory tree. PERSESSION
connections are not effectd 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 effected 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:
SQLConnectInfo ftpusers@foo.com
means "Try connecting to the database 'ftpuser' via the default port
at 'foo.com'. Use a NULL username and a NULL password. Use the
PERSESSION policy."
SQLConnectInfo ftpusers:3000 admin
means "Try connecting to the database 'ftpuser' via port 3000 at
'localhost'. Use the username 'admin' and a NULL password. Use the
PERSESSION policy"
SQLConnectInfo ftpusers@foo.com:3000 admin mypassword PERCALL
means "Try connecting to the database 'ftpuser' via port 3000 at
'foo.com'. Use the username 'admin' and the password
'mypassword'. Use the PERCALL policy."
SQLConnectInfo ftpusers@foo.com:3000 admin mypassword 30
means "Try connecting to the database 'ftpuser' via port 3000 at
'foo.com'. Use the username 'admin' and the password
'mypassword'. Use a 30 second inactivity timer."
Backends may require different information in the connection-info
field; check your backend module for specifics.
-------------------
SQLDefaultGID
Syntax: SQLDefaultGID defaultgid
Default: 65533
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Sets the default GID for users. Must be greater than SQLMinUserGID.
-------------------
SQLDefaultHomedir
Syntax: SQLDefaultHomedir /path/to/virtual/site
Default: none
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Specifies a default homedir for all users authenticated with this
module, overriding any SQLHomedirField directive. If no homedir is
set with either directive, authentication fails. This directive does
not change the data retrieved from the database -- if you specify a
homedir 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.
-------------------
SQLDefaultUID
Syntax: SQLDefaultUID defaultuid
Default: 65533
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Sets the default UID for users. Must be greater than SQLMinUserUID.
-------------------
SQLGroupInfo
Syntax: SQLGroupInfo grouptable groupname gid members
Default: groups groupname gid members
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Specifies the group table and fields that hold group information.
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.
If you need to change ANY of these field names from the default, you
need to specify ALL of them.
-------------------
SQLHomedirOnDemand
Syntax: SQLHomedirOnDemand on|off
Default: off
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Specifies whether to automatically create a user's home directory if
it doesn't exist at login.
-------------------
SQLLog
Syntax: SQLLog cmd-set query-name [IGNORE_ERRORS]
Default: none
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Logs information to a table. Multiple SQLLog directives can be in
effect for any command; for example, a user changing directories can
trigger multiple logging statements.
The first argument 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 ProFTPD's processing of the
command.
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 it's processing. To log both
errors and successfull completion of a given command X, therefore,
you'll need both 'X' and 'ERR_X' in your cmd-list.
The special command '*' matches all FTP commands, while 'ERR_*'
matches all errors.
The second argument is the name of a query defined by the
'SQLNamedQuery' directive. The query must be an UPDATE, INSERT, or
FREEFORM type query -- explicit SELECT queries will not be processed.
The third argument is optional. If you add 'IGNORE_ERRORS' as the
third argument, SQLLog *will not* check for errors in the processing
of the named query. Any value for this argument 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 won't occur). Check
your directives carefully.
Examples:
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1 WHERE userid='%u'" users
together, these replicate the old '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'" users
together these replicate the logging side of the old '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()" filehistory
would 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')"
-------------------
SQLMinID
Syntax: SQLMinID minimumid
Default: 999
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
SQLMinID is a quick way of setting both SQLMinUserGID and
SQLMinUserUID. These values are checked whenever retrieving a user's
GID or UID. See the 'SQLMinUserGID' and 'SQLMinUserUID' directives.
-------------------
SQLMinUserGID
Syntax: SQLMinUserGID minimumuid
Default: 999
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
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
Syntax: SQLMinUserUID minimumuid
Default: 999
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
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
Syntax: SQLNamedQuery name type query-string [table]
Default: none
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
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 argument, 'name', should be unique across all named queries
and must not contain spaces. The result of re-using a name is
undefined.
The second argument, 'type', is the type of query -- either 'SELECT',
'UPDATE', 'INSERT', or 'FREEFORM'. See the note below for information
on FREEFORM type queries.
The third argument is the substance of the database query itself;
this should match the form of the second argument. The meta-sequence
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'. This IS NOT available in mod_sql.
%{format}t: these two meta-sequences logged the local server time;
and %t : They ARE NOT available in mod_sql. Your database
undoubtedly provides another way to get the time; for
example, MySQL provides the now() function.
and the following IS IN ADDITION TO the 'LogFormat' meta-sequences:
%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 correct form of a query will be built from the directive
arguments, except in the case of FREEFORM queries which will be sent
directly to the database. The examples below show the way queries are
built from the arguments.
The fourth argument, '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's recommended that a specific query type is 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'" users
creates 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'" accesslog
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 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 portion of the directive removed for space reasons)
.. 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'.
-------------------
SQLRatios
Syntax: SQLRatios on|off
or: SQLRatios file-ratio file-creds byte-ratio byte-creds
Default: off
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Activates mod_ratio's storage of per-user ratio data. See the
mod_ratio documentation for more information on these fields and their
meaning. This directive requires additional fields in the user table:
COLUMN TYPE DATA STORED
------ ---- -----------
frate num
fcred num
brate num
bcred num
The column names above are the default names used if SQLRatios is
set to "on".
This directive is used solely by mod_ratio. Without mod_ratio
running, this directive will have no effect.
-------------------
SQLRatioStats
Syntax: SQLRatioStats on|off
or: SQLRatioStats f-stored f-retrieved b-stored b-retrieved
Default: off
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Activates mod_ratio's logging of the number of files stored, files
retrieved, bytes stored, and bytes retrieved by the users. See the
mod_ratio documentation for more information on these fields and their
meaning. This directive requires additional fields in the user table:
COLUMN TYPE DATA STORED
------ ---- -----------
fstor num Number of files stored
fretr num Number of files retrieved
bstor num Number of bytes stored
bretr num Number of bytes retrieved
The column names above are the default names used if SQLRatioStats is
set to "on".
NOTE: The bstor and bretr fields should be able to hold values larger
than 32 bits, unless you want the counting to stop at approx. 4Gb.
This directive is used solely by mod_ratio. Without mod_ratio
running, this directive will have no effect.
-------------------
SQLShowInfo
Syntax: SQLShowInfo cmd-set numeric query-string
Default: none
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Creates a message to be sent to the user after any successful
command.
The first argument, 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
info 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-list.
The special command '*' matches all FTP commands, while 'ERR_*'
matches all errors.
The second argument, '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'll want
to use "214", the "Help message" numeric. It specifies that the
information is only meant to be human readable.
The third argument, 'query-string', is exactly equivalent to the
'query-string' argument 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.
Any references to non-existent named queries, non-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.
-------------------
SQLUserInfo
Syntax: SQLUserInfo usertable username passwd uid gid homedir shell
Default: users userid password uid gid homedir shell
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Specifies the user table and fields that hold user information.
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 homedir will be set to the value of SQLHomedir. If
no homedir 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 ("").
Read the above descriptions. If you need to change ANY of these field
names from the default, you need to specify ALL of them, whether NULL
or not.
-------------------
SQLUserWhereClause
Syntax: SQLUserWhereClause whereclause
Default: none
Context: server config, <Global>, <VirtualHost>
Module: mod_sql
Specifies 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')"
-------------------
========================
5. Deprecated Directives
========================
The following directives were recently deprecated and have been
removed. None of these directives is supported.
Original Directive Replaced With
------------------ -------------
MySQLInfo SQLConnectInfo
PostgresInfo SQLConnectInfo
PostgresPort SQLConnectInfo
SQLAuthoritative SQLAuthenticate
SQLDoAuth SQLAuthenticate
SQLDoGroupAuth SQLAuthenticate
SQLEmptyPasswords SQLAuthTypes
SQLEncryptedPasswords SQLAuthTypes
SQLGidField SQLUserInfo
SQLGroupGIDField SQLGroupInfo
SQLGroupMembersField SQLGroupInfo
SQLGroupTable SQLGroupInfo
SQLGroupnameField SQLGroupInfo
SQLHomedir SQLDefaultHomedir
SQLHomedirField SQLUserInfo
SQLKey SQLUserWhereClause
SQLKeyField SQLUserWhereClause
SQLLogDirs SQLLog
SQLLogHits SQLLog
SQLLogHosts SQLLog
SQLLogStats SQLRatioStats
SQLLoginCountField SQLLog
SQLPasswordField SQLUserInfo
SQLPlaintextPasswords SQLAuthTypes
SQLProcessPwEnt SQLAuthenticate
SQLProcessGrEnt SQLAuthenticate
SQLSSLHashedPasswords SQLAuthTypes
SQLScrambledPasswords SQLAuthTypes
SQLShellField SQLUserInfo
SQLUidField SQLUserInfo
SQLUserTable SQLUserInfo
SQLUsernameField SQLUserInfo
SQLWhereClause SQLUserWhereClause
=========
6. Thanks
=========
* Johnie Ingram <johnie@netgod.net> for the original mod_sqlpw.
* TJ Saunders && Jesse Sipprell for dealing with my inane questions.
* John Morrissey for mod_ldap, which lit the way, here and there.
* Zeev Suraski <bourbon@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.
=========
9. Author
=========
mod_sqlpw was originally written and maintained by Johnie Ingram
<johnie@netgod.net>.
mod_sql is based on mod_sqlpw and shares much of the same code. It
is being developed and maintained by Andrew Houghton <aah@acm.org>
Please file any bug reports/suggestions/feature requests with the ProFTPD
bug database at http://www.ProFTPD.org
=================================
A. Example SQL for Table Creation
=================================
These two SQL statements should work for any ANSI SQL compliant
databases, and are known to work for MySQL and PostgreSQL. They both
fully specify the tables as defined in section 2 of this document,
with reasonable defaults for field length and data type. More
stringent definitions are suggested -- if you plan on keeping homedir
or shell information in the database, those fields could be defined as
NOT NULL, or even UNIQUE for homedir. 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(30) NOT NULL, uid INTEGER UNIQUE, gid INTEGER, homedir
VARCHAR(255), shell VARCHAR(255))
To create a group table:
CREATE TABLE groups (groupname VARCHAR(30) NOT NULL, gid INTEGER NOT
NULL, members VARCHAR(255))
=======================================
B. Using mod_sql in Third-Party Modules
=======================================
mod_sql now provides hooks to allow third-party modules (mod_quota,
mod_ratio, etc.) to retrieve data from a database, and change data in
a database, without any changes to mod_sql itself.
Two functions are now available from the mod_sql cmdtable:
-------------------------------
MODRET sql_lookup(cmd_rec *cmd)
This function takes a cmd_rec with the name of a SQLNamedQuery as
it's first argument. The named query MUST refer to a SELECT or
FREEFORM query.
The returned modret_t * will either be an error, or contain an
array_header * in the data slot.
-------------------------------
MODRET sql_change(cmd_rec *cmd)
This function takes a cmd_rec with the name of a SQLNamedQuery as
it's first argument, and arguments to the query following in argv[2],
argv[3], etc. All arguments should be character strings.
The returned modret_t * will either be an error, or HANDLED to
designate success. There is no return data.
-------------------------------
You'll need to do two things to use these:
1) Write a function to properly create and fill in a cmd_rec object.
Since you'll be calling the mod_sql functions directly, you need to
provide a correctly filled cmd_rec yourself. There is an example in
Appendix C, below.
2) Tell your users to write the appropriately named 'SQLNamedQuery'
directives -- it's likely that you'll have to specify the form of the
queries yourself.
3) Check return values. If an error occurs in the *processing* of
your queries, mod_sql will log errors to the debug output and the
session will shut down. But it's entirely possible that a query may
return no data, less data than you expect, or NULL values instead of
valid data. Try not to make assumptions here.
======================================
C. Example Code for Hooking to mod_sql
======================================
This code is for example purposes only. It is meant to show one way
of using these functions to update data in the database and pull data
from the database, but it's not the only way.
/*
* ProFTPD: mod_count -- Sample module for mod_sql hook functions
* Copyright (c) 2001 Andrew Houghton.
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
*
* As a special exemption, Public Flood Software/MacGyver aka Habeeb J. Dihu
* and other respective copyright holders give permission to link this program
* with OpenSSL, and distribute the resulting executable, without including
* the source code for OpenSSL in the source distribution.
*/
/* This module is *extremely* simplistic. It takes no directives, does no
* initialization, and only has one command handler. Its entire goal is to
* keep a count of user logins in the database, updating the count by a random
* number between one and five each time a user logs in and relaying the
* current count to the user. This module is not meant to be useful.
*
* It's recommended that you examine mod_sample.c and other modules if you
* haven't done so already.
*
* This sample code assumes the existence of two 'SQLNamedQuery' directives, as
* follows:
*
* SQLNamedQuery count_get SELECT "count FROM users WHERE userid='%u'"
* SQLNamedQuery count_set UPDATE "count=count+%{0} WHERE userid='%u'" users
*
* If you plan to hook to mod_sql, you should document the names of the queries
* you expect to exist, or provide a directive to change those names. You
* should probably also give example queries as documentation.
*/
#include "conf.h"
/* _make_cmd creates a cmd_rec structure for passing to the mod_sql functions.
* The code is essentially equivalent to the core ProFTPD function that does
* the same thing, but that function isn't available to modules.
*
* Note that the first argument in the cmd_rec should be the function name.
* This function is a good candidate for cut-and-paste.
*/
static cmd_rec *_make_cmd(pool * cp, int argc, ...)
{
pool *newpool = NULL;
cmd_rec *c = NULL;
va_list args;
int i = 0;
newpool = make_named_sub_pool( cp, "temp pool" );
c = pcalloc(newpool, sizeof(cmd_rec));
c->argc = argc;
c->symtable_index = -1;
c->pool = newpool;
c->argv = pcalloc(newpool, sizeof(void *) * (argc));
c->tmp_pool = newpool;
va_start(args, argc);
for (i = 0; i < argc; i++)
c->argv[i] = (void *) va_arg(args, char *);
va_end(args);
return c;
}
MODRET post_cmd_pass(cmd_rec *cmd)
{
char rndnum[2] = {'\0'};
cmd_rec *sqlcmd = NULL;
cmdtable *c = NULL;
modret_t *mr = NULL;
array_header *ah = NULL;
char *retval = NULL;
int cnt = 0;
int rnd = 0;
/* 1. Change the value in the Database */
/* find the command table that holds the sql_change command */
c = mod_find_cmd_symbol("sql_change", NULL, NULL);
/* if we couldn't find the handler, fail gracefully */
if (!c) return DECLINED(cmd);
/* get a random number, put it in a string */
rnd = (random() % 4) + 1;
snprintf(rndnum,2,"%1d",rnd);
/* create our cmd_rec for updating the database */
sqlcmd = _make_cmd(cmd->tmp_pool, 3, "sql_change", "count_set", rndnum);
/* call the handler */
mr = call_module_cmd(c->m, c->handler, sqlcmd);
/* if there was an error, fail gracefully */
if (MODRET_ISERROR(mr)) return DECLINED(cmd);
/* 2. Get the value from the Database */
/* find the command table that holds the sql_change command */
c = mod_find_cmd_symbol("sql_lookup", NULL, NULL);
/* if we couldn't find the handler, fail gracefully */
if (!c) return DECLINED(cmd);
/* create our cmd_rec for updating the database */
sqlcmd = _make_cmd(cmd->tmp_pool, 2, "sql_change", "count_get");
/* call the handler */
mr = call_module_cmd(c->m, c->handler, sqlcmd);
/* if there was an error, fail gracefully */
if (MODRET_ISERROR(mr)) return DECLINED(cmd);
/* get the array_header from the modret_t */
ah = (array_header *) mr->data;
/* make sure we have the expected number of elements */
if (ah->nelts != 1) return DECLINED(cmd);
/* tell the client the current count */
add_response(R_230, "Your count is now %s", ((char **) ah->elts)[0]);
return DECLINED(cmd);
}
cmdtable count_commands[] = {
{ POST_CMD, C_PASS, G_NONE, post_cmd_pass, FALSE, FALSE },
{ 0, NULL }
};
module count_module = {
NULL,NULL, /* Always NULL */
0x20, /* API Version 2.0 */
"count",
NULL, /* Sample configuration handler table */
count_commands, /* Sample command handler table */
NULL, /* No authentication handler table */
NULL, /* No initialization function */
NULL, /* No post-fork "child mode" init */
};
