This patch changes the behaviour of the Courier-MTA package used with MySQL. You can freely download the patch clicking HERE.

The purpose of the patch is to

• allow user names like user%domain.com to log in, transforming the name into user@domain.com before querying mysql,
• optionally introduce an additional field that delivers to mutidomain ISPs some of the domainless login capability that is otherwise available only to local domain.

Disclaimer

I'm currently using this patch. However, I have a specific Courier configuration and I cannot guarantee that it works smoothly with different settings. In particular, I don't use webadmin and I don't know how it behaves with my added field.

Install

Save the patch as, e.g, authmysqllib.patch.txt. Then from your courier-0.3x.x build directory do

 cd authlib
 patch -b authmysqllib.c authmysqllib.patch.txt
 cd ..

Then proceed with usual Courier build and install.

If you want to take advantage of the optional ability to use domainless logins, you must edit authmysqlrc in your courier configuration directory and add lines like

##NAME: MYSQL_ALT_LOGIN_FIELD:0
#
# Optionally define an alternate field where to look for names
# that don't have a '@' in them: this will be used as a last
# resort instead of MYSQL_LOGIN_FIELD, after possibly trying to
# substitute '%' with '@' and to apped a DEFAULT_DOMAIN.
#

MYSQL_ALT_LOGIN_FIELD   alt_addr

Warning: when you run make install-configure extraneous settings such as the one exemplified above will be removed.

Then you must also define the field in the data base. Assume you created the database as, say,

CREATE DATABASE mail;

# grant
GRANT DELETE, INSERT, SELECT, UPDATE ON mail.*
	TO courier@localhost;

# user table
CREATE TABLE user (
	id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	addr CHAR(40) NOT NULL,                 # user@example.com
	home CHAR(56) NOT NULL,                 # /your/path/to/home
	passwd CHAR(30) NOT NULL,               # cleartext password
	name CHAR(40) NOT NULL DEFAULT '',      # real user name
	quota CHAR(24) NOT NULL DEFAULT '',     # xxxS,yyC size/count
	ins TIMESTAMP,
	uid ENUM ('1002') NOT NULL DEFAULT '1002',
	gid ENUM ('1002') NOT NULL DEFAULT '1002',
	INDEX by_addr(addr(16)));

then, you should now modify it as follows:

USE mail;

ALTER TABLE user ADD COLUMN
	alt_addr CHAR(20) NOT NULL DEFAULT '' AFTER addr,
	ADD INDEX by_alias(alt_addr(16));

# test it on some user, find out which one
SELECT id, addr, name FROM user WHERE addr RLIKE 'test';
+----+------------------+------+
| id | addr             | name |
+----+------------------+------+
| 28 | test@example.com |      |
+----+------------------+------+

UPDATE user SET alt_addr = 'test' WHERE id = 28;

Now you are able to login using test, without specifying the @domain.com part. Of course, test must be systemwide unique, therefore you cannot have multiple john for each John that you may host on different domains. However, these login aliases don't need to resemble the user names.

Check that you can login and change your password with webmail. Look at the mysql log to verify what was queried.

How it works

The mysql auth is called whenever a user logs in and also when mail arrives for the user. In the latter case, Courier is looking for user's home directory and already has a fully qualified e-mail address. Anyway, the address is being looked up as follows.

Is there an '@'?

If the user name contains no '@' character, then the first '%' character is replaced with '@'.

Is there now any '@'?

If not, then if DEFAULT_DOMAIN is defined in authmysqlrc, the string "@$DEFAULT_DOMAIN" is appended to the user name. The official implementation of authmysqllib.c only performs this adjustment.

Still no '@'?

If none of the above succeeded, then if MYSQL_ALT_LOGIN_FIELD is defined in authmysqlrc then the query string will end in WHERE "username" = $MYSQL_ALT_LOGIN_FIELD, otherwise the clause will be WHERE "username" = $MYSQL_LOGIN_FIELD. The latter must be defined in authmysqlrc.

When the query succeeds, the user name is taken from the relevant mysql field. Hence change of behaviour is limited to this section of code.

More patches...

If you are looking for other patches to Courier-MTA, you may wan to check
(none)@courier.serv.ch/patches

Changes

05 oct 2001 - published on this page
07 jan 2002 - verified no changes needed for Courier 0.37.0

Have fun!