launchpad-reviewers team mailing list archive

[Guruprasad <mp+427425@xxxxxxxxxxxxxxxxxx>]

Guruprasad has proposed merging ~lgp171188/launchpad:merge-db-stable into launchpad:master.

Commit message:
Merge db-stable 14771964f7 Add the VulnerabilitySubscription table

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~lgp171188/launchpad/+git/launchpad/+merge/427425
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~lgp171188/launchpad:merge-db-stable into launchpad:master.

diff --git a/database/schema/patch-2211-02-0.sql b/database/schema/patch-2211-02-0.sql
new file mode 100644
index 0000000..8f7d622
--- /dev/null
+++ b/database/schema/patch-2211-02-0.sql
@@ -0,0 +1,112 @@
+-- Copyright 2022 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+ALTER TABLE Vulnerability
+    ADD COLUMN access_policy integer,
+    ADD COLUMN access_grants integer[];
+
+CREATE TABLE VulnerabilitySubscription (
+    id serial PRIMARY KEY,
+    person integer REFERENCES Person NOT NULL,
+    vulnerability integer REFERENCES Vulnerability NOT NULL,
+    date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL,
+    subscribed_by integer REFERENCES Person NOT NULL
+);
+
+COMMENT ON TABLE VulnerabilitySubscription IS 'Person subscription for Vulnerabilities.';
+COMMENT ON COLUMN VulnerabilitySubscription.person IS 'The person subscribing to the vulnerability.';
+COMMENT ON COLUMN VulnerabilitySubscription.vulnerability IS 'The vulnerability being subscribed to.';
+COMMENT ON COLUMN VulnerabilitySubscription.date_created IS 'The date when the subscription was created.';
+COMMENT ON COLUMN VulnerabilitySubscription.subscribed_by IS 'The person who created the subscription.';
+
+CREATE UNIQUE INDEX vulnerabilitysubscription__person__vulnerability__key
+    ON VulnerabilitySubscription (person, vulnerability);
+
+CREATE INDEX vulnerabilitysubscription__vulnerability__idx
+    ON VulnerabilitySubscription (vulnerability);
+
+CREATE INDEX vulnerabilitysubscription__subscribed_by__idx
+    ON VulnerabilitySubscription (subscribed_by);
+
+ALTER TABLE AccessArtifact
+    ADD COLUMN vulnerability integer REFERENCES Vulnerability;
+
+
+ALTER TABLE AccessArtifact DROP CONSTRAINT has_artifact;
+ALTER TABLE AccessArtifact
+    ADD CONSTRAINT has_artifact CHECK (
+    (null_count(ARRAY[bug, branch, gitrepository, snap, specification, ocirecipe, vulnerability]) = 6)) NOT VALID;
+
+
+CREATE OR REPLACE FUNCTION vulnerability_denorm_access(vulnerability_id integer)
+    RETURNS void LANGUAGE plpgsql AS
+$$
+DECLARE
+    info_type integer;
+BEGIN
+    SELECT Vulnerability.information_type INTO info_type
+    FROM Vulnerability where id = vulnerability_id;
+
+    UPDATE Vulnerability
+        SET access_policy = policies[1], access_grants = grants
+        FROM
+            build_access_cache(
+                (SELECT id FROM accessartifact WHERE vulnerability = vulnerability_id),
+                info_type)
+            AS (policies integer[], grants integer[])
+        WHERE id = vulnerability_id;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer)
+    RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    artifact_row accessartifact%ROWTYPE;
+BEGIN
+    SELECT * INTO artifact_row FROM accessartifact WHERE id = artifact_id;
+    IF artifact_row.bug IS NOT NULL THEN
+        PERFORM bug_flatten_access(artifact_row.bug);
+    END IF;
+    IF artifact_row.branch IS NOT NULL THEN
+        PERFORM branch_denorm_access(artifact_row.branch);
+    END IF;
+    IF artifact_row.gitrepository IS NOT NULL THEN
+        PERFORM gitrepository_denorm_access(artifact_row.gitrepository);
+    END IF;
+    IF artifact_row.snap IS NOT NULL THEN
+        PERFORM snap_denorm_access(artifact_row.snap);
+    END IF;
+    IF artifact_row.specification IS NOT NULL THEN
+        PERFORM specification_denorm_access(artifact_row.specification);
+    END IF;
+    IF artifact_row.ocirecipe IS NOT NULL THEN
+        PERFORM ocirecipe_denorm_access(artifact_row.ocirecipe);
+    END IF;
+    IF artifact_row.vulnerability IS NOT NULL THEN
+        PERFORM vulnerability_denorm_access(artifact_row.vulnerability);
+    END IF;
+    RETURN;
+END;
+$$;
+
+COMMENT ON FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer) IS
+    'Denormalize the policy access and artifact grants to bugs, branches, git repositories, snaps, specifications, ocirecipes, and vulnerabilities.';
+
+-- A trigger to handle vulnerability.information_type changes.
+CREATE OR REPLACE FUNCTION vulnerability_maintain_access_cache_trig() RETURNS trigger
+    LANGUAGE plpgsql as $$
+BEGIN
+    PERFORM vulnerability_denorm_access(NEW.id);
+    RETURN NULL;
+END;
+$$;
+
+CREATE TRIGGER vulnerability_maintain_access_cache
+    AFTER INSERT OR UPDATE OF information_type ON Vulnerability
+    FOR EACH ROW EXECUTE PROCEDURE vulnerability_maintain_access_cache_trig();
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 02, 0);
diff --git a/database/schema/security.cfg b/database/schema/security.cfg
index 56eb404..a7f801b 100644
--- a/database/schema/security.cfg
+++ b/database/schema/security.cfg
@@ -92,6 +92,7 @@ public.valid_keyid(text)                   = EXECUTE
 public.valid_name(text)                    = EXECUTE
 public.valid_regexp(text)                  = EXECUTE
 public.version_sort_key(text)              = EXECUTE
