sqlite3 – Kurz und bündig (in a Nutshell) unter Ubuntu 20.04

Bei vielen DevOps Themen kann man die sqlite rasch e.g. fürs Testen verwenden. Bei sqlite ist eine Datenbank (mit allen Tabellen) in einer Datei. Dies macht das Backup sehr einfach!

Installation

Ganz einfach mittels apt:

sudo apt install -y sqlite3

Nutzung Cheat Sheet

Du startest die Datenbank einfach indem Du den Dateinamen angiebst. e.g. Für die Asterisk CDR Datenbank:

sqlite3 /var/log/asterisk/master.db

Nun kannst Du Deine SQL Befehla absetzen. Hier ein paar Beispiele:

SELECT * FROM cdr LIMIT 10;

DROP TABLE cdr;

Alternativ kannst Du Kommandos (dot-commands) absetzen:

# um alle Tables anzuzeigen
.tables
# um das DB Schema anzuzeigen
.schema

Beenden Sie die sqlite3 Kommandozeile, indem Sie das End-Of-File-Zeichen des Systems eingeben (normalerweise ein Strg-D). Verwenden Sie das Unterbrechungszeichen (in der Regel ein Strg-C), um eine lange laufende SQL-Anweisung zu beenden.

Mehr Info zu den CLI Kommandos und zu sqlite generell gibt es hier.

Frontend

Es gibt ein Frontend-Tool (sqlitebrowser), welches Ich allerdings noch nie verwendet habe. Da die Server üblicherweise Headless irgendwo in der CLoud stehen macht es für mich mehr Sinn eine Web-basierte Lösung zu nutzen.

SQlite-web

https://github.com/coleifer/sqlite-web

sqlite-web Installation mittels Docker

Erstelle ein Verzeichnis und wechsle hinein:

mkdir ~/sqlite-web
cd ~/sqlite-web

Nun erzeuge ein Dockerfile:

nano Dockerfile

Und paste folgenden Inhalt:

FROM python:3

RUN pip install sqlite-web

VOLUME /db
WORKDIR /db

EXPOSE 8080

# --read-only  ... to ensure nobody can change the DB via this installation
ENTRYPOINT ["sqlite_web", "--host=0.0.0.0", "--port=8080", "--no-browser", "--read-only"]

Nun erzeuge ein die docker-compose.yml Datei:

nano docker-compose.yml

Und paste folgenden Inhalt. Du musst dabei den Dateinamen der sqlite Datenbank angeben. in Folgendem Beispiel habe ich die CDR Datenbank von Asterisk angegeben Weiters kannst Du den Port auf Deiner Maschine ändern:

version: '3'

services:
    sqliteweb:
        container_name: sqlite-web
        build:
          context: .
        ports:
            # expose the port only on localhost. Access to 8080 via apache and reverse proxy
            - 127.0.0.1:8080:8080
        volumes:
            - /var/log/asterisk/master.db:/db/master.db
        command: master.db
        restart: unless-stopped
        healthcheck:
            test: bash -c ">/dev/tcp/127.0.0.1/8080"

In diesem Beispiel ist der Webserver NUR auf localhost unter dem Port 8080 (127.0.0.1:8080) erreichbar. Nimmst Du die 127.0.0.1: weg ist sqliteweb vom public internet auf Deinem Server unter dem Port 8080 erreichbar. -> Sicherheitsproblem!

Nun konfiguriere einen VirtualHost am apacheserver:

nano /etc/apache2/sites-available/sqlite-web.conf

Mit folgendem Inhalt (dabei musst Du den Domainnamen Deines servers verwenden):

<VirtualHost *:80>
  ServerName sqlite.meinserver.at

  ProxyPreserveHost On

  ProxyPass / http://127.0.0.1:8080/
  ProxyPassReverse / http://127.0.0.1:8080/
</VirtualHost>

Mittels a2ensite sqlite-web und anschließendem systemctl reload apache2 kannst Du den neuen VirtualHost enablen und Apache neu laden.

Achtung Sicherheit!

In diesem Beispiel habe ich im Dockerfile den Parameter –read-only mitgegeben. Somit kannst Du die DB nicht verändern. Allerdings kann JEDER die Daten einsehen.

Nachtrag 1:

Basic Auth hinzufügen (in diesem Fall für den Benutzer neuerbenutzer):

sudo htpasswd -c /etc/apache2/.htpasswd neuerbenutzer

Nun musst Du das Passwort (incl. Wiederholung) für den Webbenutzer eingeben.

Füge folgenden code noch vor dem closing-tag </VirtualHost> in /etc/apache2/sites-available/sqlite-web.conf ein.

<Proxy *>
        Order deny,allow
        Allow from all
        Authtype Basic
        Authname "Password Required"
        AuthUserFile /etc/apache2/.htpasswd
        Require valid-user
</Proxy>

Nachtrag 2:

Natürlich sollte / könnte man noch ein letsencrypt Zertifikat installieren um mittels https auf den Server zuzugreifen.