Authentication Module, MySQLAuth

MySQLAuth enables you to authenticate against an SQL server. You must already have an SQL server setup and working before this module can be tested.

Installing and Setup

Building from Source

Configuration Options

Command Line Options

Supported Commands

Creating/Using a MySQL Database

Downloading a recommended MySQL server

Converting nwauth database to mysqlauth

Installing and Setup

Some applications come with MySQLAuth already built. If you have not got a build and/or require the latest code it can be downloaded from the one of the links below:

Source: auth_src_25b.zip 118k

Windows mysqlauth_2.5a_windows.zip 609k

Linux (libc6)
(For mysql v4.0) mysqlauth_2.5a_linux.tar.gz 594k

Linux 64bit
mysqlauth_linux64.tar.gz

Solaris
(For mysql v4.0) mysqlauth_2.4b_solaris7_sparc.tar.gz 670k

Building From Source:

If you are building MySQLAuth from the source you will need to have a C compiler either cc or
gcc and make. To build the command line prompt would be:

make -f Makefile.mysql config=linux

Configuration Options:

MySQLAuth comes requires an mysqlauth.ini to configurate it's options. This file is located in the same directory as the MySQLAuth binary OR in a directory specified by the -path command line option.

You should consult the mysqlauth.ini that comes with the mysqlauth download this will display all the default and common settings that you will need.Here is an example of what this file should look like:

# Welcome to MySQLAuth ini File.
# Leading # makes line a comment
# Save this file to /etc/mysqlauth.ini or c:\winnt\system32\mysqlauth.ini or
# in same directory as executable.
# Manual page is at, http://www.netwinsite.com/dmail/mysqlauth.htm
# -------------------------------------------------------------------------------------

# SQL Table Field Names
# ---------------------

domain your.domain.com

mysql_server your.sql.server
mysql_login loginname
mysql_password xpassword
mysql_mail_user_db maildb
mysql_mail_user_table maildb
# mysql_mail_uid 99

use_sqlupdate true

field_username username
field_password passwd
field_forward forward
# field_quota quota
# field_mailmask mailmask
# field_maildrop maildrop

# mysql_pwd_set true
# unix_password true
# plain_password true

# Surgemail defaults
# -------------------
# info_fields created created
# info_fields full_name full_name
# info_fields phone phone
# info_fields pass_question pass_question
# info_fields pass_answer pass_answer
# info_fields groups groups

# SQL Aliasing
# ------------
# If you are using SurgeMail this section is not needed.
# As aliasing in surgeMail is NOT stored in the external
# database. This section is for systems which have external
# apps which create/maintain a seperate alias database.

# mysql_mail_alias_db maildb
# mysql_mail_alias_table alias

# field_aliasuser alias
# field_alias username

# Other Extended Fields
# ----------------
# info_fields <mysql schema field> <netauth field>

The table below display the available options are available to use:

Label

Example

Default

Explanation

domain your.default.domain none This is default domain that is appended to any username which does not already have a domain setting attached.

field_username user username The label of the username field in your table.

field_password password passwd The label of the password field in your table.

field_forward fwd forward The label of the forward field in your table. This is the forwarding information that SurgeMail uses to determine whether mail for the account looked up should be delivered to a different address.

field_quota quota none The label of the quota field in your table. This is the disk quota which the user has.
eg: 100k, 10M

field_mailmask mailmask none The label of the mailmask field in your table. This is the IP mask which MySQLAuth checks against the from IP of the user. If specified, this forces the user to connect only to the specified IP in order to collect mail (POP).

field_maildrop maildrop none The label of the maildrop field in your table. This is the location where SurgeMail will drop the user mail files. NB: if the field value is empty in the database (NULL), MySQLAuth will return the keyword 'config' indicating that the server knows where to locate the drop file.

info_fields auth_name mysql_name none
This setting allows you to pass any MySQL setting in the user database back to the program that is using MySQLAuth. The 'mysql_name' is the name that your MySQL server uses. 'auth_name' is the name that you want MySQLAuth to display when returning the information.

log_path mysqlauth.log auth.log This is the MySQLAuth log file.

mysql_server
mysql_server2
your.sql.server none This is the IP or name of the computer hosting the MySQL server. The second MySQL server setting is used if the first server goes down.

mysql_login username none This is the username that has access to the correct database and table that stores the usernames and passwords.

