Dependency-Track GHSA Cleanup

ghsa-cleanup.sql

CREATE FUNCTION "delete_ghsa_vulnerabilities"() RETURNS BOOLEAN
    LANGUAGE "plpgsql"
AS
$$
DECLARE "v_vulnerability_id" BIGINT;
BEGIN
    FOR "v_vulnerability_id" IN SELECT "ID" FROM "VULNERABILITY" WHERE "VULNID" LIKE 'GHSA-%'
        LOOP
            IF "v_vulnerability_id" IS NOT NULL THEN
                DELETE FROM "AFFECTEDVERSIONATTRIBUTION"
                    WHERE "VULNERABILITY" = "v_vulnerability_id";
                DELETE FROM "ANALYSISCOMMENT" 
                    USING "ANALYSIS"
                    WHERE "ANALYSISCOMMENT"."ANALYSIS_ID" = "ANALYSIS"."ID"
                        AND "ANALYSIS"."VULNERABILITY_ID" = "v_vulnerability_id";
                DELETE FROM "ANALYSIS" 
                    WHERE "VULNERABILITY_ID" = "v_vulnerability_id";
                DELETE FROM "COMPONENTS_VULNERABILITIES" 
                    WHERE "VULNERABILITY_ID" = "v_vulnerability_id";
                DELETE FROM "SERVICECOMPONENTS_VULNERABILITIES"
                    WHERE "VULNERABILITY_ID" = "v_vulnerability_id";
                DELETE FROM "FINDINGATTRIBUTION" 
                    WHERE "VULNERABILITY_ID" = "v_vulnerability_id";
                DELETE FROM "VULNERABLESOFTWARE_VULNERABILITIES" 
                    WHERE "VULNERABILITY_ID" = "v_vulnerability_id";
                DELETE FROM "VULNERABILITY" 
                    WHERE "ID" = "v_vulnerability_id";
            END IF;
        END LOOP;
    RETURN TRUE;
END;
$$;

BEGIN;
SELECT "delete_ghsa_vulnerabilities"();
COMMIT;
DROP FUNCTION "delete_ghsa_vulnerabilities";