Add DB migration for CISA Known Vulnerabilities

[freeqaz]

Will likely add the ingester in another commit. Just getting this up for review before I start digging into that.

[github-actions[bot]]

Hasura Semantic Diff

Hasura config files have changed. This comment shows which fields have changed ignoring formatting.

Click to expand!

``` (root level) + one list entry added: - "!include vulnerability_cisa_known_exploited.yaml" (root level) + two map entries added: table: name: cisa_known_exploited schema: vulnerability computed_fields: - name: vulnerability comment: "Vulnerability referenced by the known exploited vulnerability." definition: function: name: cisa_known_exploited_vulnerability schema: vulnerability table_argument: known_exploited (root level) + one map entry added: computed_fields: - name: cisa_known_exploited definition: function: name: vulnerability_cisa_known_exploited schema: vulnerability table_argument: vulnerability lunatrace-custom.permissions - three list entries removed: - role: user definition: schema: | scalar JSON scalar UUID type AuthenticatedRepoCloneUrlOutput { url: String } type Mutation { presignManifestUpload(project_id: UUID!): PresignedUrlResponse } type PresignedUrlResponse { bucket: String! headers: JSON! key: String! url: String! } type Query { authenticatedRepoCloneUrl(repoGithubId: Int!): AuthenticatedRepoCloneUrlOutput fakeQueryToHackHasuraBeingABuggyMess: String sbomUrl(buildId: UUID!): String } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } type UploadUrl { headers: JSON! url: String! } - role: service definition: schema: | scalar JSON scalar UUID type AuthenticatedRepoCloneUrlOutput { url: String } type Mutation { presignManifestUpload(project_id: UUID!): PresignedUrlResponse } type PresignedUrlResponse { bucket: String! headers: JSON! key: String! url: String! } type Query { authenticatedRepoCloneUrl(repoGithubId: Int!): AuthenticatedRepoCloneUrlOutput fakeQueryToHackHasuraBeingABuggyMess: String presignSbomUpload(orgId: UUID!, buildId: UUID!): SbomUploadUrlOutput sbomUrl(buildId: UUID!): String } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } type UploadUrl { headers: JSON! url: String! } input SbomUploadUrlInput { orgId: UUID! projectId: UUID! } - role: cli definition: schema: | scalar JSON scalar UUID type Query { presignSbomUpload(orgId: UUID!, buildId: UUID!): SbomUploadUrlOutput } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } type UploadUrl { headers: JSON! url: String! } + three list entries added: - role: user definition: schema: | scalar JSON scalar UUID type AuthenticatedRepoCloneUrlOutput { url: String } type Mutation { presignManifestUpload(project_id: UUID!): PresignedUrlResponse } type PresignedUrlResponse { bucket: String! headers: JSON! key: String! url: String! } type Query { authenticatedRepoCloneUrl(repoGithubId: Int!): AuthenticatedRepoCloneUrlOutput fakeQueryToHackHasuraBeingABuggyMess: String sbomUrl(buildId: UUID!): String } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } type UploadUrl { headers: JSON! url: String! } - role: service definition: schema: | scalar JSON scalar UUID type AuthenticatedRepoCloneUrlOutput { url: String } type Mutation { presignManifestUpload(project_id: UUID!): PresignedUrlResponse } type PresignedUrlResponse { bucket: String! headers: JSON! key: String! url: String! } type Query { authenticatedRepoCloneUrl(repoGithubId: Int!): AuthenticatedRepoCloneUrlOutput fakeQueryToHackHasuraBeingABuggyMess: String presignSbomUpload(orgId: UUID!, buildId: UUID!): SbomUploadUrlOutput sbomUrl(buildId: UUID!): String } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } type UploadUrl { headers: JSON! url: String! } input SbomUploadUrlInput { orgId: UUID! projectId: UUID! } - role: cli definition: schema: | scalar JSON scalar UUID type Query { presignSbomUpload(orgId: UUID!, buildId: UUID!): SbomUploadUrlOutput } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } type UploadUrl { headers: JSON! url: String! } diff --git a/lunatrace/bsl/hasura/migrations/lunatrace/1672788403469_add_cisa_known_exploited_vulnerabilities/down.sql b/lunatrace/bsl/hasura/migrations/lunatrace/1672788403469_add_cisa_known_exploited_vulnerabilities/down.sql new file mode 100644 index 00000000..5ce32bb1 --- /dev/null +++ b/lunatrace/bsl/hasura/migrations/lunatrace/1672788403469_add_cisa_known_exploited_vulnerabilities/down.sql @@ -0,0 +1,5 @@ + +DROP TABLE IF EXISTS vulnerability.cisa_known_exploited CASCADE; +DROP INDEX IF EXISTS vulnerability_equivalent_b_idx; +DROP FUNCTION vulnerability.cisa_known_exploited_vulnerability; +DROP FUNCTION vulnerability.vulnerability_cisa_known_exploited; diff --git a/lunatrace/bsl/hasura/migrations/lunatrace/1672788403469_add_cisa_known_exploited_vulnerabilities/up.sql b/lunatrace/bsl/hasura/migrations/lunatrace/1672788403469_add_cisa_known_exploited_vulnerabilities/up.sql new file mode 100644 index 00000000..83e8ffa4 --- /dev/null +++ b/lunatrace/bsl/hasura/migrations/lunatrace/1672788403469_add_cisa_known_exploited_vulnerabilities/up.sql @@ -0,0 +1,37 @@ +-- Indexes to speed up EPSS inserter +CREATE INDEX IF NOT EXISTS vulnerability_equivalent_b_idx ON vulnerability.equivalent (b); + +CREATE INDEX IF NOT EXISTS vulnerability_vulnerability_source_id_idx ON vulnerability.vulnerability (source_id); + +-- Table to hold the CISA Known Exploited vulnerabilities +CREATE TABLE IF NOT EXISTS vulnerability.cisa_known_exploited ( + "id" uuid NOT NULL DEFAULT gen_random_uuid(), + cve TEXT UNIQUE, + vendor_project text NOT NULL, + product text NOT NULL, + vulnerability_name text NOT NULL, + date_added date NOT NULL, + short_description text NOT NULL, + required_action text NOT NULL, + due_date date NOT NULL, + notes text NOT NULL, + PRIMARY KEY ("id"), + CHECK (cve LIKE 'CVE-%') +); + +CREATE OR REPLACE FUNCTION vulnerability.cisa_known_exploited_vulnerability(known_exploited vulnerability.cisa_known_exploited) + RETURNS SETOF vulnerability.vulnerability AS $$ + SELECT * + FROM vulnerability.vulnerability + WHERE source_id = known_exploited.cve +$$ LANGUAGE sql STABLE; + +CREATE OR REPLACE FUNCTION vulnerability.vulnerability_cisa_known_exploited(vulnerability vulnerability.vulnerability) + RETURNS SETOF vulnerability.cisa_known_exploited + LANGUAGE sql + STABLE +AS $function$ + SELECT * + FROM vulnerability.cisa_known_exploited + WHERE cve = vulnerability.source_id +$function$ ```

[freeqaz]

Made the change your requested in my other branch (since that has actual code with SQL queries). Those changes will get landed into this PR before they are deployed on master :)