Sendmail-SQL Files

Sendmail SQL README, Version 8.12.8-7

SENDMAIL+PGSQL 
(Verbatim from http://www.missing.net/pgsendmail.php3 for the time being)

July 9, 2000 - Fix for "Broken pipe" error.

Don't you just love a patch that fixes another patch? Sheesh! Sometimes I wonder if I'm getting as bad about bugs as a large software conglomerate located in Redmond, WA. Nah. I hope I'll never get that bad.

Click here to get the fix for this problem if you have already patched your Sendmail 8.9.3 with the first Sendmail + PostgreSQL patch. If you just want to do the whole thing over, a fresh patch for Sendmail 8.9.3 with the fixes in it is here.

The fix for the PostgreSQL-patched Sendmail 8.10.2 is here and the new complete patch is here.

Thanks to quite a few people for finding this problem. You know who you are...

History

If you use Sendmail's virtual user tables, then you know what a hassle it can be rebuilding the flat- file maps with makemap every time you add a new virtual user table entry. This gets to be just impossible when you have 10,000+ customers using 200+ domains scattered across multiple mail exchangers.

Many ISP's (Bluegrass Net included) route email for other local domains into one POP email account. A lot of times a customer will ask to have his/her email routed to another person while they are on vacation or otherwise unable to read their email for a period of time. This means a tech has to put a .forward file in (bad) or hand-edit the virtual user table (worse?) to put an entry in. Not pretty.

Since there is the potential for multiple people to edit the same map source file at the same time, there is the danger that the file will be corrupted or changes will be lost. Using PostgreSQL eliminates the need for hand-editing Sendmail map source files as a root user, with the possibility for typing mistakes and duplicates.

NEWDB maps need to be rebuilt with makemap every time there is a change. It's been my experience that on a busy email server, Sendmail sometimes just locks up running makemap and/or does not process the changes. I have seen this behavior on FreeBSD, BSDi and Linux using the Berkeley DB, both the 1.85 release and the newer versions. I don't know where the problem occurs, or if it's a file locking issue or what. I just know it occurs and drives me crazy.

The overall goal was to allow general web-based control of user information in general (not just Sendmail map files) using a PHP-based system. Since PHP can use PostgreSQL, it was the obvious choice. PostgreSQL is an excellent general-purpose data store, you can put all sorts of things in the tables, not just map entries. This opened up a number of opportunities that are the basis of an entire reengineering of our ISP. Sendmail map files just happened to be an excellent place to start.

Why didn't I use the LDAP map feature? For one, I'm not familiar enough with LDAP to make use of it. Certainly, it's probably better suited for this task and LDAP can be edited with PHP, but the learning curve was simply too much for the rest of the coders. We needed a good solution that worked and could be implemented quickly, but also allowed flexibility in allowing other information to be stored. So PostgreSQL was the choice.

What it does

The patch adds a new map class ("pgsql") to Sendmail, which is used in the Sendmail config file (usually sendmail.cf) when specifying maps. You can continue to use other map classes as before, so it does not take away anything. I have tested and used the patch with the following maps: 

mailertable
virtusertable
access_db
aliases (replaces /etc/aliases)

Note that I plan on working on an a "dynamic SMTP relay authorization" that will make more use of the "access_db" map using the "pgsql" map class in the future.

Thanks to Sandu Mihai for letting me know that this patch can also replace /etc/aliases! See here for details.

Installation Steps

This patch has been tested and is operating on:

FreeBSD 3.3, 3.4 and 4.0
BSDi 3.1
Mandrake Linux 7.0
Redhat Linux 5.2

If you are using something different, then please let me know the results.

You'll need to have PostgreSQL or the library and header files for PostgreSQL installed on your system before you proceed. I am using version 6.5.3, so I don't know about other versions working. I will be installing PostgreSQL 7.0 and testing with it in the near future. Once you have PostgreSQL 6.5.3 installed, or it's already installed, you'll need to know the location of "libpq.a" and "libpq-fe.h" on your system.

If you don't have the source code to Sendmail go get it from Sendmail.Org and untar it where you are going to build Sendmail. Currently I only have a patch for Sendmail 8.9.3 but I am working on one for Sendmail 8.10.1.

Sendmail 8.9.3 (Version 1.0)

Get the PostgreSQL map patch for Sendmail 8.9.3 (Version 2.0) 
Copy the patch into the "src" subdirectory where you untarred Sendmail. 
Type patch < pgsqlmap-8.9.3-2.patch at the command line. 

Sendmail 8.10.2 (Version 1.1)

Get the PostgreSQL map patch for Sendmail 8.10.2 (Version 2.0) 
Copy the patch into the "sendmail" subdirectory where you untarred Sendmail. 
Type patch < pgsqlmap-8.10.2-2.patch at the command line.

Both versions

Find out which file Sendmail is going to use as it's build file by running "Build" with the "- m" option and looking at the last line of output (the build filename for me using Sendmail 8.9.3 is ../BuildTools/OS/Linux) 
Make a copy of the build file (depending on your editor you might also need to chmod 644 it before you can modify it) and append a few lines to it to enable the PostgreSQL maps. Change confINCDIRS and confLIBDIRS in your local copy of the build file to point to the directories where "libpq-fe.h" and "libpq.a" (or .so) are on your system.

Note: These are examples only - your system build file will contains lots of other stuff. You would be best served to keep the information that is currently in your build file and just add the definitions below into it. 


   define(`confMAPDEF',`-DPGSQLMAP')
   define(`confINCDIRS',`-I/usr/local/pgsql/include')
   define(`confLIBDIRS',`-L/usr/local/pgsql/lib')
   define(`confLIBS',`-lpq')


(Watch out for those funky "open single quote" characters. This drove one of our junior admins nuts for about an hour. It's the character on the same button as the tilde ("~") character.)

Run Sendmail's "Build" script, specifying the copy you edited, using the "-f" option. Make a note of where the "obj" directory is; you'll need to know this when you are ready to test.

If everything goes well with the build process, you should have a copy of Sendmail that's capable of using PostgreSQL maps now. This feature is not active yet, but if you are brave you can kill Sendmail now and install this version using ./makesendmail install. I walked through all of these steps as I was writing this and I had no unexpected problems. Just make sure you have access to the include and library paths for PostgreSQL.

Making it work

OK, so you just compiled a new Sendmail daemon. Before you install it, you need to test it out. I suggest making a backup copy of your current Sendmail daemon before installing just in case. My examples assume that you are capable of using the psql tool that comes with PostgreSQL to create databases and tables, that you know enough SQL to create tables and insert records and that you understand Sendmail config files. So to begin, you'll need to have access to a running PostgreSQL database server to make some tables in it. I'll use Sendmail's virtuser map for my example, but any Sendmail map can be used.

If you are familiar with the Sendmail virtuser map, you know that you need two columns, one for the incoming email address and one for the "mapped" outgoing email address. So your PostgreSQL table needs at least these two columns in it, but you could conceivably use much more complex of a table. Here's a quickie table and index to test with, but it will also work for production use. You can cut and paste it into psql if you want:

create table virtuser(emailin char(128), emailout char(128));
create index virtuseridx on virtuser using btree(emailin);

Now you should stick a few records in it using SQL that you can test with. This is entirely
dependent on your installation, but suffice it to say that you use something like:

insert into virtuser (emailin,emailout) values ('testin','testout');

Where testin is an incoming email address and testout is what you want it rewritten as. I suggest
you use something that's already in your virtuser map.

Change to the Sendmail "obj" directory inside the Sendmail source tree. Make a copy of your sendmail.cf file into this directory and edit it with your favorite editor. Locate the virtuser map and comment it out with a "#". Now you'll need to replace this map with the PostgreSQL map. Since there are too many variations on this, I'll show you the one I am using:

# Virtual user table (maps incoming users)
#Kvirtuser hash -o /etc/virtusertable
Kvirtuser pgsql -c "dbname=system user=sendmail"
 -s "select distinct emailout from virtuser where emailin='%s'"



June 12, 2000  Sandu Mihai emailed me to inform me that this patch will also handle alias
expansions found in /etc/aliases. I gave it a test using this in my Sendmail config file:


#replaces /etc/aliases
Kaliases pgsql -c "dbname=system user=sendmail"
 -s "select distinct aliasaddr from aliases where localaddr='%s'"


I also commented out the lines O AliasWait=10 and O AliasFile=/etc/aliases in the Sendmail config file just to be sure. I tested it out and it does indeed work as expected. I'll leave the SQL table creation to your imagination.

The key points are:

The -c option specifies a PostgreSQL connect string, as documented in the PostgreSQL manual. You'll need to fill in whatever is appropriate for your system.
The -s option specifies the SQL query that is to be executed. The "%s" in the string is replaced by the email address to be queried and must be there The distinct directive instructs PostgreSQL to return only the first matching row. It's not required but it speeds stuff up.

Now you're ready to test. You'll be using Sendmail in "address test mode" using the "-bt" option off the command line to see if the map resolves correctly. You will also specify the copy of the Sendmail config file you edited to put the map information in. If you have never used this, it looks like this:


./sendmail -bt -Csendmail.cf
ADDRESS TEST MODE (ruleset 3 NOT automatically invoked)
Enter <ruleset> <address>
> 3,0 info@missing.net
rewrite: ruleset   3   input: info @ missing . net
rewrite: ruleset  96   input: info < @ missing . net >
rewrite: ruleset  96 returns: info < @ missing . net . >
rewrite: ruleset   3 returns: info < @ missing . net . >
rewrite: ruleset   0   input: info < @ missing . net . >
rewrite: ruleset 199   input: info < @ missing . net . >
rewrite: ruleset 199 returns: info < @ missing . net . >
rewrite: ruleset  98   input: info < @ missing . net . >
rewrite: ruleset  98 returns: info < @ missing . net . >
rewrite: ruleset 198   input: info < @ missing . net . >
rewrite: ruleset  97   input: jyarden
rewrite: ruleset   3   input: jyarden
rewrite: ruleset  96   input: jyarden
rewrite: ruleset  96 returns: jyarden
rewrite: ruleset   3 returns: jyarden
rewrite: ruleset   0   input: jyarden
rewrite: ruleset 199   input: jyarden
rewrite: ruleset 199 returns: jyarden
rewrite: ruleset  98   input: jyarden
rewrite: ruleset  98 returns: jyarden
rewrite: ruleset 198   input: jyarden
rewrite: ruleset 198 returns: $# local $: jyarden
rewrite: ruleset   0 returns: $# local $: jyarden
rewrite: ruleset  97 returns: $# local $: jyarden
rewrite: ruleset 198 returns: $# local $: jyarden
rewrite: ruleset   0 returns: $# local $: jyarden
> ^D


This shows that "info@missing.net" is mapped to the local account "jyarden" which just happens to be me. Ctrl-D returns you to the shell prompt. BTW, you can see the record of my virtuser that makes this example work below:


pgsql=> select emailin, emailout from virtuser;
emailin                       |emailout
------------------------------+---------------------------------
@missing.net                  |jyarden
@whatsthis.com                |jyarden
(2 rows)

pgsql=>


Common Problems

If you get an error on Sendmail startup, it's most likely due to not having the proper database permissions. Check the "-c" option and make sure that you really have access to the database using the same parameters. psql has a lot of options and I am not going to go into detail on how to manage permissions in PostgreSQL.

If you get an error on a query, check the "-s" option to make sure it looks correct. See if you can reproduce the error manually using psql. If you can't, make sure that you have permissions to access the table.

If something really goes bonk, either comment out the "pgsql" map line and uncomment your original map line, or replace sendmail.cf with a backup copy (you did make a backup copy of the Sendmail config file, right?)

Feedback

I'm quite sure that I am going to be getting a lot of email asking for help getting this patch to work. Please be aware that I will not be able to provide any support for people free of charge. Additionally, I will not provide support through email unless it's to fix a bug. Provided that I have time, I will be able to offer assistance for a fee. If you are interested in getting this patch integrated and working for your company, please contact me and we can make arrangements.

Update (June 9, 2000): I didn't realize what a jerk I came off like on the above paragraph until I ran into a problem yesterday and needed someone else's help to fix it. Let me "amend" the above statement by saying this: I'll gladly help people, provided that I can answer questions without eating into my normal work time (which at the moment is pretty much all the time). If it's a simple patch-related question that I can answer quickly, then I'm obviously going to for nothing. But for questions about Sendmail or PostgreSQL, or anything other than the patch itself, then there has to be a fee. I don't think it's unreasonable to charge for this.

So if you are having trouble, send me an email and let me see if I can answer you quick. If it's something that is going to require additional time, or I get asked to explain how to create an SQL table, I'll probably tell you to that it's time to pull out the wallet. There are so many systems that I just don't know all of the details of rolling this out to all of them, even though it's probably more fun than work.

Jon

BUILDING SENDMAIL+MYSQL.

If you are reading this file, then you have probably already obtained the
appropriate patch from http://sourceforge.net/projects/sendmail-sql/

In order to build Sendmail with (My)SQL support (more database engines are
planned in future), you will need to do roughly the following:

tar -C <your sendmail root> -zxvf sendmail-<version>.tar.gz
cp sendmail+mysql-<version>.patch <your sendmail root>
cd <your sendmail root>
patch -p1 < sendmail+mysql-<version>.patch

If there are any errors that stop the patch, the you may need to retry with -p0
in your patch incantation.

If the patch fails due to files that do not exist, and the patch cannot add to
them, then delete all of the sendmail source, untar it again, do
"touch <missing file>", and then re-apply the patch.

Once the patch has applied correctly, without error, then you will need to
compile Sendmail as normal, with the "./Build" script (or with "make", for
earlier versions of sendmail?).

Watch out during the compilation for "-DMYSQLMAP". If you do not see that
during the compilation, you will need to add it into the Makefile manually,
as it should always be there, and yet that definition has escaped from some
versions of the Sendmail SQL patch.

If you do not see "-DMYSQLMAP", Sendmail will (probably) compile okay, but
you'll get "map class mysql not available" (or similar) when you try and
utilise MySQL maps.

CONFIGURATION.

The main configuration file for Sendmail-SQL is found in /etc/mail/mail.conf,
for versions up to 8.11.2(?), and thereafter /etc/mail/sqlmail.conf.

Its format is thus:

#############################
# MySQL Config for Sendmail #
#############################

MysqlHost               <mysql host>
MysqlUsername           <mysql userame>
MysqlPassword           <mysql password>
MysqlDatabase           <mysql database>
MysqlUserTable          <user table>
MysqlAliasTable         <alias table>
MysqlMapTable           <tables used for maps>
MysqlLHSColumn          <lhs column for the aliases table>
MysqlRHSColumn          <rhs column for the aliases table>

Where the "user" table is equivalent to /etc/passwd under Sendmail-SQL, and
the "alias" table is equivalent to /etc/[mail/]aliases under Sendmail-SQL,
and the LHS and RHS are equivalent to respective sections of the aliases file.

The "MysqlMapTable" defines the default(s?) table(s?) for Sendmail-SQL to try
for a class mapping.

This file should be viewed as a file defining defaults, as most of the options
in this file can be overriden on a per class-definition basis, as below.

SENDMAIL CONFIGURATION

If you are using m4 to configure your sendmail implementation, then you should 
read through all the following to work out what you are going to require, and 
read the m4 configuration section below. If you are not using m4, then you 
should add in the appropriate configuration directives directly into
/etc/sendmail.cf (may be /etc/mail/sendmail.cf or other location).

ALIASES CLASS

The details in the defaults file define how Sendmail-SQL handles the aliases
class.

OTHER CLASSES

The new feature that it is 0.2.3 is that now you can have 
a MySQL class for any kinda marco like the F class.

Ex: Qw -Hlocalhost -Dmail -Tdomain

You can store any kinda marco data in MySQL like in any F class
for exif you have a table like that, it will assign all those values
the the w marco so instead of the sendmail.cw file you can have the
following table:

+--------------------+
| domain             |
+--------------------+
| altavista.com      |
| virtualdomain1.com |
| yahoo.com          |
| mydomain.net       |
+--------------------+

or like that:

+--------------------+
| SpamList           |
+--------------------+
| altavista.com      |
| virtualdomain1.com |
| yahoo.com          |
| mydomain.net       |
+--------------------+
 and the you just use it like F or C classes:

Q{SpamList} -Hlocalhost -Dmail -TSpam

for the Q( MySQL ) class you can only have a simple table
with 1 column.

The passwordand username are taken only from /etc/sendmail_mysql.con
( for now )

The options:

-H -> hostname
-D -> database name
-T -> table name
-U -> MySQL username to access the database.

for the rest please read the prvious READMEs
A full and more comprehencive doc is to come

OTHER MAPS

You can also define maps (which you can use for transforms, whereas you can only
perform lookups on a class), using Sendmail-SQL.

For example, this section of table will map several subdomains of ore.org to
the root domain ore.org:

+----------------+---------+
| lhs            | rhs     |
+----------------+---------+
| a.ore.org      | ore.org |
| b.ore.org      | ore.org |
| c.ore.org      | ore.org |
+----------------+---------+

The following options exist in Sendmail-SQL class definitions (note the extras):

-H -> hostname
-D -> database name
-M -> Map table name
-U -> MySQL username to access the database.
-L -> LHS column in the table
-R -> RHS column in the table

So, you would use the following configuration line, for example to access the
above table structure for the "virtuser" class:

Kvirtuser mysql -Mvirtuser -Llhs -Rrhs -Dsendmail -Hlocalhost -U<mysql user>

It is also probably a good idea not to name all of your column names "lhs" and
"rhs" for map tables, because doing that can mean that a simple typo, or
missing configuration option for the table name to interrogate for a particular
map can then become incredibly hard to detect. 

A better option would perhaps be to have each table include the table name in
the lhs and rhs columns, ie access_lhs, and access_rhs for the access class
table's column names.

DAMHIKIJKOK.

M4 CONFIGURATION.

At this stage, m4 configuration for Sendmail SQL is in its infancy, even more
so than the whole project.

There is no support for Sendmail-SQL mysql-alias configuration via m4, since
there is no support for generating /etc/mail/sqlmail.conf, etc. via m4.

However, if you wish to utilise SQL maps and classes, you can use the following
m4 lines in your /etc/mail/sendmail.mc file, and use the appropriate m4
incantation to generate /etc/sendmail.cf from it:

m4 /etc/mail/sendmail.cw > /etc/sendmail.cf

The following can be adapted to your particular needs, but should generate
mysql maps in your sendmail.cf:

FEATURE(`genericstable',`mysql -Mgenericstable')dnl
FEATURE(`virtusertable',`mysql -Mvirtuser -Llhs -Rrhs -Dsendmail')dnl
FEATURE(`mailertable',`mysql -Mmailertable -Ldomain -Rmapping -Dsendmail')dnl

Or if you're feeling particularly brave, you may try the following, which are
entirely untested, but included in the patch at this time.

FEATURE(`sql-genericstable');
FEATURE(`sql-virtusertable');
FEATURE(`sql-mailertable');
FEATURE(use_cw_sql);

Sendmail SQL classes are currently not supported at all via m4 configuration.

TESTING/DEBUGGING.

Once you have built your Sendmail SQL binaries, and created your test
configuration file (you didn't overwrite your main sendmail.cf with this yet,
right??), you can test them using "sendmail -bt".

To test a map, use "/map <map name> <address>" in sendmail's test mode:

[root@akasha SOURCES]# sendmail -bt
ADDRESS TEST MODE (ruleset 3 NOT automatically invoked)
Enter <ruleset> <address>
> /map virtuser webmaster@ore.org
map_lookup: virtuser (nospam@ore.org) no match (68)
> [CONTROL+D TO EXIT]

To test the entire routing of a mail, use "3,0 <address>" while in test mode.

ADVICE.

You can configure your primary MX server to run Sendmail-SQL, and your backup
to run the same, using MySQL replication between the two.

However, for various reasons, if your connectivity isn't very, very good indeed,
and your servers absolutely rock solid, replication can freeze and die, and
leave your servers out of sync horribly.

Also, if MySQL dies on either server, you are in the same situation, so it is
probably worth considering taking a dump of your MySQL databases into flat
files on a regular basis, and also keeping the original definitions of your
file classes in your sendmail.cf.

Bear in mind that for any one class/map, you can have multiple different 
definitions, which can both be used. Although the one you define first is
(probably) the one which will take precedence.

CHANGES

8.12.8 Kev Green, 27/03/2003.
   - Trying to track down bugs in the untested 8.12.8 release.
   - Adding in bug trapping on both execution of queries, and the retrieval
     of their respective results, for sanity.
   - Documentation update, and correction.
8.12.8 Kev Green, 04/03/2003.
   - In response to CERT CA-2003-07 sendmail security advisory, checked the
     patch against sendmail 8.12.8, which worked okay. 
   - Added some elementary m4 FEATURE() files to the sendmail-cf package.
   - Made sure that the /etc/mail/sqlmail.conf file was actually included
     in the RPM.
   - Applied pgsql maps patch from Jonathan Yarden <jyarden@bluegrass.net>.
   - Various documentation updates.
   - Rebuilt RPM's with the new patch version(s)
   - Added missing BuildPrereq, and Prereq directives.
   - Started trying to get rid of those sys_nerr, etc. warnings.

8.12.5 Doc file updates, Kev Green, 22/10/2002.
   - Added information on m4 configuration, building the patch, debugging,
     maps, and general advice section.

0.2.3b Doc file submitted to Sourceforge by Paul Khavine, 24/07/1999.