Hacking the List of Installed Packages in FreeBSD

How to manually manipulate the list of installed packages in FreeBSD

Problem

Because it was stupidly easy to do so, I set up Nextcloud on my FreeBSD server using pkg install. This was wonderful, except Nextcloud is fast-moving, and before long, it wanted to install an update, which I did. So far, so good!

The next morning I check my daily security report email from the FreeBSD host I installed Nextcloud on, and I was surprised to see Nextcloud show up on the list of packages with mismatched checksums (I shouldn’t have been surprised, I just didn’t think this through all the way). I thought I could merely ignore its presence on the report, but after a few days, I was thoroughly annoyed. There had to be a way to remove it, right?

Solution

A little digging led me to /var/db/pkg/, which is where the package tool keeps track of what’s available and what’s installed. One of the files, local.sqlite seemed like it might have a list of locally installed packages. Time to go poking!

First, I opened the database in SQLite:

cd /var/db/pkg
sqlite3 local.sqlite

Using the .tables command got me a list of tables in the database, and I found one called packages that seemed to be the one I’d be most interested in. .schema packages revealed the table schema, and from there, I could construct a simple query to help me find what I needed:

SELECT
    id,
    name,
    version
FROM
    packages
WHERE
    name LIKE '%nextcloud%'
ORDER BY
    id

My query returned:

id   name                       version
---  -------------------------  ----------------
241  nextcloud-php74            19.0.3

Paranoia sets in, and at this point, I .quit and make a copy of the database before making any changes (cp local.sqlite ~ was sufficient here). I return to SQLite (using sudo, otherwise the database is read-only) and issue the following query:

DELETE FROM packages
WHERE id = 241;

and from there, .quit. Now, to wait.

Outcome

The next morning came, and I eagerly checked the daily security output email. Lo and behold - Nextcloud was no longer on the list of packages with mismatched checksums!

Final Thoughts

Was this the best way to solve this problem? Probably not. Should you make a habit of manually editing the package database? Again, probably not. But for my situation, this was the best course of action. Your mileage may vary.

How do you deal with these situations? Are there better or more recommended ways of excluding a package from this list? Sound off in the comments!