Feed update and SQL failure

GVM versions

gsad: 20.08.0~git
gvmd: 20.08.0 (Manager DB revision 233)
openvas-scanner: 20.8.0
gvm-libs: 20.8.0

Environment

Operating system: Debian v10.7 (Buster)
Kernel: Linux openvas 4.19.0-13-amd64 #1 SMP Debian 4.19.160-2 (2020-11-28) x86_64 GNU/Linux
Installation method / source: Source install.

I have a mostly working system in place, except that the feed updates fail.

I see this continuously:

md manage:WARNING:2020-12-14 22h19.21 utc:12611: update_scap: No SCAP db present, rebuilding SCAP db from scratch
md manage:   INFO:2020-12-14 22h19.21 utc:12611: update_scap: Updating data from feed
md manage:   INFO:2020-12-14 22h19.21 utc:12611: Updating CPEs
md manage:WARNING:2020-12-14 22h21.25 utc:12611: sql_exec_internal: PQexec failed: ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

It is followed by a very large sql statement (I can paste if requested, but I’m not permitted to upload files) along the lines of:

SQL: INSERT INTO scap2.cpes (uuid, name, title, creation_time,  modification_time, status, deprecated_by_id,  nvd_id) VALUES ('cpe:/a:chef:chef:15.0.273', 'cpe:/a:chef:chef:15.0.273', 'Chef 15.0.273', 1574089089, 1574089089, 'FINAL', NULL, '634111'),
... (many such values) ...
ON CONFLICT (uuid) DO UPDATE SET name = EXCLUDED.name,     title = EXCLUDED.title,     creation_time = EXCLUDED.creation_time, modification_time = EXCLUDED.modification_time, status = EXCLUDED.status,     deprecated_by_id = EXCLUDED.deprecated_by_id,     nvd_id = EXCLUDED.nvd_id;

The table it is attempting to insert into, is empty:

gvmd=> select * from scap2.cpes 
gvmd-> ;
 id | uuid | name | comment | creation_time | modification_time | title | status | deprecated_by_id | max_cvss | cve_refs | nvd_id 
----+------+------+---------+---------------+-------------------+-------+--------+------------------+----------+----------+--------
(0 rows)

Anyway, this causes the feed update to fail, and leaves a 468MB temp dir which it does not clean up. It immediately tries again, downloading and failing until my /tmp folder is filled:

md manage:WARNING:2020-12-14 21h37.36 utc:11864: split_xml_file: Failed to make temp dir: No space left on device
md manage:WARNING:2020-12-14 21h37.36 utc:11864: update_scap_cpes: Failed to split CPEs, attempting with full file

I can only assume that this is not a widespread problem, or that there is a straightforward fix for this that I’ve not hit on. I’d really appreciate any ideas/suggestions you might have.

Note that this behavior results in repeated fetches from the feed server. That’s consuming a lot of bandwidth for no benefit. I don’t want to be doing that, but I don’t know that there is even a way to turn off automated feed updates by gvmd, apart from just not running the daemon.

That said, without this initial update, my openvas installation is just about useless (or will require massive amounts of configuration of ports, scanners, and everything else).

If another source edition user could provide me with a current dump of their scap2 db namespace, I suspect that would get me up and running.

I’m sure that’s a horrible idea, but I have no alternatives, currently.

Hi,

just a wild guess, but maybe you missed to configure the feed import owner or the configuration was not working correctly. Try this query inside the gvm postgres db

<your-psql-db-name>=# select * from settings where name='Feed Import Owner';

You should see the uuid of the user you configured in the value column. Configuration of this setting is found here --> https://github.com/greenbone/gvmd/blob/master/INSTALL.md#set-the-feed-import-owner.

Cheers,
Carl

1 Like

A feed user does appear to be assigned:

gvmd=> SELECT s.uuid AS setting_uuid, s.name AS setting_name, s.value AS feed_owner, u.name AS username FROM settings s JOIN users u ON u.uuid = s.value                       
;
             setting_uuid             |   setting_name    |              feed_owner              | username                                                                    
