gromox-kdb2mt — Utility for analysis/import of Kopano mailboxes


gromox-kdb2mt [-pstv] [–src-host sqlhost] [–src-user sqluser] [–src-port sqlport] [–src-db dbname] –src-attach dir {–src-guid mboxguid|–src-mbox username} […]


gromox-kdb2mt reads one store from a Zarafa/Kopano SQL database and, for attachments, the associated filesystem. The data is then re-exported in a Gromox-specific mailbox transfer format to stdout, intended for consumption by pipe by the gromox-mt2exm(8gx) program. Optionally, kdb2mt can print a summary of the hierarchy during extraction.

The SQL server that carries the Zarafa/Kopano database for the home server of the user must be active. Databases with a schema version n61 or newer (ZCP versions 7.0.3 and onwards, and all KC versions) are supported. The kopano-server(8) process need not be running. Its LDAP need not be available either. ACLs can be extracted, but, owing to the independence of the Kopano LDAP data model, require custom post-processing.

The directory of Kopano attachments of the home server should be made available in the mount namespace wherever gromox-kdb2mt runs.

Properties of the store object itself are only shown (if and when -p is used), but never dumped to the MT data stream. Properties of the store root folder object are(!) transferred.


Show properties in detail (enhances -t).
Map the source mailbox folder hierarchy (and its subobjects) to the target mailbox’s hierarchy and splice objects accordingly. Only use -s when both the source side and the target side are private stores. See the section “Splice mode” below for more information. When –with-hidden is not selected, -s will imply –without-hidden by default to avoid polluting e.g. “QuickStep settings” (which may already have settings).
Show a diagnostic tree view of the source data as it is being read.
Print message count progress while processing larger folders. This option has no effect if (the even more verbose) -t option was used.
–acl-map file
Use the given file to perform ACL mapping. See section “ACL Extraction” below for details. This option implies –with-acl.
–src-host hostname
Hostname for the source SQL connection.
Default: (MySQL default; localhost)
–src-port port
Port for the source SQL connection.
Default: (MySQL default; automatic)
–src-user username
Username for the source SQL connection.
Default: root
–src-db dbname
Database name.
Default: kopano
–src-attach directory
Required specification to the /var/lib/kopano/attachments directory mounted somewhere locally. (To skip over file-based attachments, use the empty value, i.e. –src-attach “”.)
–src-guid guid
Selects the mailbox with the particular GUID for extraction. (This may be used to read orphaned stores.)
–src-mbox username
Scan the source database for a mailbox which appears to have last been used by username, and use it for extraction. There are a number of caveats related to this lookup; see the section “Store lookup by name” further below. (To get a listing of all stores, use –src-mbox “”.)
–l1 x, –l2 y
If you are using “attachment_storage=files_v1-x-y” in kopano-server.cfg, call kdb2mt with the L1 and L2 options.
–only-obj hid
Extract just the object with the given hierarchy id. This option may be specified multiple times. In the output stream, the objects will have an unspecified location given as their parent folder. (mt2exm will use the drafts folder because it is expectedly less crowded than one’s inbox.)
Perform partial conversion of ACLs. See the ACL section below for details.
–with-hidden, –without-hidden
This option controls the import of folders that have PR_ATTR_HIDDEN=1.

Splice mode

Normally, kdb2mt will have a folder mapping table that specifies one entry, which is “(Source root)” to “(Target root)Top of Information StoreImport of GUID @date”. Any objects within an entry’s source folder (including more folders) are copied. This default entry makes sure absolutely everything is imported, without loss.

Using the -s option, this behavior will change. The default mapping is replaced by one that will intermix imported folders with an existing hierarchy. Specifically, special folders such as the root folder item, Top of Information Store (a.k.a. TOIS or IPM_SUBTREE), Inbox, etc. are mapped. This only works when both the source and target are private stores!

Special folders are identified by metadata, not by name. This way, kdb2mt can support localized folder names and correctly map, for example, a German “Gesendete Elemente” to a French “Éléments envoyés” (Sent Items). Regular folders will be processed normally (by name), e.g. “(Source root)Top of Information StoreInvoices” will be imported at “(Target root)Top of Information StoreInvoices”.

The -s option is most useful when importing one’s own store from one system to another that’s new and blank. If importing someone else’s store into yours, leaving out -s is normally the desired behavior, since you may not want want to mix your (existing) with their mails.

Environment variables

Password for the source SQL connection.


Common scenario (Separate hosts)

When Gromox and Kopano run on different hosts, and you wish to have the Gromox host to initiate all necessary connections.

Step 1. Establish an sshfs mount. This is used to get at the attachment directory of Kopano Core. Command:

sshfs root@kp:/var/lib/kopano/attachment /mnt

For this to work, root logins need to be possible in some form (password or pubkey-based authentication).

Step 2. Establish an SSH tunnel. This is used to get at the MariaDB/MySQL database, assuming that this database is not already accepting connections on port 3306. Command:

ssh -L 12345:localhost:3306 root@kp

This way, the database can be accessed as later.

Step 3. Locate the MariaDB connection parameters that you want to use. You can use the MariaDB “root” user (if available), or reuse the credentials from /etc/kopano/server.cfg (often a “kopano” user).

Step 4. Run the conversion. The use of “” is necessary to bypass the special meaning of “localhost” (which implies the use of an AF_LOCAL socket, e.g. /run/mysql/mysql.sock). Command:

SRCPASS=kopanosqlpass gromox-kdb2mt –src-host –src-port 12345 –src-user kopano –src-attach /mnt –src-mbox jdoe | gromox-mt2exm -u

Done! The speed of the operation depends on the capabilities of the network and the source database (latency more so than throughput).

Other options

