1 Topic by abmpinoy

  • abmpinoy
  • Member
  • Offline
  • Registered: 2013-06-06
  • Posts: 1

Topic: MYSQL Remove mailbox from file system upon delete.

==== Required information ====
- iRedMail version:
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):
- Linux/BSD distribution name and version:
- Related log if you're reporting an issue:
================ Required information ====
- iRedMail version: .0.8.4
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): mysql
- Linux/BSD distribution name and version: centos 6.4
- Related log if you're reporting an issue:
====

We are close to pushing live our ired servers hosting +30,000.

Its looks to be a well known fact that iRedmail purposely does not delete a users actual mailbox from the harddrive, but only within the mysql DB.

I've read the reason and understand the reason for this, however, we're going to need to have those mailboxes deleted once you delete the user from the iredadmin panel. The iRed servers will be backed up, so that in the event an admin user accidentally deletes an account, the associated mailbox can be restored as needed.

The obvious concern is that we cannot litter the disk space with unused mailboxes and just leave files left on the harddrive. Postmasters will be able to create and remove mailboxes on their own, from a business standpoint, we cannot have regular(less experienced) staff members dropping down to shell deleting mailboxes manually, nor can we devote to much time to babying the tidiness of the file system. It's best that once that "Delete" user button has been pressed(by our staff member or a domain admin), that the physical mailbox on the hdd goes with it and/or have a logging mechanism that emails to out ticketing systems that x y  and z users have been removed from the database, make sure to remove their actual email mailboxes.

Remember this is 30,000 accounts with 4389 domain and corresponding domain admins which can obviously add and delete users at their will, managing the file system properly is critical.

It looks like I should be able to sneak in a mysql query that first locates the mailbox dir and deletes it, then proceeds with the rest of its work... in "/libs/mysql/user.py"

We'll need something very similar to the LDAP solution found here
iredmail.org/forum/topic2853-iredadminpro-support-remove-mailboxes-when-deleting-user.html

I will say that I lack python experience, however I can write it, more over I can read and understand it.

Assistance will be of great help, as this is a major issue with this amount of users.

Thanks

----

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

Re: MYSQL Remove mailbox from file system upon delete.

abmpinoy wrote:

Its looks to be a well known fact that iRedmail purposely does not delete a users actual mailbox from the harddrive, but only within the mysql DB.

iRedAdmin-Pro doesn't delete mailbox from hard driver, with all 3 backends (OpenLDAP, MySQL, PostgreSQL).

How about log the maildir path of deleted user in SQL database? Then you can delete it with a shell script, and choose when to delete it.

3 Reply by amora

  • amora
  • Member
  • Offline
  • Registered: 2013-04-09
  • Posts: 58

Re: MYSQL Remove mailbox from file system upon delete.

This is a stretch but could you provide the section of the python "delete user" code in which the logging mechanism must live? My python is limited, ill likely have to develop the logging mechanism code/script after we push the servers live. Much like you suggested for the request in the link I posted for ldap.

Also can you point me to a sample python script that cleans the database of old files after a certain period of time? I'll need this to clear the "deleted mailboxes" mysql log after about every week or so.

I know this is a stretch, but for major deployments with 30,000 plus accounts the inability to physically remove mailboxes from the disks is a major concern that we didn't notice till almost on top of pushing live.

Irregardless, it's a great product thus far, we very much appreciate the quick support.

4 Reply by amora

  • amora
  • Member
  • Offline
  • Registered: 2013-04-09
  • Posts: 58

Re: MYSQL Remove mailbox from file system upon delete.

Apparently I managed to log in with a different account in my initial posting

5 Reply by camel1cz

  • camel1cz
  • camel1cz
  • Member
  • Offline
  • From: Czech Republic
  • Registered: 2013-04-20
  • Posts: 220

Re: MYSQL Remove mailbox from file system upon delete.

Just my 2 cents - why you try to modify the webadmin code and on top in language you don't know while you can create standalone script in your preferred programming language?

General idea of the script:
- get list of all physical mailbox folders in the filesystem,
- for each physical mailbox, check if there is corresponding entry in mailbox table (attributes storagebasedirectory, storagenode, mailbox)
- delete/move away the not existing ones

Run this script as often as suits best your application.

6 Reply by ZhangHuangbin

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

Re: MYSQL Remove mailbox from file system upon delete.

