[21.4.2] gvmd --rebuild fails to rebuild NVT cache

[tgurr]

Expected behavior

See su -c "gvmd --rebuild" -s /bin/bash gvm succeed.

Actual behavior

After some time after issuing su -c "gvmd --rebuild" -s /bin/bash gvm it fails with:

Failed to rebuild NVT cache.

In the gvmd.log I have:

md   main:MESSAGE:2021-07-01 08h09.09 utc:563653:    Greenbone Vulnerability Manager version 21.4.2 (DB revision 242)
md manage:   INFO:2021-07-01 08h09.09 utc:563653:    Rebuilding NVTs.
md manage:   INFO:2021-07-01 08h12.53 utc:563653: Updating VTs in database ... 74525 new VTs, 0 changed VTs
md manage:WARNING:2021-07-01 08h12.54 utc:563653: update_nvts_from_vts: SHA-256 hash of the VTs in the database (ccf07d7f4f49fddf61efc9def8240753e3d2795dacbcde473fde32e316aaf76c) does not match the one from the scanner (c6e978aa270a0d2a3d03fafb3ef6e1de9fc75a3394ef2cd8ad410b30ca12a857).

Note: things appear to work despite the rebuild failure, I can issue scans from GSA and get results. no idea how serious that rebuild failure actually is for the Greenbone stack.

Steps to reproduce

1. issue su -c "gvmd --rebuild" -s /bin/bash gvm.

GVM versions

gsa: Greenbone Security Assistant 21.4.1~dev1
gvm: Greenbone Vulnerability Manager 21.4.2 / Manager DB revision 242
openvas-scanner: OpenVAS 21.4.1
gvm-libs: gvm-libs 21.4.1

Environment

Operating system: Linux exherbo 5.12.0 #1 SMP Thu Apr 29 14:22:53 CEST 2021 x86_64 GNU/Linux
Installation method / source: Exherbo packages (source-based)

Logfiles

See above.

[tgurr]

I've applied the backport commit from the 21.04 branch: 95dfd86

Stopped gvmd.service and trying to run:

su -c "gvmd -m" -s /bin/bash gvm

I get:

(gvmd:606220): md manage-WARNING **: 11:51:16.331: sql_exec_internal: PQexec failed: FEHLER:  Sortierfolge ?C.UTF-8? f?r Kodierung ?UTF8? existiert nicht
LINE 1: ..._id                                               COLLATE "C...
                                                             ^
 (7)

(gvmd:606220): md manage-WARNING **: 11:51:16.334: sql_exec_internal: SQL: CREATE OR REPLACE FUNCTION vts_verification_str () RETURNS text AS $$ WITH pref_str AS (   SELECT name,          substring(name, '^(.*?):') AS oid,          substring (name, '^.*?:([^:]+):') AS pref_id,          (substring (name, '^.*?:([^:]+):')           || substring (name,                         '^[^:]*:[^:]*:[^:]*:(.*)')           || value) AS pref   FROM nvt_preferences  ),  nvt_str AS (   SELECT (SELECT nvts.oid             || max(modification_time)             || coalesce (string_agg(pref_str.pref, ''                                     ORDER BY (pref_id                                               COLLATE "C.UTF-8")),                          ''))          AS vt_string   FROM nvts   LEFT JOIN pref_str ON nvts.oid = pref_str.oid   GROUP BY nvts.oid   ORDER BY (nvts.oid COLLATE "C.UTF-8") ASC  ) SELECT coalesce (string_agg (nvt_str.vt_string, ''), '')   FROM nvt_str$$ LANGUAGE SQL STABLE;

(gvmd:606220): md manage-WARNING **: 11:51:16.335: sqlv: sql_exec_internal failed
hnexherbo ~ # su -c "gvmd -m" -s /bin/bash gvm

