Petter Reinholdtsen: New and improved sqlcipher in Debian for accessing Signal database

+% sqlcipher ~/.config/Signal/sql/db.sqlite +sqlite> PRAGMA key = "x'secretkey'"; +sqlite> .schema +CREATE TABLE sqlite_stat1(tbl,idx,stat); +CREATE TABLE conversations( + id STRING PRIMARY KEY ASC, + json TEXT, + + active_at INTEGER, + type STRING, + members TEXT, + name TEXT, + profileName TEXT + , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER); +CREATE TABLE identityKeys( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE items( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE sessions( + id TEXT PRIMARY KEY, + conversationId TEXT, + json TEXT + , ourServiceId STRING, serviceId STRING); +CREATE TABLE attachment_downloads( + id STRING primary key, + timestamp INTEGER, + pending INTEGER, + json TEXT + ); +CREATE TABLE sticker_packs( + id TEXT PRIMARY KEY, + key TEXT NOT NULL, + + author STRING, + coverStickerId INTEGER, + createdAt INTEGER, + downloadAttempts INTEGER, + installedAt INTEGER, + lastUsed INTEGER, + status STRING, + stickerCount INTEGER, + title STRING + , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync + INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE stickers( + id INTEGER NOT NULL, + packId TEXT NOT NULL, + + emoji STRING, + height INTEGER, + isCoverOnly INTEGER, + lastUsed INTEGER, + path STRING, + width INTEGER, + + PRIMARY KEY (id, packId), + CONSTRAINT stickers_fk + FOREIGN KEY (packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE sticker_references( + messageId STRING, + packId TEXT, + CONSTRAINT sticker_references_fk + FOREIGN KEY(packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE emojis( + shortName TEXT PRIMARY KEY, + lastUsage INTEGER + ); +CREATE TABLE messages( + rowid INTEGER PRIMARY KEY ASC, + id STRING UNIQUE, + json TEXT, + readStatus INTEGER, + expires_at INTEGER, + sent_at INTEGER, + schemaVersion INTEGER, + conversationId STRING, + received_at INTEGER, + source STRING, + hasAttachments INTEGER, + hasFileAttachments INTEGER, + hasVisualMediaAttachments INTEGER, + expireTimer INTEGER, + expirationStartTimestamp INTEGER, + type STRING, + body TEXT, + messageTimer INTEGER, + messageTimerStart INTEGER, + messageTimerExpiresAt INTEGER, + isErased INTEGER, + isViewOnce INTEGER, + sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER + GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER + GENERATED ALWAYS AS ( + json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1 + ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT + GENERATED ALWAYS + AS (ifnull( + expirationStartTimestamp + (expireTimer * 1000), + 9007199254740991 + )), shouldAffectActivity INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), shouldAffectPreview INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), isUserInitiatedMessage INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'group-v2-change', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER + GENERATED ALWAYS AS ( + type IS 'group-v2-change' AND + json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND + json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND + json_extract(json, '$.groupV2Change.from') IS NOT NULL AND + json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci') + ), isGroupLeaveEventFromOther INTEGER + GENERATED ALWAYS AS ( + isGroupLeaveEvent IS 1 + AND + isChangeCreatedByUs IS 0 + ), callId TEXT + GENERATED ALWAYS AS ( + json_extract(json, '$.callId') + )); +CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); +CREATE TABLE jobs( + id TEXT PRIMARY KEY, + queueType TEXT STRING NOT NULL, + timestamp INTEGER NOT NULL, + data STRING TEXT + ); +CREATE TABLE reactions( + conversationId STRING, + emoji STRING, + fromId STRING, + messageReceivedAt INTEGER, + targetAuthorAci STRING, + targetTimestamp INTEGER, + unread INTEGER + , messageId STRING); +CREATE TABLE senderKeys( + id TEXT PRIMARY KEY NOT NULL, + senderId TEXT NOT NULL, + distributionId TEXT NOT NULL, + data BLOB NOT NULL, + lastUpdatedDate NUMBER NOT NULL + ); +CREATE TABLE unprocessed( + id STRING PRIMARY KEY ASC, + timestamp INTEGER, + version INTEGER, + attempts INTEGER, + envelope TEXT, + decrypted TEXT, + source TEXT, + serverTimestamp INTEGER, + sourceServiceId STRING + , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER); +CREATE TABLE sendLogPayloads( + id INTEGER PRIMARY KEY ASC, + + timestamp INTEGER NOT NULL, + contentHint INTEGER NOT NULL, + proto BLOB NOT NULL + , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE sendLogRecipients( + payloadId INTEGER NOT NULL, + + recipientServiceId STRING NOT NULL, + deviceId INTEGER NOT NULL, + + PRIMARY KEY (payloadId, recipientServiceId, deviceId), + + CONSTRAINT sendLogRecipientsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE sendLogMessageIds( + payloadId INTEGER NOT NULL, + + messageId STRING NOT NULL, + + PRIMARY KEY (payloadId, messageId), + + CONSTRAINT sendLogMessageIdsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE preKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE signedPreKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE badges( + id TEXT PRIMARY KEY, + category TEXT NOT NULL, + name TEXT NOT NULL, + descriptionTemplate TEXT NOT NULL + ); +CREATE TABLE badgeImageFiles( + badgeId TEXT REFERENCES badges(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + 'order' INTEGER NOT NULL, + url TEXT NOT NULL, + localPath TEXT, + theme TEXT NOT NULL + ); +CREATE TABLE storyReads ( + authorId STRING NOT NULL, + conversationId STRING NOT NULL, + storyId STRING NOT NULL, + storyReadDate NUMBER NOT NULL, + + PRIMARY KEY (authorId, storyId) + ); +CREATE TABLE storyDistributions( + id STRING PRIMARY KEY NOT NULL, + name TEXT, + + senderKeyInfoJson STRING + , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER); +CREATE TABLE storyDistributionMembers( + listId STRING NOT NULL REFERENCES storyDistributions(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + serviceId STRING NOT NULL, + + PRIMARY KEY (listId, serviceId) + ); +CREATE TABLE uninstalled_sticker_packs ( + id STRING NOT NULL PRIMARY KEY, + uninstalledAt NUMBER NOT NULL, + storageID STRING, + storageVersion NUMBER, + storageUnknownFields BLOB, + storageNeedsSync INTEGER NOT NULL + ); +CREATE TABLE groupCallRingCancellations( + ringId INTEGER PRIMARY KEY, + createdAt INTEGER NOT NULL + ); +CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID; +CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0); +CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID; +CREATE TABLE edited_messages( + messageId STRING REFERENCES messages(id) + ON DELETE CASCADE, + sentAt INTEGER, + readStatus INTEGER + , conversationId STRING); +CREATE TABLE mentions ( + messageId REFERENCES messages(id) ON DELETE CASCADE, + mentionAci STRING, + start INTEGER, + length INTEGER + ); +CREATE TABLE kyberPreKeys( + id STRING PRIMARY KEY NOT NULL, + json TEXT NOT NULL, ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE callsHistory ( + callId TEXT PRIMARY KEY, + peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId + ringerId TEXT DEFAULT NULL, -- ringer uuid + mode TEXT NOT NULL, -- enum "Direct" | "Group" + type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group" + direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing + -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted" + -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted" + status TEXT NOT NULL, + timestamp INTEGER NOT NULL, + UNIQUE (callId, peerId) ON CONFLICT FAIL + ); +[ dropped all indexes to save space in this blog post ] +CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages + WHEN + new.body IS NOT NULL AND new.isViewOnce = 1 + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + END; +CREATE TRIGGER messages_on_insert AFTER INSERT ON messages + WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + DELETE FROM sendLogPayloads WHERE id IN ( + SELECT payloadId FROM sendLogMessageIds + WHERE messageId = old.id + ); + DELETE FROM reactions WHERE rowid IN ( + SELECT rowid FROM reactions + WHERE messageId = old.id + ); + DELETE FROM storyReads WHERE storyId = old.storyId; + END; +CREATE VIRTUAL TABLE messages_fts USING fts5( + body, + tokenize = 'signal_tokenizer' + ); +CREATE TRIGGER messages_on_update AFTER UPDATE ON messages + WHEN + (new.body IS NULL OR old.body IS NOT new.body) AND + new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages + BEGIN + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages + BEGIN + DELETE FROM mentions WHERE messageId = new.id; + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +sqlite> +

New chrpath release 0.17 @@ -225,7 +711,7 @@ activities, please send Bitcoin donations to my address

October (1)

November (2)

November (3)

@@ -641,7 +1127,7 @@ activities, please send Bitcoin donations to my address

@@ -655,7 +1141,7 @@ activities, please send Bitcoin donations to my address

@@ -729,7 +1215,7 @@ activities, please send Bitcoin donations to my address

@@ -741,7 +1227,7 @@ activities, please send Bitcoin donations to my address

diff --git a/blog/geteltorito_make_CD_firmware_upgrades_a_breeze.html b/blog/geteltorito_make_CD_firmware_upgrades_a_breeze.html index a4f48d026d..087a2f79d1 100644 --- a/blog/geteltorito_make_CD_firmware_upgrades_a_breeze.html +++ b/blog/geteltorito_make_CD_firmware_upgrades_a_breeze.html @@ -81,7 +81,7 @@ minutes my machine had the latest and greatest BIOS firmware in place.

October (1)

November (2)

November (3)

@@ -497,7 +497,7 @@ minutes my machine had the latest and greatest BIOS firmware in place.

@@ -511,7 +511,7 @@ minutes my machine had the latest and greatest BIOS firmware in place.

@@ -585,7 +585,7 @@ minutes my machine had the latest and greatest BIOS firmware in place.

@@ -597,7 +597,7 @@ minutes my machine had the latest and greatest BIOS firmware in place.

diff --git a/blog/index.html b/blog/index.html index e11281187c..0ebaddd32d 100644 --- a/blog/index.html +++ b/blog/index.html @@ -19,6 +19,486 @@ +

New and improved sqlcipher in Debian for accessing Signal database

12th November 2023

For a while now I wanted to have direct access to the +Signal database of messages and +channels of my Desktop edition of Signal. I prefer the enforced end +to end encryption of Signal these days for my communication with +friends and family, to increase the level of safety and privacy as +well as raising the cost of the mass surveillance government and +non-government entities practice these days. In August I came across +a nice +recipe +on how to use sqlcipher to extract statistics from the Signal +database explaining how to do this. Unfortunately this did not +work with the version of sqlcipher in Debian. The +sqlcipher +package is a "fork" of the sqlite package with added support for +encrypted databases. Sadly the current Debian maintainer +announced more than three +years ago that he did not have time to maintain sqlcipher, so it +seemed unlikely to be upgraded by the maintainer. I was reluctant to +take on the job myself, as I have very limited experience maintaining +shared libraries in Debian. After waiting and hoping for a few +months, I gave up the last week, and set out to update the package. In +the process I orphaned it to make it more obvious for the next person +looking at it that the package need proper maintenance.

+ +

The version in Debian was around five years old, and quite a lot of +changes had taken place upstream into the Debian maintenance git +repository. After spending a few days importing the new upstream +versions, realising that upstream did not care much for SONAME +versioning as I saw library symbols being both added and removed with +minor version number changes to the project, I concluded that I had to +do a SONAME bump of the library package to avoid surprising the +reverse dependencies. I even added a simple +autopkgtest script to ensure the package work as intended. Dug deep +into the hole of learning shared library maintenance, I set out a few +days ago to upload the new version to Debian experimental to see what +the quality assurance framework in Debian had to say about the result. +The feedback told me the pacakge was not too shabby, and yesterday I +uploaded the latest version to Debian unstable. It should enter +testing today or tomorrow, perhaps delayed by +a small library +transition.

+ +

Armed with a new version of sqlcipher, I can now have a look at the +SQL database in ~/.config/Signal/sql/db.sqlite. First, one need to +fetch the encryption key from the Signal configuration using this +simple JSON extraction command:

+ +

/usr/bin/jq -r '."key"' ~/.config/Signal/config.json

+ +

Assuming the result from that command is 'secretkey', which is a +hexadecimal number representing the key used to encrypt the database. +Next, one can now connect to the database and inject the encryption +key for access via SQL to fetch information from the database. Here +is an example dumping the database structure:

+ +

+% sqlcipher ~/.config/Signal/sql/db.sqlite
+sqlite> PRAGMA key = "x'secretkey'";
+sqlite> .schema
+CREATE TABLE sqlite_stat1(tbl,idx,stat);
+CREATE TABLE conversations(
+      id STRING PRIMARY KEY ASC,
+      json TEXT,
+
+      active_at INTEGER,
+      type STRING,
+      members TEXT,
+      name TEXT,
+      profileName TEXT
+    , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER);
+CREATE TABLE identityKeys(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE items(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE sessions(
+      id TEXT PRIMARY KEY,
+      conversationId TEXT,
+      json TEXT
+    , ourServiceId STRING, serviceId STRING);
+CREATE TABLE attachment_downloads(
+    id STRING primary key,
+    timestamp INTEGER,
+    pending INTEGER,
+    json TEXT
+  );
+CREATE TABLE sticker_packs(
+    id TEXT PRIMARY KEY,
+    key TEXT NOT NULL,
+
+    author STRING,
+    coverStickerId INTEGER,
+    createdAt INTEGER,
+    downloadAttempts INTEGER,
+    installedAt INTEGER,
+    lastUsed INTEGER,
+    status STRING,
+    stickerCount INTEGER,
+    title STRING
+  , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync
+      INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE stickers(
+    id INTEGER NOT NULL,
+    packId TEXT NOT NULL,
+
+    emoji STRING,
+    height INTEGER,
+    isCoverOnly INTEGER,
+    lastUsed INTEGER,
+    path STRING,
+    width INTEGER,
+
+    PRIMARY KEY (id, packId),
+    CONSTRAINT stickers_fk
+      FOREIGN KEY (packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE sticker_references(
+    messageId STRING,
+    packId TEXT,
+    CONSTRAINT sticker_references_fk
+      FOREIGN KEY(packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE emojis(
+    shortName TEXT PRIMARY KEY,
+    lastUsage INTEGER
+  );
+CREATE TABLE messages(
+        rowid INTEGER PRIMARY KEY ASC,
+        id STRING UNIQUE,
+        json TEXT,
+        readStatus INTEGER,
+        expires_at INTEGER,
+        sent_at INTEGER,
+        schemaVersion INTEGER,
+        conversationId STRING,
+        received_at INTEGER,
+        source STRING,
+        hasAttachments INTEGER,
+        hasFileAttachments INTEGER,
+        hasVisualMediaAttachments INTEGER,
+        expireTimer INTEGER,
+        expirationStartTimestamp INTEGER,
+        type STRING,
+        body TEXT,
+        messageTimer INTEGER,
+        messageTimerStart INTEGER,
+        messageTimerExpiresAt INTEGER,
+        isErased INTEGER,
+        isViewOnce INTEGER,
+        sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER
+        GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1
+        ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT
+        GENERATED ALWAYS
+        AS (ifnull(
+          expirationStartTimestamp + (expireTimer * 1000),
+          9007199254740991
+        )), shouldAffectActivity INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), shouldAffectPreview INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), isUserInitiatedMessage INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'group-v2-change',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER
+        GENERATED ALWAYS AS (
+          type IS 'group-v2-change' AND
+          json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND
+          json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND
+          json_extract(json, '$.groupV2Change.from') IS NOT NULL AND
+          json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci')
+        ), isGroupLeaveEventFromOther INTEGER
+        GENERATED ALWAYS AS (
+          isGroupLeaveEvent IS 1
+          AND
+          isChangeCreatedByUs IS 0
+        ), callId TEXT
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.callId')
+        ));
+CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
+CREATE TABLE jobs(
+        id TEXT PRIMARY KEY,
+        queueType TEXT STRING NOT NULL,
+        timestamp INTEGER NOT NULL,
+        data STRING TEXT
+      );
+CREATE TABLE reactions(
+        conversationId STRING,
+        emoji STRING,
+        fromId STRING,
+        messageReceivedAt INTEGER,
+        targetAuthorAci STRING,
+        targetTimestamp INTEGER,
+        unread INTEGER
+      , messageId STRING);
+CREATE TABLE senderKeys(
+        id TEXT PRIMARY KEY NOT NULL,
+        senderId TEXT NOT NULL,
+        distributionId TEXT NOT NULL,
+        data BLOB NOT NULL,
+        lastUpdatedDate NUMBER NOT NULL
+      );
+CREATE TABLE unprocessed(
+        id STRING PRIMARY KEY ASC,
+        timestamp INTEGER,
+        version INTEGER,
+        attempts INTEGER,
+        envelope TEXT,
+        decrypted TEXT,
+        source TEXT,
+        serverTimestamp INTEGER,
+        sourceServiceId STRING
+      , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER);
+CREATE TABLE sendLogPayloads(
+        id INTEGER PRIMARY KEY ASC,
+
+        timestamp INTEGER NOT NULL,
+        contentHint INTEGER NOT NULL,
+        proto BLOB NOT NULL
+      , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE sendLogRecipients(
+        payloadId INTEGER NOT NULL,
+
+        recipientServiceId STRING NOT NULL,
+        deviceId INTEGER NOT NULL,
+
+        PRIMARY KEY (payloadId, recipientServiceId, deviceId),
+
+        CONSTRAINT sendLogRecipientsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE sendLogMessageIds(
+        payloadId INTEGER NOT NULL,
+
+        messageId STRING NOT NULL,
+
+        PRIMARY KEY (payloadId, messageId),
+
+        CONSTRAINT sendLogMessageIdsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE preKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE signedPreKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE badges(
+        id TEXT PRIMARY KEY,
+        category TEXT NOT NULL,
+        name TEXT NOT NULL,
+        descriptionTemplate TEXT NOT NULL
+      );
+CREATE TABLE badgeImageFiles(
+        badgeId TEXT REFERENCES badges(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        'order' INTEGER NOT NULL,
+        url TEXT NOT NULL,
+        localPath TEXT,
+        theme TEXT NOT NULL
+      );
+CREATE TABLE storyReads (
+        authorId STRING NOT NULL,
+        conversationId STRING NOT NULL,
+        storyId STRING NOT NULL,
+        storyReadDate NUMBER NOT NULL,
+
+        PRIMARY KEY (authorId, storyId)
+      );
+CREATE TABLE storyDistributions(
+        id STRING PRIMARY KEY NOT NULL,
+        name TEXT,
+
+        senderKeyInfoJson STRING
+      , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER);
+CREATE TABLE storyDistributionMembers(
+        listId STRING NOT NULL REFERENCES storyDistributions(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        serviceId STRING NOT NULL,
+
+        PRIMARY KEY (listId, serviceId)
+      );
+CREATE TABLE uninstalled_sticker_packs (
+        id STRING NOT NULL PRIMARY KEY,
+        uninstalledAt NUMBER NOT NULL,
+        storageID STRING,
+        storageVersion NUMBER,
+        storageUnknownFields BLOB,
+        storageNeedsSync INTEGER NOT NULL
+      );
+CREATE TABLE groupCallRingCancellations(
+        ringId INTEGER PRIMARY KEY,
+        createdAt INTEGER NOT NULL
+      );
+CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
+CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0);
+CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
+CREATE TABLE edited_messages(
+        messageId STRING REFERENCES messages(id)
+          ON DELETE CASCADE,
+        sentAt INTEGER,
+        readStatus INTEGER
+      , conversationId STRING);
+CREATE TABLE mentions (
+        messageId REFERENCES messages(id) ON DELETE CASCADE,
+        mentionAci STRING,
+        start INTEGER,
+        length INTEGER
+      );
+CREATE TABLE kyberPreKeys(
+        id STRING PRIMARY KEY NOT NULL,
+        json TEXT NOT NULL, ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE callsHistory (
+        callId TEXT PRIMARY KEY,
+        peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId
+        ringerId TEXT DEFAULT NULL, -- ringer uuid
+        mode TEXT NOT NULL, -- enum "Direct" | "Group"
+        type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group"
+        direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing
+        -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted"
+        -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted"
+        status TEXT NOT NULL,
+        timestamp INTEGER NOT NULL,
+        UNIQUE (callId, peerId) ON CONFLICT FAIL
+      );
+[ dropped all indexes to save space in this blog post ]
+CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages
+      WHEN
+        new.body IS NOT NULL AND new.isViewOnce = 1
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+      END;
+CREATE TRIGGER messages_on_insert AFTER INSERT ON messages
+      WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        DELETE FROM sendLogPayloads WHERE id IN (
+          SELECT payloadId FROM sendLogMessageIds
+          WHERE messageId = old.id
+        );
+        DELETE FROM reactions WHERE rowid IN (
+          SELECT rowid FROM reactions
+          WHERE messageId = old.id
+        );
+        DELETE FROM storyReads WHERE storyId = old.storyId;
+      END;
+CREATE VIRTUAL TABLE messages_fts USING fts5(
+        body,
+        tokenize = 'signal_tokenizer'
+      );
+CREATE TRIGGER messages_on_update AFTER UPDATE ON messages
+      WHEN
+        (new.body IS NULL OR old.body IS NOT new.body) AND
+         new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages
+      BEGIN
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages
+      BEGIN
+        DELETE FROM mentions WHERE messageId = new.id;
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+sqlite>
+

+ +

Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format.

+ +

As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b.

+ + + Tags: debian, english, sikkerhet, surveillance. + + +

New chrpath release 0.17

10th November 2023

@@ -545,156 +1025,6 @@ activities, please send Bitcoin donations to my address

Speech to text, she APTly whispered, how hard can it be?

23rd April 2023

While visiting a convention during Easter, it occurred to me that -it would be great if I could have a digital Dictaphone with -transcribing capabilities, providing me with texts to cut-n-paste into -stuff I need to write. The background is that long drives often bring -up the urge to write on texts I am working on, which of course is out -of the question while driving. With the release of -OpenAI Whisper, this -seem to be within reach with Free Software, so I decided to give it a -go. OpenAI Whisper is a Linux based neural network system to read in -audio files and provide text representation of the speech in that -audio recording. It handle multiple languages and according to its -creators even can translate into a different language than the spoken -one. I have not tested the latter feature. It can either use the CPU -or a GPU with CUDA support. As far as I can tell, CUDA in practice -limit that feature to NVidia graphics cards. I have few of those, as -they do not work great with free software drivers, and have not tested -the GPU option. While looking into the matter, I did discover some -work to provide CUDA support on non-NVidia GPUs, and some work with -the library used by Whisper to port it to other GPUs, but have not -spent much time looking into GPU support yet. I've so far used an old -X220 laptop as my test machine, and only transcribed using its -CPU.

- -

As it from a privacy standpoint is unthinkable to use computers -under control of someone else (aka a "cloud" service) to transcribe -ones thoughts and personal notes, I want to run the transcribing -system locally on my own computers. The only sensible approach to me -is to make the effort I put into this available for any Linux user and -to upload the needed packages into Debian. Looking at Debian Bookworm, I -discovered that only three packages were missing, -tiktoken, -triton, and -openai-whisper. For a while -I also believed -ffmpeg-python was -needed, but as its -upstream -seem to have vanished I found it safer -to rewrite -whisper to stop depending on in than to introduce ffmpeg-python -into Debian. I decided to place these packages under the umbrella of -the Debian Deep -Learning Team, which seem like the best team to look after such -packages. Discussing the topic within the group also made me aware -that the triton package was already a future dependency of newer -versions of the torch package being planned, and would be needed after -Bookworm is released.

- -

All required code packages have been now waiting in -the Debian NEW -queue since Wednesday, heading for Debian Experimental until -Bookworm is released. An unsolved issue is how to handle the neural -network models used by Whisper. The default behaviour of Whisper is -to require Internet connectivity and download the model requested to -~/.cache/whisper/ on first invocation. This obviously would -fail the -deserted island test of free software as the Debian packages would -be unusable for someone stranded with only the Debian archive and solar -powered computer on a deserted island.

- -

Because of this, I would love to include the models in the Debian -mirror system. This is problematic, as the models are very large -files, which would put a heavy strain on the Debian mirror -infrastructure around the globe. The strain would be even higher if -the models change often, which luckily as far as I can tell they do -not. The small model, which according to its creator is most useful -for English and in my experience is not doing a great job there -either, is 462 MiB (deb is 414 MiB). The medium model, which to me -seem to handle English speech fairly well is 1.5 GiB (deb is 1.3 GiB) -and the large model is 2.9 GiB (deb is 2.6 GiB). I would assume -everyone with enough resources would prefer to use the large model for -highest quality. I believe the models themselves would have to go -into the non-free part of the Debian archive, as they are not really -including any useful source code for updating the models. The -"source", aka the model training set, according to the creators -consist of "680,000 hours of multilingual and multitask supervised -data collected from the web", which to me reads material with both -unknown copyright terms, unavailable to the general public. In other -words, the source is not available according to the Debian Free -Software Guidelines and the model should be considered non-free.

- -

I asked the Debian FTP masters for advice regarding uploading a -model package on their IRC channel, and based on the feedback there it -is still unclear to me if such package would be accepted into the -archive. In any case I wrote build rules for a -OpenAI -Whisper model package and -modified the -Whisper code base to prefer shared files under /usr/ and -/var/ over user specific files in ~/.cache/whisper/ -to be able to use these model packages, to prepare for such -possibility. One solution might be to include only one of the models -(small or medium, I guess) in the Debian archive, and ask people to -download the others from the Internet. Not quite sure what to do -here, and advice is most welcome (use the debian-ai mailing list).

- -

To make it easier to test the new packages while I wait for them to -clear the NEW queue, I created an APT source targeting bookworm. I -selected Bookworm instead of Bullseye, even though I know the latter -would reach more users, is that some of the required dependencies are -missing from Bullseye and I during this phase of testing did not want -to backport a lot of packages just to get up and running.

- -

Here is a recipe to run as user root if you want to test OpenAI -Whisper using Debian packages on your Debian Bookworm installation, -first adding the APT repository GPG key to the list of trusted keys, -then setting up the APT repository and finally installing the packages -and one of the models:

- -

-curl https://geekbay.nuug.no/~pere/openai-whisper/D78F5C4796F353D211B119E28200D9B589641240.asc \
-  -o /etc/apt/trusted.gpg.d/pere-whisper.asc
-mkdir -p /etc/apt/sources.list.d
-cat > /etc/apt/sources.list.d/pere-whisper.list <<EOF
-deb https://geekbay.nuug.no/~pere/openai-whisper/ bookworm main
-deb-src https://geekbay.nuug.no/~pere/openai-whisper/ bookworm main
-EOF
-apt update
-apt install openai-whisper
-

- -

The package work for me, but have not yet been tested on any other -computer than my own. With it, I have been able to (badly) transcribe -a 2 minute 40 second Norwegian audio clip to test using the small -model. This took 11 minutes and around 2.2 GiB of RAM. Transcribing -the same file with the medium model gave a accurate text in 77 minutes -using around 5.2 GiB of RAM. My test machine had too little memory to -test the large model, which I believe require 11 GiB of RAM. In -short, this now work for me using Debian packages, and I hope it will -for you and everyone else once the packages enter Debian.

- -

Now I can start on the audio recording part of this project.

- -

As usual, if you use Bitcoin and want to show your support of my -activities, please send Bitcoin donations to my address -15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b.

- - - Tags: debian, english, multimedia, video. - - -

@@ -722,7 +1052,7 @@ activities, please send Bitcoin donations to my address

October (1)

November (2)

November (3)

@@ -1138,7 +1468,7 @@ activities, please send Bitcoin donations to my address

@@ -1152,7 +1482,7 @@ activities, please send Bitcoin donations to my address

@@ -1226,7 +1556,7 @@ activities, please send Bitcoin donations to my address

@@ -1238,7 +1568,7 @@ activities, please send Bitcoin donations to my address

diff --git a/blog/index.rss b/blog/index.rss index 4f013d5afd..434ba4114e 100644 --- a/blog/index.rss +++ b/blog/index.rss @@ -6,6 +6,480 @@ https://people.skolelinux.org/pere/blog/ + + New and improved sqlcipher in Debian for accessing Signal database + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + Sun, 12 Nov 2023 12:00:00 +0100 + For a while now I wanted to have direct access to the +<a href="https://signal.org/">Signal</a> database of messages and +channels of my Desktop edition of Signal. I prefer the enforced end +to end encryption of Signal these days for my communication with +friends and family, to increase the level of safety and privacy as +well as raising the cost of the mass surveillance government and +non-government entities practice these days. In August I came across +a nice +<a href="https://www.yoranbrondsema.com/post/the-guide-to-extracting-statistics-from-your-signal-conversations/">recipe +on how to use sqlcipher to extract statistics from the Signal +database</a> explaining how to do this. Unfortunately this did not +work with the version of sqlcipher in Debian. The +<a href="http://tracker.debian.org/sqlcipher/">sqlcipher</a> +package is a "fork" of the sqlite package with added support for +encrypted databases. Sadly the current Debian maintainer +<a href="https://bugs.debian.org/961598">announced more than three +years ago that he did not have time to maintain sqlcipher</a>, so it +seemed unlikely to be upgraded by the maintainer. I was reluctant to +take on the job myself, as I have very limited experience maintaining +shared libraries in Debian. After waiting and hoping for a few +months, I gave up the last week, and set out to update the package. In +the process I orphaned it to make it more obvious for the next person +looking at it that the package need proper maintenance. + +The version in Debian was around five years old, and quite a lot of +changes had taken place upstream into the Debian maintenance git +repository. After spending a few days importing the new upstream +versions, realising that upstream did not care much for SONAME +versioning as I saw library symbols being both added and removed with +minor version number changes to the project, I concluded that I had to +do a SONAME bump of the library package to avoid surprising the +reverse dependencies. I even added a simple +autopkgtest script to ensure the package work as intended. Dug deep +into the hole of learning shared library maintenance, I set out a few +days ago to upload the new version to Debian experimental to see what +the quality assurance framework in Debian had to say about the result. +The feedback told me the pacakge was not too shabby, and yesterday I +uploaded the latest version to Debian unstable. It should enter +testing today or tomorrow, perhaps delayed by +<a href="https://bugs.debian.org/1055812">a small library +transition</a>. + +Armed with a new version of sqlcipher, I can now have a look at the +SQL database in ~/.config/Signal/sql/db.sqlite. First, one need to +fetch the encryption key from the Signal configuration using this +simple JSON extraction command: + +<pre>/usr/bin/jq -r '."key"' ~/.config/Signal/config.json</pre> + +Assuming the result from that command is 'secretkey', which is a +hexadecimal number representing the key used to encrypt the database. +Next, one can now connect to the database and inject the encryption +key for access via SQL to fetch information from the database. Here +is an example dumping the database structure: + +<pre> +% sqlcipher ~/.config/Signal/sql/db.sqlite +sqlite> PRAGMA key = "x'secretkey'"; +sqlite> .schema +CREATE TABLE sqlite_stat1(tbl,idx,stat); +CREATE TABLE conversations( + id STRING PRIMARY KEY ASC, + json TEXT, + + active_at INTEGER, + type STRING, + members TEXT, + name TEXT, + profileName TEXT + , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER); +CREATE TABLE identityKeys( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE items( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE sessions( + id TEXT PRIMARY KEY, + conversationId TEXT, + json TEXT + , ourServiceId STRING, serviceId STRING); +CREATE TABLE attachment_downloads( + id STRING primary key, + timestamp INTEGER, + pending INTEGER, + json TEXT + ); +CREATE TABLE sticker_packs( + id TEXT PRIMARY KEY, + key TEXT NOT NULL, + + author STRING, + coverStickerId INTEGER, + createdAt INTEGER, + downloadAttempts INTEGER, + installedAt INTEGER, + lastUsed INTEGER, + status STRING, + stickerCount INTEGER, + title STRING + , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync + INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE stickers( + id INTEGER NOT NULL, + packId TEXT NOT NULL, + + emoji STRING, + height INTEGER, + isCoverOnly INTEGER, + lastUsed INTEGER, + path STRING, + width INTEGER, + + PRIMARY KEY (id, packId), + CONSTRAINT stickers_fk + FOREIGN KEY (packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE sticker_references( + messageId STRING, + packId TEXT, + CONSTRAINT sticker_references_fk + FOREIGN KEY(packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE emojis( + shortName TEXT PRIMARY KEY, + lastUsage INTEGER + ); +CREATE TABLE messages( + rowid INTEGER PRIMARY KEY ASC, + id STRING UNIQUE, + json TEXT, + readStatus INTEGER, + expires_at INTEGER, + sent_at INTEGER, + schemaVersion INTEGER, + conversationId STRING, + received_at INTEGER, + source STRING, + hasAttachments INTEGER, + hasFileAttachments INTEGER, + hasVisualMediaAttachments INTEGER, + expireTimer INTEGER, + expirationStartTimestamp INTEGER, + type STRING, + body TEXT, + messageTimer INTEGER, + messageTimerStart INTEGER, + messageTimerExpiresAt INTEGER, + isErased INTEGER, + isViewOnce INTEGER, + sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER + GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER + GENERATED ALWAYS AS ( + json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1 + ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT + GENERATED ALWAYS + AS (ifnull( + expirationStartTimestamp + (expireTimer * 1000), + 9007199254740991 + )), shouldAffectActivity INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), shouldAffectPreview INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), isUserInitiatedMessage INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'group-v2-change', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER + GENERATED ALWAYS AS ( + type IS 'group-v2-change' AND + json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND + json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND + json_extract(json, '$.groupV2Change.from') IS NOT NULL AND + json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci') + ), isGroupLeaveEventFromOther INTEGER + GENERATED ALWAYS AS ( + isGroupLeaveEvent IS 1 + AND + isChangeCreatedByUs IS 0 + ), callId TEXT + GENERATED ALWAYS AS ( + json_extract(json, '$.callId') + )); +CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); +CREATE TABLE jobs( + id TEXT PRIMARY KEY, + queueType TEXT STRING NOT NULL, + timestamp INTEGER NOT NULL, + data STRING TEXT + ); +CREATE TABLE reactions( + conversationId STRING, + emoji STRING, + fromId STRING, + messageReceivedAt INTEGER, + targetAuthorAci STRING, + targetTimestamp INTEGER, + unread INTEGER + , messageId STRING); +CREATE TABLE senderKeys( + id TEXT PRIMARY KEY NOT NULL, + senderId TEXT NOT NULL, + distributionId TEXT NOT NULL, + data BLOB NOT NULL, + lastUpdatedDate NUMBER NOT NULL + ); +CREATE TABLE unprocessed( + id STRING PRIMARY KEY ASC, + timestamp INTEGER, + version INTEGER, + attempts INTEGER, + envelope TEXT, + decrypted TEXT, + source TEXT, + serverTimestamp INTEGER, + sourceServiceId STRING + , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER); +CREATE TABLE sendLogPayloads( + id INTEGER PRIMARY KEY ASC, + + timestamp INTEGER NOT NULL, + contentHint INTEGER NOT NULL, + proto BLOB NOT NULL + , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE sendLogRecipients( + payloadId INTEGER NOT NULL, + + recipientServiceId STRING NOT NULL, + deviceId INTEGER NOT NULL, + + PRIMARY KEY (payloadId, recipientServiceId, deviceId), + + CONSTRAINT sendLogRecipientsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE sendLogMessageIds( + payloadId INTEGER NOT NULL, + + messageId STRING NOT NULL, + + PRIMARY KEY (payloadId, messageId), + + CONSTRAINT sendLogMessageIdsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE preKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE signedPreKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE badges( + id TEXT PRIMARY KEY, + category TEXT NOT NULL, + name TEXT NOT NULL, + descriptionTemplate TEXT NOT NULL + ); +CREATE TABLE badgeImageFiles( + badgeId TEXT REFERENCES badges(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + 'order' INTEGER NOT NULL, + url TEXT NOT NULL, + localPath TEXT, + theme TEXT NOT NULL + ); +CREATE TABLE storyReads ( + authorId STRING NOT NULL, + conversationId STRING NOT NULL, + storyId STRING NOT NULL, + storyReadDate NUMBER NOT NULL, + + PRIMARY KEY (authorId, storyId) + ); +CREATE TABLE storyDistributions( + id STRING PRIMARY KEY NOT NULL, + name TEXT, + + senderKeyInfoJson STRING + , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER); +CREATE TABLE storyDistributionMembers( + listId STRING NOT NULL REFERENCES storyDistributions(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + serviceId STRING NOT NULL, + + PRIMARY KEY (listId, serviceId) + ); +CREATE TABLE uninstalled_sticker_packs ( + id STRING NOT NULL PRIMARY KEY, + uninstalledAt NUMBER NOT NULL, + storageID STRING, + storageVersion NUMBER, + storageUnknownFields BLOB, + storageNeedsSync INTEGER NOT NULL + ); +CREATE TABLE groupCallRingCancellations( + ringId INTEGER PRIMARY KEY, + createdAt INTEGER NOT NULL + ); +CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID; +CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0); +CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID; +CREATE TABLE edited_messages( + messageId STRING REFERENCES messages(id) + ON DELETE CASCADE, + sentAt INTEGER, + readStatus INTEGER + , conversationId STRING); +CREATE TABLE mentions ( + messageId REFERENCES messages(id) ON DELETE CASCADE, + mentionAci STRING, + start INTEGER, + length INTEGER + ); +CREATE TABLE kyberPreKeys( + id STRING PRIMARY KEY NOT NULL, + json TEXT NOT NULL, ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE callsHistory ( + callId TEXT PRIMARY KEY, + peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId + ringerId TEXT DEFAULT NULL, -- ringer uuid + mode TEXT NOT NULL, -- enum "Direct" | "Group" + type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group" + direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing + -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted" + -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted" + status TEXT NOT NULL, + timestamp INTEGER NOT NULL, + UNIQUE (callId, peerId) ON CONFLICT FAIL + ); +[ dropped all indexes to save space in this blog post ] +CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages + WHEN + new.body IS NOT NULL AND new.isViewOnce = 1 + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + END; +CREATE TRIGGER messages_on_insert AFTER INSERT ON messages + WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + DELETE FROM sendLogPayloads WHERE id IN ( + SELECT payloadId FROM sendLogMessageIds + WHERE messageId = old.id + ); + DELETE FROM reactions WHERE rowid IN ( + SELECT rowid FROM reactions + WHERE messageId = old.id + ); + DELETE FROM storyReads WHERE storyId = old.storyId; + END; +CREATE VIRTUAL TABLE messages_fts USING fts5( + body, + tokenize = 'signal_tokenizer' + ); +CREATE TRIGGER messages_on_update AFTER UPDATE ON messages + WHEN + (new.body IS NULL OR old.body IS NOT new.body) AND + new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages + BEGIN + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages + BEGIN + DELETE FROM mentions WHERE messageId = new.id; + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +sqlite> +</pre> + +Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format. + +As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +<a href="bitcoin:15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b">15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b</a>. + + + New chrpath release 0.17 https://people.skolelinux.org/pere/blog/New_chrpath_release_0_17.html @@ -472,150 +946,6 @@ Debian. Not sure how much work it would be to get it working, but suspect some kernel related packages need to be extended with more header files to get it working. -As usual, if you use Bitcoin and want to show your support of my -activities, please send Bitcoin donations to my address -<a href="bitcoin:15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b">15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b</a>. - - - - - Speech to text, she APTly whispered, how hard can it be? - https://people.skolelinux.org/pere/blog/Speech_to_text__she_APTly_whispered__how_hard_can_it_be_.html - https://people.skolelinux.org/pere/blog/Speech_to_text__she_APTly_whispered__how_hard_can_it_be_.html - Sun, 23 Apr 2023 09:40:00 +0200 - While visiting a convention during Easter, it occurred to me that -it would be great if I could have a digital Dictaphone with -transcribing capabilities, providing me with texts to cut-n-paste into -stuff I need to write. The background is that long drives often bring -up the urge to write on texts I am working on, which of course is out -of the question while driving. With the release of -<a href="https://github.com/openai/whisper/">OpenAI Whisper</a>, this -seem to be within reach with Free Software, so I decided to give it a -go. OpenAI Whisper is a Linux based neural network system to read in -audio files and provide text representation of the speech in that -audio recording. It handle multiple languages and according to its -creators even can translate into a different language than the spoken -one. I have not tested the latter feature. It can either use the CPU -or a GPU with CUDA support. As far as I can tell, CUDA in practice -limit that feature to NVidia graphics cards. I have few of those, as -they do not work great with free software drivers, and have not tested -the GPU option. While looking into the matter, I did discover some -work to provide CUDA support on non-NVidia GPUs, and some work with -the library used by Whisper to port it to other GPUs, but have not -spent much time looking into GPU support yet. I've so far used an old -X220 laptop as my test machine, and only transcribed using its -CPU. - -As it from a privacy standpoint is unthinkable to use computers -under control of someone else (aka a "cloud" service) to transcribe -ones thoughts and personal notes, I want to run the transcribing -system locally on my own computers. The only sensible approach to me -is to make the effort I put into this available for any Linux user and -to upload the needed packages into Debian. Looking at Debian Bookworm, I -discovered that only three packages were missing, -<a href="https://bugs.debian.org/1034307">tiktoken</a>, -<a href="https://bugs.debian.org/1034144">triton</a>, and -<a href="https://bugs.debian.org/1034091">openai-whisper</a>. For a while -I also believed -<a href="https://bugs.debian.org/1034286">ffmpeg-python</a> was -needed, but as its -<a href="https://github.com/kkroening/ffmpeg-python/issues/760">upstream -seem to have vanished</a> I found it safer -<a href="https://github.com/openai/whisper/pull/1242">to rewrite -whisper</a> to stop depending on in than to introduce ffmpeg-python -into Debian. I decided to place these packages under the umbrella of -<a href="https://salsa.debian.org/deeplearning-team">the Debian Deep -Learning Team</a>, which seem like the best team to look after such -packages. Discussing the topic within the group also made me aware -that the triton package was already a future dependency of newer -versions of the torch package being planned, and would be needed after -Bookworm is released. - -All required code packages have been now waiting in -<a href="https://ftp-master.debian.org/new.html">the Debian NEW -queue</a> since Wednesday, heading for Debian Experimental until -Bookworm is released. An unsolved issue is how to handle the neural -network models used by Whisper. The default behaviour of Whisper is -to require Internet connectivity and download the model requested to -<tt>~/.cache/whisper/</tt> on first invocation. This obviously would -fail <a href="https://people.debian.org/~bap/dfsg-faq.html">the -deserted island test of free software</a> as the Debian packages would -be unusable for someone stranded with only the Debian archive and solar -powered computer on a deserted island. - -Because of this, I would love to include the models in the Debian -mirror system. This is problematic, as the models are very large -files, which would put a heavy strain on the Debian mirror -infrastructure around the globe. The strain would be even higher if -the models change often, which luckily as far as I can tell they do -not. The small model, which according to its creator is most useful -for English and in my experience is not doing a great job there -either, is 462 MiB (deb is 414 MiB). The medium model, which to me -seem to handle English speech fairly well is 1.5 GiB (deb is 1.3 GiB) -and the large model is 2.9 GiB (deb is 2.6 GiB). I would assume -everyone with enough resources would prefer to use the large model for -highest quality. I believe the models themselves would have to go -into the non-free part of the Debian archive, as they are not really -including any useful source code for updating the models. The -"source", aka the model training set, according to the creators -consist of "680,000 hours of multilingual and multitask supervised -data collected from the web", which to me reads material with both -unknown copyright terms, unavailable to the general public. In other -words, the source is not available according to the Debian Free -Software Guidelines and the model should be considered non-free. - -I asked the Debian FTP masters for advice regarding uploading a -model package on their IRC channel, and based on the feedback there it -is still unclear to me if such package would be accepted into the -archive. In any case I wrote build rules for a -<a href="https://salsa.debian.org/deeplearning-team/openai-whisper-model">OpenAI -Whisper model package</a> and -<a href="https://github.com/openai/whisper/pull/1257">modified the -Whisper code base</a> to prefer shared files under <tt>/usr/</tt> and -<tt>/var/</tt> over user specific files in <tt>~/.cache/whisper/</tt> -to be able to use these model packages, to prepare for such -possibility. One solution might be to include only one of the models -(small or medium, I guess) in the Debian archive, and ask people to -download the others from the Internet. Not quite sure what to do -here, and advice is most welcome (use the debian-ai mailing list). - -To make it easier to test the new packages while I wait for them to -clear the NEW queue, I created an APT source targeting bookworm. I -selected Bookworm instead of Bullseye, even though I know the latter -would reach more users, is that some of the required dependencies are -missing from Bullseye and I during this phase of testing did not want -to backport a lot of packages just to get up and running. - -Here is a recipe to run as user root if you want to test OpenAI -Whisper using Debian packages on your Debian Bookworm installation, -first adding the APT repository GPG key to the list of trusted keys, -then setting up the APT repository and finally installing the packages -and one of the models: - -<pre> -curl https://geekbay.nuug.no/~pere/openai-whisper/D78F5C4796F353D211B119E28200D9B589641240.asc \ - -o /etc/apt/trusted.gpg.d/pere-whisper.asc -mkdir -p /etc/apt/sources.list.d -cat > /etc/apt/sources.list.d/pere-whisper.list <<EOF -deb https://geekbay.nuug.no/~pere/openai-whisper/ bookworm main -deb-src https://geekbay.nuug.no/~pere/openai-whisper/ bookworm main -EOF -apt update -apt install openai-whisper -</pre> - -The package work for me, but have not yet been tested on any other -computer than my own. With it, I have been able to (badly) transcribe -a 2 minute 40 second Norwegian audio clip to test using the small -model. This took 11 minutes and around 2.2 GiB of RAM. Transcribing -the same file with the medium model gave a accurate text in 77 minutes -using around 5.2 GiB of RAM. My test machine had too little memory to -test the large model, which I believe require 11 GiB of RAM. In -short, this now work for me using Debian packages, and I hope it will -for you and everyone else once the packages enter Debian. - -Now I can start on the audio recording part of this project. - As usual, if you use Bitcoin and want to show your support of my activities, please send Bitcoin donations to my address <a href="bitcoin:15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b">15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b</a>. diff --git a/blog/jXplorer__a_very_nice_LDAP_GUI.html b/blog/jXplorer__a_very_nice_LDAP_GUI.html index d58165459b..26cd3c87c8 100644 --- a/blog/jXplorer__a_very_nice_LDAP_GUI.html +++ b/blog/jXplorer__a_very_nice_LDAP_GUI.html @@ -72,7 +72,7 @@ and remove the failing query. Nothing big, but very annoying.

October (1)

November (2)

November (3)

@@ -488,7 +488,7 @@ and remove the failing query. Nothing big, but very annoying.

@@ -502,7 +502,7 @@ and remove the failing query. Nothing big, but very annoying.

@@ -576,7 +576,7 @@ and remove the failing query. Nothing big, but very annoying.

@@ -588,7 +588,7 @@ and remove the failing query. Nothing big, but very annoying.

diff --git a/blog/listadmin__the_quick_way_to_moderate_mailman_lists___nice_free_software.html b/blog/listadmin__the_quick_way_to_moderate_mailman_lists___nice_free_software.html index 6f52dc82bc..317fc6fd5f 100644 --- a/blog/listadmin__the_quick_way_to_moderate_mailman_lists___nice_free_software.html +++ b/blog/listadmin__the_quick_way_to_moderate_mailman_lists___nice_free_software.html @@ -141,7 +141,7 @@ sure why.

October (1)

November (2)

November (3)

@@ -557,7 +557,7 @@ sure why.

@@ -571,7 +571,7 @@ sure why.

@@ -645,7 +645,7 @@ sure why.

@@ -657,7 +657,7 @@ sure why.

diff --git a/blog/rtlsdr_scanner__software_defined_radio_frequency_scanner_for_Linux____nice_free_software.html b/blog/rtlsdr_scanner__software_defined_radio_frequency_scanner_for_Linux____nice_free_software.html index 5120f4e272..ab18636ca6 100644 --- a/blog/rtlsdr_scanner__software_defined_radio_frequency_scanner_for_Linux____nice_free_software.html +++ b/blog/rtlsdr_scanner__software_defined_radio_frequency_scanner_for_Linux____nice_free_software.html @@ -117,7 +117,7 @@ activities, please send Bitcoin donations to my address

October (1)

November (2)

November (3)

@@ -533,7 +533,7 @@ activities, please send Bitcoin donations to my address

@@ -547,7 +547,7 @@ activities, please send Bitcoin donations to my address

@@ -621,7 +621,7 @@ activities, please send Bitcoin donations to my address

@@ -633,7 +633,7 @@ activities, please send Bitcoin donations to my address

diff --git a/blog/sitemap.xml b/blog/sitemap.xml index 4680044230..13fbc5c626 100644 --- a/blog/sitemap.xml +++ b/blog/sitemap.xml @@ -2325,6 +2325,11 @@ 0.50 weekly + + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + 0.50 + weekly + https://people.skolelinux.org/pere/blog/New_book___Fri_kultur__by__lessig__a_Norwegian_Bokm_l_translation_of__Free_Culture__from_2004.html 0.50 diff --git a/blog/syslog_trusted_timestamp___chain_of_trusted_timestamps_for_your_syslog.html b/blog/syslog_trusted_timestamp___chain_of_trusted_timestamps_for_your_syslog.html index d7109a1e56..0806dd1ca1 100644 --- a/blog/syslog_trusted_timestamp___chain_of_trusted_timestamps_for_your_syslog.html +++ b/blog/syslog_trusted_timestamp___chain_of_trusted_timestamps_for_your_syslog.html @@ -165,7 +165,7 @@ activities, please send Bitcoin donations to my address

October (1)

November (2)

November (3)

@@ -581,7 +581,7 @@ activities, please send Bitcoin donations to my address

@@ -595,7 +595,7 @@ activities, please send Bitcoin donations to my address

@@ -669,7 +669,7 @@ activities, please send Bitcoin donations to my address

@@ -681,7 +681,7 @@ activities, please send Bitcoin donations to my address

diff --git a/blog/systemd__an_interesting_alternative_to_upstart.html b/blog/systemd__an_interesting_alternative_to_upstart.html index 5a867f3ebe..80954fc174 100644 --- a/blog/systemd__an_interesting_alternative_to_upstart.html +++ b/blog/systemd__an_interesting_alternative_to_upstart.html @@ -90,7 +90,7 @@ with parallel booting enabled by default.

October (1)

November (2)

November (3)

@@ -506,7 +506,7 @@ with parallel booting enabled by default.

@@ -520,7 +520,7 @@ with parallel booting enabled by default.

@@ -594,7 +594,7 @@ with parallel booting enabled by default.

@@ -606,7 +606,7 @@ with parallel booting enabled by default.

diff --git a/blog/tags/3d-printer/index.html b/blog/tags/3d-printer/index.html index ef1deae7e9..2c725b2ffe 100644 --- a/blog/tags/3d-printer/index.html +++ b/blog/tags/3d-printer/index.html @@ -1059,7 +1059,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

October (1)

November (2)

November (3)

@@ -1475,7 +1475,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

@@ -1489,7 +1489,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

@@ -1563,7 +1563,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

@@ -1575,7 +1575,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

diff --git a/blog/tags/amiga/index.html b/blog/tags/amiga/index.html index 0fb48d0a69..9b7172f33f 100644 --- a/blog/tags/amiga/index.html +++ b/blog/tags/amiga/index.html @@ -76,7 +76,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

October (1)

November (2)

November (3)

@@ -492,7 +492,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

@@ -506,7 +506,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

@@ -580,7 +580,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

@@ -592,7 +592,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

diff --git a/blog/tags/aros/index.html b/blog/tags/aros/index.html index c9d32a70a4..213a3386f2 100644 --- a/blog/tags/aros/index.html +++ b/blog/tags/aros/index.html @@ -76,7 +76,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

October (1)

November (2)

November (3)

@@ -492,7 +492,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

@@ -506,7 +506,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

@@ -580,7 +580,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

@@ -592,7 +592,7 @@ pakke. Kanskje Aros kunne vÃ¦rt interessant for et NUUG-foredrag?

diff --git a/blog/tags/bankid/index.html b/blog/tags/bankid/index.html index 80a0024ad4..075aa3f693 100644 --- a/blog/tags/bankid/index.html +++ b/blog/tags/bankid/index.html @@ -488,7 +488,7 @@ omtalt over. Anbefalt lesning.

October (1)

November (2)

November (3)

@@ -904,7 +904,7 @@ omtalt over. Anbefalt lesning.

@@ -918,7 +918,7 @@ omtalt over. Anbefalt lesning.

@@ -992,7 +992,7 @@ omtalt over. Anbefalt lesning.

@@ -1004,7 +1004,7 @@ omtalt over. Anbefalt lesning.

diff --git a/blog/tags/betalkontant/index.html b/blog/tags/betalkontant/index.html index 5598ae0190..b7ac02d8c1 100644 --- a/blog/tags/betalkontant/index.html +++ b/blog/tags/betalkontant/index.html @@ -1708,7 +1708,7 @@ kontanter for noen dager siden.

October (1)

November (2)

November (3)

@@ -2124,7 +2124,7 @@ kontanter for noen dager siden.

@@ -2138,7 +2138,7 @@ kontanter for noen dager siden.

@@ -2212,7 +2212,7 @@ kontanter for noen dager siden.

@@ -2224,7 +2224,7 @@ kontanter for noen dager siden.

diff --git a/blog/tags/bitcoin/index.html b/blog/tags/bitcoin/index.html index 99af113137..b710e6042b 100644 --- a/blog/tags/bitcoin/index.html +++ b/blog/tags/bitcoin/index.html @@ -962,7 +962,7 @@ donations to the address

October (1)

November (2)

November (3)

@@ -1378,7 +1378,7 @@ donations to the address

@@ -1392,7 +1392,7 @@ donations to the address

@@ -1466,7 +1466,7 @@ donations to the address

@@ -1478,7 +1478,7 @@ donations to the address

diff --git a/blog/tags/bootsystem/index.html b/blog/tags/bootsystem/index.html index 48650d076a..41aeb3172c 100644 --- a/blog/tags/bootsystem/index.html +++ b/blog/tags/bootsystem/index.html @@ -1334,7 +1334,7 @@ insserv'. Will need to test if that work. :)

October (1)

November (2)

November (3)

@@ -1750,7 +1750,7 @@ insserv'. Will need to test if that work. :)

@@ -1764,7 +1764,7 @@ insserv'. Will need to test if that work. :)

@@ -1838,7 +1838,7 @@ insserv'. Will need to test if that work. :)

@@ -1850,7 +1850,7 @@ insserv'. Will need to test if that work. :)

diff --git a/blog/tags/bsa/index.html b/blog/tags/bsa/index.html index 3db26c6a53..7345d496e9 100644 --- a/blog/tags/bsa/index.html +++ b/blog/tags/bsa/index.html @@ -164,7 +164,7 @@ pÃ¥ Slashdot.

October (1)

November (2)

November (3)

@@ -580,7 +580,7 @@ pÃ¥ Slashdot.

@@ -594,7 +594,7 @@ pÃ¥ Slashdot.

@@ -668,7 +668,7 @@ pÃ¥ Slashdot.

@@ -680,7 +680,7 @@ pÃ¥ Slashdot.

diff --git a/blog/tags/chrpath/index.html b/blog/tags/chrpath/index.html index d3a82dca95..06e431cdd7 100644 --- a/blog/tags/chrpath/index.html +++ b/blog/tags/chrpath/index.html @@ -219,7 +219,7 @@ include a testsuite check.

October (1)

November (2)

November (3)

@@ -635,7 +635,7 @@ include a testsuite check.

@@ -649,7 +649,7 @@ include a testsuite check.

@@ -723,7 +723,7 @@ include a testsuite check.

@@ -735,7 +735,7 @@ include a testsuite check.

diff --git a/blog/tags/debian edu/index.html b/blog/tags/debian edu/index.html index 4c91c196aa..7a5e1fbcd3 100644 --- a/blog/tags/debian edu/index.html +++ b/blog/tags/debian edu/index.html @@ -15090,7 +15090,7 @@ be the only one fitting our needs. :/

October (1)

November (2)

November (3)

@@ -15506,7 +15506,7 @@ be the only one fitting our needs. :/

@@ -15520,7 +15520,7 @@ be the only one fitting our needs. :/

@@ -15594,7 +15594,7 @@ be the only one fitting our needs. :/

@@ -15606,7 +15606,7 @@ be the only one fitting our needs. :/

diff --git a/blog/tags/debian-handbook/index.html b/blog/tags/debian-handbook/index.html index 89e11f26af..66a4dce304 100644 --- a/blog/tags/debian-handbook/index.html +++ b/blog/tags/debian-handbook/index.html @@ -488,7 +488,7 @@ available for many more languages.

October (1)

November (2)

November (3)

@@ -904,7 +904,7 @@ available for many more languages.

@@ -918,7 +918,7 @@ available for many more languages.

@@ -992,7 +992,7 @@ available for many more languages.

@@ -1004,7 +1004,7 @@ available for many more languages.

diff --git a/blog/tags/debian/debian.rss b/blog/tags/debian/debian.rss index ac9012bcfd..f930ec1872 100644 --- a/blog/tags/debian/debian.rss +++ b/blog/tags/debian/debian.rss @@ -6,6 +6,480 @@ https://people.skolelinux.org/pere/blog/ + + New and improved sqlcipher in Debian for accessing Signal database + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + Sun, 12 Nov 2023 12:00:00 +0100 + For a while now I wanted to have direct access to the +<a href="https://signal.org/">Signal</a> database of messages and +channels of my Desktop edition of Signal. I prefer the enforced end +to end encryption of Signal these days for my communication with +friends and family, to increase the level of safety and privacy as +well as raising the cost of the mass surveillance government and +non-government entities practice these days. In August I came across +a nice +<a href="https://www.yoranbrondsema.com/post/the-guide-to-extracting-statistics-from-your-signal-conversations/">recipe +on how to use sqlcipher to extract statistics from the Signal +database</a> explaining how to do this. Unfortunately this did not +work with the version of sqlcipher in Debian. The +<a href="http://tracker.debian.org/sqlcipher/">sqlcipher</a> +package is a "fork" of the sqlite package with added support for +encrypted databases. Sadly the current Debian maintainer +<a href="https://bugs.debian.org/961598">announced more than three +years ago that he did not have time to maintain sqlcipher</a>, so it +seemed unlikely to be upgraded by the maintainer. I was reluctant to +take on the job myself, as I have very limited experience maintaining +shared libraries in Debian. After waiting and hoping for a few +months, I gave up the last week, and set out to update the package. In +the process I orphaned it to make it more obvious for the next person +looking at it that the package need proper maintenance. + +The version in Debian was around five years old, and quite a lot of +changes had taken place upstream into the Debian maintenance git +repository. After spending a few days importing the new upstream +versions, realising that upstream did not care much for SONAME +versioning as I saw library symbols being both added and removed with +minor version number changes to the project, I concluded that I had to +do a SONAME bump of the library package to avoid surprising the +reverse dependencies. I even added a simple +autopkgtest script to ensure the package work as intended. Dug deep +into the hole of learning shared library maintenance, I set out a few +days ago to upload the new version to Debian experimental to see what +the quality assurance framework in Debian had to say about the result. +The feedback told me the pacakge was not too shabby, and yesterday I +uploaded the latest version to Debian unstable. It should enter +testing today or tomorrow, perhaps delayed by +<a href="https://bugs.debian.org/1055812">a small library +transition</a>. + +Armed with a new version of sqlcipher, I can now have a look at the +SQL database in ~/.config/Signal/sql/db.sqlite. First, one need to +fetch the encryption key from the Signal configuration using this +simple JSON extraction command: + +<pre>/usr/bin/jq -r '."key"' ~/.config/Signal/config.json</pre> + +Assuming the result from that command is 'secretkey', which is a +hexadecimal number representing the key used to encrypt the database. +Next, one can now connect to the database and inject the encryption +key for access via SQL to fetch information from the database. Here +is an example dumping the database structure: + +<pre> +% sqlcipher ~/.config/Signal/sql/db.sqlite +sqlite> PRAGMA key = "x'secretkey'"; +sqlite> .schema +CREATE TABLE sqlite_stat1(tbl,idx,stat); +CREATE TABLE conversations( + id STRING PRIMARY KEY ASC, + json TEXT, + + active_at INTEGER, + type STRING, + members TEXT, + name TEXT, + profileName TEXT + , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER); +CREATE TABLE identityKeys( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE items( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE sessions( + id TEXT PRIMARY KEY, + conversationId TEXT, + json TEXT + , ourServiceId STRING, serviceId STRING); +CREATE TABLE attachment_downloads( + id STRING primary key, + timestamp INTEGER, + pending INTEGER, + json TEXT + ); +CREATE TABLE sticker_packs( + id TEXT PRIMARY KEY, + key TEXT NOT NULL, + + author STRING, + coverStickerId INTEGER, + createdAt INTEGER, + downloadAttempts INTEGER, + installedAt INTEGER, + lastUsed INTEGER, + status STRING, + stickerCount INTEGER, + title STRING + , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync + INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE stickers( + id INTEGER NOT NULL, + packId TEXT NOT NULL, + + emoji STRING, + height INTEGER, + isCoverOnly INTEGER, + lastUsed INTEGER, + path STRING, + width INTEGER, + + PRIMARY KEY (id, packId), + CONSTRAINT stickers_fk + FOREIGN KEY (packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE sticker_references( + messageId STRING, + packId TEXT, + CONSTRAINT sticker_references_fk + FOREIGN KEY(packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE emojis( + shortName TEXT PRIMARY KEY, + lastUsage INTEGER + ); +CREATE TABLE messages( + rowid INTEGER PRIMARY KEY ASC, + id STRING UNIQUE, + json TEXT, + readStatus INTEGER, + expires_at INTEGER, + sent_at INTEGER, + schemaVersion INTEGER, + conversationId STRING, + received_at INTEGER, + source STRING, + hasAttachments INTEGER, + hasFileAttachments INTEGER, + hasVisualMediaAttachments INTEGER, + expireTimer INTEGER, + expirationStartTimestamp INTEGER, + type STRING, + body TEXT, + messageTimer INTEGER, + messageTimerStart INTEGER, + messageTimerExpiresAt INTEGER, + isErased INTEGER, + isViewOnce INTEGER, + sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER + GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER + GENERATED ALWAYS AS ( + json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1 + ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT + GENERATED ALWAYS + AS (ifnull( + expirationStartTimestamp + (expireTimer * 1000), + 9007199254740991 + )), shouldAffectActivity INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), shouldAffectPreview INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), isUserInitiatedMessage INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'group-v2-change', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER + GENERATED ALWAYS AS ( + type IS 'group-v2-change' AND + json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND + json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND + json_extract(json, '$.groupV2Change.from') IS NOT NULL AND + json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci') + ), isGroupLeaveEventFromOther INTEGER + GENERATED ALWAYS AS ( + isGroupLeaveEvent IS 1 + AND + isChangeCreatedByUs IS 0 + ), callId TEXT + GENERATED ALWAYS AS ( + json_extract(json, '$.callId') + )); +CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); +CREATE TABLE jobs( + id TEXT PRIMARY KEY, + queueType TEXT STRING NOT NULL, + timestamp INTEGER NOT NULL, + data STRING TEXT + ); +CREATE TABLE reactions( + conversationId STRING, + emoji STRING, + fromId STRING, + messageReceivedAt INTEGER, + targetAuthorAci STRING, + targetTimestamp INTEGER, + unread INTEGER + , messageId STRING); +CREATE TABLE senderKeys( + id TEXT PRIMARY KEY NOT NULL, + senderId TEXT NOT NULL, + distributionId TEXT NOT NULL, + data BLOB NOT NULL, + lastUpdatedDate NUMBER NOT NULL + ); +CREATE TABLE unprocessed( + id STRING PRIMARY KEY ASC, + timestamp INTEGER, + version INTEGER, + attempts INTEGER, + envelope TEXT, + decrypted TEXT, + source TEXT, + serverTimestamp INTEGER, + sourceServiceId STRING + , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER); +CREATE TABLE sendLogPayloads( + id INTEGER PRIMARY KEY ASC, + + timestamp INTEGER NOT NULL, + contentHint INTEGER NOT NULL, + proto BLOB NOT NULL + , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE sendLogRecipients( + payloadId INTEGER NOT NULL, + + recipientServiceId STRING NOT NULL, + deviceId INTEGER NOT NULL, + + PRIMARY KEY (payloadId, recipientServiceId, deviceId), + + CONSTRAINT sendLogRecipientsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE sendLogMessageIds( + payloadId INTEGER NOT NULL, + + messageId STRING NOT NULL, + + PRIMARY KEY (payloadId, messageId), + + CONSTRAINT sendLogMessageIdsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE preKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE signedPreKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE badges( + id TEXT PRIMARY KEY, + category TEXT NOT NULL, + name TEXT NOT NULL, + descriptionTemplate TEXT NOT NULL + ); +CREATE TABLE badgeImageFiles( + badgeId TEXT REFERENCES badges(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + 'order' INTEGER NOT NULL, + url TEXT NOT NULL, + localPath TEXT, + theme TEXT NOT NULL + ); +CREATE TABLE storyReads ( + authorId STRING NOT NULL, + conversationId STRING NOT NULL, + storyId STRING NOT NULL, + storyReadDate NUMBER NOT NULL, + + PRIMARY KEY (authorId, storyId) + ); +CREATE TABLE storyDistributions( + id STRING PRIMARY KEY NOT NULL, + name TEXT, + + senderKeyInfoJson STRING + , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER); +CREATE TABLE storyDistributionMembers( + listId STRING NOT NULL REFERENCES storyDistributions(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + serviceId STRING NOT NULL, + + PRIMARY KEY (listId, serviceId) + ); +CREATE TABLE uninstalled_sticker_packs ( + id STRING NOT NULL PRIMARY KEY, + uninstalledAt NUMBER NOT NULL, + storageID STRING, + storageVersion NUMBER, + storageUnknownFields BLOB, + storageNeedsSync INTEGER NOT NULL + ); +CREATE TABLE groupCallRingCancellations( + ringId INTEGER PRIMARY KEY, + createdAt INTEGER NOT NULL + ); +CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID; +CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0); +CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID; +CREATE TABLE edited_messages( + messageId STRING REFERENCES messages(id) + ON DELETE CASCADE, + sentAt INTEGER, + readStatus INTEGER + , conversationId STRING); +CREATE TABLE mentions ( + messageId REFERENCES messages(id) ON DELETE CASCADE, + mentionAci STRING, + start INTEGER, + length INTEGER + ); +CREATE TABLE kyberPreKeys( + id STRING PRIMARY KEY NOT NULL, + json TEXT NOT NULL, ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE callsHistory ( + callId TEXT PRIMARY KEY, + peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId + ringerId TEXT DEFAULT NULL, -- ringer uuid + mode TEXT NOT NULL, -- enum "Direct" | "Group" + type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group" + direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing + -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted" + -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted" + status TEXT NOT NULL, + timestamp INTEGER NOT NULL, + UNIQUE (callId, peerId) ON CONFLICT FAIL + ); +[ dropped all indexes to save space in this blog post ] +CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages + WHEN + new.body IS NOT NULL AND new.isViewOnce = 1 + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + END; +CREATE TRIGGER messages_on_insert AFTER INSERT ON messages + WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + DELETE FROM sendLogPayloads WHERE id IN ( + SELECT payloadId FROM sendLogMessageIds + WHERE messageId = old.id + ); + DELETE FROM reactions WHERE rowid IN ( + SELECT rowid FROM reactions + WHERE messageId = old.id + ); + DELETE FROM storyReads WHERE storyId = old.storyId; + END; +CREATE VIRTUAL TABLE messages_fts USING fts5( + body, + tokenize = 'signal_tokenizer' + ); +CREATE TRIGGER messages_on_update AFTER UPDATE ON messages + WHEN + (new.body IS NULL OR old.body IS NOT new.body) AND + new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages + BEGIN + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages + BEGIN + DELETE FROM mentions WHERE messageId = new.id; + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +sqlite> +</pre> + +Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format. + +As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +<a href="bitcoin:15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b">15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b</a>. + + + New chrpath release 0.17 https://people.skolelinux.org/pere/blog/New_chrpath_release_0_17.html diff --git a/blog/tags/debian/index.html b/blog/tags/debian/index.html index b486a9c434..313ca91f30 100644 --- a/blog/tags/debian/index.html +++ b/blog/tags/debian/index.html @@ -20,6 +20,492 @@

Entries tagged "debian".

+ New and improved sqlcipher in Debian for accessing Signal database +

+ 12th November 2023 +

+ +

/usr/bin/jq -r '."key"' ~/.config/Signal/config.json

+ +

+% sqlcipher ~/.config/Signal/sql/db.sqlite
+sqlite> PRAGMA key = "x'secretkey'";
+sqlite> .schema
+CREATE TABLE sqlite_stat1(tbl,idx,stat);
+CREATE TABLE conversations(
+      id STRING PRIMARY KEY ASC,
+      json TEXT,
+
+      active_at INTEGER,
+      type STRING,
+      members TEXT,
+      name TEXT,
+      profileName TEXT
+    , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER);
+CREATE TABLE identityKeys(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE items(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE sessions(
+      id TEXT PRIMARY KEY,
+      conversationId TEXT,
+      json TEXT
+    , ourServiceId STRING, serviceId STRING);
+CREATE TABLE attachment_downloads(
+    id STRING primary key,
+    timestamp INTEGER,
+    pending INTEGER,
+    json TEXT
+  );
+CREATE TABLE sticker_packs(
+    id TEXT PRIMARY KEY,
+    key TEXT NOT NULL,
+
+    author STRING,
+    coverStickerId INTEGER,
+    createdAt INTEGER,
+    downloadAttempts INTEGER,
+    installedAt INTEGER,
+    lastUsed INTEGER,
+    status STRING,
+    stickerCount INTEGER,
+    title STRING
+  , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync
+      INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE stickers(
+    id INTEGER NOT NULL,
+    packId TEXT NOT NULL,
+
+    emoji STRING,
+    height INTEGER,
+    isCoverOnly INTEGER,
+    lastUsed INTEGER,
+    path STRING,
+    width INTEGER,
+
+    PRIMARY KEY (id, packId),
+    CONSTRAINT stickers_fk
+      FOREIGN KEY (packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE sticker_references(
+    messageId STRING,
+    packId TEXT,
+    CONSTRAINT sticker_references_fk
+      FOREIGN KEY(packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE emojis(
+    shortName TEXT PRIMARY KEY,
+    lastUsage INTEGER
+  );
+CREATE TABLE messages(
+        rowid INTEGER PRIMARY KEY ASC,
+        id STRING UNIQUE,
+        json TEXT,
+        readStatus INTEGER,
+        expires_at INTEGER,
+        sent_at INTEGER,
+        schemaVersion INTEGER,
+        conversationId STRING,
+        received_at INTEGER,
+        source STRING,
+        hasAttachments INTEGER,
+        hasFileAttachments INTEGER,
+        hasVisualMediaAttachments INTEGER,
+        expireTimer INTEGER,
+        expirationStartTimestamp INTEGER,
+        type STRING,
+        body TEXT,
+        messageTimer INTEGER,
+        messageTimerStart INTEGER,
+        messageTimerExpiresAt INTEGER,
+        isErased INTEGER,
+        isViewOnce INTEGER,
+        sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER
+        GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1
+        ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT
+        GENERATED ALWAYS
+        AS (ifnull(
+          expirationStartTimestamp + (expireTimer * 1000),
+          9007199254740991
+        )), shouldAffectActivity INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), shouldAffectPreview INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), isUserInitiatedMessage INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'group-v2-change',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER
+        GENERATED ALWAYS AS (
+          type IS 'group-v2-change' AND
+          json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND
+          json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND
+          json_extract(json, '$.groupV2Change.from') IS NOT NULL AND
+          json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci')
+        ), isGroupLeaveEventFromOther INTEGER
+        GENERATED ALWAYS AS (
+          isGroupLeaveEvent IS 1
+          AND
+          isChangeCreatedByUs IS 0
+        ), callId TEXT
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.callId')
+        ));
+CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
+CREATE TABLE jobs(
+        id TEXT PRIMARY KEY,
+        queueType TEXT STRING NOT NULL,
+        timestamp INTEGER NOT NULL,
+        data STRING TEXT
+      );
+CREATE TABLE reactions(
+        conversationId STRING,
+        emoji STRING,
+        fromId STRING,
+        messageReceivedAt INTEGER,
+        targetAuthorAci STRING,
+        targetTimestamp INTEGER,
+        unread INTEGER
+      , messageId STRING);
+CREATE TABLE senderKeys(
+        id TEXT PRIMARY KEY NOT NULL,
+        senderId TEXT NOT NULL,
+        distributionId TEXT NOT NULL,
+        data BLOB NOT NULL,
+        lastUpdatedDate NUMBER NOT NULL
+      );
+CREATE TABLE unprocessed(
+        id STRING PRIMARY KEY ASC,
+        timestamp INTEGER,
+        version INTEGER,
+        attempts INTEGER,
+        envelope TEXT,
+        decrypted TEXT,
+        source TEXT,
+        serverTimestamp INTEGER,
+        sourceServiceId STRING
+      , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER);
+CREATE TABLE sendLogPayloads(
+        id INTEGER PRIMARY KEY ASC,
+
+        timestamp INTEGER NOT NULL,
+        contentHint INTEGER NOT NULL,
+        proto BLOB NOT NULL
+      , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE sendLogRecipients(
+        payloadId INTEGER NOT NULL,
+
+        recipientServiceId STRING NOT NULL,
+        deviceId INTEGER NOT NULL,
+
+        PRIMARY KEY (payloadId, recipientServiceId, deviceId),
+
+        CONSTRAINT sendLogRecipientsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE sendLogMessageIds(
+        payloadId INTEGER NOT NULL,
+
+        messageId STRING NOT NULL,
+
+        PRIMARY KEY (payloadId, messageId),
+
+        CONSTRAINT sendLogMessageIdsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE preKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE signedPreKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE badges(
+        id TEXT PRIMARY KEY,
+        category TEXT NOT NULL,
+        name TEXT NOT NULL,
+        descriptionTemplate TEXT NOT NULL
+      );
+CREATE TABLE badgeImageFiles(
+        badgeId TEXT REFERENCES badges(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        'order' INTEGER NOT NULL,
+        url TEXT NOT NULL,
+        localPath TEXT,
+        theme TEXT NOT NULL
+      );
+CREATE TABLE storyReads (
+        authorId STRING NOT NULL,
+        conversationId STRING NOT NULL,
+        storyId STRING NOT NULL,
+        storyReadDate NUMBER NOT NULL,
+
+        PRIMARY KEY (authorId, storyId)
+      );
+CREATE TABLE storyDistributions(
+        id STRING PRIMARY KEY NOT NULL,
+        name TEXT,
+
+        senderKeyInfoJson STRING
+      , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER);
+CREATE TABLE storyDistributionMembers(
+        listId STRING NOT NULL REFERENCES storyDistributions(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        serviceId STRING NOT NULL,
+
+        PRIMARY KEY (listId, serviceId)
+      );
+CREATE TABLE uninstalled_sticker_packs (
+        id STRING NOT NULL PRIMARY KEY,
+        uninstalledAt NUMBER NOT NULL,
+        storageID STRING,
+        storageVersion NUMBER,
+        storageUnknownFields BLOB,
+        storageNeedsSync INTEGER NOT NULL
+      );
+CREATE TABLE groupCallRingCancellations(
+        ringId INTEGER PRIMARY KEY,
+        createdAt INTEGER NOT NULL
+      );
+CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
+CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0);
+CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
+CREATE TABLE edited_messages(
+        messageId STRING REFERENCES messages(id)
+          ON DELETE CASCADE,
+        sentAt INTEGER,
+        readStatus INTEGER
+      , conversationId STRING);
+CREATE TABLE mentions (
+        messageId REFERENCES messages(id) ON DELETE CASCADE,
+        mentionAci STRING,
+        start INTEGER,
+        length INTEGER
+      );
+CREATE TABLE kyberPreKeys(
+        id STRING PRIMARY KEY NOT NULL,
+        json TEXT NOT NULL, ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE callsHistory (
+        callId TEXT PRIMARY KEY,
+        peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId
+        ringerId TEXT DEFAULT NULL, -- ringer uuid
+        mode TEXT NOT NULL, -- enum "Direct" | "Group"
+        type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group"
+        direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing
+        -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted"
+        -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted"
+        status TEXT NOT NULL,
+        timestamp INTEGER NOT NULL,
+        UNIQUE (callId, peerId) ON CONFLICT FAIL
+      );
+[ dropped all indexes to save space in this blog post ]
+CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages
+      WHEN
+        new.body IS NOT NULL AND new.isViewOnce = 1
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+      END;
+CREATE TRIGGER messages_on_insert AFTER INSERT ON messages
+      WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        DELETE FROM sendLogPayloads WHERE id IN (
+          SELECT payloadId FROM sendLogMessageIds
+          WHERE messageId = old.id
+        );
+        DELETE FROM reactions WHERE rowid IN (
+          SELECT rowid FROM reactions
+          WHERE messageId = old.id
+        );
+        DELETE FROM storyReads WHERE storyId = old.storyId;
+      END;
+CREATE VIRTUAL TABLE messages_fts USING fts5(
+        body,
+        tokenize = 'signal_tokenizer'
+      );
+CREATE TRIGGER messages_on_update AFTER UPDATE ON messages
+      WHEN
+        (new.body IS NULL OR old.body IS NOT new.body) AND
+         new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages
+      BEGIN
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages
+      BEGIN
+        DELETE FROM mentions WHERE messageId = new.id;
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+sqlite>
+

+ +

Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format.

+ +

As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b.

+ +

+ + + Tags: debian, english, sikkerhet, surveillance. + + +

New chrpath release 0.17 @@ -15886,7 +16372,7 @@ be the only one fitting our needs. :/

October (1)

November (2)

November (3)

@@ -16302,7 +16788,7 @@ be the only one fitting our needs. :/

@@ -16316,7 +16802,7 @@ be the only one fitting our needs. :/

@@ -16390,7 +16876,7 @@ be the only one fitting our needs. :/

@@ -16402,7 +16888,7 @@ be the only one fitting our needs. :/

diff --git a/blog/tags/digistan/index.html b/blog/tags/digistan/index.html index f4e670d41a..77adc19311 100644 --- a/blog/tags/digistan/index.html +++ b/blog/tags/digistan/index.html @@ -1453,7 +1453,7 @@ produkter basert pÃ¥ standarden.

October (1)

November (2)

November (3)

@@ -1869,7 +1869,7 @@ produkter basert pÃ¥ standarden.

@@ -1883,7 +1883,7 @@ produkter basert pÃ¥ standarden.

@@ -1957,7 +1957,7 @@ produkter basert pÃ¥ standarden.

@@ -1969,7 +1969,7 @@ produkter basert pÃ¥ standarden.

diff --git a/blog/tags/dld/index.html b/blog/tags/dld/index.html index 384f47150d..b821d97ed1 100644 --- a/blog/tags/dld/index.html +++ b/blog/tags/dld/index.html @@ -1667,7 +1667,7 @@ fÃ¥r snart se om det stemmer.

October (1)

November (2)

November (3)

@@ -2083,7 +2083,7 @@ fÃ¥r snart se om det stemmer.

@@ -2097,7 +2097,7 @@ fÃ¥r snart se om det stemmer.

@@ -2171,7 +2171,7 @@ fÃ¥r snart se om det stemmer.

@@ -2183,7 +2183,7 @@ fÃ¥r snart se om det stemmer.

diff --git a/blog/tags/docbook/index.html b/blog/tags/docbook/index.html index e9544a0088..6db3463d43 100644 --- a/blog/tags/docbook/index.html +++ b/blog/tags/docbook/index.html @@ -2422,7 +2422,7 @@ slik at du kan oppdatere direkte.

October (1)

November (2)

November (3)

@@ -2838,7 +2838,7 @@ slik at du kan oppdatere direkte.

@@ -2852,7 +2852,7 @@ slik at du kan oppdatere direkte.

@@ -2926,7 +2926,7 @@ slik at du kan oppdatere direkte.

@@ -2938,7 +2938,7 @@ slik at du kan oppdatere direkte.

diff --git a/blog/tags/drivstoffpriser/index.html b/blog/tags/drivstoffpriser/index.html index 19905f59cf..d1a5f6ac2b 100644 --- a/blog/tags/drivstoffpriser/index.html +++ b/blog/tags/drivstoffpriser/index.html @@ -505,7 +505,7 @@ en liste med stasjoner pÃ¥ samme format som PriserVedStasjoner.

October (1)

November (2)

November (3)

@@ -921,7 +921,7 @@ en liste med stasjoner pÃ¥ samme format som PriserVedStasjoner.

@@ -935,7 +935,7 @@ en liste med stasjoner pÃ¥ samme format som PriserVedStasjoner.

@@ -1009,7 +1009,7 @@ en liste med stasjoner pÃ¥ samme format som PriserVedStasjoner.

@@ -1021,7 +1021,7 @@ en liste med stasjoner pÃ¥ samme format som PriserVedStasjoner.

diff --git a/blog/tags/english/english.rss b/blog/tags/english/english.rss index 46f88b7095..581ac825c0 100644 --- a/blog/tags/english/english.rss +++ b/blog/tags/english/english.rss @@ -6,6 +6,480 @@ https://people.skolelinux.org/pere/blog/ + + New and improved sqlcipher in Debian for accessing Signal database + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + Sun, 12 Nov 2023 12:00:00 +0100 + For a while now I wanted to have direct access to the +<a href="https://signal.org/">Signal</a> database of messages and +channels of my Desktop edition of Signal. I prefer the enforced end +to end encryption of Signal these days for my communication with +friends and family, to increase the level of safety and privacy as +well as raising the cost of the mass surveillance government and +non-government entities practice these days. In August I came across +a nice +<a href="https://www.yoranbrondsema.com/post/the-guide-to-extracting-statistics-from-your-signal-conversations/">recipe +on how to use sqlcipher to extract statistics from the Signal +database</a> explaining how to do this. Unfortunately this did not +work with the version of sqlcipher in Debian. The +<a href="http://tracker.debian.org/sqlcipher/">sqlcipher</a> +package is a "fork" of the sqlite package with added support for +encrypted databases. Sadly the current Debian maintainer +<a href="https://bugs.debian.org/961598">announced more than three +years ago that he did not have time to maintain sqlcipher</a>, so it +seemed unlikely to be upgraded by the maintainer. I was reluctant to +take on the job myself, as I have very limited experience maintaining +shared libraries in Debian. After waiting and hoping for a few +months, I gave up the last week, and set out to update the package. In +the process I orphaned it to make it more obvious for the next person +looking at it that the package need proper maintenance. + +The version in Debian was around five years old, and quite a lot of +changes had taken place upstream into the Debian maintenance git +repository. After spending a few days importing the new upstream +versions, realising that upstream did not care much for SONAME +versioning as I saw library symbols being both added and removed with +minor version number changes to the project, I concluded that I had to +do a SONAME bump of the library package to avoid surprising the +reverse dependencies. I even added a simple +autopkgtest script to ensure the package work as intended. Dug deep +into the hole of learning shared library maintenance, I set out a few +days ago to upload the new version to Debian experimental to see what +the quality assurance framework in Debian had to say about the result. +The feedback told me the pacakge was not too shabby, and yesterday I +uploaded the latest version to Debian unstable. It should enter +testing today or tomorrow, perhaps delayed by +<a href="https://bugs.debian.org/1055812">a small library +transition</a>. + +Armed with a new version of sqlcipher, I can now have a look at the +SQL database in ~/.config/Signal/sql/db.sqlite. First, one need to +fetch the encryption key from the Signal configuration using this +simple JSON extraction command: + +<pre>/usr/bin/jq -r '."key"' ~/.config/Signal/config.json</pre> + +Assuming the result from that command is 'secretkey', which is a +hexadecimal number representing the key used to encrypt the database. +Next, one can now connect to the database and inject the encryption +key for access via SQL to fetch information from the database. Here +is an example dumping the database structure: + +<pre> +% sqlcipher ~/.config/Signal/sql/db.sqlite +sqlite> PRAGMA key = "x'secretkey'"; +sqlite> .schema +CREATE TABLE sqlite_stat1(tbl,idx,stat); +CREATE TABLE conversations( + id STRING PRIMARY KEY ASC, + json TEXT, + + active_at INTEGER, + type STRING, + members TEXT, + name TEXT, + profileName TEXT + , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER); +CREATE TABLE identityKeys( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE items( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE sessions( + id TEXT PRIMARY KEY, + conversationId TEXT, + json TEXT + , ourServiceId STRING, serviceId STRING); +CREATE TABLE attachment_downloads( + id STRING primary key, + timestamp INTEGER, + pending INTEGER, + json TEXT + ); +CREATE TABLE sticker_packs( + id TEXT PRIMARY KEY, + key TEXT NOT NULL, + + author STRING, + coverStickerId INTEGER, + createdAt INTEGER, + downloadAttempts INTEGER, + installedAt INTEGER, + lastUsed INTEGER, + status STRING, + stickerCount INTEGER, + title STRING + , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync + INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE stickers( + id INTEGER NOT NULL, + packId TEXT NOT NULL, + + emoji STRING, + height INTEGER, + isCoverOnly INTEGER, + lastUsed INTEGER, + path STRING, + width INTEGER, + + PRIMARY KEY (id, packId), + CONSTRAINT stickers_fk + FOREIGN KEY (packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE sticker_references( + messageId STRING, + packId TEXT, + CONSTRAINT sticker_references_fk + FOREIGN KEY(packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE emojis( + shortName TEXT PRIMARY KEY, + lastUsage INTEGER + ); +CREATE TABLE messages( + rowid INTEGER PRIMARY KEY ASC, + id STRING UNIQUE, + json TEXT, + readStatus INTEGER, + expires_at INTEGER, + sent_at INTEGER, + schemaVersion INTEGER, + conversationId STRING, + received_at INTEGER, + source STRING, + hasAttachments INTEGER, + hasFileAttachments INTEGER, + hasVisualMediaAttachments INTEGER, + expireTimer INTEGER, + expirationStartTimestamp INTEGER, + type STRING, + body TEXT, + messageTimer INTEGER, + messageTimerStart INTEGER, + messageTimerExpiresAt INTEGER, + isErased INTEGER, + isViewOnce INTEGER, + sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER + GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER + GENERATED ALWAYS AS ( + json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1 + ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT + GENERATED ALWAYS + AS (ifnull( + expirationStartTimestamp + (expireTimer * 1000), + 9007199254740991 + )), shouldAffectActivity INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), shouldAffectPreview INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), isUserInitiatedMessage INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'group-v2-change', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER + GENERATED ALWAYS AS ( + type IS 'group-v2-change' AND + json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND + json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND + json_extract(json, '$.groupV2Change.from') IS NOT NULL AND + json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci') + ), isGroupLeaveEventFromOther INTEGER + GENERATED ALWAYS AS ( + isGroupLeaveEvent IS 1 + AND + isChangeCreatedByUs IS 0 + ), callId TEXT + GENERATED ALWAYS AS ( + json_extract(json, '$.callId') + )); +CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); +CREATE TABLE jobs( + id TEXT PRIMARY KEY, + queueType TEXT STRING NOT NULL, + timestamp INTEGER NOT NULL, + data STRING TEXT + ); +CREATE TABLE reactions( + conversationId STRING, + emoji STRING, + fromId STRING, + messageReceivedAt INTEGER, + targetAuthorAci STRING, + targetTimestamp INTEGER, + unread INTEGER + , messageId STRING); +CREATE TABLE senderKeys( + id TEXT PRIMARY KEY NOT NULL, + senderId TEXT NOT NULL, + distributionId TEXT NOT NULL, + data BLOB NOT NULL, + lastUpdatedDate NUMBER NOT NULL + ); +CREATE TABLE unprocessed( + id STRING PRIMARY KEY ASC, + timestamp INTEGER, + version INTEGER, + attempts INTEGER, + envelope TEXT, + decrypted TEXT, + source TEXT, + serverTimestamp INTEGER, + sourceServiceId STRING + , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER); +CREATE TABLE sendLogPayloads( + id INTEGER PRIMARY KEY ASC, + + timestamp INTEGER NOT NULL, + contentHint INTEGER NOT NULL, + proto BLOB NOT NULL + , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE sendLogRecipients( + payloadId INTEGER NOT NULL, + + recipientServiceId STRING NOT NULL, + deviceId INTEGER NOT NULL, + + PRIMARY KEY (payloadId, recipientServiceId, deviceId), + + CONSTRAINT sendLogRecipientsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE sendLogMessageIds( + payloadId INTEGER NOT NULL, + + messageId STRING NOT NULL, + + PRIMARY KEY (payloadId, messageId), + + CONSTRAINT sendLogMessageIdsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE preKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE signedPreKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE badges( + id TEXT PRIMARY KEY, + category TEXT NOT NULL, + name TEXT NOT NULL, + descriptionTemplate TEXT NOT NULL + ); +CREATE TABLE badgeImageFiles( + badgeId TEXT REFERENCES badges(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + 'order' INTEGER NOT NULL, + url TEXT NOT NULL, + localPath TEXT, + theme TEXT NOT NULL + ); +CREATE TABLE storyReads ( + authorId STRING NOT NULL, + conversationId STRING NOT NULL, + storyId STRING NOT NULL, + storyReadDate NUMBER NOT NULL, + + PRIMARY KEY (authorId, storyId) + ); +CREATE TABLE storyDistributions( + id STRING PRIMARY KEY NOT NULL, + name TEXT, + + senderKeyInfoJson STRING + , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER); +CREATE TABLE storyDistributionMembers( + listId STRING NOT NULL REFERENCES storyDistributions(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + serviceId STRING NOT NULL, + + PRIMARY KEY (listId, serviceId) + ); +CREATE TABLE uninstalled_sticker_packs ( + id STRING NOT NULL PRIMARY KEY, + uninstalledAt NUMBER NOT NULL, + storageID STRING, + storageVersion NUMBER, + storageUnknownFields BLOB, + storageNeedsSync INTEGER NOT NULL + ); +CREATE TABLE groupCallRingCancellations( + ringId INTEGER PRIMARY KEY, + createdAt INTEGER NOT NULL + ); +CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID; +CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0); +CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID; +CREATE TABLE edited_messages( + messageId STRING REFERENCES messages(id) + ON DELETE CASCADE, + sentAt INTEGER, + readStatus INTEGER + , conversationId STRING); +CREATE TABLE mentions ( + messageId REFERENCES messages(id) ON DELETE CASCADE, + mentionAci STRING, + start INTEGER, + length INTEGER + ); +CREATE TABLE kyberPreKeys( + id STRING PRIMARY KEY NOT NULL, + json TEXT NOT NULL, ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE callsHistory ( + callId TEXT PRIMARY KEY, + peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId + ringerId TEXT DEFAULT NULL, -- ringer uuid + mode TEXT NOT NULL, -- enum "Direct" | "Group" + type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group" + direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing + -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted" + -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted" + status TEXT NOT NULL, + timestamp INTEGER NOT NULL, + UNIQUE (callId, peerId) ON CONFLICT FAIL + ); +[ dropped all indexes to save space in this blog post ] +CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages + WHEN + new.body IS NOT NULL AND new.isViewOnce = 1 + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + END; +CREATE TRIGGER messages_on_insert AFTER INSERT ON messages + WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + DELETE FROM sendLogPayloads WHERE id IN ( + SELECT payloadId FROM sendLogMessageIds + WHERE messageId = old.id + ); + DELETE FROM reactions WHERE rowid IN ( + SELECT rowid FROM reactions + WHERE messageId = old.id + ); + DELETE FROM storyReads WHERE storyId = old.storyId; + END; +CREATE VIRTUAL TABLE messages_fts USING fts5( + body, + tokenize = 'signal_tokenizer' + ); +CREATE TRIGGER messages_on_update AFTER UPDATE ON messages + WHEN + (new.body IS NULL OR old.body IS NOT new.body) AND + new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages + BEGIN + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages + BEGIN + DELETE FROM mentions WHERE messageId = new.id; + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +sqlite> +</pre> + +Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format. + +As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +<a href="bitcoin:15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b">15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b</a>. + + + New chrpath release 0.17 https://people.skolelinux.org/pere/blog/New_chrpath_release_0_17.html diff --git a/blog/tags/english/index.html b/blog/tags/english/index.html index 69adc9192b..914e6571a8 100644 --- a/blog/tags/english/index.html +++ b/blog/tags/english/index.html @@ -20,6 +20,492 @@

Entries tagged "english".

+ New and improved sqlcipher in Debian for accessing Signal database +

+ 12th November 2023 +

+ +

/usr/bin/jq -r '."key"' ~/.config/Signal/config.json

+ +

+% sqlcipher ~/.config/Signal/sql/db.sqlite
+sqlite> PRAGMA key = "x'secretkey'";
+sqlite> .schema
+CREATE TABLE sqlite_stat1(tbl,idx,stat);
+CREATE TABLE conversations(
+      id STRING PRIMARY KEY ASC,
+      json TEXT,
+
+      active_at INTEGER,
+      type STRING,
+      members TEXT,
+      name TEXT,
+      profileName TEXT
+    , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER);
+CREATE TABLE identityKeys(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE items(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE sessions(
+      id TEXT PRIMARY KEY,
+      conversationId TEXT,
+      json TEXT
+    , ourServiceId STRING, serviceId STRING);
+CREATE TABLE attachment_downloads(
+    id STRING primary key,
+    timestamp INTEGER,
+    pending INTEGER,
+    json TEXT
+  );
+CREATE TABLE sticker_packs(
+    id TEXT PRIMARY KEY,
+    key TEXT NOT NULL,
+
+    author STRING,
+    coverStickerId INTEGER,
+    createdAt INTEGER,
+    downloadAttempts INTEGER,
+    installedAt INTEGER,
+    lastUsed INTEGER,
+    status STRING,
+    stickerCount INTEGER,
+    title STRING
+  , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync
+      INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE stickers(
+    id INTEGER NOT NULL,
+    packId TEXT NOT NULL,
+
+    emoji STRING,
+    height INTEGER,
+    isCoverOnly INTEGER,
+    lastUsed INTEGER,
+    path STRING,
+    width INTEGER,
+
+    PRIMARY KEY (id, packId),
+    CONSTRAINT stickers_fk
+      FOREIGN KEY (packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE sticker_references(
+    messageId STRING,
+    packId TEXT,
+    CONSTRAINT sticker_references_fk
+      FOREIGN KEY(packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE emojis(
+    shortName TEXT PRIMARY KEY,
+    lastUsage INTEGER
+  );
+CREATE TABLE messages(
+        rowid INTEGER PRIMARY KEY ASC,
+        id STRING UNIQUE,
+        json TEXT,
+        readStatus INTEGER,
+        expires_at INTEGER,
+        sent_at INTEGER,
+        schemaVersion INTEGER,
+        conversationId STRING,
+        received_at INTEGER,
+        source STRING,
+        hasAttachments INTEGER,
+        hasFileAttachments INTEGER,
+        hasVisualMediaAttachments INTEGER,
+        expireTimer INTEGER,
+        expirationStartTimestamp INTEGER,
+        type STRING,
+        body TEXT,
+        messageTimer INTEGER,
+        messageTimerStart INTEGER,
+        messageTimerExpiresAt INTEGER,
+        isErased INTEGER,
+        isViewOnce INTEGER,
+        sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER
+        GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1
+        ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT
+        GENERATED ALWAYS
+        AS (ifnull(
+          expirationStartTimestamp + (expireTimer * 1000),
+          9007199254740991
+        )), shouldAffectActivity INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), shouldAffectPreview INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), isUserInitiatedMessage INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'group-v2-change',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER
+        GENERATED ALWAYS AS (
+          type IS 'group-v2-change' AND
+          json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND
+          json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND
+          json_extract(json, '$.groupV2Change.from') IS NOT NULL AND
+          json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci')
+        ), isGroupLeaveEventFromOther INTEGER
+        GENERATED ALWAYS AS (
+          isGroupLeaveEvent IS 1
+          AND
+          isChangeCreatedByUs IS 0
+        ), callId TEXT
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.callId')
+        ));
+CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
+CREATE TABLE jobs(
+        id TEXT PRIMARY KEY,
+        queueType TEXT STRING NOT NULL,
+        timestamp INTEGER NOT NULL,
+        data STRING TEXT
+      );
+CREATE TABLE reactions(
+        conversationId STRING,
+        emoji STRING,
+        fromId STRING,
+        messageReceivedAt INTEGER,
+        targetAuthorAci STRING,
+        targetTimestamp INTEGER,
+        unread INTEGER
+      , messageId STRING);
+CREATE TABLE senderKeys(
+        id TEXT PRIMARY KEY NOT NULL,
+        senderId TEXT NOT NULL,
+        distributionId TEXT NOT NULL,
+        data BLOB NOT NULL,
+        lastUpdatedDate NUMBER NOT NULL
+      );
+CREATE TABLE unprocessed(
+        id STRING PRIMARY KEY ASC,
+        timestamp INTEGER,
+        version INTEGER,
+        attempts INTEGER,
+        envelope TEXT,
+        decrypted TEXT,
+        source TEXT,
+        serverTimestamp INTEGER,
+        sourceServiceId STRING
+      , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER);
+CREATE TABLE sendLogPayloads(
+        id INTEGER PRIMARY KEY ASC,
+
+        timestamp INTEGER NOT NULL,
+        contentHint INTEGER NOT NULL,
+        proto BLOB NOT NULL
+      , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE sendLogRecipients(
+        payloadId INTEGER NOT NULL,
+
+        recipientServiceId STRING NOT NULL,
+        deviceId INTEGER NOT NULL,
+
+        PRIMARY KEY (payloadId, recipientServiceId, deviceId),
+
+        CONSTRAINT sendLogRecipientsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE sendLogMessageIds(
+        payloadId INTEGER NOT NULL,
+
+        messageId STRING NOT NULL,
+
+        PRIMARY KEY (payloadId, messageId),
+
+        CONSTRAINT sendLogMessageIdsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE preKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE signedPreKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE badges(
+        id TEXT PRIMARY KEY,
+        category TEXT NOT NULL,
+        name TEXT NOT NULL,
+        descriptionTemplate TEXT NOT NULL
+      );
+CREATE TABLE badgeImageFiles(
+        badgeId TEXT REFERENCES badges(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        'order' INTEGER NOT NULL,
+        url TEXT NOT NULL,
+        localPath TEXT,
+        theme TEXT NOT NULL
+      );
+CREATE TABLE storyReads (
+        authorId STRING NOT NULL,
+        conversationId STRING NOT NULL,
+        storyId STRING NOT NULL,
+        storyReadDate NUMBER NOT NULL,
+
+        PRIMARY KEY (authorId, storyId)
+      );
+CREATE TABLE storyDistributions(
+        id STRING PRIMARY KEY NOT NULL,
+        name TEXT,
+
+        senderKeyInfoJson STRING
+      , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER);
+CREATE TABLE storyDistributionMembers(
+        listId STRING NOT NULL REFERENCES storyDistributions(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        serviceId STRING NOT NULL,
+
+        PRIMARY KEY (listId, serviceId)
+      );
+CREATE TABLE uninstalled_sticker_packs (
+        id STRING NOT NULL PRIMARY KEY,
+        uninstalledAt NUMBER NOT NULL,
+        storageID STRING,
+        storageVersion NUMBER,
+        storageUnknownFields BLOB,
+        storageNeedsSync INTEGER NOT NULL
+      );
+CREATE TABLE groupCallRingCancellations(
+        ringId INTEGER PRIMARY KEY,
+        createdAt INTEGER NOT NULL
+      );
+CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
+CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0);
+CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
+CREATE TABLE edited_messages(
+        messageId STRING REFERENCES messages(id)
+          ON DELETE CASCADE,
+        sentAt INTEGER,
+        readStatus INTEGER
+      , conversationId STRING);
+CREATE TABLE mentions (
+        messageId REFERENCES messages(id) ON DELETE CASCADE,
+        mentionAci STRING,
+        start INTEGER,
+        length INTEGER
+      );
+CREATE TABLE kyberPreKeys(
+        id STRING PRIMARY KEY NOT NULL,
+        json TEXT NOT NULL, ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE callsHistory (
+        callId TEXT PRIMARY KEY,
+        peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId
+        ringerId TEXT DEFAULT NULL, -- ringer uuid
+        mode TEXT NOT NULL, -- enum "Direct" | "Group"
+        type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group"
+        direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing
+        -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted"
+        -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted"
+        status TEXT NOT NULL,
+        timestamp INTEGER NOT NULL,
+        UNIQUE (callId, peerId) ON CONFLICT FAIL
+      );
+[ dropped all indexes to save space in this blog post ]
+CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages
+      WHEN
+        new.body IS NOT NULL AND new.isViewOnce = 1
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+      END;
+CREATE TRIGGER messages_on_insert AFTER INSERT ON messages
+      WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        DELETE FROM sendLogPayloads WHERE id IN (
+          SELECT payloadId FROM sendLogMessageIds
+          WHERE messageId = old.id
+        );
+        DELETE FROM reactions WHERE rowid IN (
+          SELECT rowid FROM reactions
+          WHERE messageId = old.id
+        );
+        DELETE FROM storyReads WHERE storyId = old.storyId;
+      END;
+CREATE VIRTUAL TABLE messages_fts USING fts5(
+        body,
+        tokenize = 'signal_tokenizer'
+      );
+CREATE TRIGGER messages_on_update AFTER UPDATE ON messages
+      WHEN
+        (new.body IS NULL OR old.body IS NOT new.body) AND
+         new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages
+      BEGIN
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages
+      BEGIN
+        DELETE FROM mentions WHERE messageId = new.id;
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+sqlite>
+

+ +

Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format.

+ +

As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b.

+ +

+ + + Tags: debian, english, sikkerhet, surveillance. + + +

New chrpath release 0.17 @@ -36543,7 +37029,7 @@ be the only one fitting our needs. :/

October (1)

November (2)

November (3)

@@ -36959,7 +37445,7 @@ be the only one fitting our needs. :/

@@ -36973,7 +37459,7 @@ be the only one fitting our needs. :/

@@ -37047,7 +37533,7 @@ be the only one fitting our needs. :/

@@ -37059,7 +37545,7 @@ be the only one fitting our needs. :/

diff --git a/blog/tags/fiksgatami/index.html b/blog/tags/fiksgatami/index.html index 714c6d45e4..62b7e2ea7d 100644 --- a/blog/tags/fiksgatami/index.html +++ b/blog/tags/fiksgatami/index.html @@ -1356,7 +1356,7 @@ med dem. Dette blir bra.

October (1)

November (2)

November (3)

@@ -1772,7 +1772,7 @@ med dem. Dette blir bra.

@@ -1786,7 +1786,7 @@ med dem. Dette blir bra.

@@ -1860,7 +1860,7 @@ med dem. Dette blir bra.

@@ -1872,7 +1872,7 @@ med dem. Dette blir bra.

diff --git a/blog/tags/fildeling/index.html b/blog/tags/fildeling/index.html index 907328a2dd..108c2969fd 100644 --- a/blog/tags/fildeling/index.html +++ b/blog/tags/fildeling/index.html @@ -1503,7 +1503,7 @@ og fildeling av slike filer er fullt ut lovlig.

October (1)

November (2)

November (3)

@@ -1919,7 +1919,7 @@ og fildeling av slike filer er fullt ut lovlig.

@@ -1933,7 +1933,7 @@ og fildeling av slike filer er fullt ut lovlig.

@@ -2007,7 +2007,7 @@ og fildeling av slike filer er fullt ut lovlig.

@@ -2019,7 +2019,7 @@ og fildeling av slike filer er fullt ut lovlig.

diff --git a/blog/tags/freeculture/index.html b/blog/tags/freeculture/index.html index b410b89a10..e4782aeda5 100644 --- a/blog/tags/freeculture/index.html +++ b/blog/tags/freeculture/index.html @@ -3223,7 +3223,7 @@ slik at du kan oppdatere direkte.

October (1)

November (2)

November (3)

@@ -3639,7 +3639,7 @@ slik at du kan oppdatere direkte.

@@ -3653,7 +3653,7 @@ slik at du kan oppdatere direkte.

@@ -3727,7 +3727,7 @@ slik at du kan oppdatere direkte.

@@ -3739,7 +3739,7 @@ slik at du kan oppdatere direkte.

diff --git a/blog/tags/freedombox/index.html b/blog/tags/freedombox/index.html index f565ebdb3a..be94238aee 100644 --- a/blog/tags/freedombox/index.html +++ b/blog/tags/freedombox/index.html @@ -950,7 +950,7 @@ default password is 'secret'.

October (1)

November (2)

November (3)

@@ -1366,7 +1366,7 @@ default password is 'secret'.

@@ -1380,7 +1380,7 @@ default password is 'secret'.

@@ -1454,7 +1454,7 @@ default password is 'secret'.

@@ -1466,7 +1466,7 @@ default password is 'secret'.

diff --git a/blog/tags/frikanalen/index.html b/blog/tags/frikanalen/index.html index 179513c38c..b0a9828cb0 100644 --- a/blog/tags/frikanalen/index.html +++ b/blog/tags/frikanalen/index.html @@ -1538,7 +1538,7 @@ NUUG lykkes med Ã¥ fÃ¥ ut sine opptak med like stor suksess.

October (1)

November (2)

November (3)

@@ -1954,7 +1954,7 @@ NUUG lykkes med Ã¥ fÃ¥ ut sine opptak med like stor suksess.

@@ -1968,7 +1968,7 @@ NUUG lykkes med Ã¥ fÃ¥ ut sine opptak med like stor suksess.

@@ -2042,7 +2042,7 @@ NUUG lykkes med Ã¥ fÃ¥ ut sine opptak med like stor suksess.

@@ -2054,7 +2054,7 @@ NUUG lykkes med Ã¥ fÃ¥ ut sine opptak med like stor suksess.

diff --git a/blog/tags/h264/index.html b/blog/tags/h264/index.html index 98900edee8..023200eca4 100644 --- a/blog/tags/h264/index.html +++ b/blog/tags/h264/index.html @@ -2522,7 +2522,7 @@ sure hope it was using the announced Ogg Theora support. :)

October (1)

November (2)

November (3)

@@ -2938,7 +2938,7 @@ sure hope it was using the announced Ogg Theora support. :)

@@ -2952,7 +2952,7 @@ sure hope it was using the announced Ogg Theora support. :)

@@ -3026,7 +3026,7 @@ sure hope it was using the announced Ogg Theora support. :)

@@ -3038,7 +3038,7 @@ sure hope it was using the announced Ogg Theora support. :)

diff --git a/blog/tags/intervju/index.html b/blog/tags/intervju/index.html index a1579495c8..be9ff053b5 100644 --- a/blog/tags/intervju/index.html +++ b/blog/tags/intervju/index.html @@ -5270,7 +5270,7 @@ veldig bra utvalg av gratis spill som er av hÃ¸y kvalitet. Veldig lett

October (1)

November (2)

November (3)

@@ -5686,7 +5686,7 @@ veldig bra utvalg av gratis spill som er av hÃ¸y kvalitet. Veldig lett

@@ -5700,7 +5700,7 @@ veldig bra utvalg av gratis spill som er av hÃ¸y kvalitet. Veldig lett

@@ -5774,7 +5774,7 @@ veldig bra utvalg av gratis spill som er av hÃ¸y kvalitet. Veldig lett

@@ -5786,7 +5786,7 @@ veldig bra utvalg av gratis spill som er av hÃ¸y kvalitet. Veldig lett

diff --git a/blog/tags/isenkram/index.html b/blog/tags/isenkram/index.html index abe72b59bf..adf4016ca1 100644 --- a/blog/tags/isenkram/index.html +++ b/blog/tags/isenkram/index.html @@ -1732,7 +1732,7 @@ please send me an email. :)

October (1)

November (2)

November (3)

@@ -2148,7 +2148,7 @@ please send me an email. :)

@@ -2162,7 +2162,7 @@ please send me an email. :)

@@ -2236,7 +2236,7 @@ please send me an email. :)

@@ -2248,7 +2248,7 @@ please send me an email. :)

diff --git a/blog/tags/kart/index.html b/blog/tags/kart/index.html index 9ff6bd5292..9a1ef2a370 100644 --- a/blog/tags/kart/index.html +++ b/blog/tags/kart/index.html @@ -2460,7 +2460,7 @@ det viser at behovet for fribruks-sjÃ¸kart er til stedet.

October (1)

November (2)

November (3)

@@ -2876,7 +2876,7 @@ det viser at behovet for fribruks-sjÃ¸kart er til stedet.

@@ -2890,7 +2890,7 @@ det viser at behovet for fribruks-sjÃ¸kart er til stedet.

@@ -2964,7 +2964,7 @@ det viser at behovet for fribruks-sjÃ¸kart er til stedet.

@@ -2976,7 +2976,7 @@ det viser at behovet for fribruks-sjÃ¸kart er til stedet.

diff --git a/blog/tags/kodi/index.html b/blog/tags/kodi/index.html index be6f28d80c..a671e801a1 100644 --- a/blog/tags/kodi/index.html +++ b/blog/tags/kodi/index.html @@ -551,7 +551,7 @@ activities, please send Bitcoin donations to my address

October (1)

November (2)

November (3)

@@ -967,7 +967,7 @@ activities, please send Bitcoin donations to my address

@@ -981,7 +981,7 @@ activities, please send Bitcoin donations to my address

@@ -1055,7 +1055,7 @@ activities, please send Bitcoin donations to my address

@@ -1067,7 +1067,7 @@ activities, please send Bitcoin donations to my address

diff --git a/blog/tags/ldap/index.html b/blog/tags/ldap/index.html index cb165abb45..9184d7e4c5 100644 --- a/blog/tags/ldap/index.html +++ b/blog/tags/ldap/index.html @@ -1066,7 +1066,7 @@ new IETF work group?

October (1)

November (2)

November (3)

@@ -1482,7 +1482,7 @@ new IETF work group?

@@ -1496,7 +1496,7 @@ new IETF work group?

@@ -1570,7 +1570,7 @@ new IETF work group?

@@ -1582,7 +1582,7 @@ new IETF work group?

diff --git a/blog/tags/lego/index.html b/blog/tags/lego/index.html index ee5bf9f6c3..c779af444b 100644 --- a/blog/tags/lego/index.html +++ b/blog/tags/lego/index.html @@ -383,7 +383,7 @@ including links to Lego related packages is now available.

October (1)

November (2)

November (3)

@@ -799,7 +799,7 @@ including links to Lego related packages is now available.

@@ -813,7 +813,7 @@ including links to Lego related packages is now available.

@@ -887,7 +887,7 @@ including links to Lego related packages is now available.

@@ -899,7 +899,7 @@ including links to Lego related packages is now available.

diff --git a/blog/tags/lenker/index.html b/blog/tags/lenker/index.html index f08640319f..53dc92f5a9 100644 --- a/blog/tags/lenker/index.html +++ b/blog/tags/lenker/index.html @@ -661,7 +661,7 @@ Word 2007 hÃ¥ndterer ODF dÃ¥rlig

October (1)

November (2)

November (3)

@@ -1077,7 +1077,7 @@ Word 2007 hÃ¥ndterer ODF dÃ¥rlig

@@ -1091,7 +1091,7 @@ Word 2007 hÃ¥ndterer ODF dÃ¥rlig

@@ -1165,7 +1165,7 @@ Word 2007 hÃ¥ndterer ODF dÃ¥rlig

@@ -1177,7 +1177,7 @@ Word 2007 hÃ¥ndterer ODF dÃ¥rlig

diff --git a/blog/tags/linuxcnc/index.html b/blog/tags/linuxcnc/index.html index 62da1cd373..19e1ad1d94 100644 --- a/blog/tags/linuxcnc/index.html +++ b/blog/tags/linuxcnc/index.html @@ -452,7 +452,7 @@ activities, please send Bitcoin donations to my address

October (1)

November (2)

November (3)

@@ -868,7 +868,7 @@ activities, please send Bitcoin donations to my address

@@ -882,7 +882,7 @@ activities, please send Bitcoin donations to my address

@@ -956,7 +956,7 @@ activities, please send Bitcoin donations to my address

@@ -968,7 +968,7 @@ activities, please send Bitcoin donations to my address

diff --git a/blog/tags/lsdvd/index.html b/blog/tags/lsdvd/index.html index 79443a7edb..01dbc8bb2a 100644 --- a/blog/tags/lsdvd/index.html +++ b/blog/tags/lsdvd/index.html @@ -147,7 +147,7 @@ list. :)

October (1)

November (2)

November (3)

@@ -563,7 +563,7 @@ list. :)

@@ -577,7 +577,7 @@ list. :)

@@ -651,7 +651,7 @@ list. :)

@@ -663,7 +663,7 @@ list. :)

diff --git a/blog/tags/ltsp/index.html b/blog/tags/ltsp/index.html index 1f76e06569..6a8020eb55 100644 --- a/blog/tags/ltsp/index.html +++ b/blog/tags/ltsp/index.html @@ -81,7 +81,7 @@ of these cards.

October (1)

November (2)

November (3)

@@ -497,7 +497,7 @@ of these cards.

@@ -511,7 +511,7 @@ of these cards.

@@ -585,7 +585,7 @@ of these cards.

@@ -597,7 +597,7 @@ of these cards.

diff --git a/blog/tags/madewithcc/index.html b/blog/tags/madewithcc/index.html index 5864660871..bff0782ac4 100644 --- a/blog/tags/madewithcc/index.html +++ b/blog/tags/madewithcc/index.html @@ -248,7 +248,7 @@ activities, please send Bitcoin donations to my address

October (1)

November (2)

November (3)

@@ -664,7 +664,7 @@ activities, please send Bitcoin donations to my address

@@ -678,7 +678,7 @@ activities, please send Bitcoin donations to my address

@@ -752,7 +752,7 @@ activities, please send Bitcoin donations to my address

@@ -764,7 +764,7 @@ activities, please send Bitcoin donations to my address

diff --git a/blog/tags/mesh network/index.html b/blog/tags/mesh network/index.html index 7e78766074..13bc0ef756 100644 --- a/blog/tags/mesh network/index.html +++ b/blog/tags/mesh network/index.html @@ -731,7 +731,7 @@ mesh system.

October (1)

November (2)

November (3)

@@ -1147,7 +1147,7 @@ mesh system.

@@ -1161,7 +1161,7 @@ mesh system.

@@ -1235,7 +1235,7 @@ mesh system.

@@ -1247,7 +1247,7 @@ mesh system.

diff --git a/blog/tags/multimedia/index.html b/blog/tags/multimedia/index.html index f0e7502a9b..3aacb3b913 100644 --- a/blog/tags/multimedia/index.html +++ b/blog/tags/multimedia/index.html @@ -3880,7 +3880,7 @@ be the only one fitting our needs. :/

October (1)

November (2)

November (3)

@@ -4296,7 +4296,7 @@ be the only one fitting our needs. :/

@@ -4310,7 +4310,7 @@ be the only one fitting our needs. :/

@@ -4384,7 +4384,7 @@ be the only one fitting our needs. :/

@@ -4396,7 +4396,7 @@ be the only one fitting our needs. :/

diff --git a/blog/tags/nice free software/index.html b/blog/tags/nice free software/index.html index a855404cf2..6b16757fa9 100644 --- a/blog/tags/nice free software/index.html +++ b/blog/tags/nice free software/index.html @@ -1586,7 +1586,7 @@ section.

October (1)

November (2)

November (3)

@@ -2002,7 +2002,7 @@ section.

@@ -2016,7 +2016,7 @@ section.

@@ -2090,7 +2090,7 @@ section.

@@ -2102,7 +2102,7 @@ section.

diff --git a/blog/tags/noark5/index.html b/blog/tags/noark5/index.html index c35940df26..4b8246a554 100644 --- a/blog/tags/noark5/index.html +++ b/blog/tags/noark5/index.html @@ -2357,7 +2357,7 @@ meg en epost hvis du har innspill.

October (1)

November (2)

November (3)

@@ -2773,7 +2773,7 @@ meg en epost hvis du har innspill.

@@ -2787,7 +2787,7 @@ meg en epost hvis du har innspill.

@@ -2861,7 +2861,7 @@ meg en epost hvis du har innspill.

@@ -2873,7 +2873,7 @@ meg en epost hvis du har innspill.

diff --git a/blog/tags/norsk/index.html b/blog/tags/norsk/index.html index 114e9b68b3..30677163c7 100644 --- a/blog/tags/norsk/index.html +++ b/blog/tags/norsk/index.html @@ -29940,7 +29940,7 @@ forsÃ¸k.

October (1)

November (2)

November (3)

@@ -30356,7 +30356,7 @@ forsÃ¸k.

@@ -30370,7 +30370,7 @@ forsÃ¸k.

@@ -30444,7 +30444,7 @@ forsÃ¸k.

@@ -30456,7 +30456,7 @@ forsÃ¸k.

diff --git a/blog/tags/nuug/index.html b/blog/tags/nuug/index.html index ac00ab256a..930b93ac9f 100644 --- a/blog/tags/nuug/index.html +++ b/blog/tags/nuug/index.html @@ -15110,7 +15110,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

October (1)

November (2)

November (3)

@@ -15526,7 +15526,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

@@ -15540,7 +15540,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

@@ -15614,7 +15614,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

@@ -15626,7 +15626,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

diff --git a/blog/tags/offentlig innsyn/index.html b/blog/tags/offentlig innsyn/index.html index 19efff3713..f7a1d23683 100644 --- a/blog/tags/offentlig innsyn/index.html +++ b/blog/tags/offentlig innsyn/index.html @@ -5303,7 +5303,7 @@ til Ã¥ levere hver uke. Har ikke undersÃ¸kt noen av de andre.

October (1)

November (2)

November (3)

@@ -5719,7 +5719,7 @@ til Ã¥ levere hver uke. Har ikke undersÃ¸kt noen av de andre.

@@ -5733,7 +5733,7 @@ til Ã¥ levere hver uke. Har ikke undersÃ¸kt noen av de andre.

@@ -5807,7 +5807,7 @@ til Ã¥ levere hver uke. Har ikke undersÃ¸kt noen av de andre.

@@ -5819,7 +5819,7 @@ til Ã¥ levere hver uke. Har ikke undersÃ¸kt noen av de andre.

diff --git a/blog/tags/open311/index.html b/blog/tags/open311/index.html index 4d7e019a63..75072fde1a 100644 --- a/blog/tags/open311/index.html +++ b/blog/tags/open311/index.html @@ -176,7 +176,7 @@ work like the free software project communities I am used to.

October (1)

November (2)

November (3)

@@ -592,7 +592,7 @@ work like the free software project communities I am used to.

@@ -606,7 +606,7 @@ work like the free software project communities I am used to.

@@ -680,7 +680,7 @@ work like the free software project communities I am used to.

@@ -692,7 +692,7 @@ work like the free software project communities I am used to.

diff --git a/blog/tags/opensnitch/index.html b/blog/tags/opensnitch/index.html index ad662c853d..81a31e5be4 100644 --- a/blog/tags/opensnitch/index.html +++ b/blog/tags/opensnitch/index.html @@ -271,7 +271,7 @@ activities, please send Bitcoin donations to my address

October (1)

November (2)

November (3)

@@ -687,7 +687,7 @@ activities, please send Bitcoin donations to my address

@@ -701,7 +701,7 @@ activities, please send Bitcoin donations to my address

@@ -775,7 +775,7 @@ activities, please send Bitcoin donations to my address

@@ -787,7 +787,7 @@ activities, please send Bitcoin donations to my address

diff --git a/blog/tags/opphavsrett/index.html b/blog/tags/opphavsrett/index.html index 9b073a7fb4..f0917a5dc1 100644 --- a/blog/tags/opphavsrett/index.html +++ b/blog/tags/opphavsrett/index.html @@ -9261,7 +9261,7 @@ database.

October (1)

November (2)

November (3)

@@ -9677,7 +9677,7 @@ database.

@@ -9691,7 +9691,7 @@ database.

@@ -9765,7 +9765,7 @@ database.

@@ -9777,7 +9777,7 @@ database.

diff --git a/blog/tags/personvern/index.html b/blog/tags/personvern/index.html index abea8ac28a..3663274743 100644 --- a/blog/tags/personvern/index.html +++ b/blog/tags/personvern/index.html @@ -10728,7 +10728,7 @@ kontanter for noen dager siden.

October (1)

November (2)

November (3)

@@ -11144,7 +11144,7 @@ kontanter for noen dager siden.

@@ -11158,7 +11158,7 @@ kontanter for noen dager siden.

@@ -11232,7 +11232,7 @@ kontanter for noen dager siden.

@@ -11244,7 +11244,7 @@ kontanter for noen dager siden.

diff --git a/blog/tags/raid/index.html b/blog/tags/raid/index.html index ff3295539f..c284feb50d 100644 --- a/blog/tags/raid/index.html +++ b/blog/tags/raid/index.html @@ -211,7 +211,7 @@ disk(s) is failing when the RAID is running short on disks.

October (1)

November (2)

November (3)

@@ -627,7 +627,7 @@ disk(s) is failing when the RAID is running short on disks.

@@ -641,7 +641,7 @@ disk(s) is failing when the RAID is running short on disks.

@@ -715,7 +715,7 @@ disk(s) is failing when the RAID is running short on disks.

@@ -727,7 +727,7 @@ disk(s) is failing when the RAID is running short on disks.

diff --git a/blog/tags/reactos/index.html b/blog/tags/reactos/index.html index 64b483dc23..a47af16840 100644 --- a/blog/tags/reactos/index.html +++ b/blog/tags/reactos/index.html @@ -116,7 +116,7 @@ image.

October (1)

November (2)

November (3)

@@ -532,7 +532,7 @@ image.

@@ -546,7 +546,7 @@ image.

@@ -620,7 +620,7 @@ image.

@@ -632,7 +632,7 @@ image.

diff --git a/blog/tags/reprap/index.html b/blog/tags/reprap/index.html index d8f811ec2b..6b64b7d3d9 100644 --- a/blog/tags/reprap/index.html +++ b/blog/tags/reprap/index.html @@ -543,7 +543,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

October (1)

November (2)

November (3)

@@ -959,7 +959,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

@@ -973,7 +973,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

@@ -1047,7 +1047,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

@@ -1059,7 +1059,7 @@ hÃ¥per det ikke gÃ¥r tapt pÃ¥ samme vis.

diff --git a/blog/tags/rfid/index.html b/blog/tags/rfid/index.html index cf3fd93dac..91656ae2a6 100644 --- a/blog/tags/rfid/index.html +++ b/blog/tags/rfid/index.html @@ -447,7 +447,7 @@ mer om de nye biometriske passene.

October (1)

November (2)

November (3)

@@ -863,7 +863,7 @@ mer om de nye biometriske passene.

@@ -877,7 +877,7 @@ mer om de nye biometriske passene.

@@ -951,7 +951,7 @@ mer om de nye biometriske passene.

@@ -963,7 +963,7 @@ mer om de nye biometriske passene.

diff --git a/blog/tags/robot/index.html b/blog/tags/robot/index.html index 57031bfbdf..4c9788c59a 100644 --- a/blog/tags/robot/index.html +++ b/blog/tags/robot/index.html @@ -1088,7 +1088,7 @@ firmwaren. :)

October (1)

November (2)

November (3)

@@ -1504,7 +1504,7 @@ firmwaren. :)

@@ -1518,7 +1518,7 @@ firmwaren. :)

@@ -1592,7 +1592,7 @@ firmwaren. :)

@@ -1604,7 +1604,7 @@ firmwaren. :)

diff --git a/blog/tags/rss/index.html b/blog/tags/rss/index.html index 5bbd8b87be..2d2e3aaf93 100644 --- a/blog/tags/rss/index.html +++ b/blog/tags/rss/index.html @@ -70,7 +70,7 @@ forsÃ¸k.

October (1)

November (2)

November (3)

@@ -486,7 +486,7 @@ forsÃ¸k.

@@ -500,7 +500,7 @@ forsÃ¸k.

@@ -574,7 +574,7 @@ forsÃ¸k.

@@ -586,7 +586,7 @@ forsÃ¸k.

diff --git a/blog/tags/ruter/index.html b/blog/tags/ruter/index.html index 3a0ec740a8..39e544a943 100644 --- a/blog/tags/ruter/index.html +++ b/blog/tags/ruter/index.html @@ -407,7 +407,7 @@ OsloomrÃ¥det.

October (1)

November (2)

November (3)

@@ -823,7 +823,7 @@ OsloomrÃ¥det.

@@ -837,7 +837,7 @@ OsloomrÃ¥det.

@@ -911,7 +911,7 @@ OsloomrÃ¥det.

@@ -923,7 +923,7 @@ OsloomrÃ¥det.

diff --git a/blog/tags/scraperwiki/index.html b/blog/tags/scraperwiki/index.html index 60357a2c23..f35d7a6d75 100644 --- a/blog/tags/scraperwiki/index.html +++ b/blog/tags/scraperwiki/index.html @@ -159,7 +159,7 @@ av Ã¥ dele informasjon med andre uten bruksbegresninger.

October (1)

November (2)

November (3)

@@ -575,7 +575,7 @@ av Ã¥ dele informasjon med andre uten bruksbegresninger.

@@ -589,7 +589,7 @@ av Ã¥ dele informasjon med andre uten bruksbegresninger.

@@ -663,7 +663,7 @@ av Ã¥ dele informasjon med andre uten bruksbegresninger.

@@ -675,7 +675,7 @@ av Ã¥ dele informasjon med andre uten bruksbegresninger.

diff --git a/blog/tags/sikkerhet/index.html b/blog/tags/sikkerhet/index.html index da8f75ba64..40593e941b 100644 --- a/blog/tags/sikkerhet/index.html +++ b/blog/tags/sikkerhet/index.html @@ -20,6 +20,492 @@

Entries tagged "sikkerhet".

+ New and improved sqlcipher in Debian for accessing Signal database +

+ 12th November 2023 +

+ +

/usr/bin/jq -r '."key"' ~/.config/Signal/config.json

+ +

+% sqlcipher ~/.config/Signal/sql/db.sqlite
+sqlite> PRAGMA key = "x'secretkey'";
+sqlite> .schema
+CREATE TABLE sqlite_stat1(tbl,idx,stat);
+CREATE TABLE conversations(
+      id STRING PRIMARY KEY ASC,
+      json TEXT,
+
+      active_at INTEGER,
+      type STRING,
+      members TEXT,
+      name TEXT,
+      profileName TEXT
+    , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER);
+CREATE TABLE identityKeys(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE items(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE sessions(
+      id TEXT PRIMARY KEY,
+      conversationId TEXT,
+      json TEXT
+    , ourServiceId STRING, serviceId STRING);
+CREATE TABLE attachment_downloads(
+    id STRING primary key,
+    timestamp INTEGER,
+    pending INTEGER,
+    json TEXT
+  );
+CREATE TABLE sticker_packs(
+    id TEXT PRIMARY KEY,
+    key TEXT NOT NULL,
+
+    author STRING,
+    coverStickerId INTEGER,
+    createdAt INTEGER,
+    downloadAttempts INTEGER,
+    installedAt INTEGER,
+    lastUsed INTEGER,
+    status STRING,
+    stickerCount INTEGER,
+    title STRING
+  , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync
+      INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE stickers(
+    id INTEGER NOT NULL,
+    packId TEXT NOT NULL,
+
+    emoji STRING,
+    height INTEGER,
+    isCoverOnly INTEGER,
+    lastUsed INTEGER,
+    path STRING,
+    width INTEGER,
+
+    PRIMARY KEY (id, packId),
+    CONSTRAINT stickers_fk
+      FOREIGN KEY (packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE sticker_references(
+    messageId STRING,
+    packId TEXT,
+    CONSTRAINT sticker_references_fk
+      FOREIGN KEY(packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE emojis(
+    shortName TEXT PRIMARY KEY,
+    lastUsage INTEGER
+  );
+CREATE TABLE messages(
+        rowid INTEGER PRIMARY KEY ASC,
+        id STRING UNIQUE,
+        json TEXT,
+        readStatus INTEGER,
+        expires_at INTEGER,
+        sent_at INTEGER,
+        schemaVersion INTEGER,
+        conversationId STRING,
+        received_at INTEGER,
+        source STRING,
+        hasAttachments INTEGER,
+        hasFileAttachments INTEGER,
+        hasVisualMediaAttachments INTEGER,
+        expireTimer INTEGER,
+        expirationStartTimestamp INTEGER,
+        type STRING,
+        body TEXT,
+        messageTimer INTEGER,
+        messageTimerStart INTEGER,
+        messageTimerExpiresAt INTEGER,
+        isErased INTEGER,
+        isViewOnce INTEGER,
+        sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER
+        GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1
+        ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT
+        GENERATED ALWAYS
+        AS (ifnull(
+          expirationStartTimestamp + (expireTimer * 1000),
+          9007199254740991
+        )), shouldAffectActivity INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), shouldAffectPreview INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), isUserInitiatedMessage INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'group-v2-change',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER
+        GENERATED ALWAYS AS (
+          type IS 'group-v2-change' AND
+          json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND
+          json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND
+          json_extract(json, '$.groupV2Change.from') IS NOT NULL AND
+          json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci')
+        ), isGroupLeaveEventFromOther INTEGER
+        GENERATED ALWAYS AS (
+          isGroupLeaveEvent IS 1
+          AND
+          isChangeCreatedByUs IS 0
+        ), callId TEXT
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.callId')
+        ));
+CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
+CREATE TABLE jobs(
+        id TEXT PRIMARY KEY,
+        queueType TEXT STRING NOT NULL,
+        timestamp INTEGER NOT NULL,
+        data STRING TEXT
+      );
+CREATE TABLE reactions(
+        conversationId STRING,
+        emoji STRING,
+        fromId STRING,
+        messageReceivedAt INTEGER,
+        targetAuthorAci STRING,
+        targetTimestamp INTEGER,
+        unread INTEGER
+      , messageId STRING);
+CREATE TABLE senderKeys(
+        id TEXT PRIMARY KEY NOT NULL,
+        senderId TEXT NOT NULL,
+        distributionId TEXT NOT NULL,
+        data BLOB NOT NULL,
+        lastUpdatedDate NUMBER NOT NULL
+      );
+CREATE TABLE unprocessed(
+        id STRING PRIMARY KEY ASC,
+        timestamp INTEGER,
+        version INTEGER,
+        attempts INTEGER,
+        envelope TEXT,
+        decrypted TEXT,
+        source TEXT,
+        serverTimestamp INTEGER,
+        sourceServiceId STRING
+      , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER);
+CREATE TABLE sendLogPayloads(
+        id INTEGER PRIMARY KEY ASC,
+
+        timestamp INTEGER NOT NULL,
+        contentHint INTEGER NOT NULL,
+        proto BLOB NOT NULL
+      , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE sendLogRecipients(
+        payloadId INTEGER NOT NULL,
+
+        recipientServiceId STRING NOT NULL,
+        deviceId INTEGER NOT NULL,
+
+        PRIMARY KEY (payloadId, recipientServiceId, deviceId),
+
+        CONSTRAINT sendLogRecipientsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE sendLogMessageIds(
+        payloadId INTEGER NOT NULL,
+
+        messageId STRING NOT NULL,
+
+        PRIMARY KEY (payloadId, messageId),
+
+        CONSTRAINT sendLogMessageIdsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE preKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE signedPreKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE badges(
+        id TEXT PRIMARY KEY,
+        category TEXT NOT NULL,
+        name TEXT NOT NULL,
+        descriptionTemplate TEXT NOT NULL
+      );
+CREATE TABLE badgeImageFiles(
+        badgeId TEXT REFERENCES badges(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        'order' INTEGER NOT NULL,
+        url TEXT NOT NULL,
+        localPath TEXT,
+        theme TEXT NOT NULL
+      );
+CREATE TABLE storyReads (
+        authorId STRING NOT NULL,
+        conversationId STRING NOT NULL,
+        storyId STRING NOT NULL,
+        storyReadDate NUMBER NOT NULL,
+
+        PRIMARY KEY (authorId, storyId)
+      );
+CREATE TABLE storyDistributions(
+        id STRING PRIMARY KEY NOT NULL,
+        name TEXT,
+
+        senderKeyInfoJson STRING
+      , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER);
+CREATE TABLE storyDistributionMembers(
+        listId STRING NOT NULL REFERENCES storyDistributions(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        serviceId STRING NOT NULL,
+
+        PRIMARY KEY (listId, serviceId)
+      );
+CREATE TABLE uninstalled_sticker_packs (
+        id STRING NOT NULL PRIMARY KEY,
+        uninstalledAt NUMBER NOT NULL,
+        storageID STRING,
+        storageVersion NUMBER,
+        storageUnknownFields BLOB,
+        storageNeedsSync INTEGER NOT NULL
+      );
+CREATE TABLE groupCallRingCancellations(
+        ringId INTEGER PRIMARY KEY,
+        createdAt INTEGER NOT NULL
+      );
+CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
+CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0);
+CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
+CREATE TABLE edited_messages(
+        messageId STRING REFERENCES messages(id)
+          ON DELETE CASCADE,
+        sentAt INTEGER,
+        readStatus INTEGER
+      , conversationId STRING);
+CREATE TABLE mentions (
+        messageId REFERENCES messages(id) ON DELETE CASCADE,
+        mentionAci STRING,
+        start INTEGER,
+        length INTEGER
+      );
+CREATE TABLE kyberPreKeys(
+        id STRING PRIMARY KEY NOT NULL,
+        json TEXT NOT NULL, ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE callsHistory (
+        callId TEXT PRIMARY KEY,
+        peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId
+        ringerId TEXT DEFAULT NULL, -- ringer uuid
+        mode TEXT NOT NULL, -- enum "Direct" | "Group"
+        type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group"
+        direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing
+        -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted"
+        -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted"
+        status TEXT NOT NULL,
+        timestamp INTEGER NOT NULL,
+        UNIQUE (callId, peerId) ON CONFLICT FAIL
+      );
+[ dropped all indexes to save space in this blog post ]
+CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages
+      WHEN
+        new.body IS NOT NULL AND new.isViewOnce = 1
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+      END;
+CREATE TRIGGER messages_on_insert AFTER INSERT ON messages
+      WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        DELETE FROM sendLogPayloads WHERE id IN (
+          SELECT payloadId FROM sendLogMessageIds
+          WHERE messageId = old.id
+        );
+        DELETE FROM reactions WHERE rowid IN (
+          SELECT rowid FROM reactions
+          WHERE messageId = old.id
+        );
+        DELETE FROM storyReads WHERE storyId = old.storyId;
+      END;
+CREATE VIRTUAL TABLE messages_fts USING fts5(
+        body,
+        tokenize = 'signal_tokenizer'
+      );
+CREATE TRIGGER messages_on_update AFTER UPDATE ON messages
+      WHEN
+        (new.body IS NULL OR old.body IS NOT new.body) AND
+         new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages
+      BEGIN
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages
+      BEGIN
+        DELETE FROM mentions WHERE messageId = new.id;
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+sqlite>
+

+ +

Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format.

+ +

As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b.

+ +

+ + + Tags: debian, english, sikkerhet, surveillance. + + +

Latest Jami back in Debian Testing, and scriptable using dbus @@ -4860,7 +5346,7 @@ betydelige.

October (1)

November (2)

November (3)

@@ -5276,7 +5762,7 @@ betydelige.

@@ -5290,7 +5776,7 @@ betydelige.

@@ -5364,7 +5850,7 @@ betydelige.

@@ -5376,7 +5862,7 @@ betydelige.

diff --git a/blog/tags/sikkerhet/sikkerhet.rss b/blog/tags/sikkerhet/sikkerhet.rss index c785186f48..b59e7bb195 100644 --- a/blog/tags/sikkerhet/sikkerhet.rss +++ b/blog/tags/sikkerhet/sikkerhet.rss @@ -6,6 +6,480 @@ https://people.skolelinux.org/pere/blog/ + + New and improved sqlcipher in Debian for accessing Signal database + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + Sun, 12 Nov 2023 12:00:00 +0100 + For a while now I wanted to have direct access to the +<a href="https://signal.org/">Signal</a> database of messages and +channels of my Desktop edition of Signal. I prefer the enforced end +to end encryption of Signal these days for my communication with +friends and family, to increase the level of safety and privacy as +well as raising the cost of the mass surveillance government and +non-government entities practice these days. In August I came across +a nice +<a href="https://www.yoranbrondsema.com/post/the-guide-to-extracting-statistics-from-your-signal-conversations/">recipe +on how to use sqlcipher to extract statistics from the Signal +database</a> explaining how to do this. Unfortunately this did not +work with the version of sqlcipher in Debian. The +<a href="http://tracker.debian.org/sqlcipher/">sqlcipher</a> +package is a "fork" of the sqlite package with added support for +encrypted databases. Sadly the current Debian maintainer +<a href="https://bugs.debian.org/961598">announced more than three +years ago that he did not have time to maintain sqlcipher</a>, so it +seemed unlikely to be upgraded by the maintainer. I was reluctant to +take on the job myself, as I have very limited experience maintaining +shared libraries in Debian. After waiting and hoping for a few +months, I gave up the last week, and set out to update the package. In +the process I orphaned it to make it more obvious for the next person +looking at it that the package need proper maintenance. + +The version in Debian was around five years old, and quite a lot of +changes had taken place upstream into the Debian maintenance git +repository. After spending a few days importing the new upstream +versions, realising that upstream did not care much for SONAME +versioning as I saw library symbols being both added and removed with +minor version number changes to the project, I concluded that I had to +do a SONAME bump of the library package to avoid surprising the +reverse dependencies. I even added a simple +autopkgtest script to ensure the package work as intended. Dug deep +into the hole of learning shared library maintenance, I set out a few +days ago to upload the new version to Debian experimental to see what +the quality assurance framework in Debian had to say about the result. +The feedback told me the pacakge was not too shabby, and yesterday I +uploaded the latest version to Debian unstable. It should enter +testing today or tomorrow, perhaps delayed by +<a href="https://bugs.debian.org/1055812">a small library +transition</a>. + +Armed with a new version of sqlcipher, I can now have a look at the +SQL database in ~/.config/Signal/sql/db.sqlite. First, one need to +fetch the encryption key from the Signal configuration using this +simple JSON extraction command: + +<pre>/usr/bin/jq -r '."key"' ~/.config/Signal/config.json</pre> + +Assuming the result from that command is 'secretkey', which is a +hexadecimal number representing the key used to encrypt the database. +Next, one can now connect to the database and inject the encryption +key for access via SQL to fetch information from the database. Here +is an example dumping the database structure: + +<pre> +% sqlcipher ~/.config/Signal/sql/db.sqlite +sqlite> PRAGMA key = "x'secretkey'"; +sqlite> .schema +CREATE TABLE sqlite_stat1(tbl,idx,stat); +CREATE TABLE conversations( + id STRING PRIMARY KEY ASC, + json TEXT, + + active_at INTEGER, + type STRING, + members TEXT, + name TEXT, + profileName TEXT + , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER); +CREATE TABLE identityKeys( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE items( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE sessions( + id TEXT PRIMARY KEY, + conversationId TEXT, + json TEXT + , ourServiceId STRING, serviceId STRING); +CREATE TABLE attachment_downloads( + id STRING primary key, + timestamp INTEGER, + pending INTEGER, + json TEXT + ); +CREATE TABLE sticker_packs( + id TEXT PRIMARY KEY, + key TEXT NOT NULL, + + author STRING, + coverStickerId INTEGER, + createdAt INTEGER, + downloadAttempts INTEGER, + installedAt INTEGER, + lastUsed INTEGER, + status STRING, + stickerCount INTEGER, + title STRING + , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync + INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE stickers( + id INTEGER NOT NULL, + packId TEXT NOT NULL, + + emoji STRING, + height INTEGER, + isCoverOnly INTEGER, + lastUsed INTEGER, + path STRING, + width INTEGER, + + PRIMARY KEY (id, packId), + CONSTRAINT stickers_fk + FOREIGN KEY (packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE sticker_references( + messageId STRING, + packId TEXT, + CONSTRAINT sticker_references_fk + FOREIGN KEY(packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE emojis( + shortName TEXT PRIMARY KEY, + lastUsage INTEGER + ); +CREATE TABLE messages( + rowid INTEGER PRIMARY KEY ASC, + id STRING UNIQUE, + json TEXT, + readStatus INTEGER, + expires_at INTEGER, + sent_at INTEGER, + schemaVersion INTEGER, + conversationId STRING, + received_at INTEGER, + source STRING, + hasAttachments INTEGER, + hasFileAttachments INTEGER, + hasVisualMediaAttachments INTEGER, + expireTimer INTEGER, + expirationStartTimestamp INTEGER, + type STRING, + body TEXT, + messageTimer INTEGER, + messageTimerStart INTEGER, + messageTimerExpiresAt INTEGER, + isErased INTEGER, + isViewOnce INTEGER, + sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER + GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER + GENERATED ALWAYS AS ( + json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1 + ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT + GENERATED ALWAYS + AS (ifnull( + expirationStartTimestamp + (expireTimer * 1000), + 9007199254740991 + )), shouldAffectActivity INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), shouldAffectPreview INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), isUserInitiatedMessage INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'group-v2-change', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER + GENERATED ALWAYS AS ( + type IS 'group-v2-change' AND + json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND + json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND + json_extract(json, '$.groupV2Change.from') IS NOT NULL AND + json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci') + ), isGroupLeaveEventFromOther INTEGER + GENERATED ALWAYS AS ( + isGroupLeaveEvent IS 1 + AND + isChangeCreatedByUs IS 0 + ), callId TEXT + GENERATED ALWAYS AS ( + json_extract(json, '$.callId') + )); +CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); +CREATE TABLE jobs( + id TEXT PRIMARY KEY, + queueType TEXT STRING NOT NULL, + timestamp INTEGER NOT NULL, + data STRING TEXT + ); +CREATE TABLE reactions( + conversationId STRING, + emoji STRING, + fromId STRING, + messageReceivedAt INTEGER, + targetAuthorAci STRING, + targetTimestamp INTEGER, + unread INTEGER + , messageId STRING); +CREATE TABLE senderKeys( + id TEXT PRIMARY KEY NOT NULL, + senderId TEXT NOT NULL, + distributionId TEXT NOT NULL, + data BLOB NOT NULL, + lastUpdatedDate NUMBER NOT NULL + ); +CREATE TABLE unprocessed( + id STRING PRIMARY KEY ASC, + timestamp INTEGER, + version INTEGER, + attempts INTEGER, + envelope TEXT, + decrypted TEXT, + source TEXT, + serverTimestamp INTEGER, + sourceServiceId STRING + , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER); +CREATE TABLE sendLogPayloads( + id INTEGER PRIMARY KEY ASC, + + timestamp INTEGER NOT NULL, + contentHint INTEGER NOT NULL, + proto BLOB NOT NULL + , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE sendLogRecipients( + payloadId INTEGER NOT NULL, + + recipientServiceId STRING NOT NULL, + deviceId INTEGER NOT NULL, + + PRIMARY KEY (payloadId, recipientServiceId, deviceId), + + CONSTRAINT sendLogRecipientsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE sendLogMessageIds( + payloadId INTEGER NOT NULL, + + messageId STRING NOT NULL, + + PRIMARY KEY (payloadId, messageId), + + CONSTRAINT sendLogMessageIdsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE preKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE signedPreKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE badges( + id TEXT PRIMARY KEY, + category TEXT NOT NULL, + name TEXT NOT NULL, + descriptionTemplate TEXT NOT NULL + ); +CREATE TABLE badgeImageFiles( + badgeId TEXT REFERENCES badges(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + 'order' INTEGER NOT NULL, + url TEXT NOT NULL, + localPath TEXT, + theme TEXT NOT NULL + ); +CREATE TABLE storyReads ( + authorId STRING NOT NULL, + conversationId STRING NOT NULL, + storyId STRING NOT NULL, + storyReadDate NUMBER NOT NULL, + + PRIMARY KEY (authorId, storyId) + ); +CREATE TABLE storyDistributions( + id STRING PRIMARY KEY NOT NULL, + name TEXT, + + senderKeyInfoJson STRING + , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER); +CREATE TABLE storyDistributionMembers( + listId STRING NOT NULL REFERENCES storyDistributions(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + serviceId STRING NOT NULL, + + PRIMARY KEY (listId, serviceId) + ); +CREATE TABLE uninstalled_sticker_packs ( + id STRING NOT NULL PRIMARY KEY, + uninstalledAt NUMBER NOT NULL, + storageID STRING, + storageVersion NUMBER, + storageUnknownFields BLOB, + storageNeedsSync INTEGER NOT NULL + ); +CREATE TABLE groupCallRingCancellations( + ringId INTEGER PRIMARY KEY, + createdAt INTEGER NOT NULL + ); +CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID; +CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0); +CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID; +CREATE TABLE edited_messages( + messageId STRING REFERENCES messages(id) + ON DELETE CASCADE, + sentAt INTEGER, + readStatus INTEGER + , conversationId STRING); +CREATE TABLE mentions ( + messageId REFERENCES messages(id) ON DELETE CASCADE, + mentionAci STRING, + start INTEGER, + length INTEGER + ); +CREATE TABLE kyberPreKeys( + id STRING PRIMARY KEY NOT NULL, + json TEXT NOT NULL, ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE callsHistory ( + callId TEXT PRIMARY KEY, + peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId + ringerId TEXT DEFAULT NULL, -- ringer uuid + mode TEXT NOT NULL, -- enum "Direct" | "Group" + type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group" + direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing + -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted" + -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted" + status TEXT NOT NULL, + timestamp INTEGER NOT NULL, + UNIQUE (callId, peerId) ON CONFLICT FAIL + ); +[ dropped all indexes to save space in this blog post ] +CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages + WHEN + new.body IS NOT NULL AND new.isViewOnce = 1 + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + END; +CREATE TRIGGER messages_on_insert AFTER INSERT ON messages + WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + DELETE FROM sendLogPayloads WHERE id IN ( + SELECT payloadId FROM sendLogMessageIds + WHERE messageId = old.id + ); + DELETE FROM reactions WHERE rowid IN ( + SELECT rowid FROM reactions + WHERE messageId = old.id + ); + DELETE FROM storyReads WHERE storyId = old.storyId; + END; +CREATE VIRTUAL TABLE messages_fts USING fts5( + body, + tokenize = 'signal_tokenizer' + ); +CREATE TRIGGER messages_on_update AFTER UPDATE ON messages + WHEN + (new.body IS NULL OR old.body IS NOT new.body) AND + new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages + BEGIN + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages + BEGIN + DELETE FROM mentions WHERE messageId = new.id; + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +sqlite> +</pre> + +Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format. + +As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +<a href="bitcoin:15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b">15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b</a>. + + + Latest Jami back in Debian Testing, and scriptable using dbus https://people.skolelinux.org/pere/blog/Latest_Jami_back_in_Debian_Testing__and_scriptable_using_dbus.html diff --git a/blog/tags/sitesummary/index.html b/blog/tags/sitesummary/index.html index a2fdd5034d..bd10bf1d6b 100644 --- a/blog/tags/sitesummary/index.html +++ b/blog/tags/sitesummary/index.html @@ -275,7 +275,7 @@ everything is taken care of.

October (1)

November (2)

November (3)

@@ -691,7 +691,7 @@ everything is taken care of.

@@ -705,7 +705,7 @@ everything is taken care of.

@@ -779,7 +779,7 @@ everything is taken care of.

@@ -791,7 +791,7 @@ everything is taken care of.

diff --git a/blog/tags/skepsis/index.html b/blog/tags/skepsis/index.html index 340476598b..40e29f7692 100644 --- a/blog/tags/skepsis/index.html +++ b/blog/tags/skepsis/index.html @@ -455,7 +455,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

October (1)

November (2)

November (3)

@@ -871,7 +871,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

@@ -885,7 +885,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

@@ -959,7 +959,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

@@ -971,7 +971,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

diff --git a/blog/tags/standard/index.html b/blog/tags/standard/index.html index 250973c49a..36a497aa40 100644 --- a/blog/tags/standard/index.html +++ b/blog/tags/standard/index.html @@ -6827,7 +6827,7 @@ Kjenner kun til ufullstendige lÃ¸sninger for slikt.

October (1)

November (2)

November (3)

@@ -7243,7 +7243,7 @@ Kjenner kun til ufullstendige lÃ¸sninger for slikt.

@@ -7257,7 +7257,7 @@ Kjenner kun til ufullstendige lÃ¸sninger for slikt.

@@ -7331,7 +7331,7 @@ Kjenner kun til ufullstendige lÃ¸sninger for slikt.

@@ -7343,7 +7343,7 @@ Kjenner kun til ufullstendige lÃ¸sninger for slikt.

diff --git a/blog/tags/stavekontroll/index.html b/blog/tags/stavekontroll/index.html index 5238aa5010..8ddea62563 100644 --- a/blog/tags/stavekontroll/index.html +++ b/blog/tags/stavekontroll/index.html @@ -498,7 +498,7 @@ stavekontrollen.

October (1)

November (2)

November (3)

@@ -914,7 +914,7 @@ stavekontrollen.

@@ -928,7 +928,7 @@ stavekontrollen.

@@ -1002,7 +1002,7 @@ stavekontrollen.

@@ -1014,7 +1014,7 @@ stavekontrollen.

diff --git a/blog/tags/stortinget/index.html b/blog/tags/stortinget/index.html index 62e1d5da7e..c13da0198a 100644 --- a/blog/tags/stortinget/index.html +++ b/blog/tags/stortinget/index.html @@ -1458,7 +1458,7 @@ at vi i NUUG har fÃ¥tt operativ en norsk utgave av

October (1)

November (2)

November (3)

@@ -1874,7 +1874,7 @@ at vi i NUUG har fÃ¥tt operativ en norsk utgave av

@@ -1888,7 +1888,7 @@ at vi i NUUG har fÃ¥tt operativ en norsk utgave av

@@ -1962,7 +1962,7 @@ at vi i NUUG har fÃ¥tt operativ en norsk utgave av

@@ -1974,7 +1974,7 @@ at vi i NUUG har fÃ¥tt operativ en norsk utgave av

diff --git a/blog/tags/surveillance/index.html b/blog/tags/surveillance/index.html index 32bb9d5c86..7324ed03c7 100644 --- a/blog/tags/surveillance/index.html +++ b/blog/tags/surveillance/index.html @@ -20,6 +20,492 @@

Entries tagged "surveillance".

+ New and improved sqlcipher in Debian for accessing Signal database +

+ 12th November 2023 +

+ +

/usr/bin/jq -r '."key"' ~/.config/Signal/config.json

+ +

+% sqlcipher ~/.config/Signal/sql/db.sqlite
+sqlite> PRAGMA key = "x'secretkey'";
+sqlite> .schema
+CREATE TABLE sqlite_stat1(tbl,idx,stat);
+CREATE TABLE conversations(
+      id STRING PRIMARY KEY ASC,
+      json TEXT,
+
+      active_at INTEGER,
+      type STRING,
+      members TEXT,
+      name TEXT,
+      profileName TEXT
+    , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER);
+CREATE TABLE identityKeys(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE items(
+      id STRING PRIMARY KEY ASC,
+      json TEXT
+    );
+CREATE TABLE sessions(
+      id TEXT PRIMARY KEY,
+      conversationId TEXT,
+      json TEXT
+    , ourServiceId STRING, serviceId STRING);
+CREATE TABLE attachment_downloads(
+    id STRING primary key,
+    timestamp INTEGER,
+    pending INTEGER,
+    json TEXT
+  );
+CREATE TABLE sticker_packs(
+    id TEXT PRIMARY KEY,
+    key TEXT NOT NULL,
+
+    author STRING,
+    coverStickerId INTEGER,
+    createdAt INTEGER,
+    downloadAttempts INTEGER,
+    installedAt INTEGER,
+    lastUsed INTEGER,
+    status STRING,
+    stickerCount INTEGER,
+    title STRING
+  , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync
+      INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE stickers(
+    id INTEGER NOT NULL,
+    packId TEXT NOT NULL,
+
+    emoji STRING,
+    height INTEGER,
+    isCoverOnly INTEGER,
+    lastUsed INTEGER,
+    path STRING,
+    width INTEGER,
+
+    PRIMARY KEY (id, packId),
+    CONSTRAINT stickers_fk
+      FOREIGN KEY (packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE sticker_references(
+    messageId STRING,
+    packId TEXT,
+    CONSTRAINT sticker_references_fk
+      FOREIGN KEY(packId)
+      REFERENCES sticker_packs(id)
+      ON DELETE CASCADE
+  );
+CREATE TABLE emojis(
+    shortName TEXT PRIMARY KEY,
+    lastUsage INTEGER
+  );
+CREATE TABLE messages(
+        rowid INTEGER PRIMARY KEY ASC,
+        id STRING UNIQUE,
+        json TEXT,
+        readStatus INTEGER,
+        expires_at INTEGER,
+        sent_at INTEGER,
+        schemaVersion INTEGER,
+        conversationId STRING,
+        received_at INTEGER,
+        source STRING,
+        hasAttachments INTEGER,
+        hasFileAttachments INTEGER,
+        hasVisualMediaAttachments INTEGER,
+        expireTimer INTEGER,
+        expirationStartTimestamp INTEGER,
+        type STRING,
+        body TEXT,
+        messageTimer INTEGER,
+        messageTimerStart INTEGER,
+        messageTimerExpiresAt INTEGER,
+        isErased INTEGER,
+        isViewOnce INTEGER,
+        sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER
+        GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1
+        ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT
+        GENERATED ALWAYS
+        AS (ifnull(
+          expirationStartTimestamp + (expireTimer * 1000),
+          9007199254740991
+        )), shouldAffectActivity INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), shouldAffectPreview INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), isUserInitiatedMessage INTEGER
+        GENERATED ALWAYS AS (
+          type IS NULL
+          OR
+          type NOT IN (
+            'change-number-notification',
+            'contact-removed-notification',
+            'conversation-merge',
+            'group-v1-migration',
+            'group-v2-change',
+            'keychange',
+            'message-history-unsynced',
+            'profile-change',
+            'story',
+            'universal-timer-notification',
+            'verified-change'
+          )
+        ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER
+        GENERATED ALWAYS AS (
+          type IS 'group-v2-change' AND
+          json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND
+          json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND
+          json_extract(json, '$.groupV2Change.from') IS NOT NULL AND
+          json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci')
+        ), isGroupLeaveEventFromOther INTEGER
+        GENERATED ALWAYS AS (
+          isGroupLeaveEvent IS 1
+          AND
+          isChangeCreatedByUs IS 0
+        ), callId TEXT
+        GENERATED ALWAYS AS (
+          json_extract(json, '$.callId')
+        ));
+CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
+CREATE TABLE jobs(
+        id TEXT PRIMARY KEY,
+        queueType TEXT STRING NOT NULL,
+        timestamp INTEGER NOT NULL,
+        data STRING TEXT
+      );
+CREATE TABLE reactions(
+        conversationId STRING,
+        emoji STRING,
+        fromId STRING,
+        messageReceivedAt INTEGER,
+        targetAuthorAci STRING,
+        targetTimestamp INTEGER,
+        unread INTEGER
+      , messageId STRING);
+CREATE TABLE senderKeys(
+        id TEXT PRIMARY KEY NOT NULL,
+        senderId TEXT NOT NULL,
+        distributionId TEXT NOT NULL,
+        data BLOB NOT NULL,
+        lastUpdatedDate NUMBER NOT NULL
+      );
+CREATE TABLE unprocessed(
+        id STRING PRIMARY KEY ASC,
+        timestamp INTEGER,
+        version INTEGER,
+        attempts INTEGER,
+        envelope TEXT,
+        decrypted TEXT,
+        source TEXT,
+        serverTimestamp INTEGER,
+        sourceServiceId STRING
+      , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER);
+CREATE TABLE sendLogPayloads(
+        id INTEGER PRIMARY KEY ASC,
+
+        timestamp INTEGER NOT NULL,
+        contentHint INTEGER NOT NULL,
+        proto BLOB NOT NULL
+      , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL);
+CREATE TABLE sendLogRecipients(
+        payloadId INTEGER NOT NULL,
+
+        recipientServiceId STRING NOT NULL,
+        deviceId INTEGER NOT NULL,
+
+        PRIMARY KEY (payloadId, recipientServiceId, deviceId),
+
+        CONSTRAINT sendLogRecipientsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE sendLogMessageIds(
+        payloadId INTEGER NOT NULL,
+
+        messageId STRING NOT NULL,
+
+        PRIMARY KEY (payloadId, messageId),
+
+        CONSTRAINT sendLogMessageIdsForeignKey
+          FOREIGN KEY (payloadId)
+          REFERENCES sendLogPayloads(id)
+          ON DELETE CASCADE
+      );
+CREATE TABLE preKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE signedPreKeys(
+        id STRING PRIMARY KEY ASC,
+        json TEXT
+      , ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE badges(
+        id TEXT PRIMARY KEY,
+        category TEXT NOT NULL,
+        name TEXT NOT NULL,
+        descriptionTemplate TEXT NOT NULL
+      );
+CREATE TABLE badgeImageFiles(
+        badgeId TEXT REFERENCES badges(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        'order' INTEGER NOT NULL,
+        url TEXT NOT NULL,
+        localPath TEXT,
+        theme TEXT NOT NULL
+      );
+CREATE TABLE storyReads (
+        authorId STRING NOT NULL,
+        conversationId STRING NOT NULL,
+        storyId STRING NOT NULL,
+        storyReadDate NUMBER NOT NULL,
+
+        PRIMARY KEY (authorId, storyId)
+      );
+CREATE TABLE storyDistributions(
+        id STRING PRIMARY KEY NOT NULL,
+        name TEXT,
+
+        senderKeyInfoJson STRING
+      , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER);
+CREATE TABLE storyDistributionMembers(
+        listId STRING NOT NULL REFERENCES storyDistributions(id)
+          ON DELETE CASCADE
+          ON UPDATE CASCADE,
+        serviceId STRING NOT NULL,
+
+        PRIMARY KEY (listId, serviceId)
+      );
+CREATE TABLE uninstalled_sticker_packs (
+        id STRING NOT NULL PRIMARY KEY,
+        uninstalledAt NUMBER NOT NULL,
+        storageID STRING,
+        storageVersion NUMBER,
+        storageUnknownFields BLOB,
+        storageNeedsSync INTEGER NOT NULL
+      );
+CREATE TABLE groupCallRingCancellations(
+        ringId INTEGER PRIMARY KEY,
+        createdAt INTEGER NOT NULL
+      );
+CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
+CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0);
+CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
+CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
+CREATE TABLE edited_messages(
+        messageId STRING REFERENCES messages(id)
+          ON DELETE CASCADE,
+        sentAt INTEGER,
+        readStatus INTEGER
+      , conversationId STRING);
+CREATE TABLE mentions (
+        messageId REFERENCES messages(id) ON DELETE CASCADE,
+        mentionAci STRING,
+        start INTEGER,
+        length INTEGER
+      );
+CREATE TABLE kyberPreKeys(
+        id STRING PRIMARY KEY NOT NULL,
+        json TEXT NOT NULL, ourServiceId NUMBER
+        GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
+CREATE TABLE callsHistory (
+        callId TEXT PRIMARY KEY,
+        peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId
+        ringerId TEXT DEFAULT NULL, -- ringer uuid
+        mode TEXT NOT NULL, -- enum "Direct" | "Group"
+        type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group"
+        direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing
+        -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted"
+        -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted"
+        status TEXT NOT NULL,
+        timestamp INTEGER NOT NULL,
+        UNIQUE (callId, peerId) ON CONFLICT FAIL
+      );
+[ dropped all indexes to save space in this blog post ]
+CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages
+      WHEN
+        new.body IS NOT NULL AND new.isViewOnce = 1
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+      END;
+CREATE TRIGGER messages_on_insert AFTER INSERT ON messages
+      WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        DELETE FROM sendLogPayloads WHERE id IN (
+          SELECT payloadId FROM sendLogMessageIds
+          WHERE messageId = old.id
+        );
+        DELETE FROM reactions WHERE rowid IN (
+          SELECT rowid FROM reactions
+          WHERE messageId = old.id
+        );
+        DELETE FROM storyReads WHERE storyId = old.storyId;
+      END;
+CREATE VIRTUAL TABLE messages_fts USING fts5(
+        body,
+        tokenize = 'signal_tokenizer'
+      );
+CREATE TRIGGER messages_on_update AFTER UPDATE ON messages
+      WHEN
+        (new.body IS NULL OR old.body IS NOT new.body) AND
+         new.isViewOnce IS NOT 1 AND new.storyId IS NULL
+      BEGIN
+        DELETE FROM messages_fts WHERE rowid = old.rowid;
+        INSERT INTO messages_fts
+          (rowid, body)
+        VALUES
+          (new.rowid, new.body);
+      END;
+CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages
+      BEGIN
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages
+      BEGIN
+        DELETE FROM mentions WHERE messageId = new.id;
+        INSERT INTO mentions (messageId, mentionAci, start, length)
+        
+    SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
+      bodyRanges.value ->> 'start' as start,
+      bodyRanges.value ->> 'length' as length
+    FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
+    WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
+  
+        AND messages.id = new.id;
+      END;
+sqlite>
+

+ +

Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format.

+ +

As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b.

+ +

+ + + Tags: debian, english, sikkerhet, surveillance. + + +

ONVIF IP camera management tool finally in Debian @@ -5755,7 +6241,7 @@ automatisk over i spesialkartet.

October (1)

November (2)

November (3)

@@ -6171,7 +6657,7 @@ automatisk over i spesialkartet.

@@ -6185,7 +6671,7 @@ automatisk over i spesialkartet.

@@ -6259,7 +6745,7 @@ automatisk over i spesialkartet.

@@ -6271,7 +6757,7 @@ automatisk over i spesialkartet.

diff --git a/blog/tags/surveillance/surveillance.rss b/blog/tags/surveillance/surveillance.rss index 691ec6b609..b44889a3be 100644 --- a/blog/tags/surveillance/surveillance.rss +++ b/blog/tags/surveillance/surveillance.rss @@ -6,6 +6,480 @@ https://people.skolelinux.org/pere/blog/ + + New and improved sqlcipher in Debian for accessing Signal database + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + https://people.skolelinux.org/pere/blog/New_and_improved_sqlcipher_in_Debian_for_accessing_Signal_database.html + Sun, 12 Nov 2023 12:00:00 +0100 + For a while now I wanted to have direct access to the +<a href="https://signal.org/">Signal</a> database of messages and +channels of my Desktop edition of Signal. I prefer the enforced end +to end encryption of Signal these days for my communication with +friends and family, to increase the level of safety and privacy as +well as raising the cost of the mass surveillance government and +non-government entities practice these days. In August I came across +a nice +<a href="https://www.yoranbrondsema.com/post/the-guide-to-extracting-statistics-from-your-signal-conversations/">recipe +on how to use sqlcipher to extract statistics from the Signal +database</a> explaining how to do this. Unfortunately this did not +work with the version of sqlcipher in Debian. The +<a href="http://tracker.debian.org/sqlcipher/">sqlcipher</a> +package is a "fork" of the sqlite package with added support for +encrypted databases. Sadly the current Debian maintainer +<a href="https://bugs.debian.org/961598">announced more than three +years ago that he did not have time to maintain sqlcipher</a>, so it +seemed unlikely to be upgraded by the maintainer. I was reluctant to +take on the job myself, as I have very limited experience maintaining +shared libraries in Debian. After waiting and hoping for a few +months, I gave up the last week, and set out to update the package. In +the process I orphaned it to make it more obvious for the next person +looking at it that the package need proper maintenance. + +The version in Debian was around five years old, and quite a lot of +changes had taken place upstream into the Debian maintenance git +repository. After spending a few days importing the new upstream +versions, realising that upstream did not care much for SONAME +versioning as I saw library symbols being both added and removed with +minor version number changes to the project, I concluded that I had to +do a SONAME bump of the library package to avoid surprising the +reverse dependencies. I even added a simple +autopkgtest script to ensure the package work as intended. Dug deep +into the hole of learning shared library maintenance, I set out a few +days ago to upload the new version to Debian experimental to see what +the quality assurance framework in Debian had to say about the result. +The feedback told me the pacakge was not too shabby, and yesterday I +uploaded the latest version to Debian unstable. It should enter +testing today or tomorrow, perhaps delayed by +<a href="https://bugs.debian.org/1055812">a small library +transition</a>. + +Armed with a new version of sqlcipher, I can now have a look at the +SQL database in ~/.config/Signal/sql/db.sqlite. First, one need to +fetch the encryption key from the Signal configuration using this +simple JSON extraction command: + +<pre>/usr/bin/jq -r '."key"' ~/.config/Signal/config.json</pre> + +Assuming the result from that command is 'secretkey', which is a +hexadecimal number representing the key used to encrypt the database. +Next, one can now connect to the database and inject the encryption +key for access via SQL to fetch information from the database. Here +is an example dumping the database structure: + +<pre> +% sqlcipher ~/.config/Signal/sql/db.sqlite +sqlite> PRAGMA key = "x'secretkey'"; +sqlite> .schema +CREATE TABLE sqlite_stat1(tbl,idx,stat); +CREATE TABLE conversations( + id STRING PRIMARY KEY ASC, + json TEXT, + + active_at INTEGER, + type STRING, + members TEXT, + name TEXT, + profileName TEXT + , profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER); +CREATE TABLE identityKeys( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE items( + id STRING PRIMARY KEY ASC, + json TEXT + ); +CREATE TABLE sessions( + id TEXT PRIMARY KEY, + conversationId TEXT, + json TEXT + , ourServiceId STRING, serviceId STRING); +CREATE TABLE attachment_downloads( + id STRING primary key, + timestamp INTEGER, + pending INTEGER, + json TEXT + ); +CREATE TABLE sticker_packs( + id TEXT PRIMARY KEY, + key TEXT NOT NULL, + + author STRING, + coverStickerId INTEGER, + createdAt INTEGER, + downloadAttempts INTEGER, + installedAt INTEGER, + lastUsed INTEGER, + status STRING, + stickerCount INTEGER, + title STRING + , attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync + INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE stickers( + id INTEGER NOT NULL, + packId TEXT NOT NULL, + + emoji STRING, + height INTEGER, + isCoverOnly INTEGER, + lastUsed INTEGER, + path STRING, + width INTEGER, + + PRIMARY KEY (id, packId), + CONSTRAINT stickers_fk + FOREIGN KEY (packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE sticker_references( + messageId STRING, + packId TEXT, + CONSTRAINT sticker_references_fk + FOREIGN KEY(packId) + REFERENCES sticker_packs(id) + ON DELETE CASCADE + ); +CREATE TABLE emojis( + shortName TEXT PRIMARY KEY, + lastUsage INTEGER + ); +CREATE TABLE messages( + rowid INTEGER PRIMARY KEY ASC, + id STRING UNIQUE, + json TEXT, + readStatus INTEGER, + expires_at INTEGER, + sent_at INTEGER, + schemaVersion INTEGER, + conversationId STRING, + received_at INTEGER, + source STRING, + hasAttachments INTEGER, + hasFileAttachments INTEGER, + hasVisualMediaAttachments INTEGER, + expireTimer INTEGER, + expirationStartTimestamp INTEGER, + type STRING, + body TEXT, + messageTimer INTEGER, + messageTimerStart INTEGER, + messageTimerExpiresAt INTEGER, + isErased INTEGER, + isViewOnce INTEGER, + sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER + GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER + GENERATED ALWAYS AS ( + json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1 + ), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT + GENERATED ALWAYS + AS (ifnull( + expirationStartTimestamp + (expireTimer * 1000), + 9007199254740991 + )), shouldAffectActivity INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), shouldAffectPreview INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), isUserInitiatedMessage INTEGER + GENERATED ALWAYS AS ( + type IS NULL + OR + type NOT IN ( + 'change-number-notification', + 'contact-removed-notification', + 'conversation-merge', + 'group-v1-migration', + 'group-v2-change', + 'keychange', + 'message-history-unsynced', + 'profile-change', + 'story', + 'universal-timer-notification', + 'verified-change' + ) + ), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER + GENERATED ALWAYS AS ( + type IS 'group-v2-change' AND + json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND + json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND + json_extract(json, '$.groupV2Change.from') IS NOT NULL AND + json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci') + ), isGroupLeaveEventFromOther INTEGER + GENERATED ALWAYS AS ( + isGroupLeaveEvent IS 1 + AND + isChangeCreatedByUs IS 0 + ), callId TEXT + GENERATED ALWAYS AS ( + json_extract(json, '$.callId') + )); +CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); +CREATE TABLE jobs( + id TEXT PRIMARY KEY, + queueType TEXT STRING NOT NULL, + timestamp INTEGER NOT NULL, + data STRING TEXT + ); +CREATE TABLE reactions( + conversationId STRING, + emoji STRING, + fromId STRING, + messageReceivedAt INTEGER, + targetAuthorAci STRING, + targetTimestamp INTEGER, + unread INTEGER + , messageId STRING); +CREATE TABLE senderKeys( + id TEXT PRIMARY KEY NOT NULL, + senderId TEXT NOT NULL, + distributionId TEXT NOT NULL, + data BLOB NOT NULL, + lastUpdatedDate NUMBER NOT NULL + ); +CREATE TABLE unprocessed( + id STRING PRIMARY KEY ASC, + timestamp INTEGER, + version INTEGER, + attempts INTEGER, + envelope TEXT, + decrypted TEXT, + source TEXT, + serverTimestamp INTEGER, + sourceServiceId STRING + , serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER); +CREATE TABLE sendLogPayloads( + id INTEGER PRIMARY KEY ASC, + + timestamp INTEGER NOT NULL, + contentHint INTEGER NOT NULL, + proto BLOB NOT NULL + , urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL); +CREATE TABLE sendLogRecipients( + payloadId INTEGER NOT NULL, + + recipientServiceId STRING NOT NULL, + deviceId INTEGER NOT NULL, + + PRIMARY KEY (payloadId, recipientServiceId, deviceId), + + CONSTRAINT sendLogRecipientsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE sendLogMessageIds( + payloadId INTEGER NOT NULL, + + messageId STRING NOT NULL, + + PRIMARY KEY (payloadId, messageId), + + CONSTRAINT sendLogMessageIdsForeignKey + FOREIGN KEY (payloadId) + REFERENCES sendLogPayloads(id) + ON DELETE CASCADE + ); +CREATE TABLE preKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE signedPreKeys( + id STRING PRIMARY KEY ASC, + json TEXT + , ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE badges( + id TEXT PRIMARY KEY, + category TEXT NOT NULL, + name TEXT NOT NULL, + descriptionTemplate TEXT NOT NULL + ); +CREATE TABLE badgeImageFiles( + badgeId TEXT REFERENCES badges(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + 'order' INTEGER NOT NULL, + url TEXT NOT NULL, + localPath TEXT, + theme TEXT NOT NULL + ); +CREATE TABLE storyReads ( + authorId STRING NOT NULL, + conversationId STRING NOT NULL, + storyId STRING NOT NULL, + storyReadDate NUMBER NOT NULL, + + PRIMARY KEY (authorId, storyId) + ); +CREATE TABLE storyDistributions( + id STRING PRIMARY KEY NOT NULL, + name TEXT, + + senderKeyInfoJson STRING + , deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER); +CREATE TABLE storyDistributionMembers( + listId STRING NOT NULL REFERENCES storyDistributions(id) + ON DELETE CASCADE + ON UPDATE CASCADE, + serviceId STRING NOT NULL, + + PRIMARY KEY (listId, serviceId) + ); +CREATE TABLE uninstalled_sticker_packs ( + id STRING NOT NULL PRIMARY KEY, + uninstalledAt NUMBER NOT NULL, + storageID STRING, + storageVersion NUMBER, + storageUnknownFields BLOB, + storageNeedsSync INTEGER NOT NULL + ); +CREATE TABLE groupCallRingCancellations( + ringId INTEGER PRIMARY KEY, + createdAt INTEGER NOT NULL + ); +CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID; +CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0); +CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB); +CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID; +CREATE TABLE edited_messages( + messageId STRING REFERENCES messages(id) + ON DELETE CASCADE, + sentAt INTEGER, + readStatus INTEGER + , conversationId STRING); +CREATE TABLE mentions ( + messageId REFERENCES messages(id) ON DELETE CASCADE, + mentionAci STRING, + start INTEGER, + length INTEGER + ); +CREATE TABLE kyberPreKeys( + id STRING PRIMARY KEY NOT NULL, + json TEXT NOT NULL, ourServiceId NUMBER + GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId'))); +CREATE TABLE callsHistory ( + callId TEXT PRIMARY KEY, + peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId + ringerId TEXT DEFAULT NULL, -- ringer uuid + mode TEXT NOT NULL, -- enum "Direct" | "Group" + type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group" + direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing + -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted" + -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted" + status TEXT NOT NULL, + timestamp INTEGER NOT NULL, + UNIQUE (callId, peerId) ON CONFLICT FAIL + ); +[ dropped all indexes to save space in this blog post ] +CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages + WHEN + new.body IS NOT NULL AND new.isViewOnce = 1 + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + END; +CREATE TRIGGER messages_on_insert AFTER INSERT ON messages + WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + DELETE FROM sendLogPayloads WHERE id IN ( + SELECT payloadId FROM sendLogMessageIds + WHERE messageId = old.id + ); + DELETE FROM reactions WHERE rowid IN ( + SELECT rowid FROM reactions + WHERE messageId = old.id + ); + DELETE FROM storyReads WHERE storyId = old.storyId; + END; +CREATE VIRTUAL TABLE messages_fts USING fts5( + body, + tokenize = 'signal_tokenizer' + ); +CREATE TRIGGER messages_on_update AFTER UPDATE ON messages + WHEN + (new.body IS NULL OR old.body IS NOT new.body) AND + new.isViewOnce IS NOT 1 AND new.storyId IS NULL + BEGIN + DELETE FROM messages_fts WHERE rowid = old.rowid; + INSERT INTO messages_fts + (rowid, body) + VALUES + (new.rowid, new.body); + END; +CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages + BEGIN + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages + BEGIN + DELETE FROM mentions WHERE messageId = new.id; + INSERT INTO mentions (messageId, mentionAci, start, length) + + SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci, + bodyRanges.value ->> 'start' as start, + bodyRanges.value ->> 'length' as length + FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges + WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL + + AND messages.id = new.id; + END; +sqlite> +</pre> + +Finally I have the tool needed to inspect and process Signal +messages that I need, without using the vendor provided client. Now +on to transforming it to a more useful format. + +As usual, if you use Bitcoin and want to show your support of my +activities, please send Bitcoin donations to my address +<a href="bitcoin:15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b">15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b</a>. + + + ONVIF IP camera management tool finally in Debian https://people.skolelinux.org/pere/blog/ONVIF_IP_camera_management_tool_finally_in_Debian.html diff --git a/blog/tags/sysadmin/index.html b/blog/tags/sysadmin/index.html index 5db63924c8..0d901c4214 100644 --- a/blog/tags/sysadmin/index.html +++ b/blog/tags/sysadmin/index.html @@ -523,7 +523,7 @@ and DHCP updates I wanted to do.

October (1)

November (2)

November (3)

@@ -939,7 +939,7 @@ and DHCP updates I wanted to do.

@@ -953,7 +953,7 @@ and DHCP updates I wanted to do.

@@ -1027,7 +1027,7 @@ and DHCP updates I wanted to do.

@@ -1039,7 +1039,7 @@ and DHCP updates I wanted to do.

diff --git a/blog/tags/usenix/index.html b/blog/tags/usenix/index.html index 0ce5122916..5c31731bae 100644 --- a/blog/tags/usenix/index.html +++ b/blog/tags/usenix/index.html @@ -245,7 +245,7 @@ for other sites to consider too?

October (1)

November (2)

November (3)

@@ -661,7 +661,7 @@ for other sites to consider too?

@@ -675,7 +675,7 @@ for other sites to consider too?

@@ -749,7 +749,7 @@ for other sites to consider too?

@@ -761,7 +761,7 @@ for other sites to consider too?

diff --git a/blog/tags/valg/index.html b/blog/tags/valg/index.html index 3c8cd8f412..0fae053fb9 100644 --- a/blog/tags/valg/index.html +++ b/blog/tags/valg/index.html @@ -1018,7 +1018,7 @@ inneholdt i Iran hvis de ikke hadde hemmelige valg?

October (1)

November (2)

November (3)

@@ -1434,7 +1434,7 @@ inneholdt i Iran hvis de ikke hadde hemmelige valg?

@@ -1448,7 +1448,7 @@ inneholdt i Iran hvis de ikke hadde hemmelige valg?

@@ -1522,7 +1522,7 @@ inneholdt i Iran hvis de ikke hadde hemmelige valg?

@@ -1534,7 +1534,7 @@ inneholdt i Iran hvis de ikke hadde hemmelige valg?

diff --git a/blog/tags/verkidetfri/index.html b/blog/tags/verkidetfri/index.html index 96abe00877..54f6b3de59 100644 --- a/blog/tags/verkidetfri/index.html +++ b/blog/tags/verkidetfri/index.html @@ -4059,7 +4059,7 @@ would like to tax copyrighted work values if they could.

October (1)

November (2)

November (3)

@@ -4475,7 +4475,7 @@ would like to tax copyrighted work values if they could.

@@ -4489,7 +4489,7 @@ would like to tax copyrighted work values if they could.

@@ -4563,7 +4563,7 @@ would like to tax copyrighted work values if they could.

@@ -4575,7 +4575,7 @@ would like to tax copyrighted work values if they could.

diff --git a/blog/tags/video/index.html b/blog/tags/video/index.html index 03ba4788e5..8e53041a39 100644 --- a/blog/tags/video/index.html +++ b/blog/tags/video/index.html @@ -7147,7 +7147,7 @@ larger stick as well.

October (1)

November (2)

November (3)

@@ -7563,7 +7563,7 @@ larger stick as well.

@@ -7577,7 +7577,7 @@ larger stick as well.

@@ -7651,7 +7651,7 @@ larger stick as well.

@@ -7663,7 +7663,7 @@ larger stick as well.

diff --git a/blog/tags/vitenskap/index.html b/blog/tags/vitenskap/index.html index 994caeb2d5..db186cf53d 100644 --- a/blog/tags/vitenskap/index.html +++ b/blog/tags/vitenskap/index.html @@ -411,7 +411,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

October (1)

November (2)

November (3)

@@ -827,7 +827,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

@@ -841,7 +841,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

@@ -915,7 +915,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

@@ -927,7 +927,7 @@ skyskrapere. Takke meg til en tur til mÃ¥nen.

diff --git a/blog/tags/web/index.html b/blog/tags/web/index.html index a2095eaa25..5b81d1109a 100644 --- a/blog/tags/web/index.html +++ b/blog/tags/web/index.html @@ -3843,7 +3843,7 @@ be the only one fitting our needs. :/

October (1)

November (2)

November (3)

@@ -4259,7 +4259,7 @@ be the only one fitting our needs. :/

@@ -4273,7 +4273,7 @@ be the only one fitting our needs. :/

@@ -4347,7 +4347,7 @@ be the only one fitting our needs. :/

@@ -4359,7 +4359,7 @@ be the only one fitting our needs. :/

diff --git a/blog/wmbusmeters__parse_data_from_your_utility_meter___nice_free_software.html b/blog/wmbusmeters__parse_data_from_your_utility_meter___nice_free_software.html index f64e1bfb54..6ca3add58b 100644 --- a/blog/wmbusmeters__parse_data_from_your_utility_meter___nice_free_software.html +++ b/blog/wmbusmeters__parse_data_from_your_utility_meter___nice_free_software.html @@ -93,7 +93,7 @@ activities, please send Bitcoin donations to my address

October (1)

November (2)

November (3)

@@ -509,7 +509,7 @@ activities, please send Bitcoin donations to my address

@@ -523,7 +523,7 @@ activities, please send Bitcoin donations to my address

@@ -597,7 +597,7 @@ activities, please send Bitcoin donations to my address

@@ -609,7 +609,7 @@ activities, please send Bitcoin donations to my address

diff --git a/blog/youtube_dl_for_nedlasting_fra_NRK_med_undertekster___nice_free_software.html b/blog/youtube_dl_for_nedlasting_fra_NRK_med_undertekster___nice_free_software.html index 1513f002ef..cc2984883f 100644 --- a/blog/youtube_dl_for_nedlasting_fra_NRK_med_undertekster___nice_free_software.html +++ b/blog/youtube_dl_for_nedlasting_fra_NRK_med_undertekster___nice_free_software.html @@ -116,7 +116,7 @@ komplett liste.

October (1)

November (2)

November (3)

@@ -532,7 +532,7 @@ komplett liste.

@@ -546,7 +546,7 @@ komplett liste.

drivstoffpriser (4)

english (453)

english (454)