+public.vulnerability_denorm_access(integer) = EXECUTE
 # BugSummary trigger functions and helpers.
 public.bug_summary_dec(bugsummary)         =
 public.bug_summary_inc(bugsummary)         =
@@ -355,6 +356,7 @@ public.vote                             = SELECT, INSERT, UPDATE
 public.votecast                         = SELECT, INSERT
 public.vulnerability                    = SELECT, INSERT, UPDATE, DELETE
 public.vulnerabilityactivity            = SELECT, INSERT, UPDATE, DELETE
+public.vulnerabilitysubscription        = SELECT, INSERT, UPDATE, DELETE
 public.webhook                          = SELECT, INSERT, UPDATE, DELETE
 public.webhookjob                       = SELECT, INSERT, UPDATE, DELETE
 public.wikiname                         = SELECT, INSERT, UPDATE, DELETE
@@ -2173,6 +2175,7 @@ public.snapsubscription                 = SELECT, UPDATE, DELETE
 public.specification                    = SELECT
 public.specificationsubscription        = SELECT, DELETE
 public.teamparticipation                = SELECT
+public.vulnerabilitysubscription        = SELECT, UPDATE, DELETE
 type=user
 
 [upgrade-branches]
@@ -2466,6 +2469,7 @@ public.vote                             = SELECT, UPDATE
 public.votecast                         = SELECT, UPDATE
 public.vulnerability                    = SELECT, UPDATE
 public.vulnerabilityactivity            = SELECT, UPDATE
+public.vulnerabilitysubscription        = SELECT, UPDATE, DELETE
 public.webhook                          = SELECT, UPDATE
 public.wikiname                         = SELECT, UPDATE
 public.xref                             = SELECT, UPDATE
diff --git a/lib/lp/registry/personmerge.py b/lib/lp/registry/personmerge.py
index 9dd3dba..66b19b3 100644
--- a/lib/lp/registry/personmerge.py
+++ b/lib/lp/registry/personmerge.py
@@ -893,6 +893,24 @@ def _mergeOCIRecipeSubscription(cur, from_id, to_id):
     )
 
 
+def _mergeVulnerabilitySubscription(cur, from_id, to_id):
+    # Update only the VulnerabilitySubscription that will not conflict.
+    cur.execute('''
+        UPDATE VulnerabilitySubscription
+        SET person=%(to_id)d
+        WHERE person=%(from_id)d AND vulnerability NOT IN
+            (
+            SELECT vulnerability
+            FROM VulnerabilitySubscription
+            WHERE person = %(to_id)d
+            )
+    ''' % vars())
+    # and delete those left over.
+    cur.execute('''
+        DELETE FROM VulnerabilitySubscription WHERE person=%(from_id)d
+        ''' % vars())
+
+
 def _mergeCharmRecipe(cur, from_person, to_person):
     # This shouldn't use removeSecurityProxy.
     recipes = getUtility(ICharmRecipeSet).findByOwner(from_person)
@@ -1162,6 +1180,9 @@ def merge_people(from_person, to_person, reviewer, delete=False):
     _mergeCharmRecipe(cur, from_id, to_id)
     skip.append(("charmrecipe", "owner"))
 
+    _mergeVulnerabilitySubscription(cur, from_id, to_id)
+    skip.append(('vulnerabilitysubscription', 'person'))
+
     # Sanity check. If we have a reference that participates in a
     # UNIQUE index, it must have already been handled by this point.
     # We can tell this by looking at the skip list.