Merge ~pappacena/launchpad:db-patch-lp-signing-integration into launchpad:db-devel

Proposed by Thiago F. Pappacena on 2020-02-14
Status: Needs review
Proposed branch: ~pappacena/launchpad:db-patch-lp-signing-integration
Merge into: launchpad:db-devel
Diff against target: 87 lines (+49/-0)
2 files modified
database/schema/patch-2210-12-0.sql (+45/-0)
database/schema/security.cfg (+4/-0)
Reviewer Review Type Date Requested Status
Colin Watson db 2020-02-14 Approve on 2020-02-28
Review via email: mp+379218@code.launchpad.net

Commit message

Database patch for signing service integration tables.

To post a comment you must log in.
Colin Watson (cjwatson) :
review: Needs Fixing
Thiago F. Pappacena (pappacena) wrote :

Pushing requested changes.

Colin Watson (cjwatson) :
review: Approve (db)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1diff --git a/database/schema/patch-2210-12-0.sql b/database/schema/patch-2210-12-0.sql
2new file mode 100644
3index 0000000..f459287
4--- /dev/null
5+++ b/database/schema/patch-2210-12-0.sql
6@@ -0,0 +1,45 @@
7+-- Copyright 2020 Canonical Ltd. This software is licensed under the
8+-- GNU Affero General Public License version 3 (see the file LICENSE).
9+
10+SET client_min_messages=ERROR;
11+
12+
13+CREATE TABLE signingkey (
14+ id serial PRIMARY KEY,
15+ key_type integer NOT NULL,
16+ description text,
17+ fingerprint text NOT NULL,
18+ public_key bytea NOT NULL,
19+ date_created timestamp without time zone NOT NULL
20+ DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
21+
22+ -- This unique constraint is needed because ArchiveSigningKey has a
23+ -- compound foreign key using both columns.
24+ CONSTRAINT signingkey__id__key_type__key
25+ UNIQUE(id, key_type),
26+
27+ CONSTRAINT signingkey__key_type__fingerprint__key
28+ UNIQUE (key_type, fingerprint)
29+);
30+
31+
32+CREATE TABLE archivesigningkey (
33+ id serial PRIMARY KEY,
34+ archive integer NOT NULL REFERENCES archive,
35+ earliest_distro_series integer REFERENCES distroseries,
36+ key_type integer NOT NULL,
37+ signing_key integer NOT NULL,
38+ date_created timestamp without time zone NOT NULL
39+ DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
40+
41+ CONSTRAINT archivesigningkey__signing_key__fk
42+ FOREIGN KEY (signing_key, key_type)
43+ REFERENCES signingkey (id, key_type),
44+
45+ CONSTRAINT archivesigningkey__archive__key_type__earliest_distro_series__key
46+ UNIQUE(archive, key_type, earliest_distro_series)
47+);
48+
49+
50+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 12, 0);
51+
52diff --git a/database/schema/security.cfg b/database/schema/security.cfg
53index 306d206..c406105 100644
54--- a/database/schema/security.cfg
55+++ b/database/schema/security.cfg
56@@ -1200,6 +1200,7 @@ public.account = SELECT, INSERT, UPDATE
57 public.archive = SELECT, INSERT, UPDATE
58 public.archivearch = SELECT, INSERT, UPDATE, DELETE
59 public.archivejob = SELECT, INSERT
60+public.archivesigningkey = SELECT, INSERT, UPDATE
61 public.binarypackagebuild = SELECT, INSERT, UPDATE
62 public.binarypackagefile = SELECT, INSERT, UPDATE
63 public.binarypackagename = SELECT, INSERT, UPDATE
64@@ -1277,6 +1278,7 @@ public.project = SELECT, INSERT, UPDATE
65 public.section = SELECT, INSERT, UPDATE
66 public.sectionselection = SELECT, INSERT, UPDATE
67 public.signedcodeofconduct = SELECT, INSERT, UPDATE
68+public.signingkey = SELECT, INSERT, UPDATE
69 public.sourcepackageformatselection = SELECT, INSERT
70 public.sourcepackagename = SELECT, INSERT, UPDATE
71 public.sourcepackagepublishinghistory = SELECT, INSERT, UPDATE
72@@ -1370,6 +1372,7 @@ public.archivearch = SELECT, INSERT, UPDATE
73 public.archivefile = SELECT
74 public.archivejob = SELECT, INSERT
75 public.archivepermission = SELECT
76+public.archivesigningkey = SELECT, INSERT
77 public.binarypackagebuild = SELECT, INSERT, UPDATE
78 public.binarypackagefile = SELECT, INSERT
79 public.binarypackagename = SELECT, INSERT
80@@ -1464,6 +1467,7 @@ public.snapbuild = SELECT, UPDATE
81 public.snapbuildjob = SELECT, INSERT, UPDATE
82 public.snapfile = SELECT, INSERT, UPDATE
83 public.snappyseries = SELECT
84+public.signingkey = SELECT, INSERT
85 public.sourcepackageformatselection = SELECT
86 public.sourcepackagename = SELECT, INSERT
87 public.sourcepackagepublishinghistory = SELECT, INSERT

Subscribers

People subscribed via source and target branches