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.
Dear Debianites
This morning I decided to just start writing Bits from DPL and send
whatever I have by 18:00 local time. Here it is, barely proof read,
along with all it's warts and grammar mistakes! It's slightly long and
doesn't contain any critical information, so if you're not in the mood,
don't feel compelled to read it!
Get ready for a new DPL!
Soon, the voting period will start to elect our next DPL, and my time
as DPL will come to an end. Reading the questions posted to the new
candidates on 
Probably a trait of my family s origins as migrants from East Europe, probably
part of the collective trauma of jews throughout the world or probably
because that s just who I turned out to be, I hold in high regard the
preservation of memory of my family s photos, movies and such items. And it s a
trait shared by many people in my familiar group.
Shortly after my grandmother died 24 years ago, my mother did a large, loving
work of digitalization and restoration of my grandparent s photos. Sadly, the
higher resolution copies of said photos is lost but she took the work of not
just scanning the photos, but assembling them in presentations, telling a story,
introducing my older relatives, many of them missing 40 or more years before my
birth.
But said presentations were built using Flash. Right, not my choice of tool, and
I told her back in the day but given I wasn t around to do the work in what
I d chosen (a standards-abiding format, naturally), and given my graphic design
skills are nonexistant Several years ago, when Adobe pulled the plug on the
Flash format, we realized they would no longer be accessible. I managed to get
the photos out of the preentations, but lost the narration, that is a great part
of the work.
Three days ago, however, I read a post on 
Read all parts of the series
And here s what he explains about his drawing:
In their drawing we see a Youtube prank channel on a screen, an external
trackpad on the right (likely it s not a touch screen), and headphones.
Notice how there is no keyboard, or maybe it s folded away.
If you could ask a nice and friendly dragon anything you d like to
learn about the internet, what would it be?
In her drawing, we see again Google - it s clearly everywhere - and also
the interfaces for calling and texting someone.
To explain what the internet is, besides the fact that one
can use it for calling and listening to music, she says:
When I asked if he knew what actually happens between the device and a
website he visits, he put forth the hypothesis of the existence of some
kind of
A new release 0.3.3.9.4 of 
A new minor release of the 
Official logo of DebConf23
Suresh and me celebrating Onam in Kochi.
Four Points Hotel by Sheraton was the venue of DebConf23. Photo credits: Bilal
Photo of the pool. Photo credits: Andreas Tille.
View from the hotel window.
This place served as lunch and dinner place and later as hacklab during debconf. Photo credits: Bilal
Picture of the awesome swag bag given at DebConf23. Photo credits: Ravi Dwivedi
My presentation photo. Photo credits: Valessio
Selfie with Anisa and Kristi. Photo credits: Anisa.
Me helping with the Cheese and Wine Party.
This picture was taken when there were few people in my room for the party.
Sadhya Thali: A vegetarian meal served on banana leaf. Payasam and rasam were especially yummy! Photo credits: Ravi Dwivedi.
Sadhya thali being served at debconf23. Photo credits: Bilal
Group photo of our daytrip. Photo credits: Radhika Jhalani
A selfie in memory of Abraham.
Thanks to Niibe Yutaka (the person towards your right hand) from Japan (FSIJ), who gave me a wonderful Japanese gift during debconf23: A folder to keep pages with ancient Japanese manga characters printed on it. I realized I immediately needed that :)
This is the Japanese gift I received.
Bits from the DPL. Photo credits: Bilal
Kristi on GNOME community. Photo credits: Ravi Dwivedi.
Abhas' talk on home automation. Photo credits: Ravi Dwivedi.
I was roaming around with a QR code on my T-shirt for downloading Prav.
Me in mundu. Picture credits: Abhijith PA
From left: Nilesh, Saswata, me, Sahil. Photo credits: Sahil.
Ruchika (taking the selfie) and from left to right: Yash, 
Joost and me going to Delhi. Photo credits: Ravi.
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
There is a bit of context that needs to be shared before I get to this and would be a long one. For reasons known and unknown, I have a lot of sudden electricity outages. Not just me, all those who are on my line. A discussion with a lineman revealed that around 200+ families and businesses are on the same line and when for whatever reason the electricity goes for all. Even some of the traffic lights don t work. This affects software more than hardware or in some cases, both. And more specifically HDD s are vulnerable. I had bought an APC unit several years for precisely this, but over period of time it just couldn t function and trips also when the electricity goes out. It s been 6-7 years so can t even ask customer service to fix the issue and from whatever discussions I have had with APC personnel, the only meaningful difference is to buy a new unit but even then not sure this is an issue that can be resolved, even with that.
That comes to the issue that happens once in a while where the system fsck is unable to repair /home and you need to use an external pen drive for the same. This is my how my hdd stacks up 
The main argument as have shared before is that Indian Govt. thinks we need our home grown CPU and while I have no issues with that, as shared before except for RISC-V there is no other space where India could look into doing that. Especially after the Chip Act, Biden has made that any new fabs or any new thing in chip fabrication will only be shared with 
. Backups certainly make a lot of sense, especially 
The phrase "Root of Trust" turns up at various points in discussions about verified boot and measured boot, and to a first approximation nobody is able to give you a coherent explanation of what it means[1]. The Trusted Computing Group has a fairly wordy 