gromox-kdb2mt(8)

💖 Name 💖

okay so bestie, this is gromox-kdb2mt(8) from the Gromox admin reference — gromox-kdb2mt — Utility for analysis/importing of Zarafa/Kopano SQL-stored mailboxes. It’s giving “rescue the old mail store” energy, and honestly? iconic. ✨

🦋 Synopsis 🦋

Here’s the summoning ritual, no cap:

gromox-kdb2mt [-pstv] [--sql-host hostname] [--sql-user identity]
[--sql-port number] [--sql-db name] --src-attach dir {--mbox-guid
mboxguid|--mbox-name username|--mbox-mro username} [...]

🌸 Description 🌸

gromox-kdb2mt reads one store from a Zarafa/Kopano SQL database and, for attachments, the associated filesystem. Then it re-exports that data in a Gromox-specific mailbox transfer format to stdout, intended to be piped into gromox-import(8). Optionally, kdb2mt can print a summary of the hierarchy during extraction. cute, useful, we love. 🫶

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. slay. 💅

ACLs can be extracted, but, owing to the independence of the Kopano LDAP data model, require pre- or post-processing (see below).

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. yes, the exclamation mark is doing cardio. 😭

✨ Options ✨

• -p — Show properties in detail (enhances -t).
• -s — 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).
• -t — Show a diagnostic tree view of the source data as it is being read.
• -v — Print message count progress while processing larger folders. This option has no effect if (the even more verbose) -t option was used.
• --loglevel n — Maximum verbosity of general logging (not connected to -p, -t or -v). 1=crit, 2=error, 3=warn, 4=notice, 5=info, 6=debug. Default: 4 (notice).
• --user-map file — Use the given file to perform ACL mapping. See section “ACL Extraction” below for details. The file is also used for translating ZARAFA-type addresses to SMTP. See section “ZARAFA Address Type” below for details.
• --sql-host hostname — Hostname for the source SQL connection. Default: (MySQL default; localhost).
• --sql-port number — Port for the source SQL connection. Default: (MySQL default; automatic).
• --sql-user identity — Username for the source SQL connection. Default: root.
• --sql-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 "".)
• --mbox-guid guid — Selects the mailbox with the particular GUID for extraction. (This may be used to read orphaned stores.)
• --mbox-user username — Scan the user map (cf. --user-map) for a mailbox which was used by the given username, and use it for extraction. (To get a listing of all stores, use --mbox-user "".)
• --mbox-mro 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 --mbox-mro "".)
• --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 to selectively extract more objects. In the output stream, objects so extracted will be declared as unanchored so they can be imported to a folder of choice later (cf. gromox-import’s -B option).
• --acl={auto|no|noextract|extract|convert} — Handling for ACLs on MAPI objects: ignore, extract to synthetic address, or convert to an e-mail address. auto plus the presence of --user-map leads to convert. auto plus the absence of --user-map leads to noextract. See the section “ACL Extraction” below for some more 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 Store\Import 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. periodt. 💚

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 Store\Invoices" will be imported at "(Target root)\Top of Information Store\Invoices".

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 to mix your (existing) with their mails.

🌈 Environment variables 🌈

• SQLPASS — Password for the source SQL connection.

⭐ Examples ⭐

💜 Common scenario (Separate hosts) 💜

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

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

sshfs root@kp:/var/lib/kopano/attachments /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 127.0.0.1:12345 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 "127.0.0.1" 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:

SQLPASS=kopanosqlpass gromox-kdb2mt --sql-host 127.0.0.1 --sql-port 12345 --sql-user kopano --src-attach /mnt --mbox-mro jdoe | gromox-import -u user@domain.example

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 --mbox-mro 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 --mbox-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 💅

Okay so here's the tea, bestie: Kopano SQL databases generally do not store usernames. Like, at all. Store ownership is recorded with a Kopano-level numeric ID, and that ID is itself mapped to some site-specific attribute of an authentication service — for example, the uidNumber field of an LDAP. So only the authentication service would actually know the username, and kdb2mt does not rely on the existence of such an authentication provider. It's giving "I don't know her" energy, and honestly? By design. 🫶

Every store has a metadata field for the most recent owner (MRO). That 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 --mbox-mro 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. And on top of that, the MRO field is missing the domain/company part of the username. Company public stores (in hosted setups) use the company name as MRO. All of this contributes to --mbox-mro possibly matching multiple stores. Messy? Yeah. Iconic? Not exactly. 😭

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 --mbox-guid instead. That's your cue, bestie — grab the GUID and run with it. 🏃‍♀️💨

---

🎀 ACL Extraction 🎀

Because kdb2mt works completely LDAP-less, it knows nothing about users except for their numeric user object ID on the homeserver, plus 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 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

See that synthetic identity in the username column? Yeah, that's the vibe. 💅

kdb2mt can be instructed to map these to a new email address using the --user-map command-line option. That file can be generated by executing kdb-uidextract(8) or the kdb-uidextract-limited(8) helper programs on the original, live Kopano system. (With some effort, the file can also be manually constructed. See that manpage for format details.) 🧚

---

🌈 ZARAFA Address Type 🌈

MAPI as a system supports referencing a message's participants by arbitrary target descriptors. These include, but are not limited to, SMTP e-mail addresses, usernames, LDAP RDNs, X.400 address, or even a fax number. For example, the "EX" address type employed by Exchange uses the ESSDN (Enterprise/Site/Server Distinguished Name) of the user, which is sort of a LDAP Relative Distinguished Name (RDN). Like, okay, flex. 💀

Zarafa/Kopano systems define a "ZARAFA" address type, and the identifiers contain the username (possibly in other forms such as company\username or company@username) or the (SMTP) email address; it is not entirely consistent. 🙈 With the help of a user map file, the ZARAFA-type addresses can be converted to SMTP addresses. Slay. ✨

---

👀 See also 👑

• gromox(7)
• gromox-import(8)

---

And that's the whole page, bestie — you ate that! 💖 This little masterpiece is brought to you by Gromox, filed under gromox-kdb2mt(8) — go forth and migrate those stores like the main character you are. 🎀🦋👑