=======================
HowTo/DovecotPostgresql
=======================

.. warning::

    This document has been taken out of the old wiki and
    has not yet been updated.

Introduction
============

Gluing together virtual user/domain support for:

-  Debian (These instructions are for Sid)

-  Postfix 2 with SMTP AUTH

-  SASL2 with libpam-pgsql for Postfix

-  PostgreSQL

-  Dovecot (dovecot-pop3d and or dovecot-imapd)

Note(s)
=======

On Debian, the package dovecot-pgsql do not yet include postgresql as a
dependency to pull it if not installed.

Software Installation
=====================

For Debian:

::

   apt-get install postfix-pgsql sasl2-bin libsasl2-modules postgresql libpam-pgsql dovecot-pgsql dovecot-imapd dovecot-pop3d

Configuring PostgreSQL
======================

Edit /etc/postgresql/pg_hba.conf to accept password authentication for
localhost:

::

   host    all         all         127.0.0.1         255.255.255.255   password

Then create the database:

::

   sudo su postgres
   createdb mails
   psql mails

Create tables:

::

   CREATE TABLE transport (
     domain VARCHAR(128) NOT NULL,
     transport VARCHAR(128) NOT NULL,
     PRIMARY KEY (domain)
   );
   CREATE TABLE users (
     userid VARCHAR(128) NOT NULL,
     password VARCHAR(128),
     realname VARCHAR(128),
     uid INTEGER NOT NULL,
     gid INTEGER NOT NULL,
     home VARCHAR(128),
     mail VARCHAR(255),
     PRIMARY KEY (userid)
   );
   CREATE TABLE virtual (
     address VARCHAR(255) NOT NULL,
     userid VARCHAR(255) NOT NULL,
     PRIMARY KEY (address)
   );
   create view postfix_mailboxes as
     select userid, home||'/' as mailbox from users
     union all
     select domain as userid, 'dummy' as mailbox from transport;
   create view postfix_virtual as
     select userid, userid as address from users
     union all
     select userid, address from virtual;

Create separate users for read and write accesses. Postfix and Dovecot
needs only read access. You may want to use the writer user for your own
purposes.

::

   CREATE USER mailreader PASSWORD 'secret';
   grant select on transport, users, virtual, postfix_mailboxes, postfix_virtual to mailreader;
   create user mailwriter password 'secret';
   grant select, insert, update, delete on transport, users, virtual, postfix_mailboxes, postfix_virtual to mailwriter;

Here's a few example values:

::

   insert into transport (domain, transport) values ('domain.org', 'virtual:');
   insert into transport (domain, transport) values ('foo.org', 'virtual:');
   insert into users (userid, uid, gid, home) values ('user@domain.org', 1001, 1001, 'domain.org/mails/user');
   insert into users (userid, uid, gid, home) values ('user2@domain.org', 1001, 1001, 'domain.org/mails/user2');
   insert into users (userid, uid, gid, home) values ('user@foo.org', 1002, 1002, 'foo.org/mails/user');
   insert into virtual (address, userid) values ('foo@foo.org', 'user@foo.org');

Above examples assume that you'd use separate system UID and GID for
each domain. I think that's good enough compromise between simplicity
and security. The UIDs and GIDs aren't required to be in /etc/passwd and
/etc/group, "ls -l" will just show them in numeric form in that case.

In this case, the virtual domain "domain.org" and "foo.org" will define
virtual: as the transport. Please note in this case, virtual service
from postfix will deliver the mail and ignore all virtual_transport
config settings.

If you prefer dovecot as the transport, make sure 'dovecot' or something
like ``lmtp:unix:private/dovecot-lmtp`` is returned from the transport_maps
query.

In order to make virtual_transport setting effective, leave
transport_maps as default.

Configuring Postfix
===================

PostgreSQL configuration in main.cf:

::

   transport_maps = pgsql:/etc/postfix/transport.cf
   virtual_uid_maps = pgsql:/etc/postfix/uids.cf
   virtual_gid_maps = pgsql:/etc/postfix/gids.cf
   virtual_mailbox_base = /home
   virtual_mailbox_maps = pgsql:/etc/postfix/mailboxes.cf
   virtual_maps = pgsql:/etc/postfix/virtual.cf
   mydestination = $mydomain, $myhostname

