One Hat Cyber Team

Edit File: mod_sql.html

<!DOCTYPE html>
<html>
<head>
<title>ProFTPD module mod_sql</title>
</head>

<hr>
<center>
<h2>ProFTPD module <code>mod_sql</code></h2>
</center>
<hr>

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

The <code>mod_sql</code> module is not compiled by default. Installation
instructions are discussed <a href="#Installation">here</a>.

This product includes software developed by the OpenSSL Project for use in the
OpenSSL Toolkit (http://www.openssl.org/).

This product includes cryptographic software written by Eric Young
(eay@cryptsoft.com).

The most current version of <code>mod_sql</code> is distributed with ProFTPD.

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

<h2>Directives</h2>
<ul>
 <li><a href="#SQLAuthenticate">SQLAuthenticate</a>
 <li><a href="#SQLAuthTypes">SQLAuthTypes</a>
 <li><a href="#SQLBackend">SQLBackend</a>
 <li><a href="#SQLConnectInfo">SQLConnectInfo</a>
 <li><a href="#SQLDefaultGID">SQLDefaultGID</a>
 <li><a href="#SQLDefaultHomedir">SQLDefaultHomedir</a>
 <li><a href="#SQLDefaultUID">SQLDefaultUID</a>
 <li><a href="#SQLEngine">SQLEngine</a>
 <li><a href="#SQLGroupInfo">SQLGroupInfo</a>
 <li><a href="#SQLGroupPrimaryKey">SQLGroupPrimaryKey</a>
 <li><a href="#SQLGroupWhereClause">SQLGroupWhereClause</a>
 <li><a href="#SQLKeepAlive">SQLKeepAlive</a>
 <li><a href="#SQLLog">SQLLog</a>
 <li><a href="#SQLLogFile">SQLLogFile</a>
 <li><a href="#SQLLogOnEvent">SQLLogOnEvent</a>
 <li><a href="#SQLMinID">SQLMinID</a>
 <li><a href="#SQLMinUserGID">SQLMinUserGID</a>
 <li><a href="#SQLMinUserUID">SQLMinUserUID</a>
 <li><a href="#SQLNamedConnectInfo">SQLNamedConnectInfo</a>
 <li><a href="#SQLNamedQuery">SQLNamedQuery</a>
 <li><a href="#SQLNegativeCache">SQLNegativeCache</a>
 <li><a href="#SQLOptions">SQLOptions</a>
 <li><a href="#SQLRatios">SQLRatios</a>
 <li><a href="#SQLRatioStats">SQLRatioStats</a>
 <li><a href="#SQLShowInfo">SQLShowInfo</a>
 <li><a href="#SQLUserInfo">SQLUserInfo</a>
 <li><a href="#SQLUserPrimaryKey">SQLUserPrimaryKey</a>
 <li><a href="#SQLUserWhereClause">SQLUserWhereClause</a>
</ul>

<hr>
<h3><a name="SQLAuthenticate">SQLAuthenticate</a></h3>
Syntax: SQLAuthenticate on|off or 
Syntax: SQLAuthenticate [users] [groups] [userset[fast]] [groupset[fast]] 
Default: on 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.5rc1 and later

The <code>SQLAuthenticate</code> directive configures <code>mod_sql</code>'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:
<ul>
 <li>on 
 Shorthand for <code>SQLAuthenticate users groups userset groupset</code>. 
 </li>

<li>off 
 Disables all <code>mod_sql</code> authentication functions. 
 </li>

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

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

<li>userset[fast] 
 If present, <code>mod_sql</code> will process the potentially expensive
 <code>{set|get|end}pwent()</code> calls. If not present,
 <code>mod_sql</code> will not process these calls. Adding the suffix
 &quot;fast&quot; tells <code>mod_sql</code> 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
 &quot;users&quot; is not specified. 
 </li>

<li>groupset[fast] 
 If present, <code>mod_sql</code> will process the potentially expensive
 <code>{set|get|end}grent()</code> calls. If not present,
 <code>mod_sql</code> will not process these calls. Adding the suffix
 &quot;fast&quot; tells <code>mod_sql</code> 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
 &quot;groups&quot; is not specified. 
 </li>
 
</ul>

The <code>SQLLog</code> and <code>SQLShowInfo</code> directives will always be
processed by <code>mod_sql</code>. The <code>SQLAuthenticate</code> 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 <code>mod_sql</code>.
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 &quot;fast&quot; suffix is not appropriate for every site. Normally,
<code>mod_sql</code> 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 &quot;fast&quot; lookups,
<code>mod_sql</code> will make a single <code>SELECT</code> 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 &quot;fast&quot; option for groupset,
you must use only one row per group. For example, normally
<code>mod_sql</code> treats the following three tables in exactly the same way:

<pre>
|--------------------------------------------------|
| 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 |
|--------------------------------------------------|
</pre>
If you use the &quot;fast&quot; option, <code>mod_sql</code> assumes that all
entries are structured like the last example.

<hr>
<h3><a name="SQLAuthTypes">SQLAuthTypes</a></h3>
Syntax: SQLAuthTypes [&quot;Backend&quot;|&quot;Crypt&quot;|&quot;Empty&quot;|&quot;OpenSSL&quot;|&quot;Plaintext&quot;] ... 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.0 and later

The <code>SQLAuthTypes</code> 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:
<ul>
 <li>Backend 
 Allows database-specific backend passwords. Not all backend databases
 support this option. For example, MySQL datatabases use this option
 to authenticate MySQL <code>'PASSWORD()'</code> 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. 
 </li>

<li>Crypt 
 Allows passwords in the database to be of Unix <code>crypt(3)</code>
 form. Note that the traditional Unix <code>crypt(3)</code>
 function only uses the first 8 characters of a password. If you have
 passwords longer than 8 characters, and some of those passwords have
 the same first 8 characters, then those users may be able to log into
 each others accounts. 
 </li>

<li>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; <code>NULL</code> values are not acceptable as empty
 passwords. Be very careful if using this authentication method. 
 </li>

<li>OpenSSL 
 Allows passwords in the database to be of the form
 <code>'{digest-name}hashed-value'</code>, where <code>hashed-value</code>
 is the base64-encoded digest of the passsword. Only available if you
 define <code>HAVE_OPENSSL</code> when you compile <code>proftpd</code>
 and you link with OpenSSL's <code>libcrypto</code> library; the easiest
 way to handle this is to use the <code>--enable-openssl</code> configure
 option. 
 </li>

<li>Plaintext 
 Allows passwords in the database to be in plaintext. 
 </li>
 
</ul>

For example:
<pre>
 SQLAuthTypes Crypt Empty
</pre>
configures <code>mod_sql</code> to first attempt to verify the password
using the Unix <code>crypt(3)</code> 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,
<code>mod_sql</code> will fail to authenticate the user.

Note that the <a href="mod_sql_passwd.html"><code>mod_sql_passwd</code></a>
module also provides other <code>SQLAuthTypes</code> values.

<hr>
<h3><a name="SQLBackend">SQLBackend</a></h3>
Syntax: SQLBackend backend 
Default: Depends 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.3.0rc1 and later

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

If there is only one backend module compiled in, the <code>SQLBackend</code>
directive is not needed. If there are multiple backend modules compiled
and no <code>SQLBackend</code> directive is specified, then
<code>mod_sql</code> will default to using the first backend module listed.
For instance, if you configured <code>proftpd</code> using a
<code>configure</code> command such as:
<pre>
 ./configure --with-modules=mod_sql:mod_sql_postgres:mod_sql_mysql ...
</pre>
then <code>mod_sql</code> would default to using <code>mod_sql_postgres</code>
as the backend module to use.

You might have multiple <code>&lt;VirtualHost&gt;</code> sections which use
different SQL backends, e.g.:
<pre>
 &lt;VirtualHost 1.2.3.4&gt;
 SQLBackend mysql
 ...
 &lt;/VirtualHost&gt;

&lt;VirtualHost 5.6.7.8&gt;
 SQLBackend postgres
 ...
 &lt;/VirtualHost&gt;
</pre>
Use &quot;mysql&quot; for the <code>mod_sql_mysql</code> module, and
&quot;postgres&quot; for the <code>mod_sql_postgres</code> module.

<hr>
<h3><a name="SQLConnectInfo">SQLConnectInfo</a></h3>
Syntax: SQLConnectInfo connection-info [username] [password] [policy] [ssl-ca:&lt;path&gt;] [ssl-cert:&lt;path&gt;] [ssl-key:&lt;path&gt;] [ssl-ciphers:&lt;list&gt;] 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.0 and later

The <code>SQLConnectInfo</code> 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
<code>NULL</code>, which the backend will treat in some backend-specific
manner. If you specify a password, you must specify a username. If
no <code>SQLConnectInfo</code> directive is specified, <code>mod_sql</code>
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
<code>mod_sql</code> 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:
<ul>
 <li>PERCONNECTION 
 Open a database connection when the client connects to the server,
 and close the database connection when the client disconnects. 
 </li>

<li>PERSESSION 
 Open a database connection on first use (e.g. during authentication)
 and close the database connection at the end of the session. 
 </li>

<li>number ("timed") 
 Timed database connections that close themselves after number
 seconds of inactivity. 
 </li>
 
</ul>
If a connection policy is not specified, if the policy is not a number or is
a number less than 1, or if the policy is the string &quot;PERSESSION&quot;,
the <code>PERSESSION</code> 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:
<pre>
 database[@hostname][:port]
</pre>
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:
<blockquote>
 the value of host may be either a hostname or an IP address. If host is NULL
 or the string &quot;localhost&quot;, 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.
</blockquote>
(Note: In <code>proftpd-1.3.1rc1</code>, if hostname starts
with a slash, it is interpreted as the Unix domain socket path by
<code>mod_sql_mysql</code>, similar to how <code>mod_sql_postgres</code>
handles the parameter.)

From the PostgreSQL documentation:
<blockquote>
 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 <code>/tmp</code>.
</blockquote>

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

In <code>proftpd-1.3.6rc2</code> and later, it is possible to configure SSL/TLS
parameters for a given connection, which tells <code>mod_sql</code> to try
to open an SSL session with the database server. Most of the time, all that
is needed for the SSL session is the CA (Certificate Authority) to use, for
verifying the certificate presented by the database server. Thus:
<pre>
 SQLConnectInfo ... ssl-ca:/path/to/cacert.pem
</pre>
If your database server is configured to require SSL/TLS mutual authentication
(also called "client auth"), you may need the <code>ssl-cert:</code> and
<code>ssl-key:</code> parameters as well:
<pre>
 SQLConnectInfo ... ssl-ca:/path/to/cacert.pem \
 ssl-cert:/path/to/client-cert.pem \
 ssl-key:/path/to/client-key.pem
</pre>
Finally, some database clients (such as MySQL) allow you to configure the
specific SSL/TLS ciphersuites that should be used; the <code>ssl-ciphers:</code>
parameter can be used for this:
<pre>
 SQLConnectInfo ... ssl-ca:/path/to/cacert.pem \
 ssl-cert:/path/to/client-cert.pem \
 ssl-key:/path/to/client-key.pem \
 ssl-ciphers:DEFAULT:!EXPORT:!DES
</pre>

Examples:
<pre>
 # 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

# Connect to the database 'ftpusers' via port 3000 at host 'foo.com'.
 # Use a username of 'admin' and a password of 'mypassword' when
 # connection. A 30 second inactivity/idle timer is used. In addition,
 # use SSL for the connection.
 SQLConnectInfo ftpusers@foo.com:3000 admin mypassword 30 ssl-ca:/path/to/cacert.pem
</pre>
Backends may require different information in the connection-info
field; check your backend module for more detailed information.

<hr>
<h3><a name="SQLDefaultGID">SQLDefaultGID</a></h3>
Syntax: SQLDefaultGID default-gid 
Default: 65533 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.0 and later

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

See also: <a href="#SQLMinUserGID"><code>SQLMinUserGID</code></a>

<hr>
<h3><a name="SQLDefaultHomedir">SQLDefaultHomedir</a></h3>
Syntax: SQLDefaultHomedir path 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.5rc1 and later

The <code>SQLDefaultHomedir</code> directive configures a default home
directory for all users authenticated with this module. 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 <code>SQLUserInfo</code>, 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 <code>NULL</code>.

See also: <a href="#SQLUserInfo">SQLUserInfo</a>

<hr>
<h3><a name="SQLDefaultUID">SQLDefaultUID</a></h3>
Syntax: SQLDefaultUID default-uid 
Default: 65533 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.0 and later

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

See also: <a href="#SQLMinUserUID"><code>SQLMinUserUID</code></a>

<hr>
<h3><a name="SQLEngine">SQLEngine</a></h3>
Syntax: SQLEngine on|off|auth|log 
Default: SQLEngine on 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code>, <code>&lt;Anonymous&gt;</code> 
Module: mod_sql 
Compatibility: 1.3.0rc1 and later

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

In addition to on and off, <code>SQLEngine</code> accepts
two other values: auth and log. If you wish to use
<code>mod_sql</code> for authentication and not for logging (via
<a href="#SQLLog"><code>SQLLog</code></a>), use auth. Conversely,
to do only <code>SQLLog</code>-type logging, and no authentication, use
log.

This directive can be used to have <code>&lt;Anonymous&gt;</code> sections
that do not use <code>mod_sql</code>, e.g.:
<pre>
 &lt;Anonymous ~ftp&gt;
 ...
 SQLEngine off
 ...
 &lt;/Anonymous&gt;
</pre>

<hr>
<h3><a name="SQLGroupInfo">SQLGroupInfo</a></h3>
Syntax: SQLGroupInfo group-table group-name gid members 
Default: &quot;groups groupname gid members&quot; 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.5rc1 and later

The <code>SQLGroupInfo</code> directive configures the group table and fields
that hold group information. The parameters for this directive are
described below:
<ul>
 <li>grouptable 
 Specifies the name of the table that holds group information. 
 </li>

<li>groupname 
 Specifies the field in the group table that holds the group name. 
 </li>

<li>gid 
 Specifies the field in the group table that holds the group's GID. 
 </li>

<li>members 
 Specifies the field in the group table that holds the group members. 
 </li>
 
</ul>
If you need to change any of these field names from the default, you
need to specify all of them.

Custom Queries 
As of <code>1.3.3rc1</code>, the <code>SQLGroupInfo</code> directive accepts
an alternate syntax:
<pre>
 SQLGroupInfo custom:/get-group-by-name/get-group-by-id/get-group-by-member
</pre>
where get-group-by-name refers to a configured SELECT
<code>SQLNamedQuery</code>. This named query must return one row, and
return the following columns, in this order: <code>groupname, gid,
members</code>. The configured query may make use of the variables mentioned
in the <code>SQLLog</code> description. The <code>%{0}</code> variable will
be populated with the group name for which info is being retrieved. The
get-group-by-id here refers to a <code>SQLNamedQuery</code> which is
just like the lookup given a group name, only the get-group-by-id
query needs to return group information given a GID. For the
get-group-by-id query, the <code>%{0}</code> variable is populated with
the GID in question. Last, the get-group-by-member query returns group
information given a user member; again, the <code>%{0}</code> will contain
the member/user name by which to look up the group information.

If your custom get-group-by-name query references a table
other than the default groups table, then you must also supply a
custom get-group-by-id query as well. Otherwise, <code>mod_sql</code>
will fail with a "Table not found" error, and disconnect the client, whenever
the client asks for a directory listing.

Note, however, that if you use the groupset or
groupsetfast <a href="#SQLAuthenticate"><code>SQLAuthenticate</code></a>
options, you will need to supply some additional <code>SQLNamedQuery</code>
names in your custom <code>SQLGroupInfo</code> directive. The
<code>SQLGroupInfo</code> directive supports:
<pre>
 SQLGroupInfo custom:/lookup-by-name/lookup-by-id/lookup-by-member/[/groupset-lookup[/groupsetfast-lookup]]
</pre>
where groupset-lookup is the name of a SELECT
<code>SQLNamedQuery</code> that returns the names (and only the names)
of all groups, and the groupsetfast-lookup refers to a SELECT
<code>SQLNamedQuery</code> that returns all the fields (i.e.
<code>groupname, gid, members</code> in that order) for all groups.

To provide a concrete example:
<pre>
 SQLAuthenticate users groups groupsetfast
 SQLGroupInfo custom:/get-group-by-name/get-group-by-id/get-group-by-member/get-all-groupnames/get-all-groups
 SQLNamedQuery get-group-by-name SELECT "groupname, gid, members FROM ftpgroups WHERE groupname = '%{0}'"
 SQLNamedQuery get-group-by-id SELECT "groupname, gid, members FROM ftpgroups WHERE gid = %{0}"
 SQLNamedQuery get-group-by-member SELECT "groupname, gid, members FROM ftpgroups WHERE (members LIKE '%%,%{0},%%' OR members LIKE '%{0},%%' OR members LIKE '%%,%{0}')"
 SQLNamedQuery get-all-groupnames SELECT "groupname FROM ftpgroups"
 SQLNamedQuery get-all-groups SELECT "groupname, gid, members FROM ftpgroups"
</pre>
With a configuration similar to these, using either of the groupset or
groupsetfast in your <code>SQLAuthenticate</code> directive will
work as expected.

Another consideration when using custom <code>SQLGroupInfo</code> queries
is their relationship to the "useNormalizedGroupSchema" <code>SQLOption</code>.
I.e. does using the "useNormalizedGroupSchema" option affect how
the results of custom <code>SQLGroupInfo</code> queries are handled? No.
If custom <code>SQLGroupInfo</code> queries are used, <code>mod_sql</code>
automatically assumes that the custom group members query will return
N rows, where each row contains 3 columns: group name, group ID,
and member name. That is, the custom <code>SQLGroupInfo</code> queries act as
if "useNormalizedGroupSchema" is always in effect. (When support for custom
<code>SQLGroupInfo</code> queries was added, there was no good reason for
supporting the old, inefficient comma-delimited format for the members column.)

See Also:
 <a href="#SQLAuthenticate"><code>SQLAuthenticate</code></a>,
 <a href="#SQLLog"><code>SQLLog</code></a>,
 <a href="#SQLNamedQuery"><code>SQLNamedQuery</code></a>,
 <a href="#SQLUserInfo"><code>SQLUserInfo</code></a>

<hr>
<h3><a name="SQLGroupPrimaryKey">SQLGroupPrimaryKey</a></h3>
Syntax: SQLGroupPrimaryKey column | &quot;custom:/&quot;named-query 
Default: gid 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.3.5rc3 and later

The <code>SQLGroupPrimaryKey</code> directive configures the column name
that is used as the "primary key" for group data; this primary key can
then be used in other SQL tables via foreign key constraints. The
<a href="../howto/SQL.html#SQLPrimaryKeys">SQL howto</a> covers using this
directive in more details.

See also: <a href="#SQLUserPrimaryKey"><code>SQLUserPrimaryKey</code></a>

<hr>
<h3><a name="SQLGroupWhereClause">SQLGroupWhereClause</a></h3>
Syntax: SQLGroupWhereClause where-clause 
Default: off 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.5rc1 and later

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 &quot;and&quot;.

As an example of a possible use for this directive, imagine if your group table
included a &quot;LoginAllowed&quot; field:
<pre>
 SQLGroupWhereClause "LoginAllowed = 'true'"
</pre>
would be appended to every group-related query as the string:
<pre>
 " WHERE (LoginAllowed = 'true')"
</pre>

Note that if custom group <code>SQLNamedQuery</code> are configured,
those custom queries will be used as is; any configured
<code>SQLGroupWhereClause</code> will not be appended. Custom queries
can be of any format/syntax, and thus simply appending a
<code>SQLGroupWhereClause</code> to a custom query may be syntactically invalid.

As of ProFTPD 1.3.1rc2, the configured <code>SQLGroupWhereClause</code>
parameter can use the same set of variables as supported by the
<a href="#SQLNamedQuery"><code>SQLNamedQuery</code></a> directive.

<hr>
<h3><a name="SQLKeepAlive">SQLKeepAlive</a></h3>
Syntax: SQLKeepAlive interval [stmt] 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.3.7rc4 and later

The <code>SQLKeepAlive</code> directive configures a periodic stmt to
be executed on a timer, every interval seconds, as way of keeping
the connection between <code>mod_sql</code> and the backend database server
alive.

Note that the default stmt used is:
<pre>
 SELECT 1
</pre>
This SQL statement works for MySQL, Postgres, and SQLite.

Examples:
<pre>
 # Schedule a "keepalive" query every 30 seconds
 SQLKeepAlive 30

# Schedule a "keepalive" query every 30 seconds, using our custom SQL
 SQLKeepAlive 30 "SELECT foo FROM bar"
</pre>
The stmt used does not have to suceed; the important behavior
is the sending (and receiving) of bytes across the network to the database
server, so that any networking gear in between, such as firewalls and load
balancers, know that the connection is not idle, is still in use.

<hr>
<h3><a name="SQLLog">SQLLog</a></h3>
Syntax: SQLLog cmd-set query-name [&quot;IGNORE_ERRORS&quot;] 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.1 and later

This directive is used to log information to a database table. Multiple
<code>SQLLog</code> directives can be in effect for any command; for example,
a user changing directories can trigger multiple logging statements.
Note that this logging occurs at the end of command processing.

The first parameter to <code>SQLLog</code>, 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 <code>CWD, DELE, HELP, LIST, MKD, MODE, NLST, PASS, PASV,
PORT</code> and many more. For the complete list check the FTP RFCs.
Normally <code>mod_sql</code> will log events after they have completed
successfully; in the case of the <code>QUIT</code> command,
<code>mod_sql</code> logs prior to the server's processing of the command.
(Note, however, that the client may not issue a <code>QUIT</code>
before logging out; in this case, use a command of <code>EXIT</code> rather
than <code>QUIT</code>. <code>EXIT</code> is not a real FTP command, but
it is used here to provide a means for having <code>SQLLog</code> work
whenever a session ends.)

FTP commands in the command set will only be logged if they complete
successfully. Prefixing any command with &quot;ERR_&quot; 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 &quot;X&quot; and &quot;ERR_X&quot; in your
cmd-set.

The special command &quot;*&quot; matches all FTP commands, while
&quot;ERR_*&quot; matches all errors.

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

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

Normally, <code>SQLLog</code> directives are considered important enough that
errors in their processing will cause <code>mod_sql</code> 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 <code>UPDATE</code> or <code>INSERT</code> will not occur).
Check your directives carefully.

