POSTGRESQL import export from csv

http://www.gistutor.com/postgresqlpostgis/10-intermediate-postgresqlpostgis-tutorials/39-how-to-import-or-export-a-csv-file-using-postgresql-copy-to-and-copy-from-queries.html

Postfix + Dovecot + PostgreSQL + (Amavis + SpamAssassin + ClamAV + Postgrey)

Source: http://habrahabr.ru/post/193220/

This is free translation of that article. I haven't aimed to translate world by world. Just enough to give you some imagination, what you need to do to build the email system.  If you dont want to do this routine work, there is package, called iRedMail. All in one: Postfix, Dovecot, Apache, MySQL/PostgreSQL, Policyd, Amavis, Fail2ban, Roundcube and even Awstats. Easy to install, having a nice admin panel (free) and another one, much nicer (but you have to pay for it) , that can beat any comparison to awful  PostfixAdmin. But if you wanna do all by yourself, you might continue reading.The task is to build stable post systen on CentOS. MTA and MDA will be Postfix + Dovecot. The software, attached to them: Amavis + SpamAssassin + ClamAV + Postgrey + Fail2Ban. User's emails will be stored on hard drive in specific directory, but users accounts and domain records — in PostgreSQL.  Good for multiply domains with user's quota restrictions. 

00 Preparation:

[*] Add additional reos: epel, rpmforge, centalt и remi. Recomended to use them with yum-priorities plugin; 

[*] If you would like to have SELinux enabled, and you know, what to do - thats would be fine. In our case we will leave it as disabled. Guess, the permissive mode could be used as well. 

[*] Don't forget ntp:

yum install ntp
ntpdate <your server>
chkconfig ntpd on && /etc/init.d/ntpd start

This will help you to avoid Dovecot’s bug «Time has moved backwards». As well, you can change in /etc/sysconfig/ntpd the key and swap to -L, so ntpd wont listen the network.

[*] For testing reason, install these utilites:

yum install wget mlocate bind-utils telnet mailx sharutils

01 PostgreSQL, ClamAV

In original article, the author is using MySQL 5.5 from Remi. But we going to use PgSQL. It could be installed "from box" without any additional changes. Then do 

su - postgres

psql -d template1 -U postgres

Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
Type:  \\copyright for distribution terms
       \\h for help with SQL commands
       \\? for help on internal slash commands
       \\g or terminate with semicolon to execute query
       \\q to quit
template1=# 

Create user and db:
template1=# CREATE USER postfix WITH PASSWORD 'mypassword';
template1=# CREATE DATABASE postfix;
template1=# GRANT ALL PRIVILEGES ON DATABASE postfix  TO postfix;

Exit from psql console:template1=# \q

Don't forget to allow your user in /var/lib/pgsql/9.3/data/pg_hba.conf

# "local" is for Unix domain socket connections only 
local     all             all                                     peer # IPv4 local connections:host    postfix           all             127.0.0.1/32            md5

Our antivirus will be ClamAV. I have to mention, that latest version is available at CentALT, but can't be downloaded out of there. The downloading stops after 50 mb done of clamav-db. So, just simply install from EPEL. Clam will work through socket, so comment this in /etc/clamd.conf :

#TCPSocket 3310
#TCPAddr 127.0.0.1

It updates its dbases itself automatically by freshclam. Check, if it appears in cron.daily then run it manually:

freshclam
chkconfig clamd on && /etc/init.d/clamd start

02 Database and web-inteface

To manage domains and mailboxes we could use PostfixAdmin. As well we going to use its database scheme. If you DON'T want to install it, here is mysql dump, that can be deployed without PostfixAdmin installed: mysql_dump.sql at github.

To have PostfixAdmin working we will need nginx/apache + php. There not that much settings for PostfixAdmin. Edit config.inc.php:

## password hash must be same as for dovecot
$CONF['encrypt'] = 'md5crypt';
$CONF['transport_default'] = 'virtual';
$CONF['emailcheck_resolve_domain']='NO';
## As dovecot will be used, remove prefix
$CONF['create_mailbox_subdirs_prefix']='';
## In case, if you going to store your quotas not in maildir, but in database through dict
$CONF['new_quota_table'] = 'yes';

