Archive

Archive for July, 2017

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 dbserver.example.org

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.

manitou-connect-ssh

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: