1 Topic by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Topic: Quarantine release doesn't work after iRedAdmin-Pro upgrade

==== Required information ====
- iRedMail version: 0.8.7
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Linux/BSD distribution name and version: Centos 6.5, Linux 3.14.4-x86_64-linode40 on x86_64
- Related log if you're reporting an issue: maillog
====

After I recently upgraded to Iredadmin-pro v1.4.2, my quarantine (and possibly whitelisting) don't work.

You should be aware that as part of this process I also updated postgresql to v9.1 doing the dump and restore method, from 8.4.

Whenever I try releasing an email from quarantine, it fails with these maillog entries:

May 21 20:44:00 mail amavis[19169]: (rel-aQ2mMS59szoQ) Quarantined message release (miscategorized): aQ2mMS59szoQ <> ->
May 21 20:44:00 mail amavis[19169]: (rel-aQ2mMS59szoQ) Quarantine release aQ2mMS59szoQ: missing X-Quarantine-ID; missing X-Envelope-From or Return-Path; missing X-Envelope-To
May 21 20:44:00 mail amavis[19169]: (rel-aQ2mMS59szoQ) WARN: dispatch_from_quarantine failed: Stored quarantine ID '' does not match requested ID 'aQ2mMS59szoQ' at /usr/sbin/amavisd line 10502, <GEN26> line 35.

Every single time, for any quarantine type (banned, spam, virus, etc..), and every message. They seem to just disappear and I have yet to find any evidence that they actually wind up somewhere.

Now, also, I tried to whitelist an entire sender domain (@domain.org) and messages from there where still being quarantined. I guess this means that whitelist is only for policyd(cluebringer) purposes? Or am I missing something / having an issue?

Thanks. Your assistance is greatly appreciated. This is causing me some grief as I need to get through some normally banned attachments. Thanks again.

- Fabian S.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

Looks like mail message stored in SQL database is incomplete.

*) Was the email quarantined before you upgraded to PostgreSQL 9.1?
*) Does releasing/deleting newly quarantined email work?

3 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

ZhangHuangbin wrote:

Looks like mail message stored in SQL database is incomplete.

*) Was the email quarantined before you upgraded to PostgreSQL 9.1?
*) Does releasing/deleting newly quarantined email work?

* No it was not.

* deleting continues to work fine it seems.Releasing does not work at all, including now, since the upgrade it seems.

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

Can you find any record in SQL table "amavisd.msgs" which has "mail_id=rel-aQ2mMS59szoQ" or "mail_id= aQ2mMS59szoQ"?
MAYBE SQL records were not completed restored/imported.

5 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

no they don't exist.

6 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

I have a theory; when i upgraded to postgresql v9.1, i left 8.4 in place but shutdown. i DID NOT update / create any symlinks for the postgresql utilities / command line stuff to make sure everything pointed to the new v9.1 files. i did notice that v9.1 created all new directories for itself and its files under a subfolders of '9.1'.

Could amavisd / any other components on my server be pointing at and using the wrong version of postgresql CLI utilities / binaries?

- Fabian S.

7 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

fsantiago06111979 wrote:

no they don't exist.

Please allow me to explain how Amavisd stores quarantined message:

1: All basic info (sender, recipient, size, etc, but not mail header and body) of in/out emails will be stored in table "amavisd.msgs" and "amavisd.msgrcpt". Every record will be assigned a unique id stored in column "msgs.mail_id", and a secret id in column "msgs.secret_id".

2: Mail header and body of quarantined message will be stored in table "amavisd.quarantine". It has the same "mail_id" as the one stored in "amavisd.msgs", so that you know which one it is. Note: depends on the mail size, one message may be splitted into several records.

3: if you concatenate all records stored in "amavisd.quarantine" which has the same mail_id, then it's a complete mail contains mail header and body. iRedAdmin-Pro parses it and let you see the decoded mail header and body.

4: To release a quarantined mail, Amavisd needs two values: msgs.mail_id and msgs.secret_id. Actually, only msgs.mail_id is required, but iRedMail configures Amavisd to require "msgs.secret_id".

