Get_reports via GMP fails with database issue

altjx · July 25, 2024, 10:38pm

I have been calling gmp.get_reports for quite some time now without any issues; however, recent changes to the greenbone community containers have started resulting in failures when calling this. Here’s what I’m seeing consistently across multiple environments:

greenbone-community-edition-pg-gvm-1               | 2024-07-26 12:21:33.197 UTC [11603] gvmd@gvmd ERROR:  column result_vt_epss.epss_severity does not exist at character 4671
greenbone-community-edition-pg-gvm-1               | 2024-07-26 12:21:33.197 UTC [11603] gvmd@gvmd STATEMENT:  WITH permissions_subject     AS (SELECT * FROM permissions         WHERE subject_location               = 0         AND ((subject_type = 'user'               AND subject                   = (SELECT id FROM users WHERE users.uuid = 'ed6da0d0-19a0-49d7-8508-e3335cde6b9e'))              OR (subject_type = 'group'                  AND subject                      IN (SELECT DISTINCT "group"                          FROM group_users                          WHERE "user"                                = (SELECT id FROM users WHERE users.uuid = 'ed6da0d0-19a0-49d7-8508-e3335cde6b9e')))              OR (subject_type = 'role'                  AND subject                      IN (SELECT DISTINCT role                          FROM role_users                          WHERE "user"                                = (SELECT id FROM users WHERE users.uuid = 'ed6da0d0-19a0-49d7-8508-e3335cde6b9e'))))),     super_on_users     AS (SELECT DISTINCT *         FROM (SELECT resource FROM permissions_subject               WHERE name = 'Super'               AND resource_type = 'user'               UNION               SELECT "user" FROM role_users               WHERE role                     IN (SELECT resource                         FROM permissions_subject                         WHERE name = 'Super'                         AND resource_type = 'role')               UNION               SELECT "user" FROM group_users               WHERE "group"                     IN (SELECT resource                         FROM permissions_subject                         WHERE name = 'Super'                         AND resource_type = 'group'))              AS all_users)SELECT results.id AS id, results.uuid AS uuid, nvts.name AS name, '' AS comment, date AS creation_time, date AS modification_time, date AS created, date AS modified, (SELECT name FROM users WHERE users.id = results.owner) AS _owner, results.owner, host, port AS location, nvt, severity_to_type (results.severity) AS original_type, severity_to_type (lateral_new_severity.new_severity) AS type, description, task, report AS report_rowid, nvts.cvss_base AS cvss_base, nvt_version, results.severity AS original_severity, lateral_new_severity.new_severity AS severity, nvts.name AS vulnerability, date, (SELECT uuid FROM reports WHERE id = report) AS report_id, nvts.solution_type AS solution_type, results.qod AS qod, results.qod_type, (CASE WHEN (hostname IS NULL)            OR (hostname = '') THEN (SELECT value FROM report_host_details       WHERE name = 'hostname'         AND report_host = (SELECT id FROM report_hosts                             WHERE report_hosts.host = results.host                            AND                            report_hosts.report = results.report)       LIMIT 1) ELSE hostname END) AS hostname, (SELECT uuid FROM tasks WHERE id = task) AS task_id, nvts.cve AS cve, path, (SELECT CASE WHEN host IS NULL             THEN NULL             ELSE (SELECT uuid FROM hosts                   WHERE id = (SELECT host FROM host_identifiers                               WHERE source_type = 'Report Host'                               AND name = 'ip'                               AND source_id                                   = (SELECT uuid                                      FROM reports                                      WHERE id = results.report)                               AND value = results.host                               LIMIT 1))             END), (SELECT CASE        WHEN EXISTS (SELECT * FROM notes                     WHERE (result = results.id                            OR (result = 0 AND nvt = results.nvt))                     AND (task = 0 OR task = results.task))        THEN 1        ELSE 0        END), (SELECT CASE        WHEN EXISTS (SELECT * FROM overrides                     WHERE (result = results.id                            OR (result = 0 AND nvt = results.nvt))                     AND (task = 0 OR task = results.task))        THEN 1        ELSE 0        END), (SELECT EXISTS (SELECT * FROM tickets                WHERE id IN (SELECT ticket FROM ticket_results                             WHERE result = results.id                             AND result_location                                 = 0))), (SELECT name FROM tasks WHERE tasks.id = task) AS task, nvts.summary, nvts.insight, nvts.affected, nvts.impact, nvts.solution, nvts.detection, nvts.family, nvts.tag, coalesce(lower(substring(description, '^Compliant:[\s]*([A-Z_]*)')),         'undefined') AS compliant, coalesce (result_vt_epss.epss_score, 0.0) AS epss_score, coalesce (result_vt_epss.epss_percentile, 0.0) AS epss_percentile, result_vt_epss.epss_cve AS epss_cve, coalesce (result_vt_epss.epss_severity, 0.0) AS epss_severity, coalesce (result_vt_epss.max_epss_score, 0.0) AS max_epss_score, coalesce (result_vt_epss.max_epss_percentile, 0.0) AS max_epss_percentile, result_vt_epss.max_epss_cve AS max_epss_cve, coalesce (result_vt_epss.max_epss_severity, 0.0) AS max_epss_severity, (ARRAY (SELECT name::text        FROM cert_bund_advs        WHERE id IN (SELECT adv_id FROM cert_bund_cves                     WHERE cve_name IN (SELECT ref_id                                        FROM vt_refs                                        WHERE vt_oid = results.nvt                                        AND type = 'cve'))        ORDER BY name DESC)), (ARRAY (SELECT name::text        FROM dfn_cert_advs        WHERE id IN (SELECT adv_id FROM dfn_cert_cves                     WHERE cve_name IN (SELECT ref_id                                        FROM vt_refs                                        WHERE vt_oid = results.nvt                                        AND type = 'cve'))        ORDER BY name DESC)) FROM results  LEFT OUTER JOIN result_vt_epss ON results.nvt = result_vt_epss.vt_id LEFT OUTER JOIN nvts ON results.nvt = nvts.oid , (SELECT   'user_zone'::text AS user_zone,   1 AS user_id,   0 AS override,   0 AS dynamic) AS opts, LATERAL (SELECT results.severity AS new_severity) AS lateral_new_severity WHERE  ((results.owner   = (SELECT id FROM users WHERE users.uuid = 'ed6da0d0-19a0-49d7-8508-e3335cde6b9e'))  OR EXISTS (SELECT * FROM permissions_subject             WHERE name = 'Super'             AND (resource = 0))  OR results.owner IN (SELECT *                     FROM super_on_users)  OR results.id IN (SELECT resource FROM permissions_subject  WHERE resource_type = 'result'  AND resource_location = 0  AND (t ())) OR results.task IN (SELECT resource FROM permissions_subject  WHERE resource_type = 'task'  AND (t ()))) AND (report = 1)  AND severity != -3.0 AND (results.qod >= CAST (70 AS INTEGER)) ORDER BY lower (nvts.name) ASC LIMIT ALL OFFSET 0;
greenbone-community-edition-gvmd-1                 | md manage:WARNING:2024-07-26 12h21.33 utc:8666: sql_exec_internal: PQexec failed: ERROR:  column result_vt_epss.epss_severity does not exist
greenbone-community-edition-gvmd-1                 | LINE 1: ...e, result_vt_epss.epss_cve AS epss_cve, coalesce (result_vt_...
greenbone-community-edition-gvmd-1                 |                                                              ^
greenbone-community-edition-gvmd-1                 |  (7)
greenbone-community-edition-gvmd-1                 | md manage:WARNING:2024-07-26 12h21.33 utc:8666: sql_exec_internal: SQL: WITH permissions_subject     AS (SELECT * FROM permissions         WHERE subject_location               = 0         AND ((subject_type = 'user'               AND subject                   = (SELECT id FROM users WHERE users.uuid = 'ed6da0d0-19a0-49d7-8508-e3335cde6b9e'))              OR (subject_type = 'group'                  AND subject                      IN (SELECT DISTINCT "group"                          FROM group_users                          WHERE "user"                                = (SELECT id FROM users WHERE users.uuid = 'ed6da0d0-19a0-49d7-8508-e3335cde6b9e')))              OR (subject_type = 'role'                  AND subject                      IN (SELECT DISTINCT role                          FROM role_users                          WHERE "user"                                = (SELECT id FROM users WHERE users.uuid = 'ed6da0d0-19a0-49d7-8508-e3335cde6b9e'))))),     super_on_users     AS (SELECT DISTINCT *         FROM (SELECT resource FROM permissions_subject               WHERE name = 'Super'               AND resource_type = 'user'               UNION               SELECT "user" FROM role_users               WHERE role                     IN (SELECT resource                         FROM permissions_subject                         WHERE name = 'Super'                         AND resource_type = 'role')               UNION               SELECT "user" FROM group_users               WHERE "group"                     IN (SELECT resource                         FROM permissions_subject                         WHERE name = 'Super'                         AND resource_type = 'group'))              AS all_users)SELECT results.id AS id, results.uuid AS uuid, nvts.name AS name, '' AS comment, date AS creation_time, date AS modification_time, date AS created, date AS modified, (SELECT name FROM users WHERE users.id = results.owner) AS _owner, results.owner, host, port AS location, nvt, severity_to_type (results.severity) AS original_type, severity_to_type (lateral_new_severity.new_severity) AS type, description, task, report AS report_rowid, nvts.cvss_base AS cvss_base, nvt_version, results.severity AS original_severity, lateral_new_severity.new_severity AS severity, nvts.name AS vulnerability, date, (SELECT uuid FROM reports WHERE id = report) AS report_id, nvts.solution_type AS solution_type, results.qod AS qod, results.qod_type, (CASE WHEN (hostname IS NULL)            OR (hostname = '') THEN (SELECT value FROM report_host_details       WHERE name = 'hostname'         AND report_host = (SELECT id FROM report_hosts                             WHERE report_hosts.host = results.host                            AND                            report_hosts.report = results.report)       LIMIT 1) ELSE hostname END) AS hostname, (SELECT uuid FROM tasks WHERE id = task) AS task_id, nvts.cve AS cve, path, (SELECT CASE WHEN host IS NULL             THEN NULL             ELSE (SELECT uuid FROM hosts                   WHERE id = (SELECT host FROM host_identifiers                               WHERE source_type = 'Report Host'                               AND name = 'ip'                               AND source_id                                   = (SELECT uuid                                      FROM reports                                      WHERE id = results.report)                               AND value = results.host                               LIMIT 1))             END), (SELECT CASE        WHEN EXISTS (SELECT * FROM notes                     WHERE (result = results.id                            OR (result = 0 AND nvt = results.nvt))                     AND (task = 0 OR task = results.task))        THEN 1        ELSE 0        END), (SELECT CASE        WHEN EXISTS (SELECT * FROM overrides                     WHERE (result = results.id                            OR (result = 0 AND nvt = results.nvt))                     AND (task = 0 OR task = results.task))        THEN 1        ELSE 0        END), (SELECT EXISTS (SELECT * FROM tickets                WHERE id IN (SELECT ticket FROM ticket_results                             WHERE result = results.id                             AND result_location                                 = 0))), (SELECT name FROM tasks WHERE tasks.id = task) AS task, nvts.summary, nvts.insight, nvts.affected, nvts.impact, nvts.solution, nvts.detection, nvts.family, nvts.tag, coalesce(lower(substring(description, '^Compliant:[\s]*([A-Z_]*)')),         'undefined') AS compliant, coalesce (result_vt_epss.epss_score, 0.0) AS epss_score, coalesce (result_vt_epss.epss_percentile, 0.0) AS epss_percentile, result_vt_epss.epss_cve AS epss_cve, coalesce (result_vt_epss.epss_severity, 0.0) AS epss_severity, coalesce (result_vt_epss.max_epss_score, 0.0) AS max_epss_score, coalesce (result_vt_epss.max_epss_percentile, 0.0) AS max_epss_percentile, result_vt_epss.max_epss_cve AS max_epss_cve, coalesce (result_vt_epss.max_epss_severity, 0.0) AS max_epss_severity, (ARRAY (SELECT name::text        FROM cert_bund_advs        WHERE id IN (SELECT adv_id FROM cert_bund_cves                     WHERE cve_name IN (SELECT ref_id                                        FROM vt_refs                                        WHERE vt_oid = results.nvt                                        AND type = 'cve'))        ORDER BY name DESC)), (ARRAY (SELECT name::text        FROM dfn_cert_advs        WHERE id IN (SELECT adv_id FROM dfn_cert_cves                     WHERE cve_name IN (SELECT ref_id                                        FROM vt_refs                                        WHERE vt_oid = results.nvt                                        AND type = 'cve'))        ORDER BY name DESC)) FROM results  LEFT OUTER JOIN result_vt_epss ON results.nvt = result_vt_epss.vt_id LEFT OUTER JOIN nvts ON results.nvt = nvts.oid , (SELECT   'user_zone'::text AS user_zone,   1 AS user_id,   0 AS override,   0 AS dynamic) AS opts, LATERAL (SELECT results.severity AS new_severity) AS lateral_new_severity WHERE  ((results.owner   = (SELECT id FROM users WHERE users.uuid = 'ed6da0d0-19a0-49d7-8508-e3335cde6b9e'))  OR EXISTS (SELECT * FROM permissions_subject             WHERE name = 'Super'             AND (resource = 0))  OR results.owner IN (SELECT *                     FROM super_on_users)  OR results.id IN (SELECT resource FROM permissions_subject  WHERE resource_type = 'result'  AND resource_location = 0  AND (t ())) OR results.task IN (SELECT resource FROM permissions_subject  WHERE resource_type = 'task'  AND (t ()))) AND (report = 1)  AND severity != -3.0 AND (results.qod >= CAST (70 AS INTEGER)) ORDER BY lower (nvts.name) ASC LIMIT ALL OFFSET 0;
greenbone-community-edition-gvmd-1                 | md manage:WARNING:2024-07-26 12h21.33 utc:8666: next: sql_exec_internal failed
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(+0x6dcda) [0x56229d2f6cda]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: /lib/x86_64-linux-gnu/libpthread.so.0(+0x13140) [0x7f740f805140]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x141) [0x7f740f3d9ce1]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: /lib/x86_64-linux-gnu/libc.so.6(abort+0x123) [0x7f740f3c3537]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(+0x6d5c3) [0x56229d2f65c3]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(+0xc82f4) [0x56229d3512f4]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(manage_send_report+0x25d) [0x56229d35d9dd]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(+0x12d8b3) [0x56229d3b68b3]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: /usr/lib/x86_64-linux-gnu/libglib-2.0.so.0(+0x55894) [0x7f740f6a0894]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: /usr/lib/x86_64-linux-gnu/libglib-2.0.so.0(g_markup_parse_context_parse+0x9f1) [0x7f740f6a1381]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(process_gmp_client_input+0x4b) [0x56229d3c551b]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(serve_gmp+0x3c7) [0x56229d2fade7]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(+0x6d9c6) [0x56229d2f69c6]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(+0x6df7a) [0x56229d2f6f7a]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(gvmd+0x1b50) [0x56229d2fa080]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xea) [0x7f740f3c4d0a]
greenbone-community-edition-gvmd-1                 | md   main:MESSAGE:2024-07-26 12h21.33 utc:8666: BACKTRACE: gvmd(_start+0x2a) [0x56229d2f663a]
greenbone-community-edition-gvmd-1                 | md manage:MESSAGE:2024-07-26 12h21.33 utc:8666: Received Aborted signal