Examples:
<pre>
 SQLLog PASS updatecount
 SQLNamedQuery updatecount UPDATE "count=count+1 WHERE userid='%u'" users
</pre>
If the current user was &quot;joe&quot;, this would translate into the query
&quot;UPDATE users SET count=count+1 WHERE userid='joe'&quot;. This query
would run whenever a user was first authenticated.

<pre>
 SQLLog CWD updatedir
 SQLNamedQuery updatedir UPDATE "cwd='%d' where userid='%u'" users
</pre>
If the current user was &quot;joe&quot; and the current working directory were
<code>/tmp</code>, this would translate into the query &quot;UPDATE users SET cwd='/tmp' WHERE userid='joe'&quot;. This query would run whenever a user
changed directories.

<pre>
 SQLLog RETR,STOR insertfileinfo
 SQLNamedQuery insertfileinfo INSERT "'%f', %b, '%u@%v', now()" filehistory
</pre>
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:
&quot;INSERT INTO filehistory VALUES ('somefile', 12345, 'joe@joe.org', '21-05-2001 20:01:00')&quot;

<hr>
<h3><a name="SQLLogFile">SQLLogFile</a></h3>
Syntax: SQLLogFile file 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.8rc2 and later

The <code>SQLLogFile</code> directive is used to specify a log file for
<code>mod_sql</code> 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 <code>AllowLogSymlinks</code> is explicitly set to
on (generally a bad idea), the path must not be a symbolic
link.

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

