launchpad-reviewers team mailing list archive

[Colin Watson <mp+409463@xxxxxxxxxxxxxxxxxx>]

Diff comments:

> diff --git a/database/schema/patch-2210-36-0.sql b/database/schema/patch-2210-36-0.sql
> new file mode 100644
> index 0000000..fa08939
> --- /dev/null
> +++ b/database/schema/patch-2210-36-0.sql
> @@ -0,0 +1,38 @@
> +-- Copyright 2021 Canonical Ltd.  This software is licensed under the
> +-- GNU Affero General Public License version 3 (see the file LICENSE).
> +
> +SET client_min_messages=ERROR;
> +
> +CREATE TABLE AccessToken (
> +    id serial PRIMARY KEY,
> +    date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL,
> +    token_sha256 text NOT NULL,
> +    owner integer NOT NULL REFERENCES person,
> +    description text NOT NULL,
> +    git_repository integer REFERENCES gitrepository NOT NULL,
> +    scopes jsonb NOT NULL,
> +    date_last_used timestamp without time zone,

Hm, right, I'll have to look into that.  Thanks.

> +    date_expires timestamp without time zone

I was initially intending to include date_revoked etc., but I realized I couldn't think of a good reason not to just implement revocation by deleting the corresponding rows; GitHub and GitLab don't show revoked tokens in the UI, and it's not clear that there's anything that we could reasonably log that might let us correlate requests with revoked tokens other than the token's scopes.  Do you disagree?

> +);
> +
> +COMMENT ON TABLE AccessToken IS 'A personal access token for the webservice API.';
> +COMMENT ON COLUMN AccessToken.date_created IS 'When the token was created.';
> +COMMENT ON COLUMN AccessToken.token_sha256 IS 'SHA-256 hash of the secret token.';
> +COMMENT ON COLUMN AccessToken.owner IS 'The person who created the token.';
> +COMMENT ON COLUMN AccessToken.description IS 'A short description of the token''s purpose.';
> +COMMENT ON COLUMN AccessToken.git_repository IS 'The Git repository for which the token was issued.';
> +COMMENT ON COLUMN AccessToken.scopes IS 'A list of scopes granted by the token.';
> +COMMENT ON COLUMN AccessToken.date_last_used IS 'When the token was last used.';
> +COMMENT ON COLUMN AccessToken.date_expires IS 'When the token should expire.';
> +
> +CREATE UNIQUE INDEX accesstoken__token_sha256__key
> +    ON AccessToken (token_sha256);
> +CREATE INDEX accesstoken__owner__idx
> +    ON AccessToken (owner);
> +CREATE INDEX accesstoken__git_repository__idx
> +    ON AccessToken (git_repository);
> +CREATE INDEX accesstoken__date_expires__idx
> +    ON AccessToken (date_expires)
> +    WHERE date_expires IS NOT NULL;
> +
> +INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 36, 0);


-- 
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/409463
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:db-access-token into launchpad:db-devel.