Nvt update fails with SQL error after update to 20.8.0

After the update from 9 to 20.8.0 the nvt update of gvmd fails with this SQL error:

md manage:   INFO:2020-09-02 12h13.10 utc:3263: OSP service has different VT status (version 202009021005) from database (version 202008310959, 61692 VTs). Starting update ..
md manage:   INFO:2020-09-02 12h13.40 utc:3263: Updating VTs in database ... 99 new VTs, 46 changed VTs
md manage:WARNING:2020-09-02 12h13.40 utc:3263: sql_exec_internal: PQexec failed: ERROR:  function digest(text, unknown) does not exist
LINE 1: ...d  ORDER BY nvts.oid ASC ) SELECT encode         (digest    ...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 (7)
md manage:WARNING:2020-09-02 12h13.40 utc:3263: sql_exec_internal: SQL: 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),                         ''))         AS vt_string  FROM nvts  LEFT JOIN pref_str ON nvts.oid = pref_str.oid  GROUP BY nvts.oid  ORDER BY nvts.oid ASC ) SELECT encode         (digest           (coalesce (string_agg (nvt_str.vt_string, ''),                      ''),            'sha256'),          'hex') FROM nvt_str;
md manage:WARNING:2020-09-02 12h13.40 utc:3263: sql_x: sql_exec_internal failed
md manage:WARNING:2020-09-02 12h13.40 utc:3263: update_nvts_from_vts: SHA-256 hash of the VTs in the database ((null)) does not match the one from the scanner (fd000f9114d17f53c4fe156f8ba11ce40342af47b4795dd8d4e2e4f941704819).
md   main:MESSAGE:2020-09-02 12h13.40 utc:3263: Rebuilding NVTs because integrity check failed

The database migration itself was running without any errors.
I use pgsql 11.
Log of the sql server:

2020-09-02T14:13:40+0200 postgres[3264]: [7-1] < 2020-09-02 14:13:40.350 CEST >ERROR: function digest(text, unknown) does not exist at character 662
2020-09-02T14:13:40+0200 postgres[3264]: [7-2] < 2020-09-02 14:13:40.350 CEST >HINT: No function matches the given name and argument types. You might need to add explicit type casts.
2020-09-02T14:13:40+0200 postgres[3264]: [7-3] < 2020-09-02 14:13:40.350 CEST >STATEMENT: WITH pref_str AS ( SELECT name, substring(name, β€˜^(.?):') AS oid, substring (name, '^.?:([^:]+):’) AS pref_id, (substring (name, β€˜^.?:([^:]+):') || substring (name, '[1]:[^:]:[^:]:(.)') || 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), β€˜β€™)) AS vt_string FROM nvts LEFT JOIN pref_str ON nvts.oid = pref_str.oid GROUP BY nvts.oid ORDER BY nvts.oid ASC ) SELECT encode (digest (coalesce (string_agg (nvt_str.vt_string, β€˜β€™), β€˜β€™), β€˜sha256’), β€˜hex’) FROM nvt_str;
2020-09-02T14:18:22+0200 postgres[3264]: [8-1] < 2020-09-02 14:18:22.462 CEST >ERROR: function digest(text, unknown) does not exist at character 662
2020-09-02T14:18:22+0200 postgres[3264]: [8-2] < 2020-09-02 14:18:22.462 CEST >HINT: No function matches the given name and argument types. You might need to add explicit type casts.
2020-09-02T14:18:22+0200 postgres[3264]: [8-3] < 2020-09-02 14:18:22.462 CEST >STATEMENT: WITH pref_str AS ( SELECT name, substring(name, '^(.
?):’) AS oid, substring (name, β€˜^.?:([^:]+):') AS pref_id, (substring (name, '^.?:([^:]+):’) || substring (name, β€˜[2]:[^:]:[^:]:(.)’) || 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), β€˜β€™)) AS vt_string FROM nvts LEFT JOIN pref_str ON nvts.oid = pref_str.oid GROUP BY nvts.oid ORDER BY nvts.oid ASC ) SELECT encode (digest (coalesce (string_agg (nvt_str.vt_string, β€˜β€™), β€˜β€™), β€˜sha256’), β€˜hex’) FROM nvt_str;

What goes wrong? :frowning:


  1. ^: β†©οΈŽ

  2. ^: β†©οΈŽ

Looks like you’re missing the pgcrypto PostgreSQL DB extension described in the release notes:

1 Like

Thanks, that was it :slight_smile: