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
