Module mod_auth_msql

This module is contained in the mod_auth_msql.c file and is compiled in by default. It allows access control using the public domain mSQL database ftp://ftp.bond.edu.au/pub/Minerva/msql, a fast but limited SQL engine which can be contacted over an internal Unix domain protocol as well as over normal TCP/IP socket communication. It is only available in Apache 1.1 and later.

Full description / Example / Compile time options / RevisionHistory / Person to blame / Sourcecode

Full description of all tokens

Auth_MSQLhost < FQHN | IP Address | localhost >: Hostname of the machine running the mSQL demon. The effective uid of the server should be allowed access. If not given, or if it is the magic name localhost, it is passed to the mSQL library as a null pointer. This effectively forces it to use /dev/msql rather than the (slower) socket communication.
Auth_MSQLdatabase < mSQL database name >: Name of the database in which the following table(s) are contained (Quick check: use the mSQL command relshow [<hostname> dbase] to verify the spelling of the database name).
Auth_MSQLpwd_table < mSQL table name >: Contains at least the fields with the username and the (encrypted) password. Each uid should only occur once in this table and for performance reasons should be a primary key. Normally this table is compulsory, but it is possible to use a fall-through to other methods and use the mSQL module for group control only. See the Auth_MSQL_Authoritative directive below.
Auth_MSQLgrp_table < mSQL table name in the above database >: Contains at least the fields with the username and the groupname. A user which is in multiple groups has therefore multiple entries. There might be some performance problems associated with this and one might consider to have separate tables for each group (rather than all groups in one table) if your directory structure allows for it. One only need to specify this table when doing group control.
Auth_MSQLuid_field < mSQL field name >: Name of the field containing the username in the Auth_MSQLpwd_table and optionally in the Auth_MSQLgrp_table tables.
Auth_MSQLpwd_field < mSQL field name >: Fieldname for the passwords in the Auth_MSQLpwd_table table.
Auth_MSQLgrp_field < mSQL field name >: Fieldname for the groupname
Only the fields used need to be specified. When this module is compiled with the BACKWARD_VITEK option then the uid and pwd field names default to 'user' and 'password'. However you are strongly encouraged to always specify these values explicitly given the security issues involved.
Auth_MSQL_nopasswd < on | off >: Skip password comparison if passwd field is empty, i.e. allow any password. This is 'off' by default to ensure that an empty field in the mSQL table does not allow people in by default with a random password.
Auth_MSQL_Authoritative < on | off >: Default is 'on'. When set 'on', there is no fall-through to other authorization methods. So if a user is not in the mSQL dbase table (and perhaps not in the right group) or has the password wrong, then he or she is denied access. When this directive is set to 'off', control is passed on to any other authorization modules, such as the basic auth module with the htpasswd file or the Unix-(g)dbm modules. The default is 'on' to avoid nasty 'fall-through' surprises. Be sure you know what you are doing when you decide to switch it off.
Auth_MSQL_EncryptedPasswords < on | off >: Default is 'on'. When set on, the values in the pwd_field are assumed to be crypt-ed using *your* machines 'crypt()' function and the incoming password is 'crypt'ed before comparison. When this function is 'off', the comparison is done directly with the plaintext entered password. (Yes, http-basic-auth does send the password as plaintext over the wire :-( ). The default is a sensible 'on', and I personally think that it is a *very-bad-idea* to change this. However a multi vendor or international environment (which sometimes leads to different crypts functions) might force you to.

Example

An example mSQL table could be created with the following commands:


     % msqladmin create www               

     % msql www                           

     -> create table user_records (       

     ->   User_id  char(32) primary key,  

     ->   Cpasswd  char(32),              

     ->   Xgroup   char(32)               

     ->   ) \g                            

     query OK                             

     -> \q                                

     %

The User_id can be as long as desired. However some of the popular web browsers truncate names at or stop the user from entering names longer than 32 characters. Furthermore the 'crypt' function on your platform might impose further limits. Also use of the require users uid [uid..] directive in the access.conf file where the uid's are separated by spaces can possibly prohibit the use of spaces in your usernames. Also, please note the MAX_FIELD_LEN directive somewhere below.

To use the above, the following example could be in your access.conf file. Also there is a more elaborate description below this example.

<directory /web/docs/private>

Auth_MSQLhost localhost

or

Auth_MSQLhost datab.machine.your.org

If this directive is omitted or set to localhost, it is assumed that Apache and the mSQL database run on the same (physical) machine and the faster /dev/msql communication channel will be used. Otherwise, it is the machine to contact by TCP/IP. Consult the mSQL documentation for more information.

Auth_MSQLdatabase www

The name of the database on the above machine, which contains *both* the tables for group and for user/passwords. Currently it is not possible to have these split over two databases. Make sure that the msql.acl (access control file) of mSQL does indeed allow the effective uid of the web server read access to this database. Check the httpd.conf file for this uid.


Auth_MSQLpwd_table   user_records

This is the table which contain the uid/password combination is specified.


Auth_MSQLuid_field   User_id 

Auth_MSQLpwd_field   Cpasswd

These two directive specify the field names in the user_record table. If this module is compiled with the BACKWARD_VITEK compatibility switch, the defaults user and password are assumed if you do not specify them. Currently the user_id field *MUST* be a primary key or one must ensure that each user only occurs once in the table. If a uid occurs twice access is denied by default; but see the ONLY_ONCE compiler directive for more information.


Auth_MSQLgrp_table   user_records 

Auth_MSQLgrp_field   Xgroup

Optionally one can also specify a table which contains the user/group combinations. This can be the same table which also contains the username/password combinations. However if a user belongs to two or more groups, one will have to use a different table with multiple entries.


Auth_MSQL_nopasswd           off 

Auth_MSQL_Authoritative        on  

Auth_MSQL_EncryptedPasswords on

These three optional fields (all set to the sensible defaults, so you really do not have to enter them) are described in more detail below. If you choose to set these to any other values then the above, be very sure you understand the security implications and do verify that Apache does what you expect it to do.


AuthName                example mSQL realm 

AuthType                basic

Normal Apache/NCSA tokens for access control

<limit get post head>
order deny,allow
allow from all

require valid-user

valid-user; allow in any user which has a valid uid/passwd pair in the above pwd_table.

or
require user smith jones

Limit access to users who have a valid uid/passwd pair in the above pwd_table *and* whose uid is 'smith' or 'jones'. Do note that the uid's are separated by 'spaces' for historic (NCSA) reasons. So allowing uids with spaces might cause problems.

require group has_paid

Optionally also ensure that the uid has the value 'has_paid' in the group field in the group table.

<limit>

Compile Time Options

#define ONLY_ONCE 1

If the mSQL table containing the uid/passwd combination does not have the uid field as a primary key, it is possible for the uid to occur more than once in the table with possibly different passwords. When this module is compiled with the ONLY_ONCE directive set, access is denied if the uid occurs more than once in the uid/passwd table. If you choose not to set it, the software takes the first pair returned and ignores any further pairs. The SQL statement used for this is

"select password form pwd_table where user='UID'"

this might lead to unpredictable results. For this reason as well as for performance reasons you are strongly advised to make the uid field a primary key. Use at your own peril :-)

