VUXML – doing it faster

It takes a long time for FreshPorts to process a new vuln.xml file. A very long time. Why? Because it process all entries in the file. Every time.

Anton Berezin proposed an idea. I paraphrase, but in brief, he said:

Figuring out what changed is as easy as hashing an entry and storing it in a db

That’s a very good start. I’ve checked the code, and the sticking point is going to be is table:

freshports.org=# \d ports_vulnerable
    Table "public.ports_vulnerable"
 Column  |  Type   |     Modifiers      
---------+---------+--------------------
 port_id | integer | not null
 current | integer | not null default 1
 past    | integer | not null default 0
Indexes:
    "ports_vulnerable_pkey" PRIMARY KEY, btree (port_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE RESTRICT ON DELETE CASCADE
Triggers:
    ports_vulnerable_delete_clear_cache AFTER DELETE ON ports_vulnerable FOR EACH ROW EXECUTE PROCEDURE ports_vulnerable_delete_clear_cache()

freshports.org=# 

This table is used to decide whether or not to display a grey or black skull at the top of each port page. This table is populated with two different queries. The first, figures out the number of vulnerabilities, current and past, that affect a given port:

  SELECT count(distinct vuxml_id)
    INTO l_VulnCount
    FROM commit_log_ports_vuxml
   WHERE port_id = p_PortID;

The second, determines the number of vulnerabilities that currently affect the port:

    SELECT count(distinct vuxml_id)
      INTO l_VulnCurrent
      FROM commit_log_ports_vuxml CLPV, commit_log_ports CLP, ports P, ports_vulnerable PV
     WHERE CLP.commit_log_id = CLPV.commit_log_id
       AND CLPV.port_id      = CLP.port_id
       AND P.id              = CLP.port_id
       AND P.version         = CLP.port_version
       AND P.revision        = CLP.port_revision
       AND CLP.port_epoch    = P.portepoch
       AND PV.port_id        = P.id
       AND P.id              = p_PortID
     GROUP BY P.id;

NOTE: I am not sure of the reason for referencing ports_vulnerable here… My first thought is it is unnecessary. I’m not sure how it is to reference a table that is just about to get updated.

The difference between the two values, if any, is the number of past vulnerabilities.

The problem is an edge case where a vuln entry affects a port, then does not affect a port. For example, consider that a new vuln entry is created and mistakenly refers to PortA instead of PortB. At present, we wipe all entries from the ports_vulnerable table each time we process a new vuln.xml file. Thus, we start from nil each time. If we are now processing just one vuln at a time, we may have a lingering entry in ports_vulnerable which should be deleted.

How do we detect this situation?

Well, when we process the new vuln, we will delete the old one first, from the vuxml table. When this happens, various triggers are set off to delete related records. We can harness that to delete entries in ports_vulnerable as well.

And how?

Via this table:

freshports.org=# \d commit_log_ports_vuxml
                             Table "public.commit_log_ports_vuxml"
    Column     |  Type   |                              Modifiers                              
---------------+---------+---------------------------------------------------------------------
 id            | integer | not null default nextval('commit_log_ports_vuxml_id_seq'::regclass)
 commit_log_id | integer | not null
 port_id       | integer | not null
 vuxml_id      | integer | not null
Indexes:
    "commit_log_ports_vuxml_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (vuxml_id) REFERENCES vuxml(id) ON UPDATE CASCADE ON DELETE CASCADE
    "$2" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    "$3" FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE

freshports.org=# 

When the vuxml entry is deleted, we can see all the ports affected by this vuln. We can then delete entries from the ports_vulnerable table as well.

Or so I think….

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

Leave a Comment

Scroll to Top