4.1a 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.

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 libary 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_Authorative 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 optionaly 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_Authorative < 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 wih 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 crypted 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.


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
Auth_MSQLhost datab.machine.your.org
If this directive is ommited 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
Optionaly 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_Authorative 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

require user smith jones
require group has_paid

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 occures 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 adviced to make the uid field a primary key. Use at your own peril :-)

Normally the (tcp/ip) connection with the database is opened and closed for each SQL query. When the Apache webserver 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 occures an attempt is made to reopen the connection for the next http request.

This has a number of very serious drawbacks

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

#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 optained 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 choosen 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:

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

#define MAX_FIELD_LEN (64)
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:

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 authentification 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, for spotting this one.
Version 0.8
Authorative directive added. See above.
Version 0.9
palloc return code check(s), should be backward compatible with 1.11 version of Vivek Khera 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, icecreams or bugs. This documentation, courtesy of Nick Himba, <himba@cs.utwente.nl>.


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 avaible 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.