Sep 022013
 

A recent post by Abbe in the Website Feedback forum indicates that the vuxml processing has a bit to be improved.

When I looked, prod differed from beta and dev. This could be as simple as a missed processing of vuln.xml. I re-ran the script, and the marked commits on prod then agreed with dev and beta. This situation reminded me of a past problem we had. I am not sure why this particular problem arose as identified by Abbe, but I am convinced of a new problem.

It’s not so much a new problem. It more like I am newly aware of the problem.

FreshPorts lists the commits which are affected by vulnerabilities. If you look at the commit history for a port, a black skull indicates that that version of the port is vulnerable. This information is stored in the commit_log_ports table, which looks like this:

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)
    "commit_log_ports_vuxml_port_id" btree (port_id)
    "commit_log_ports_vuxml_vuxml_id_idx" btree (vuxml_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=# 

This table a commit (commit_log_id) to a port which is vulnerable (port_id) and the vulnerability in question (vuxml_id).

In turn, the commit_log_ports table links the commit to the revision of that port, which helps us to compose the port history.

freshports.org=# \d commit_log_ports
   Table "public.commit_log_ports"
    Column     |   Type   | Modifiers 
---------------+----------+-----------
 commit_log_id | integer  | not null
 port_id       | integer  | not null
 needs_refresh | smallint | not null
 port_version  | text     | 
 port_revision | text     | 
 port_epoch    | text     | 
Indexes:
    "commit_log_ports_pkey" PRIMARY KEY, btree (commit_log_id, port_id)
    "commit_log_ports_needs_refresh_idx" btree (needs_refresh) WHERE needs_refresh <> 0
    "commit_log_ports_port_id" btree (port_id)
    "needs_refresh" btree (needs_refresh)
Foreign-key constraints:
    "$1" FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE
    "$2" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    commit_log_ports_insert AFTER INSERT ON commit_log_ports FOR EACH ROW EXECUTE PROCEDURE commit_log_ports_insert()

freshports.org=# 

If you look at the top of a port page, you will see a black skull if the most recent version of the port is vulnerable. That indication is stored in the ports_vulnerable 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 indicates whether or not a given port (port_id) has an existing vulnerability (current) or a past vulnerability (past). This indicates whether the port page displays a black skull, or a white skull, respectively.

Fixing the ports_vulnerable table is easy:

freshports.org=# select PortsVulnerabilityCountAdjust(6191);
 portsvulnerabilitycountadjust 
-------------------------------
                             1
(1 row)

freshports.org=# select * from ports_vulnerable where port_id = 6191;
 port_id | current | past 
---------+---------+------
    6191 |       0 |    1
(1 row)

freshports.org=# \q