mysqlSendmail Tutorial

Linux/*BSD/Solaris Sendmail admins and ISP owners/operators. BASH shell or similar users skilled at compiling and installing .tar.gz distributed programs. You also need to know about configuring Apache 1.3 series httpd.conf. (DO NOT USE Apache 2.0 in production servers just yet.)

Some minor mySQL skills maybe required for upgrading.

You can always hire us to help you out and that way contribute to the enormous undertaking that maintaining and improving the mysqlISP suite of tools has become.

It is a web based console application (cgi) and (mostly root crontab run) command line application for managing large number of pop (qpopper pop3d etc) and esmtp (sendmail only) users. Can probably be modified quite easily for accountless pop3/imap systems. It is useful for creating complex large telco quality redundant mail server arrays.

It uses the mySQL database system to keep track of all this and uses normal system files to configure sendmail etc. It does however interface with our mysqlRadius Cistron based RADIUS and our modified qpopper for SMTP-AFTER-AUTH and SMTP-AFTER-POP for a "live" modern ISP solution to the dialup relay problem. Even with the current shift to RADIUS based NAS filter and acl rule sets for limiting port 25 traffic to end users, SMTP-AFTER-X systems are still widely needed.

Usually ISP's use our commercial mysqlISP wrappers: ism|3/4 template driven applications that hide all this complex database table handling from the normal user. See www.unixservice.com for more info for complete ISP customer/product/services/billing 100% template driven solutions.

This application is GPL and can be used standalone -or under GPL mysqlISP- so if you don't need support contracts and are fairly skilled you should have no problems running a complete ISP with mysqlISP and it's subsystems like this one mysqlSendmail.

A beta end user passwd changing 100% template driven interface sample is provided.

In the works is a mySQL 5 based stored procedure native GUI interface for mysqlISP.

It does not use "live" mySQL table data for pop3 logins and passwords. Neither does it use mySQL for "hot" Sendmail configuration. Paraphrasing the above: It follows the sucessful KISS strategy of keeping all the Sendmail and server passwd and shadow file information in a mySQL database, and from there it can update, create, recreate all needed Sendmail and system files that you need. And across hundreds of servers if need be.

Out of the box RH 7.3+ linux servers, FreeBSD, NetBSD or similarly Solaris 8 boxen. MAC OSX will also work but with some more work. The glibc2 version of crypt is required for MD5 support (since version 1.51.)

shell>crontab -e

#
#mysqlSendmail. Max setup using smtp-after-auth via our mysql Cistron radius (see mysqlRadius)
#
* * * * * /cgi-bin/mysqlSendmail.cgi CleanLocalAccess; /cgi-bin/mysqlSendmail.cgi\
 ProcessExtJobQueue localhost >> /usr/local/openisp/logs/mysqlSendmailExt.log\
 2>&1 ; /bin/sleep 1 ; /cgi-bin/mysqlSendmail.cgi ProcessJobQueue\
 localhost >> /usr/local/openisp/logs/mysqlSendmail.log 2>&1

#
#mysqlSendmail. Min setup local server with no mysqlISP jobs
#
5/* * * * * /cgi-bin/mysqlSendmail.cgi ProcessJobQueue localhost >>\
 /usr/local/openisp/logs/mysqlSendmail.log 2>&1
#
#mysqlSendmail. basic backup setup. mkdir -p ~openip/mysqlSendmail/data 
#chgrp mysql, chmod g+rwx..etc..
#
5 4 * * * export ISMROOT=/usr/local/openisp;/cgi-bin/mysqlSendmail.cgi Backup\
 mysqlrootpwd > /dev/null

Turn off any mysqlSendmail crontab entries.
Run backup (example: Saved in ~openisp/mysqlSendmail/data.)
Save old mysqlSendmail.cgi just in case....!
Make new mysqlSendmail:
shell>mysql -u mysqlsendmail -pmysqlsendmailpwd mysqlsendmail

mysql>drop table tAccess;

mysql>quit

Open browser to mysqlSendmail.cgi console/login.
click on tAccess.
shell>export ISMROOT=~openisp.
	(csh: setenv ISMROOT ~openisp.)
shell>/cgi-bin/mysqlSendmail.cgi Restore mysqlrootpwd tAccess

That should do it.

shell>/cgi-bin/mysqlSendmail.cgi UpgradeSchema mysqlrootpwd

Since this documentation is sorely lacking depth. You may find it useful to read all the tConfiguration entries. They have extensive notes of interest to developers and users alike.

Latest data/tConfiguration.txt (mySQL export text files) also may have interesting new templates for end-user use.

You can with some mySQL knowledge edit these files and add the relevant parts to your live tConfiguration table.

If upgrading you will need to add some tConfiguration entries. See data/tConfiguration.txt and use your mySQL expertise (or ours) to import the relevant records. Edit these records for your system. Setup spamassassin with spamd/spamc and razor and dcc spam community database support Check the provided user .procmailrc in tConfiguration adjust for your needs. Setup the isolation webmail enabled smtp server as specified in cValue and in the cComment .procmail source. Edit and place in correct place the provided example notify.pl perl script. Check test, test and test before using on production accounts.

Setup ClamAV and a procmail based ClamAV frontend for a very flexible and per user configurable anti-spam and anti-virus system.

Remember to edit the tConfiguration procmail templates. Make sure you setup the spam destination server name correctly and the number of points (via the wacky spamd ***** mail header system)

Example mysqlSendmail (spam cluster spam iso server):

*/2 * * * * /cgi-bin/mysqlSendmail.cgi ProcessExtJobQueue mail.isp.net >>\
 ~openisp/webconsole/logs/mysqlSendmailExtSpam.log 2>>&1 ;\
 /cgi-bin/mysqlSendmail.cgi ProcessJobQueue spam.isp.net >>\
 ~openisp/webconsole/logs/mysqlSendmailLocalSpam.log 2>&1

