Can you show me your mail.log when these e-mails come in? I need to see what Policy Banks they trigger, you're probably missing the new configuration I've devised for AmavisD-New which fixes this

I was going to do the dynamic version of that ZhangHuangbin

My method is dynamic and will make all current and future domains work. It is near the bottom of this thread : http://www.iredmail.org/forum/topic3809 … sdnew.html

Dear 7t3chguy,

as mentioned in my reply in this link, your solution is not ideal. still need to modify '$sql_select_policy' setting in Amavisd to make it return per-recipient policy (records in `amavisd.users` and `amavisd.policy`). Still no good idea how to improve default $sql_select_policy setting.

I can change it to per recipient easily, basically, change the view to only include users which should be treated by the myusers policy back, the query will check user and domain already

No problem, and remember, we're talking about per-recipient policy lookup, not per-sender.

*) When you have '@lookup_sql_dsn = ' enabled in Amavisd config file (/etc/amavisd/amavisd.conf on RHEL/CentOS, or /etc/amavis/conf.d/50-user on Debian/Ubuntu, ...), you have per-recipient policy lookup enabled.

*) Below is SQL command defined in /usr/sbin/amavisd (depends on the linux distribution you're running, the path or file name may be different, e.g. /usr/sbin/amavisd-new, or /usr/local/usr/sbin/amavisd, etc), used to query per-recipient policy:

  # The SQL select clause to fetch per-recipient policy settings.
  # The %k will be replaced by a comma-separated list of query addresses
  # for a recipient (e.g. a full address, domain only, catchall), %a will be
  # replaced by an exact recipient address (same as the first entry in %k,
  # suitable for pattern matching), %l by a full unmodified localpart, %u by
  # a lowercased username (a localpart without extension), %e by lowercased
  # addr extension (which includes a delimiter), and %d for lowercased domain.
  # Use ORDER if there is a chance that multiple records will match - the
  # first match wins (i.e. the first returned record). If field names are
  # not unique (e.g. 'id'), the later field overwrites the earlier in a hash
  # returned by lookup, which is why we use 'users.*, policy.*, users.id',
  # i.e. the id is repeated at the end.
  # This is a legacy variable for upwards compatibility, now only referenced
  # by the program through a %sql_clause entry 'sel_policy' - newer config
  # files may assign directly to $sql_clause{'sel_policy'} if preferred.

  $sql_select_policy =                                                               
    'SELECT users.*, policy.*, users.id'.                                            
    ' FROM users LEFT JOIN policy ON users.policy_id=policy.id'.                                                              
    ' WHERE users.email IN (%k) ORDER BY users.priority DESC';

Please do read comment lines above.

Amavisd stores per-recipient policy in SQL table `amavisd.policy` (iRedMail uses database 'amavisd', so we use this database name for example here), according to above SQL command, we can know Amavisd will query 2 tables to get per-recipient policy: users, policy.

What data does Amavisd require in these 2 tables?

1) table 'amavisd.policy': define some policy rules. for example, with 'policy.bypass_spam_checks=Y', Amavisd will bypass spam checking for recipient who link to this policy. with 'policy.bypass_virus_checks=Y', Amavisd will bypass virus checking for recipient who link to this policy.

2) table 'amavisd.users': If you want to define some per-recipient policy for a LOCAL user, you must insert info about this local user in this table, THEN link to a policy (defined in 'amavisd.policy' to this user). The most important columns are:

- users.id: a unique id
- users.email: full email address, or '@domain.com' for per-domain account, '@.' for a catch-all account
- users.policy_id: link to policy record defined in table 'amavisd.policy'.

So, if you want to query virtual mail domains, please do return policy data in your new SQL query.

Hope it helps a little.

@Zhang, when I try to untick "Prefix text [SPAM] to the subject of spam" in iRedAdmin Pro, it redirects me to ?msg=UPDATED, but when I go back onto the Spam Policy page, its still ticked.

Edit:
When its ticked and I hit SAVE, it removes the policy row, when its unticked and I hit save, it creates a policy row with Y in the spam_modifies_subj. But most importantly, nothing ever gets created in amavis.users so the MySQL Query never actually returns anything. I'm assuming what you meant to do is to not use policy groups, but have a separate policy per user? You'd need to modify the MySQL Query for that but it doesn't seem you do that.

and why is amavis.users.email of type VarBinary(255)?

Alright, after a lot of playing, the only way I could get the MySQL Working would be a really ugly Union Select which had a Select with 40+ Null Columns. The easiest way would be if iRedAdmin upon the creation of a domain, would add it to the AmavisD DB, same for User, into the Policy table, [@exampledomain.com / user@exampleuser.com]
(better if the user is only added if his settings are non-default, but domain should be added each time)

This shouldn't be too hard for you to implement?

The harder way to implement it would be to create a GUI within iRedAdmin-Pro to create Policy Sets, then on each User and Domain you could attach that User/Domain to a specific Policy Set. This would give you Full Control with less repeated data. Domains would still have to be added dynamically upon being created [Even in OSE]

I think I've solved it. One MySQL View.
iRedAdmin would just have to create a new row in the Policy Table with the policy_name as the e-mail address, or @domain of the rules it wants to change.
(This seems to be how iRedAdmin-Pro is currently handling it, which right now does nothing since your Policies don't have a relevant User Table Entry, this View and modified Select would just fix that)

Edit:
Does Amavisd need me to return an ID? I can just run the Join on the email instead of ID if it doesn't need an ID.
If it needs an ID, then for this to work, I'd suggest giving vmail.mailbox an id column [AI PK]

Would you mind sharing the SQL command used to create VIEW?

Per-recipient policy should have higher priority than per-domain policy. catch-all policy (@.) has the lowest priority.

Excuse me, what do you mean "policies don't have a revlvant user table entry"?

*) Policies are independent.
*) Relation between 'users' and 'policy' table is defined in 'users.policy_id' column. To define some policy for one recipient, you need to

1) create proper policy in 'policy' table;
2) add this recipient info in table 'users', and set the id (value of column 'policy.id') of the policy in 'users.policy' for this recipient.

Let me know if it's not explained clearly.

Yes. With default sql statement, it returns 'users.id'.

I still don't understand why you need an id column in `vmail.mailbox`.

If there's no policy defined for a recipient, just return NULL data of all columns.

Are you sure?
Currently, per-recipient policies can be defined in user profile page (per-user), domain profile page (per-domain), or under menu 'System -> Anti Spam -> Global spam policy' (global policy, lowest priority).

When you define a per-recipient policy, iRedAdmin-Pro will insert this account (user, domain, or catch-all account (@.)) to table 'users'. If not, it's a bug in iRedAdmin-Pro, i will try to reproduce this issue later.

Amavisd expects a unique id for recipient, and it should be defined in 'amavisd.users' table, because other sql tables (e.g. amavisd.wblist) will use this id. Remember, if you defined a policy for one user, table 'users' should contain info of this user (e.g. full email address, a unique id number).

That's what your VIEW should do. Must check whether recipient (full email address) or domain is local, if yes, return data (if no policy for this recipient, return NULL as value of all SQL columns (policy.* columns)).

@Zhang, could you please join me at irc.esper.net#iredmail