Help appreciated with sa_userconf_maps and sa_username_maps

[Marc Stürmer]

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 ma...@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 us...@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

[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

> To: amavis...@amavis.org


To: ma...@marc-stuermer.de
amavis...@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.

[Rob Sterenborg (lists)]

On 11/20/2012 10:19 AM, Marc Stürmer wrote:
> 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 ma...@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:' =

I tried sa_userconf once and it didn't work for me.
Now I'm just using sa_username (sa_userconf=null) for each Amavis policy
I need, which works fine. (I'm using both an Amavis and SA config in
MySQL though..)


--
Rob