Email: tutorial.txt

#!/bin/sh

# Database configuration
echo 'postgresql_enable="YES"' >> /etc/rc.conf
/usr/local/etc/rc.d/postgresql initdb
/usr/local/etc/rc.d/postgresql start

createdb -U pgsql mail
createuser -U pgsql -l -R -S -D postfix
createuser -U pgsql -l -R -S -D courier

cat > /tmp/basic.sql <<EOF
CREATE TABLE virtual_aliases (
        source                  text            NOT NULL        PRIMARY KEY,
        destination             text            NOT NULL
);

COMMENT ON TABLE virtual_aliases IS 'A table for allowing users to maintain their own forwarding addresses';
CREATE TABLE virtual_mailboxes (
        email                   text            NOT NULL        PRIMARY KEY,
        passwd                  text            NOT NULL,
        gecos                   text,
        homedir                 text            NOT NULL
);

COMMENT ON TABLE virtual_mailboxes IS 'Store account information here. See passwd(5). UID/GID and shell are hardcoded in the configuration files';

CREATE TABLE virtual_domains (
        domain_name             text            NOT NULL        PRIMARY KEY
);

COMMENT ON TABLE virtual_domains IS 'Store the list of hosted domains here. If a domain is not listed, we will not be able to accept and deliver mail for that domain on our systems';

INSERT INTO virtual_domains (domain_name) VALUES ('example.com');
INSERT INTO virtual_domains (domain_name) VALUES ('example.org');

INSERT INTO virtual_aliases VALUES ('postmaster@example.org', 'admin@example.org');
INSERT INTO virtual_aliases VALUES ('abuse@example.org', 'admin@example.org');

INSERT INTO virtual_mailboxes (email, passwd, gecos, homedir) VALUES ('admin@example.org', 'password', 'The admin user', 'example.org/admin/');
INSERT INTO virtual_mailboxes (email, passwd, gecos, homedir) VALUES ('user1@example.org', 'password', 'The normal user', 'example.org/user1/');

GRANT SELECT ON virtual_mailboxes TO postfix;
GRANT SELECT ON virtual_domains TO postfix;
GRANT SELECT ON virtual_aliases TO postfix;

GRANT SELECT ON virtual_mailboxes TO courier;
EOF
psql -U pgsql mail < /tmp/basic.sql

# System configuration
echo "Create a user named vmail. This script assumes the existence of the
vmail username later"
/usr/sbin/adduser

uid=`id -u vmail`
gid=`id -g vmail`

# Postfix maps
cat > /usr/local/etc/postfix/virtual_mailboxes.cf <<EOF
user = postfix
dbname = mail
query = SELECT homedir||'/Maildir/' FROM virtual_mailboxes WHERE email = '%s'
hosts = 127.0.0.1:5432
EOF

# Test your map
postmap -q 'admin@example.org' pgsql:/usr/local/etc/postfix/virtual_mailboxes.cf
# If the above line doesn't print "example.org/admin" without the quotes
# stop to debug your configuration.
postmap -q 'admin@example.com' pgsql:/usr/local/etc/postfix/virtual_mailboxes.cf
# Should print nothing

cat > /usr/local/etc/postfix/virtual_aliases.cf <<EOF
user = postfix
dbname = mail
query = SELECT destination FROM virtual_aliases WHERE source = '%s'
hosts = 127.0.0.1:5432
EOF

# Test your map
postmap -q 'postmaster@example.org' pgsql:/usr/local/etc/postfix/virtual_aliases.cf
# If the above line doesn't print "admin@example.org" without the quotes
# Stop to debug your configuration.
postmap -q 'admin@example.com' pgsql:/usr/local/etc/postfix/virtual_aliases.cf
# Should print nothing

cat > /usr/local/etc/postfix/virtual_mailbox_domains.cf <<EOF
user = postfix
dbname =  mail
query = SELECT domain_name FROM virtual_domains WHERE domain_name = '%s'
hosts = 127.0.0.1:5432
EOF 

# Test your map
postmap -q 'example.org' pgsql:/usr/local/etc/postfix/virtual_mailbox_domains.cf
# If the above line doesn't print "admin@example.org" without the quotes
# Stop to debug your configuration.
postmap -q 'example.net' pgsql:/usr/local/etc/postfix/virtual_mailbox_domains.cf
# Should print nothing

# Postfix configuration
postconf -e 'virtual_mailbox_base = /home/vmail'
postconf -e "virtual_uid_maps = static:$uid"
postconf -e "virtual_gid_maps = static:$gid"
postconf -e 'virtual_mailbox_domains = pgsql:/usr/local/etc/postfix/virtual_mailbox_domains.cf'
postconf -e 'virtual_mailbox_maps = pgsql:/usr/local/etc/postfix/virtual_mailboxes.cf'
postconf -e 'virtual_alias_maps = pgsql:/usr/local/etc/postfix/virtual_aliases.cf'

# Courier authentication configuration
cat > /usr/local/etc/authlib/authpgsqlrc <<EOF
PGSQL_HOST                      localhost
PGSQL_PORT                      5432
PGSQL_USERNAME                  courier
PGSQL_DATABASE                  mail
PGSQL_USER_TABLE                virtual_mailboxes
PGSQL_CLEAR_PWFIELD             passwd
PGSQL_UID_FIELD                 $uid
PGSQL_GID_FIELD                 $gid
PGSQL_LOGIN_FIELD               email
PGSQL_HOME_FIELD                '/home/vmail/' || homedir || '/'
PGSQL_NAME_FIELD                gecos
EOF

# Let the services know something has changed
/usr/local/etc/rc.d/postfix restart
/usr/local/etc/rc.d/courier-authdaemond restart

# Send a mail to admin@example.org
# This creates the maildir for the user, after which the user
# can login normally.
# If the Maildir is not created, then logins will fail.
echo "Welcome, humble user." | mail -s 'Welcome mail' admin@example.org

# Test via a MUA
# Login: admin@example.org 
# Password: password

# Send a mail to an alias
echo "Welcome, all-powerful postmaster." | mail -s 'Welcome mail' postmaster@example.org

# Test via a MUA

# See your mail delivery logs for how aliases and account delivery shows up differently.