#define KEEP_MSQL_CONNECTION_OPEN

Normally the (TCP/IP) connection with the database is opened and closed for each SQL query. When the Apache web-server and the database are on the same machine, and /dev/msql is used this does not cause a serious overhead. However when your platform does not support this (see the mSQL documentation) or when the web server and the database are on different machines the overhead can be considerable. When the above directive is set defined the server leaves the connection open, i.e. no call to msqlClose(). If an error occurs an attempt is made to reopen the connection for the next http request.

This has a number of very serious drawbacks

It costs 2 already rare file-descriptors for each child.
It costs msql-connections, typically one per child. The (compiled in) number of connections mSQL can handle is low, typically 6 or 12. which might prohibit access to the mSQL database for later processes.
When a child dies, it might not free that connection properly or quick enough.
When errors start to occur, connection/file-descriptor resources might become exhausted very quickly.

In short, use this at your own peril and only in a highly controlled and monitored environment.

#define BACKWARD_VITEK #define VITEK_uid_name "user" #define VITEK_gid_name "passwd"

A second mSQL auth module for Apache has also been developed by Vivek Khera <khera@kciLink.com> and was subsequently distributed with some early versions of Apache. It can be obtained from ftp://ftp.kcilink.com/pub/mod_auth_msql.c*. Older 'vitek' versions had the field/table names compiled in. Newer versions, v.1.11 have more access.conf configuration options. However these where chosen not to be in line the 'ewse' version of this module. Also, the 'vitek' module does not give group control or 'empty' password control.