Only one interface available right now: For changing pop3/imap system password. Simple templates provided in tConfiguration can be changed to suit your needs, as long as variable names and form action is respected.

Setup method:
1-. make
2-. install -s mysqlSendmail.cgi /cgi-bin/ChangeMailPass.cgi
	(or wherever you want it)
3-. For security reasons you should only allow SSL access from your dialups and
	not have mysqlSendmail.cgi or any other ISP management cgi in same cgi-bin.
4-. Modify the tConfiguration cComment based templates for your look and feel.

Note: You should use spamassassin with spamc/spamd and razor and dcc spam id database support. Forget this old and redundant dnsbl stuff. Only provided for die-hards. The tMailFilter system also make it easy to use the excellent ClamAV clamscan with a C front end. See ClamAV site for links and examples.

1-. Example on how to add dnsbl to existing sendmail.cf file without using m4

Added below the # Access list database rule:
# map for DNS based blacklist lookups
Kdnsbl host -T<TMP>n

Added after rules below  "check_relay -- check hostname/address on SMTP startup" 
and before "check_mail -- check SMTP `MAIL FROM:' command argument"

# DNS based IP address spam list relays.osirusoft.com
R$*                     $: $&{client_addr}
R$-.$-.$-.$-            $: <?> $(dnsbl $4.$3.$2.$1.relays.osirusoft.com. $: OK $)
R<?>OK                  $: OKSOFAR
R<?>$+<TMP>             $: TMPOK
R<?>$+                  $#error $@ 5.7.1 $: "553 Rejected: " $&{client_addr} " listed at relays.osirusoft.com"

# DNS based IP address spam list orbs.dorkslayers.com
R$*                     $: $&{client_addr}
R$-.$-.$-.$-            $: <?> $(dnsbl $4.$3.$2.$1.orbs.dorkslayers.com. $: OK $)
R<?>OK                  $: OKSOFAR
R<?>$+<TMP>             $: TMPOK
R<?>$+                  $#error $@ 5.7.1 $: "553 Rejected: " $&{client_addr} " listed at orbs.dorkslayers.com"

Note: There are tabs between the two columns. They HAVE to be in the .cf file!

This system works for smaller ISPs since the maillog is too fast and too big in large ISPs. Unless you have very nice hardware.

Uncomment #define NUKESPAM in the local.h file.

Compile and install again with make clean, make and make install.

