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.