Dovecot CE
Search K

Appearance

Virtual Users with PostgreSQL ​

WARNING

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

Document describing 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

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

Software Installation ​

For Debian:

console
$ 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:

console
$ sudo su postgres
$ createdb mails
$ psql mails

Create tables:

sql
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.

sql
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:

sql
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');

The 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 ​

transport_maps = pgsql:/etc/postfix/transport.cf
virtual_uid_maps = pgsql:/etc/postfix/uids.cf
virtual_gid_maps = pgsql:/etc/postfix/gids.cf
# Note that we've set virtual_mailbox_base to /home, which means that it's
# prefixed to all home directories in SQL database.
virtual_mailbox_base = /home
virtual_mailbox_maps = pgsql:/etc/postfix/mailboxes.cf
virtual_maps = pgsql:/etc/postfix/virtual.cf
mydestination = $mydomain, $myhostname

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
pwcheck_method: saslauthd
saslauthd_path: /etc/mux
user=mailreader
password=secret
dbname=mails
table=transport
select_field=transport
where_field=domain
hosts=localhost
user=mailreader
password=secret
dbname=mails
table=users
select_field=uid
where_field=userid
hosts=localhost
user=mailreader
password=secret
dbname=mails
table=users
select_field=gid
where_field=userid
hosts=localhost
user=mailreader
password=secret
dbname=mails
table=postfix_mailboxes
select_field=mailbox
where_field=userid
hosts=localhost
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:

console
$ 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 ​

mail_location = maildir:~/

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

userdb {
  driver = sql
  args = /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:

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

Copyright © Open-Xchange GmbH