Run command 
SHELL>mysqlSendmail.cgi NukeSpamSetup
This will create two tables tMailLog and tRejects where the antispam data
will be stored. 

Note: If you run again the command mysqlSendmail.cgi NukeSpamSetup this will 
remove the two tables and create them again losing all the previous data.

Finally add this crontab entries for NukeSpam:
*/5 * * * * /bin/date << ~openisp/logs/mysqlSendmailNukeSpam.log;\
/usr/bin/tail -1000 /var/log/maillog | ~openisp/cgi-bin/mysqlSendmail.cgi\
 NukeSpamLoad >> ~openisp/logs/mysqlSendmailNukeSpam.log 2>&1;

This provides a static html page with a brief report on sendmail reject operation

8,18,28,38,48,58 * * * * ~openisp/cgi-bin/mysqlSendmail.cgi NukeSpamStats >\
 ~openisp/htdocs/NukeSpamStats.html

Testing if some ip is dnsbl black listed or not : For example to test if the IP 61.30.123.45 is black listed by relays.osirusoft.com you can do the following command (note the format of the query, the IP is backwards and the dnsbl resource is added at the end of the ip):

SHELL>dig 45.123.30.61.relays.osirusoft.com +short
	(+short optional)

And depending of the answer of this DNS query we then know if osirusoft thinks that this IP is a spam relay.

The list of dnsbl resources is at the link below each with the answer it gives (and with an explanation): http://www.declude.com/junkmail/support/ip4r.htm

You can also check here http://relays.osirusoft.com/cgi-bin/rbcheck.cgi and test if an IP is listed.

Please contact support @ openisp . net anytime for free email help.

Most of these are optional since they have hardcoded defaults. You may have special system requirements that need one or more of these to be set by you.

Note that if the tConfiguration.cLabel exists with cLabel empty it is functionally the same as not being in the tConfiguration table at all. This is useful for documentation. Corollary: For reduced load you should delete all tConfiguration entries not needed.

cAccessFile

This is the absolute path to the sendmail access file.

Default: /etc/mail/access

cAliasFile

This is the absolute path to the sendmail aliases file.

Default: /etc/mail/aliases

cAnnounceList

This is a mysqlSendmail internal file path for ISP wide email announcements. The list is created in tAlias right panel -after [Modify]. And usually an alias that uses that list as source is used BCC: in a mail sent to and from support@yourisp.net.

Default: ~openisp/announce.list

cCryptMethod

This is the MD5 or DES encryption method setting. Allowed values are: MD5 or DES.

Default: DES or system crypt default method.

cESMTPServer (Alpha Unstable!)

This is used in mail.c in MakeAccessFile(), CleanLocalAccess() and ProcessJobQueue() but is in alpha use and unstable. We do not recommend it's use unless you can figure out it's system wide implications.

Default: --empty--

cExtJobQueueDbIp

For connecting to mysqlISP job queue. The IP number of the mySQL database server.

Default: --empty-- which means localhost via socket file.

cExtJobQueueDbLogin

For connecting to mysqlISP job queue. The mySQL authorized login. Read mySQL manual GRANT syntax for more information.

Default: mysqlisp

cExtJobQueueDbName

Default: mysqlisp

cExtJobQueueDbPort

Default: --empty-- which means standard mySQL port as compiled in libmysqlclient.a.

cExtJobQueueDbPwd

Default: wsxedc (Please change ASAP!)

cExtJobQueueDbSocket

Default: --empty-- Which means NULL, use DbIP above.

cGID

Default: nobody

cHomeDir

Default: No

cHomeDirPreFix

Default: /home

cISPContactEmail

For internal support email messages.
Used by beta end user template system as the TO: isp support email address.

Default: --empty--

cISPFromEmail

Used by beta end user template system as the FROM: email address.

Default: --empty--

cISPParameter

Beta end user template system. For passwd changing backannotation into mysqlISP client product instances.

Default: --empty--

cLocalFile

This is the sendmail local-host-names (older systems sendmail.cw) file. Or your sendmail.cf specified equivalent. It has one domain per line. The mail server will consider these domains as local, or put another way, should accept mail for these domains.

Default: /etc/mail/sendmail.cw
Note: You can usually just # cd /etc/mail; ln -s local-host-names sendmail.cw

