Incorrect definition for materialized view result_vt_epss with fresh docker deploy

[leonardb]

Expected behavior

Reports to work

Actual behavior

Errors trying to access reports

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   |                                                              ^

Steps to reproduce

1. Fresh docker deployment as per https://greenbone.github.io/docs/latest/22.4/container/index.html
2. Scan a host
3. Attempt to access reports

GVM versions

gsa: (gsad --version)

Greenbone Security Assistant 22.11.0

gvm: (gvmd --version)

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.

openvas-scanner: (openvas --version)

sudo docker exec -it greenbone-community-edition-openvas-1 openvas --version exec /usr/local/sbin/openvas: operation not permitted

gvm-libs:

unknown

Environment

Docker: https://greenbone.github.io/docs/latest/22.4/container/index.html

Logfiles

See above

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

Looking at https://github.com/greenbone/gvmd/blob/fd961c080c3355abac1e056a0c35a83e6bafbd03/src/manage_pg.c#L1813

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

Replacing the mat-view with the version defined at Line 1823 resolves the issue

[nikosch86]

I am facing the same issue, a quick and dirty fix is to connect to the DB and run these commands:

DROP MATERIALIZED VIEW 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);

[altjx]

Same here.

[oleg-filiutsich]

I use docker compose install mode. And fix the issus like talk @nikosch86

root@security-scanner:~/greenbone-community-container# docker exec -it greenbone-community-edition-pg-gvm-1 bash
root@c131ea1525a5:/home/postgres# psql gvmd -U gvmd
gvmd=> \c gvmd;
gvmd=> DROP MATERIALIZED VIEW result_vt_epss;
gvmd=> 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);