Then go to domain.tld/postfixadmin/setup.php, generate password and super-admin account. After it, put your newly generated hash to your config.inc.php and change the status:

$CONF['configured'] = true;
$CONF['setup_password'] = 't8h9i9s2i7s7m2y4l9o8g9i4n:a0n9d5p2a5s2s9w5o4r7d';

[!] Postfixadmin will create database schemes in mysql/postgresql after you run setup.php. 

03 Postfix

Check your database postfix if there needed tables were created, then proceed to MTA and MDA installation. Postfix comes with CentOS has no pgsql support. To fix this, please read the article: http://shirkerphp.blogspot.com/2013/10/howto-rebuild-postfix-with.html. Once you have finished rebuilding, install Postfix by following command:

cd /root/rpmbuild/RPMS/x86_64
rpm -i postfix-2.6.6-2.2.el6.x86_64.rpm

All user's mailboxes will be stored in /var/vmail and owned by vmail user:

groupadd  -g 1000 vmail
useradd -d /var/vmail/ -g 1000 -u 1000 vmail
chown vmail:vmail /var/vmail

Create hanmaid  SSL certs:

mkdir /etc/postfix/certs
openssl req -new -x509 -days 3650 -nodes -out /etc/postfix/certs/cert.pem -keyout /etc/postfix/certs/key.pem

The hard part is to make Postfix working with database:

mkdir /etc/postfix/pgsql

In that directory make following files:

Postfix pgsql

relay_domains.cf

hosts = localhost
user = postfix
password = mypassword
dbname = postfix
query = SELECT domain FROM domain WHERE domain='%s' and backupmx = '1'

virtual_alias_domain_maps.cf

hosts = localhost
user = postfix
password = mypassword
dbname = postfix
query = SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' and alias.address = CONCAT('%u', '@', alias_domain.target_domain) AND alias.active = true

virtual_alias_maps.cf

hosts = localhost
user = postfix
password = mypassword
dbname = postfix
query = SELECT goto FROM alias WHERE address='%s' AND active = '1'

virtual_mailbox_domains.cf

hosts = localhost
user = postfix
password = mypassword
dbname = postfix
query = SELECT domain FROM domain WHERE domain='%s' AND backupmx = '0' AND active = '1'

virtual_mailbox_maps.cf

hosts = localhost
user = postfix
password = mypassword
dbname = postfix
query = SELECT maildir FROM mailbox WHERE username='%s' AND active = '1'

Edit /etc/postfix/main.cf, to make Postfix work with database:

# ============================================================
# MySQL mappings
# ============================================================
relay_domains = mysql:/etc/postfix/mysql/relay_domains.cf
virtual_alias_maps = mysql:/etc/postfix/mysql/virtual_alias_maps.cf,
                     mysql:/etc/postfix/mysql/virtual_alias_domain_maps.cf
virtual_mailbox_domains = mysql:/etc/postfix/mysql/virtual_mailbox_domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql/virtual_mailbox_maps.cf

Good SMTP should check credentials for outsiders, but our own users may have ability to send emails without any authorization. To let it work correctly, lets start Submission on 587 port. The smartphones while creating new accounts, offer port 587 in smtp settings by default. You don't want to explain your clients, that inserting in the SMTP settings field simply domain name mail.domain.tld is not enough, and they have to specify some ports numbers.  So lets make their life easier.. 

In /etc/postfix/master.cf edit submission part:

submission inet n       -       n       -       -       smtpd
  -o syslog_name=postfix/submission
  -o smtpd_tls_wrappermode=no
  -o smtpd_tls_security_level=encrypt
  -o smtpd_sasl_auth_enable=yes
  -o smtpd_recipient_restrictions=permit_mynetworks,permit_sasl_authenticated,reject
  -o smtpd_relay_restrictions=permit_mynetworks,permit_sasl_authenticated,defer_unauth_destination
  -o milter_macro_daemon_name=ORIGINATING

[!] Don't forget to insert spaces in front of -o keys, otherwise the config won't be valid. 

Let master.cf to stand by, we will be back to it soon, now now let's continue with  /etc/postfix/main.cf

soft_bounce = no
myhostname = mail.domain.tld
mydomain = domain.tld
myorigin = $myhostname

