qmail/MySQL
The questions in this FAQ appear in neither alphabetical nor chronological nor indeed any sort of order...
Why was this patch developed?
I made this patch when I was working at Zen Internet. We wanted to use qmail with MySQL and I ... how shall I put this? ... didn't agree with takeshi's way of thinking.
Because I'm a good Open Source citizen I released the patch for people to do with as they see fit. Thus far no one has seen fit.
How actively is it developed?
It isn't. I've got other things to do and it works fine for me. Until I hear from my old boss that something's gone horribly wrong or I experience something going horribly wrong there isn't much chance of me working on it.
There are issues. Issues which from my position are minor. For example the infamous control/sqlserver world-readability thing. My setup doesn't have user accounts on my mail server so I don't worry about this. If you want to use this patch on a box which has user accounts then get the source and hack away. I've heard rumours that the patched qmail-smtpd crashes out when run under softlimit. I don't use softlimit (maybe I should). If you do then get the source and fix it.
There are also things on the TODO list that it would be interesting to play with. I had a very interesting discussion with someone about using shared memory to share a single database connection instead of having all the different parts of the qmail system make their own. That would be worth a try but it's not going to happen.
I know this is the wrong attitude on my behalf but I simply don't have time. I'm just too busy.
From time to time I'll do something and release it. I hope to get the FreeBSD incompatibilities with the later versions of the patch fixed (going back to the older ones doesn't lose you very much functionality) but who knows. This is open-source software, folks. Do something with it. I'm an inexperienced C programmer. If I can get in Dan's head then so can you, and you can get in mine.
How do I use Mailbox delivery?
To deliver ALL users' mail to Mailboxes, this isn't a MySQL question: edit ~qmail/rc.
To deliver one specific user's mail to a Mialbox, put a line like the following in the alias table.
| alias table | |||
|---|---|---|---|
| username | alias | alias_username | alias_host |
| iain | / | home/iain/Mailbox | |
The important thing to remember is that when you are delivering to Mailboxes, Maildirs or programs you must put the first character of the delivery instruction (., / or |) alone in the alias_username column.
checkpassword can't connect to the database
Make sure that the conf-qmail file in the checkpassword source tree points to the correct qmail install directory. If conf-qmail contains the line /opt/qmail then checkpassword will look for the /opt/qmail/control/sqlserver file for its database connection parameters.
Note that the format of the sqlserver is far too strict. It will only parse name=value or name value pairs - no tabs, only one space. I'll fix this at some stage (unless someone else wants to).
How can I compile this on Solaris?
Grab hold of the Solaris binary distribution of MySQL or compile from source using gcc 2.95. Add the following
-lsocket -lnsl
to the end of the line in the Makefile that references libmysqlclient.a and recompile.
I keep getting "Temporary failure in qmail-lspawn." I'm using Solaris.
There was, prior to version 1.1.2, an issue that only occurs with Solaris whereby qmail-getpw will fail for addresses containing dashes (more specifically, auto_breaks). Because qmail-getpw fails, so does qmail-lspawn. Here's the fix:
From version 1.1.2 onwards
Just uncomment -DSOLARIS_STUPIDITY in the Makefile. You may want to read the discussion of this problem and perhaps try it out for yourself.
Prior to version 1.1.2
You will have to patch the source files yourself. Add the line
substdio_put(subfderr,"",1);
to qmail-getpw.c above the line that reads
if (connection) num = getpw_mysql(username, &uid, &gid);
DJB fans please note that that really is substdio_put() and not substdio_putsflush() or anything else. God knows why but Solaris just bombs out if it can't find a username matching the argument given to qmail-getpw if you don't add this line, regardless of whether the argument is actually valid as a username-extension pair. Neither Linux nor FreeBSD exhibit this stupid behaviour.
Example
imagine I have the following mailbox table
| mailbox table | ||||||
|---|---|---|---|---|---|---|
| username | uid | gid | home | password | password_type | suspended |
| iain | 800 | 100 | /home/mail/iain | **** | Password | N |
| iain-sanity | 800 | 100 | /home/mail/sanity | **** | Password | N |
and the following alias table
| alias table | |||
|---|---|---|---|
| username | alias | alias_username | alias_host |
| iain | slowlaris | . | /Maildir/ |
On a sensible operating system, you'd expect the following results
/bin/bash:~$ qmail-getpw iain iain800100/home/mail/iain /bin/bash:~$ qmail-getpw iain-sanity iain800100/home/mail/sanity /bin/bash:~$ qmail-getpw iain-slowlaris iain800100/home/mail/iain-slowlaris
but on Solaris, without making the change above, you get:
/bin/bash:~$ qmail-getpw iain iain800100/home/mail/iain /bin/bash:~$ qmail-getpw iain-sanity iain800100/home/mail/sanity /bin/bash:~$ qmail-getpw iain-slowlaris /bin/bash:~$
Notice how the iain-sanity user is OK, because it's a username with a dash, whereas the iain-slowlaris user, a real user with an extension that IS in the alias table, gets swallowed up.
Just try and guess how long it took me to figure this one out...
That didn't work.
Pretend you aren't using Solaris (believe me you'll feel better for it) and see the next question.
I keep getting "Temporary failure in qmail-lspawn." I'm not using Solaris.
Are you sure the MySQL server is running?
Yes I am sure it is running.
Yes but are you sure you are connecting to it properly? Set your MySQL
server to log all queries with --log=/some/path/mysql.log and
look for select queries from the user listed in control/sqlserver.
Technical notes
If you look through the qmail-lspawn.c source you will see that the above error occurs when qmail-lspawn is unable to fork() and run qmail-getpw. This might be because your system really cannot fork() (maybe you ran out of memory) or because qmail-getpw couldn't be exec()ed, for example. However it's probably because qmail-getpw didn't return success.
To try and track the problem down, try running qmail-getpw from the commandline yourself. Try running it under strace or truss. Make sure the uid under which qmail-getpw is supposed to run is capable of calling qmail-getpw. If you don't know which user this is, it will be the default qmailp. As root:
cd ~qmailp setuidgid qmailp bin/qmail-getpw alias
qmail-getpw should return immediately and print the details of the alias user. If it appears to hang you have a problem connecting to the database. Check control/sqlserver is readable to qmailp and that you can connect to MySQL and run selects on the qmail database using the username and password in that file.
Why no quota support?
If you use maildirs then it's trivial to find large messages and delete them. For this reason I decided quotas were unnecessary. Just look at bigfoot.com. Try and send a large email to them and watch it bounce around. Not helpful.
Of course there are valid reasons for quotas sometimes. Hack them in if you want. I don't.
I see "alert: out of memory, sleeping..." in the log file and no deliveries happen
Make sure the control/sqlserver file exists and is readable by the qmail users. And see the next question.
The control/sqlserver file doesn't work
I know, I know, the code that parses control/sqlserver is pants. You need to make sure that the lines are of the form key value or key=value. That's one space OR one equals. Nothing else. Sorry.
The control/sqlserver file is insecure - how do I fix this?
Yes, the control/sqlserver file IS insecure. It needs to be readable by the qmaild, qmaill, qmailp and qmails users but since these users are members of two different groups the file is left world-readable by default.
A better solution is to put these users into a separate group and give that group read permissions to the file. Alternatively, if you're using an OS that supports ACLs (Solaris, FreeBSD with TrustedBSD patches and Linux with a -acl kernel or trustees spring to mind) then you can use those to grant read access to the file to only those users. I won't provide the commands necessary to do this because ACLs are a complicated beast and beyond the scope of this document. Not because I can't remember how.
What are the consequences of not modifying that file's permissions?
Anyone who can read the control/sqlserver file can learn the password for the user you use to connect to the qmail database. That means he can read your POP users' passwords and expand your alias definitions (although you can do that anyway with qmail-local).
If the user you name in the file has UPDATE, INSERT or DELETE privileges on the database our hypothetical bad person could screw up your database. But you don't need me to tell you that a user who only needs SELECT privileges should only have SELECT privileges...
So you knew the control/sqlserver file was insecure - what's your excuse?
My excuse is that this code is intended for people running pop toasters who don't have user accounts. No user accounts = no one messing about in your filesystem.
No it's not a very good excuse. Please refer to the second question in this FAQ.
How can I set up a virtual catchall?
If you put user, @, [blank], domain into virtual then you'll need to set up a catchall alias in the alias table. You can't have a blank entry (user, blank, blank, blank) because that would be interpreted as an empty dot-qmail file. Versions of the patches greater than 1.0.4 will spit out a warning if they find such bogus entries. The proper solution is to set alias_username to . and alias_hostname to /Maildir/ (or /Mailbox).
| virtual table | |||
|---|---|---|---|
| username | ext | virtual_username | virtual_host |
| virtual | @ | virtual.com | |
| alias table | |||
|---|---|---|---|
| username | alias | alias_username | alias_host |
| virtual | @ | . | /Maildir/ |
Note that prior to version 1.1.2 of the patches you needed to have a blank entry for alias in the above table!
Messages to virtual domains bounce even though they're correctly set up
If you get a bounce message saying "although I'm listed as a best-preference MX or A for that host, it isn't in my control/locals file, so I don't treat it as local. (#5.4.6)" for some virtual domain, check that the stuff on the left of the @ forms a valid address.
Example, if you've got an entry in virtual with the username being sales and the domain being sillydomain.com then mail for sales@sillydomain.com will be delivered but anything else addressed to sillydomain.com will bounce with this message. This can be good if you don't want spam addressed to made up usernames being delivered. If you want mail to come through, set up a default entry in the virtual table.
I can't read mail by POP3: I get "this user has no $HOME/Maildir"
Check the permissions on the user's Maildir. It should be owned by the uid.gid that appears in the mailbox table. Remember that by the time qmail-pop3d, which spits out this error message, runs, it is running as the user, so you need to make sure that the whole directory structure is executable to that uid.gid.
You can test this with:
/bin/bash:~$ ls -ld /home drwxr-xr-x 14 root wheel 1024 Jan 7 14:08 /home /bin/bash:~$ ls -ld /home/mail drwx--x--x 5 virtual mailuser 512 Jan 7 14:08 /home/mail /bin/bash:~$ ls -ld /home/mail/dmp drwx------ 3 virtual mailuser 512 Jan 7 14:10 /home/mail/dmp /bin/bash:~$ ls -ld /home/mail/dmp/Maildir drwx------ 3 virtual mailuser 512 Jan 7 14:10 /home/mail/dmp/Maildir
Another useful diagnostic is strace on Linux or truss on Solaris or BSD and friends:
strace -f tcpserver 0 110 qmail-popup test checkpassword qmail-pop3d Maildir
How do I forward user@domain to an external address
The setup below sends all mail for user@virtual.com to popaccount@hotmail.com.
From version 1.1.3 onwards
This is NOT recommended - magic forwarding has a bug which can cause mail loops if incorrectly configured.
Uncomment -DMAGIC_FORWARDING in the Makefile and rebuild qmail. Then set up your virtual table as follows:
| virtual table | |||
|---|---|---|---|
| username | ext | virtual_username | virtual_host |
| forward | popaccount@hotmail.com | user | virtual.com |
If you do this, DO NOT create a user named forward. qmail will recognise the "username" forward and deliver mail to whatever is in the ext field.
You MUST be aware that no checking is done on the remote address. If you make a mistake adding entries to this table you might end up with a loop. Either be very careful and test your configuration thoroughly or use the "old" method described below.
I must declare an interest and say that I have used MAGIC FORWARDING on production and continue to do so. However, the fact that the code is buggy and could potentially introduce a loop bothers me. This is why the option is disabled by default and this is why I recommend you NOT to use it.
Prior to version 1.1.3
| virtual table | |||
|---|---|---|---|
| username | ext | virtual_username | virtual_host |
| forward | user | virtual.com | |
| alias table | |||
|---|---|---|---|
| username | alias | alias_username | alias_host |
| forward | address | external.com | |
Note that unless you use MAGIC_FORWARDING you need to set up the forward user with a Maildir/Mailbox and entry in the mailbox table as usual.
checkpassword always refuses to authenticate my users - I've checked the directory permissions
From version 1.1.2 onwards
Check the users' password_type in the mailbox table. If the password is in plain text, password type should be "Password". If the password is in crypt(3) format, password type should be "Crypt-Password". If the password is in MySQL scrambled format , password type should be "MySQL-Password".
Prior to version 1.1.2.
Make sure you've compiled checkpassword with the correct password type. If you want to store your passwords in cleartext in the database, set -DO_USE_PLAINTEXT_PASSWORDS in the Makefile. If you want to store your passwords in evil Unix crypt(3) format, set -DO_USE_CRYPT_PASSWORDS. If you want to use MySQL hashed passwords, use the default -DO_USE_MYSQL_PASSWORDS.
Don't forget to unlink getpw_mysql.o before remaking if you do change the makefile.
I upgraded to FUNKY QUERIES and now none of my virtual domain catchalls work!
Run the following query:
update virtual set virtual_username='%' where virtual_username='';
If you don't understand why this fixes it you shouldn't be using FUNKY QUERIES. Comment them out in the Makefile and your virtual domains will work again.
I get "MySQL misconfiguration: <some nasty sounding message>" in the logs!
This is to tell you that you did something silly like missing out a username in a table where you need to have a username. If the problem was in the alias table your message has been eaten. If it was in the virtual table the message has probably been requeued although it might have been sent out to your upstream relay if you've set your server up like that.
I wasn't sure what to do if such an error was detected and since no one answered my request for comments I decided to leave it like this so at least administrators who read their log files from time to time (this is good) get a chance to fix it before the mail bounces.
I want usernames longer than 32 characters
Edit qmail-getpw.c and change the GETPW_USERLEN #define to a more appropriate number. You will also need to fix the MySQL tables. At least increase the size of the username column in each table. To set the username length to 128, for example:
alter table mailbox change username username varchar(128) not null; alter table alias change username username varchar(128) not null; alter table virtual change username username varchar(128) not null;
Or change the mysqldump if you haven't already created your tables.
You will probably also want to increase the size of other columns too. home, alias, ext, alias_username and virtual_username could, depending on your setup, all take values at least as long as your longest username.
When I run qmail-getpw it hangs and then prints out details for the alias user
Sounds like qmail-getpw is not connecting to the database.
When I run qmail-getpw it doesn't hang but prints out details for alias yet I have the user I'm querying in my mailbox table!
Make sure that the uid and gid of the user's home directory match the values in the mailbox table.
Are you going to [insert desired functionality]?
The patches are not in active development.
Did Susan Leppard's mother know Buzz Meeks was lying dead under her house?
Quite possibly. Or maybe she's just insane.