SQLite database missing, Force Reload DNSBL to recover!

SteveITS

I decided to experiment with blocking DoH on my home router and went down a rabbit hole. If I "Enable DNSBL" (using the non-Python method) and enable "DoH/DoT Blocking," select a few domains like Firefox's, and run an update and force reload DNSBL, the upload log has DNSBL entries but the dashboard widget shows Unknown with a title/tooltip status of "*** SQLite database missing, Force Reload DNSBL to recover! ***" which doesn't sound all that successful. :) And "use-application-dns.net" resolves. I feel like I'm missing something simple.

Force Reload, DNSBL outputs this in the log:
Clearing all DNSBL Feeds
Stopping Unbound Resolver
Unbound stopped in 1 sec.
Additional mounts:
No changes required.
Starting Unbound Resolver... completed [ 04/16/21 17:48:37 ]
DNSBL update [ 0 | PASSED ]... completed

SG-2100 on 21.02_1 at the moment. Updated pfB to 3.0.0_16 and Force Reload again, same result.

SteveITS

It still hasn't fixed itself after several days so I turned it off. Functionally, using the TheGreatWall_DoH_IP feed should be equivalent...?

Gertjan

@steveits

pfBlockerNG/16 creates three (3) "sqlite' files here :

/var/unbound

What do you have there ?

ls -al /var/unbound/pfb_*.sqlite

It should look like

-rw-r--r--  1 unbound  unbound  32768 Apr 20 07:19 /var/unbound/pfb_py_cache.sqlite
-rw-r--r--  1 unbound  unbound   8192 Apr 20 07:41 /var/unbound/pfb_py_dnsbl.sqlite
-rw-r--r--  1 unbound  unbound  16384 Apr 20 07:42 /var/unbound/pfb_py_resolver.sqlite

Free disk space ok ?

df

Btw : the "DoH/DoT Blocking List" : I've selected them all. I'll be more picky in the future ^^

SteveITS

I only have two, though DNSBL is disabled since my post yesterday:

-rw-r--r-- 1 unbound unbound 8192 Apr 18 10:20 /var/unbound/pfb_py_dnsbl.sqlite
-rw-r--r-- 1 unbound unbound 12288 Apr 19 16:43 /var/unbound/pfb_py_resolver.sqlite

For the record the feed can be used to block all traffic from LAN to public DNS/DoH servers which includes Quad9, Google, etc., however in that configuration pfSense can connect out to them.

Gertjan

@steveits said in SQLite database missing, Force Reload DNSBL to recover!:

however in that configuration pfSense can connect out to them.

pfSense, that is, 'unbound', is a resolver. It is possible to forward with it, using classic '53' or even do DNS over TLS using 853.
So, no, even if it could, it wouldn't. Only the admin decides. That's me, and you.

@steveits said in SQLite database missing, Force Reload DNSBL to recover!:

title/tooltip status of "*** SQLite database missing, Force Reload DNSBL to recover! ***" which doesn't sound all that successful

I had a look at the manual.
This message is shown if there is no 'DNSBL' to show for the Widget. The database file is there, the file was opened ok etc, just empty - like 'no results yet'.

SteveITS

@gertjan said in SQLite database missing, Force Reload DNSBL to recover!:

even if it could, it wouldn't

I meant, set the feed as a Deny outbound (and update) and there is a rule created on LAN blocking all traffic to those IPs. pfSense's Diagnostics/DNS lookup (or unbound) connects out on WAN so isn't blocked.

There's a manual? :) Where is that?

So DNSBL is empty...shouldn't it contain the selected DoH servers? Or am I misunderstanding? What I did was:

Enable DoH/DoT Blocking
Select services in DoH/DoT Blocking List
Save
Enable DNSBL
Save DNSBL Settings
Force an update

Gertjan

@steveits said in SQLite database missing, Force Reload DNSBL to recover!:

I meant, set the feed as a Deny outbound (and update) and there is a rule created on LAN blocking all traffic to those IPs. pfSense's Diagnostics/DNS lookup (or unbound) connects out on WAN so isn't blocked.

Firewall rules for a LAN interface operate on traffic that comes from the cable (from your devices) INTO the LAN interface. That traffic is filtered through the rules you created.

A process running in IN the box called "pfSense" is not part of your LAN. It's between LAN - and WAN for that matter.
It's (process that are running on the box called pfSense) not putting traffic through the (a) LAN port on to the wire, to get back into the same LAN interface, to get it's traffic filtered.
Yo might think : hey, traffic from (inside - the processes of) pfSense can go anywhere where they want ?! Well, that right !!

Although, the real manual will tell you about "Floating firewall rules" These behave different as interface based rules, and do permit you to act upon traffic coming from pfSense itself.

The real manual is any of these :

@steveits said in SQLite database missing, Force Reload DNSBL to recover!:

There's a manual? :) Where is that?

Ok. You have it coming ;)

The story about "open source" is that the source is open.
You can look it upon the Internet (github) and you have a copy on your box called 'pfSense'. The (FreeBSD) kernel is binary, but every line is also open source, as are most if not all the drivers.
It's something that can be consulted, it's very readable. Most of pfSense script code, PHP, has been removed from status what has been known as 'pure rocket science' somewhere at the end of the last century.
PHP is 'open' and easy to understand, to be 'used' as soon as one learned how to read and write.

So, I looked at /usr/local/www/widgets/widgets/pfblockerng.widget.php, where the message comes from, and /usr/local/pkg/pfblockerng/pfblockerng.inc where the "PHP SQL databases" are opened/created/handled.

Btw : the part we're discussing here is just the part called 'GUI' : an interactive web server in front of the dull stuff : the actual firewall, and some (a lot of - actually to many) other functionalities.

SteveITS

Thinking about it logically, most using pfBlocker's DNSBL feature are probably using the actual DNS blocklists, so I bet the widget is looking for those items and they don't exist if only the SafeSearch feature is being used. Hence the error/warning.

To think out loud for the forum, we could use the feed and do something like either:

set Windows DNS to forward to pfSense
set pfSense to forward to desired DNS (e.g. Quad9)
set Deny Outbound rule to block using DoH feed

or

set Windows DNS to forward to desired DNS
create rule to allow Windows DNS to query desired DNS
create rule to deny to DoH feed (using Alias Native, so one can set an order with a custom rule)

Most of our clients use Windows AD; the smaller ones just query the pfSense directly, so we can just block DoH using the feed.

Edit: the Windows AD domain of course can be listed as a domain override pointing back to those servers on LAN.