001.mkd

maildb=# select * from users;
 userid  |   domain    |                     password                     |         home         | uid | gid 
---------+-------------+--------------------------------------------------+----------------------+-----+-----
 test    | example.com | yMAq2mkG/JG8MFoU10vawpUHQQj5HglWlQoQzBaLU8IQcQnP | example.com/test/    |   8 |   8
 testbis | example.com | tEoDe9rt6PgJ9xFkDK9f90r/W7i46M8GenGpdoSZH/I5gVEy | example.com/testbis/ |   8 |   8
(2 rows)

# postmap -q [email protected] pgsql://etc/postfix/mailbox_maps.cf 
example.com/test/

dovecot-sql.conf

# Database driver: mysql, pgsql, sqlite
driver = pgsql

connect = host=localhost dbname=maildb user=mailuser password=mailpassword

# Default password scheme.
#
# List of supported schemes is in
# http://wiki.dovecot.org/Authentication/PasswordSchemes
#
# SSHA256 = Salted SHA256 sum of the password stored in base64. (v1.2 and later).
default_pass_scheme = SSHA256

password_query = SELECT userid || '@' || domain AS user, password \
  FROM users WHERE userid = '%n' AND domain = '%d'

user_query = SELECT '/var/mail/' || home, uid, gid, 'maildir:/var/mail/%d/%n' as mail FROM users WHERE userid = '%n' AND domain = '%d'

dovecot.conf

auth_debug_passwords = yes                                                                                                                                   
auth_debug = yes
auth_verbose = yes
protocols = imap imaps
log_timestamp = "%Y-%m-%d %H:%M:%S "
mail_privileged_group = mail
mail_location = maildir:/var/mail/%d/%u/
first_valid_uid=8
  
auth default {
  mechanisms = plain
  userdb sql {
    args = /etc/dovecot/dovecot-sql.conf
  }
  userdb passwd {
  }
  passdb  sql {
    args = /etc/dovecot/dovecot-sql.conf
  }
  passdb pam {
  }
  socket listen {
    client {
      mode = 0660
      user = postfix
      group = postfix
      path = /var/spool/postfix/private/auth
    }
  }
}

dump.sql

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.users_id_seq OWNER TO postgres;

--
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('users_id_seq', 5, true);


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: users; Type: TABLE; Schema: public; Owner: mailuser; Tablespace: 
--
CREATE TABLE users (
    userid character varying(128) NOT NULL,
    domain character varying(128) NOT NULL,
    password character varying(64) NOT NULL,
    home character varying(255) NOT NULL,
    uid integer NOT NULL,
    gid integer NOT NULL,
    id integer DEFAULT nextval('users_id_seq'::regclass) NOT NULL
);


ALTER TABLE public.users OWNER TO mailuser;

--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: mailuser
--

COPY users (userid, domain, password, home, uid, gid, id) FROM stdin;
test    example.com     yMAq2mkG/JG8MFoU10vawpUHQQj5HglWlQoQzBaLU8IQcQnP        example.com/test/       8       8       3
testbis example.com     tEoDe9rt6PgJ9xFkDK9f90r/W7i46M8GenGpdoSZH/I5gVEy        example.com/testbis/    8       8       4
miao    example.com     DykyZ9HtDNPSJIGCK2lh21uHXlcwQc+ND05JalZhOF5mOWhJMGlaNA==        miao/example.com        8       8       5
\.


--
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: mailuser; Tablespace: 
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- Name: users_id_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON SEQUENCE users_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE users_id_seq FROM postgres;
GRANT ALL ON SEQUENCE users_id_seq TO postgres;
GRANT ALL ON SEQUENCE users_id_seq TO mailuser;

--
-- Name: users; Type: ACL; Schema: public; Owner: mailuser
--

REVOKE ALL ON TABLE users FROM PUBLIC;
REVOKE ALL ON TABLE users FROM mailuser;
GRANT ALL ON TABLE users TO mailuser;


--
-- PostgreSQL database dump complete
--

mailbox_domains.cf