# remove $myhostname and $mydomain from local class
# to avoid do not list domain in BOTH mydestination and virtual_mailbox_domains
mydestination = localhost.$mydomain, localhost

## your network
mynetworks = 192.168.0.0/16, 127.0.0.0/8

## Remove nis:/ to avoid overflowing the log files: 
## dict_nis_init: NIS domain name not set - NIS lookups disabled
alias_maps = hash:/etc/aliases

smtpd_banner = $myhostname ESMTP $mail_name
debug_peer_level = 2
debug_peer_list = 127.0.0.1

We have changed above the default directives. And now we need to add this part at the bottom of  /etc/postfix/main.cf:

# ============================================================
# RESTRICTIONS
#
# Uncomment reject_rbl_client if necessary
# More information at: http://help.ubuntu.ru/wiki/фильтрация_спама_на_уровне_smtp_протокола
# ============================================================
smtpd_discard_ehlo_keywords = etrn, silent-discard
smtpd_forbidden_commands = CONNECT GET POST
broken_sasl_auth_clients = yes
smtpd_delay_reject = yes
smtpd_helo_required = yes
disable_vrfy_command = yes

smtpd_helo_restrictions = permit_mynetworks,
                          permit_sasl_authenticated,
                          reject_non_fqdn_helo_hostname,
                          reject_invalid_helo_hostname

smtpd_data_restrictions = permit_mynetworks,
                          permit_sasl_authenticated,
                          reject_unauth_pipelining,
                          reject_multi_recipient_bounce,

smtpd_sender_restrictions = permit_mynetworks,
                            permit_sasl_authenticated,
                            reject_non_fqdn_sender,
                            reject_unknown_sender_domain

smtpd_recipient_restrictions = reject_non_fqdn_recipient,
                               reject_unknown_recipient_domain,
                               reject_multi_recipient_bounce,
                               permit_mynetworks,
                               permit_sasl_authenticated,
                               reject_unauth_destination,
                               check_policy_service unix:/var/spool/postfix/postgrey/socket,
                               #reject_rbl_client zen.spamhaus.org,
                               #reject_rbl_client bl.spamcop.net,
                               #reject_rbl_client dnsbl.sorbs.net,
                               reject_invalid_hostname

# ============================================================
# TLS
# ============================================================
smtp_tls_security_level = may
smtpd_tls_security_level = may
smtpd_tls_loglevel = 1
smtpd_tls_received_header = yes
smtpd_tls_session_cache_timeout = 3600s
smtp_tls_session_cache_database = btree:$data_directory/smtp_tls_session_cache
smtpd_tls_key_file = /etc/postfix/certs/key.pem
smtpd_tls_cert_file = /etc/postfix/certs/cert.pem
tls_random_source = dev:/dev/urandom

# ============================================================
# LIMITS
# ============================================================
message_size_limit = 51200000
smtpd_soft_error_limit = 10
smtpd_hard_error_limit = 15
smtpd_error_sleep_time = 20
anvil_rate_time_unit = 60s
smtpd_client_connection_count_limit = 20
smtpd_client_connection_rate_limit = 30
smtpd_client_message_rate_limit = 30
smtpd_client_event_limit_exceptions = 127.0.0.1/8
smtpd_client_connection_limit_exceptions = 127.0.0.1/8

# ============================================================
# QUEUE
# ============================================================
maximal_queue_lifetime = 1d
bounce_queue_lifetime = 1d

[!] Using blacklists is your choise. But I have commented reject_rbl_client. Greylisting does all work good enough , but things like Spamhaus and etc  could cause some emails dropped, which you, probably, dont want.
[!] We will be back to main.cf soon by adding there postgrey, amavis and dovecot. But for now lest go to  MDA part.

04.1 Dovecot

Dovecot is:

• Our Mail Delivery Agent, local transport
• SASL. Through that will work our Postfix
• Work with user's quotas
• Provides imap and pop3 connections

Change in /etc/dovecot/dovecot.conf:

protocols = imap pop3
login_greeting = Hello there.

The folowwing files you'll find in /etc/dovecot/conf.d directory:

10-auth.conf

disable_plaintext_auth = no
auth_realms = domain.tld domain2.tld
auth_default_realm = domain.tld
auth_mechanisms = plain login
## [!] comment auth-system.conf.ext, so dovecot wont look users in  pam
#!include auth-system.conf.ext

10-logging.conf

Decide here, what you want to see in your logs

10-mail.conf

mail_location = maildir:/var/vmail/%d/%n
mail_uid = 1000
mail_gid = 1000
mail_plugins = quota

10-master.conf

# Allow imap and imaps
service imap-login {
  inet_listener imap {
    port = 143
  }
  inet_listener imaps {
    port = 993
    ssl = yes
  }
}

# Allow pop3 and pop3s
service pop3-login {
  inet_listener pop3 {
    port = 110
  }
  inet_listener pop3s {
    port = 995
    ssl = yes
  }
}

# Allow SASL
service auth {
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
    group = vmail
  }
  unix_listener /var/spool/postfix/private/auth {
    mode = 0666
    user=postfix
    group=postfix
  }
}

10-ssl.conf

ssl = yes
ssl_cert = </etc/postfix/certs/cert.pem
ssl_key = </etc/postfix/certs/key.pem

15-lda.conf

quota_full_tempfail = no
lda_mailbox_autocreate = yes
lda_mailbox_autosubscribe = yes
protocol lda {
mail_plugins = $mail_plugins autocreate
}

20-imap.conf

protocol imap {
mail_plugins = $mail_plugins autocreate quota imap_quota
}

90-plugin.conf

# These folders might be different in your system.
# This is only example
plugin {
  autocreate = Trash
  autocreate2 = sent-mail
  autocreate3 = drafts
  autosubscribe = Trash
  autosubscribe2 = sent-mail
  autosubscribe3 = drafts
}

90-quota.conf

# Set, how much disk will be used per user.
plugin {
  quota_rule = *:storage=200M
  quota_rule2 = Trash:storage=+10M
}

plugin {
  quota_warning = storage=90%% quota-warning 90 %u
}

# Check for chmod of the script
# As well in quota-warning.sh is correct path to dovecot-lda
service quota-warning {
  executable = script /usr/local/bin/quota-warning.sh
  user = vmail
  unix_listener quota-warning {
    user = vmail
  }
}

# Quota's format - maildir
# Could be swaped to dict, fs or dirsize
# Read this: http://wiki2.dovecot.org/Quota
plugin {
  quota = maildir:User quota
}

auth-sql.conf.ext

passdb {
driver = sql
args = /etc/dovecot/dovecot-sql.conf.ext
}
userdb {
driver = sql
args = /etc/dovecot/dovecot-sql.conf.ext
}

Create /etc/dovecot/dovecot-sql.conf.ext:

dovecot-sql.conf.ext

driver = pgsql
connect = host=localhost dbname=postfix user=postfix password=mypassword
default_pass_scheme = MD5-CRYPT
user_query = SELECT '/var/vmail/%d/%n' as home, 'maildir:/var/vmail/%d/%n'as mail, 1000 AS uid, 1000 AS gid, concat('*:bytes=', quota) AS quota_rule FROM mailbox WHERE username = '%u' AND active = true

# active could be '1' as well, try both ways

password_query = SELECT username as user, password, '/var/vmail/%d/%n' as userdb_home, 'maildir:/var/vmail/%d/%n' as userdb_mail, 1000 as userdb_uid, 1000 as userdb_gid, concat('*:bytes=', quota) AS userdb_quota_rule FROM mailbox WHER
E username = '%u' AND active = true

Glue together Postfix and Dovecot. Add to /etc/postfix/main.cf:

# ============================================================
# SASL
# ============================================================
smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth

# ============================================================
# VIRTUAL
# ============================================================
virtual_mailbox_base = /var/vmail
virtual_minimum_uid = 1000
virtual_uid_maps = static:1000
virtual_gid_maps = static:1000
virtual_transport = dovecot
dovecot_destination_recipient_limit = 1

Inform Postfix that mail delivery will be done by dovecot. In /etc/postfix/master.cf:

# ====================================================================
# DOVECOT
# ====================================================================
dovecot unix    -       n       n       -       -      pipe
  flags=DRhu user=vmail:vmail argv=/usr/libexec/dovecot/deliver -f ${sender} -d ${recipient}