If the Gromox host is not allowed to connect to the Kopano host for reasons of networking and/or firewall setups, there are plenty of other ways to carry over the data. Administrators are asked to use their experience to mix and match the plethora of utilities available at their disposal. Possible operations include mysqldump(1), sftp(1), rsync(1), tar(1) and curl(1).

Store lookup using Kopano tools

If kdb2mt’s built-in heuristic –src-mbox resolution mechanism is not adequate enough, you can use utilities from the Kopano installation, provided that is still active.

  • `kopano-storeadm -M` is the gold standard. This dumps the entire store list, in JSON representation. The GUIDs can then be used together with –src-guid.
  • The global “SYSTEM” user object in Kopano also happens to have a private store, titled “Inbox - SYSTEM”. This store however is practically empty and it is unlikely it will ever need extraction. Alternatively, its GUID can also be shown with `kopano-admin –details SYSTEM`.
  • The global public store in Kopano, if it exists, is owned by the “Everyone” group object. In kopano-storeadm output, it can be found by looking for the display name “Public Folders”. There is no way to see the GUID via kopano-admin.
  • Just for completeness: There is no per-company SYSTEM user (and hence no store). If anything, companies re-use the global SYSTEM user as a member.
  • The per-company public folder, if it exists, is owned by the respective company object. In kopano-storeadm output, it can be found by looking for the display name “Public Folders - MyCompany”. Alternatively, the GUID can also be shown with `kopano-admin –type company –details MyCompany`.

Store lookup by name

Generally, Kopano SQL databases do not store usernames. Store ownership is recorded with a Kopano-level numeric ID, which itself is mapped to a site-specific attribute of an authentication service, e.g. the uidNumber field of an LDAP. Only the authentication service would know the username, and kdb2mt does not rely on the existence of such authentication provider.

Every store has a metadata field for the most recent owner (MRO). This field was intended for orphaned stores and has informational value only. The MRO field is not always updated by Kopano services, which can lead to –src-mbox not necessarily finding an expected match. In particular, kopano-server misses doing the MRO update on store detach, and on changes to the username in LDAP.

Furthermore, because it is possible to detach/orphan and create a new store for a user (and repeatedly so), the MRO field value is not unique across the set of all stores.

Furthermore, the MRO field is missing the domain/company part of the username. Company public stores (in hosted setups) use the company name as MRO. This all contributes to –src-mbox possibly matching multiple stores.

When more than one store matches in any way, kdb2mt will print the result set with GUIDs and exit, at which point you need to use –src-guid instead.

ACL Extraction

Because kdb2mt works completely LDAP-less, it knows nothing about users save for their numeric user object ID on the homeserver and a reference to an LDAP object (the so-called “Extern id”, e.g. objectUUID/uidNumber). The user object ID is local to a kopano-server instance. The composition of the object ID and server instance GUID forms a unique token. ACEs are carried over such that that permissions for user with a given objid are transformed to the synthetic identity objid@serverguid.kopano.invalid.

sqlite3 /var/lib/gromox/user/1/1/exmdb/exchange.sqlite3
sqlite> select * from permissions;
member_id  folder_id  username                                             permission
---------  ---------  ---------------------------------------------------  ----------
1          15         default                                              2048
2          24         default                                              2048
3          2090545    256@aa8e2b20b2054ca98987ea1053c3bb16.kopano.invalid  1177

kdb2mt can be instructed to map these to a new email address using the –acl-map command-line option. That file shall be a plain text file containing one mapping entry per line. Empty lines and lines beginning with a fields (spaces/tabs). The first field shall be the synthesized identity that is to be matched (objid@serverguid.kopano.invalid), the second field its desired replacement.

Take note that in multi-server Kopano installations, every LDAP user will appear in all kopano-server databases, and with generally different user IDs:

# kopano-admin --details joe -h https://kopano01:237/
Object id:              256
Extern id:              10072
Username:               joe
Auto-accept meeting req:no
Home server:            kopano01.invalid
Store GUID:             45A167B5AA7D44B1B5AC1195AA25E6B0

# kopano-admin --details joe -h https://kopano02:237/
Object id:              209
Extern id:              10072
Username:               joe
Auto-accept meeting req:no
Home server:            kopano01.invalid
[No store GUID shown since home is not on kopano02]

Mailboxes which have kopano02 as home server have ACL entries using the kopano02 object ID of joe. Therefore, the mapping file should contain the identities from all servers, that is,

256@aa8e2b20b2054ca98987ea1053c3bb16.kopano.invalid  joe@gromox
209@dabb6b35a8cf471c94c6d85a25edf8c9.kopano.invalid  joe@gromox

The server GUID(s) may be found by examining the output from kopano-stats(1). (You will need to lowercase them.)

# kopano-stats -h https://kopano01:237/ --system | grep server_guid
    server_guid    AA8E2B20B2054CA98987EA1053C3BB16
# kopano-stats -h https://kopano02:237/ --system | grep server_guid
    server_guid    DABB6B35A8CF471C94C6D85A25EDF8C9

ZARAFA Address Type

MAPI is a system that supports referencing conversation participants of a message by means other than an SMTP e-mail address. For example, the “EX” address type employed by Exchange uses the Distinguished Name of the user object in the ActiveDirectory. (Obviously, this only works for users present in the LDAP tree.) Zarafa/Kopano systems define a “ZARAFA” address type, and the identifiers contain the username (possibly in other forms such as companyusername or company@username) or the (SMTP) email address.

Because kdb2mt operates without LDAP, the utility cannot convert those usernames. Conversion of what appears to be email addresses but isn’t may lead to a wrong result. Rewriting ZARAFA-type addresses is therefore left to a separate tool.

The presence of a ZARAFA-type address on a message object will make many clients skip such objects silently in part or in full.

See also

gromox(7), gromox-mt2exm(8gx)