1 Topic by sergiocesar

  • sergiocesar
  • Member
  • Offline
  • Registered: 2014-04-28
  • Posts: 94

Topic: manage spam storage in sql

==== Required information ====
- iRedMail version (0.9.2):
- Linux/BSD distribution name and version: centos 6.6
- Store mail accounts in which backend (MySQL/
- Web server (Apache ):
- Manage mail accounts with iRedAdmin-Pro? no
- Related log if you're reporting an issue:
====
how can user manage spam stored in mysql?
or how to turn  it off so it does not fillup the disk with no possible way to manage.

----

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,165

Re: manage spam storage in sql

Please comment out @storage_sql_dsn in /etc/amavisd/amavisd.conf.

3 Reply by sergiocesar

  • sergiocesar
  • Member
  • Offline
  • Registered: 2014-04-28
  • Posts: 94

Re: manage spam storage in sql

ZhangHuangbin wrote:

Please comment out @storage_sql_dsn in /etc/amavisd/amavisd.conf.

How do I delete all email store in the database? as this being the default setting and there is not way to manage it it makes no sense keeping a copy in the database?

and I just realized that in the pro version a copy of all received and all send email is kept in the database, what is that for and who is to manage this?

thanks

4 Reply by ZhangHuangbin

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

Re: manage spam storage in sql

*) It doesn't store FULL email messages, it's just some basic mail info like sender, recipient, size, subject, not the mail body.

*) To delete these stored info, you can delete SQL records stored in table `amavisd.msgs`, `amavisd.msgrcpt`, `amavisd.quarantine`.

5 Reply by sergiocesar

  • sergiocesar
  • Member
  • Offline
  • Registered: 2014-04-28
  • Posts: 94

Re: manage spam storage in sql

ZhangHuangbin wrote:

*) It doesn't store FULL email messages, it's just some basic mail info like sender, recipient, size, subject, not the mail body.

If you dont store the full email so one could potentially release it
Please, what is the intent of storing this information?

in the latest pro version I see no entries at all via the admin page but there are still many entries in the tables,

6 Reply by ZhangHuangbin

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

Re: manage spam storage in sql

sergiocesar wrote:

If you dont store the full email so one could potentially release it
Please, what is the intent of storing this information?

Oh, you mean quarantined mails. For quarantined mails, yes, it stores FULL messages in sql table `amavisd.quarantine`.

sergiocesar wrote:

in the latest pro version I see no entries at all via the admin page but there are still many entries in the tables,

Excuse me, what entries in which tables?

7 Reply by sergiocesar

  • sergiocesar
  • Member
  • Offline
  • Registered: 2014-04-28
  • Posts: 94

Re: manage spam storage in sql

The data will be removed every day at 2:01AM, you can see the task in root's cron job, It removes old records:
# iRedMail: Cleanup Amavisd database
1   2   *   *   *   python /var/www/iredadmin/tools/cleanup_amavisd_db.py >/dev/null

It's used to track basic info of inbound/outbound emails, so that you can see top 10 senders/recipients in iRedAdmin-Pro "Dashboard" page, and Sent/Received Mails in iRedAdmin-Pro (Menu: System -> Mail Log -> Sent Mails (or "Received Mails")", and white/blacklists.

*** NOTE *** I found that: this is only available in the pro version.
The open source version does not have the /var/www/iredadmin/tools/cleanup_amavisd_db.py and the files required for the cron run to work and the database will grow forever if not manually cleaned up.
the cron entry does exists but it does nothing.

8 Reply by mir

  • mir
  • Member
  • Offline
  • Registered: 2014-05-30
  • Posts: 170

Re: manage spam storage in sql

The script is available on github. It has some dependencies but all dependent scripts are available from github too. I have it running here on the open source version without problems.

Log from last nights run:
Subject: Cron <root@foo.bar> python /opt/iredapd/tools/cleanup_amavisd_db.py >/dev/null
Date: Fri, 30 Oct 2015 01:30:01 +0100 (CET)

* [2015-10-30 01:30:01] Backend: ldap
* [2015-10-30 01:30:01] Delete quarantined mails which older than 7 days
* [2015-10-30 01:30:01] [-] Deleting 20 records
* [2015-10-30 01:30:01] Delete incoming/outgoing emails which older than 7 days
* [2015-10-30 01:30:01] [-] Deleting 100 records
* [2015-10-30 01:30:01] [-] Deleting 158 records
* [2015-10-30 01:30:01] Delete unreferenced records from table `msgrcpt`.
* [2015-10-30 01:30:01] Delete unreferenced records from table `quarantine`.
* [2015-10-30 01:30:01] - `msgs` table has 1761 records
* [2015-10-30 01:30:01] - `quarantine` table has 222 records
* [2015-10-30 01:30:01] Delete unreferenced records from table `maddr`.
* [2015-10-30 01:30:01] - `maddr` contains 432 addresses
* [2015-10-30 01:30:01] - `msgs` and `msgrcpt` have 388 addresses
* [2015-10-30 01:30:01] - Removed 44 unreferenced addresses in `maddr`
* [2015-10-30 01:30:01] Delete unreferenced records from table `mailaddr`.
* [2015-10-30 01:30:01] - `mailaddr` contains 750 addresses
* [2015-10-30 01:30:01] - `wblist` and `outbound_wblist` contain 750 addresses
* [2015-10-30 01:30:01] Log cleanup status.

9 Reply by ZhangHuangbin

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

Re: manage spam storage in sql

sergiocesar wrote:

*** NOTE *** I found that: this is only available in the pro version.
The open source version does not have the /var/www/iredadmin/tools/cleanup_amavisd_db.py and the files required for the cron run to work and the database will grow forever if not manually cleaned up.
the cron entry does exists but it does nothing.

This is not true, at least it was not true.
The recent iRedAdmin open source edition ships this script too.