The next, we need quota warning script /usr/local/bin/quota-warning.sh to work correctly. In my case, the path in CentOS was incorrect, so I had to fix it manually. BTW, you need to correct it anyway to set correct sender's name, which is set as postmaster@domain.tld by default:

updatedb
locate dovecot-lda
chmod 755 /usr/local/bin/quota-warning.sh

And correct path /usr/local/bin/quota-warning.sh

04.2 Adding Sieve support to Dovecot

What's sieve? Its a script interpreter, that let you use custom users scripts to filter or moving emails.
From Dovecot website:"The Dovecot Sieve plugin provides mail filtering facilities at time of final message delivery using the Sieve (RFC 5228) language. By writing Sieve scripts, users can customize how messages are delivered, e.g. whether they are forwarded or stored in special folders. The Sieve language is meant to be simple, extensible and system independent. And, unlike most other mail filtering script languages, it does not allow users to execute arbitrary programs. This is particularly useful to prevent virtual users from having full access to the mail store. The intention of the language is to make it impossible for users to do anything more complex (and dangerous) than write simple mail filters"

yum install dovecot-pigeonhole



Change dovecot.conf, add sieve support:



protocols = imap sieve






cat conf.d/20-managesieve.conf

service managesieve-login {
}
service managesieve {
}
protocol sieve {
}
plugin {
  sieve = ~/.dovecot.sieve
  sieve_global_path = /var/vmail/sieve/.dovecot.sieve
}



cat conf.d/15-lda.conf |grep -v "#" | tr -s "[\n]"
postmaster_address = postmaster@12beonline.com
protocol lda {
  mail_plugins = $mail_plugins sieve
}


cat /var/vmail/sieve/.dovecot.sieve (moving emails tagged as Spam to Spam folder)
require "fileinto";
if header :contains "X-Spam-Flag" "YES" {
  fileinto "Spam";
}
if header :contains "CM SPAM detection" "spam" {
  fileinto "Spam";
}


run #service dovecot restart



issue dovecot -n to see your full configuration. If you think, that something is not working well, enable debug at 

 cat conf.d/10-logging.conf |grep -v "#" | tr -s "[\n]"
log_path = /var/log/dovecot
info_log_path = /var/log/debug_mail
auth_verbose = no
auth_verbose_passwords = plain
auth_debug = no
mail_debug = yes
plugin {
}

restart dovecot. to watch debug log:
tail -f /var/log/debug_mail 


05 Spamassasin + Amavis-new 
As we decided to have Amavis, which stays between MTA and antivirus/antispam systems, then start spamd is not necessary – it will work as module, loaded by call. To keep SA updated, will be used sa-update. Check, if in etc/cron.d you do have sa-update file with updated scheduled.



[!] Install spamassassin 3.3.2 from rpmforge-extras, because EPEL's version 3.3.1 has a bug in sa-update. The latest 3.3.2 was freed from this bug and works correct:





yum install spamassassin amavisd-new

Edit /etc/mail/spamassassin/local.cf


local.cf


required_hits 6
report_safe 0
rewrite_header Subject ***SPAM***

#
# WEIRD STUFF
#
score FORGED_MUA_OUTLOOK 2.199 2.199 0.963 1.116
score FH_DATE_PAST_20XX 0.0
score DOS_OE_TO_MX_IMAGE 0.0
score DOS_OE_TO_MX 0.0
score DOS_OUTLOOK_TO_MX 0.0
score TO_NO_BRKTS_DIRECT 0.0
score HELO_LOCALHOST 0.0
score FSL_RU_URL 0.0
score FROM_MISSP_EH_MATCH 1.0
score TVD_RCVD_SINGLE 1.0
score TO_IN_SUBJ 1.0

#
# TUNING
#
score SUBJ_FULL_OF_8BITS 0.00
score HTML_COMMENT_8BITS 0.01
score HEADER_8BITS 0.00
score TO_NO_USER 0.01
score FORGED_MUA_OUTLOOK 0.5
score X_AUTH_WARNING 0.01
score SUBJ_HAS_UNIQ_ID 9.99
score HTTP_USERNAME_USED 9.99
score FORGED_YAHOO_RCVD 9.99
score FORGED_JUNO_RCVD 16
score UNWANTED_LANGUAGE_BODY 1.02
score MLM 5.55
score RCVD_NUMERIC_HELO 4.95

