Add Postgres-backed query logging to Technitium #70

Open
opened 2026-07-22 16:21:19 +00:00 by mconway · 0 comments
Owner

Goal

Enable DNS query logging in Technitium, backed by our existing in-cluster Postgres
instead of local Sqlite, using Technitium's official "Query Logs (PostgreSQL)" app.

Plan

  1. Provision a dedicated Postgres database + user, reusing the existing
    postgresql-backup role pattern already used by k8s-lanager/k8s-zabbix/
    k8s-authentik/etc., pointed at postgresql.database.svc.cluster.local.
    Needs a new Bitwarden item (or field) for the dedicated app username/password,
    following the same convention as Lanager-Setup's DBUsername/DBPassword fields.

  2. Install the app via Technitium's API (verified against the real app store
    metadata, not guessed):

    POST /api/apps/downloadAndInstall?name=Query Logs (PostgreSQL)&url=https://download.technitium.com/dns/apps/QueryLogsPostgreSqlApp-v1.2.1.zip
    

    (v1.2.1 is the current release, compatible with our Technitium 15.4.0.)

  3. Configure the app:

    POST /api/apps/config/set?name=Query Logs (PostgreSQL)
    Content-Type: application/x-www-form-urlencoded
    Body: config=<urlencoded JSON>
    

    JSON body: {"enableLogging": true, "databaseName": "<db>", "connectionString": "Server=postgresql.database.svc.cluster.local; Port=5432; Username=<user>; Password=<pass>;", "maxLogDays": <retention>, "maxLogRecords": <retention>}
    The app auto-creates its own table/indexes on first save - no manual schema needed.

Open question - needs a decision before implementing

DNS Apps in Technitium are per-node, not synced via clustering (unlike zones/
settings). So this needs installing on the primary and, if we want logs for queries
answered directly by secondaries too (they're in the same LB pool), on each
secondary as well - all pointed at the same shared Postgres database. Decide scope
(primary only vs. primary+secondaries) before building the Ansible tasks.

Retention

Set maxLogDays/maxLogRecords to something bounded rather than unlimited (0/0
defaults), to avoid unbounded growth in the shared Postgres instance.

## Goal Enable DNS query logging in Technitium, backed by our existing in-cluster Postgres instead of local Sqlite, using Technitium's official "Query Logs (PostgreSQL)" app. ## Plan 1. **Provision a dedicated Postgres database + user**, reusing the existing `postgresql-backup` role pattern already used by k8s-lanager/k8s-zabbix/ k8s-authentik/etc., pointed at `postgresql.database.svc.cluster.local`. Needs a new Bitwarden item (or field) for the dedicated app username/password, following the same convention as `Lanager-Setup`'s DBUsername/DBPassword fields. 2. **Install the app** via Technitium's API (verified against the real app store metadata, not guessed): ``` POST /api/apps/downloadAndInstall?name=Query Logs (PostgreSQL)&url=https://download.technitium.com/dns/apps/QueryLogsPostgreSqlApp-v1.2.1.zip ``` (v1.2.1 is the current release, compatible with our Technitium 15.4.0.) 3. **Configure the app**: ``` POST /api/apps/config/set?name=Query Logs (PostgreSQL) Content-Type: application/x-www-form-urlencoded Body: config=<urlencoded JSON> ``` JSON body: `{"enableLogging": true, "databaseName": "<db>", "connectionString": "Server=postgresql.database.svc.cluster.local; Port=5432; Username=<user>; Password=<pass>;", "maxLogDays": <retention>, "maxLogRecords": <retention>}` The app auto-creates its own table/indexes on first save - no manual schema needed. ## Open question - needs a decision before implementing DNS Apps in Technitium are **per-node, not synced via clustering** (unlike zones/ settings). So this needs installing on the primary and, if we want logs for queries answered directly by secondaries too (they're in the same LB pool), on each secondary as well - all pointed at the same shared Postgres database. Decide scope (primary only vs. primary+secondaries) before building the Ansible tasks. ## Retention Set `maxLogDays`/`maxLogRecords` to something bounded rather than unlimited (0/0 defaults), to avoid unbounded growth in the shared Postgres instance.
Sign in to join this conversation.
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
mconway/Homelab#70
No description provided.