I’m not exactly sure how to fix this. I run the same code on a daily basis and never had issues until a little less than 2 days ago.

Any tips would be greatly appreciated. I’m running the latest stable version of all the greenbone community containers.

rippledj · July 26, 2024, 4:41am

Hello, please don’t post screenshots of the errors. Can you pleaes reformat your post to follow the general guidelines?

altjx · July 26, 2024, 12:22pm

Fixed. Sorry about that.

leonardb · July 26, 2024, 1:07pm

I literally just joined the forum to search for this exact error.
I only set up the environment yesterday afternoon using the latest container

gsa-1    | libgvm util-Message: 12:52:25.804:    Error: Error on line 1 char 1: Document was empty or contained only whitespace
gsa-1    | 
gsa-1    | 
gsa-1    | (gsad:12): libgvm util-WARNING **: 12:52:25.804:    End error: Error on line 1 char 1: Document was empty or contained only whitespace
gsa-1    | 
gvmd-1   | md manage:WARNING:2024-07-26 12h52.25 utc:10459: sql_exec_internal: PQexec failed: ERROR:  column result_vt_epss.epss_severity does not exist
gvmd-1   | LINE 1: ...e, result_vt_epss.epss_cve AS epss_cve, coalesce (result_vt_...
gvmd-1   |                                                              ^

Greenbone Vulnerability Manager 23.8.0
Manager DB revision 256
Copyright (C) 2009-2021 Greenbone AG
License: AGPL-3.0-or-later
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

I’m, checking if this can be fixed with a minor modification to the materialized view

sudo docker exec -it greenbone-community-edition-pg-gvm-1 bash

root@833862e8e0b9:/home/postgres# su - postgres
postgres@833862e8e0b9:~$ psql gvmd 
psql (13.14 (Debian 13.14-0+deb11u1))
Type "help" for help.

gvmd=# \d+ result_vt_epss
                                    Materialized view "public.result_vt_epss"
       Column        |       Type       | Collation | Nullable | Default | Storage  | Stats target | Description 
---------------------+------------------+-----------+----------+---------+----------+--------------+-------------
 vt_id               | text             |           |          |         | extended |              | 
 epss_score          | double precision |           |          |         | plain    |              | 
 epss_percentile     | double precision |           |          |         | plain    |              | 
 epss_cve            | text             |           |          |         | extended |              | 
 max_epss_score      | double precision |           |          |         | plain    |              | 
 max_epss_percentile | double precision |           |          |         | plain    |              | 
 max_epss_cve        | text             |           |          |         | extended |              | 
Indexes:
    "result_vt_epss_by_vt_id" btree (vt_id)
View definition:
 SELECT nvts.oid AS vt_id,
    nvts.epss_score,
    nvts.epss_percentile,
    nvts.epss_cve,
    nvts.max_epss_score,
    nvts.max_epss_percentile,
    nvts.max_epss_cve
   FROM nvts;
Access method: heap

gvmd=# \d nvts
                                       Table "public.nvts"
       Column        |       Type       | Collation | Nullable |             Default              
---------------------+------------------+-----------+----------+----------------------------------
 id                  | integer          |           | not null | nextval('nvts_id_seq'::regclass)
 uuid                | text             |           | not null | 
 oid                 | text             |           | not null | 
 name                | text             |           |          | 
 comment             | text             |           |          | 
 summary             | text             |           |          | 
 insight             | text             |           |          | 
 affected            | text             |           |          | 
 impact              | text             |           |          | 
 cve                 | text             |           |          | 
 tag                 | text             |           |          | 
 category            | text             |           |          | 
 family              | text             |           |          | 
 cvss_base           | text             |           |          | 
 creation_time       | integer          |           |          | 
 modification_time   | integer          |           |          | 
 solution            | text             |           |          | 
 solution_type       | text             |           |          | 
 solution_method     | text             |           |          | 
 detection           | text             |           |          | 
 qod                 | integer          |           |          | 
 qod_type            | text             |           |          | 
 epss_cve            | text             |           |          | 
 epss_score          | double precision |           |          | 
 epss_percentile     | double precision |           |          | 
 epss_severity       | double precision |           |          | 
 max_epss_cve        | text             |           |          | 
 max_epss_score      | double precision |           |          | 
 max_epss_percentile | double precision |           |          | 
 max_epss_severity   | double precision |           |          | 
Indexes:
    "nvts_pkey" PRIMARY KEY, btree (id)
    "nvts_by_creation_time" btree (creation_time)
    "nvts_by_cvss_base" btree (cvss_base)
    "nvts_by_family" btree (family)
    "nvts_by_modification_time" btree (modification_time)
    "nvts_by_name" btree (name)
    "nvts_by_solution_type" btree (solution_type)
    "nvts_oid_key" UNIQUE CONSTRAINT, btree (oid)
    "nvts_uuid_key" UNIQUE CONSTRAINT, btree (uuid)

gvmd=# BEGIN;
DROP MATERIALIZED VIEW public.result_vt_epss;
CREATE MATERIALIZED VIEW result_vt_epss AS (
 SELECT nvts.oid AS vt_id,
    nvts.epss_score,
    nvts.epss_percentile,
    nvts.epss_cve,
    nvts.epss_severity,
    nvts.max_epss_score,
    nvts.max_epss_percentile,
    nvts.max_epss_cve,
    nvts.max_epss_severity
   FROM nvts
);
BEGIN
DROP MATERIALIZED VIEW
SELECT 141873
gvmd=*# commit;
COMMIT

gvmd=# alter materialized view result_vt_epss owner to gvmd ;

gvmd=# create index result_vt_epss_by_vt_id on result_vt_epss using btree (vt_id);
CREATE INDEX

I’ll report back as to whether or not reports work once a scan has completed

leonardb · July 26, 2024, 1:36pm

Don’t do this…

Digging further it seems to be a bug in how the materialized view is created or ‘not re-created’.

Looking at gvmd/src/manage_pg.c at fd961c080c3355abac1e056a0c35a83e6bafbd03 · greenbone/gvmd · GitHub

If I run the guard query on the DB of my install

gvmd=# SELECT EXISTS (SELECT * FROM information_schema.tables WHERE table_catalog = 'gvmd' AND table_schema = 'scap' AND table_name = 'cves');
 exists 
--------
 t
(1 row)

so it should have mat-view as per line 1823

and yet DB had the fallback mat-view as per line 1847

(sorry, seems I’m only allowed 2 links max)

cfi · July 26, 2024, 1:57pm

Please raise an issue below for the development team, it is very likely that you won’t get far / this is getting noticed in such a volunteer based community portal:

leonardb · July 26, 2024, 2:08pm

I’m going to do that now.

Replacing the mat-view with the version on line 1823 does resolve the issue and reports are accessible.

altjx · July 26, 2024, 2:42pm

Thanks a lot for digging into this. Would you mind sharing the issue once it’s created? I’m desperately trying to figure out how to resolve this and implement some stability going forward.

leonardb · July 26, 2024, 3:04pm

Issue is logged here: Incorrect definition for materialized view result_vt_epss with fresh docker deploy · Issue #2273 · greenbone/gvmd · GitHub

You can fix by manually replacing the mat-view and index, but I’m not sure it that would provide a permanent fix since it seems the mat-view is dependent on feature sets. That’d need to be answered by someone familiar with the actual code.

I’ve verified that reports do work after replacing the mat-view.

Steps I took:

log into the postgres server and connect to DB as per previous post
Run the following SQL

BEGIN;
DROP MATERIALIZED VIEW public.result_vt_epss;
CREATE MATERIALIZED VIEW result_vt_epss AS (
  SELECT cve AS vt_id,
    epss AS epss_score,
    percentile AS epss_percentile,
    cve AS epss_cve,
    cves.severity AS epss_severity,
    epss AS max_epss_score,
    percentile AS max_epss_percentile,
    cve AS max_epss_cve,
    cves.severity AS max_epss_severity
  FROM scap.epss_scores
  JOIN scap.cves ON cve = cves.uuid
  UNION ALL
  SELECT oid AS vt_id,
    epss_score,
    epss_percentile,
    epss_cve,
    epss_severity,
    max_epss_score,
    max_epss_percentile,
    max_epss_cve,
    max_epss_severity
  FROM nvts);
alter materialized view result_vt_epss owner to gvmd ;
SELECT create_index ('result_vt_epss_by_vt_id', 'result_vt_epss', 'vt_id');
COMMIT;

cfi · August 1, 2024, 5:14am

It seems this has been solved now in / via:

github.com/greenbone/gvmd

Fix: Add missing EPSS columns for "no CVEs" case

greenbone:main ← greenbone:add-missing-epss-view-columns

opened 10:06AM - 29 Jul 24 UTC

timopollmeier

+3 -1

## What The materialized view result_vt_epss now also has the columns epss_seve…rity and max_epss_severity in case there are no CVEs present when the view is created. ## Why This fixes SQL errors resulting from those missing columns. ## References closes #2273 https://forum.greenbone.net/t/get-reports-via-gmp-fails-with-database-issue/18785