To get things slightly more in line this version (0.9) should be backward compatible with the 'vitek' module by:

Adding support for the Auth_MSQL_EncryptedPasswords on/off functionality
Adding support for the different spelling of the 4 configuration tokens for user-table-name, user/password-field-name and dbase-name.
Setting some field names to a default which used to be hard coded in in older 'vitek' modules.

If this troubles you, remove the 'BACKWARD_VITEK' define.

#define MAX_FIELD_LEN (64) #define MAX_QUERY_LEN (32+24+MAX_FIELD_LEN*2+3*MSQL_FIELD_NAME_LEN+1*MSQL_TABLE_NAME_LEN)

In order to avoid using the very large HUGE_STRING_LENGTH, the above two compile time directives are supplies. The MAX_FIELD_LEN contains the maximum number of characters in your user, password and group fields. The maximum query length is derived from those values.

We only do the following two queries:

For the user/passwd combination
"select PWDFIELD from PWDTABLE where USERFIELD='UID'"
Optionally for the user/group combination:
"select GROUPFIELD from GROUPTABLE where USERFIELD='UID' and GROUPFIELD='GID'"

This leads to the above limit for the query string. We are ignoring escaping a wee bit here assuming not more than 24 escapes.)

Revision History

This version: 23 Nov 1995, 24 Feb 1996, 16 May 1996.

Version 0.0: First release
Version 0.1: Update to Apache 1.00
Version 0.2: Added lines which got missing God knows when and which did the valid-user authentication no good at all !
Version 0.3: Added 'Auth_MSQL_nopasswd' option
Version 0.4: Cleaned out the error messages mess.
Version 0.6: Inconsistency with gid/grp in comment/token/source Make sure you really use 'Auth_MSQLgrp_field' as indicated above.
Version 0.7: *host to host fixed. Credits go to Rob Stout, <stout@lava.et.tudelft.nl> for spotting this one.
Version 0.8: Authoritative directive added. See above.
Version 0.9: palloc return code check(s), should be backward compatible with 1.11 version of Vivek Khera <khera@kciLink.com> msql module, fixed broken err msg in group control, changed command table messages to make more sense when displayed in that new module management tool. Added Auth_MSQL_EncryptedPasswords on/off functionality. msqlClose() statements added upon error. Support for persistent connections with the mSQL database (riscy). Escaping of ' and \. Replaced some MAX_STRING_LENGTH claims.

Contact/person to blame

This module was written for the European Wide Service Exchange by <Dirk.vanGulik@jrc.it>. Feel free to contact me if you have any problems, ice-creams or bugs. This documentation, courtesy of Nick Himba, <himba@cs.utwente.nl>.

Sourcecode

The source code can be found at


http://www.apache.org

. A snapshot of a development version usually resides at


http://me-www.jrc.it/~dirkx/mod_auth_msql.c

. Please make sure that you always quote the version you use when filing a bug report.

Furthermore a test/demonstration suite (which assumes that you have both mSQL and Apache compiled and installed) is available at the contrib section of ftp://ftp.apache.org/apache/dist/contrib or http://me-www.jrc.it/~dirkx/apache-msql-demo.tar.gz and its README file.