mod_sql v.4.x ======= mod_sql is an authentication and logging module for ProFTPD. It is comprised of a front end module (mod_sql.c), and database-specific backend modules such as mod_sql_mysql.c or mod_sql_postgres.c. mod_sql leaves the specifics of database handling to the backend modules. 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>. 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 */ };