modoboa
amavis extension does not manage policies in a way compatible with amavisd-new's own documentation
From @tonioo on December 4, 2013 15:44
Originally created by Louis-Dominique Dubeau on 2012-09-06T12:34:53Z
Modoboa version: 0.9.2 Amavisd-new version: 2.7.1
The documentation regarding SQL usage provided with amavisd-new suggests that multiple records in the users table should point to the same record in the policy table. Said documentation is here:
http://www.ijs.si/software/amavisd/README.sql-mysql.txt
Recipes helping sysadmins setting up amavisd-new for SQL suggest populating the policy table with useful values. Here is a typical example:
INSERT INTO policy (id, policy_name, virus_lover, spam_lover, banned_files_lover, bad_header_lover, bypass_virus_checks, bypass_spam_checks, bypass_banned_checks, bypass_header_checks, spam_modifies_subj, spam_tag_level, spam_tag2_level, spam_kill_level) VALUES (1, 'Non-paying', 'N','N','N','N', 'Y','Y','Y','N', 'Y', 3.0, 7, 10), (2, 'Uncensored', 'Y','Y','Y','Y', 'N','N','N','N', 'N', 3.0, 999, 999), (3, 'Wants all spam','N','Y','N','N', 'N','N','N','N', 'Y', 3.0, 999, 999), (4, 'Wants viruses', 'Y','N','Y','Y', 'N','N','N','N', 'Y', 3.0, 6.9, 6.9), (5, 'Normal', 'N','N','N','N', 'N','N','N','N', 'Y', -999, 6.9, 9.0), (6, 'Trigger happy', 'N','N','N','N', 'N','N','N','N', 'Y', 3.0, 5, 5), (7, 'Permissive', 'N','N','N','Y', 'N','N','N','N', 'Y', 3.0, 10, 20);
If an administrator manages multiple domains and all these domains require a policy corresponding to the "Normal" policy, then the administrator would have all of these domains point to the "Normal" policy record. E.g.:
- @foo.com in the table users points to policy "Normal" (id 5) in the table policy.
- @bar.com in the table users points to policy "Normal" (id 5) in the table policy.
Presumably, the idea is that if administrator decides to change what "Normal" means, then he does not have to go through all domains one by one. He just changes the "Normal" record in the policy table.
However, assuming the setup above, let's assume that an administrator adds Modoboa to his setup and uses Modoboa to alter the policy of foo.com.
Expected result: Only the settings of foo.com should change.
Actual result: Modoboa also silently alters the settings of bar.com. There is nothing on screen indicating that what Modoboa is changing has an effect greater than foo.com. This is because Modoboa modifies the settings for foo.com by modifying the "Normal" record in the policy table. Thus, everything pointing to "Normal" is altered.
(I've considered whether filing this as a bug or a feature request. I've decided on a bug since the current behavior yields unexpected results.)
As an administrator what I would ideally want the amavis extension to do is handle the two tables --- users and policy --- in a way which is compatible with what the developers of amavisd-new intended. I should be able to:
- Create new policies. Delete policies. Edit policies. "Policies" here means "records in the policy table."
- Change the relationship between a user and a policy (e.g. get foo.com to use the 'Trigger happy' policy rather than 'Normal'.)
Copied from original issue: tonioo/modoboa#302
Posted by Antoine Nguyen on 2012-09-06T19:49:30Z
Hi,
I understand your point of view but I haven't designed the per-domain policy feature in this way.
Actually, Modoboa can't be used on an existing database (I mean for users and policies). My idea was :
- A user record is created for each domain
- Provide a default policy (id == 1) that doesn't define anything (use default values everywhere) : it is associated to each user record by default and should not be modified
- Provide a simple way to modify a subset of parameters (per-domain) : the first time this subset is modified, I create a new policy dedicated to that domain
It is how it currently works and I'd like to keep it simple, I didn't want to provide a simple and raw policy editor but maybe I'm wrong...
Antoine
Posted by Antoine Nguyen on 2012-09-10T08:12:53Z
Hi Louis-Dominique,
what do you think about my previous reply ?
Antoine
Posted by Louis-Dominique Dubeau on 2012-09-10T23:23:11Z
Antoine Nguyen wrote:
I understand your point of view but I haven't designed the per-domain policy feature in this way.
And that is the problem. It is very likely that sysadmins who might want to install modoboa already use the scheme suggested by the documentation that comes with amavisd-new. They'll have a users table and policy table already populated before they install modoboa.
I'm not sure whether I prefer the way suggested in amavisd-new's documentation or yours. However, for the benefit of sysadmins who already have amavisd-new working by the time they install modoboa and the amavis extension, I'd say that at the very least the documentation for modoboa's amavis extension should mention explicitly that it uses the scheme you described, and that this may clash with how a sysadmin has been managing the users and policy tables before installing modoboa. It would additionally be desirable for the amavis extension to test the structure of the users and policy tables to detect whether they are structured in a way that the extension can use.
Posted by Antoine Nguyen on 2012-09-12T16:08:02Z
Louis-Dominique Dubeau wrote:
And that is the problem. It is very likely that sysadmins who might want to install modoboa already use the scheme suggested by the documentation that comes with amavisd-new. They'll have a users table and policy table already populated before they install modoboa.
I agree, this is a real issue. I really didn't think users of existing solution (such as maia) would use Modoboa to handle policies and more so quickly. i need to find a way to fix that issue.
I'm not sure whether I prefer the way suggested in amavisd-new's documentation or yours. However, for the benefit of sysadmins who already have amavisd-new working by the time they install modoboa and the amavis extension, I'd say that at the very least the documentation for modoboa's amavis extension should mention explicitly that it uses the scheme you described, and that this may clash with how a sysadmin has been managing the users and policy tables before installing modoboa. It would additionally be desirable for the amavis extension to test the structure of the users and policy tables to detect whether they are structured in a way that the extension can use.
I can indeed update the documentation to let people know about that restriction. Reading your messages, I finally think it would be useful to implement a raw policy editor and then allow administrators to use existing policies for their domain(s). The big problem is I don't know the other softwares that provide this functionality. I'd be interested to have your feedback about how it works in maia.
Do you think you could help me on that subject ?
PS : are you a python developer ?
Posted by Louis-Dominique Dubeau on 2012-09-14T12:26:16Z
Antoine Nguyen wrote:
Louis-Dominique Dubeau wrote:
And that is the problem. It is very likely that sysadmins who might want to install modoboa already use the scheme suggested by the documentation that comes with amavisd-new. They'll have a users table and policy table already populated before they install modoboa.
I agree, this is a real issue. I really didn't think users of existing solution (such as maia) would use Modoboa to handle policies and more so quickly. i need to find a way to fix that issue.
I'd actually say that the fact I used Maia before really has no bearing on the case. When I used Maia, my entire mail system was managed by the hosting company I was using. When I moved to my new hosting company I had to install postfix, dovecot, amavisd-new, etc with the help of my hosting company's documentation and documentation from the various softwares involved. The installation order was roughly:
postfix + dovecot (using mysql for the mapping),
amavisd-new,
Modoboa,
switch amavisd-new to use SQL tables (by default, it does not),
turn on Modoboa's amavis extension
The order of the last two steps was enough to have me populate amavisd-new's tables in a way which differs from what Modoboa's amavis extension expects.
I can indeed update the documentation to let people know about that restriction. Reading your messages, I finally think it would be useful to implement a raw policy editor and then allow administrators to use existing policies for their domain(s). The big problem is I don't know the other softwares that provide this functionality. I'd be interested to have your feedback about how it works in maia.
I've never touched this specific functionality in Maia. My hosting company set some reasonable defaults and I did not have to mess with them.
Do you think you could help me on that subject ?
Gosh... hmm... this is a tough one, because I want to see Modoboa flourish but my time is severely limited.
PS : are you a python developer ?
Yes, but again time is an issue. I'm an academic. "Publish or perish" and all that stuff. I can submit intelligent bug reports but it is hard to be involved beyond that right now.
Thank you for listening, by the way. It is really frustrating when bug reports are dismissed out of hand.
Best, Louis
Posted by Antoine Nguyen on 2012-09-14T19:22:00Z
Louis-Dominique Dubeau wrote:
I'd actually say that the fact I used Maia before really has no bearing on the case. When I used Maia, my entire mail system was managed by the hosting company I was using. When I moved to my new hosting company I had to install postfix, dovecot, amavisd-new, etc with the help of my hosting company's documentation and documentation from the various softwares involved. The installation order was roughly:
postfix + dovecot (using mysql for the mapping),
amavisd-new,
Modoboa,
switch amavisd-new to use SQL tables (by default, it does not),
turn on Modoboa's amavis extension
The order of the last two steps was enough to have me populate amavisd-new's tables in a way which differs from what Modoboa's amavis extension expects.
Ok I understand now. In addition to the schema, you also applied the example queries used to populate the policy tables and the others.
I've never touched this specific functionality in Maia. My hosting company set some reasonable defaults and I did not have to mess with them.
Ok.
Gosh... hmm... this is a tough one, because I want to see Modoboa flourish but my time is severely limited.
PS : are you a python developer ?
Yes, but again time is an issue. I'm an academic. "Publish or perish" and all that stuff. I can submit intelligent bug reports but it is hard to be involved beyond that right now.
I can understand that. Anyway, reporting new issues or giving your point of view about the product is already a big contribution. Thanks for that :)
Thank you for listening, by the way. It is really frustrating when bug reports are dismissed out of hand.
You're welcome. I rarely see bug reports so complete as yours, it is a pleasure to handle them :)
Posted by Louis-Dominique Dubeau on 2012-10-05T15:03:37Z
This problem blew in my face today. For the reasons already given, I am sure that someone who follows in my footsteps will encounter the following error because their policy table will already be filled with the values that the amavisd-new folks suggest to put in there.
I tried the development version of modoboa. When I got to doing:
$ python manage syncdb --migrate
The command failed on amavis:0002_create_records. Unfortunately, this left the amavis database in a non-functional state, because the migration had already cleared the users table.
Error follows:
Syncing... Creating tables ... Installing custom SQL ... Installing indexes ... Installed 0 object(s) from 0 fixture(s) Migrating... Running migrations for lib: - Nothing to migrate. - Loading initial data for lib. Installed 0 object(s) from 0 fixture(s) Running migrations for admin: - Nothing to migrate. - Loading initial data for admin. Installed 2 object(s) from 1 fixture(s) Running migrations for limits: - Nothing to migrate. - Loading initial data for limits. Installed 0 object(s) from 0 fixture(s) Running migrations for postfix_autoreply: - Nothing to migrate. - Loading initial data for postfix_autoreply. Installed 0 object(s) from 0 fixture(s) Running migrations for amavis: - Migrating forwards to 0002_create_records. > amavis:0002_create_records - Migration 'amavis:0002_create_records' is marked for no-dry-run. ! Error found during real run of migration! Aborting. ! Since you have a database that does not support running ! schema-altering statements in transactions, we have had ! to leave it in an interim state between migrations. ! You *might* be able to recover with: (migration cannot be dry-run; cannot discover commands) ! The South developers regret this has happened, and would ! like to gently persuade you to consider a slightly ! easier-to-deal-with DBMS (one that supports DDL transactions) ! NOTE: The error which caused the migration to fail is further up. Error in migration: amavis:0002_create_records Traceback (most recent call last): File "manage.py", line 10, inexecute_from_command_line(sys.argv) File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 443, in execute_from_command_line utility.execute() File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 382, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 196, in run_from_argv self.execute(*args, **options.__dict__) File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 232, in execute output = self.handle(*args, **options) File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 371, in handle return self.handle_noargs(**options) File "/usr/local/lib/python2.7/dist-packages/South-0.7.6-py2.7.egg/south/management/commands/syncdb.py", line 99, in handle_noargs management.call_command('migrate', **options) File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 150, in call_command return klass.execute(*args, **defaults) File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 232, in execute output = self.handle(*args, **options) File "/usr/local/lib/python2.7/dist-packages/South-0.7.6-py2.7.egg/south/management/commands/migrate.py", line 108, in handle ignore_ghosts = ignore_ghosts, File "/usr/local/lib/python2.7/dist-packages/South-0.7.6-py2.7.egg/south/migration/__init__.py", line 213, in migrate_app success = migrator.migrate_many(target, workplan, database) File "/usr/local/lib/python2.7/dist-packages/South-0.7.6-py2.7.egg/south/migration/migrators.py", line 235, in migrate_many result = migrator.__class__.migrate_many(migrator, target, migrations, database) File "/usr/local/lib/python2.7/dist-packages/South-0.7.6-py2.7.egg/south/migration/migrators.py", line 310, in migrate_many result = self.migrate(migration, database) File "/usr/local/lib/python2.7/dist-packages/South-0.7.6-py2.7.egg/south/migration/migrators.py", line 133, in migrate result = self.run(migration) File "/usr/local/lib/python2.7/dist-packages/South-0.7.6-py2.7.egg/south/migration/migrators.py", line 107, in run return self.run_migration(migration) File "/usr/local/lib/python2.7/dist-packages/South-0.7.6-py2.7.egg/south/migration/migrators.py", line 81, in run_migration migration_function() File "/usr/local/lib/python2.7/dist-packages/South-0.7.6-py2.7.egg/south/migration/migrators.py", line 57, in return (lambda: direction(orm)) File "/usr/local/lib/python2.7/dist-packages/modoboa-0.9.2-py2.7.egg/modoboa/extensions/amavis/migrations/0002_create_records.py", line 15, in forwards spam_modifies_subj=None) File "/usr/local/lib/python2.7/dist-packages/django/db/models/manager.py", line 137, in create return self.get_query_set().create(**kwargs) File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 377, in create obj.save(force_insert=True, using=self.db) File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 463, in save self.save_base(using=using, force_insert=force_insert, force_update=force_update) File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 551, in save_base result = manager._insert([self], fields=fields, return_id=update_pk, using=using, raw=raw) File "/usr/local/lib/python2.7/dist-packages/django/db/models/manager.py", line 203, in _insert return insert_query(self.model, objs, fields, **kwargs) File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 1576, in insert_query return query.get_compiler(using=using).execute_sql(return_id) File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py", line 910, in execute_sql cursor.execute(sql, params) File "/usr/local/lib/python2.7/dist-packages/django/db/backends/util.py", line 40, in execute return self.cursor.execute(sql, params) File "/usr/local/lib/python2.7/dist-packages/django/db/backends/mysql/base.py", line 114, in execute return self.cursor.execute(query, args) File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue django.db.utils.IntegrityError: (1062, "Duplicate entry '1' for key 'PRIMARY'")
Posted by Antoine Nguyen on 2012-10-05T18:26:07Z
Unfortunately it is currently normal because I provide a default policy with an id set to 1.
I need to totally refactor this part and I don't think I'll do that for 0.9.3. I'm wondering what I should do : provide a raw policy editor and allow domains to use an existing policy or to define their own ?
Posted by Mike C on 2013-01-02T13:13:22Z
I'm facing the same problem, whats the status on this? Is there any workaround?
Thanks
Posted by Louis-Dominique Dubeau on 2013-01-02T13:51:55Z
My workaround is to ignore Modoboa's facilities for managing amavisd's policies. I don't need to change them often. When I do, I just start a SQL shell from the command line and alter the tables myself.
Posted by Antoine Nguyen on 2013-01-02T14:15:35Z
Mike C wrote:
I'm facing the same problem, whats the status on this? Is there any workaround?
Thanks
Hi Miguel,
I'll refactor this part for 1.1. How did you come to this situation ?
Posted by Mike C on 2013-01-02T15:41:22Z
Just running installing the first time on a Centos 5 box, the auto install failed to tun the sql script, I tried to run manually "syndb --migrate" and get a migration error for amavis:
python26 manage.py syncdb --migrate Syncing... Creating tables ... Installing custom SQL ... Installing indexes ... Installed 0 object(s) from 0 fixture(s) Migrating... Running migrations for lib:
-
Nothing to migrate.
- Loading initial data for lib. Installed 0 object(s) from 0 fixture(s) Running migrations for admin:
-
Nothing to migrate.
- Loading initial data for admin. Installed 2 object(s) from 1 fixture(s) Running migrations for limits:
-
Nothing to migrate.
- Loading initial data for limits. Installed 0 object(s) from 0 fixture(s) Running migrations for postfix_autoreply:
-
Nothing to migrate.
- Loading initial data for postfix_autoreply. Installed 0 object(s) from 0 fixture(s) Running migrations for amavis:
- Migrating forwards to 0002_create_records.
amavis:0002_create_records
- Migration 'amavis:0002_create_records' is marked for no-dry-run. ! Error found during real run of migration! Aborting.
! Since you have a database that does not support running ! schema-altering statements in transactions, we have had ! to leave it in an interim state between migrations.
! You might be able to recover with: (migration cannot be dry-run; cannot discover commands)
! The South developers regret this has happened, and would
! like to gently persuade you to consider a slightly
! easier-to-deal-with DBMS (one that supports DDL transactions)
! NOTE: The error which caused the migration to fail is further up.
Error in migration: amavis:0002_create_records
Traceback (most recent call last):
File "manage.py", line 10, in
Posted by Mike C on 2013-01-02T15:43:49Z
Sorry, maybe this is more readable:
@python26 manage.py syncdb --migrate Syncing... Creating tables ... Installing custom SQL ... Installing indexes ... Installed 0 object(s) from 0 fixture(s) Migrating... Running migrations for lib:
- Nothing to migrate.
- Loading initial data for lib. Installed 0 object(s) from 0 fixture(s) Running migrations for admin:
- Nothing to migrate.
- Loading initial data for admin. Installed 2 object(s) from 1 fixture(s) Running migrations for limits:
- Nothing to migrate.
- Loading initial data for limits. Installed 0 object(s) from 0 fixture(s) Running migrations for postfix_autoreply:
- Nothing to migrate.
- Loading initial data for postfix_autoreply. Installed 0 object(s) from 0 fixture(s) Running migrations for amavis:
- Migrating forwards to 0002_create_records.
amavis:0002_create_records
- Migration 'amavis:0002_create_records' is marked for no-dry-run. ! Error found during real run of migration! Aborting. ! Since you have a database that does not support running ! schema-altering statements in transactions, we have had ! to leave it in an interim state between migrations.
! You might be able to recover with: (migration cannot be dry-run; cannot discover commands)
! The South developers regret this has happened, and would
! like to gently persuade you to consider a slightly
! easier-to-deal-with DBMS (one that supports DDL transactions)
! NOTE: The error which caused the migration to fail is further up.
Error in migration: amavis:0002_create_records
Traceback (most recent call last):
File "manage.py", line 10, in
Posted by Antoine Nguyen on 2013-01-02T15:47:51Z
Mike C wrote:
django.db.utils.DatabaseError: (1054, "Unknown column 'sa_username' in 'field list'") <---- I notice this now... maybe this is the problem!?
Yes, it seems to be a different issue.
What's your amavis version ?
Posted by Mike C on 2013-01-02T16:48:06Z
Latest available trough the system packages manager (YUM)
amavisd-new.i386 2.6.6-2.el5.rf installed
Unfortunately 2.8 is not available trough yum, perhaps I'll compile it later, but I still need to do some research, my guess is that it won't be an easy task :)
Posted by Antoine Nguyen on 2013-01-02T17:41:41Z
Mike C wrote:
Latest available trough the system packages manager (YUM)
amavisd-new.i386 2.6.6-2.el5.rf installed
Unfortunately 2.8 is not available trough yum, perhaps I'll compile it later, but I still need to do some research, my guess is that it won't be an easy task :)
Indeed Modoboa is only compatible with amavis >= 2.7
You may like this thread: http://lists.amavis.org/pipermail/amavis-users/2012-December/002060.html
Posted by Mike C on 2013-01-02T17:44:01Z
This is Centos 5 though... the upgrade to Centos 6 require a clean install and honestly 5.8 is working fine.
I guess I'll have to compile it or try to use the SRPM specs from 2.6.
thanks for the help
Posted by Louis-Dominique Dubeau on 2013-02-06T20:08:22Z
Still causing problems with 0.9.4. (Yeah, I noticed the target version is 1.1.) Symptoms are the same as before. The workaround:
$ rename 's/$/.off/' /usr/local/lib/python2.7/dist-packages/modoboa/extensions/amavis/migrations/0002_create_records.py
(The rename script basically renames files by applying a perl substitution to them. The above command just adds ".off" to the file passed to it.)
If you do the above renaming before running the syncdb --migrate command, then the migration should happen without problem.
If you have done the migration before doing the renaming, then you'll get the exception mentioned earlier in this bug report and will now have an unusable amavis database. You will have to restore your amavis database from backup (or recreate it by hand if you do not have a backup). Do the script rename as shown above and reissue syncdb --migrate to make sure everything was migrated.