mysql_password password none This is the password that is required and used in conjunction with mysql_login.

mysql_mail_user_db user_data maildb This is the database name where the mail usernames are stored.

mysql_mail_user_table users_list maildb This is the table name that is within the mail user database that has the user details.

mysql_mail_alias_db alias_data none This is the database name where the alias usernames are stored.

mysql_mail_alias_table alias_list aliasdb This is the table name that is within the alias user database that has the user details.

mysql_mail_uid 99 0 This is the unique mail ID. NB: by default, MySQLAuth returns 0 for this, which indicates to the SurgeMail Servers that the user ID should not be checked.

sqlsuffix_lookup_where admin=false none This is the extra information that is added to the end of the 'SELECT .. FROM .. WHERE" MySQL command when the MyAuth is doing a lookup command.

sqlsuffix_check_where admin=false none This is the extra information that is added to the end of the 'SELECT .. FROM .. WHERE" MySQL command when the myauth is doing a check command.

sqlsuffix_set admin=false none This is the extra information that is added to the end of the 'SET" MySQL command.

domain_split (do not use)
mysqldomainname none This will split up the user@domain into 2 separate fields when talking to the SQL server. Were the value of this setting is the name of the MySQL server label. NEVER USE THIS

unix_password true none This will encode passwords with the unix 'crypt()' command.

check_md5 true false Enable md5 password checking

use_md5_prefix true false Add the 'md5' prefix to md5 encrypted passwords {md5}

set_md5 true false Generate 'md5' encrypted passwords when setting a new password

md5_salt_field salt none The field in your database that contains the 'salt' information for password checking

use_sqlupdate true false This causes 'set' commands to use the mysql update function to modify users. The default behaviour is to delete and re-create them. (we recommend using 'true' and the update function) (this setting is no longer used in mysqlauth version 2.5 it automatically uses update if the record exists)

Command Line Options:

-path Tells mysqlAuth where to create it's logfile and where to find it's config file.

-debug Sets debug mode writing debug message to output (should not be used with SurgeMail or similar with this specified)

-log Turns on logging to nwauth.log.

-version This display the mysqlAuth version information

-filtername,str1,str2 The username on a set,del,lookup,check command is check for 'str1' and replaced with 'str2'

-convert_nwauth nwauth_path This will create a 'nw_convert.bat' file from the nwauth.add and nwauth.txt files that when run will run MySQLAuth with various command to make the SQL database match NWAuth setup.

Supported Commands

The commands below are the list of commands that this module supports. For a full description about the command see Authentication Protocol

Command Parameters

check <user> <pass>

lookup <user>

set <user> <pass>|(NULL) [label="value"]

del <user>

search <string> [-from n] [-max m]

version

verbose

help

quit

exit

Command	Parameters
check	<user> <pass>
lookup	<user>
set	<user> <pass>\|(NULL) [label="value"]
del	<user>
search	<string> [-from n] [-max m]
version
verbose
help
quit
exit

Creating/Using a MySQL Database

MySQLAuth requires a MySQL database which is setup and working. The database that is setup must have a username and a password that is encrypted using the MySQL command PASSWORD(). You can either create a new database/table for MySQLAuth or use a current database that has usernames and passwords.

New Database:

Below are instructions on how to setup a brand new database and table to work with MySQLAuth, with all of the features that MySQLAuth provides.
Install mysql server (we used 5.1 but any version should work)
Setup a root password:
	mysqladmin -u root password secret
Set those details in mysqlauth.ini
	mysql_login root
	mysql_password secret
Create database:


	mysql -u root -p
	Password> secret
