Implement post-install hook to customize gravity.db

[ngrigoriev]

Hello,

I would like to recommend a feature: executing an SQL script form post-install hook. This script can be used to initialize things that are not exposed via gravity.sh - for example, creating the clients/groups, assigning adlists to groups etc.

[MoJo2600]

This is a good idea. I'm happy to merge a pull request if you're willing to write this implementation.

[ngrigoriev]

I would be happy to contribute but after thinking about it I have realized that there are no decent options for doing it. Or at least I fail to see one.

Helm post-install can, essentially, create and delete K8S objects, including jobs. The problem is that we need to modify sqlite3 database (file) inside of pihole container. There is no interface to do it from the outside, no documented API for things like client/group management etc. I do it with a shell script right now which performs "kubectl exec .. cat init.sql | sqlite3" more or less. I think that a K8S job doing something like this is not an appropriate solution.

Having this file on a PVC that can be accessed from another pod? Too much of a requirement for the volume.

So far the only idea I have left is adding an init container that would take the SQL template of the gravity database (/etc/.pihole/ and append custom SQL to it. Of course, this will have to be consistent, e.g. adlists would have to be added in the same script if you want to refer to the groups etc. And there is no way to make it safe - pihole's SQL schema can change at any point.

I should have explained my goal. I do not want to use pihole's management GUI to manage its configuration. I run it in small Kubernetes cluster at home. I want to keep all my configuration in GIT and make pi-hole deployment, essentially, discardable. So instead of configuration backup/restore I want to be able to uninstall it and reinstall it with all the parameters I need, groups, custom white/black lists etc. If I want to modify the blacklist I would change it in git and redeploy. I want to run two pi-hole replicas but if my configuration is, essentially, read-only, I do not need any replication for them. Init once, use until it is deleted.

[lloydw]

I have the same use case and worked around it with an init container like this:

adlists:
- https://list1
- https://list2
extraInitContainers:
  - name: lists
    image: pihole/pihole:2024.03.2
    command:
    - sh
    - "-c"
    - cp /etc/pihole/gravity.db /target/ && cat /adlists/adlists.list | xargs -I{} sudo sqlite3 /target/gravity.db "INSERT INTO adlist (address, enabled, comment) VALUES ('{}', 1, 'comment');"
    volumeMounts:
    - mountPath: /target
      name: config
    - mountPath: /adlists
      name: adlists

It might be nice to update the adlists parameter to do something like this by default? Happy to send a PR if it would be accepted.