<hr>
<h3><a name="SQLLogOnEvent">SQLLogOnEvent</a></h3>
Syntax: SQLLogOnEvent event query-name ["IGNORE_ERRORS"] 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code>
Module: mod_sql 
Compatibility: 1.3.5rc2 and later

The <code>SQLLogOnEvent</code> directive is used to log information to database
tables via query-name. Multiple <code>SQLLogOnEvent</code> directives
can be in effect for any event.

The first parameter is the name of an event.

The third parameter is optional. If you add &quot;IGNORE_ERRORS&quot; as the
third parameter, <code>SQLLogOnEvent</code> will not check for
errors in the processing of the named query. Any value for this parameter
other than the string &quot;IGNORE_ERRORS&quot; (case-insensitive) will not
cause errors to be ignored.

Examples:
<pre>
 SQLNamedQuery update_max_logins UPDATE "max_login_attempts=max_login_attempts+1 WHERE userid='%u'" users
 SQLLogOnEvent mod_auth.max-login-attempts update_max_logins
</pre>
In this example, a <code>users.max_login_attempts</code> column is updated
for the user trying to login, whenever that user exceeds a
<code>MaxLoginAttempts</code> limit.

<pre>
 SQLNamedQuery add_banned_host INSERT "host = '%a'" bans
 SQLLogOnEvent mod_ban.ban-host add_banned_host
</pre>
Here, whenever <code>mod_ban</code> bans a host, we add a row to a
<code>bans</code> table.

<hr>
<h3><a name="SQLMinID">SQLMinID</a></h3>
Syntax: SQLMinID minimum-id 
Default: 999 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.0 and later

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

See also: <a href="#SQLMinUserGID"><code>SQLMinUserGID</code></a>,
 <a href="#SQLMinUserUID"><code>SQLMinUserUID</code></a>

<hr>
<h3><a name="SQLMinUserGID">SQLMinUserGID</a></h3>
Syntax: SQLMinUserGID minimum-gid 
Default: 999 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.0 and later

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

See also: <a href="#SQLDefaultGID"><code>SQLDefaultGID</code></a>

<hr>
<h3><a name="SQLMinUserUID">SQLMinUserUID</a></h3>
Syntax: SQLMinUserUID minimum-uid 
Default: 999 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.0 and later

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

See also: <a href="#SQLDefaultUID"><code>SQLDefaultUID</code></a>

<hr>
<h3><a name="SQLNamedConnectInfo">SQLNamedConnectInfo</a></h3>
Syntax: SQLNamedConnectInfo connection-name sql-backend connection-info [username] [password] [policy] [ssl-ca:&lt;path&gt;] [ssl-cert:&lt;path&gt;] [ssl-key:&lt;path&gt;] [ssl-ciphers:&lt;list&gt;] 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.3.4rc2 and later