create database maildb;
use maildb;
CREATE TABLE maildb (
        username VARCHAR(128) binary DEFAULT '' NOT NULL,
        passwd VARCHAR(128) DEFAULT '*' NOT NULL,
        forward VARCHAR(255) DEFAULT '',
        quota VARCHAR(20) DEFAULT '',
        mailmask VARCHAR(18) DEFAULT '0.0.0.0' NOT NULL,
        maildrop VARCHAR(255),

       created VARCHAR(20) DEFAULT '',
       full_name VARCHAR(128) DEFAULT '',
       phone VARCHAR(128) DEFAULT '',
       groups VARCHAR(255) DEFAULT '',

       smsto VARCHAR(128) DEFAULT '',
       mailaccess VARCHAR(255) DEFAULT '',
       mailstatus VARCHAR(128) DEFAULT '',
       spf_block VARCHAR(20) DEFAULT '',
       disabled VARCHAR(20) DEFAULT '',
       alias_quota VARCHAR(20) DEFAULT '',
       list_quota VARCHAR(20) DEFAULT '',
       user_access VARCHAR(255) DEFAULT '',
       send_limit VARCHAR(20) DEFAULT '',
       tohost VARCHAR(255) DEFAULT '',
       realuser VARCHAR(255) DEFAULT '',
       allow VARCHAR(255) DEFAULT '',
       friends VARCHAR(20) DEFAULT '',
       enotify VARCHAR(255) DEFAULT '',

       ddpriv VARCHAR(128) DEFAULT '',
       ddfrom VARCHAR(128) DEFAULT '',
       ccname VARCHAR(128) DEFAULT '',
       ccnumber VARCHAR(128) DEFAULT '',
       ccexpires VARCHAR(20) DEFAULT '',
       ccciv VARCHAR(128) DEFAULT '',
       cctype VARCHAR(128) DEFAULT '',
        PRIMARY KEY (username)
);
CREATE TABLE alias (
       username VARCHAR(128) binary DEFAULT '' NOT NULL,
       alias VARCHAR(128) binary DEFAULT '' NOT NULL,
       PRIMARY KEY (username)
);

# Create an example user to test with (change your.domain to match your surgemail domain name)
INSERT INTO maildb VALUES ('test@your.domain', PASSWORD('test'), '','100mb', '0.0.0.0','','',''   ,'','','','','','','','','','','','','','','','','','','','','','','',''   );
To manually remove a user the command is:

DELETE FROM maildb WHERE username='test@test.org'

The inserting and deleting of users is usually taken care of by MySQLAuth. The above is to show how you would do this manually.

The mysqlauth.ini settings for the above would look like the following:

mysql_server your.sql.server
mysql_login root
mysql_password password

domain your.default.domain

mysql_mail_user_db maildb
mysql_mail_user_table maildb

field_username username
field_password passwd
field_forward forward
field_quota quota
field_mailmask mailmask
field_maildrop maildrop

info_fields created created
info_fields full_name full_name
info_fields phone phone
info_fields pass_question pass_question
info_fields pass_answer pass_answer
info_fields groups groups

Now test the authent module manually:

c:> mysqlauth
lookup test@your.domain
check test@your.domain test
set test2@your.domain test2
check test2@your.domain test2
quit

Using Current DataBase:

If you already have an existing database which you wish to use then as long as the usernames are unique and the password field is encrypted using the PASSWORD() MySQL command you should simply be able to change the ini settings to point to this database, table and field label names.

eg: If you have a database called 'accounts' and a table called 'mail_users' that stores all sorts of information but has the username field names 'name' and the password field called 'pwd' then the ini settings that you required are:

mysql_server your.sql.server
mysql_login login
mysql_password password

domain your.default.domain

mysql_mail_user_db accounts
mysql_mail_user_table mail_users

field_username name
field_password pwd

If you also have the ability to store the mail quota or forwarding, then you can add these ini settings as well...

ie: field_forward forward
field_quota quota

Notes:

If you created a table with a primary key of username, but then used the domain_split feature (not recommended) then you must define a primary key with both user and domain name like this:
alter table maildb drop primary key;
alter table maildb add primary key (username,domain);

Converting nwauth database to mysqlauth

The MySQLAuth binary has a command line switch -convert_nwauth to convert the nwauth database into mysql, use it like this:

nwauth -size 1
set a a
del a
quit
(unix) ./mysqlauth -convert_nwauth /usr/local/surgemail ./mysqlauth
(windows) mysqlauth -convert_nwauth c:\surgemail mysqlauth

and it will produce 2 files:
nw_convert.bat
nw_input.dat

Edit mysqlauth.ini and set plain_password true

These represent 2 different ways to import the users, we'll just use the 2nd method which is faster. Before you do, you need to set the:
plain_password true setting in mysqlauth.ini, this prevents it from re-encoding the
already encoded password, once set you can run:

./mysqlauth < nw_input.dat

Edit mysqlauth.ini and set unix_password true