#
# WHITE/BLACK LISTS
#
whitelist_from root@localhost
whitelist_from *@example.com
blacklist_from *@outblaze.com
auto_whitelist_path /etc/mail/spamassassin/auto-whitelist
auto_whitelist_file_mode 0666



And another one is Amavis's config file. Complete opposite of  Dovecot's conf files. It's a plain perl script, hard to read:


amavisd.conf


# amount of copies amavisd
# must be the same as in /etc/postfix/master.cf
$max_servers = 4;

# Domain by default
$mydomain = 'domain.tld';

# your networks
@mynetworks = qw( 127.0.0.0/8 [::1] [FE80::]/10 [FEC0::]/10
                  10.0.0.0/8 172.16.0.0/12 192.168.0.0/16 );

# Twik for 2.6.4 and later. Removes log's output: 
# Open relay? Nonlocal recips but not originating
@client_ipaddr_policy = map { $_ => 'MYNETS' } @mynetworks;

# Max values for spamassassin
$sa_tag_level_deflt  = 2.0;
$sa_tag2_level_deflt = 5.2;
$sa_kill_level_deflt = 6.5;
$sa_dsn_cutoff_level = 10; 

# Where will be send spam and viruses
$virus_admin               = "virusalert\@$mydomain";
$mailfrom_notify_admin     = "virusalert\@$mydomain";
$mailfrom_notify_recip     = "virusalert\@$mydomain";
$mailfrom_notify_spamadmin = "abuse\@$mydomain";     

# Your host name
$myhostname = 'mail.domain.tld'; 

# Что делать при срабатывании
$final_virus_destiny      = D_DISCARD;
$final_banned_destiny     = D_BOUNCE;
$final_spam_destiny       = D_DISCARD;
$final_bad_header_destiny = D_PASS;

# If we going to use quarantine
# 
$virus_quarantine_to = "virus-quarantine\@$mydomain";
$spam_quarantine_to  = "spamtrap\@$mydomain";

# Domains white lists
# Array
@whitelist_sender_maps = ( new_RE(
    qr'.*@example\.com$'i,
    qr'user@test\.org$'i,
));

# Truncate @av_scanners array to
@av_scanners = (
  ['ClamAV-clamd',
    \&ask_daemon, ["CONTSCAN {}\n", "/var/run/clamav/clamd.sock"],
    qr/\bOK$/m, qr/\bFOUND$/m,
    qr/^.*?: (?!Infected Archive)(.*) FOUND$/m ],
);

);

# As well another one
@av_scanners_backup = (
  ['ClamAV-clamscan', 'clamscan',
  "--stdout --no-summary -r --tempdir=$TEMPBASE {}",
  [0], qr/:.*\sFOUND$/m, qr/^.*?: (?!Infected Archive)(.*) FOUND$/m ],
);



Tell Postfix to use Amavis for emails check. Add this block to /etc/postfix/master.cf


master.cf


# ====================================================================
# AMAVIS
# ====================================================================
amavisfeed unix -       -       n       -       4       lmtp
  -o lmtp_data_done_timeout=1200
  -o lmtp_send_xforward_command=yes
  -o disable_dns_lookups=yes
  -o max_use=20
127.0.0.1:10025 inet n    -       n       -       -     smtpd
  -o content_filter=
  -o smtpd_delay_reject=no
  -o smtpd_client_restrictions=permit_mynetworks,reject
  -o smtpd_helo_restrictions=
  -o smtpd_sender_restrictions=
  -o smtpd_recipient_restrictions=permit_mynetworks,reject
  -o smtpd_data_restrictions=reject_unauth_pipelining
  -o smtpd_end_of_data_restrictions=
  -o smtpd_restriction_classes=
  -o mynetworks=127.0.0.0/8,192.168.0.0/16
  -o smtpd_error_sleep_time=0
  -o smtpd_soft_error_limit=1001
  -o smtpd_hard_error_limit=1000
  -o smtpd_client_connection_count_limit=0
  -o smtpd_client_connection_rate_limit=0
  -o receive_override_options=no_header_body_checks,no_unknown_recipient_checks,no_milters,no_address_mappings
  -o local_header_rewrite_clients=
  -o smtpd_milters=
  -o local_recipient_maps=
  -o relay_recipient_maps=



