Error 'pragma_page_count' and empty dashboard when using Postgres as backend server

[NewlineDotBlog]

Describe the bug
When using an external Postgres database as storage for Gowitness results, the dashboard (in the server web UI) is non-functional (see the screenshot below). Additionally, the following error is shown in the logs of the Gowitness server:

2024/10/30 06:59:47 INFO starting web server host=0.0.0.0 port=60001
2024/10/30 06:59:47 "GET http://localhost:60001/ HTTP/1.1" from 172.17.0.1:62582 - 200 481B in 13.650958ms
2024/10/30 06:59:47 "GET http://localhost:60001/assets/index-DewcPO5K.css HTTP/1.1" from 172.17.0.1:62582 - 200 42932B in 717.959µs
2024/10/30 06:59:47 "GET http://localhost:60001/assets/index-Kdb9YQmt.js HTTP/1.1" from 172.17.0.1:62582 - 200 908278B in 2.643167ms

2024/10/30 06:59:47 github.com/sensepost/gowitness/web/api/statistics.go:38 ERROR: function pragma_page_count() does not exist (SQLSTATE 42883)
[99.330ms] [rows:0] SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size()
2024/10/30 06:59:47 ERRO an error occured getting database size err="ERROR: function pragma_page_count() does not exist (SQLSTATE 42883)"

To Reproduce
Steps to reproduce the behavior:

1. Set up a PostgreSQL database
2. Perform a scan that stores some results in that database.

echo 'http://example.com' | gowitness scan --skip-html --write-db-uri 'postgres://postgres:<PASS>@<PostgresIP>:<PostgresPort>/<PostgresDB>' --write-db --write-screenshots file  -f -

3. Start the Gowitness server with the Postgres database configured as backend:

docker run -p 127.0.0.1:60001:60001 --rm ghcr.io/sensepost/gowitness gowitness report server --port 60001 --host 0.0.0.0 --db-uri 'postgres://postgres:<PASS>@<PostgresIP>:<PostgresPort>/<PostgresDB>'

5. Navigate to the Gowitness web UI dashboard
6. Observe the error above in the logs, and the screenshot below in the browser.

Expected behavior
The dashboard shows statistics and overview of database metrics.

Screenshots

Version Information:

• Gowitness server, using the ghcr.io/sensepost/gowitness Docker image:

              _ _
___ ___ _ _ _|_| |_ __ ___ ___ ___
| . | . | | | | |  _|  | -_|_ -|_ -|
|_  |___|_____|_|_||_|_|___|___|___|
|___|    v3, with <3 by @leonjza

gowitness: 3.0.4
git hash: d837746
build env: go1.23.2_linux/amd64
build time: 2024-10-14T10:34:21Z

• Original scan was performed using the following version:

               _ _
 ___ ___ _ _ _|_| |_ __ ___ ___ ___
| . | . | | | | |  _|  | -_|_ -|_ -|
|_  |___|_____|_|_||_|_|___|___|___|
|___|    v3, with <3 by @leonjza

gowitness: 3.0.5
git hash: 6b0000e
build env: go1.23.2_linux/arm64
build time: 2024-10-21T19:22:14Z

Additional context
Behavior was checked in Chrome, Firefox, and Zen. Result was the same across all.

Additionally, the gallery and overview pages of the Web UI do work as expected. The issue is limited to the dashboard page.

[leonjza]

Thanks for the report! Means we need to rework this query.

[NewlineDotBlog]

To add a bit of info, I did some googling when I encountered the error first. My hypothesis is that the issue is due to (unlike SQLite) Postgres does not support pragma_page_count and other pragma_* functions natively. So this would either need to be created on the server as (IE) SQL stored procedure, or in the client be replaced by Postgres equivalent SQL queries.