Postcot + Postfix + Dovecot + Postgresql

1. create database

    su - postgres
    psql template1
    

create role postcot with login nocreatedb password 'postk012'; create database postcotdb with owner=postcot encoding='UTF8';

2. create database table

    PGPASSWORD=postk012 psql -h localhost -U postcot postcotdb
    

create table postcot_users ( pc_id serial, -- int primary key default nextval('postcot_users_seq'), pc_address varchar(250) not null, -- email address pc_domain varchar(250), -- domain part pc_type varchar(250), -- N or null for user disabled (inbox and login),E email,L alias/mailing list pc_password varchar(250), -- account: email/password pc_alias_csv text, -- alias/mailing list pc_mgrdom_csv text, -- managed domanins (only admin) or * or null pc_mgrdom_nr int, -- address limit pc_mgrquota_kb int, -- managed domanins quota pc_mgrusage_kb int, -- managed domanins disk usage pc_diskusage_kb int, -- filesystem du pc_diskquota_kb int, -- my quota pc_note text, pc_maddr varchar(250), -- last modifier pc_mtime timestamp ); create unique index pc_address_idx on postcot_users(pc_address); create index pc_domain_idx on postcot_users(pc_domain); insert into postcot_users(pc_address,pc_domain,pc_type,pc_password,pc_mgrdom_csv,pc_mgrdom_nr,pc_mtime) values ('postmaster@example.it','example.it','E',md5('password'),'*',100,current_timestamp);

3. configure apache and php

    apt-get install libapache2-mod-php5 php5-pgsql php5-pdo
    vi /etc/apache2/sites-available/postcot.inc
    

Alias /postcot /home/webapps/postcot <Directory /home/webapps/postcot> Options -Indexes FollowSymLinks MultiViews AllowOverride None Order allow,deny allow from all php_value default_charset "utf-8" php_flag short_open_tag On php_admin_value open_basedir /home/webapps/postcot </Directory>

    vi /etc/apache2/sites-available/example
    

<VirtualHost *> ServerName example.it ... Include /etc/apache2/sites-available/postcot.inc ... </VirtualHost>

4. configure postcot and run

    vi pc-config.inc.php
    
    open browser to
	http://postcot.lan/postcot
    login as
	postmaster@example.it / password
    

5. configure postfix

create vmail system user as owner of vmail directory (mailbox repository /home/vmail)

    groupadd -g 5000 vmail
    useradd -m -u 5000 -g 5000 -s /bin/false vmail
    

install postfix in bundle with dovecot (ubuntu)

    apt-get install dovecot-postfix
    apt-get install postfix-pgsql
    cd /etc/postfix
    

select all active domains

    vi vhosts-sql.cf
    

#hosts = localhost user = postcot password = postk012 dbname = postcotdb query = SELECT pc_domain FROM postcot_users WHERE pc_domain='%s' and pc_type in('E','L') - pgsql: comment #host for fast unix socket
- mysql: use 127.0.0.1 to avoid "postfix/trivial-rewrite[26321]: fatal: mysql:/etc/postfix/valias-sql.cf(0,lock|fold_fix): table lookup problem"

select all active email addresses

    vi vmailbox-sql.cf
    

#hosts = localhost user = postcot password = postk012 dbname = postcotdb query = SELECT '%d/%u' FROM postcot_users WHERE pc_address='%s' and pc_type='E' - %s addr %u user %d domain
- select 1 if transport is dovecot lda

select all active email aliases

    vi valias-sql.cf
    

#hosts = localhost user = postcot password = postk012 dbname = postcotdb query = SELECT pc_alias_csv FROM postcot_users WHERE pc_address='%s' and pc_type='L' configure postfix virtual users

    vi main.cf
    

# utenti virtuali virtual_mailbox_base = /home/vmail virtual_uid_maps = static:5000 virtual_gid_maps = static:5000 virtual_mailbox_domains = pgsql:/etc/postfix/vhosts-sql.cf virtual_mailbox_maps = pgsql:/etc/postfix/vmailbox-sql.cf virtual_alias_maps = pgsql:/etc/postfix/valias-sql.cf # dovecot lda dovecot_destination_recipient_limit = 1 virtual_transport = dovecot mailbox_command = /usr/lib/dovecot/deliver -c /etc/dovecot/conf.d/01-mail-stack-delivery.conf -m "${EXTENSION}" smtpd_recipient_restrictions = reject_unauth_pipelining, permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination, reject_rbl_client sbl.spamhaus.org, reject_rbl_client bl.spamcop.net - 5000 is vmail uid
- reject_unauth_destination reject unknown email recipient

enable dovecot local delivery (support quota and sieve... vacation)

    vi master.cf
    

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

6. configure dovecot

    apt-get install dovecot-pgsql
    cd /etc/dovecot
    

select all mailbox and aliases

    vi dovecot-sql.conf.ext
    

driver = pgsql connect = host=localhost dbname=postcotdb user=postcot password=postk012 default_pass_scheme = PLAIN-MD5 password_query = SELECT pc_password AS password, pc_address AS username FROM postcot_users WHERE pc_address='%u' and pc_type in ('L','E'); - see http://wiki2.dovecot.org/AuthDatabase/SQL

    vi conf.d/auth-sql.conf.ext
    

passdb { driver = sql args = /etc/dovecot/dovecot-sql.conf.ext } # fast, accept all user (postfix) userdb { driver = static args = uid=5000 gid=5000 home=/home/vmail/%d/%n allow_all_users=yes }

    vi conf.d/01-mail-stack-delivery.conf
    

protocols = imap pop3 sieve ... mail_home = /home/vmail/%d/%n mail_location = maildir:~/Maildir ...

    vi conf.d/10-auth.conf
    

!include auth-sql.conf.ext test configuration:

    dovecot -n
    

7. configure system

www-data (user group) can run disk usage command (du)

    visudo
    

%www-data ALL = (vmail)NOPASSWD: /usr/bin/du

8. end