Author Archive

Secure connections

July 17th, 2017 No comments

Since commit 5881ed4, there is now an Encrypted session tri-state checkbox in the login dialog, to be certain that the connection to the mail database is encrypted.

When the checkbox is neither checked or unchecked (it’s in the third state), it means to use the default, which generally consists of trying an encrypted connection first, and if that’s not accepted by the server, an unencrypted connection next.

When the checkbox is checked, it means to attemps an encrypted connection exclusively.

Finally when it’s unchecked, a non-encrypted connection is requested. This can be a good choice when the transport channel is already encrypted, such as with a VPN or an SSH tunnel, or if it’s local.

It’s still possible, as it has always been, to have finer control over encryption by setting directly sslmode as a libpq parameter in the “More parameters” text field of the dialog.
In that case, the new checkbox should be left in the “neither checked or unchecked” state, so as not to conflict with the setting in the other field.
As an alternative, the environment variable PGSSLMODE will be be taken into account if set in the environment. See Environment Variables and SSL Support in the PostgreSQL documentation for all the details.

Manually setting sslmode is necessary to use the more specific modes verify-ca or verify-full, which in addition to request an encrypted connection, require that the server-side certificate is signed by a trusted authority.

Now, what if the server is not set up to support TLS? There’s still the possibility of encrypting the connection through an SSH tunnel, provided you have a shell account on the server, or at least on a proxy server closer to the database server, and which itself can connect securely to it.

An SSH tunnel requires finding a free-to-use TCP port on the client machine running the Manitou-Mail user interface. It can be 5432, the default PostgreSQL port, if there’s no local PostgreSQL instance on that host, otherwise an unused port should be taken. For example, if using 4000, this command would do:

ssh -N -L4000:localhost:5432

Then in Manitou-Mail connection window, the host should be set to localhost and to port to 4000 through the “More parameters” field. When the user interface will connect to localhost:4000, ssh will do the rest by having the remote server connect to its own localhost at port 5432, and from then pas all the traffic between the user interface and the database through itself, all encrypted.


In addition to the encryption, this method also alleviates the need for pg_hba.conf to allow the IP address of the remote user interface, since what connects to it is the ssh server running locally on the database server itself, or close to it in the case of a gateway to the LAN.

Categories: New features, User Interface Tags:

Note to query writers about mail_status

July 8th, 2017 Comments off

The mail_status table

mail_status is a (mail_id, status) table containing the subset of the
mail that is not “current”, which in terms of status meant, technically:
status & (16+32+256) = 0

Commit de2ee18  and related commit 7804642 in the user interface remove that table
in favor of a partial index on the mail table with the expression:
(status & 32 = 0) which means exactly: “not archived”.

This might raise a few questions among users who have developed their own set of queries. Hopefully the rest of this post will answer them in advance.

Can we keep the old queries (involving mail_status) unchanged?

Yes, by creating a mail_status view emulating the old table, taking advantage of the new index:

CREATE VIEW mail_status AS
SELECT mail_id, status FROM mail WHERE status&32=0 AND status&(16+256)=0;

Simple views like that are normally inlined by the PostgreSQL optimizer, so this should perform pretty well. When in doubt, use EXPLAIN in SQL to check the execution plan.

What is the motivation behind the change ?

Mostly performance. According to EXPLAIN ANALYZE, joining against a mail_status real table populated with a few thousand messages is fast (which is why this table existed in the first place), but avoiding the join and using the partial index instead is faster.

Also mail_status was maintained by triggers on INSERT, UPDATE, DELETE, and these triggers were not free in execution time. Now they’re no longer necessary and have been removed in the above-mentioned commits.

Why is the index on status&32=0, instead of status&(16+32+256)=0 ?

For simplicity. The triggers maintaining mail_status used the latter expression, but a message with the status “sent” (256) or “trashed” (16), but not “archived”, is a bit of a weird case, because there’s generally no action pending on a message that was sent or moved into the trashcan. It’s easier to reason about this new index knowing that it partitions the mail simply between archived and not archived, matching exactly the “archived” bit in the status.
In most cases, status&32=0 is the expression that should be used to mean this message is “current”. For exact compatibility with the old expression, status&32=0 AND status&(16+256)=0 should be used, so that the PostgreSQL optimizer can use the new index.