Note that we've set virtual_mailbox_base to /home, which means that it's
prefixed to all home directories in SQL database.

SASL2 authentication configuration in main.cf:

::

   smtpd_recipient_restrictions = permit_sasl_authenticated, permit_mynetworks, reject_unauth_destination
   smtpd_sasl_auth_enable = yes
   smtpd_sasl_security_options = noanonymous
   smtpd_sasl_local_domain = domain.org
   smtp_sasl_auth_enable = no

And /etc/postfix/sasl/smtpd.conf:

::

   pwcheck_method: saslauthd
   saslauthd_path: /etc/mux

/etc/postfix/transport.cf:

::

   user=mailreader
   password=secret
   dbname=mails
   table=transport
   select_field=transport
   where_field=domain
   hosts=localhost

/etc/postfix/uids.cf:

::

   user=mailreader
   password=secret
   dbname=mails
   table=users
   select_field=uid
   where_field=userid
   hosts=localhost

/etc/postfix/gids.cf:

::

   user=mailreader
   password=secret
   dbname=mails
   table=users
   select_field=gid
   where_field=userid
   hosts=localhost

/etc/postfix/mailboxes.cf:

::

   user=mailreader
   password=secret
   dbname=mails
   table=postfix_mailboxes
   select_field=mailbox
   where_field=userid
   hosts=localhost

/etc/postfix/virtual.cf:

::

   user=mailreader
   password=secret
   dbname=mails
   table=postfix_virtual
   select_field=userid
   where_field=address
   hosts=localhost

Configuring SASL2
=================

We want to use PAM authentication via saslauthd. SMTP process runs
chrooted into /var/spool/postfix and we have to be able to communicate
to saslauthd via UNIX socket, so create the socket inside the chroot.

In Debian you can configure it in /etc/default/saslauthd:

::

   START=yes
   MECHANISMS=pam
   PARAMS="-m /var/spool/postfix/etc"

As of version 2.1.19 of SASL you also need to add the -r parameter in
order to authenticate with an email address (containing a @) as user id:

::

   PARAMS="-r -m /var/spool/postfix/etc"

(This parameter will probably break saslauthd if used with previous
versions.)

Configure libpam-pgsql in /etc/pam_pgsql.conf:

::

   database = mails
   host = localhost
   user = mailreader
   password = secret
   table = users
   user_column = userid
   pwd_column = password
   #expired_column = acc_expired
   #newtok_column = acc_new_pwreq
   pw_type = crypt
   #debug

And create /etc/pam.d/smtp:

::

   auth        required    pam_pgsql.so
   account     required    pam_pgsql.so
   password    required    pam_pgsql.so

libsasl2-modules install a lot of plugins which you most likely don't
need and which don't even work with PAM. You mostly just need PLAIN and
possibly LOGIN authentication. I'm not sure if there's any pretty way to
select only them, but one evil way is to just delete others:

::

   cd /usr/lib/sasl2
   rm -f libcrammd5.* libdigestmd5.* libsasldb.* libotp.* libntlm.* libanonymous.*

The better way is to put in /etc/postfix/sasl/smtpd.conf the following
line:

::

   mech_list: login plain

Where mech_list is a list of all the mechanism names to enable.

Configuring Dovecot
===================

In dovecot.conf, set:

::

   mail_location = maildir:~/

   passdb {
      driver = sql
      args = /usr/local/etc/dovecot-sql.conf 
   }

   userdb {
      driver = sql
      args = /usr/local/etc/dovecot-sql.conf
   }
 
And create /usr/local/etc/dovecot-sql.conf:

::

   driver = pgsql
   connect = host=localhost dbname=mails user=mailreader password=secret
   default_pass_scheme = CRYPT
   password_query = SELECT userid as user, password FROM users WHERE userid = '%u'
   user_query = SELECT '/home/'||home AS home, uid, gid FROM users WHERE userid = '%u'

Restart
=======

Finally remember to restart everything before trying to figure out why
nothing is working:

::

   /etc/init.d/saslauthd restart
   /etc/init.d/postgresql restart
   /etc/init.d/postfix restart
   /etc/init.d/dovecot restart