and add to /etc/postfix/main.cf 

# ============================================================
# AMAVIS
# ============================================================
content_filter=amavisfeed:[127.0.0.1]:10024

Start/restart/startup services:

chkconfig amavisd on && /etc/init.d/amavisd start
/etc/init.d/postfix restart

telnet 127.0.0.1 10024
# 220 [127.0.0.1] ESMTP amavisd-new service ready


06 Postgrey, Fail2Ban
To install postgrey, simply run:

yum install postgrey

It doesn't need to be configured. So go and add it to /etc/postfix/main.cf

smtpd_recipient_restrictions = ...
                               reject_unauth_destination,
                               check_policy_service unix:/var/spool/postfix/postgrey/socket,
                               ...

[!] Directive check_policy_service MUST appear after reject_unauth_destination.

But if you want to exclude some servers from being shecked — do changes in /etc/postfix/postgrey_whitelist_clients.local and in postgrey_whitelist_recipients. More info at:wiki.centos.org/HowTos/postgrey






To see, how good is Fail2ban take a look at graph below. There are two parts: before fail2ban installed and after. It bans all non-authorised attempts of all spammers: 


yum install fail2ban


Rules of /etc/fail2ban/jail.conf



##
## Make sure, the paths to log files are correct
##

[ssh-iptables]
enabled  = true
filter   = sshd
action   = iptables[name=SSH, port=ssh, protocol=tcp]
logpath  = /var/log/secure
maxretry = 5

[postfix-banhammer]
enabled  = true
filter   = postfix
action   = iptables-multiport-tcp[name=PFIX, port="smtp,smtps", protocol=tcp]
logpath  = /var/log/maillog
maxretry = 3
bantime  = 7200

[dovecot-banhammer]
enabled  = true
filter   = dovecot
action   = iptables-multiport-tcp[name=DCOT, port="pop3,pop3s,imap,imaps", protocol=tcp]
logpath  = /var/log/maillog
findtime = 300
maxretry = 10
bantime  = 1800

[sasl-banhammer]
enabled  = true
filter   = sasl
action   = iptables-multiport-tcp[name=SASL, port="smtp,smtps", protocol=tcp]
logpath  = /var/log/maillog
findtime = 300
maxretry = 10
bantime  = 1800



If SSH is opened for local network only, remove the first rule. Thats all, rules work right away, just from box. Thanks urbain for reminding of protection from smtp brut forcing.

chkconfig fail2ban on && /etc/init.d/fail2ban start


07 Testing
As we set in Dovecot plugin autocreate, then to add users and domains, will be enough to insert them to dbase from Postfixadmin or do INSERT INTO in pgsql console. The user's email structure will be created upon first login.


Test pop3, imap, smtp



Test login POP3
telnet 127.0.0.1 110
user test
pass testpassword
UIDL 

Test login IMAP
telnet 127.0.0.1 143
1 LOGIN test testpassword
1 SELECT INBOX


Test SMTP without authorization 
telnet 127.0.0.1 25
#220 mail.domain.tld ESMTP Postfix
helo darling
#250 mail.domain.tld
MAIL FROM: root@localhost
#250 2.1.0 Ok
RCPT TO: test@example.com
#250 2.1.5 Ok
DATA
#354 End data with <CR><LF>.<CR><LF>
this is text message

.
#250 2.0.0 Ok: queued as E3BA1A15FA
quit
#221 2.0.0 Bye


Test SMTP with authorization but without SSL
Encode to base64 your test login and password, whose will be sent after AUTH LOGIN.
telnet 127.0.0.1 25
#220 mail.domain.tld ESMTP Postfix
ehlo darling
#250-mail2.dautkom.lv
#250-PIPELINING
#250-SIZE 51200000
#250-STARTTLS
#250-AUTH PLAIN LOGIN
#250-AUTH=PLAIN LOGIN
#250-ENHANCEDSTATUSCODES
#250-8BITMIME
#250 DSN
AUTH LOGIN
#334 VXNlcm5hbWU6
dGVzdA==
#334 UGFzc3dvcmQ6
dGVzdHBhc3N3b3Jk
#235 2.7.0 Authentication successful


