Create public request database

[baltpeter]

The barrier of entry for looking into how individual trackers work is currently very high. To be able to reliably reverse-engineer endpoints, you need a large dataset of (mobile app) traffic with hundreds of thousands of requests from thousands of apps. Even though our tools and libraries make it easier to collect such a dataset, this still makes it really hard for anyone who hasn't spent significant prior effort. And they won't have access to historical data either way. That is obviously not in our interest. Our goal is to shine a light on how trackers work and what they collect, and as such we of course want as many people as possible researching them.

This is also relevant for TrackHAR. We need to create documentation on why/how we have concluded what certain values transmitted to a tracking endpoint mean (tweaselORG/TrackHAR#9). To be able to do that in a way that is replicable by others, we need to be able to reference the actual requests we have based our conclusions on.

As such, we want to create a public repository/database of tracking requests. We already have multiple large datasets of mobile tracking requests from previous research projects and will be continuously collecting new requests through our platform.

This will finally also greatly benefit trackers.tweasel.org. We already have static example values for the existing adapters from one of our datasets (tweaselORG/tracker-wiki#3). With a public request database, we could be computing this data on the fly and even provide a long list of real example requests for each adapter.

[baltpeter]

I think Datasette would be a great tool for this. That provides a really nice web interface + exports in common formats + APIs for any SQLite database.

Plugins we may want to consider:

• https://github.com/simonw/datasette-graphql
• https://github.com/simonw/datasette-saved-queries
• https://github.com/simonw/datasette-public
• https://github.com/simonw/datasette-jq
• https://github.com/simonw/datasette-cors

[baltpeter]

All our current datasets are in PostgreSQL databases. How can I dump those to SQLite? Other than StackOverflow answers suggesting to modify the pgdump file to remove unsupported statements, I've found two tools: https://github.com/simonw/db-to-sqlite and https://github.com/caiiiycuk/postgresql-to-sqlite

I tried db-to-sqlite to make an SQLite database out of one view in the database from my MT:

pipx install 'db-to-sqlite[postgresql]'
db-to-sqlite "postgresql://<user>:<password>@<host>:<port>/<database>" ma.db --table filtered_requests

That produced a ~335MB file after 1.5mins. I opened that with Datasette:

datasette ma.db

And everything is working as expected. But queries take ~1s.

EDIT: That's not quite true. For some reason, listing the first page takes almost a second. But the subsequent pages take only ~50ms. Even a query like select * from filtered_requests where host like '%adcolony%'; that returns 1000 results only takes 52ms. So, I don't think that this an issue.

[baltpeter]

One thing we need to keep in mind: In rowid tables, Datasette (expectedly) uses the rowid to identify (and importantly) link to individual rows. However, those are not stable:

If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.
https://www.sqlite.org/rowidtable.html

There are also WITHOUT ROWID tables for which one needs to define an explicit primary key in the create table statement. I tested that with a composite primary key. Datasette handled that just fine. It then uses all elements of the primary key for the URL.

[baltpeter]

So, now we need to decide on a common schema that we can transform the existing datasets to. Luckily, they all use a very similar schema (e.g. https://github.com/baltpeter/thesis-mobile-consent-dialogs/blob/main/schema.sql, https://github.com/datenanfragen/android-data-safety-label-analysis/blob/main/schema.sql, https://github.com/baltpeter/ios-privacy-violations/blob/main/schema.sql).

I think it's a good idea to use TrackHAR's Request type as a base (that in turn was modeled after the previous schemas :D):

export type Request = {
    /** The time when the request was sent. */
    startTime: Date;
    /** The HTTP method used. */
    method: string;
    /** The host name of the request. */
    host: string;
    /** The full path of the request, including the query string. */
    path: string;
    /** The full URL, but without the query string. This is useful for matching in the adapters. */
    endpointUrl: string;
    /** The request body, if any. */
    content?: string;
    /** The port of the request. */
    port: string;
    /** The scheme of the request. */
    scheme: 'http' | 'https';
    /** The HTTP version of the request. */
    httpVersion: string;
    /** The headers included in the request. */
    headers?: { name: string; value: string }[];
    /** The cookies set through request. */
    cookies?: { name: string; value: string }[];
};

That would mean listing headers and cookies as JSON arrays in the rows instead of having separate tables for them. But to be honest, I prefer that for this use case anyway. Makes the schema simpler and otherwise, you'd probably pretty much always be JOINing against those auxiliary tables anyway. Also, I'm pretty sure that we won't lose any query features if we install the jq extension I listed above.

[baltpeter]

We then need to extend that with the information in my filtered_requests view: app ID, app version, platform. I don't think we care about the concept of runs that the existing schemas have. I'm not sure whether we want to encode information about whether a consent dialog was accepted/rejected. I don't really think that matters here.

I would use a composite primary key: The ID from the original dataset (can be whatever) plus a dataset column (we could even have a separate table that has more metadata about the datasets). That way, we also don't need to worry about ID collisions between datasets.

[baltpeter]

We then need to extend that with the information in my filtered_requests view: app ID, app version, platform.

Actually, on second thought, maybe we want to be more flexible. What if we later want to add requests from websites? platform: web works already, but websites don't have an app ID and version. Maybe we have an initiator column instead that is <app ID>@<version> for apps and the URL for websites?

[baltpeter]

That would leave us with something like this:

CREATE TABLE "requests" (
	"id"	TEXT,
	"dataset"	TEXT,
	"initiator"	TEXT,
	"platform"	TEXT NOT NULL,
	"runType"	TEXT,
	"startTime"	TEXT NOT NULL,
	"method"	TEXT NOT NULL,
	"httpVersion"	TEXT NOT NULL,
	"scheme"	TEXT NOT NULL,
	"host"	TEXT NOT NULL,
	"port"	INTEGER NOT NULL,
	"path"	TEXT NOT NULL,
	"content"	BLOB,
	"headers"	TEXT NOT NULL,
	"cookies"	TEXT NOT NULL,
	PRIMARY KEY("dataset","id")
) WITHOUT ROWID;

Some notes:

• I deliberately left the initiator column nullable. I think it can also be valuable to have requests that we can't attribute to a specific app (e.g. from the OS background noise).
• I did add a nullable column for run types, after all. I wouldn't enforce an enum across different datasets for those, but that allows us to import some additional context from the datasets.
• I didn't include the endpoint URL here yet. I think we'll have one interim table per dataset and then create a view that merges them and allows us to introduce such concepts globally.

[baltpeter]

I think the best way to get the data from the datasets into this format is to construct a temporary view.

First step for my master's thesis:

select filtered_requests.id                                           as id,
       'informed-consent'                                             as dataset,
       filtered_requests.name || '@' || version                       as initiator,
       platform,
       run_type                                                       as runType,
       start_time                                                     as startTime,
       method,
       http_version                                                   as httpVersion,
       scheme,
       host,
       port,
       content_raw                                                    as content,
       coalesce((select json_agg(json_build_object('name', h.name, 'value', h.values[1]))
                 from headers h
                 where h.request = filtered_requests.id), '[]'::json) as headers,
       coalesce((select json_agg(json_build_object('name', c.name, 'value', c.values[1]))
                 from cookies c
                 where c.request = filtered_requests.id), '[]'::json) as cookies
from filtered_requests;

But I still need to add the background traffic with initiator: null.

[baltpeter]

Here's the query I used to create the view that includes both types of requests:

create view informedConsentRequests as

(select filtered_requests.id                                           as id,
        'informed-consent'                                             as dataset,
        filtered_requests.name || '@' || version                       as initiator,
        platform,
        run_type                                                       as runType,
        start_time                                                     as startTime,
        method,
        http_version                                                   as httpVersion,
        scheme,
        host,
        port,
        content_raw                                                    as content,
        coalesce((select json_agg(json_build_object('name', h.name, 'value', h.values[1]))
                  from headers h
                  where h.request = filtered_requests.id), '[]'::json) as headers,
        coalesce((select json_agg(json_build_object('name', c.name, 'value', c.values[1]))
                  from cookies c
                  where c.request = filtered_requests.id), '[]'::json) as cookies
 from filtered_requests)

union all

(select requests.id                                           as id,
        'informed-consent'                                    as dataset,
        null                                                  as initiator,
        platform,
        run_type                                              as runType,
        requests.start_time                                   as startTime,
        method,
        http_version                                          as httpVersion,
        scheme,
        host,
        port,
        content_raw                                           as content,
        coalesce((select json_agg(json_build_object('name', h.name, 'value', h.values[1]))
                  from headers h
                  where h.request = requests.id), '[]'::json) as headers,
        coalesce((select json_agg(json_build_object('name', c.name, 'value', c.values[1]))
                  from cookies c
                  where c.request = requests.id), '[]'::json) as cookies
 from requests
          join runs r on r.id = requests.run
          join apps a on r.app = a.id
 where not exists(select 1 from filtered_requests where requests.id = filtered_requests.id));

The subqueries for the headers and cookies make this very slow but that's not too much of a problem since I only want to export the full view once and then never touch it again.

[baltpeter]

I severely underestimated how long this was going to take. The db-to-sqlite command ran for 6.5 hours! :o

In the meantime, I tried creating a second materialized view hoping that that would be faster, but I aborted that after almost two hours when db-to-sqlite had finished.

I have no idea what is going on. The two queries making up the union "only" take less than two minutes each to run. How can combining them into a view take this ridiculously long?

[baltpeter]

Okay, I think I've found the source of my confusion. I'm pretty sure DataGrip helpfully automatically inserted a limit 501 to my select queries because it only shows 500 rows in the preview anyway (and it didn't show me a row count).

Even if manually add a limit 1000, the query takes the same time, it only shows me 500 rows and the row count is displayed as 501+. If I click the "next page" button, loading takes another two minutes. That confirms my theory. Pretty unintuitive design here, DataGrip…

[baltpeter]

Okay. But if computing 500 rows already takes 2 mins, I shouldn't be surprised that 220k rows take a long, long time. :D

So, the subqueries are a lot worse than I figured. But upon now thinking about this more this does actually make sense. There are 1.6M header rows and 150k cookie rows. It has to go through all these for 220k rows…

I confirmed this by running the query with EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) (with a limit 501 — didn't want to wait another couple of hours :D) and loading the result into explain.dalibo.com: https://explain.dalibo.com/plan/d6b7gc26a17c54a5

That clearly shows that the headers and cookies (though the latter much less so) are the problem:

[baltpeter]

My first idea for optimisation was to precompute the headers and cookies JSON like this:

with cookiesJson as (select request, json_agg(json_build_object('name', c.name, 'value', c.values[1])) as json
                     from cookies c
                     group by c.request),
     headersJson as (select request, json_agg(json_build_object('name', h.name, 'value', h.values[1])) as json
                     from headers h
                     group by h.request)
select filtered_requests.id                                                                                  as id,
       'informed-consent'                                                                                    as dataset,
       filtered_requests.name || '@' || version                                                              as initiator,
       platform,
       run_type                                                                                              as runType,
       start_time                                                                                            as startTime,
       method,
       http_version                                                                                          as httpVersion,
       scheme,
       host,
       port,
       content_raw                                                                                           as content,
       coalesce((select json from headersJson where headersJson.request = filtered_requests.id), '[]'::json) as headers,
       coalesce((select json from cookiesJson where cookiesJson.request = filtered_requests.id), '[]'::json) as cookies
from filtered_requests;

However, that didn't help at all. The query still takes the exact same amount of time.

[baltpeter]

I don't really understand why that is. Computing cookiesJson and headersJson is super fast and they only have 16k and 220k rows each, so I would have expected at least some speedup.

Looking at the query plan, it's almost the exact same with just an additional "Subquery Scan" for each of the subqueries. Clearly, I'm misunderstanding something here.