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

Proposed by Thiago F. Pappacena on 2020-02-14
Status: Merged
Approved by: Thiago F. Pappacena on 2020-04-08
Approved revision: 7f15d0459ab3719f5f45ff5ddf9b3c3a52014be5
Merge reported by: Otto Co-Pilot
Merged at revision: not available
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) :
Colin Watson (cjwatson) :
review: Needs Fixing
e077c2a... by Thiago F. Pappacena on 2020-02-27

Refactoring ArchiveSigningKey to make sure we don't have duplicated key_types for a given archive/distroseries

Thiago F. Pappacena (pappacena) wrote :

Pushing requested changes.

Colin Watson (cjwatson) :
review: Approve (db)
7f15d04... by Thiago F. Pappacena on 2020-03-02

renaming index and reordering keys

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
diff --git a/database/schema/patch-2210-12-0.sql b/database/schema/patch-2210-12-0.sql
0new file mode 1006440new file mode 100644
index 0000000..f459287
--- /dev/null
+++ b/database/schema/patch-2210-12-0.sql
@@ -0,0 +1,45 @@
1-- Copyright 2020 Canonical Ltd. This software is licensed under the
2-- GNU Affero General Public License version 3 (see the file LICENSE).
3
4SET client_min_messages=ERROR;
5
6
7CREATE TABLE signingkey (
8 id serial PRIMARY KEY,
9 key_type integer NOT NULL,
10 description text,
11 fingerprint text NOT NULL,
12 public_key bytea NOT NULL,
13 date_created timestamp without time zone NOT NULL
14 DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
15
16 -- This unique constraint is needed because ArchiveSigningKey has a
17 -- compound foreign key using both columns.
18 CONSTRAINT signingkey__id__key_type__key
19 UNIQUE(id, key_type),
20
21 CONSTRAINT signingkey__key_type__fingerprint__key
22 UNIQUE (key_type, fingerprint)
23);
24
25
26CREATE TABLE archivesigningkey (
27 id serial PRIMARY KEY,
28 archive integer NOT NULL REFERENCES archive,
29 earliest_distro_series integer REFERENCES distroseries,
30 key_type integer NOT NULL,
31 signing_key integer NOT NULL,
32 date_created timestamp without time zone NOT NULL
33 DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
34
35 CONSTRAINT archivesigningkey__signing_key__fk
36 FOREIGN KEY (signing_key, key_type)
37 REFERENCES signingkey (id, key_type),
38
39 CONSTRAINT archivesigningkey__archive__key_type__earliest_distro_series__key
40 UNIQUE(archive, key_type, earliest_distro_series)
41);
42
43
44INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 12, 0);
45
diff --git a/database/schema/security.cfg b/database/schema/security.cfg
index 306d206..c406105 100644
--- a/database/schema/security.cfg
+++ b/database/schema/security.cfg
@@ -1200,6 +1200,7 @@ public.account = SELECT, INSERT, UPDATE
1200public.archive = SELECT, INSERT, UPDATE1200public.archive = SELECT, INSERT, UPDATE
1201public.archivearch = SELECT, INSERT, UPDATE, DELETE1201public.archivearch = SELECT, INSERT, UPDATE, DELETE
1202public.archivejob = SELECT, INSERT1202public.archivejob = SELECT, INSERT
1203public.archivesigningkey = SELECT, INSERT, UPDATE
1203public.binarypackagebuild = SELECT, INSERT, UPDATE1204public.binarypackagebuild = SELECT, INSERT, UPDATE
1204public.binarypackagefile = SELECT, INSERT, UPDATE1205public.binarypackagefile = SELECT, INSERT, UPDATE
1205public.binarypackagename = SELECT, INSERT, UPDATE1206public.binarypackagename = SELECT, INSERT, UPDATE
@@ -1277,6 +1278,7 @@ public.project = SELECT, INSERT, UPDATE
1277public.section = SELECT, INSERT, UPDATE1278public.section = SELECT, INSERT, UPDATE
1278public.sectionselection = SELECT, INSERT, UPDATE1279public.sectionselection = SELECT, INSERT, UPDATE
1279public.signedcodeofconduct = SELECT, INSERT, UPDATE1280public.signedcodeofconduct = SELECT, INSERT, UPDATE
1281public.signingkey = SELECT, INSERT, UPDATE
1280public.sourcepackageformatselection = SELECT, INSERT1282public.sourcepackageformatselection = SELECT, INSERT
1281public.sourcepackagename = SELECT, INSERT, UPDATE1283public.sourcepackagename = SELECT, INSERT, UPDATE
1282public.sourcepackagepublishinghistory = SELECT, INSERT, UPDATE1284public.sourcepackagepublishinghistory = SELECT, INSERT, UPDATE
@@ -1370,6 +1372,7 @@ public.archivearch = SELECT, INSERT, UPDATE
1370public.archivefile = SELECT1372public.archivefile = SELECT
1371public.archivejob = SELECT, INSERT1373public.archivejob = SELECT, INSERT
1372public.archivepermission = SELECT1374public.archivepermission = SELECT
1375public.archivesigningkey = SELECT, INSERT
1373public.binarypackagebuild = SELECT, INSERT, UPDATE1376public.binarypackagebuild = SELECT, INSERT, UPDATE
1374public.binarypackagefile = SELECT, INSERT1377public.binarypackagefile = SELECT, INSERT
1375public.binarypackagename = SELECT, INSERT1378public.binarypackagename = SELECT, INSERT
@@ -1464,6 +1467,7 @@ public.snapbuild = SELECT, UPDATE
1464public.snapbuildjob = SELECT, INSERT, UPDATE1467public.snapbuildjob = SELECT, INSERT, UPDATE
1465public.snapfile = SELECT, INSERT, UPDATE1468public.snapfile = SELECT, INSERT, UPDATE
1466public.snappyseries = SELECT1469public.snappyseries = SELECT
1470public.signingkey = SELECT, INSERT
1467public.sourcepackageformatselection = SELECT1471public.sourcepackageformatselection = SELECT
1468public.sourcepackagename = SELECT, INSERT1472public.sourcepackagename = SELECT, INSERT
1469public.sourcepackagepublishinghistory = SELECT, INSERT1473public.sourcepackagepublishinghistory = SELECT, INSERT

Subscribers

People subscribed via source and target branches