In your case, Amavisd gets the mail_id stored in quarantined message, but it cannot find it in "amavisd.msgs", of course no "amavisd.secret_id" too. So it cannot release it.

Hope it's clear now.

8 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

That makes perfect sense but why is that happening to me is what I'm trying to figure out. I'm trying my idea now of fixing symlinks to point to v9.1 files.

9 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

for postgresql that is.

10 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

no, that did not solve it either. where else can i look or what can i do?

11 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

i think it's caused by incomplete SQL records, i have no idea yet.

12 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

ZhangHuangbin wrote:

i think it's caused by incomplete SQL records, i have no idea yet.

Ok thanks. I'll keep poking around myself too.

13 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

ok i've been digging through my database and all the required values in all the necessary tables / columns exist for a currently quarantined message. so the information is there yet it keeps failing to release. so now what? seems like amavisd / iredadmin-pro is having trouble talking to postgresql.

i'll keep looking around.

14 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

Is it possible to let me login to your server for further debug? I need to debug iRedAdmin-Pro to see how it works ON YOUR SERVER.

15 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

when i look in the quarantine table, each record begins with:

X-Envelope-To: \012X-Envelope-To-Blocked: \012X-Quarantine-ID: \

and the amavisd logs complain about lack of a quarantine-ID value (I upped logging to level 5 on amavisd):

Stored quarantine ID '' does not match requested ID 'PTW7adVs0_ye'

so could it be that a value isn't being stored correctly?

also, when i look phppgadmin, the amavisd role is the only one who's permissions CREATE & USAGE have asterisks next to YES. no other role appears that way. could it be permissions?

thanks.

- Fabian S.

16 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

yes i can let you login. i just saw your comment. how would you like to proceed?

17 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

here are some more relevant entries from the maillog with amavisd logging set at level 5:

May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) preprocess_policy_query: opening in sql: 7b8Yv0Jxo0Zs
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) preprocess_policy_query: missing partition_tag in request, fetching msgs record for mail_id=7b8Yv0Jxo0Zs
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) sql begin, nontransaction
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) Connecting to SQL database server
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) connect_to_sql: trying 'DBI:Pg:database=amavisd;host=127.0.0.1;port=5432'
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) connect_to_sql: 'DBI:Pg:database=amavisd;host=127.0.0.1;port=5432' succeeded
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) sql: preparing and executing (1 args): SELECT partition_tag FROM msgs WHERE mail_id=?
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) release: got msgs record for mail_id=7b8Yv0Jxo0Zs: 0
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) release: opening mail_id=7b8Yv0Jxo0Zs, partition_tag=0
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) sql begin, nontransaction
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) sql: preparing and executing (2 args): SELECT mail_text FROM quarantine WHERE partition_tag=? AND mail_id=? ORDER BY chunk_ind
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) Amavis::IO::SQL::open r drv=Pg (SELECT mail_text FROM quarantine WHERE partition_tag=? AND mail_id=? ORDER BY chunk_ind); key=7b8Yv0Jxo0Zs, p_tag=0, s:
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) Request: release 7b8Yv0Jxo0Zs /var/spool/amavisd/tmp/amavis-20140522T105726-13388-9zpfpicT:  ->
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) Amavis::IO::SQL::seek mode=r, pos=0
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) msg_from_quarantine: releasing 7b8Yv0Jxo0Zs
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) Amavis::IO::SQL::getline, chunk 1, pos 0
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) Amavis::IO::SQL::getline, chunk 1, pos 3868
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) Quarantined message release (miscategorized): 7b8Yv0Jxo0Zs <> ->
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) Quarantine release 7b8Yv0Jxo0Zs: missing X-Quarantine-ID; missing X-Envelope-From or Return-Path; missing X-Envelope-To
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) WARN: dispatch_from_quarantine failed: Stored quarantine ID '' does not match requested ID '7b8Yv0Jxo0Zs' at /usr/sbin/amavisd line 10502, <GEN27> line 5.
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) TempDir::DESTROY called
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) TempDir removal: empty tempdir is being removed: /var/spool/amavisd/tmp/amavis-20140522T105726-13388-9zpfpicT
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) rmdir_recursively: /var/spool/amavisd/tmp/amavis-20140522T105726-13388-9zpfpicT, excl=
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) size: 0, TIMING [total 20 ms] - got data: 0 (0%)0, mkdir tempdir: 2 (10%)10, sql-connect: 11 (55%)65, rmdir: 7 (35%)99, rundown: 0 (1%)100
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) switch_to_client_time 480 s, receiving AM.PDP data
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) get_deadline switch_to_my_time(end of AM.PDP session) - deadline in 480.0 s, set to 336.000 s
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) prolong_timer switch_to_my_time(end of AM.PDP session): timer 336, was 480, deadline in 480.0 s
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) exiting process_request
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) idle_proc, bye: was busy, 31.5 ms, total idle 0.000 s, busy 0.031 s
May 22 10:57:26 mail amavis[13388]: (rel-7b8Yv0Jxo0Zs) load: 100 %, total idle 0.000 s, busy 0.031 s

