dbmail / dbmail

DBMail: fast and scalable email services

DBMail

Copyright (c) 2020-2024 Alan Hicks, Persistent Objects Ltd [email protected]

Copyright (c) 2014-2019 Paul J Stevens, The Netherlands, [email protected]

Copyright (c) 2004-2013 NFG Net Facilities Group BV [email protected]

Copyright (c) 2000-2006 IC&S, The Netherlands

What is it?

DBMail is a collection of programs that enables email to be managed, stored in and retrieved from a database.

Why is it useful?

• Securely and scaleably manages user emails with industry standard IMAP;

• Integrates with existing authentication backends including ActiveDirectory and OpenLDAP;

• DBMail is scalable including multiple terrabyte installations;

• DBMail is flexible. You can run dbmail programs on different servers talking to the same database;

• Email filtering is integrated into DBMail and managed using SIEVE;

• An experimental Docker image is available;

• High Availability thanks to database replication and Docker images;

• Data safety thanks to database replication;

• Secure connections thanks to TLS;

• DBMail is database driven so no need of systemusers;

• No need to maintain system users or write access to the filesystem;

• DBMail is Free with a recognised GPL2 Open Source licence.

Who created it?

DBMail was originally created by IC&S in the Netherlands.

Around 2003, Paul Stevens at NFG also joined the development team, initially to provide debian packaging, later to take over development of the 2.1 release. Aaron Stone also deserves special thanks for initiating the LDAP driver, maintaining the delivery chain and of course sieve support.

DBMail is now a community effort to create a fast, effecient and scalable database driven mailingsystem. Both IC&S and NFG are fully behind opensource and the GPL. Therefore DBMail has the GPL licence.

Support

Support is available by raising an issue at https://github.com/dbmail/dbmail

Architecture

For an architectural overview of DBMail and its components please visit: https://dbmail.org/architecture/

Future

Check the website for further DBMail plans.

What kind of licence is DBMail?

DBMail uses the GPL version 2 licence.

It's included in the COPYING file.

How do I install it?

DBMail is available on many Linux and BSD distributions.

There is also an experimental Docker image with instructions to configure compose.yaml at https://dbmail.org/docker/

Installation Procedure

Check README and INSTALL files and on https://dbmail.org for detailed information and HOWTOs including how to use the Docker image.

See also contrib, debian and docker on https://github.com/dbmail/dbmail

Configuration

There are various settings including database access, authentication and logging. Please see dbmail.conf or https://dbmail.org/man-pages/dbmailconf/

Exim is a modern MTA and there is an example configure at https://github.com/dbmail/dbmail/blob/main/contrib/exim-dbmail-configure

Integration

DBMail integrates with most MTAs. There are a number of examples on https://dbmail.org/docs/

Web frontends including SquirrelMail and Roundcube work well with IMAP using dbmail-imapd plus Roundcube and most desktop software has integration with email filtering via dbmail-sieved.

Dependencies