The <code>SQLNamedConnectInfo</code> directive configures the information
necessary to connect to the backend database. This connection will be given
the name connection-name; this named connection can then be referenced
by that name in e.g. a <code>SQLNamedQuery</code>. The
sql-backend name configures the backend SQL module to use for this
connection; see <a href="#SQLBackend"><code>SQLBackend</code></a>. 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 <code>NULL</code>, which the
backend will treat in some backend-specific manner. If you specify a password,
you must specify a username. Multiple <code>SQLNamedConnectInfo</code>
directives may be configured.

Note that <code>SQLNamedConnectInfo</code> directives will only be
honored if a <a href="#SQLConnectInfo"><code>SQLConnectInfo</code></a>
directive is configured.

See also: <a href="#SQLBackend"><code>SQLBackend</code></a>,
<a href="#SQLConnectInfo"><code>SQLConnectInfo</code></a>

<hr>
<h3><a name="SQLNamedQuery">SQLNamedQuery</a></h3>
Syntax: SQLNamedQuery name type query-string [table] [connection-name] 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.4 and later

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

It is strongly recommended that you read documentation on the
<code>LogFormat</code> and <code>ExtendedLog</code> directives, as the
meta-sequences available to <code>SQLNamedQuery</code> 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
&quot;SELECT&quot;, &quot;UPDATE&quot;, &quot;INSERT&quot;, or
&quot;FREEFORM&quot;. 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 <code>LogFormat</code> directive except
the following are not accepted:
<ul>
 <li><code>%{FOOBAR}e</code> 
 For <code>LogFormat</code>, this logs the content of environment variable
 &quot;FOOBAR&quot;. In a <code>SQLNamedQuery</code> SQL statement,
 however, environment variables can be referenced using <code>%{env:FOOBAR}</code>.
 </li>

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

However, a <code>%{time:format}</code> meta-sequence is
 supported. This sequence allows for formatting time strings according
 to the rules of the <code>strftime(3)</code> function.
 </li>
 
</ul>
and the following is in addition to the <code>LogFormat</code>
meta-sequences:
<ul>
 <li><code>%d</code> 
 The current working directory or &quot;-&quot; if none. 
 </li>

<li><code>%{n}</code> 
 This meta-sequence is used internally by <code>mod_sql</code> and other
 third-party modules and patches to pass information to the database.
 Using this meta-sequence in anything other than an <code>INSERT</code> or
 <code>UPDATE</code> query is an error, and using this meta-sequence unless
 directed to by a third-party module or patch is also an error. 
 </li>
 
</ul>
The correct form of a query will be built from the directive arguments, except
in the case of <code>FREEFORM</code> 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
<code>UPDATE</code> or <code>INSERT</code> type queries, but is
required for those types.

The last parameter, connection-name, is only necessary for telling
<code>mod_sql</code> to use the specified connection name (from
<a href="#SQLNamedConnectInfo"><code>SQLNamedConnectInfo</code></a>), rather
than the default connection, for executing the query/statement.

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

Examples:
<pre>
 SQLNamedQuery count SELECT "count from users where userid='%u'"
</pre>
creates a query named &quot;count&quot; which could be used by
<code>SQLShowInfo</code> to inform a user of their login count. The actual
query would look something like &quot;SELECT count FROM users WHERE
userid='matilda'&quot; for user &quot;matilda&quot;.

<pre>
 SQLNamedQuery updatecount UPDATE "count=count+1 WHERE userid='%u'" users
</pre>
creates a query named &quot;updatecount&quot; which could be used by
<code>SQLLog</code> to update a user login counter in the table
<code>users</code>. The actual query would look something like &quot;UPDATE
users SET count=count+1 WHERE userid='persephone'&quot; for user
&quot;persephone&quot;.

<pre>
 SQLNamedQuery accesslog INSERT "now(), '%u'" accesslog
</pre>
creates a query named &quot;accesslog&quot; which could be used by
<code>SQLLog</code> to track access times by clients. The actual query would
look something like &quot;INSERT INTO accesslog VALUES (now(), 'pandora')&quot;
for user &quot;pandora&quot;. Note that this may be too simplistic for your
table structure, since most databases require data for all columns to be
provided in an <code>INSERT</code> statement of this form. See the following
<code>FREEFORM</code> query for an example of something which may suit your
needs better.

<pre>
 SQLNamedQuery accesslog FREEFORM "INSERT INTO accesslog(date, user) VALUES (now(), '%u')"
</pre>
creates a query named &quot;accesslog&quot; which could be used by
<code>SQLLog</code> to track access times by clients. The actual query would
look something like
&quot;INSERT INTO accesslog(date, user) VALUES (now(), 'tilda')&quot; for
user &quot;tilda&quot;.

<hr>
<h3><a name="SQLNegativeCache">SQLNegativeCache</a></h3>
Syntax: SQLNegativeCache on|off 
Default: off 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.7rc1 and later

<code>SQLNegativeCache</code> 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
<code>SQLNegativeCache</code> set to on, negative
(&quot;not found&quot;) responses from SQL queries will be cached and speed
will improve on directory listings that contain many users not present in the
SQL database.

<hr>
<h3><a name="SQLOptions">SQLOptions</a></h3>
Syntax: SQLOptions opt1 ... 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.3.1rc1 and later

The <code>SQLOptions</code> directive is used to tweak various optional
behavior of <code>mod_sql</code>.

Example:
<pre>
 SQLOptions NoDisconnectOnError
</pre>

The currently implemented options are:
<ul>
 <li><code>IgnoreConfigfile</code> 
 
 If the database client library in use has its own configuration file
 (e.g. <code>my.cnf</code> for the MySQL client library), then
 the <code>mod_sql</code> sub-module will read that file. There are some
 cases, however, where access to that config file is restricted. For
 such cases, use "IgnoreConfigFile" to tell <code>mod_sql</code> to not
 attempt to read that client library configuration file.

Note that this option first appeared in
 <code>proftpd-1.3.5rc4</code>.
 </li>

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

<li><code>NoReconnect</code> 
 
 If supported by the database client library, <code>mod_sql</code> will
 try to automatically reconnect once, if it determines that the
 connection to the database server was lost. Use "NoReconnect" to disable
 this auto-reconnection attempt.

