Signal-Desktop: how to export messages?

Question

How can signal-desktop messages be exported?

I want to backup my correspondence.

Is it possible at all?

Answer 1

Yes, it is possible.

Just save this in a file <yourFilename>:

sigBase="${HOME}/.config/Signal/";
key=$( /usr/bin/jq -r '."key"' ${sigBase}config.json );
db="${HOME}/.config/Signal/sql/db.sqlite";
clearTextMsgs="${sigBase}clearTextMsgs.csv";

/usr/bin/sqlcipher -list -noheader "$db" "PRAGMA key = \"x'"$key"'\";select json from messages;" > "$clearTextMsgs";

and call it via bash <yourFilename>. Or render it executable with chmod 700 <yourFilename> and call it directly: ./<yourFilename>

This script uses sqlcipher and jq with signal-desktop's database key to open, decrypt and extract all messages in JSON format into clearTextMsgs.csv inside your signal-desktop folder ~/.config/Signal.

Besides the key extraction by filtering JSON with jq (from ~/.config/Signal/config.json), the crucial bit happens here:

sqlcipher -list -noheader <DB> <SQL>

where <SQL> contains the PRAGMA key definition and the actual SQL statement (SELECT json FROM messages;).

One can then use jq to access any key/value from the messages backup.

You have to install sqlcipher and jq for that:

sudo apt install sqlcipher jq

Note:

While this does extract all messages, we need to specify that "all" in signal-desktop has the meaning of "all messages actually loaded". So, in order to extract every single message, the slider of the active contact has to be slid way up, then signal-desktop will load previously not availalble messages (lather rinse repeat until satisfied). Do so as far in the past you would want your messages loaded. This gets tedious quite quickly. Remember to do so for all of your contacts' histories.

Having that said, it is technically feasable to backup your message history, in practice it is a manual job. A way around this might be a cron job backing up all recent messages, maybe once a day. Then this is likely to contain duplicates and might miss messages in case signal-desktop has been restarted.

In any case, this method is working fine if the (not too far -- read: a couple of months maybe) history is to be searched programmatically once in a while.

Answer 2

sigtop worked for me, and has options for export formats.

Answer 3

Basically as Gen.Stack answered, but more worked-out instructions for Debian.

I also removed some unnecessary complexity. E.g.: jq '."key"' can also just be written as jq .key since none of these characters are dynamic and so will never need escaping with two layers of quotes. Variables like "$HOME" are still quoted, since those could contain characters that need quoting.

1. You need to compile sqlcipher from source, at least as of 2022 even the version in sid is not new enough as per rubo77's comment
   1. Install dependencies:
      apt install build-essential libssl-dev tcl libsqlite3-dev
      Not sure if libsqlite3-dev is required, but I installed it as part of debugging stuff.
      If you are missing a dependency but ./configure ran correctly and you tried to make, then subsequent builds will fail until you manually remove sqlite3.h, see https://github.com/sqlcipher/sqlcipher/issues/157
   2. Download the code:
      git clone https://github.com/sqlcipher/sqlcipher.git
cd sqlcipher
   3. Configure with dynamic linking, then compile:
      ./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"
make
2. Install jq to extract your database encryption key:
   apt install jq
3. Set the environment variables for convenience:
   db="$HOME/.config/Signal/sql/db.sqlite"
key="$(jq -r .key "$HOME/.config/Signal/config.json")"
4. Export the messages. Note that I use ./sqlcipher below. If you are not in the compilation directory anymore, you should specify the path, for example: ~/Downloads/sqlcipher/sqlcipher -list ....
   • Export just your messages table:
     ./sqlcipher -list -noheader "$db" "PRAGMA key = \"x'"$key"'\";select json from messages;" > Signal_messages.jsonl
   • Export the whole database with the .dump command:
     echo -e 'PRAGMA key = "x'\'"$key"\''";\n.dump\n' | ./sqlcipher -list -noheader "$db" > Signal_database.sql
   • Just browse the database:

     $ echo "$key"
     SECRETSECRET
     $ ./sqlcipher -list -noheader "$db"
     sqlite> PRAGMA key = "x'SECRETSECRET'";
     ok
     sqlite> .tables
     attachment_downloads   messages_fts_config    sessions
     badgeImageFiles        messages_fts_content   signedPreKeys
     ...
     

   Note that the quotes around the key are all necessary and that the "ok" does not mean that it has checked your key. If you get a "file is not a database" when running something like .tables then, most likely, you got the wrong key or typo'd something or selected the wrong file (or, in a rare case perhaps, the file could be damaged damaged).

Et voilá, wasn't that easy? Why would Signal add a backup feature when you can just do the above!

Answer 4

Find your encrypted db.sqlite database (the decryption key is in the config.json file). Depending on your OS and how you install Signal you may find these files in one of these locations:

Linux:

• ~/.config/Signal/
• ~/.var/app/org.signal.Signal/config/Signal

Windows:

• %AppData%\Signal\config.json

Mac OS:

• ~/Library/Application Support/Signal

What you want to do is execute this SQL query:

SELECT M.sent_at, M.source, C.name, M.type, M.body
  FROM messages AS M
      JOIN conversations AS C
          ON M.conversationId = C.id
ORDER BY M.sent_at;

It returns all messages in a reasonable format:
timestamp; phone number; conversation name; incoming/outgoing; message content

---

If you want a GUI, you can use sqlitebrowser.
Open sqlitebrowser, click Open, select Raw Key, enter your key with a leading 0x as shown in the screenshot, select SQLCipher 4 defaults, click okay:
Go to the Execute SQL tab and paste the aforementioned query, then click the blue triangular play button to execute the query: