Provide a database snapshot to facilitate development.

[LeopoldArkham]

It would be nice to have a snapshot of (a subset of) the database that someone wishing to contribute could load locally to test out the code on real data. You can fake crates, dependencies, downloads, etc., but that only gets you so far.

[Eh2406]

I recall there being an issue for creating sanitized sql dumps of the database. Such a dump, would be useful for this aswell. Of course now I cannot find the issue.

[carols10cents]

#410 is the closest one I know of, going to close that in favor of this one since we want to keep the index small.

Notable info from that issue is that rubygems.org provides a sanitized snapshot, i'll probably look into their infrastructure to see if there's something we could reuse.

The easiest thing to do sanitation-wise is probably to just not dump the users and follows tables. Actually we should probably make this a white list instead of a black list of tables to dump so that we don't leak data by adding a table that should be stripped out and forgetting to add it to the blacklist.

[Eh2406]

That is the one I had in mind, thanks! When we have a game plan for this I'd love to help.

[moore3071]

Would it be possible to use the repository crates.io-index to create a quick development database? This would make it a lot harder to accidentally leak any private data.

[icefoxen]

Do people still want this? Not having to scrape the crates.io API for things like downloads and crate owners would be great. I'll happily throw together a PR.

Edit: Would it be easier for you to just have a script to dump to a static file that gets run by a cron job and stuck in a static location? Or have an API endpoint that serves it somehow? I dunno much about heroku and your other infrastructure actually works. Upload it to a particular S3 location or something?

[icefoxen]

Ok, after a little research it looks like heroku lets you make backups, pull backups to a local machine or push them to heroku and so on, but not do what we want directly, which is to be very careful to dump only certain tables to a backup and then make it easily public. Seems like the easy way would be to use heroku pg:pull to pull a remote database to a local postgres instance, call pg_dump and give it your whitelist of tables to dump, then upload the results to an S3 bucket or something and have a URL that redirects to that. Not sure how big/unwieldy the database is, and I dunno how easy it is for crates.io to set up these sort of ad-hoc scripts; this is really not a programming problem as much as an infrastructure problem.

[smarnach]

Given the recent discussion about squashing the index, I took a look at this issue. Here is a suggestion for a design.

Goals

• Allow crawlers to download a single archive with all information instead of hitting crates.io thousands of times to retrieve all information on all crates.
• Facilitate local testing in crates.io development (e.g. query optimisation with a realistic dataset).
• Facilitate running a crates.io read-only mirror.

Data to export and privacy considerations

We should only export data that users expect us to be public, which is roughly the data that is already exposed via the API.

Some of the tables should not be exported at all, for some table we can only export a subset of the columns, and for some tables we should even filter the rows.

The users table is in the last category. I believe that users have a reasonable expectation of remaining private if they just create an account on crates.io without publishing anything (though I can see that others may disagree with this sentinment).

Here is a prototype suggestion for the data to export, in the form a a psql script:

-- Only select crate owners that have not been deleted.
CREATE TEMPORARY VIEW crate_owners_export AS (
    SELECT
        crate_id, owner_id, created_at, updated_at, owner_kind
    FROM crate_owners
    WHERE NOT deleted
);

-- Only select users who are publicly visible through public activity.
-- This query can be simplified by introducing a Boolean `public` column that is
-- flipped to `true` when a user has their first public activity.
CREATE TEMPORARY VIEW users_export AS (
    SELECT
        id, gh_login, name, gh_avatar, gh_id
    FROM users
    WHERE
        id in (
            SELECT owner_id AS user_id FROM crate_owners_export WHERE owner_kind = 0
            UNION
            SELECT published_by as user_id FROM versions
        )
);

