Help appreciated with sa_userconf_maps and sa_username_maps

[Steve Scotter]

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...
general_log=ON
general_log_file=/var/db/mysql/mysqld-queries.log
slow_query_log=ON
slow_query_log_file=/var/db/mysql/mysqld-slow-queries.log
log-output=TABLE
expire_logs_days=1

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 www.oreillynet.com/databases/blog/2007/05/hacking_mysql_table_logs.html

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 relay_domains.cf for postfix.

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

Good luck!

Steve

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

> 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 name.org 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 amavis.org


To: mail at marc-stuermer.de
    amavis-users at amavis.org


DISCLAIMER
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.