Record time of last successful updater runs

[paulaldridge]

Problem

There is currently no record or way to see when the last time each updater successfully ran, and therefore no way to know how up to date vulnerabilities are in the database.

Required solution

A way to record and expose the time each updater last ran, in a form that shows each updater by name along with a timestamp of when it was last successfully run.
A successful updater run must meet one of these criteria:

1. Source successfully checked and no new vulnerabilities found to add to database
2. Source successfully checked, new vulnerabilities found, and all these vulnerabilities are successfully committed to the database

Proposed solution

During the regular update operation, a successful run for each individual updater will be recorded in a new table ‘updaters_last_run’. This will contain two fields: ‘updater_name’ and ‘last_successful_run’. The table can then be queried to find all or a subset of the updaters and when they were last successfully run.

Where driveUpdater is called:

1. Record the time before the driveUpdater is called
2. If driveUpdater returns without an error call a funtion which writes the updater name and recored time to the updaters_last_run table.

updateTime := time.Now()
err = m.driveUpdater(ctx, u)
if err != nil {
    errChan <- fmt.Errorf("%v: %w", u.Name(), err)
} else {
    recordSuccessfulUpdate(toRun[i], updateTime)
}

As an extension or second iteration:
To make the data more consumable we could add an endpoint to the clair matcher such as ‘matcher/api/v1/updaters_last_run’, which queries the above table and returns a json response containing a mapping of ‘updater_name’ and ‘last_successful_run’.

Does the solution satisfisfy?

This solution records the data required in a table that can be queried, showing a view of how up to date each updater. It will only be recorded in the table as a successful run if an update has happened, including where no new vulnerabilites have been found. Nothing will be recorded if the updater fails to run, fails to commit every new vulnerability, or is skipped.

[ibazulic]

@paulaldridge Doesn't the update_operation table already contain this data?

clair=# select updater, fingerprint, date from update_operation limit 5;
             updater              |                                      fingerprint                                       |             date              
----------------------------------+----------------------------------------------------------------------------------------+-------------------------------
 RHEL7-openshift-3.5              | {"Etag":"\"3d9d092352ea80bf6d7cc0b08a68ce3e\"","Date":"Sun, 10 Oct 2021 10:40:19 GMT"} | 2021-10-11 11:18:42.077939+01
 RHEL8-rhacm-1                    | {"Etag":"\"a2428639c09af7b5fe92be384cc77bd5\"","Date":"Sat, 09 Oct 2021 17:20:43 GMT"} | 2021-10-11 11:18:42.384558+01
 RHEL8-storage-ceph-4             | {"Etag":"\"662dc05ae7e5131eb6af42fc3972d3f1\"","Date":"Sun, 10 Oct 2021 10:38:10 GMT"} | 2021-10-11 11:18:42.506569+01
 pyupio                           | "353faf3a06ad04336497257f4512587e5d123342ceadbc97d2ebb928e20658b3"                     | 2021-10-11 11:18:42.95406+01
 RHEL7-rhvh-4-including-unpatched | {"Etag":"\"feb6277fa4acd6baf2eb2306694192b2\"","Date":"Sun, 10 Oct 2021 10:44:04 GMT"} | 2021-10-11 11:18:43.018055+01
(5 rows)

I've limited the output to just 5 entries, out of 552 that I have inside my database.

[paulaldridge]

Partly, update_operation only records when an updater finds new vulnerabilities to add to the database, so there isn't a record when updaters run successfully but do not find any new vulns. The gap that I'm concerned with is if any updater is failing to run it will look the same as if an updater hasn't found any new vulnerabilites for some time. If we record all successful runs it would be easy to see if the vulnerabilties are up to date in database.

[ibazulic]

But those are equivalent situations, aren't they? At least from Clair's perspective. Clair will in both cases provide information about what it has in the db. Which is also why we run updaters very frequently, every 30 minutes (as opposed to Clair v2's 6 hours by default). So even if we do fail to grab something now (due to, for example, networking issues), we will still try to update the again from the same source in 30 minutes. In the end, the only thing that matters is when we updated the database with new definitions. Which is what's been stored here.

I do, however, see your point, why it would be useful to store each update operation regardless of the outcome in the db. Maybe expand the update_operation table with two new columns? failed and date_ran with the current date column be changed to date_updated.

[paulaldridge]

Yeah I think it’s only useful to prove that the database is up to date as of a recorded time. Our team need to declare that a vulnerability report is correct as of a certain time, so we need a way of knowing that we’ve captured all vulnerabilities up to that time.
I think adding a row to the update_operation whether or not there were new vulnerabilities would work, I just wasn’t 100% sure how the table was used and if that would interfere with it. It would also cause there to be a lot more data in that table as the updaters run so often, whereas a separate table would allow you to limit it to a single row for each updater with a last successful run time that is updated each time.

[hdonnay]

The edge case I'm worried about is when the updaters aren't even called, like with the RHEL updater. In this patten, the factory does some work to determine the updater doesn't need to be run, and then doesn't even bother to construct one. I think the behavior would have to change to have some stub updater that goes through the system to get recorded as having "run"?

[paulaldridge]

Nice catch I didn't realise some updaters followed a different pattern. Would it be possible to just make an additonal call, to record that the updater is up to date, from wherever the work is done to find if the updater needs to run? I need to have a read into it to see if that's possible.

[paulaldridge]

@hdonnay I managed to have a read of the RHEL updater and see what you mean. Assuming we go for the design that uses a new table to record the latest time we know an updaters vulnerabilities are ‘up to date’, how about we have an extra column to record the OS for each updater recorded, and if we find that the RHEL source hasn’t been changed since we last checked, we update all rows with the OS of ‘RHEL’ with the current time. This would avoid us having to start retrieving and processing the RHEL data when we know it is unchanged.

[paulaldridge]

@hdonnay do you have any more thoughts on this? Sorry to chase, we're just quite keen to get a solution for this soon if we can (and happy to do the work once we agree on a good solution :) ) thank you

[paulaldridge]

Put together a PR for this for consideration: #558