--------------------------------------+-------------------+--------------------------------------+----------                                                                   
 78eceaec-3385-11ea-b237-28d24461215b | Feed Import Owner | bab59909-a9b4-4c1b-98e6-7cbf6ddad44a | admin                                                                       
(1 row)

That said…if I try to use gvmd to look stuff up…

root@openvas:~# gvmd --get-users --verbose
root@openvas:~#

I get nothing. I created that user, and I can log in to the web interface with it.

Nevermind. I just needed to do that as the right user:

root@openvas:~# sudo -u gvm gvmd --get-users
admin
root@openvas:~# sudo -u gvm gvmd --get-users --verbose
admin bab59909-a9b4-4c1b-98e6-7cbf6ddad44a

Anyway, so it doesn’t seem as if the feed user is problematic.

OK, although it seems unlikely, this really looks like bad feed data to me.

Taking the SQL in the error message in gvmd.log (scap_data_insert.sql.gz (166.1 KB) ), I tried to run this insert manually. I commented out the ON CONFLICT ... bit so that it would error on the first duplicate entry.

root@openvas:~# sudo -u gvm psql -d gvmd -f /tmp/scap_data_insert.sql           
...                                                                             
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.                 
psql:/tmp/scap_data_insert.sql:10010: WARNING:  nonstandard use of escape in a string literal
LINE 9453: ...12.2\(60\)ez8', 'cpe:/o:cisco:ios:12.2\(60\)ez8', 'Cisco IOS...   
                                                                ^               
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.                 
psql:/tmp/scap_data_insert.sql:10010: ERROR:  duplicate key value violates unique constraint "cpe_idx"
DETAIL:  Key (name)=(cpe:/o:cisco:ios:12.2(25)fz) already exists. 

Note all the warnings about escaping. There are multiple entries that postgresql treats identically:

root@openvas:/home/david_clymer# grep 'cpe:/o:cisco:ios:12.2' scap_data_insert.sql|cut -d'(' -f2- |cut -d, -f1|grep fz
'cpe:/o:cisco:ios:12.2(25)fz'                                                   
'cpe:/o:cisco:ios:12.2\(25\)fz'                                                 
'cpe:/o:cisco:ios:12.2fz'    

Two of the items above are identical, by postgresql’s standard:

gvmd=> select 'cpe:/o:cisco:ios:12.2(25)fz' = 'cpe:/o:cisco:ios:12.2\(25\)fz'   
gvmd-> ;                                                                        
WARNING:  nonstandard use of escape in a string literal                         
LINE 1: select 'cpe:/o:cisco:ios:12.2(25)fz' = 'cpe:/o:cisco:ios:12....         
                                               ^                                
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.                 
 ?column?                                                                       
----------                                                                      
 t                                                                              
(1 row)              

This leads to conflicts, triggering the ON CONFLICT clause in the SQL. Apparently postgresql’s upsert mechanism cannot deal with conflicts resulting from inserts which happen within the same statement.

So, I manually did the insert, removing the duplicate data with the meaningless escaping.

root@openvas:~# grep -v '\\' /tmp/scap_data_insert.sql > /tmp/scap_data_insert_fixed.sql
root@openvas:~# sudo -u gvm psql -d gvmd -f /tmp/scap_data_insert_fixed.sql
could not change directory to "/root": Permission denied
INSERT 0 9894

Yay!, except it made no difference at all. Updates from feeds still fail in the exact same way. None of my feeds have updated successfully. CERT, SCAP, or GVMD.

This is really, super !@#$#!!% frustrating.

Reading back over what I’ve written so far, I’m not sure how clear I’ve been: This is a problem with gvmd, not the feed sync script. That works just fine. The failure happens when gvmd attempts to sync that feed data to its database.

SOLVED:

So, this was a case of a bad postgresql config. My ansible was setting postgresql’s standard_conforming_strings = off in postgresql.conf. We use that for another product…and I failed to configure it differently for this project.

That is what makes postgresql see these as equivalent: '\(hi\)' and 'hi', resulting in the duplicate conflicts in the sql update.

1 Like