Source:	auth_src_25b.zip	118k
Windows	mysqlauth_2.5a_windows.zip	609k
Linux (libc6) (For mysql v4.0)	mysqlauth_2.5a_linux.tar.gz	594k
Linux 64bit	mysqlauth_linux64.tar.gz
Solaris (For mysql v4.0)	mysqlauth_2.4b_solaris7_sparc.tar.gz	670k

Label	Example	Default	Explanation
domain	your.default.domain	none	This is default domain that is appended to any username which does not already have a domain setting attached.
field_username	user	username	The label of the username field in your table.
field_password	password	passwd	The label of the password field in your table.
field_forward	fwd	forward	The label of the forward field in your table. This is the forwarding information that SurgeMail uses to determine whether mail for the account looked up should be delivered to a different address.
field_quota	quota	none	The label of the quota field in your table. This is the disk quota which the user has. eg: 100k, 10M
field_mailmask	mailmask	none	The label of the mailmask field in your table. This is the IP mask which MySQLAuth checks against the from IP of the user. If specified, this forces the user to connect only to the specified IP in order to collect mail (POP).
field_maildrop	maildrop	none	The label of the maildrop field in your table. This is the location where SurgeMail will drop the user mail files. NB: if the field value is empty in the database (NULL), MySQLAuth will return the keyword 'config' indicating that the server knows where to locate the drop file.
info_fields	auth_name mysql_name	none	This setting allows you to pass any MySQL setting in the user database back to the program that is using MySQLAuth. The 'mysql_name' is the name that your MySQL server uses. 'auth_name' is the name that you want MySQLAuth to display when returning the information.
log_path	mysqlauth.log	auth.log	This is the MySQLAuth log file.
mysql_server mysql_server2	your.sql.server	none	This is the IP or name of the computer hosting the MySQL server. The second MySQL server setting is used if the first server goes down.
mysql_login	username	none	This is the username that has access to the correct database and table that stores the usernames and passwords.
mysql_password	password	none	This is the password that is required and used in conjunction with mysql_login.
mysql_mail_user_db	user_data	maildb	This is the database name where the mail usernames are stored.
mysql_mail_user_table	users_list	maildb	This is the table name that is within the mail user database that has the user details.
mysql_mail_alias_db	alias_data	none	This is the database name where the alias usernames are stored.
mysql_mail_alias_table	alias_list	aliasdb	This is the table name that is within the alias user database that has the user details.
mysql_mail_uid	99	0	This is the unique mail ID. NB: by default, MySQLAuth returns 0 for this, which indicates to the SurgeMail Servers that the user ID should not be checked.
sqlsuffix_lookup_where	admin=false	none	This is the extra information that is added to the end of the 'SELECT .. FROM .. WHERE" MySQL command when the MyAuth is doing a lookup command.
sqlsuffix_check_where	admin=false	none	This is the extra information that is added to the end of the 'SELECT .. FROM .. WHERE" MySQL command when the myauth is doing a check command.
sqlsuffix_set	admin=false	none	This is the extra information that is added to the end of the 'SET" MySQL command.
domain_split (do not use)	mysqldomainname	none	This will split up the user@domain into 2 separate fields when talking to the SQL server. Were the value of this setting is the name of the MySQL server label. NEVER USE THIS
unix_password	true	none	This will encode passwords with the unix 'crypt()' command.
check_md5	true	false	Enable md5 password checking
use_md5_prefix	true	false	Add the 'md5' prefix to md5 encrypted passwords {md5}
set_md5	true	false	Generate 'md5' encrypted passwords when setting a new password
md5_salt_field	salt	none	The field in your database that contains the 'salt' information for password checking
use_sqlupdate	true	false	This causes 'set' commands to use the mysql update function to modify users. The default behaviour is to delete and re-create them. (we recommend using 'true' and the update function) (this setting is no longer used in mysqlauth version 2.5 it automatically uses update if the record exists)

-path	Tells mysqlAuth where to create it's logfile and where to find it's config file.
-debug	Sets debug mode writing debug message to output (should not be used with SurgeMail or similar with this specified)
-log	Turns on logging to nwauth.log.
-version	This display the mysqlAuth version information
-filtername,str1,str2	The username on a set,del,lookup,check command is check for 'str1' and replaced with 'str2'
-convert_nwauth nwauth_path	This will create a 'nw_convert.bat' file from the nwauth.add and nwauth.txt files that when run will run MySQLAuth with various command to make the SQL database match NWAuth setup.