launchpad-reviewers team mailing list archive

[Colin Watson <mp+409463@xxxxxxxxxxxxxxxxxx>]

Colin Watson has proposed merging ~cjwatson/launchpad:db-access-token into launchpad:db-devel.

Commit message:
Add AccessToken table

Requested reviews:
  William Grant (wgrant): db
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/409463

This will be used for personal access tokens for the webservice API, as part of https://docs.google.com/document/d/1RiiAwB_hmda0Xwm61wyOKG-v8y2SDIbKYYS17aNVqtE.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:db-access-token into launchpad:db-devel.

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,
+    date_expires timestamp without time zone
+);
+
+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);
diff --git a/database/schema/security.cfg b/database/schema/security.cfg
index 8779ef9..96158cc 100644
--- a/database/schema/security.cfg
+++ b/database/schema/security.cfg
@@ -116,6 +116,7 @@ public.accesspolicy                     = SELECT, INSERT, UPDATE, DELETE
 public.accesspolicyartifact             = SELECT, INSERT, UPDATE, DELETE
 public.accesspolicygrant                = SELECT, INSERT, UPDATE, DELETE
 public.accesspolicygrantflat            = SELECT
+public.accesstoken                      = SELECT, INSERT, UPDATE, DELETE
 public.account                          = SELECT, INSERT, UPDATE, DELETE
 public.announcement                     = SELECT, INSERT, UPDATE, DELETE
 public.answercontact                    = SELECT, INSERT, UPDATE, DELETE
@@ -2296,6 +2297,7 @@ public.accesspolicyartifact             = SELECT
 public.accessartifactgrant              = SELECT, UPDATE, DELETE
 public.accesspolicy                     = SELECT, UPDATE, DELETE
 public.accesspolicygrant                = SELECT, UPDATE, DELETE
+public.accesstoken                      = SELECT, UPDATE
 public.account                          = SELECT, UPDATE
 public.announcement                     = SELECT, UPDATE
 public.answercontact                    = SELECT, UPDATE, DELETE