<li><code>UseNormalizedGroupSchema</code> 
 
 If this option is enabled, then <code>mod_sql</code>, when retrieving all
 of the groups for a user, will use a SQL statement like:
<pre>
 SELECT groupname, groupid, member FROM grouptable WHERE member = $userName
</pre>
 rather than the statement it uses by default, which is:
<pre>
 SELECT groupname, groupid, member FROM grouptable WHERE member = $userName OR
 member LIKE '%,$userName' OR member LIKE '$username,%' OR
 member LIKE '%,$userName,%'
</pre>
 To put it another way, when this <code>SQLOption</code> is used,
 <code>mod_sql</code> 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.
</ul>

<hr>
<h3><a name="SQLRatios">SQLRatios</a></h3>
Syntax: 
Default: None 
Context: 
Module: mod_sql 
Compatibility:

<hr>
<h3><a name="SQLRatioStats">SQLRatioStats</a></h3>
Syntax: 
Default: None 
Context: 
Module: mod_sql 
Compatibility:

<hr>
<h3><a name="SQLShowInfo">SQLShowInfo</a></h3>
Syntax: SQLShowInfo cmd-set numeric query-string 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.5rc1 and later

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:
<code>CWD, DELE, HELP, LIST, MKD, MODE, NLST, PASS, PASV, PORT</code> 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 &quot;ERR_&quot; will show
information only if there was an error in command processing. To send a
message on both errors and successful completion of a given command X,
therefore, you'll need both &quot;X&quot; and &quot;ERR_X&quot;
in your cmd-set.

The special command &quot;*&quot; matches all FTP commands, while
&quot;ERR_*&quot; 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 <code>214</code>, the &quot;Help message&quot; numeric. It
specifies that the information is only meant to be human readable. Note
that FTP clients can be very picky about these response codes; choosing
the wrong code can cause clients not to work. Section 5.4 of <a href="http://www.faqs.org/rfcs/rfc959.html">RFC959</a> defines the acceptable response codes
for each FTP command.

The third parameter, query-string, is exactly equivalent to the
query-string parameter to the <code>SQLLog</code> directive, with one
addition:
<ul>
 <li><code>%{name}</code> 
 The first return value from the <code>SQLNamedQuery</code>
 identified by &quot;name&quot;. There is currently no way to retrieve more
 than one value from the database at a time. 
 </li>
 
</ul>
Any references to non-existent named queries, non-<code>SELECT</code> or
-<code>FREEFORM</code> type queries, or references to queries which return a
NULL first value, will be replaced with the string &quot;{null}&quot;.
For example:
<pre>
 SQLNamedQuery count SELECT "count from users where userid='%u'"
 SQLShowInfo PASS 230 "You've logged on %{count} times, %u"
</pre>
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 &quot;230&quot; numeric, which means
&quot;User logged in, proceed&quot;. &quot;230&quot; is appropriate in this
case because the message will be sent immediately after their password has
been accepted and the session has started.

<hr>
<h3><a name="SQLUserInfo">SQLUserInfo</a></h3>
Syntax: SQLUserInfo user-table user-name passwd uid gid home-dir shell 
Default: &quot;users userid passwd uid gid homedir shell&quot; 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.5rc1 and later

The <code>SQLUserInfo</code> 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:
<ul>
 <li>usertable 
 Specifies the name of the table that holds user information. 
 </li>

<li>username 
 Specifies the field in the user table that holds the username. 
 </li>

<li>passwd 
 Specifies the field in the user table that holds the user's password. 
 </li>

<li>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
 <code>SQLMinUserUID</code>. If the field name is specified as
 &quot;NULL&quot; the database will not be queried for this value and the
 user's UID will be set to the value of <code>SQLDefaultUID</code>. 
 </li>

<li>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
 <code>SQLMinUserGID</code>. If the field name is specified as
 &quot;NULL&quot; the database will not be queried for this value and the
 user's GID will be set to the value of <code>SQLDefaultGID</code>. 
 </li>

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

<li>shell 
 Specifies the field in the user table that holds the user's shell. If the
 fieldname is specified as &quot;NULL&quot; the database will not be
 queried and the shell will be reported as an empty string
 (&quot;&quot;). 
 </li>
 
</ul>

Custom Queries 
As of <code>1.2.9rc1</code>, the <code>SQLUserInfo</code> directive accepts
an alternate syntax:
<pre>
 SQLUserInfo custom:/name
</pre>
where name refers to a configured SELECT <code>SQLNamedQuery</code>.
This named query must return one row, and return the following columns,
in this order: <code>username, passwd, uid, gid, homedir, shell</code>. The
configured query may make use of the variables mentioned in the
<code>SQLLog</code> description. This syntax allows the administrator
a flexible way of constructing queries as needed. Note that if you want
use the given <code>USER</code> name, you should use the <code>%U</code>
variable, not <code>%u</code>; the latter requires the locally authenticated
user name, which is exactly what <code>SQLUserInfo</code> is meant to 
provide.

Starting with <code>1.3.3rc1</code>, you can supply other queries as well.
You can supply a SELECT <code>SQLNamedQuery</code> for returning the
same information as above, only this query is given a UID instead of
a user name. For example:
<pre>
 SQLNamedQuery get-user-by-name SELECT "userid, passwd, uid, gid, homedir, shell FROM ftpusers WHERE userid = '%U'"
 SQLNamedQuery get-user-by-id SELECT "userid, passwd, uid, gid, homedir, shell FROM ftpusers WHERE uid = %{0}"

