blog/data/2023-11-12-debian-sqlcipher-signal.txt

   1 Title: New and improved sqlcipher in Debian for accessing Signal database
   2 Tags: english, debian, sikkerhet, surveillance
   3 Date: 2023-11-12 12:00
   4
   5 <p>For a while now I wanted to have direct access to the
   6 <a href="https://signal.org/">Signal</a> database of messages and
   7 channels of my Desktop edition of Signal.  I prefer the enforced end
   8 to end encryption of Signal these days for my communication with
   9 friends and family, to increase the level of safety and privacy as
  10 well as raising the cost of the mass surveillance government and
  11 non-government entities practice these days.  In August I came across
  12 a nice
  13 <a href="https://www.yoranbrondsema.com/post/the-guide-to-extracting-statistics-from-your-signal-conversations/">recipe
  14 on how to use sqlcipher to extract statistics from the Signal
  15 database</a> explaining how to do this.  Unfortunately this did not
  16 work with the version of sqlcipher in Debian.  The
  17 <a href="http://tracker.debian.org/sqlcipher/">sqlcipher</a>
  18 package is a "fork" of the sqlite package with added support for
  19 encrypted databases.  Sadly the current Debian maintainer
  20 <a href="https://bugs.debian.org/961598">announced more than three
  21 years ago that he did not have time to maintain sqlcipher</a>, so it
  22 seemed unlikely to be upgraded by the maintainer.  I was reluctant to
  23 take on the job myself, as I have very limited experience maintaining
  24 shared libraries in Debian.  After waiting and hoping for a few
  25 months, I gave up the last  week, and set out to update the package.  In
  26 the process I orphaned it to make it more obvious for the next person
  27 looking at it that the package need proper maintenance.</p>
  28
  29 <p>The version in Debian was around five years old, and quite a lot of
  30 changes had taken place upstream into the Debian maintenance git
  31 repository.  After spending a few days importing the new upstream
  32 versions, realising that upstream did not care much for SONAME
  33 versioning as I saw library symbols being both added and removed with
  34 minor version number changes to the project, I concluded that I had to
  35 do a SONAME bump of the library package to avoid surprising the
  36 reverse dependencies.  I even added a simple
  37 autopkgtest script to ensure the package work as intended.  Dug deep
  38 into the hole of learning shared library maintenance, I set out a few
  39 days ago to upload the new version to Debian experimental to see what
  40 the quality assurance framework in Debian had to say about the result.
  41 The feedback told me the pacakge was not too shabby, and yesterday I
  42 uploaded the latest version to Debian unstable.  It should enter
  43 testing today or tomorrow, perhaps delayed by
  44 <a href="https://bugs.debian.org/1055812">a small library
  45 transition</a>.</p>
  46
  47 <p>Armed with a new version of sqlcipher, I can now have a look at the
  48 SQL database in ~/.config/Signal/sql/db.sqlite.  First, one need to
  49 fetch the encryption key from the Signal configuration using this
  50 simple JSON extraction command:</p>
  51
  52 <pre>/usr/bin/jq -r '."key"' ~/.config/Signal/config.json</pre>
  53
  54 <p>Assuming the result from that command is 'secretkey', which is a
  55 hexadecimal number representing the key used to encrypt the database.
  56 Next, one can now connect to the database and inject the encryption
  57 key for access via SQL to fetch information from the database.  Here
  58 is an example dumping the database structure:</p>
  59
  60 <pre>
  61 % sqlcipher ~/.config/Signal/sql/db.sqlite
  62 sqlite> PRAGMA key = "x'secretkey'";
  63 sqlite> .schema
  64 CREATE TABLE sqlite_stat1(tbl,idx,stat);
  65 CREATE TABLE conversations(
  66       id STRING PRIMARY KEY ASC,
  67       json TEXT,
  68
  69       active_at INTEGER,
  70       type STRING,
  71       members TEXT,
  72       name TEXT,
  73       profileName TEXT
  74     , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER);
  75 CREATE TABLE identityKeys(
  76       id STRING PRIMARY KEY ASC,
  77       json TEXT
  78     );
  79 CREATE TABLE items(
  80       id STRING PRIMARY KEY ASC,
  81       json TEXT
  82     );
  83 CREATE TABLE sessions(
  84       id TEXT PRIMARY KEY,
  85       conversationId TEXT,
  86       json TEXT
  87     , ourServiceId STRING, serviceId STRING);
  88 CREATE TABLE attachment_downloads(
  89     id STRING primary key,
  90     timestamp INTEGER,
  91     pending INTEGER,
  92     json TEXT
  93   );
  94 CREATE TABLE sticker_packs(
  95     id TEXT PRIMARY KEY,
  96     key TEXT NOT NULL,
  97
  98     author STRING,
  99     coverStickerId INTEGER,
 100     createdAt INTEGER,
 101     downloadAttempts INTEGER,
 102     installedAt INTEGER,
 103     lastUsed INTEGER,
 104     status STRING,
 105     stickerCount INTEGER,
 106     title STRING
 107   , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync
 108       INTEGER DEFAULT 0 NOT NULL);
 109 CREATE TABLE stickers(
 110     id INTEGER NOT NULL,
 111     packId TEXT NOT NULL,
 112
 113     emoji STRING,
 114     height INTEGER,
 115     isCoverOnly INTEGER,
 116     lastUsed INTEGER,
 117     path STRING,
 118     width INTEGER,
 119
 120     PRIMARY KEY (id, packId),
 121     CONSTRAINT stickers_fk
 122       FOREIGN KEY (packId)
 123       REFERENCES sticker_packs(id)
 124       ON DELETE CASCADE
 125   );
 126 CREATE TABLE sticker_references(
 127     messageId STRING,
 128     packId TEXT,
 129     CONSTRAINT sticker_references_fk
 130       FOREIGN KEY(packId)
 131       REFERENCES sticker_packs(id)
 132       ON DELETE CASCADE
 133   );
 134 CREATE TABLE emojis(
 135     shortName TEXT PRIMARY KEY,
 136     lastUsage INTEGER
 137   );
 138 CREATE TABLE messages(
 139         rowid INTEGER PRIMARY KEY ASC,
 140         id STRING UNIQUE,
 141         json TEXT,
 142         readStatus INTEGER,
 143         expires_at INTEGER,
 144         sent_at INTEGER,
 145         schemaVersion INTEGER,
 146         conversationId STRING,
 147         received_at INTEGER,
 148         source STRING,
 149         hasAttachments INTEGER,
 150         hasFileAttachments INTEGER,
 151         hasVisualMediaAttachments INTEGER,
 152         expireTimer INTEGER,
 153         expirationStartTimestamp INTEGER,
 154         type STRING,
 155         body TEXT,
 156         messageTimer INTEGER,
 157         messageTimerStart INTEGER,
 158         messageTimerExpiresAt INTEGER,
 159         isErased INTEGER,
 160         isViewOnce INTEGER,
 161         sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER
 162         GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER
 163         GENERATED ALWAYS AS (
 164           json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1
 165         ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT
 166         GENERATED ALWAYS
 167         AS (ifnull(
 168           expirationStartTimestamp + (expireTimer * 1000),
 169           9007199254740991
 170         )), shouldAffectActivity INTEGER
 171         GENERATED ALWAYS AS (
 172           type IS NULL
 173           OR
 174           type NOT IN (
 175             'change-number-notification',
 176             'contact-removed-notification',
 177             'conversation-merge',
 178             'group-v1-migration',
 179             'keychange',
 180             'message-history-unsynced',
 181             'profile-change',
 182             'story',
 183             'universal-timer-notification',
 184             'verified-change'
 185           )
 186         ), shouldAffectPreview INTEGER
 187         GENERATED ALWAYS AS (
 188           type IS NULL
 189           OR
 190           type NOT IN (
 191             'change-number-notification',
 192             'contact-removed-notification',
 193             'conversation-merge',
 194             'group-v1-migration',
 195             'keychange',
 196             'message-history-unsynced',
 197             'profile-change',
 198             'story',
 199             'universal-timer-notification',
 200             'verified-change'
 201           )
 202         ), isUserInitiatedMessage INTEGER
 203         GENERATED ALWAYS AS (
 204           type IS NULL
 205           OR
 206           type NOT IN (
 207             'change-number-notification',
 208             'contact-removed-notification',
 209             'conversation-merge',
 210             'group-v1-migration',
 211             'group-v2-change',
 212             'keychange',
 213             'message-history-unsynced',
 214             'profile-change',
 215             'story',
 216             'universal-timer-notification',
 217             'verified-change'
 218           )
 219         ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER
 220         GENERATED ALWAYS AS (
 221           type IS 'group-v2-change' AND
 222           json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND
 223           json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND
 224           json_extract(json, '$.groupV2Change.from') IS NOT NULL AND
 225           json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci')
 226         ), isGroupLeaveEventFromOther INTEGER
 227         GENERATED ALWAYS AS (
 228           isGroupLeaveEvent IS 1
 229           AND
 230           isChangeCreatedByUs IS 0
 231         ), callId TEXT
 232         GENERATED ALWAYS AS (
 233           json_extract(json, '$.callId')
 234         ));
 235 CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
 236 CREATE TABLE jobs(
 237         id TEXT PRIMARY KEY,
 238         queueType TEXT STRING NOT NULL,
 239         timestamp INTEGER NOT NULL,
 240         data STRING TEXT
 241       );
 242 CREATE TABLE reactions(
 243         conversationId STRING,
 244         emoji STRING,
 245         fromId STRING,
 246         messageReceivedAt INTEGER,
 247         targetAuthorAci STRING,
 248         targetTimestamp INTEGER,
 249         unread INTEGER
 250       , messageId STRING);
 251 CREATE TABLE senderKeys(
 252         id TEXT PRIMARY KEY NOT NULL,
 253         senderId TEXT NOT NULL,
 254         distributionId TEXT NOT NULL,
 255         data BLOB NOT NULL,
 256         lastUpdatedDate NUMBER NOT NULL
 257       );
 258 CREATE TABLE unprocessed(
 259         id STRING PRIMARY KEY ASC,
 260         timestamp INTEGER,
 261         version INTEGER,
 262         attempts INTEGER,
 263         envelope TEXT,
 264         decrypted TEXT,
 265         source TEXT,
 266         serverTimestamp INTEGER,
 267         sourceServiceId STRING
 268       , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER);
 269 CREATE TABLE sendLogPayloads(
 270         id INTEGER PRIMARY KEY ASC,
 271
 272         timestamp INTEGER NOT NULL,
 273         contentHint INTEGER NOT NULL,
 274         proto BLOB NOT NULL
 275       , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL);
 276 CREATE TABLE sendLogRecipients(
 277         payloadId INTEGER NOT NULL,
 278
 279         recipientServiceId STRING NOT NULL,
 280         deviceId INTEGER NOT NULL,
 281
 282         PRIMARY KEY (payloadId, recipientServiceId, deviceId),
 283
 284         CONSTRAINT sendLogRecipientsForeignKey
 285           FOREIGN KEY (payloadId)
 286           REFERENCES sendLogPayloads(id)
 287           ON DELETE CASCADE
 288       );
 289 CREATE TABLE sendLogMessageIds(
 290         payloadId INTEGER NOT NULL,
 291
 292         messageId STRING NOT NULL,
 293
 294         PRIMARY KEY (payloadId, messageId),
 295
 296         CONSTRAINT sendLogMessageIdsForeignKey
 297           FOREIGN KEY (payloadId)
 298           REFERENCES sendLogPayloads(id)
 299           ON DELETE CASCADE
 300       );
 301 CREATE TABLE preKeys(
 302         id STRING PRIMARY KEY ASC,
 303         json TEXT
 304       , ourServiceId NUMBER
 305         GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
 306 CREATE TABLE signedPreKeys(
 307         id STRING PRIMARY KEY ASC,
 308         json TEXT
 309       , ourServiceId NUMBER
 310         GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
 311 CREATE TABLE badges(
 312         id TEXT PRIMARY KEY,
 313         category TEXT NOT NULL,
 314         name TEXT NOT NULL,
 315         descriptionTemplate TEXT NOT NULL
 316       );
 317 CREATE TABLE badgeImageFiles(
 318         badgeId TEXT REFERENCES badges(id)
 319           ON DELETE CASCADE
 320           ON UPDATE CASCADE,
 321         'order' INTEGER NOT NULL,
 322         url TEXT NOT NULL,
 323         localPath TEXT,
 324         theme TEXT NOT NULL
 325       );
 326 CREATE TABLE storyReads (
 327         authorId STRING NOT NULL,
 328         conversationId STRING NOT NULL,
 329         storyId STRING NOT NULL,
 330         storyReadDate NUMBER NOT NULL,
 331
 332         PRIMARY KEY (authorId, storyId)
 333       );
 334 CREATE TABLE storyDistributions(
 335         id STRING PRIMARY KEY NOT NULL,
 336         name TEXT,
 337
 338         senderKeyInfoJson STRING
 339       , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER);
 340 CREATE TABLE storyDistributionMembers(
 341         listId STRING NOT NULL REFERENCES storyDistributions(id)
 342           ON DELETE CASCADE
 343           ON UPDATE CASCADE,
 344         serviceId STRING NOT NULL,
 345
 346         PRIMARY KEY (listId, serviceId)
 347       );
 348 CREATE TABLE uninstalled_sticker_packs (
 349         id STRING NOT NULL PRIMARY KEY,
 350         uninstalledAt NUMBER NOT NULL,
 351         storageID STRING,
 352         storageVersion NUMBER,
 353         storageUnknownFields BLOB,
 354         storageNeedsSync INTEGER NOT NULL
 355       );
 356 CREATE TABLE groupCallRingCancellations(
 357         ringId INTEGER PRIMARY KEY,
 358         createdAt INTEGER NOT NULL
 359       );
 360 CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
 361 CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
 362 CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0);
 363 CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
 364 CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
 365 CREATE TABLE edited_messages(
 366         messageId STRING REFERENCES messages(id)
 367           ON DELETE CASCADE,
 368         sentAt INTEGER,
 369         readStatus INTEGER
 370       , conversationId STRING);
 371 CREATE TABLE mentions (
 372         messageId REFERENCES messages(id) ON DELETE CASCADE,
 373         mentionAci STRING,
 374         start INTEGER,
 375         length INTEGER
 376       );
 377 CREATE TABLE kyberPreKeys(
 378         id STRING PRIMARY KEY NOT NULL,
 379         json TEXT NOT NULL, ourServiceId NUMBER
 380         GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
 381 CREATE TABLE callsHistory (
 382         callId TEXT PRIMARY KEY,
 383         peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId
 384         ringerId TEXT DEFAULT NULL, -- ringer uuid
 385         mode TEXT NOT NULL, -- enum "Direct" | "Group"
 386         type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group"
 387         direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing
 388         -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted"
 389         -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted"
 390         status TEXT NOT NULL,
 391         timestamp INTEGER NOT NULL,
 392         UNIQUE (callId, peerId) ON CONFLICT FAIL
 393       );
 394 [ dropped all indexes to save space in this blog post ]
 395 CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages
 396       WHEN
 397         new.body IS NOT NULL AND new.isViewOnce = 1
 398       BEGIN
 399         DELETE FROM messages_fts WHERE rowid = old.rowid;
 400       END;
 401 CREATE TRIGGER messages_on_insert AFTER INSERT ON messages
 402       WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL
 403       BEGIN
 404         INSERT INTO messages_fts
 405           (rowid, body)
 406         VALUES
 407           (new.rowid, new.body);
 408       END;
 409 CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN
 410         DELETE FROM messages_fts WHERE rowid = old.rowid;
 411         DELETE FROM sendLogPayloads WHERE id IN (
 412           SELECT payloadId FROM sendLogMessageIds
 413           WHERE messageId = old.id
 414         );
 415         DELETE FROM reactions WHERE rowid IN (
 416           SELECT rowid FROM reactions
 417           WHERE messageId = old.id
 418         );
 419         DELETE FROM storyReads WHERE storyId = old.storyId;
 420       END;
 421 CREATE VIRTUAL TABLE messages_fts USING fts5(
 422         body,
 423         tokenize = 'signal_tokenizer'
 424       );
 425 CREATE TRIGGER messages_on_update AFTER UPDATE ON messages
 426       WHEN
 427         (new.body IS NULL OR old.body IS NOT new.body) AND
 428          new.isViewOnce IS NOT 1 AND new.storyId IS NULL
 429       BEGIN
 430         DELETE FROM messages_fts WHERE rowid = old.rowid;
 431         INSERT INTO messages_fts
 432           (rowid, body)
 433         VALUES
 434           (new.rowid, new.body);
 435       END;
 436 CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages
 437       BEGIN
 438         INSERT INTO mentions (messageId, mentionAci, start, length)
 439
 440     SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
 441       bodyRanges.value ->> 'start' as start,
 442       bodyRanges.value ->> 'length' as length
 443     FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
 444     WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
 445
 446         AND messages.id = new.id;
 447       END;
 448 CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages
 449       BEGIN
 450         DELETE FROM mentions WHERE messageId = new.id;
 451         INSERT INTO mentions (messageId, mentionAci, start, length)
 452
 453     SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
 454       bodyRanges.value ->> 'start' as start,
 455       bodyRanges.value ->> 'length' as length
 456     FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
 457     WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
 458
 459         AND messages.id = new.id;
 460       END;
 461 sqlite>
 462 </pre>
 463
 464 <p>Finally I have the tool needed to inspect and process Signal
 465 messages that I need, without using the vendor provided client.  Now
 466 on to transforming it to a more useful format.</p>
 467
 468 <p>As usual, if you use Bitcoin and want to show your support of my
 469 activities, please send Bitcoin donations to my address
 470 <b><a href="bitcoin:15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b">15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b</a></b>.</p>