Help appreciated with sa_userconf_maps and sa_username_maps

Steve Scotter amavis-users at
Tue Nov 20 21:57:17 CET 2012

I can't comment on your first two questions, but  my technique while developing my recent Amavis / SpamAssassin / Postfix / Dovecot installation was three fold.

1) Enable logging of MySQL queries...

I initially had MySQL log to a file, but the formats not very easy to read so I looked into and converted to logging to table. Logging to SQL gives you the ability filter by date/time, user/host, command type as well as the SQL command its self. The general_log_file line isn't required in my setup any more, its just there for historical reasons.

** WARNING ** Enabling logging on a busy system will quickly fill up your disk. My MySQL server can log about 5GB a day. Also, there is also a big IO performance hit when enabling MySQL logging.

After trial and error I've settled on executing "TRUNCATE TABLE general_log" on a nightly basis to keep my logging under control. I leave my slow_query table along as it seldom gets anything, but its handy to keep an eye on it in case you do have a query that takes a long time.

Further food for thought

2) Obviously best practice anyway, but I made every application use a different mysql username. This allows me to filter my general_log by application very quickly.

3) I added comment's to the SQL commands my application was using, if the application allowed me to alter the SQL commands the application used. 
For example, here's my for postfix.

SELECT domain AS virtual FROM postfix_virtual_domains WHERE domain='%s' AND active = 'y' /* */

Good luck!


-------- Original Message --------
Subject: Help appreciated with sa_userconf_maps and sa_username_maps (20-Nov-2012 9:20)
From:    Marc Stürmer <mail at>
To:      amavis-users at

> Hello out there!
> Currently I am tuning my local amavisd-new installation (v. 2.7.2) in 
> conjunction with Postfix on a modest Gentoo box.
> My aim is that amavisd-new should honor the sauserprefs of local users 
> as much as possible.
> All users which have local user prefs for Spamassassin are leaving 
> entries like mail at in the database. Connectivity is all setup 
> and working.
> So far, so good.
> My questions are now:
> 1) Is there still the bug in sa_userconf_maps that .* does not work and 
> I need to enable domains directly, e.g. like '.mail.domain' => 'sql:' =
> 2) I would like some good documentation/pointers to read for regexps in 
> sa_username_maps. It should match the local recipient adress of the mail 
> as user for SA userprefs, meaning user at mail.domain is needed as 
> identifier for SQL lookups. When I hardwired in one user it worked like 
> it should, but I need a more flexible solution since I am not the only 
> user on that system.
> BTW, is there any good way to debug the SQL queries amavisd does? I 
> raised log level to 5, also ran it with "amavisd debug-sa", but was not 
> really able to find something I could use for that kind of purposes - or 
> I am just blind. Whatever.
> Thanks in advance!
> Marc
> To: amavis-users at

To: mail at
    amavis-users at

This email is for the use of the intended recipient(s) only. If you have received this email in error, please notify the sender immediately and then delete it. 
If you are not the intended recipient, you must not keep, use, disclose, copy or distribute this email without the author’s prior permission. 
We have taken precautions to minimise the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message.
We cannot accept liability for any loss or damage caused by software viruses.
The information contained in this communication may be confidential and may be subject to the attorney-client privilege. 
If you are the intended recipient and you do not wish to receive similar electronic messages from us in future then please respond to the sender to this effect.

More information about the amavis-users mailing list