Leister Mailserver with PostgreSQL
For several years I’ve managed my own mailserver for the simple reason that it provides the freedom I desire. Mainly because I think that mailboxes out there are pretty expensive and I’m managing mail accounts for other people as well. The downside of having your own mailserver is that you have to maintain it yourself, so be aware of what you’re getting yourself into. Even though I already had my mailserver going I stumbled upon a tutorial from Thomas Leister ( https://thomas-leister.de/mailserver-debian-buster/ ) which is a pretty good one. It’s especially helpful if you intend to learn more about the technical backgrounds of used technologies. This tutorial introduced me to RSpamd which I consider to be way better than SpamAssassin (easier configurable and it provides a nice frontend to monitor the scoring of emails).
However the setup for the mailserver assumes the use of Mysql . Nothing wrong with that but I prefer to use PostgreSQL and I didn’t intend to install Mysql for this purpose (not even as a docker container).
So here you can find a simple repository with some changes to install the server using PostgreSQL:
This repository provides configurations with changed queries for the use of PGSQL.
However there is one significant difference for the table accounts:
CREATE TABLE IF NOT EXISTS accounts (
id INTEGER NOT NULL,
username VARCHAR(64) NOT NULL,
domain VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
quota INTEGER DEFAULT 0,
enabled BOOLEAN DEFAULT false,
sendonly BOOLEAN DEFAULT false,
fullusername VARCHAR(128) NOT NULL, /* HERE'S THE DIFFERENCE */
PRIMARY KEY (id),
CONSTRAINT uk_accounts__username_domain UNIQUE (username, domain),
CONSTRAINT fk_domain FOREIGN KEY (domain) REFERENCES domains (domain)
);
The field fullusername can be set by CONCAT(username, ‘@’, domain). The reason for this addition is that some webfrontends for mailservers (such as Rainloop ) don’t allow to specify the query to select a user (f.e. necessary if a user wants to change his/her password through the webfrontend). You can drop the support of the property fullusername and use the field username instead. If you do so you should find a webfrontend which allows to specify a query to select a user or you have to live with the fact that you can’t support multiple domains properly.