Not a database error when running sigtop db

[sylasabdullahnguyen]

Hello,

I am getting the following error when running sigtop db signal.db, sigtop check and sigtop query pragma user_version:

sigtop: cannot verify key: cannot execute SQL statement: file is not a database

I am on a MacBook M1 Pro with Ventura 13.6.3. I have the following versions of sqlite and sqlcipher:

3.41.2 2023-03-22 11:56:21 0d1fc92f94cb6b76bffe3ec34d69cffde2924203304e8ffc4155597af0c191da

or

3.46.0 2024-05-23 13:25:27 96c92aba00c8375bc32fafcdf12429c58bd8aabfcadab6683e35bbb9cdebf19e (64-bit)

after running

echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> \~/.zshrc
source ~/.zshrc

and

3.45.3 2024-04-15 13:34:05 8653b758870e6ef0c98d46b3ace27849054af85da891eb121e9aaa537f1ealt1 (64-bit) (SQLCipher 4.6.0 community)

From what I've found googling, the "not a database" issue generally means that the file is corrupted--but the Signal app itself seems to be functioning fine (for now, anyway). This is the output of hexdump -C db.sqlite | head -1:

00000000  22 45 77 04 d3 ae 7f d1  28 01 17 ca 87 9b 7e 93  |"Ew.....(.....~.|

This seems to be a bad sign according to what I read here, except that my Signal app starts up without an error. I've tried accessing the db.sqlite file (copied over from the /sql folder of the Signal app) directly in:

• DB Browser for SQLite (v3.13.0),
• SQLite Studio (v3.4.4),
• using node and @signalapp/better-sqlite3 in a JS script,
• sqlcipher from terminal, and
• sqlite3 from terminal

with no success. I have also updated sigtop to the latest release. Would you happen to have an idea as to what is going on?

[flatz]

The key format for SQLite was changed in latest versions of desktop Signal. It's now stored in encrypted form in JSON under key encryptedKey, e.g. on MacOS: ~/Library/Application Support/Signal/config.json

To decrypt it the special OS mechanism for safe storage is used, like Keychain on MacOS. First, you need to extract it from safe storage (for example, manually or via some API), it's Base64 encoded and used as input to PBKDF2 HMAC SHA-1 algorithm with salt set to saltysalt, number of iterations set to 1003 and derived key length set to 128 bit. The resulting 128-bit key is our key encryption key (KEK) which will be used later.

Then you should take hexadecimal string of encrypted key from Signal's JSON config, decode it as bytes and strip leading prefix v10. Then apply the result to AES-128 CBC algorithm with KEK and IV consisted of 16 bytes of space characters, unpad it using block size of 16 bytes. You should get ASCII text with hexadecimal character which is your key for SQLite database. It can be used with modifications to sigtop or by crafting old-style JSON config with key parameter that have value of this hexadecimal string.

PoC: https://gist.github.com/flatz/3f242ab3c550d361f8c6d031b07fb6b1

[tbvdm]

As @flatz explained, Signal Desktop recently started to encrypt the database key in config.json. The old unencrypted key would continue to be available as a temporary fallback, but this fallback was removed sooner than I had expected.

I have pushed a commit that should make it possible to decrypt the database key on macOS and Linux (GNOME). For the time being, you will have to fetch the encryption password manually.

@sylasabdullahnguyen, could you please try the following:

brew upgrade --fetch-HEAD sigtop
security find-generic-password -ws "Signal Safe Storage" >passfile
sigtop check -p passfile
sigtop db -p passfile signal.db

Note that passfile will contain the password used to encrypt the database key. Be careful.

[sylasabdullahnguyen]

I've managed to do what @flatz suggested and also ran the commands you've suggested successfully with a working signal.db file created and queryable. Thanks to you both!

I am not very familiar with security technology--would storing the encryption password in passfile be any less secure than what Signal Desktop was doing before this update?

[tbvdm]

It's not less secure. Before the Signal update, you had an unencrypted key in config.json. Now you have an unencrypted key in passfile.

Nevertheless, it's a good idea either to delete passfile when you no longer need it or to store it somewhere safe. Note that you can always recreate passfile using the security command above.

[tbvdm]

sigtop can now also read the password from standard input so you won't have to create a passfile. For example:

security find-generic-password -ws "Signal Safe Storage" | sigtop db -p - signal.db

[sadsirko]

any tips for windows users?

[tbvdm]

sigtop should now be able to decrypt the database key on Windows. No extra steps are needed.

[tbvdm]

Here are provisional instructions for Linux.

If you are using GNOME or any other desktop that uses GNOME Keyring, use secret-tool to fetch the encryption password. For example:

secret-tool lookup application Signal >passfile
sigtop export-messages -p passfile messages
sigtop export-attachments -p passfile attachments

(If you are using Signal Beta, replace Signal with "Signal Beta" in the secret-tool command above.)

Note that passfile will contain the password used to encrypt the database key. Be careful. Delete the file when you no longer need it.

If you are using KDE, use kwallet-query to fetch the encryption password. For example:

kwallet-query -f "Chromium Keys" -r "Chromium Safe Storage" kdewallet >passfile
sigtop export-messages -p passfile messages
sigtop export-attachments -p passfile attachments

Again, note that passfile will contain the password used to encrypt the database key. Be careful. Delete the file when you no longer need it.

sigtop can also read the password from standard input so you won't have to create a passfile. For example:

secret-tool lookup application Signal | sigtop export-messages -p - messages

[stefanputz]

sigtop should now be able to decrypt the database key on Windows. No extra steps are needed.

but how? could you please give some more details? would be much appreciated!

[tbvdm]

sigtop should now be able to get the encryption key from the keychain on macOS. You no longer have to run the security command and provide a passfile.

@sylasabdullahnguyen, or anyone else on macOS, it would be great if you could give this a try. The following commands should run without problems:

brew upgrade --fetch-HEAD sigtop
sigtop check

[flatz]

sigtop should now be able to get the encryption key from the keychain on macOS. You no longer have to run the security command and provide a passfile.

@sylasabdullahnguyen, or anyone else on macOS, it would be great if you could give this a try. The following commands should run without problems:

brew upgrade --fetch-HEAD sigtop
sigtop check

The latest version does work on MacOS without manual keychain access. Thanks.

[tbvdm]

@flatz Great, thanks very much!

@stefanputz The database key is encrypted with the encryption key in the file Local State in your Signal directory. The encryption key, in turn, is encrypted with DPAPI (a Windows API). The point of using DPAPI is that, in principle, data can be encrypted and decrypted only by the same user (or rather any program running as that user), and only on the same computer.

So sigtop first decrypts the encryption key with DPAPI. It then uses the (decrypted) encryption key to decrypt the database key. Finally, it uses the (decrypted) database key to decrypt the database.

[branneman]

@sylasabdullahnguyen, or anyone else on macOS, it would be great if you could give this a try. The following commands should run without problems:

brew upgrade --fetch-HEAD sigtop
sigtop check

Works great for me, it solved the problem! I've tried 2 different machines:

• MacBook Pro, M2, Sonoma 14.6.1
• MacBook Pro, Intel i7, Sonoma 14.5

Thanks a lot!