Categories: Database, Development Tags:

Version 1.6.0 released

March 20th, 2017 Comments off

Manitou-Mail 1.6.0 is released and available to download.

This version adds operators in the searchbar (on message senders, recipients, status, dates, attachments, tags), a statistics panel with charts and exportable results, and the creation of users and groups within the interface.

The users management features also include access rights checked at the database level, and the possibility of restricting certain accounts to certain identities, using policies with PostgreSQL’s Row Level Security feature.

Pre-compiled binaries for Windows and macOs come ready-to-use with Qt-5.5 libraries (including WebKit), and binary packages for Linux Debian 8 and Ubuntu 14.04 and 16.04 are also available through the APT repository (see the download page).


If upgrading from a previous version, make sure to run the server-side command:

manitou-mgr --upgrade-schema
Categories: New features Tags:

Improvements in mail deduplication

October 4th, 2016 Comments off

The no_duplicate plugin tracks exact duplicates, precisely incoming mail files having the same SHA1 fingerprint as a previously imported mail file.

Up to now, such duplicates could be discarded by simply declaring in manitou-mdx configuration file:

incoming_preprocess_plugins = no_duplicate

But when a manitou-mail database is used to only sync new messages from an IMAP server, with hierarchical tags reflecting folders, a message move across IMAP folders is interpreted as a duplicate coming in.

It’s fine and actually desirable not to import the message again, but ideally we’d want to see it in its new folder.

The no_duplicate plugin can now do that by acting both as a incoming_preprocess_plugin and as a incoming_postprocess_plugin.
The first step recognizes the duplicate, and optionally, updates the tags of the message instance already in the database.
The second step associates the SHA1 fingerprint of a newly imported message to its unique ID, which is necessary for the optional tags update to work, if a duplicate of this message comes in the future with different tags.

The declaration taking advantage of this new feature looks like:

incoming_preprocess_plugins = no_duplicate({update_tags=>1})
incoming_postprocess_plugins = no_duplicate

For more information on manitou-mdx plugins, see the documentation.

Categories: Database, New features Tags:

Users management in the interface

September 21st, 2016 Comments off

Starting with version 1.6, Manitou-Mail will allow the creation of users and groups from within the user interface, as shown in the screenshot below:


Although PostgreSQL has merged users and groups into “roles” years ago (since version 8.1), we intentionally stick to the old terminology, because it’s easier to describe the model. Users are accounts for persons connecting to the database. Groups are entities having a set of permissions, and to which users are assigned. Users can belong to multiple groups.

Under the hood, users correspond to PostgreSQL roles having the LOGIN attribute, so they can log in (assuming proper permissions), whereas groups are roles that don’t have this attribute. Permissions are associated to groups through SQL GRANT commands.

Manitou-Mail chooses to associate permissions to groups, instead of individual users, given its focus on team work and shared mail corpuses. For a set of permissions that apply to a single person, a group will have to be created with only that user as member.

The set of permissions currently handled is shown in the snapshot below. More fined-tuned permissions will probably be added in the future depending on users needs.



Version 1.6 can be built from the git repository; pre-compiled binaries and packages will be made available soon.

Categories: New features, User Interface Tags:

Parallel import

July 18th, 2016 Comments off

Importing in parallel from a single source  is really enabled in manitou-mdx since commit 6a860e, under the following conditions:

  • parallelism is driven from the outside: manitou-mdx instances run concurrently, but don’t fork and manage child workers. Workers don’t share anything. Fortunately GNU parallel can easily handle this part.
  • the custom full text indexing is done once the contents are imported, not during the import. The reason is that it absolutely needs a cache for performance, and such a cache wouldn’t work in the share-nothing implementation mentioned above.

The previous post showed how to create a list of all mail files to import from the Enron sample database.

Now instead of that, let’s create a list splitted in chunks of 25k messages, that will be fed separately to the parallel workers:

$ find . -type f | split -d -l 25000 - /data/enron/list-

The result is 21 numbered files of 25000 lines each, except for the last one, list-20 containing 17401 lines.

The main command is essentially the same as before. As a shell variable:

cmd="mdx/script/manitou-mdx --import-list={} \
--import-basedir=$basedir/maildir \
--conf=$basedir/enron-mdx.conf \

Based on this, a parallel import with 8 workers can be launched through a single command:

ls "$basedir"/list-* | parallel -j 8 $cmd

This invocation will automatically launch manitou-mdx processes and feed them each with a different list of mails to import (through the –import-list={} argument). It will also take care that there are always 8 such running processes if possible, launching a new one when another terminates.

This is very effective, compared to a serial import. Here are the times spent to import to entire mailset (517401 messages) for various degrees of parallelism, on a small server with a Xeon D-1540 @ 2.00GHz processor (8 cores, 16 threads).



Categories: Usage Tags:

Mass-importing case: the Enron mail database

July 12th, 2016 Comments off

Importing mail messages en masse works best when fiddling a bit with the configuration, rather than pushing the mail messages into the normal feed.

As an example, we’re going to use the mails from Enron, the energy provider that famously went down in the 90s, amidst a fraud scandal.
The mail corpus has been made public by the judicial process:

It has been cleaned from all attachments, in addition to another cleaning process to remove potentially sensitive personal information, done by Nuix.

The archive format is a 423MB .tar.gz file with an MH-style layout:
– one top-level directory per account.
– inside each account, files and directories with mail folders.

It contains 3500 directories for 151 accounts, and a total of 517401 files, taking 2.6GB on disk once uncompressed.

After unpacking the archive, follow these steps to import the mailset from scratch:

1) Create the list of files

$ cd /data/enron/maildir
$ find . -type f > /data/enron/00-list-all

2) Create a database and a dedicated configuration file for manitou-mdx

# Run this as a user with enough privileges to create
# a database (generally, postgres should do)
$ manitou-mgr --create-database --db-name=enron

Create a specific configuration file with some optimizations for mass import:

$ cat enron-mdx.conf
db_connect_string = Dbi:Pg:dbname=enron;user=manitou

update_runtime_info = no
update_addresses_last = no
apply_filters = no
index_words = no

preferred_datetime = sender

update_runtime_info is set to no to avoid needlessly update timestamps in the runtime_info table for every imported message.

update_addresses_last set to no also will avoid some unnecessary writes.

apply_filters is again a micro-optimization to avoid querying for filters on every message. On the other hand, it should be left to yes if happen to have defined filters and want them to be used during this import.

index_words is key to performance. Running the full-text indexing after the import instead of during it makes it 3x faster. Also the full-text indexing as a separate process can be parallelized (more on that below).

preferred_datetime set to sender indicates that the date of a message is given by its header Date field, as opposed to the file creation time.

If we were importing into a pre-existing manitou-mdx instance running in the background, we would stop it at this point, as
several instances of manitou-mdx cannot work on the same database because of caching, except in specific circumstances (also more on that later).

3) Run the actual import command

$ cd /data/enron/maildir
$ time manitou-mdx --import-list=../00-list-all --conf=../enron-mdx.conf

On a low-end server, it takes about 70 minutes to import the 517402 messages with this configuration and PostgreSQL 9.5.

We can check with psql that all messages came in:

$ psql -d enron -U manitou
psql (9.5.3)
Type "help" for help.

enron=> select count(*) from mail;
(1 row)

4) Run the full text indexing

As it's a new database with no preexisting index, we don't have to worry about existing partitions. We let manitou-mgr index the messages with 4 jobs in parallel:

$ time manitou-mgr --conf=enron-mdx.conf --reindex-full-text --reindex-jobs=4

Output from time:

real 10m41.855s
user 28m22.744s
sys 1m8.476s

So this part of the process takes about 10 minutes.


With manitou-mgr, we can check the final size of the database and its main tables:

$ manitou-mgr --conf=enron-mdx.conf --print-size
addresses : 13.52 MB
attachment_contents : 0.02 MB
attachments : 0.02 MB
body : 684.98 MB
header : 402.45 MB
inverted_word_index : 2664.77 MB
mail : 250.12 MB
mail_addresses : 441.17 MB
mail_tags : 0.01 MB
pg_largeobject : 0.01 MB
raw_mail : 0.01 MB
words : 106.52 MB
Total database size : 4633 MB

