1

Topic: Switch from old community to latest pro version - DB structure

Hi guys,

I'm going soon to switch very old (v0.9.2) version or iRM to latest Pro version. I compared DB structure and found few attributes I would like to consult with you. So:

Table domain
(drop) defaultpasswordscheme (empty for all accounts, didn't found any note about dropping this but it's not in current version)
(add) maillists -> '' (this should be safe? I don't use maillists at all)


Table mailbox
(drop) allowedsenders
(drop) rejectedsenders
(drop) allowedrecipients
(drop) rejectedrecipients

All above was not used in my setup. Should be safe to drop.

(drop) local_part

I read, it was never used in iRedMail. So should be safe to drop.
Is the logic around composing the path to store email still the same? storagebasedirectory + storagenode + maildir + mailboxfolder (this was added in the past implicitly as Maildir)

(drop) enablesendermismatch

I use this, but this attribute is not used. Allowed accounts are handcoded in config of iredapd - how is this solved in current version or iRM?

(add) mailboxformat -> 'maildir'

Quite clear.

(add) mailboxfolder -> 'Maildir'

I use this folder.

(add) enablepop3tls -> 1
(add) enableimaptls -> 1
(add) enablesievetls -> 1

Quite clear, even if I don't understand why is this different from secured versions? Why disable TLS and not SSL or vice versa / but it's just philosofical question smile

(add) enablequota-status -> 1

Found the docs and it's a good thing.

(add) enabledsync -> 0

Don't use activesync

(add) enablesogo -> 0

Quite clear, I don't use it.

Table alias
(drop) goto
(drop) moderators
(drop) islist

I alias for all mailboxes with address=some@domain.com and goto=some@domain.com / it's no more necessary? Did the logic around aliases change?

Is there anything else I should take care of besides the actual maildirs?
I can now thing of just the senderloginmismatch feature which I'm not sure how works in new iRM.

Thanks a lot!

2

Re: Switch from old community to latest pro version - DB structure

The suggestion is going through all iRedMail upgrade tutorials for your iRedMail-0.9.2 and find the detailed SQL changes:
https://docs.iredmail.org/iredmail.releases.html

camel1cz wrote:

Table domain
(drop) defaultpasswordscheme (empty for all accounts, didn't found any note about dropping this but it's not in current version)

iRedMail doesn't use this column at all.

camel1cz wrote:

(add) maillists -> '' (this should be safe? I don't use maillists at all)

Please keep it for compatibility.

camel1cz wrote:

(drop) local_part

I read, it was never used in iRedMail. So should be safe to drop.

Yes.

camel1cz wrote:

Is the logic around composing the path to store email still the same? storagebasedirectory + storagenode + maildir + mailboxfolder (this was added in the past implicitly as Maildir)

Yes.

camel1cz wrote:

(drop) enablesendermismatch

I use this, but this attribute is not used. Allowed accounts are handcoded in config of iredapd - how is this solved in current version or iRedMail?

In plugin /opt/iredapd/plugins/reject_sender_login_mismatch.py.

camel1cz wrote:

(add) enablepop3tls -> 1
(add) enableimaptls -> 1
(add) enablesievetls -> 1

Quite clear, even if I don't understand why is this different from secured versions? Why disable TLS and not SSL or vice versa / but it's just philosofical question smile

The name was changed in Dovecot internally, so we introduced these columns to match them.

camel1cz wrote:

(add) enabledsync -> 0

Don't use activesync

iRedMail doesn't have this column.

camel1cz wrote:

Table alias
(drop) goto
(drop) moderators
(drop) islist

I alias for all mailboxes with address=some@domain.com and goto=some@domain.com / it's no more necessary? Did the logic around aliases change?

Changed and must be synced to table "forwardings".

----

Buy me a cup of coffee ($5) to support iRedMail:

buy me a cup of coffee

3

Re: Switch from old community to latest pro version - DB structure

Thanks Zhang, my server is now up and running.

I had few issues to solve, some are just notes for anyone going the same path, some are hints for documentation update/change in iRedMail code.

General notes for users
- some of my users are used to plaintext/insecure services (SMTP with AUTH w/o encryption, POP3, IMAP) - take care about this. Docs are here.
- apply letsencrypt certificates. Docs is here.
- make sure to update DNS and/or reconfigure and copy DKIM keys in amavis. Docs are here.

Notes connected to default configuration
- backup of server configuration is not done anymore - I have updated backup_config.sh from old instalation,
- configuration of logging. On debian/ubuntu, syslog gets spammed with mail logs. They are also huge and stored twice in syslog and mail.log
In /etc/rsyslog.d/50-default.conf I change:

*.*;auth,authpriv.none> -/var/log/syslog

to

*.*;mail.none;auth,authpriv.none -/var/log/syslog

to exclude mail logs from syslog and store them only in mail.log
Then I configure logrotate.d to backup logs on daily basis - create /etc/logrotate.d/iredmail with contents:

/var/log/mail.log {                                                           
    compress                                                                  
    daily                                                                     
    rotate 10                                                                 
    create 0600 root root                                                     
    missingok                                                                 
                                                                              
    # Use bzip2 for compress.                                                 
    compresscmd /bin/bzip2                                                    
    uncompresscmd /bin/bunzip2                                                
    compressoptions -9                                                        
    compressext .bz2                                                          
                                                                              
    postrotate                                                                
        invoke-rc.d rsyslog rotate > /dev/null                                
                                                                              
        # Setup                                                               
        TARGET=/data/vmail/backup/logs/                                       
                                                                              
        # Commands                                                            
        export CMD_DATE='/bin/date'                                           
                                                                              
        # Date.                                                               
        export YEAR="$(${CMD_DATE} +%Y)"                                      
        export MONTH="$(${CMD_DATE} +%m)"                                     
        export DAY="$(${CMD_DATE} +%d)"                                       
                                                                              
        # create target dir                                                   
        if [ ! -d "$TARGET/$YEAR/$MONTH" ]                                    
        then                                                                  
            mkdir -p "$TARGET/$YEAR/$MONTH"                                   
        fi                                                                    
        mv /var/log/mail.log.1 "$TARGET/$YEAR/$MONTH/$YEAR$MONTH$DAY.log"     
        gzip "$TARGET/$YEAR/$MONTH/$YEAR$MONTH$DAY.log"                       
    endscript                                                                 
}                                                                             

This code stores mail logs to $TARGET directory.
- enable mail submission of emails from clients over ports 465 (depricated) and 587 (preffered) with forced encryption.
Update /etc/postfix/master.cf

submission inet n       -       n       -       -       smtpd          
  -o syslog_name=postfix/submission                                    
  -o smtpd_tls_security_level=encrypt                                  
  -o smtpd_sasl_auth_enable=yes                                        
  -o smtpd_client_restrictions=permit_sasl_authenticated,reject        
  -o content_filter=smtp-amavis:[127.0.0.1]:10026                      
smtps     inet  n       -       n       -       -       smtpd
    -o smtpd_tls_wrappermode=yes
    -o smtpd_sasl_auth_enable=yes
    -o smtpd_client_restrictions=permit_sasl_authenticated,reject

- !!! unfirewall ports in /etc/nftables.conf

If I found anything else/remember something, I'll update this thread.

4 (edited by camel1cz 2021-11-20 06:47:15)

Re: Switch from old community to latest pro version - DB structure

Report after few days of production usage:

* In old server, I was used to create aliases (forwardings in new version) w/o email account - at least postmaster@xxx. It does work in new version, but the forwardings are not accessible in iRedAdmin.
@Zhang - I could create regular account postmaster and add forwarding to general postmaster account, but I don't like the real account connected with pure virtual address - it's theoretically possible to break the password, etc. how to solve this? The easiest solution would be to allow "Aliases" in iRedAdmin to be from other domains?

I implemented two custom features I used on my old server in the "iRedMail way".
1) blocking of accounts breaching configurable limits of emails sent (partial duble of throttling feature) and IPs used to access server (very efficient detection of account missuse),
2) reporting domains with missconfigured DNS MX records (pointing outside of my server)

I would like to incorporate result of this checks in iRedAdmin - what about to add some general log connected either to domain or account? Also I would like to see some "warning" flag for both account and domain with exclamation mark icon in lists.

Zhang, if you are interested in any of above, I can send it per email. I use API of iRedMail-PRO, thus I do not publish my code here.

Also, I see wrong encoding in email subjects on pages like /iredadmin/activities/received - I understand, amavis doesn't care about encodings, but it's quite frustrating to see damaged characters in the lists. Any Idea? What about some charset detection?

P.S. I like the work you did since I installed the first version! Many tools I used on old server are part of the iRedMail installation - mainly cleanup scripts.

5

Re: Switch from old community to latest pro version - DB structure

camel1cz wrote:

* In old server, I was used to create aliases (forwardings in new version) w/o email account - at least postmaster@xxx. It does work in new version, but the forwardings are not accessible in iRedAdmin.

What do you mean "forwardings are not accessible in iRedAdmin"?

camel1cz wrote:

@Zhang - I could create regular account postmaster and add forwarding to general postmaster account, but I don't like the real account connected with pure virtual address - it's theoretically possible to break the password, etc. how to solve this? The easiest solution would be to allow "Aliases" in iRedAdmin to be from other domains?

There's an option in iRedAdmin-Pro default config /opt/www/iredadmin/libs/default_settings.py:

# Allow to assign per-user alias address under different domains.
USER_ALIAS_CROSS_ALL_DOMAINS = False

You can simply add this parameter in /opt/www/iredadmin/settings.py with value "True" (without quotes). Restarting iredadmin service is required.

----

Buy me a cup of coffee ($5) to support iRedMail:

buy me a cup of coffee