FreeBSD makes use of security/vuxml to keep track of vulnerabilities within software. If you install ports-mgmt/portaudit, your daily security summary email will note any reported vulnerabilities that exist within your installed ports. I recommend that you install mgmt/portaudit on all your systems. If you are running jails, I recommend that you install ports-mgmt/jailaudit
on your host system.
FreshPorts parses the ports/security/vuxml/vuln.xml file and loads the results into its database. It then uses the result to mark certain versions of the port as vulnerable or not. This information is also used to produce the Vulnerable ports.
On Wednesday, hhaamu wrote to me regarding a problem with the Vulnerable ports page. Specifically, it did not include www/seamonkey. Verifying the problem was simple. Finding the cause was not.
Processing a vuln.xlm file is a lengthy process and takes about forty minutes on the production server. Why so long? It examines every commit of every port affected by every reported vuln.xml entry. There are ideas to make this faster (i.e. processing only the new / changed vuln entries).
In short, the process is:
- Delete existing entries
- Parse the vuln.xml file
- Mark the affected commits
- Calculate the vulnerability counts for each port
The problem was in the last step. I tracked the problem down to the stored procedure PortsVulnerabilityCountAdjust(). I was confused. When I ran the function manually, it returned the correct results. However, the totals in the ports_vulnerable table were incorrect.
The ports_vulnerable table contains the number of reported vulnerabilities. It classifies these as either past or present. A given vulnerability is classified as current if it affects the latest version of the port. Otherwise, it is considered to be a past vulnerability.
A port with a current vulnerability will have a skull displayed to the right of the port name. If there is a past vulnerability, this skull will be noticeably faded.
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=# select count(*) from ports_vulnerable; count ------- 1141 (1 row) freshports.org=# select * from ports_vulnerable where port_id = 16419; port_id | current | past ---------+---------+------ 16419 | 0 | 31 (1 row) freshports.org=#
The above output represents the incorrect totals for www/seamonkey (id = 16419). But as you can see from the following, invoking the stored procedure will correct the totals:
freshports.org=# select * from ports_vulnerable where port_id = 16419; port_id | current | past ---------+---------+------ 16419 | 0 | 31 (1 row) freshports.org=# begin; BEGIN freshports.org=# select PortsVulnerabilityCountAdjust(16419); portsvulnerabilitycountadjust ------------------------------- 31 (1 row) freshports.org=# select * from ports_vulnerable where port_id = 16419; port_id | current | past ---------+---------+------ 16419 | 1 | 30 (1 row) freshports.org=# rollback; ROLLBACK freshports.org=#
I spent quite some time trying to figure out why this function worked after, but not during the vuxml processing. I added some debugging statements to the procedure trying to figure out what I was missing.
For your viewing pleasure, this is the uncorrected function:
CREATE OR REPLACE function PortsVulnerabilityCountAdjust(int) returns bigint as $$ DECLARE p_PortID ALIAS for $1; l_VulnCurrent int; l_VulnCount int; l_VulnPast int; l_RowsModified int; BEGIN SELECT count(distinct vuxml_id) INTO l_VulnCount FROM commit_log_ports_vuxml WHERE port_id = p_PortID; -- RAISE NOTICE 'Port % has % vulnerabilities registered', p_PortID, l_VulnCount; IF l_VulnCount > 0 THEN 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; IF l_VulnCurrent IS NULL THEN l_VulnCurrent := 0; END IF; -- RAISE NOTICE 'Port % has % current vulnerabilities', p_PortID, l_VulnCurrent; l_VulnPast := l_VulnCount - l_VulnCurrent; -- RAISE NOTICE 'Port % has % past vulnerabilities', p_PortID, l_VulnPast; -- try an update first UPDATE ports_vulnerable SET current = l_VulnCurrent, past = l_VulnPast WHERE ports_vulnerable.port_id = p_PortID; -- RAISE NOTICE 'UPDATE has been attempted'; GET DIAGNOSTICS l_RowsModified = ROW_COUNT; -- if that fails, do an insert IF l_RowsModified = 0 THEN INSERT INTO ports_vulnerable (port_id, current, past) VALUES (p_PortID, l_VulnCurrent, l_VulnPast); -- RAISE NOTICE 'new entry was inserted into ports_vulnerable'; ELSE -- RAISE NOTICE 'ports_vulnerable has been updated'; END IF; ELSE -- there are no vulnerabilities for this port DELETE FROM ports_vulnerable where port_id = p_PortID; END IF; return l_VulnCount; END $$ LANGUAGE 'plpgsql' VOLATILE;
The problem has been corrected on my dev machine. I will let that sit for a while, then move it to the beta machine. I’ll post the patch before this change goes to production. If you can spot the error, send me an email. If you don’t know which email to use, try WHOIS. I will publish the names of those who guess correctly.
FYI: I think this bug is the cause of the problems described in the mail/horde4-imp vulnerability report of this past September.