(gvmd:606225): md manage-WARNING **: 11:51:47.495: sql_exec_internal: PQexec failed: FEHLER:  Sortierfolge ?C.UTF-8? f?r Kodierung ?UTF8? existiert nicht
LINE 1: ..._id                                               COLLATE "C...
                                                             ^
 (7)

(gvmd:606225): md manage-WARNING **: 11:51:47.497: sql_exec_internal: SQL: CREATE OR REPLACE FUNCTION vts_verification_str () RETURNS text AS $$ WITH pref_str AS (   SELECT name,          substring(name, '^(.*?):') AS oid,          substring (name, '^.*?:([^:]+):') AS pref_id,          (substring (name, '^.*?:([^:]+):')           || substring (name,                         '^[^:]*:[^:]*:[^:]*:(.*)')           || value) AS pref   FROM nvt_preferences  ),  nvt_str AS (   SELECT (SELECT nvts.oid             || max(modification_time)             || coalesce (string_agg(pref_str.pref, ''                                     ORDER BY (pref_id                                               COLLATE "C.UTF-8")),                          ''))          AS vt_string   FROM nvts   LEFT JOIN pref_str ON nvts.oid = pref_str.oid   GROUP BY nvts.oid   ORDER BY (nvts.oid COLLATE "C.UTF-8") ASC  ) SELECT coalesce (string_agg (nvt_str.vt_string, ''), '')   FROM nvt_str$$ LANGUAGE SQL STABLE;

(gvmd:606225): md manage-WARNING **: 11:51:47.498: sqlv: sql_exec_internal failed

Is there something I'm missing, do I have to issue another rebuild su -c "gvmd --rebuild" -s /bin/bash gvm first?

Additional notes: My gvmd database has UTF8 encoding and the collation is de_DE.utf8.

[timopollmeier]

I think you'd have to add the collation to the ones supported by your database and maybe also the system locales. (see https://www.postgresql.org/docs/12/collation.html#COLLATION-MANAGING )
However, I can also check if using another collation (ucs_basic) works.

[tgurr]

It would be great if this could solved in a different way, as explained right now this causes a regression for me. I can't remember having such problems on any other system, usually the content of the database and the programs making use of it respect the collation setting so my expectation would be if it's set to de_DE.utf8 it should work.

The documentation https://github.com/greenbone/gvmd/blob/master/INSTALL.md just states createdb -O mattm gvmd so imho I did nothing wrong on initial database setup according to the documentation.

Other projects specify e.g. createdb --encoding=UTF8 --locale=C --template=template0 --owner=dbowner dbname if required and/or also warn or even refuse to work, for example matrix synapse: Synapse will refuse to set up a new database if it has the wrong values of COLLATE and CTYPE set, and will log warnings on existing databases. So if gvmd expectes a C Collation at least the documentation should be fixed.

Please advice if gvmd actually expects a C collation to begin with and I'm expected to change the Collation of my database to plain C.

[timopollmeier]

I think the (default) locale of the database should still not matter but I made the wrong assumption that "C.UTF-8" would be available for selecting the collation explicitly by default.

[timopollmeier]

I don't have a system without the "C.UTF-8" collation ready to test if it actually fixes your problem, but hopefully the backports of #1629 will.

[tgurr]

@timopollmeier thanks, applying the additional commit 392b9c2 in addition to your previous fix appears to work for me:

# su -c "gvmd --rebuild" -s /bin/bash gvm

md   main:MESSAGE:2021-07-09 13h40.46 utc:1283:    Greenbone Vulnerability Manager version 21.4.2 (DB revision 242)
md manage:   INFO:2021-07-09 13h40.46 utc:1283:    Rebuilding NVTs.
md manage:   INFO:2021-07-09 13h44.29 utc:1283: Updating VTs in database ... 74869 new VTs, 0 changed VTs
md manage:   INFO:2021-07-09 13h44.31 utc:1283: Updating VTs in database ... done (74869 VTs).

[bjoernricks]

Fixed with #1630