gromox-kdb2mt

Name

gromox-kdb2mt — Utility for analysis of Kopano databases

Synopsis

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

Description

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.

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.
–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-at 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-at “”.)
–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.)
–with-acl
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

SRCPASS
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 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 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:

SRCPASS=kopanosqlpass gromox-kdb2mt –src-host 127.0.0.1 –src-port 12345 –src-user kopano –src-at /mnt –src-mbox jdoe | gromox-mt2exm -u target@domain.de

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 ID on the homeserver and a reference to the LDAP object (e.g. objectUUID). That user ID is not globally unique, therefore ACEs are carried over such that that permissions for user with ID n are transformed to n@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    1@aa8e2b20b2054ca98987ea1053c3bb16.kopano.invalid  1177

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)