diff --git a/config/gitlab_loose_foreign_keys.yml b/config/gitlab_loose_foreign_keys.yml index 63274dd38ba573a012d72f5e2c840b650767f999..29ae075d5809d9bf07d69a25756663a5e84afb77 100644 --- a/config/gitlab_loose_foreign_keys.yml +++ b/config/gitlab_loose_foreign_keys.yml @@ -621,6 +621,14 @@ vulnerabilities: - table: projects column: project_id on_delete: async_delete +vulnerability_archived_records: + - table: projects + column: project_id + on_delete: async_delete +vulnerability_archives: + - table: projects + column: project_id + on_delete: async_delete vulnerability_export_parts: - table: organizations column: organization_id diff --git a/db/docs/vulnerability_archived_records.yml b/db/docs/vulnerability_archived_records.yml new file mode 100644 index 0000000000000000000000000000000000000000..0bc44da1682f8042dc27049ea8c86383f31f8705 --- /dev/null +++ b/db/docs/vulnerability_archived_records.yml @@ -0,0 +1,12 @@ +--- +table_name: vulnerability_archived_records +classes: +- Vulnerabilities::ArchivedRecord +feature_categories: +- vulnerability_management +description: Stores the data of archived vulnerability records. +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/179569 +milestone: '17.9' +gitlab_schema: gitlab_sec +sharding_key: + project_id: projects diff --git a/db/docs/vulnerability_archives.yml b/db/docs/vulnerability_archives.yml new file mode 100644 index 0000000000000000000000000000000000000000..b165997469d6705705f4f672f396aa5e69585262 --- /dev/null +++ b/db/docs/vulnerability_archives.yml @@ -0,0 +1,12 @@ +--- +table_name: vulnerability_archives +classes: +- Vulnerabilities::Archive +feature_categories: +- vulnerability_management +description: Stores the archive information of vulnerabilities for projects. +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/179569 +milestone: '17.9' +gitlab_schema: gitlab_sec +sharding_key: + project_id: projects diff --git a/db/migrate/20250129102358_create_vulnerability_archives.rb b/db/migrate/20250129102358_create_vulnerability_archives.rb new file mode 100644 index 0000000000000000000000000000000000000000..6429346fac30ce441d30e6c118542a7a6a405682 --- /dev/null +++ b/db/migrate/20250129102358_create_vulnerability_archives.rb @@ -0,0 +1,19 @@ +# frozen_string_literal: true + +class CreateVulnerabilityArchives < Gitlab::Database::Migration[2.2] + milestone '17.9' + + def change + create_table :vulnerability_archives do |t| # rubocop:disable Migration/EnsureFactoryForTable -- false positive + t.timestamps_with_timezone null: false + + t.bigint :project_id, null: false + t.integer :archived_records_count, null: false, default: 0 + t.date :date, null: false + + t.index %i[project_id date], unique: true + + t.check_constraint 'archived_records_count >= 0' + end + end +end diff --git a/db/migrate/20250129125636_create_vulnerability_archived_records.rb b/db/migrate/20250129125636_create_vulnerability_archived_records.rb new file mode 100644 index 0000000000000000000000000000000000000000..eeb708b2c228716c7412107f131405683495b870 --- /dev/null +++ b/db/migrate/20250129125636_create_vulnerability_archived_records.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true + +class CreateVulnerabilityArchivedRecords < Gitlab::Database::Migration[2.2] + milestone '17.9' + + VULNERABILITY_ID_INDEX_NAME = 'index_vulnerability_archived_records_on_vulnerability_id' + + def change + create_table :vulnerability_archived_records do |t| # rubocop:disable Migration/EnsureFactoryForTable -- false positive + t.timestamps_with_timezone null: false + + t.bigint :project_id, null: false, index: true + t.references :archive, null: false, foreign_key: { on_delete: :cascade, to_table: 'vulnerability_archives' } + t.bigint :vulnerability_identifier, null: false, index: { name: VULNERABILITY_ID_INDEX_NAME, unique: true } + t.jsonb :data, null: false, default: {} + end + end +end diff --git a/db/schema_migrations/20250129102358 b/db/schema_migrations/20250129102358 new file mode 100644 index 0000000000000000000000000000000000000000..03bdd669f075fb53792ae336d3ec9758da6ef28e --- /dev/null +++ b/db/schema_migrations/20250129102358 @@ -0,0 +1 @@ +0d64f398983dbf2880ff98f041a9020d962cfcc3abe4da3f6d0757840e055d84 \ No newline at end of file diff --git a/db/schema_migrations/20250129125636 b/db/schema_migrations/20250129125636 new file mode 100644 index 0000000000000000000000000000000000000000..200160d53308bf2d380b7db64dbc04f18473abc0 --- /dev/null +++ b/db/schema_migrations/20250129125636 @@ -0,0 +1 @@ +1a9d3a2837c0d01ac81f675287ce578814bb5072008721e3a290b3b913d7374a \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 659623c12c32084f7aa0a68f9f41354986605956..75fe04f65ada2305120b86174ccfeca741b48c2b 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -22594,6 +22594,44 @@ CREATE SEQUENCE vulnerabilities_id_seq ALTER SEQUENCE vulnerabilities_id_seq OWNED BY vulnerabilities.id; +CREATE TABLE vulnerability_archived_records ( + id bigint NOT NULL, + created_at timestamp with time zone NOT NULL, + updated_at timestamp with time zone NOT NULL, + project_id bigint NOT NULL, + archive_id bigint NOT NULL, + vulnerability_identifier bigint NOT NULL, + data jsonb DEFAULT '{}'::jsonb NOT NULL +); + +CREATE SEQUENCE vulnerability_archived_records_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE vulnerability_archived_records_id_seq OWNED BY vulnerability_archived_records.id; + +CREATE TABLE vulnerability_archives ( + id bigint NOT NULL, + created_at timestamp with time zone NOT NULL, + updated_at timestamp with time zone NOT NULL, + project_id bigint NOT NULL, + archived_records_count integer DEFAULT 0 NOT NULL, + date date NOT NULL, + CONSTRAINT chk_rails_6b9e2d707f CHECK ((archived_records_count >= 0)) +); + +CREATE SEQUENCE vulnerability_archives_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE vulnerability_archives_id_seq OWNED BY vulnerability_archives.id; + CREATE TABLE vulnerability_export_parts ( id bigint NOT NULL, vulnerability_export_id bigint NOT NULL, @@ -25567,6 +25605,10 @@ ALTER TABLE ONLY vs_code_settings ALTER COLUMN id SET DEFAULT nextval('vs_code_s ALTER TABLE ONLY vulnerabilities ALTER COLUMN id SET DEFAULT nextval('vulnerabilities_id_seq'::regclass); +ALTER TABLE ONLY vulnerability_archived_records ALTER COLUMN id SET DEFAULT nextval('vulnerability_archived_records_id_seq'::regclass); + +ALTER TABLE ONLY vulnerability_archives ALTER COLUMN id SET DEFAULT nextval('vulnerability_archives_id_seq'::regclass); + ALTER TABLE ONLY vulnerability_export_parts ALTER COLUMN id SET DEFAULT nextval('vulnerability_export_parts_id_seq'::regclass); ALTER TABLE ONLY vulnerability_exports ALTER COLUMN id SET DEFAULT nextval('vulnerability_exports_id_seq'::regclass); @@ -28572,6 +28614,12 @@ ALTER TABLE ONLY vs_code_settings ALTER TABLE ONLY vulnerabilities ADD CONSTRAINT vulnerabilities_pkey PRIMARY KEY (id); +ALTER TABLE ONLY vulnerability_archived_records + ADD CONSTRAINT vulnerability_archived_records_pkey PRIMARY KEY (id); + +ALTER TABLE ONLY vulnerability_archives + ADD CONSTRAINT vulnerability_archives_pkey PRIMARY KEY (id); + ALTER TABLE ONLY vulnerability_export_parts ADD CONSTRAINT vulnerability_export_parts_pkey PRIMARY KEY (id); @@ -34725,6 +34773,14 @@ CREATE INDEX index_vulnerabilities_project_id_and_id_on_default_branch ON vulner CREATE INDEX index_vulnerabilities_project_id_state_severity_default_branch ON vulnerabilities USING btree (project_id, state, severity, present_on_default_branch); +CREATE INDEX index_vulnerability_archived_records_on_archive_id ON vulnerability_archived_records USING btree (archive_id); + +CREATE INDEX index_vulnerability_archived_records_on_project_id ON vulnerability_archived_records USING btree (project_id); + +CREATE UNIQUE INDEX index_vulnerability_archived_records_on_vulnerability_id ON vulnerability_archived_records USING btree (vulnerability_identifier); + +CREATE UNIQUE INDEX index_vulnerability_archives_on_project_id_and_date ON vulnerability_archives USING btree (project_id, date); + CREATE INDEX index_vulnerability_export_parts_on_organization_id ON vulnerability_export_parts USING btree (organization_id); CREATE INDEX index_vulnerability_export_parts_on_vulnerability_export_id ON vulnerability_export_parts USING btree (vulnerability_export_id); @@ -40391,6 +40447,9 @@ ALTER TABLE ONLY incident_management_oncall_participants ALTER TABLE ONLY work_item_parent_links ADD CONSTRAINT fk_rails_601d5bec3a FOREIGN KEY (work_item_id) REFERENCES issues(id) ON DELETE CASCADE; +ALTER TABLE ONLY vulnerability_archived_records + ADD CONSTRAINT fk_rails_601e008d4b FOREIGN KEY (archive_id) REFERENCES vulnerability_archives(id) ON DELETE CASCADE; + ALTER TABLE ONLY system_access_microsoft_graph_access_tokens ADD CONSTRAINT fk_rails_604908851f FOREIGN KEY (system_access_microsoft_application_id) REFERENCES system_access_microsoft_applications(id) ON DELETE CASCADE; diff --git a/ee/app/models/vulnerabilities/archive.rb b/ee/app/models/vulnerabilities/archive.rb new file mode 100644 index 0000000000000000000000000000000000000000..48758d2a23da9a62539fd0db67421744889e9021 --- /dev/null +++ b/ee/app/models/vulnerabilities/archive.rb @@ -0,0 +1,19 @@ +# frozen_string_literal: true + +module Vulnerabilities + class Archive < Gitlab::Database::SecApplicationRecord + self.table_name = 'vulnerability_archives' + + belongs_to :project, optional: false + has_many :archived_records, class_name: 'Vulnerabilities::ArchivedRecord' + + validates :date, presence: true, uniqueness: { scope: :project_id } + validates :archived_records_count, numericality: { only_integer: true, greater_than_or_equal_to: 0 } + + def date=(value) + value = value.beginning_of_month if value + + super + end + end +end diff --git a/ee/app/models/vulnerabilities/archived_record.rb b/ee/app/models/vulnerabilities/archived_record.rb new file mode 100644 index 0000000000000000000000000000000000000000..fc853da9a0d2e06c11fb2952729a9c27d1846961 --- /dev/null +++ b/ee/app/models/vulnerabilities/archived_record.rb @@ -0,0 +1,13 @@ +# frozen_string_literal: true + +module Vulnerabilities + class ArchivedRecord < Gitlab::Database::SecApplicationRecord + self.table_name = 'vulnerability_archived_records' + + belongs_to :project, optional: false + belongs_to :archive, class_name: 'Vulnerabilities::Archive', optional: false + + validates :vulnerability_identifier, presence: true, uniqueness: true + validates :data, presence: true, json_schema: { filename: 'archived_record_data' } + end +end diff --git a/ee/app/validators/json_schemas/archived_record_data.json b/ee/app/validators/json_schemas/archived_record_data.json new file mode 100644 index 0000000000000000000000000000000000000000..08572ddb5b685aa393117aabf190dda05ffeddca --- /dev/null +++ b/ee/app/validators/json_schemas/archived_record_data.json @@ -0,0 +1,86 @@ +{ + "$schema": "http://json-schema.org/draft-07/schema#", + "type": "object", + "title": "Vulnerabilities::ArchivedRecord#data schema", + "description": "The schema validates the content of the Vulnerabilities::ArchivedRecord#data attribute", + "additionalProperties": false, + "properties": { + "report_type": { + "type": "string" + }, + "scanner": { + "type": "string" + }, + "state": { + "type": "string" + }, + "title": { + "type": "string" + }, + "description": { + "type": "string" + }, + "severity": { + "type": "string" + }, + "cve_value": { + "type": "string" + }, + "cwe_value": { + "type": "string" + }, + "created_at": { + "type": "date-time" + }, + "location": { + "type": "object" + }, + "resolved_on_default_branch": { + "type": "boolean" + }, + "notes_summary": { + "type": "string" + }, + "full_path": { + "type": "string" + }, + "cvss": { + "type": "array", + "items": { + "type": "object", + "properties": { + "vendor": { + "type": "string" + }, + "vector": { + "type": "string" + } + }, + "required": [ + "vendor", + "vector" + ] + } + }, + "dismissal_reason": { + "type": "string" + } + }, + "required": [ + "report_type", + "scanner", + "state", + "title", + "description", + "severity", + "cve_value", + "cwe_value", + "created_at", + "location", + "resolved_on_default_branch", + "notes_summary", + "full_path", + "cvss", + "dismissal_reason" + ] +} diff --git a/ee/spec/factories/vulnerabilities/archived_records.rb b/ee/spec/factories/vulnerabilities/archived_records.rb new file mode 100644 index 0000000000000000000000000000000000000000..3428c9fb3cc2ac83d663263d3e5cc15dfdadf282 --- /dev/null +++ b/ee/spec/factories/vulnerabilities/archived_records.rb @@ -0,0 +1,10 @@ +# frozen_string_literal: true + +FactoryBot.define do + factory :vulnerability_archived_record, class: 'Vulnerabilities::ArchivedRecord' do + project + archive factory: :vulnerability_archive + sequence(:vulnerability_identifier) + data { {} } + end +end diff --git a/ee/spec/factories/vulnerabilities/archives.rb b/ee/spec/factories/vulnerabilities/archives.rb new file mode 100644 index 0000000000000000000000000000000000000000..f8a364c9cf8ef101ef0b15cea26b9f7974c2be2e --- /dev/null +++ b/ee/spec/factories/vulnerabilities/archives.rb @@ -0,0 +1,8 @@ +# frozen_string_literal: true + +FactoryBot.define do + factory :vulnerability_archive, class: 'Vulnerabilities::Archive' do + project + date { Time.zone.today } + end +end diff --git a/ee/spec/models/vulnerabilities/archive_spec.rb b/ee/spec/models/vulnerabilities/archive_spec.rb new file mode 100644 index 0000000000000000000000000000000000000000..d4ff4536653f5c35ead080f2113dbd4389bd1437 --- /dev/null +++ b/ee/spec/models/vulnerabilities/archive_spec.rb @@ -0,0 +1,44 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Vulnerabilities::Archive, feature_category: :vulnerability_management do + subject(:archive) { build(:vulnerability_archive) } + + it_behaves_like 'cleanup by a loose foreign key' do + let_it_be(:parent) { create(:project) } + let_it_be(:model) { create(:vulnerability_archive, project: parent) } + end + + it { is_expected.to belong_to(:project).required } + it { is_expected.to have_many(:archived_records) } + + describe 'validations' do + it { is_expected.to validate_uniqueness_of(:date).scoped_to(:project_id) } + it { is_expected.to validate_numericality_of(:archived_records_count).only_integer.is_greater_than_or_equal_to(0) } + end + + describe '#date=' do + before do + archive.date = nil + end + + around do |example| + travel_to('29/01/2025') { example.run } + end + + context 'when the given value is nil' do + it 'does not change the value from nil' do + expect { archive.date = nil }.not_to change { archive.date }.from(nil) + end + end + + context 'when the given value is not nil' do + let(:expected_date) { Date.parse('01/01/2025') } + + it 'assigns the beginning of month of given date' do + expect { archive.date = Time.zone.today }.to change { archive.date }.to(expected_date) + end + end + end +end diff --git a/ee/spec/models/vulnerabilities/archived_record_spec.rb b/ee/spec/models/vulnerabilities/archived_record_spec.rb new file mode 100644 index 0000000000000000000000000000000000000000..7af512ec6278cc8f6cccaa49271d9578c8da1165 --- /dev/null +++ b/ee/spec/models/vulnerabilities/archived_record_spec.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Vulnerabilities::ArchivedRecord, feature_category: :vulnerability_management do + subject { build(:vulnerability_archived_record) } + + it { is_expected.to belong_to(:project).required } + it { is_expected.to belong_to(:archive).class_name('Vulnerabilities::Archive').required } + + describe 'validations' do + it { is_expected.to validate_presence_of(:vulnerability_identifier) } + it { is_expected.to validate_uniqueness_of(:vulnerability_identifier) } + it { is_expected.to validate_presence_of(:data) } + end +end