SQL User Authentication Module for Roxen/1.2

Preamble

This module fills a hole in Roxen capabilities, and completes the options an user has in adiministering its site. In particular, it handles authentication data stored in a SQL database.

Location and documentation

The latest version of this module can be found at:
http://kame.usr.dsi.unimi.it/sw/roxen/sqluserauth/ (my home site),
ftp://ftp.roxen.com/pub/roxen/contrib/ or
the Roxen Module Source

Copyright and Disclaimer of Warranty

This software and the accompanying documentation are © 1997 Francesco Chemolli <kinkie@kame.usr.dsi.unimi.it>. Use, reproduction and distribution are allowed under the terms of the GNU General Public License version 2 or, at your option, any later version. Use of the software implies you know all the terms therein explained and that you agree to these terms.
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.

Design Issues

This module exploits Pike's unique ability to use an uniform interface to all supported databases. These currently options include MiniSQL, MySql, PostgreSql. Commercial extensions should cover Ingres and Oracle.

While the interface is consistent, unluckily not all those databases follow ANSI SQL specifications except for very simple queries. This means that I couldn't automate the database creation process, short of following every SQL dialect of the supported servers.
This module can do pretty everything, from simple authentication to driving an user filesystem or a named FTP server. The last is however deprecated, since too many security issues are at stake with this approach.

Setup

In this description, I assume you know the basics of Roxen confguration and how to use interactively your SQL server.

First off, make sure the Pike interpreter your Roxen server uses supports the database of your choice. Mysql driver is distributed along with the Pike interpreter, Msql and Postgres are not. You can fetch them at the Roxen FTP site. Follow the instruction therein contained to build and install your new interpreter should it be needed.
Copy the SQLuserdb.pike file in a directory where Roxen can find it (see the Roxen configuration Interface), and then add it to the virtual server of your choice. Remember there can be only one user authentication module per virtual server.
Connect to your SQL server, and create a new database. Make sure to grant the user running the WWW server enough privileges to read the database.
Notice that the database server doesn't necesarily have to be on the same host. The host where the database resides, its name and the name of the table containing the authorization information are configurable, so you don't have to worry about this at this time.
Let's suppose that the table is called passwd, it must have at least these column definitions (these are simil-SQL, you'll have to adapt them to your SQL server's syntax:

Example: mSQL 2.0 table definition
CREATE TABLE passwd (
	username char(15) NOT NULL,
	passwd char(13) NOT NULL,
	uid int,
	gid int,
	homedir text(20),
	shell text(10)
)

CREATE UNIQUE INDEX pass_index ON passwd (
	username
)
Notice that you may want to have more tables or more columns for administrative tasks. It is of course possible.
We're almost done. Now you only need to check the default values in the configuration interface.

Configuration

It is pretty straightforward.

Let's start

Okay. Your authentication server is now hopefully set up. All you need is fill in the entries, and test it.
You will probably want to write your customized application/CGI script/pike script/whatever to correctly fill in the entries. Especially since you'll probably have extra administrative information to manage.
The program adduser can be used if such is not the case, or as an example. It simply interactively adds (or changes) user entries on the table passwd in database passwd on whatever server runs on localhost

The fields uid and gid are deprecated. It's in my opinion simply too dangerous to use them. The only use I can think of for them is to drive a named ftp upload. This is DANGEROUS! I will never stress it enough.
My suggestion is to set the "noobody" user and group in the configuration interface and leave these entries in the database empty. Also, I'm afraid to say that for implementation reasons, user ID 0 and group ID 0 (root/root) are not availible. Given the premises, I don't think I'll ever try to implement a way aroud this.

Bugs

Bugs? What are those? <grin>
Seriously. Should you notice strange behaviours, first off check your setup, your SQL server, your authentication.
Should you find in your debug/default.1 log messages of "cannot open server for unknown reason", it's quite likely that you have permission problems or that your SQL server is down.
If you're VERY SURE everything is fine, try to uncomment the #define SQLAUTHDEBUG line at the beginning of the module and reload the module. This will produce a lot of debugging output in the default.1 file.
Should everything else fail, send me a mail trying to describe exactly what the problem is and I'll see what I can do.

Final notes

This module, accompanying documentation and tools are free software. That means, you can use, copy, distribute them freely. Money donations to the author are of course welcome, but not required. My oversized ego however forces me to ask you to write me if you find this program useful. This will not make me richer, but will for sure encourage me to write more, better software, and won't cost you one bit ^_^
I hope my work will be useful, and to hear from you soon.

Sincerely,
Francesco Chemolli