user            = mailuser
password        = mailpassword
dbname          = maildb
#hosts          = /var/run/postgresql
query           = SELECT domain FROM users WHERE domain='%s'

mailbox_maps.cf

user = mailuser
password = mailpassword                                                                                                                                      
hosts = localhost
dbname = maildb
query = SELECT home FROM users WHERE userid=split_part('%s', '@', 1) and domain=split_part('%s', '@', 2);

mailmanager.py

#!/usr/bin/env python
from hashlib import sha256
import sys
import os
import string
import base64
from peewee import *

database = PostgresqlDatabase('maildb', **{'host': 'localhost', 'user': 'mailuser'})

class UnknownFieldType(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class Users(BaseModel):
    domain = CharField()
    gid = IntegerField()
    home = CharField()  
    password = CharField()
    uid = IntegerField()
    userid = CharField()

    class Meta:
        db_table = 'users'
# lines below from https://bitbucket.org/tarek/bugbro/src/b042d7640067/bugbro/util.py
_SALT_LEN = 8

def randchar(chars=string.digits + string.letters):
    pos = int(float(ord(os.urandom(1))) * 256. / 255.)
    return chars[pos % len(chars)]
def _gensalt(): 
    """Generates a salt"""
    return ''.join([randchar() for i in range(_SALT_LEN)])


def ssha256(password, salt=None):
    """Returns a Salted-SHA256 password"""
    if salt is None:   
        salt = _gensalt()
    ssha = base64.b64encode(sha256(password + salt).digest()
                               + salt).strip()
    return "%s" % ssha

if __name__ == "__main__":
.       if sys.argv[1] == "list":
.       .       users = Users.select()
.       .       for u in users:
.       .       .       print "%s@%s\t%s\t%s" % (u.userid, u.domain, u.home, u.password,)
.       elif sys.argv[1] == "create" and len(sys.argv) == 4:
.       .       userid, domain = sys.argv[2].split("@")
.       .       user = Users.create(
.       .       .       userid=userid,
.       .       .       domain=domain,
.       .       .       home="%s/%s/" % (userid, domain,),
.       .       .       password=ssha256(sys.argv[3]),
.       .       .       uid=8,
.       .       .       gid=8
.       .       )
.       .       user.save()
.       else:
.       .       print """usage: %s [list | create <email> <password>]
.       .       
.       .       """ % sys.argv[0]

main.cf

# Debian specific:  Specifying a file name will cause the first
# line of that file to be used as the name.  The Debian default
# is /etc/mailname.
#myorigin = /etc/mailname

smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
biff = no

# appending .domain is the MUA's job.
append_dot_mydomain = no

# Uncomment the next line to generate "delayed mail" warnings
#delay_warning_time = 4h

readme_directory = no

# TLS parameters
smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
smtpd_use_tls=yes
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache

# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
# information on enabling SSL in the smtp client.

myhostname = ghost-in-the-shell
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases

virtual_transport = virtual
virtual_minimum_uid = 8
virtual_gid_maps = static:8
virtual_uid_maps = static:8
virtual_mailbox_base = /var/mail
virtual_mailbox_maps = proxy:pgsql:/etc/postfix/mailbox_maps.cf
virtual_mailbox_domains = proxy:pgsql:/etc/postfix/mailbox_domains.cf

mail_spool_directory = /var/mail/
mydestination = ghost-in-the-shell, localhost.localdomain, , localhost
relayhost = 
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all

# interface with dovecot and SASL
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination

roundcube

server {
       listen 80;

       server_name www.example.com example.com;
       root /var/www/roundcubemail-0.7.2/;

       index index.php index.html;

       # Deny all attempts to access hidden files such as .htaccess, .htpasswd, .DS_Store (Mac).
       location ~ /\. {
                deny all;
                access_log off;
                log_not_found off;
       }

       location ~ \.php$ {
                try_files $uri =404;
                include /etc/nginx/fastcgi_params;
                fastcgi_pass 127.0.0.1:9000;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_index index.php;
       }
}