• Database: Current versions of MySQL, PostgreSQL, Sqlite3 and Oracle
• Glib: (>= 2.16)
• GMime: (>= 3 (3.2))
• OpenSSL
• libmhash
• libzdb (http://www.tildeslash.com/libzdb/)
• libevent: (>= 2.1)
• Optional: libsieve (>= 2.2.1) (https://github.com/sodabrew/libsieve)
• Optional: jemalloc

Installing

• Download DBMail package
• Install dependencies (some provided from your linux / BSD distribution and some (libzdb and/or libsieve) need to be compiled
• ./configure
• make
• make install

dbmail's Issues

jemalloc, latest build cannot be compiled with DBMail

Due to changes related at jemalloc level, DBMail cannot detect (and hence use) this package. At first glance, the issue resides in acinclude.m4.

Further investigation is required.

implement/update debian package build

Is there a way to build a debian package with the new 3.2.4 release? I tried to follow the process that I used for dbmail 3.2.3 but this is what I get:

$ dpkg-buildpackage -uc -us -t
dpkg-buildpackage: info: source package dbmail
dpkg-buildpackage: info: source version 3.2.3-3
dpkg-buildpackage: info: source distribution unstable
dpkg-buildpackage: info: source changed by Casper Langemeijer [email protected]
dpkg-buildpackage: info: host architecture amd64
dpkg-source --before-build .
fakeroot debian/rules clean
test -x debian/rules
rm -f debian/stamp-makefile-build debian/stamp-makefile-install
/usr/bin/make -C . -k distclean
make[1]: Entering directory '/tank/downloads/dbmail/dbmail-3.2.4'
make[1]: *** No rule to make target 'distclean'.
make[1]: Leaving directory '/tank/downloads/dbmail/dbmail-3.2.4'
make: [/usr/share/cdbs/1/class/makefile.mk:91: makefile-clean] Error 2 (ignored)
rm -f debian/stamp-autotools
rmdir --ignore-fail-on-non-empty .
rmdir: failed to remove '.': Invalid argument
make: [/usr/share/cdbs/1/class/autotools.mk:64: makefile-clean] Error 1 (ignored)
set -e;
dh_clean
rm -f debian/stamp-autotools-files
dpkg-source -b .
dpkg-source: error: can't build with source format '3.0 (quilt)': no upstream tarball found at ../dbmail_3.2.3.orig.tar.{bz2,gz,lzma,xz}
dpkg-buildpackage: error: dpkg-source -b . subprocess returned exit status 255

the line "dpkg-buildpackage: info: source version 3.2.3-3" might mean that the debian build files have not been updated to the new version.

but I guess that is not the only reason it fails.

I am using a Ubuntu Mate 20.04 LTS workstation.

Thanks, Mauro

dbmail/lmtpd[1466]: EMERGENCY:[iconv] dbmail_iconv_once(+65): iconv failure

Trying to get dbmail running on new Ubuntu 20.04 server, getting this message in terminal every time new message arrives, dbmail/lmtpd[1466]: EMERGENCY:[iconv] dbmail_iconv_once(+65): iconv failure
Also messaged didn`t get stored to db

create_tables.mysql ERROR 1067 (42000) at line 278: Invalid default value for 'since'

I using MySQL 8.x

/dbmail-3.2.3$ mysql -u dbmail dbmail -p < sql/mysql/create_tables.mysql
Enter password: 
ERROR 1067 (42000) at line 278: Invalid default value for 'since'

280: ``since datetime NOT NULL default '0000-00-00 00:00:00',

https://stackoverflow.com/questions/25349126/how-can-i-set-the-default-value-of-a-field-as-0000-00-00-000000/25351733

this works for me:
'1000-01-01 00:00:00'
https://stackoverflow.com/a/46109298/2891692

create docker builds for easy deployment

I would like the be able to use the smallest possible docker containers for deploying full-featured dbmail services in a docker swarm or k8s cluster.

I've started a docker branch, and have a poc working setup that still needs improvements:

• static binaries, no additional packages
• clear guidelines to ease deployments
• possibily add ENVIRONMENT of key-store (aws-parameters, etc) support to dbmail.conf to avoid hard coding config values.

DBMail-Users - user delete wrong SQL Syntax on latest MySQL

Latest MySQL are a bit more strict about type checking in queries.

Dbmail-users -d generates the following query:

DELETE FROM dbmail_aliases WHERE deliver_to=37949 AND lower(alias) = lower('[email protected]');

This causes the following error

Error 'Truncated incorrect DOUBLE value: '[email protected]'' on query. Default database: 'dbmail'. Query: 'DELETE FROM dbmail_aliases WHERE deliver_to=37949 AND lower(alias) = lower('[email protected]')'

Since deliver_to is a String, the correct query is:

DELETE FROM dbmail_aliases WHERE deliver_to='37949' AND lower(alias) = lower('[email protected]');

upgrades/32005.psql syntax error

dbmail=# \i 32005.psql
BEGIN
psql:32005.psql:3: ERROR:  syntax error at or near "`"
LINE 1: ALTER TABLE `dbmail_sievescripts` CHANGE COLUMN `owner_idnr`...
                    ^
psql:32005.psql:4: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

The syntax should be something like

ALTER TABLE dbmail_sievescripts ALTER COLUMN owner_idnr DROP NOT NULL;
ALTER TABLE dbmail_sievescripts ALTER COLUMN owner_idnr DEFAULT NULL;

Furthermore the declaration of owner_idnr looks inconsistent in the upgrade script compared to create_tables.pgsql where the column is still set to NOT NULL.

Issue encountered in copymsg [1]

Some times in logs can be found db_copymsg(+2991): error getting size for message [1]
Using the tools provided (dbmail-util) does not fix the issue, and the message_id=1 does not exists.

Where is mail content stored? _mailboxes, _message - has no mail content?

Where is mail content stored? dbmail_mailboxes, dbmail_message - has no mail content?

And has everything. Only dbmail_mimeparts has raw data of all mail inside data column.

Where to fetch "mail content" just like "dbmail_subjectfield" has subject of mail. Where is "mail content" stored?

How to add new additional domain as well as domain alias to server for dbmail compatible?

I have one base domain working perfectly with dbmail, But I have 2 additional domain - I want to use in same dbmail server.

Is there any way to add additional domain and create email based on new additional domain with dbmail?

Thanks

dbmail-imapd hangs randomly when only a single client is connected

Testing with a single client connected to a dbmail-imapd mailbox with a few hundred folders
One folder has around 600 messages.
When iterating through the list of messages in that folder, only fetching BODYSTRUCTURE results in random hangs.

These hangs resolve if another client connects and issues commands to dbmail.
This doesn't seem to happen when an strace is attached to the dbmail-imapd process.

This is against dbmail-3.2.3 with some patches, but I may build a plain 3.2.3 and see if it behaves the same.

function group_concat(character varying) does not exist in PostgreSQL

Version 3.2.4 has hit FreeBSD ports and
Error:[MailboxState] MailboxState_new(+331): SQLException: ERROR: function group_concat(character varying) does not exist

This appears to be an issue for PostgreSQL users as group_concat is MySQL

There is a discussion on stackoverflow that might help
https://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent

LMTP doesn't watch users.active

LMTP recieves all mail even if users.active=0.
attachment is quick hack patch for this, it works, but i'm not sure it's good for dbmail and i don't know better way.

lmtp-active.diff.txt

Changelog

Since commit 7e0bf6d the changelog is not in the projects repository anymore.

Can we restore it back into the project? The ChangeLog file only contains a link to
http://git.dbmail.eu/paul/dbmail/log/ which doesn't work.

What is this link http://git.dbmail.eu/?p=paul/dbmail;a=log

I'm happy to do the administrative work of re-creating the changelog for the changes in the last few years if that is the problem.

It would be very helpfull if we can have a document with changes between releases.

LMTP die if sieve redirect has multiple email address

Hello,

LMTP service die every time if someone add redirect "[email protected]; [email protected];"; to sieve script. dbmail-timseieved accept this bad script, and insert it to database.

Invalid utf8 character string - mysql storage

Found errors in error log like:
Error:[message] _header_value_get_id(+1568): SQLException: Invalid utf8 character string: 'F09F8E'

Use mariadb 10.4.14

I see that all table use utf8 encoding, maybe this need to changed to utf8mb4???

install via make

i have successful installed (MySql also installed and configured)

apt-get -y install make

==> make is already the newest version (4.2.1-1.2).

Whats next?

I reading lots of stuff:

• Development files (libs, scripts and include files) for your database server.
  These will probably be provided by separate packages.
• Glib (>= 2.16) development headers and libraries.
• Gmime (>= 2.4) development headers and libraries.
• libSieve (>= 2.2.1) for Sieve support (libsieve.sf.net).
• Any standard libldap for LDAP support (only tested with OpenLDAP, however).
• mhash
• openssl
• libzdb (http://www.tildeslash.com/libzdb/)
• libevent

Hmmm. Maybe you could add an installer SH-Script?
I've got used to installing everything possible via a sh-script. Do you have that too? For Ubuntu?

Install for mysql failing (datetime default invalid)

I am installing dbmail for the first time (git commit afc107a on Ubuntu LTS 20.04 LTS)
The included sql/mysql/create_tables.mysql file is setting an invalid datetime default ("0000-00-00 00:00:00") for mysql.
I am using:
Package: mysql-server
Version: 8.0.21-0ubuntu0.20.04.4

I applied these changes for my install, but wanted to call this out:

index 07ec3449..5840a59a 100644
--- a/sql/mysql/create_tables.mysql
+++ b/sql/mysql/create_tables.mysql
@@ -277,7 +277,7 @@ CREATE TABLE `dbmail_partlists` (
 DROP TABLE IF EXISTS `dbmail_pbsp`;
 CREATE TABLE `dbmail_pbsp` (
   `idnr` bigint(20) UNSIGNED NOT NULL auto_increment,
-  `since` datetime NOT NULL default '0000-00-00 00:00:00',
+  `since` datetime NOT NULL default '1000-01-01 00:00:00',
   `ipnumber` varchar(40) NOT NULL,
   PRIMARY KEY  (`idnr`),
   UNIQUE KEY `ipnumber_index` (`ipnumber`),
@@ -293,7 +293,7 @@ CREATE TABLE `dbmail_physmessage` (
   `id` bigint(20) UNSIGNED NOT NULL auto_increment,
   `messagesize` bigint(20) UNSIGNED NOT NULL default '0',
   `rfcsize` bigint(20) UNSIGNED NOT NULL default '0',
-  `internal_date` datetime NOT NULL default '0000-00-00 00:00:00',
+  `internal_date` datetime NOT NULL default '1000-01-01 00:00:00',
   PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

@@ -320,7 +320,7 @@ CREATE TABLE `dbmail_replycache` (
   `to_addr` varchar(255) NOT NULL default '',
   `from_addr` varchar(255) NOT NULL default '',
   `handle` varchar(255) NOT NULL default '',
-  `lastseen` datetime NOT NULL default '0000-00-00 00:00:00',
+  `lastseen` datetime NOT NULL default '1000-01-01 00:00:00',
   UNIQUE KEY `replycache_1` (`to_addr`,`from_addr`,`handle`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Thanks

BSD systems don't use proc

get_opened_fd_count should return zero on FreeBSD type systems

There was an ifdef to do this:
https://github.com/pjstevns/dbmail/pull/46/files#diff-c6a39d85434c5cd409eac50923f2ac1e

int get_opened_fd_count(void)
{
#if defined(FreeBSD) || defined(APPLE) || defined(__SUNPRO_C)
// BSD like systems don't use proc
return 0;
#else
DIR* dir = NULL;
struct dirent* entry = NULL;
char buf[32];
closedir(dir);
return fd_count - 2; /* exclude '.' and '..' entries */
#endif
}

RPM Building issue: 64bit-portability-issue

When build RPM on Suse Build Service I get this problem on packing:

64bit-portability-issue dm_imapsession.c:1609
64bit-portability-issue dm_misc.c:87

libgmime 2.6

Hello

Ubuntu removed libgmime-2.6 in groovy gorilla.

Do you have any plan to update it to use libgmime-3.0?

How to purge deleted messages from database by dbmail-util

Hi, guys.
As i notices on MySQL dbmail base there are a lot of messges in table dbmail_messages with delete_flag
I'd like to purge it an remove from database.
Would You explain a correct way wit dbmail-util
I made

**dbmail-util -d**
Choosing dry-run mode. No changes will be made at this time.
Counting deleted messages that need the DELETE status set...
**Ok. [44878] messages n**eed to be set for deletion.
Maintenance done. No errors found.

**dbmail-util -p**
Choosing dry-run mode. No changes will be made at this time.
Counting messages with DELETE status...
**Ok. [0] messages** have DELETE status.
Maintenance done. No errors found.

It seems to be a wrong way. Would You help me to figure out that?

SIGSEGV in imap_append_hash_as_string() at dm_misc.c

I noticed a crash of dbmail-imapd with core dump.

This seems to be caused by strncpy trying to copy offset (10570) bytes into value which has been allocated only 1024 bytes.

I tried to understand the code in dm_misc.c, imap_append_hash_as_string() to comprehend the situation under which this SIGSEGV occured in order to write a fix preventing this crash to happen again in the future.

I guess we should safe-guard the strncpy to avoid situations writing more than 1024 bytes into value. However I think this shouldn't ever happen. If I understand the backtrace right, the head pointer has exceeded the memory boundary of type which in turn shouldn't be possible in the while(head) loop for a null-terminated string.

Any ideas on this?
I am using dbmail 3.2.3 on FreeBSD 12.0.

(lldb) bt
* thread #1, name = 'dbmail-imapd', stop reason = signal SIGSEGV
    frame #0: 0x000000080097a6d5 libc.so.7`memset + 213
    frame #1: 0x0000000800962b52 libc.so.7`strncpy + 82
  * frame #2: 0x00000008002e55b6 libdbmail.so.0`imap_append_hash_as_string(list=0x0000000801ca3b60, type="attachment; filename=\"LZI_752802.pdf\";") at dm_misc.c:1417
(lldb) frame select 2
frame #2: 0x00000008002e55b6 libdbmail.so.0`imap_append_hash_as_string(list=0x0000000801ca3b60, type="attachment; filename=\"LZI_752802.pdf\";") at dm_misc.c:1417
   1414                         size_t len;
   1415                         char *clean1, *clean2, *clean3;
   1416                         memset(value, 0, sizeof(value));
-> 1417                         strncpy(value, head, offset);
   1418                         head += offset+1;
   1419                         inname = 1;
   1420                         offset = 0;
(lldb) frame variable
(GList *) list = 0x0000000801ca3b60
(const char *) type = 0x0000000803f5b690 "attachment; filename=\"LZI_752802.pdf\";"
(size_t) i = 12
(char) curr = '\0'
(char [512]) name = ""
(char [1024]) value = ""
(char *) head = 0x0000000803f5b6b6 ""
(GList *) l = 0x0000000801c189a0
(int) offset = 10570
(int) inname = 1
(size_t) len = 15
(char *) clean1 = 0x00007fffdf9fa3d1 ""
(char *) clean2 = 0x00000008036b6980 "LZI_752802.pdf"
(char *) clean3 = 0x00000008036d6240 "LZI_752802.pdf"

ERROR 1064 (42000): near 'identified by 'mypass'' at line 1

$ mysqld --version
/usr/sbin/mysqld  Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
mysql -u root -p
mysql> GRANT ALL ON dbmail.* to dbmail@localhost identified by '<mypass>'
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'mypass'' at line 1

please compare to line 59 in INSTALL:
GRANT ALL ON dbmail.* to dbmail@localhost identified by '<pass>'

Any ideas? Update the INSTALL file?

Incomplete setup documentation for PostgreSQL

Registration seems disabled on the wiki, so using a ticket instead.

The documentation for PostgreSQL at https://www.dbmail.org/dokuwiki/doku.php/setup_database is incomplete. In addition to:

psql -U dbmail dbmail < sql/postgresql/create_tables.pgsql

you also have to run

cat sql/postgresql/upgrades/*.psql|psql -U dbmail dbmail

otherwise dbmail fails at startup because the table dbmail_update_steps is missing.

Can I create and use my own Web Interface on provided database to setup Mailing application?

Can I create and use my own Web Interface on the provided database to set up a Mailing application?

I don't want to use Roundcube or webmail. I prefer to create my own web interface. Can I just use "dbmail database" to create a web interface for mailing application to retrieve mail?

Travis is failing due to a timeout

Some tests fail due to a timeout of 10s.

Exim dbmail example configuration

As Exim integrates well with dbmail it would be helpful to include an example configuration.
Although exim offers infinite possibilities, there are three supported databases (PostgreSQL, MySQL and Oracle) and database or ldap authentication that can be easily integrated. It should be straightforward to offer a customised configuration based on integrating dbmail into a default exim configuration for easy updating with comments on how to implement the various choices.

dbmail-util: implement cleanup of duplicate-delivered messages

Per the discussion in #45, implement logic in dbmail-util to clean up messages which were delivered in duplicate. For a given mailbox ID, a physical message should only appear once.

dbmail generates a possibly invalid BODYSTRUCTURE, that java clients don't like

When the content-type header is not present in an email, dbmail generates a possibly incomplete BODYSTRUCTURE:
BODYSTRUCTURE ("text" "plain" NIL NIL "7BIT" 4669 2294 NIL NIL NIL NIL)
Rather than:
BODYSTRUCTURE ("text" "plain" NIL NIL NIL "7BIT" 4669 2294 NIL NIL NIL NIL)

Non java clients seem to be fine with this, but java clients barf, sometimes quietly.

Looking through the code, I believe this difference is unintended.

It seems to be due to a call in dm_misc.c to:
imap_append_hash_as_string(GList *list, const char *type)
Where type is null, which happens when a call to:
g_mime_object_get_header(object, "Content-Type")
returns a null for the Content-Type, ie, when there is no Content-Type header.

The code in imap_append_hash_as_string returns an unmodified list when the passed-in type is null, where I believe it should append a 'NIL' and return the list.

I will submit a merge request with this implemented shortly, and the change does fix the issue in my environment.

I have installed DBmail and created MX records yet not able to find mail in MySQL

I have just installed dbmail and have configured MX records and A records for Mail. I have even imported the MySQL database of dbmail.

I created "user" using "dbmail-user" and sent a test mail to the created mail account.

But I am not able to find any mail message in the database. Even when I export mail for the user, it is empty as well.

Where is mail stored? Have I missed any step?

• dbmail installed
• postfix installed and configured
• database of dbmail imported
• user-created using dbmail-user
• MX record and A record configured for the domain name to the server
• ping test for domain record successful
• Sent test mail to created email account.

=> Waited for 2 hour ( no mail available )

Have I missed any step?

Question: Up-to-date database schema

Where I can find up-to-date database schema for 3.1-3.2 release?
I have found only for 2.0 version https://www.dbmail.org/dokuwiki/doku.php/2.0

Help Needed - DBMail Administration

Apologies for creating an issue but I couldn't find the forums to post.

Can someone please point me to a DBMail administration tool like dbmailadmin or something else ? Is it Possible to have instructions on how to setup whatever tools you suggest ? or share - How you manage DBMail with Aliases and Virtual Domains ?

Thanks in Advance

Imap daemon failed query, dbmail_messageblks not exist

I found lot of errors like:

SELECT b.messageblk, b.is_header, DATE_FORMAT(p.internal_date, GET_FORMAT(DATETIME,'ISO')) FROM dbmail_messageblks b JOIN dbmail_physmessage p ON b.physmessage_id=p.id WHERE b.physmessage_id = 886653 ORDER BY b.messageblk_idnr

The table dbmail_messageblks not exist anymore in version 3, why is it accessed?

Create travis integration

On top of the docker build, setup a .travis.yml file so we can run at least the unit-tests.

blocked by #2

integration with samba AD DC for user authentication - SASL Authentication?

Has anyone managed to configure dbmail to authenticate users through samba AD DC, either kerberos or LDAP? This is because I can have users authenticated via SASL integration with samba AD for postfix/SMTP (sending emails), but the password must be manually synchronised for IMAP access.
It would be a lot better if we could authenticate dbmail users via samba AD too.

Outlook issue, BYE command

There is a small issue encountered on * BYE. Although RFC 3501 does specify that message on normal operation is optional Outlook (at least 2016 version does end with parse error. The fix is obvious, shoud be added a message on imap.c: 453

wrong sql

Hi,

dbmail 3.2.3, fresh install with postgresql.

Error:

ERROR: syntax error at or near "(" at character 171

SELECT message_idnr FROM dbmail_messages m
LEFT JOIN dbmail_physmessage p ON m.physmessage_id=p.id
WHERE mailbox_idnr = $1 AND status IN ($2,$3) AND m.message_idnr IN (2 (MODSEQ 3)) AND p.internal_date > '2021-01-22 00:00:00'
ORDER BY message_idnr

it seems wrong sql generated, (what is MODSEQ ?) i think "IN (2, 3) " will be better

State of the mailbox and header messages (BODYSTRUCTURE) does not take into consideration messages marked to be deleted or purged

It seems that DBMail at intialization, does use all messages from a mailbox to create a state, even those which are marked as deleted (status=2), and eventually purged (status=3) . Those messages cannot be access or changed any way by the way all the code is structured.
The code resides at: dm_mailboxstate.c::state_load_messages
Also, at the fetching body structure the queries are a little slow and also do not take into consideration the above conditions (status=2 or status<2). So the fix is to optimize this query.
dm_imapsession.c::_fetch_headers
Both issues are entangled and can be seen when large mailboxes are in place (+50000 messages)

systemd units changed to type simple

@langemeijer what prompted the change of Type for the systemd units from notify to simple in 4de4bc5 ?
Is there something that needs fixing?

Portability fix on Solaris

In addition to the fixes in #123, I had to remove #include <features.h> from dbmail.h.in to get it to build on SmartOS (Illumos/Solaris derived).

SigSEGV in imapd

I have lot of error like:

kernel: traps: dbmail-imapd[32308] general protection ip:7f6b678b02ed sp:7fff1d4673a0 error:0 in libglib-2.0.so.0.5400.3[7f6b67845000+115000]

and sometimes:
dbmail-imapd[23017]: segfault at 0 ip (null) sp 00007ffdf77c8868 error 14 in dbmail-imapd[55be97869000+1d000]

System: opensuse 15.1.

How can I hel You to solve this?

In PostgreSQL lastRowId is _not_ always zero

db_insert_result() on postgreSQL backend is not returning the right id.
There is a patch used in other versions that solves the issue.
pgsql-RETURNING.zip

Fails to build on Alpine Linux

dbmail fails to compile on Alpine Linux because it is based on musl libc rather than glibc and does not have <sys/queue.h>:

gmake[3]: Entering directory '/home/majid/build/dbmail-3.2.3/src/modules'
/bin/sh ../../libtool  --tag=CC   --mode=compile gcc --std=c99 -DHAVE_CONFIG_H -I. -I../..  -I../../src   -I/usr/local/include -I/usr/local/ssl/include -pthread -I/usr/local/include -I/usr/local/include/glib-2.0 -I/usr/local/lib/glib-2.0/include -I/usr/local/include  -pthread -I/usr/local/include/gmime-2.6 -I/usr/local/include -I/usr/local/include/glib-2.0 -I/usr/local/lib/glib-2.0/include -I/usr/local/include  -I/usr/local/include/zdb -I/usr/local/include/jemalloc -I/usr/include/jemalloc -W -Wall -Wpointer-arith -Wstrict-prototypes -MT sortnull.lo -MD -MP -MF .deps/sortnull.Tpo -c -o sortnull.lo sortnull.c
libtool: compile:  gcc --std=c99 -DHAVE_CONFIG_H -I. -I../.. -I../../src -I/usr/local/include -I/usr/local/ssl/include -pthread -I/usr/local/include -I/usr/local/include/glib-2.0 -I/usr/local/lib/glib-2.0/include -I/usr/local/include -pthread -I/usr/local/include/gmime-2.6 -I/usr/local/include -I/usr/local/include/glib-2.0 -I/usr/local/lib/glib-2.0/include -I/usr/local/include -I/usr/local/include/zdb -I/usr/local/include/jemalloc -I/usr/include/jemalloc -W -Wall -Wpointer-arith -Wstrict-prototypes -MT sortnull.lo -MD -MP -MF .deps/sortnull.Tpo -c sortnull.c  -fPIC -DPIC -o .libs/sortnull.o
In file included from sortnull.c:28:
../../src/dbmail.h:86:10: fatal error: sys/queue.h: No such file or directory
   86 | #include <sys/queue.h>
      |          ^~~~~~~~~~~~~
compilation terminated.

Other projects like libevent have approached this problem by bundling a queue.h:

libevent/libevent#343

Text fields as blobs in sqlite?

Search is not working properly with a fresh install using sqlite.

Blob fields in sqlite do not behave well with LIKE or GLOB, and the table dbmail_headervalue defined the headervalue field as a blob (I assume this is probably the case for other tables as well).

To see this in action, try:

create table foo(f blob);
insert into foo values('hello');
insert into foo values(cast('hello' as blob));
select typeof(f) from foo;
# text
# blob
select * from foo where f like 'hel%';
# hello
select * from foo where cast(f as text) like 'hel%';
#hello
#hello

The current db design seems to be incompatible with sqlite.

dbmail can`t execute upgrade step ALTER TABLE `dbmail_sievescripts`

Cant execute schema upgrade on MySQL 8 BEGIN; ALTER TABLE dbmail_sievescriptsCHANGE COLUMNowner_idnr owner_idnrBIGINT(20) UNSIGNED NULL DEFAULT NULL; ALTER TABLEdbmail_sievescriptsADD COLUMNid BIGINT(20) UNSIGNED NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id`);

INSERT INTO dbmail_upgrade_steps (from_version, to_version, applied) values (32001, 32005, now());
COMMIT;

Reson
ALTER TABLE dbmail_sievescripts ADD COLUMN id BIGINT(20) UNSIGNED NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id)

1171 - All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
Time: 0,004s

Traces
]
Aug 07 08:43:46 dev-mail dbmail-lmtpd[2916]: [0x561d72ac0200] Database:[db] log_query_time(+375): last query took [0] seconds
Aug 07 08:43:46 dev-mail dbmail-lmtpd[2916]: [0x561d72ac0200] Database:[db] db_con_close(+359): [0x561d72b07390] connection to pool
Aug 07 08:43:46 dev-mail dbmail-lmtpd[2916]: [0x561d72ac0200] Database:[db] db_con_get(+331): [0x561d72b129a0] connection from pool
Aug 07 08:43:46 dev-mail dbmail-lmtpd[2916]: [0x561d72ac0200] Database:[db] db_stmt_prepare(+494): [0x561d72b129a0] [SELECT 1=1 FROM dbmail_upgrade_steps WHERE from_version = ? AND to_version = ?]
Aug 07 08:43:46 dev-mail dbmail-lmtpd[2916]: [0x561d72ac0200] Database:[db] db_stmt_set_int(+508): [0x561d72b240d0] 1:[32001]
Aug 07 08:43:46 dev-mail dbmail-lmtpd[2916]: [0x561d72ac0200] Database:[db] db_stmt_set_int(+508): [0x561d72b240d0] 2:[32005]
Aug 07 08:43:46 dev-mail dbmail-lmtpd[2916]: [0x561d72ac0200] Database:[db] db_con_clear(+366): [0x561d72b129a0] connection cleared
Aug 07 08:43:46 dev-mail dbmail-lmtpd[2916]: [0x561d72ac0200] Info:[db] check_upgrade_step(+1010): Running upgrade step 32001 -> 32005
Aug 07 08:43:46 dev-mail dbmail-lmtpd[2916]: [0x561d72ac0200] Database:[db] db_exec(+398): [0x561d72b129a0] [BEGIN;
ALTER TABLE dbmail_sievescripts CHANGE COLUMN owner_idnr owner_idnr BIGINT(20) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE dbmail_sievescripts ADD COLUMN id BIGINT(20) UNSIGNED NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);

INSERT INTO dbmail_upgrade_steps (from_version, to_version, applied) values (32001, 32005, now());
COMMIT;
]

dbmail generates a possibly invalid BODYSTRUCTURE, that java clients don't like part 2

New issue referenced in #23 , it looks like when a mulit-part email contains a part that is message type, sometimes java clients that use javax.mail blow up with this:

javax.mail.MessagingException: Unable to load BODYSTRUCTURE
        at com.sun.mail.imap.IMAPMessage.loadBODYSTRUCTURE(IMAPMessage.java:1564)
        at com.sun.mail.imap.IMAPMessage.getContentType(IMAPMessage.java:599)
        at msgshow.dumpPart(msgshow.java:258)
        at msgshow.main(msgshow.java:224)

In looking into the dbmail code, all parts of type message include envelope structure, body structure, and lines.
However, the RFC says:

A body type of type MESSAGE and subtype RFC822 contains,
immediately after the basic fields, the envelope structure,
body structure, and size in text lines of the encapsulated
message.

The java code that looks at this is here

When I remove those 3 fields, the java library is happy, so I think we need to add a subtype check and only when the subtype is rfc822, include those 3 fields.

The samples I'm looking at:
From dbmail:

* 1 FETCH (BODYSTRUCTURE (("message" "delivery-status" ("charset" "UTF-8" "")  NIL NIL "8bit" 412 NIL NIL 14 "24589f11f73bab91c9b1223af056e777" ("attachment" NIL) "en-US" NIL)))

After removing the 3 rfc822 parts:

* 1 FETCH (BODYSTRUCTURE (("message" "delivery-status" ("charset" "UTF-8" "")  NIL NIL "8bit" 412 "24589f11f73bab91c9b1223af056e777" ("attachment" NIL) "en-US" NIL)))

The calling code in dbmail is here and here and the implementation is here.

I do see the dbmail is explicitly checking in function imap_get_logical_part here for message/rfc822, but not in _structure_part_message

Multiple duplicated messages in mailboxes

This seems like a situation which should not be:

MariaDB [dbmail]> SELECT COUNT(*) AS cnt, mailbox_idnr, physmessage_id
    ->   FROM dbmail_messages 
    ->   GROUP BY physmessage_id, mailbox_idnr 
    ->   ORDER BY cnt DESC LIMIT 5;
+-----+--------------+----------------+
| cnt | mailbox_idnr | physmessage_id |
+-----+--------------+----------------+
|  26 |            5 |        2036654 |
|  26 |            5 |        2036655 |
|  26 |            5 |        2036650 |
|  26 |            5 |        2036653 |
|  26 |            5 |        2036652 |
+-----+--------------+----------------+

Am I misunderstanding the schema, bad at SQL, or the proud owner of a database where a bunch of messages are duplicated?

If the latter, there might be quite a few duplicates...

MariaDB [dbmail]> SELECT COUNT(*) AS dupcnt, mailbox_idnr FROM
    -> ( SELECT COUNT(*) AS cnt, mailbox_idnr, physmessage_id 
    ->   FROM dbmail_messages GROUP BY physmessage_id, mailbox_idnr ORDER BY cnt DESC
    -> ) dups WHERE cnt > 1 GROUP BY mailbox_idnr ORDER BY dupcnt DESC LIMIT 5;
+--------+--------------+
| dupcnt | mailbox_idnr |
+--------+--------------+
|  53606 |            5 |
|    820 |           77 |
|     29 |          129 |
|     21 |          147 |
|      4 |          130 |
+--------+--------------+

Should I perhaps remove all but one of those duplicated mailbox entries pointing at the same physmessage, or is this situation actually just fine?

Failure to install on Fedora

Can we have a packaged version, or rather a repo, for fedora please ?
I'm unable to find a usable RPM version on Fedora 31 server.

Thank you

PostgreSQL Issue related to grouping in _fetch_headers

dm_imapsession.c::_fetch_headers is using a query that is missing the explicit grouping.