Status codes in FTL database for whitelisted entries

Everybody does it, you run into a blocked web site, search for the domain that causes the problem and whitelist it (hopefully with a comment for future reference). Once whitelisted, you never look back. I was recently made aware of some domains that no longer need whitelisting. but found it to be hard to determine which whitelist entries are actually effective, mainly due to the fact there are no dedicated status entries, indicating a query was forwarded (whitelist always wins) due to a whitelist entry. There are currently 17 status IDs (0-16), the missing entries in the documentation are already addressed by this PR.

add status types:

  • ID 17: forwarded, exact whitelist entry
  • ID 18: forwarde, regex whitelist.

If at all possible, the additional info field should contain the ID of the entry.

This would allow a user to identify the whitelist entries, used (thus usefull), but also the entries that are never used (will require some sqlite3 queries to identify, but certainly not impossible).

Currently, I'm using a grafana dashboard to identify specific queries.

The possibility to identify whitelist entries would allow me to identify obsolete entries (disable for a period of time, remove later).

Whitelist entries are always effective. If something is on your whitelist, it can never be blocked by either gravity or a domain entry.

You could run pihole -q for any domain to see what lists contain it (blacklist, whitelist, gravity, regex).

pihole -q is very usefull for solving specific problems, it is not intended to use for automation.

whitelist entries are added as response to specific problems, most users never look back, once the problem is solved. When a whitelist entry is no longer required, there is no easy way to detect this.

I want to sqlite3 query the database for allowed domain queries, due to whitelist entries. This can't be done in an efficient way, hence the request for status 17 and 18.

Already discussed this with a developer, his answer:

I don't see anything that speaks against adding this right now, however, I'd first have to take a closer look at the source code before I can say this with certainty. Something for another day but go ahead and raise the request.

We'd need more than these two, at least two more for cached queries or you won't get accurate counts for your whitelist entries. Also, we have status 7 or 8 which would overwrite status 2 so we'd need four more for this, too.

You see, we first have to find a compromise here as adding eight new status codes (+50%) seems a bit extreme for what we want to achieve here.

We currently reduce the work for the database by simply querying the mere existence of a domain in said tables using something like

SELECT EXISTS(SELECT domain from vw_whitelist WHERE domain = 'some-domain' AND group_id IN (1,2,3));

(emphasis on the outer SELECT EXISTS(...) subquery). We should be able remove this and instead SELECT the id with minimal performance costs. We know the regex IDs because we have to prepare (compile) the regex domains before we can use them so we need to query the entire rows from the database anyway.

Although different approach, I think this is implemented by

Released with Pi-hole FTL v5.18, Web v5.15 and Core v5.12.1 released