18 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

Looks like Amavisd didn't store quarantine id in quarantined mail header 'X-Quarantine-ID'. Could you please track log of storing quarantined mail?

Did you change any Amavisd setting?

19 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

no i didn't; only to increase logging level today. i will try looking at logs.

20 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

nothing in the logs screams at me.

21 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

are you testing against amavisd-new v2.8.0 or something different? That's my version in case that makes a difference.

22 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

I'm looking at my pgsql v8.4 backup file and when it creates the msgs table, it did this:

CREATE TABLE msgs (
    partition_tag integer DEFAULT 0 NOT NULL,
    mail_id bytea NOT NULL,
    secret_id bytea DEFAULT ''::bytea,
    am_id character varying(20) NOT NULL,
    time_num integer NOT NULL,
    time_iso timestamp with time zone NOT NULL,
    sid integer NOT NULL,
    policy character varying(255) DEFAULT ''::character varying,
    client_addr character varying(255) DEFAULT ''::character varying,
    size integer NOT NULL,
    originating character(1) DEFAULT ' '::bpchar NOT NULL,
    content character(1),
    quar_type character(1),
    quar_loc character varying(255) DEFAULT ''::character varying,
    dsn_sent character(1),
    spam_level real,
    message_id character varying(255) DEFAULT ''::character varying,
    from_addr character varying(255) DEFAULT ''::character varying,
    subject character varying(255) DEFAULT ''::character varying,
    host character varying(255) NOT NULL,
    CONSTRAINT msgs_sid_check CHECK ((sid >= 0)),
    CONSTRAINT msgs_size_check CHECK ((size >= 0)),
    CONSTRAINT msgs_time_num_check CHECK ((time_num >= 0))
);


and for the quarantine table:

CREATE TABLE quarantine (
    partition_tag integer DEFAULT 0 NOT NULL,
    mail_id bytea NOT NULL,
    chunk_ind integer NOT NULL,
    mail_text bytea NOT NULL,
    CONSTRAINT quarantine_chunk_ind_check CHECK ((chunk_ind >= 0))
);


do they look right to the best of your knowledge? maybe it imported something wrong, or the original backup exported something wrong. IDK. thanks.

- Fabian S.

23 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

Both SQL tables are correct.

fsantiago06111979 wrote:

are you testing against amavisd-new v2.8.0 or something different? That's my version in case that makes a difference.

It should work with Amavisd-new-2.6.x, 2.7.x, 2.8.x.

24 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

Looks like Amavisd didn't store quarantine id in quarantined mail header 'X-Quarantine-ID'. Did you please track SQL log of Amavisd storing quarantined mail?

25 Reply by fsantiago06111979

  • fsantiago06111979
  • Member
  • Offline
  • Registered: 2014-05-04
  • Posts: 229

Re: Quarantine release doesn't work after iRedAdmin-Pro upgrade

ZhangHuangbin wrote:

Looks like Amavisd didn't store quarantine id in quarantined mail header 'X-Quarantine-ID'. Did you please track SQL log of Amavisd storing quarantined mail?

I tried watching it. I didn't see anything obvious. I will attach the log ASAP. Thanks.