With iRedAdmin-Pro for MySQL or PostgreSQL edition, you can find function 'deleteAccounts' in libs/mysql/core.py (or libs/pgsql/core.py), just place you code before 'try:'.

    def deleteAccounts(self, accounts, accountType):
        ...
        elif accountType == 'user':
            ...
            try:
                ...

You need to query SQL database to get maildir path of users first, then delete it.

WARNING: It may take long time to delete mailbox if it contains many mail messages.

PERSONALLY, i prefer log the maildir path in SQL db first, then execute it with a cron job. You can also keep the mailbox for as long as you want, for example, 90 days for audit.

I will achieve this in future release.

7 Reply by amora

  • amora
  • Member
  • Offline
  • Registered: 2013-04-09
  • Posts: 58

Re: MYSQL Remove mailbox from file system upon delete.

ZhangHuangbin wrote:

With iRedAdmin-Pro for MySQL or PostgreSQL edition, you can find function 'deleteAccounts' in libs/mysql/core.py (or libs/pgsql/core.py), just place you code before 'try:'.

    def deleteAccounts(self, accounts, accountType):
        ...
        elif accountType == 'user':
            ...
            try:
                ...

You need to query SQL database to get maildir path of users first, then delete it.

WARNING: It may take long time to delete mailbox if it contains many mail messages.

PERSONALLY, i prefer log the maildir path in SQL db first, then execute it with a cron job. You can also keep the mailbox for as long as you want, for example, 90 days for audit.

I will achieve this in future release.

Thank you, I'll also agree with your usage of an additional DB to store removed users for a time period and have a cron job clean up the files after a set amount of time.

Should I have success, I'll repost here should I come up with a working solution.

Thanks again!

8 Reply by amora

  • amora
  • Member
  • Offline
  • Registered: 2013-04-09
  • Posts: 58

Re: MYSQL Remove mailbox from file system upon delete.

camel1cz wrote:

Just my 2 cents - why you try to modify the webadmin code and on top in language you don't know while you can create standalone script in your preferred programming language?

General idea of the script:
- get list of all physical mailbox folders in the filesystem,
- for each physical mailbox, check if there is corresponding entry in mailbox table (attributes storagebasedirectory, storagenode, mailbox)
- delete/move away the not existing ones

Run this script as often as suits best your application.

I'm trying to avoid this method due to its Inefficiency. Why query a massive directory structure containing non uniform directory paths to specific user accounts just to again loop that through a query against the database... when you can execute, at the time of deletion, a single query that finds the exact path of the deleted mailbox user and stores it them deletes it at a later date.

1 query vs multiple 'find -type d' statements for each unique directory structure(of which are sever) over several million directories which then must be further processed to determine if the the directory is a user account or a domain, or some random number/letter.

It's just very inefficient and requires a good amount of post processing, but non the less a very valid point, one which I will fall back to should I not be able to get my python in check before deployment. The input is very much appreciated.

Thank you very much.

9 Reply by maxie_ro

  • maxie_ro
  • Member
  • Offline
  • From: Constanţa, România
  • Registered: 2009-10-09
  • Posts: 214

Re: MYSQL Remove mailbox from file system upon delete.

There is already a script included in PostFixAdmin, in the "ADDITIONS" folder, called "cleanupdirs.pl". Run it first without options to see which folder it would delete, then with the "--delete --print" options to actually delete them.

10 Reply by ZhangHuangbin

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

Re: MYSQL Remove mailbox from file system upon delete.

Hi maxie_ro,

I read the clearupdirs.pl, it should work with PostfixAdmin, but you have to modify it for iRedMail.

It queries SQL db to get existing user maildirs, then scans the whole directory under /home/vmail (or /var/vmail on iRedMail server) to get existing mailboxes, compares them and delete/print maildirs which will be deleted.

IMO, it's much better and easier to log maildir path (and other related info) while deleting user account, it works for different formats of maildir path (domain/username/, domain/u/s/e/username-timestamp/, domain/u/s/e/username/, etc).

iRedMail appends a timestamp in maildir path while creating mail user, so it won't be wrong if you choose this format of maildir path. The worry is maildir path without timestamp...

11 Reply by ZhangHuangbin

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

Re: MYSQL Remove mailbox from file system upon delete.

Hi guys,

Just want to let you know, upcoming iRedAdmin-Pro release will log maildir path of deleted user in SQL table, so you can delete mailboxes with a script manually or with a cron job.

You can check the SQL table structure here (table name: deleted_mailboxes), let me know if you want to store more info in this table:
https://bitbucket.org/zhb/iredmail/src/ … ult#cl-283