SQLUserInfo custom:/get-user-by-name/get-user-by-id
</pre>
If your custom get-user-by-name query references a table
other than the default users table, then you must also supply a
custom get-user-by-id query as well. Otherwise, <code>mod_sql</code>
will fail with a "Table not found" error, and disconnect the client, whenever
the client asks for a directory listing.

Note, however, that if you use the userset or
usersetfast <a href="#SQLAuthenticate"><code>SQLAuthenticate</code></a>
options, you will need to supply some additional <code>SQLNamedQuery</code>
names in your custom <code>SQLUserInfo</code> directive. The
<code>SQLUserInfo</code> directive supports:
<pre>
 SQLUserInfo custom:/lookup-by-name[/lookup-by-id[/userset-lookup[/usersetfast-lookup]]]
</pre>
where userset-lookup is the name of a SELECT <code>SQLNamedQuery</code>
that returns the names (and only the names) of all users, and the
usersetfast-lookup refers to a SELECT <code>SQLNamedQuery</code>
that returns all the fields (i.e. <code>username, passwd, uid, gid,
homedir, shell</code> in that order) for all users.

To provide a concrete example:
<pre>
 SQLAuthenticate users groups usersetfast
 SQLUserInfo custom:/get-user-by-name/get-user-by-id/get-user-names/get-all-users
 SQLNamedQuery get-user-by-name SELECT "userid, passwd, uid, gid, homedir, shell FROM users WHERE userid = '%U'"
 SQLNamedQuery get-user-by-id SELECT "userid, passwd, uid, gid, homedir, shell FROM users WHERE uid = %{0}"
 SQLNamedQuery get-user-names SELECT "userid FROM users"
 SQLNamedQuery get-all-users SELECT "userid, passwd, uid, gid, homedir, shell FROM users"
</pre>
With a configuration similar to these, using either of the userset or
usersetfast in your <code>SQLAuthenticate</code> directive will
work as expected.

See Also:
 <a href="#SQLAuthenticate"><code>SQLAuthenticate</code></a>,
 <a href="#SQLGroupInfo"><code>SQLGroupInfo</code></a>, 
 <a href="#SQLLog"><code>SQLLog</code></a>, 
 <a href="#SQLNamedQuery"><code>SQLNamedQuery</code></a>

<hr>
<h3><a name="SQLUserPrimaryKey">SQLUserPrimaryKey</a></h3>
Syntax: SQLUserPrimaryKey column | &quot;custom:/&quot;named-query 
Default: uid 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.3.5rc3 and later

The <code>SQLUserPrimaryKey</code> directive configures the column name
that is used as the "primary key" for user data; this primary key can
then be used in other SQL tables via foreign key constraints. The
<a href="../howto/SQL.html#SQLPrimaryKeys">SQL howto</a> covers using this
directive in more details.

See also: <a href="#SQLGroupPrimaryKey"><code>SQLGroupPrimaryKey</code></a>

<hr>
<h3><a name="SQLUserWhereClause">SQLUserWhereClause</a></h3>
Syntax: SQLUserWhereClause where-clause 
Default: None 
Context: server config, <code>&lt;VirtualHost&gt;</code>, <code>&lt;Global&gt;</code> 
Module: mod_sql 
Compatibility: 1.2.5rc1 and later

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 &quot;and&quot;.

As an example of a possible use for this directive, imagine if your user table
included a &quot;LoginAllowed&quot; field:
<pre>
 SQLUserWhereClause "LoginAllowed = 'true'"
</pre>
would be appended to every user-related query as the string:
<pre>
 " WHERE (LoginAllowed = 'true')"
</pre>

Note that if custom user <code>SQLNamedQuery</code> are configured,
those custom queries will be used as is; any configured
<code>SQLUserWhereClause</code> will not be appended. Custom queries
can be of any format/syntax, and thus simply appending a
<code>SQLUserWhereClause</code> to a custom query may be syntactically invalid.

As of ProFTPD 1.3.1rc2, the configured <code>SQLUserWhereClause</code>
parameter can use the same set of variables as supported by the
<a href="#SQLNamedQuery"><code>SQLNamedQuery</code></a> directive.

<hr>
<h2><a name="Installation">Installation</a></h2>
The <code>mod_sql</code> module is distributed with ProFTPD. Simply
follow the normal steps for using third-party modules in ProFTPD:
<pre>
 $ ./configure --with-modules=sql-module-opts
</pre>
where the specific sql-module-opts depend on your database needs. For
example, if using MySQL, sql-module-opts would be
&quot;mod_sql:mod_sql_mysql&quot;. <code>mod_sql</code> is the main
SQL-processing engine, and <code>mod_sql_mysql</code> is the backend
sub-module that handles MySQL-specific details. If Postgres is your
database of choice, sql-module-opts would be
&quot;mod_sql:mod_sql_postgres&quot;.

You will also need to tell <code>configure</code> how to find the
database-specific libraries and header files:
<pre>
 $ ./configure --with-modules=sql-module-opts \
 --with-includes=/path/to/db/header/file/dir \
 --with-libraries=/path/to/db/library/file/dir
</pre>

Complete the build with the following standard commands:
<pre>
 $ make
 $ make install
</pre>

Logging 
The <code>mod_sql</code> module supports <a href="../howto/Tracing.html">trace logging</a>, via the module-specific log channels:
<ul>
 <li>sql
</ul>
Thus for trace logging, to aid in debugging, you would use the following in
your <code>proftpd.conf</code>:
<pre>
 TraceLog /path/to/ftpd/trace.log
 Trace sql:20
</pre>
This trace logging can generate large files; it is intended for debugging use
only, and should be removed from any production configuration.

</body>
</html>