Future posts will show how it compares to the full mailset (with attachments, 18GB of .pst files), and how to parallelize the main import itself.

Categories: Usage Tags:

Operators in the search bar

July 9th, 2016 Comments off

Until now, the search bar in the user interface did not support query
terms to search on metadata.
I’m glad to say that commits 2ddddaae and a1cbe72a add support for filtering
by date and message status right from the search bar, introducing
five operators:

  • “date:” must be followed by an iso-8601 date (format YYYY-MM-DD),
    or by a specific month (format YYYY-MM), or just a year (YYYY).
    It selects the messages from respectively that day,or month, or year.

  • “before:” has the same format but selects messages dated
    from this day/month/year or an earlier date.

  • “after:” is of course the opposite, selecting messages past
    the date that follows.

  • “is:” must be followed by a status among read,replied,forward,archived,sent.
    Criteria can be combined by using the option several times, as statuses are cumulative, not mutually exclusive,

  • “isnot:” is of course the opposite of “is”. It accepts the same arguments
    and filters out the messages that have the corresponding status bit.
    “is:” and “isnot:” can also be combined, for instance: “is:archived isnot:sent”.

A few more search bar operators are likely to be added to that list, as it’s a pretty handy and fast way to express basic queries.

Categories: New features, User Interface Tags:

Version 1.5.0 released

May 24th, 2016 Comments off

Manitou-Mail 1.5.0 is released and available to download.

The major improvement is the move from Qt4 to Qt5, an important step
to continue to benefit from Qt’s progress.

Other changes are, in the user interface:

  • Bug fixes with Unicode characters in headers when composing.
  • Fix “Fetch more” bug.
  • Fix locale bug when retrieving FP numbers from queries.
  • Fix bug interpreting certain URLs with percent-encoded chars.
  • Desktop notifications available on all platforms.
  • Russian translation added.

In manitou-mdx:

  • Reimplement rfc2047 encoding for consecutive words.
  • Add workaround for a DBD::Pg 3.x bug with utf-8 handling.
  • Fix utf-8 encoding in HTML MIME parts.
  • Fix undesirable CRLF conversion in attachments on MS-Windows.
  • Minor parsing improvements in full text indexing.
Categories: Uncategorized Tags:

Experimental Qt5 package available for Ubuntu

March 15th, 2016 Comments off

The Qt5 port of the user interface is in good shape, successful tests have been made with up to Qt5.5.

The development still happens in a branch:
(github link), but that will be merged soon into the main trunk, and the Qt4.x version will become a separate branch.

In the meantime, a binary package for Ubuntu 14.04 or 15.10 on amd64 architecture is available in an experimental repository, which can be added to /etc/apt/sources.list.d/manitou.list:

deb http://manitou/apt trusty experimental

The package is manitou-ui, version 1.5.0. It’s built with Qt-5.2.1 on Ubuntu 14.04.

# apt-cache show manitou-ui
Package: manitou-ui
Version: 1.5.0
Architecture: amd64
Maintainer: Daniel Verite
Installed-Size: 2443
Depends: libc6 (>= 2.14), libgcc1 (>= 1:4.1.1), libpq5 (>= 9.0~), libqt5core5a (>= 5.0.2), libqt5gui5 (>= 5.0.2) | libqt5gui5-gles (>= 5.0.2), libqt5network5 (>= 5.0.2), libqt5printsupport5 (>= 5.0.2), libqt5webkit5, libqt5widgets5 (>= 5.2.0), libstdc++6 (>= 4.6)
Priority: extra
Section: mail
Filename: pool/experimental/m/manitou-ui/manitou-ui_1.5.0_amd64.deb
Size: 674752
SHA256: 1974bf59cc40d1da67e7fd097b41d78707c012c29d7934610b9ff3a020c14cc4
SHA1: fa6df5ac14a33de5411f06a4139ab86dff3b8536
MD5sum: 937578411e1eff15f5763eb219a1c7a9
Description: Manitou-Mail's user interface
Qt-based GUI that acts as a front-end to a Manitou-Mail database.
Description-md5: 1fcf376b591321a77a965062e2ea0b97

Categories: Development, User Interface Tags: