Mail Server:MySQL Authentication

From ArsLinuxWiki

Postifx w/ SASL + Courier IMAP w/ SSL + Maildrop + MySQL + SpamAssassin (Appendix)

Acknowledgments

This document is meant to be a continuation of Postifx w/ SASL + Courier IMAP w/ SSL + Maildrop + MySQL + SpamAssassin by Serge Stepanov. These are the troubleshooting steps I had to take to get a functional installation on my RedHat 9 machine. I highly recommend that you run completely through Serge's excellent set of instructions before continuing as I assume that you have completed a configuration like the one he outlines..

[edit]

Troubleshooting

user unknown. Command output: Invalid user specified

This is the error that I got in /var/log/maillog when I first completed the above HOWTO and tried telnetting to locahost, 25 and sending a test email to test@test.com. I took several steps to resolve it.

�

Enabled MySQL logging so that I could which queries were being executed. The MySQL config file my.cnf is located in /etc on my machine. I added the line

 log = /var/log/mysql/mysql.log

under the "[mysqld]" section, created the file /var/log/mysql/mysql.log with uid/gid mysql and restarted MySQL to enable logging.

�

At this point, I noticed that no queries were actually coming through to MySQL. That lead me to several mistakes in a /usr/local/courier/etc/authmysqlrc where I had specified incorrect table names and incorrect login info.
I also needed to add a record to the postfix_access table of the MySQL database to allow mail to be sent to the test@test.com user. The following SQL statement should allow mail to be sent to any local user.

 insert into postfix_access (source,access,type) values ("*","OK","recipient");

�

Once authmysqlrc is correctly configured and you've added the record to postfix_access, you should stop getting the above error

chdir Maildir: No such file or directory

This was the next error I got in /var/log/maillog when trying to send email to my test@test.com user once the "user unknown" error was fixed.

�

This was caused by my not setting the value MYSQL_MAILDIR_FIELD in /usr/local/courier/etc/authmysqlrc. Given the database schema we are using, MYSQL_MAILDIR should be set to "maildir".

Error: Input/output error and Check for proper operation and configuration of the File Access Monitor daemon (famd)

After fixing authmysqlrc to use the correct field for maildir, I got the above error in /var/log/maillog when trying to send to test@test.com.

�

This was caused by the famd daemon being disabled. On my RedHat 9 machine, famd runs through xinetd. I had to edit the file /etc/xinetd.d/sgi_fam so that the "disabled" value was set to "no" and restart xinetd.

No such file or directory

This was the next error I got in /var/log/maillog when trying to get mail delivered to test@test.com

�

The first step in fixing this problem was making sure that my test@test.com maildir was located in /home/vmail and that everything had a uid/gid of vmail.vmail.
You'll want to create the directory /home/vmail/test.com/test and the execute the command "/usr/local/courier/bin/maildirmake /home/vmail/test.com/test/Maildir" to create the actual maildir.
You'll also want to make sure that the entry in postfix_users is correct. Here's a list of some of the more critical values for the test@test.com user:

 email=test@test.com

 uid=520

 gid=520

 homedir=/home/vmail

 maildir=test.com/test/Maildir/

�

The uid & gid should be the same uid and gid of the vmail user you created earlier. Also make sure to add the trailing "/" on the maildir value. An example SQL statement for adding the test user would be:

 "insert into postfix_users (email,crypt,name,uid,gid,homedir,maildir) values

("test@test.com",encrypt("password"),520,520,"/home/vmail","test.com/test/Maildir/")"

�

Once you have the user entered into the database and the Maildir created, make sure that the entire /home/vmail directory has the correct permissions. Everything should have read-write-execute permissions for user and group and no permissions for other. Everything should also has uid and gid of vmail. This can be accomplished by running "chown -R vmail.vmail /home/vmail" and "chmod -R ug+rwx,o-rwx /home/vmail"
the above is wrong: simply change all permissions everytime you created a new maildirectory. execute "chown -R vmail.vmail /home/vmail" and "chmod 700 -R vmail".

Aliases not functioning

Once mail was being properly delivered to test@test.com, I set about converting my primary email account over to the MySQL database. This was pretty easy since I could just mimic the test@test.com setup but I was having trouble with the aliases.

�

My /etc/postfix/main.cf had the entry "alias_maps = mysql:/etc/postfix/mysql-aliases.cf" but also had the default entry of "alias_database = hash:/etc/aliases". I commented out the alias_database entry and added "virtual_alias_maps = mysql:/etc/postfix/mysql-aliases.cf" to tell Postfix to go to the postfix_alias table for all aliases.

Invalid SSL Certificate messages

Whenever I would try to connect the my mail server, I would get a warning message that the mail server domain didn't match the SSL Certificate domain (which was set to localhost.localdomain). I could still connect and get mail but having to clear this warning message everytime was getting annoying.

�

The certificates for Courier are kept in these 2 files

 /usr/local/courier/share/imapd.cnf

 /usr/local/courier/share/pop3d.cnf

and were created with the scripts

 /usr/local/courier/share/mkimapdcert

 /usr/local/courier/share/mkpop3dcert

Looking at the the two mkcert scripts reveals that they get their certificate information from the files

 /usr/local/courier/etc/imapd.cnf

 /usr/local/courier/etc/pop3d.cnf

I modified the two .cnf files with the appropriate information for my mail server, deleted the original .pem files and reran the 2 mkcert scripts. Here is the layout of the imapd.cnf file and the fields that need to be changed.

 RANDFILE = /usr/local/courier/share/imapd.rand

 [ req ]

 default_bits = 1024

 encrypt_key = yes

 distinguished_name = req_dn

 x509_extensions = cert_type

 prompt = no

 [ req_dn ]

 C=US (Your 2-character country code)

 ST=KY (Your 2-character locality or state)

 L=Louisville  (Your city name)

 O=example.com Mail Server (Organization name)

 OU=Automatically-generated IMAP SSL key

 CN=example.com (Domain name.  IMPORTANT:  This must be EXACTLY the same as the domain of your mailserver)

 emailAddress=email@example.com

 [ cert_type ]

 nsCertType = server

[edit]

Epilogue

After several days of struggling, I finally got a working setup (with a few pieces missing). I also decided I wanted to allow pop3 access as well as imap which meant starting /usr/local/courier/libexec/pop3d-ssl.rc as well as /usr/local/courier/libexec/imapd-ssl.rc.

Next on the list to fix are

SpamAssassin. I had SpamAssassin working with my original setup by using .forward files and Procmail for individual users. For some reason, this hasn't translated well to the new setup and I'm still trying to get that worked out.
SMTP Authentication. I've been experimenting with several SMTP auth options including Pop-Before-SMPT and SASL authentication through MySQL but still haven't had much luck.

Retrieved from "Mail Server:MySQL Authentication"

God soul science fiction humanity human life sense of life life after death time machine