Tag Archives: mail server

Postfix, Dovecot and PostGreSQL Setup

In trying to setup a simple mail server on Ubuntu for a couple of small virtual domains with limited users I came across a couple excellent articles, but they were written for use with MySQL.  I have a machine with limited resources running custom software using PostGreSQL.  Although flat files would have been acceptable, I didn’t have the same luck in breadth of documentation.  So, I decided to work with the excellent articles I found and adapt them to Postgres for my server.

The best guide I found was from Linode, and is geared to help customers setting up Email with Postfix, Dovecot, and MySQL on their excellent platform.  The second guide I found left a lot to be desired, but since it was Postgres specific I was able to take those pieces from it and adapt it to the Linode guide.  That guide was called How to setup a postfix email server with Dovecot and published as a community tutorial from DigitalOcean.  While DigitalOcean and Linode are tight competitors, Linode wins out for having the better mail server how-to.

I’m going to mainly show what changes need to be done to the Linode guide in order to make it work with Postgres.  Otherwise use that guide for complete instructions. I’ll assume you already have a Postgres server up and running on that machine.

Postgres – Creating the Database

Your first change will be under the “Creating the Database” heading.  Let’s start by using the DigitalOcean guide to create the user.  Let’s try and keep the Linode naming convention as much as possible though.

First follow the adjustments to the  /etc/postgresql/vers.ion/main/pg_ident.conf  file.

mailmap         dovecot                 mailuser
mailmap         postfix                 mailuser
mailmap         root                    mailuser

Reload postgres and setup the user.

# sudo -u postgres psql
postgres=# CREATE USER mailuser;
postgres=# GRANT CREATE ON SCHEMA public TO postgres;
postgres=# GRANT USAGE ON SCHEMA public TO postgres;
postgres=# CREATE DATABASE mailserver WITH OWNER mailuser;
postgres=# \q 
# sudo psql -U mailuser -d mailserver
postgres=# \c mailserver
CREATE SEQUENCE seq_mail_domain_id START 1;
CREATE SEQUENCE seq_mail_user_id START 1;
CREATE SEQUENCE seq_mail_alias_id START 1;

CREATE TABLE virtual_domains (
  domain_id INT2 NOT NULL DEFAULT nextval('seq_mail_domain_id'),
  domain_name varchar(50) NOT NULL,
  PRIMARY KEY (domain_id)

CREATE TABLE virtual_users (
  user_id INT2 NOT NULL DEFAULT nextval('seq_mail_user_id'),
  domain_id INT2 NOT NULL,
  password varchar(106) NOT NULL,
  email varchar(100) NOT NULL,
  PRIMARY KEY (user_id),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(domain_id) ON DELETE CASCADE

CREATE TABLE virtual_aliases (
  alias_id INT2 NOT NULL DEFAULT nextval('seq_mail_alias_id'),
  domain_id INT2 NOT NULL,
  source varchar(100) NOT NULL,
  destination varchar(100) NOT NULL,
  PRIMARY KEY (alias_id),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(domain_id) ON DELETE CASCADE

To add your domains to the database:

INSERT INTO virtual_domains (domain_name)

Do a simple select from the virtual_domains table and note the id of the domain you wish to add addresses for:

SELECT * FROM virtual_domains;

According to the postgres document we referenced earlier, you have to get the password from command line like so:

# doveadm pw -s sha512 -r 100
Enter new password: ...
Retype new password: ...

Now you need to add some email addresses like so:

INSERT INTO virtual_users
  ( domain_id, password , email)
  ( '1', '{SHA512}.............................................................==', 'postmaster@example.com'),
  ( '1', '{SHA512}.............................................................==', 'supah_user@example.com');

Now, if you created your user according to the digital ocean document there is no password.  You’ll have to add one for your user like this:

ALTER ROLE mailuser WITH PASSWORD 'supersecretPaSSvvord';

That should be it for your direct Postgres editing. You can follow all the testing they show for MySQL since it looks like standard SQL.


You can follow the Linode guide for postfix, only needing to make changes in step 8.  Just change in main.cf where they name their files by prepending mysql: to pgsql: and changing the filenames. Everything else there is the same.

  #Virtual domains, users, and aliases
  virtual_mailbox_domains = pgsql:/etc/postfix/pgsql-virtual-mailbox-domains.cf
  virtual_mailbox_maps = pgsql:/etc/postfix/pgsql-virtual-mailbox-maps.cf
  virtual_alias_maps = pgsql:/etc/postfix/pgsql-virtual-alias-maps.cf

Watch for these filenames further down as you’ll have to remember the change.  The actualy content of the files including the queries themselves from steps 11 on are the same as for MySQL.

The Linode document had a great line to test your db connection in step 15 and on.  Issue this from the shell:

postmap -q cmydomain.com pgsql:/etc/postfix/pgsql-virtual-mailbox-domains.cf


Keep in mind when working with dovecot configuration that if you don’t intend to use pop3, don’t enable it.

In step 25 specify pgsql instead of mysql in the driver line.

In step 27, Note in the dovecot-sql.conf.ext file your default_pass_scheme using the method above from the DigitalOcean document is SHA512,  not SHA512-CRYPT as in the Linode document.


That’s it! Everything else should be the same.  Follow the Linode guide to test your connections, make sure your firewall is open, and if you have any problems they have an excellent troubleshooting guide linked from the original article.  Enjoy!

Search All Zimbra Mailboxes (Community Edition)

Recently a phishing email got past our spam filter and we wanted to determine the extent to which users would be impacted.  Zimbra’s admin interface in the Community Edition doesn’t have the ability to search through all emails in a convenient way, so we started scouring the web for solutions.  That’s when I came across this post at the Zimbra forum that contained this code:

zmprov gaa |awk '{print "zmmailbox -z -m "$1" search <SEARCHSTRING>' |sh -v

However, this didn’t work for us, and you can see why in the code, there are some quotes and curly braces missing.  Here’s what worked for us on Zimbra Community Edition installed on Ubuntu Server.

Log in and su to the zimbra user.  If you don’t and you remain root, the paths to the binaries will probably be wrong and need to be specified as absolute paths from the root directory.

In the following command, change SEARCH STRING to the text you want to change.

zmprov -l gaa |awk '{print "zmmailbox -z -m "$1" search \"SEARCH STRING\" "}' |sh -v

Zmprov retrieves a list of all user mailboxes on your system, pipes that into awk which then creates the command which uses zmmailbox to search for the specified text in each of the mailboxes returned from zmprov, then pipes that into sh (shell) which executes the formatted command.

The only problem with this command is that it prints the command, along with a line that indicates the number of results returned – for every user.  So if the user didn’t have any results, you still get a line printed.  If they had results, a line indicating which email contained them is printed.  This could be improved by returning only pertinent results.  If you are executing this on command line make sure your buffer is large enough to store all results for the amount of users you have.

With approximately 1100 active accounts and 120GB of mailbox data, this command took about 3 hours to execute under normal daily load. The command itself did not appear to increase system load appreciably during execution.