-- \copy statements can't be broken up into multiple lines.
\copy badges (crate_id, badge_type, attributes) TO 'badges.csv' WITH CSV HEADER
\copy categories (id, category, slug, description, crates_cnt, created_at, path) TO 'categories.csv' WITH CSV HEADER
\copy (SELECT * FROM crate_owners_export) TO 'crate_owners.csv' WITH CSV HEADER
\copy crates (id, name, updated_at, created_at, downloads, description, homepage, documentation, readme, textsearchable_index_col, license, repository, max_upload_size) TO 'crates.csv' WITH CSV HEADER
\copy crates_categories (crate_id, category_id) TO 'crates_categories.csv' WITH CSV HEADER
\copy crates_keywords (crate_id, keyword_id) TO 'crates_keywords.csv' WITH CSV HEADER
\copy dependencies (id, version_id, crate_id, req, optional, default_features, features, target, kind) TO 'dependencies.csv' WITH CSV HEADER
\copy keywords (id, keyword, crates_cnt, created_at) TO 'keywords.csv' WITH CSV HEADER
\copy metadata (total_downloads) TO 'metadata.csv' WITH CSV HEADER
\copy readme_renderings (version_id, rendered_at) TO 'readme_renderings.csv' WITH CSV HEADER
\copy reserved_crate_names (name) TO 'reserved_crate_names.csv' WITH CSV HEADER
\copy teams (id, login, github_id, name, avatar) TO 'teams.csv' WITH CSV HEADER
\copy (SELECT * FROM users_export) TO 'users.csv' WITH CSV HEADER
\copy version_authors (id, version_id, name) TO 'version_authors.csv' WITH CSV HEADER
\copy version_downloads (version_id, downloads, counted, date) TO 'version_downloads.csv' WITH CSV HEADER
\copy versions (id, crate_id, num, updated_at, created_at, downloads, features, yanked, license, crate_size, published_by) TO 'versions.csv' WITH CSV HEADER

These CSV dumps can be bundled in a single tarball or zip archive, together with an import script that allows reimporting them.

The script above explicitly lists the names of all tables and columns to export, in order to make it a conscious decision to export the data from newly introduced columns.

As an additional mechanism to protect against accidental data leaks, we could run the script as a user who only has permission to access the public columns (and we could even consider enabling row security for the tables that we filter by row).

No data from these tables is exported by the above script:

• __diesel_schema_migrations
• api_tokens
• background_jobs
• crate_owner_invitations
• emails
• follows
• publish_limit_buckets
• publish_rate_overrides
• versions_published_by

Data consistency

The above \copy statements do not necessarily result in a consistent export. As an example, a new crate may be published between exporting the crates table and the versions table. The CSV export of the versions table will then contain a reference to a crate that is not in the CSV export of the crates table, which will result in an error when trying to re-import the data to Postgres.

It should be possible to mitigate this issue by carefully selecting the order for the table export.

Alternatives considered

SQL dumps

The pg_dump tool can only dump full tables. Since we omit columns and filter rows for several tables, we would need to create temporary export tables to be able to export them with pg_dump. SQL dumps can only be consumed by importing them into a proper SQL database, while CSV dumps can also be directly loaded to memory and processed ad hoc.

JSON dumps

The JSON schema exported by the crates/:crate_name endpoint could be expanded by including all relevant information for a single crate, including everything that is currently only covered as links. This would result in a format that is easy to consume for crawlers that currently download the data via the API. However, this format would be very verbose. While the data would be only moderately denormalized – most data belongs to only a single crate anyway – the constant repetition of column names results in significant overhead.

[carols10cents]

The users table is in the last category. I believe that users have a reasonable expectation of remaining private if they just create an account on crates.io without publishing anything (though I can see that others may disagree with this sentinment).

This is an interesting point that hasn't been raised before; I agree with your thoughts and your implementation here.

The script above explicitly lists the names of all tables and columns to export, in order to make it a conscious decision to export the data from newly introduced columns.

I agree that managing a list of allowed columns is the right direction. One concern I have is how do we manage updates to this script, if we add columns to the database that SHOULD be exported? I haven't been able to come up with a reasonable solution that isn't labor intensive and/or brittle.

[smarnach]

One concern I have is how do we manage updates to this script, if we add columns to the database that SHOULD be exported?

As mentioned above, the tarball with CSV files should also contain an SQL script to re-import the data. A basic test we can run in CI is exporting some data, then trying to re-import it in a clean database. This will catch some of the failure modes of forgetting to export a required column (e.g. if the column is not nullable, or if some database-level constraints are unmet without the new values).

In other cases, the result of not exporting a column is simply that some data that should be in the exports is missing. This generally won't break existing use cases of the dumps, since clients obviously don't rely on a column that has never been exported in a database dump. In these cases eventually someone will complain that they want to get that data, but that seem tolerable to me.

There are some failure modes that aren't easy to catch; e.g. when the import succeeds, but does not maintain some invariants that are not enforced at the database level. I epxect these failure modes to be extremely rare. (Adding new public columns is rare by itself.)