Launchpad itself

Merge ~lgp171188/launchpad:merge-db-stable into launchpad:master

  1. Git
  2. lp:~lgp171188/launchpad
  3. merge-db-stable
  4. Merge into master
Proposed by Guruprasad
Status: Merged
Approved by: Guruprasad
Approved revision: 14771964f77390c2e939bd0da72e2c1d9a12eff2
Merge reported by: Otto Co-Pilot
Merged at revision: not available
Proposed branch: ~lgp171188/launchpad:merge-db-stable
Merge into: launchpad:master
Diff against target: 193 lines (+137/-0)
3 files modified
database/schema/patch-2211-02-0.sql (+112/-0)
database/schema/security.cfg (+4/-0)
lib/lp/registry/personmerge.py (+21/-0)
Reviewer Review Type Date Requested Status
Guruprasad Approve
Review via email: mp+427425@code.launchpad.net

Commit message

Merge db-stable 14771964f7 Add the VulnerabilitySubscription table

To post a comment you must log in.
Revision history for this message
Guruprasad (lgp171188) wrote :

Self-approving and merging since this has been deployed to production today

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1diff --git a/database/schema/patch-2211-02-0.sql b/database/schema/patch-2211-02-0.sql
2new file mode 100644
3index 0000000..8f7d622
4--- /dev/null
5+++ b/database/schema/patch-2211-02-0.sql
6@@ -0,0 +1,112 @@
7+-- Copyright 2022 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+ALTER TABLE Vulnerability
13+ ADD COLUMN access_policy integer,
14+ ADD COLUMN access_grants integer[];
15+
16+CREATE TABLE VulnerabilitySubscription (
17+ id serial PRIMARY KEY,
18+ person integer REFERENCES Person NOT NULL,
19+ vulnerability integer REFERENCES Vulnerability NOT NULL,
20+ date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL,
21+ subscribed_by integer REFERENCES Person NOT NULL
22+);
23+
24+COMMENT ON TABLE VulnerabilitySubscription IS 'Person subscription for Vulnerabilities.';
25+COMMENT ON COLUMN VulnerabilitySubscription.person IS 'The person subscribing to the vulnerability.';
26+COMMENT ON COLUMN VulnerabilitySubscription.vulnerability IS 'The vulnerability being subscribed to.';
27+COMMENT ON COLUMN VulnerabilitySubscription.date_created IS 'The date when the subscription was created.';
28+COMMENT ON COLUMN VulnerabilitySubscription.subscribed_by IS 'The person who created the subscription.';
29+
30+CREATE UNIQUE INDEX vulnerabilitysubscription__person__vulnerability__key
31+ ON VulnerabilitySubscription (person, vulnerability);
32+
33+CREATE INDEX vulnerabilitysubscription__vulnerability__idx
34+ ON VulnerabilitySubscription (vulnerability);
35+
36+CREATE INDEX vulnerabilitysubscription__subscribed_by__idx
37+ ON VulnerabilitySubscription (subscribed_by);
38+
39+ALTER TABLE AccessArtifact
40+ ADD COLUMN vulnerability integer REFERENCES Vulnerability;
41+
42+
43+ALTER TABLE AccessArtifact DROP CONSTRAINT has_artifact;
44+ALTER TABLE AccessArtifact
45+ ADD CONSTRAINT has_artifact CHECK (
46+ (null_count(ARRAY[bug, branch, gitrepository, snap, specification, ocirecipe, vulnerability]) = 6)) NOT VALID;
47+
48+
49+CREATE OR REPLACE FUNCTION vulnerability_denorm_access(vulnerability_id integer)
50+ RETURNS void LANGUAGE plpgsql AS
51+$$
52+DECLARE
53+ info_type integer;
54+BEGIN
55+ SELECT Vulnerability.information_type INTO info_type
56+ FROM Vulnerability where id = vulnerability_id;
57+
58+ UPDATE Vulnerability
59+ SET access_policy = policies[1], access_grants = grants
60+ FROM
61+ build_access_cache(
62+ (SELECT id FROM accessartifact WHERE vulnerability = vulnerability_id),
63+ info_type)
64+ AS (policies integer[], grants integer[])
65+ WHERE id = vulnerability_id;
66+END;
67+$$;
68+
69+CREATE OR REPLACE FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer)
70+ RETURNS void
71+ LANGUAGE plpgsql
72+ AS $$
73+DECLARE
74+ artifact_row accessartifact%ROWTYPE;
75+BEGIN
76+ SELECT * INTO artifact_row FROM accessartifact WHERE id = artifact_id;
77+ IF artifact_row.bug IS NOT NULL THEN
78+ PERFORM bug_flatten_access(artifact_row.bug);
79+ END IF;
80+ IF artifact_row.branch IS NOT NULL THEN
81+ PERFORM branch_denorm_access(artifact_row.branch);
82+ END IF;
83+ IF artifact_row.gitrepository IS NOT NULL THEN
84+ PERFORM gitrepository_denorm_access(artifact_row.gitrepository);
85+ END IF;
86+ IF artifact_row.snap IS NOT NULL THEN
87+ PERFORM snap_denorm_access(artifact_row.snap);
88+ END IF;
89+ IF artifact_row.specification IS NOT NULL THEN
90+ PERFORM specification_denorm_access(artifact_row.specification);
91+ END IF;
92+ IF artifact_row.ocirecipe IS NOT NULL THEN
93+ PERFORM ocirecipe_denorm_access(artifact_row.ocirecipe);
94+ END IF;
95+ IF artifact_row.vulnerability IS NOT NULL THEN
96+ PERFORM vulnerability_denorm_access(artifact_row.vulnerability);
97+ END IF;
98+ RETURN;
99+END;
100+$$;
101+
102+COMMENT ON FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer) IS
103+ 'Denormalize the policy access and artifact grants to bugs, branches, git repositories, snaps, specifications, ocirecipes, and vulnerabilities.';
104+
105+-- A trigger to handle vulnerability.information_type changes.
106+CREATE OR REPLACE FUNCTION vulnerability_maintain_access_cache_trig() RETURNS trigger
107+ LANGUAGE plpgsql as $$
108+BEGIN
109+ PERFORM vulnerability_denorm_access(NEW.id);
110+ RETURN NULL;
111+END;
112+$$;
113+
114+CREATE TRIGGER vulnerability_maintain_access_cache
115+ AFTER INSERT OR UPDATE OF information_type ON Vulnerability
116+ FOR EACH ROW EXECUTE PROCEDURE vulnerability_maintain_access_cache_trig();
117+
118+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 02, 0);
119diff --git a/database/schema/security.cfg b/database/schema/security.cfg
120index 56eb404..a7f801b 100644
121--- a/database/schema/security.cfg
122+++ b/database/schema/security.cfg
123@@ -92,6 +92,7 @@ public.valid_keyid(text) = EXECUTE
124 public.valid_name(text) = EXECUTE
125 public.valid_regexp(text) = EXECUTE
126 public.version_sort_key(text) = EXECUTE
127+public.vulnerability_denorm_access(integer) = EXECUTE
128 # BugSummary trigger functions and helpers.
129 public.bug_summary_dec(bugsummary) =
130 public.bug_summary_inc(bugsummary) =
131@@ -355,6 +356,7 @@ public.vote = SELECT, INSERT, UPDATE
132 public.votecast = SELECT, INSERT
133 public.vulnerability = SELECT, INSERT, UPDATE, DELETE
134 public.vulnerabilityactivity = SELECT, INSERT, UPDATE, DELETE
135+public.vulnerabilitysubscription = SELECT, INSERT, UPDATE, DELETE
136 public.webhook = SELECT, INSERT, UPDATE, DELETE
137 public.webhookjob = SELECT, INSERT, UPDATE, DELETE
138 public.wikiname = SELECT, INSERT, UPDATE, DELETE
139@@ -2173,6 +2175,7 @@ public.snapsubscription = SELECT, UPDATE, DELETE
140 public.specification = SELECT
141 public.specificationsubscription = SELECT, DELETE
142 public.teamparticipation = SELECT
143+public.vulnerabilitysubscription = SELECT, UPDATE, DELETE
144 type=user
145
146 [upgrade-branches]
147@@ -2466,6 +2469,7 @@ public.vote = SELECT, UPDATE
148 public.votecast = SELECT, UPDATE
149 public.vulnerability = SELECT, UPDATE
150 public.vulnerabilityactivity = SELECT, UPDATE
151+public.vulnerabilitysubscription = SELECT, UPDATE, DELETE
152 public.webhook = SELECT, UPDATE
153 public.wikiname = SELECT, UPDATE
154 public.xref = SELECT, UPDATE
155diff --git a/lib/lp/registry/personmerge.py b/lib/lp/registry/personmerge.py
156index 9dd3dba..66b19b3 100644
157--- a/lib/lp/registry/personmerge.py
158+++ b/lib/lp/registry/personmerge.py
159@@ -893,6 +893,24 @@ def _mergeOCIRecipeSubscription(cur, from_id, to_id):
160 )
161
162
163+def _mergeVulnerabilitySubscription(cur, from_id, to_id):
164+ # Update only the VulnerabilitySubscription that will not conflict.
165+ cur.execute('''
166+ UPDATE VulnerabilitySubscription
167+ SET person=%(to_id)d
168+ WHERE person=%(from_id)d AND vulnerability NOT IN
169+ (
170+ SELECT vulnerability
171+ FROM VulnerabilitySubscription
172+ WHERE person = %(to_id)d
173+ )
174+ ''' % vars())
175+ # and delete those left over.
176+ cur.execute('''
177+ DELETE FROM VulnerabilitySubscription WHERE person=%(from_id)d
178+ ''' % vars())
179+
180+
181 def _mergeCharmRecipe(cur, from_person, to_person):
182 # This shouldn't use removeSecurityProxy.
183 recipes = getUtility(ICharmRecipeSet).findByOwner(from_person)
184@@ -1162,6 +1180,9 @@ def merge_people(from_person, to_person, reviewer, delete=False):
185 _mergeCharmRecipe(cur, from_id, to_id)
186 skip.append(("charmrecipe", "owner"))
187
188+ _mergeVulnerabilitySubscription(cur, from_id, to_id)
189+ skip.append(('vulnerabilitysubscription', 'person'))
190+
191 # Sanity check. If we have a reference that participates in a
192 # UNIQUE index, it must have already been handled by this point.
193 # We can tell this by looking at the skip list.

Subscribers

People subscribed via source and target branches

to status/vote changes: