Petter Reinholdtsen: New and improved sqlcipher in Debian for accessing Signal database
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.jsonAssuming 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.
TPMs contain a set of registers ("Platform Configuration Registers", or PCRs) that are used to track what a system boots. Each time a new event is measured, a cryptographic hash representing that event is passed to the TPM. The TPM appends that hash to the existing value in the PCR, hashes that, and stores the final result in the PCR. This means that while the PCR's value depends on the precise sequence and value of the hashes presented to it, the PCR value alone doesn't tell you what those individual events were. Different PCRs are used to store different event types, but there are still more events than there are PCRs so we can't avoid this problem by simply storing each event separately.
QNAP TS-453mini product photo
The logo for QNAP HappyGet 2 and Blizzard s StarCraft 2 side by side
Thermalright AXP120-X67, AMD Ryzen 5 PRO 5650GE, ASRock Rack X570D4I-2T, all assembled and running on a flat surface
Memtest86 showing test progress, taken from IPMI remote control window
Screenshot of PCIe 16x slot bifurcation options in UEFI settings, taken from IPMI remote control window
Internal image of Silverstone CS280 NAS build. Image stolen from 
Internal image of Silverstone CS280 NAS build. Image stolen from 
NAS build in Silverstone SUGO 14, mid build, panels removed
Silverstone SUGO 14 from the front, with hot swap bay installed
Storage SSD loaded into hot swap sled
TrueNAS Dashboard screenshot in browser window
The final system, powered up
Inspired by the fine Debian Local Groups all over the world, I ve long since wanted to start one in Cape Town. Unfortunately, there s been many obstacles over the years. Shiny distractions, an epidemic, DPL terms these are just some of the things that got in the way.
Fortunately, things are starting to gain traction, and we re well on our way to forming a bona fide local group for South Africa.
We got together at Woodstock Grill, they have both a nice meeting room, and good food and beverage, also reasonably central for most of us.
About 6 months ago, I decided to purchase a bike trailer. I don't drive and
although I also have a shopping caddy, it often can't handle a week's
groceries.
Since the goal for the trailer was to haul encumbering and heavy loads, I
decided to splurge and got a 
So far, I've used the trailer to go grocery shopping, buy bulk food and haul
dirt and mulch. To make things easier, I've purchased two 
Although my housing cooperative has a (small) indoor bicycle parking space, my
partner uses our spot during the summer, which means I have to store the
trailer on my balcony. To make things more manageable and free up some space, I
set up a system of pulleys to hoist the trailer up the air when it's not in
use.
I did go through a few iterations, but I'm pretty happy with the current 8
pulleys block and tackle mechanism I rigged.
All and all, this trailer wasn't cheap, but I regret nothing. Knowing Surley's
reputation, it will last me many years and not having to drive a car to get
around always ends up being the cheaper solution.
Since some time I ve been thinking about making myself a top or a dress with a wide gathered neckline that can be work at different widths, including off-the-shoulders.
A few years ago I ve been gifted a cut of nice, thin white fabric with a print of lines and lozenges that isn t uniform along the fabric, but looks like it was designed for some specific garment, and it was waiting in my stash for a suitable pattern.
And a few days ago, during a Sunday lunch, there was an off-hand mention of a dress from the late 1970s which had an elastic in the neckline, so that it could be optionally worn off-the-shoulders.
And something snapped in place.
I had plans for that afternoon, but they were scrapped, and I started to draw, measure, cut rectangles of fabric, pin and measure again, cut more fabric.
The part of the fabric with large vertical stripes had two different widths: I could have made the back narrower, but I decided to just keep a strip with narrower lines to one side.
The fabric also didn t have a full second strip of lozenges, so I had to hem it halfway through it.
The casing for the elastic was pieced from various scraps, but at least I was able to match the lines on the center front and back, even if they are different. Not that it matters a lot, since it s all hidden in the gathering, but I would have known.
And since I was working on something definitely modern, even if made out of squares and rectangles, of course I decided to hand-sew everything, mostly to be able to use quite small sewing allowances, since the fabric was pretty thin.
In my stash I had a piece of swimsuit elastic that feels nice, looks nice and makes a knot that doesn t slip, so I used it. It s a perfect match, except for the neon yellow colour, which I do like, but maybe is a bit too high visibility? I will see if the haberdasher has the same elastic in dark blue, but right now this will do.
It was a quick project anyway: by the end of the working week the top was finished; I think that on a sewing machine it would be easy to make it in a day.
And it can be worn off the shoulders! Which is something I will probably never do in public (and definitely not outdoors), but now if I wanted I could! :D
As usual, the pattern (for what pattern there is) and instructions are 
I m six months into my journey of building a business which means its time to reflect and review the goals I set for the year.
I use a LUKS-encrypted USB stick to store my GPG and SSH keys, which acts as a
backup and portable key setup when working on different laptops. One
inconvenience with LUKS-encrypted USB sticks is that you need to enter the
password every time you want to mount the device, either through a Window
Manager like KDE or using the cryptsetup luksOpen command. Fortunately, many
laptops nowadays come equipped with TPM2 modules, which can be utilized to
automatically decrypt the device and subsequently mount it. In this post, we'll
explore the usage of systemd-cryptenroll for this purpose, along with udev
rules and a set of scripts to automate the mounting of the encrypted USB.
First, ensure that your device has a TPM2 module. You can run the following
command to check:
Reduce the size of your c: partition to the smallest it can be and then turn off windows with the understanding that you will never boot this system on the iron ever again.
To find the literal path names of your detected drives you can run fdisk -l. Pay attention to the names of the partitions and the sizes of the drives to help determine which is which.
Once you have a shell in the netinst installer, you should maybe be able to run a command like the following. This will duplicate the disk located at if (in file) to the disk located at of (out file) while showing progress as the status.
In the New VM window, select Import existing disk image 
When prompted for the path to the image, use the one we created with sudo qemu-img convert above.
Select the version of Windows you want.
Select memory and CPUs to allocate to the VM.
Tick the Customize configuration before install box
If you re prompted to enable the default network, do so now.
The default hardware layout should probably suffice. Get it as close to the underlying hardware as it is convenient to do. But Windows is pretty lenient these days about virtualizing licensed windows instances so long as they re not running in more than one place at a time.
Good luck! Leave comments if you have questions.
