Loading...
 

Courier MySQL authentication module

This module allows authentication information to be stored in a MySQL database.

  • Supports both physical and virtual mail accounts.

  • Can be configured to use either crypted or cleartext passwords (or both). Cleartext passwords allow CRAM-MD5 authentication to be implemented.


When authmysql is installed, a default configuration file, authmysqlrc will be installed too. You must edit this file to set up your MySQL authentication.

Edit authmysqlrc, and initialize the following variables:

  • MYSQL_SERVER - MySQL server name (required).
  • MYSQL_PORT - server port where MySQL accepts connections.
  • MYSQL_SOCKET - If MySQL is running on the same machine and can accept connections from a filesystem socket, enter the path to the filesystem socket here, and do not initialize SERVER/PORT.
  • MYSQL_USERNAME - MySQL username with log in with (required).
  • MYSQL_PASSWORD - MySQL password with log in with (required).
  • MYSQL_DATABASE - MySQL database to log in to (required).
  • MYSQL_SSL_KEY - name of the field containing a SSL key path (optional).
  • MYSQL_SSL_CERT - name of the field containing a SSL certificate path (optional).
  • MYSQL_SSL_CACERT - name of the field containing a SSL CA certificate (optional).
  • MYSQL_SSL_CAPATH - name of the filend containing a name to a directory that contains trusted SSL CA certificates in pem format. (optional).
  • MYSQL_SSL_CIPHER - name of the field containing a SSL cipher list (optional).
  • MYSQL_USER_TABLE - name of the MySQL with the authentication information (see below) (required).
  • MYSQL_LOGIN_FIELD - field that contains the login id for this account.
  • MYSQL_CRYPT_PWFIELD - name of the field containing the crypt-ed password (either MYSQL_CRYPT_PWFIELD or MYSQL_CLEAR_PWFIELD is required). NOTE: this password must be crypt-ed using the operating system’s crypt function, NOT MySQL’s crypt function. MySQL’s crypt() function is non-standard and is not generally compatible with the operating system’s crypt function.
  • MYSQL_CLEAR_PWFIELD - name of the field containing the cleartext password (either MYSQL_CRYPT_PWFIELD or MYSQL_CLEAR_PWFIELD is required).
  • MYSQL_UID_FIELD - field that contains the system userid for this account.
  • MYSQL_GID_FIELD - field that contains the system groupid for this account.
  • MYSQL_MAILDIR_FIELD - name of the field containing a non-default location of the account’s system mailbox (optional).
  • MYSQL_DEFAULTDELIVERY - default mail delivery instructions for the Courier mail server. If this field in the record is not empty, its contents supercede the DEFAULTDELIVERY setting.
  • MYSQL_QUOTA_FIELD - name of the field containing a maildir quota (optional).
  • MYSQL_AUXOPTIONS - auxiliary options. This field, if defined, should consist of a VARCHAR consisting of a comma-separate list of “-+IDENTIFIER=VALUE+-” pairs, that specify per-account options. See INSTALL’s description of the “Account OPTIONS” setting for more information.
  • DEFAULT_DOMAIN - if the user logs in without specifying @domain, use the following domain (in this case the id field must always contain user@host) (optional).
  • MYSQL_WHERE_CLAUSE - optional freeform SQL that is appended to the SQL query string.


Here’s a recommended definition of MYSQL_USER_TABLE: 

CREATE TABLE passwd (
id                    char(128) DEFAULT '' NOT NULL,
crypt                 char(128) DEFAULT '' NOT NULL,
clear                 char(128) DEFAULT '' NOT NULL,
name                  char(128) DEFAULT '' NOT NULL,
uid                   int(10) unsigned DEFAULT '65534' NOT NULL,
gid                   int(10) unsigned DEFAULT '65534' NOT NULL,
home                  char(255) DEFAULT '' NOT NULL,
maildir               char(255) DEFAULT '' NOT NULL,
defaultdelivery       char(255) DEFAULT '' NOT NULL,
quota                 char(255) DEFAULT '' NOT NULL,
options               char(255) DEFAULT '' NOT NULL,


KEY id 128  );


Observe that a valid SQL expression may be used in place of any field setting, since all that happens is that the contents of the settings are inserted into an SQL SELECT statement. Specifically, the options field may be replaced by several normal fields, such as ” disablepop3 “, ” disableimap “, ” disablewebmail “, and ” sharedgroup “; then MYSQL_AUXOPTIONS would be set to:
CONCAT("disableimap=",disableimap,",disablepop3=",disablepop3,",disablewebmail=",disablewebmail,",sharedgroup=",sharedgroup)
This results in the expected comma-delimited options list, which is built up from individual table fields.

----------

After creating the database, dbuser and passwd table above, a new user can be added using the following SQL: 

mysql> insert into passwd (id,crypt,name,uid,gid,home,maildir) values('john','kdfjalkjfjw-fefa','John Doe',1003,1003,'/home/john','Maildir');


The password crypt can be obtained using openssl as follows: 

$openssl passwd -crypt