Test SMTP with SSL
# encode credentials
perl -MMIME::Base64 -e 'print encode_base64("\000test\@example.com\000testpassword")'           
# got this string: 
# AHRlc3RAZXhhbXBsZS5jb20AdGVzdHBhc3N3b3Jk

openssl s_client -starttls smtp -connect 127.0.0.1:587 -crlf -ign_eof
# Long handshakes exchanges
# 

EHLO darling
#250-mail.example.com
#250-PIPELINING
#250-SIZE 51200000
#250-ENHANCEDSTATUSCODES
#250-8BITMIME
#250 DSN

AUTH PLAIN AHRlc3RAZXhhbXBsZS5jb20AdGVzdHBhc3N3b3Jk
#235 2.7.0 Authentication successful






Antivirus test



amavis comes with samples of viruses and spam. Extract them:
cd /usr/share/doc/amavisd-new-2.8.0/test-messages
perl -pe 's/./chr(ord($&)^255)/sge' <sample.tar.gz.compl | zcat | tar xvf -


Try to send them to your test email:
sendmail -i your-address@example.com < sample-virus-simple.txt
sendmail -i your-address@example.com < sample-spam-GTUBE-junk.txt

The results will be seen in your logs (by default /var/log/maillog). To see more, change verbose in /etc/dovecot/conf.d/10-logging.conf and log-level in /etc/amavisd/amavisd.conf. 




08 Conclusion (rus)
Теперь питание компьютера можно отключить можно начинать работать с почтовым сервером, создавать домены, пользователей, алиасы и т.п. Напоследок несколько общих моментов и рекомендаций:


Для работы с конфигурационными файлами я использую git, превратив директорию /etc в репозиторий. Такой подход позволяет удобно шарить конфиги между сотрудниками техотдела и пошагово контролировать процесс настройки. В работе с такой комплексной системой, как почтовый сервер, подобная практика может сильно облегчить жизнь.
Одной из моих задач была миграция c courier на dovecot. И да, это реально. Официальная документацияwiki2.dovecot.org/Migration/Courier даёт почти всю необходимую информацию. В дополнение к ней порекомендую вручную выполнить POP3 команду UIDL на старом и новом сервере под одним и тем же пользователем с непустым почтовым ящиком, сравнивая результаты. Они должны быть одинаковыми, иначе почтовый клиент пользователя скачает всю почту из ящика заново.
Также можно по окончании миграции на новом сервере удалить ненужные папки и файлы
find . -name "courier*" -delete


Не забудьте про iptables — откройте только нужные порты и скройте от любопытных глаз служебные.
Идущий в комплекте с CentOS логгер rsyslog мне никогда не нравился, и я его меняю на syslog-ng из EPEL. Кстати, при непосредственном участии syslog-ng рисуется график из шестой главы — сбор и запись статистики ведётся путём парсинга логов.
Таблица vacation создаётся, но в dovecot не поставлен плагин Sieve и «автоответчика» нет. Поэтому таблица — просто задел «на будущее». Гайд и так гигантский получился.

Почти полную сборку конфигурационных файлов я выложил на гитхаб, чтобы при необходимости можно было получить доступ к конфигурационным файлам не по кусочкам, а целиком. В этой статье файлы редактировались постепенно, имитируя такую же постепенную настройку всех систем. В подавляющем большинстве руководств файлы давались сразу и у читателя не получалось составить представление что за чем идёт и как связывается. Я постарался от этого избавиться, показывая поэтапность настройки. Авось, кому-то да пригодится.

Ещё раз подчеркну, что это начальный этап установки. Даже если не считать подключение мониторинга, который крайне желателен на почтовом сервере, работы ещё непочатый край. Однозначно потребуется тщательная настройка антиспам политики; если планируется использовать дополнительные релеи, то потребуется доработка запросов; обязательно будет нужно выверять параметры ограничений и т.п.

Но в остальном на этом пробу пера в написании масштабного руководства можно считать завершённой. В планах есть публикация материала уже не для «начинающих» и не по почтовому серверу, но я решил потренироваться на кошках. Кто дочитал до этого места… гм… завидую вашему терпению, но разбивать публикацию на несколько частей представлялось нецелесообразным.