cMTA

Used for sending mail the popen system program, example: /usr/sbin/sendmail

Default: --empty--

cMailGID

For fixing mail boxes in mass command. The mail users group id.

Default: 12

cMakeAccess

The command for creating the sendmail.cf required access.db table.

Default: /usr/bin/makemap hash /etc/mail/access.db < /etc/mail/access

cMakeLocal

The command for reloading sendmail or other operation that insures that new local-host-names will be recognized.

Default: /etc/rc.d/init.d/sendmail restart

cMakeVUT

The command for creating the sendmail.cf required virtusertable.db table. This is the table for domain based virtual aliases. See mysqlSendmail.tVUT

Default: /usr/bin/makemap hash /etc/mail/virtusertable.db < /etc/mail/virtusertable

cMySQLRootPwd

For use to hide mySQL root passwd needed for certain crontab -or- command line operations (see ps scan security attacks.)

Default: --empty--

cNewaliases

The command for creating the sendmail.cf required aliases.db table. The command is usually a symbolic link to sendmail called newaliases.

Default: /usr/bin/newaliases

cNoAliasIfInUser

If "Y" then do not allow same tAlias.cUser on same server.

Default: --empty-- which is the same as "N."

cOnlyClearText

If "Y" then cEnterPasswd in tUser must always be clear text.

Default: --empty-- which is functionally equivalent to "N."

cPopShell

Used by cUserAdd and related for shell name.

Default: /bin/false

cUserAdd

This is a sprintf format string with special format requirments. You can customize as long as you respect the order and number of arguments required. The first %u is the UID, the first %s the group name (or ID number), the second the shell, the third the login. Most systems allow you to take format args off the end, but it is safer to just do a noop like so: ;/bin/false %s. Please note the security implications of this and other mysqlSendmail system calls. Keep your database secure!

Default: /usr/sbin/useradd -u %u -g %s -d /nodir -s %s %s

cUserAddWithHomeDir

Same as above but does not need directory.

Default: /usr/sbin/useradd -u %u -g %s -s %s %s

cUserDelCommand

Command for deleting mysqlSendmail tUser's from system. After this command the tUser.clogin is added. Please note your system specifics that may require additional commands and or options. Also your own preferences for deletion of mail and user home dir (if any.) Of course all of mysqlSendmail could very easily be setup for accountless pop3 and smtp use.

Default: /usr/sbin/userdel

cUserModForHomeDir

See cUserAdd for general sprintf (and see man sprintf for full details.) Here two arguments both are for the tUser.cLogin.

Default: /usr/sbin/usermod -d /home/%.32s %.32s

cUserModForUIDError

See cUserAdd above. Here two arguments first user id number and second the cLogin.

Default: /usr/sbin/usermod -u %u %s

cVUTFile

The file used by cMakeVUT above.

Default: /etc/mail/virtusertable

cuAPop

tUser default on new user creation for auth pop setup. "0" or "1." This is non standard and will be changed to "Y", "N." Someday.

Default: --empty-- "0" which is "No"

cuHDQuota

See cuAPop. The tHDQuota.uHDQuota number in string format.

Default: --empty-- "0", same as none selected.

cuMailFilter

See cuAPop. The tMailFilter.uMailFilter number in string format.

Default: --empty-- "0", same as none selected.

cuTrafficQuota

See cuAPop. The tTrafficQuota.uTrafficQuota number in string format.

Default: --empty-- "0", same as none selected.

tConfiguration cComment Templates (Beta Status!)

cChangeMailLoginForm

Sample login page for end user. Uses symbolic link argv[0] method. For now any cgi name will work the same as long as not mysqlSendmail.cgi

Default: Sample template comes in distribution data/tConfiguration.txt

cChangeMailPasswdForm

See cChangeMailLoginForm. Sample change end user passwd form.

Default: Sample template comes in distribution data/tConfiguration.txt

cChangeMailPasswdOk

See cChangeMailLoginForm. Sample passwd changed ok end user form.

Default: Sample template comes in distribution data/tConfiguration.txt

Please send us corrections and your own experiences and notes to share them with other users. Mail them to support @ openisp . net.