add sqlite session timeout storage

3 files changed, 92 insertions, 18 deletions

diff --git a/README.md b/README.md
index 1b31200..872fa20 100644
--- a/README.md
+++ b/README.md
@@ -5,31 +5,46 @@ SESSION STORAGE
 ---------------
 
 This is a quick guide on how to set up the session storage.
-Your options are:
+Your options are Redis/Valkey, MySQL/MariaDB or SQLite.
+They must be reachable by localhost or writable by the current user.
+
+This is an example configuration:
+
+    [JWEBMAIL.READ_MAILS]
+    SESSION_TYPE = 'REDIS'
+    SESSION_STORE_PASSWD = '$PASSWORD'
+
+Make sure the config file can only be read by user/group `jwebmail` when you specify a password.
+
+### SQLite
+
+1. Make sure the db is created at a location where the jwebmail user has
+   read and write permissions. Use the SESSION_STORE_DB_NAME value to set
+   the path.
 
 ### Redis
 
 1. Create a user 'jwebmail'
+2. Grant privileges for that user to 'jwm:user:*' for the commands setex and getex.
 
-That's it.
+        ACL SETUSER jwebmail ~jwm:user:* -@all +setex +getex >$PASSWORD
 
 ### MySQL / MariaDB
 
 1. Create a user 'jwebmail' with a password
 
-    CREATE USER jwebmail@localhost IDENTIFIED BY '$PASSWORD';
+        CREATE USER jwebmail@localhost IDENTIFIED BY '$PASSWORD';
 
 2. Create a database 'jwebmaildb1'
 
-    CREATE DATABASE jwebmaildb1;
-    USE jwebmaildb1;
+        CREATE DATABASE jwebmaildb1;
+        USE jwebmaildb1;
 
 3. Create a table 'session' with the following schema
 
-    CREATE TABLE session (user char(64) PRIMARY KEY, password varchar(255), timeout timestamp NOT NULL);
-    CREATE INDEX timeout_idx ON session (timeout); -- Optional
+        CREATE TABLE session (user char(64) PRIMARY KEY, password varchar(255), timeout timestamp NOT NULL);
+        CREATE INDEX timeout_idx ON session (timeout); -- Optional
 
 4. Grant privileges to the user jwebmail for the above table for at least SELECT, INSERT, UPDATE and DELETE
 
-    GRANT SELECT, INSERT, UPDATE, DELETE PRIVILEGES ON 'jwebmaildb1'.'session' TO 'jwebmail'@'localhost';
-
+        GRANT SELECT, INSERT, UPDATE, DELETE PRIVILEGES ON 'jwebmaildb1'.'session' TO 'jwebmail'@'localhost';
diff --git a/src/jwebmail/__init__.py b/src/jwebmail/__init__.py
index 875bcb5..442590b 100644
--- a/src/jwebmail/__init__.py
+++ b/src/jwebmail/__init__.py
@@ -36,7 +36,7 @@ else:
 
     toml_read_file = dict(load=toml_load, text=True)
 
-__version__ = "2.3.0.dev4"
+__version__ = "2.4.0.dev0"
 
 csrf = CSRFProtect()
 
diff --git a/src/jwebmail/read_mails.py b/src/jwebmail/read_mails.py
index 404a242..05c6e10 100644
--- a/src/jwebmail/read_mails.py
+++ b/src/jwebmail/read_mails.py
@@ -35,6 +35,9 @@ class RedisTimeoutSession:
     def get(self, key):
         return self.conn.getex(f"jwm:user:{key}", self.timeout)
 
+    def close(self):
+        self.conn.close()
+
 
 class MysqlTimeoutSession:
     def __init__(self, username, passwd, timeout, database="jwebmaildb1", port=3306):
@@ -76,14 +79,72 @@ class MysqlTimeoutSession:
                 self.conn.commit()
                 return row[0]
 
+    def close(self):
+        self.conn.close()
+
+
+class SqliteTimeoutSession:
+    def __init__(self, _username, _passwd, timeout, database):
+        import sqlite3
+
+        self.timeout = timeout
+
+        self.conn = sqlite3.connect(database, autocommit=False)
+        cur = self.conn.cursor()
+        cur.execute(
+            "CREATE TABLE IF NOT EXISTS session (user text PRIMARY KEY, password text, timeout real NOT NULL) STRICT"
+        )
+        cur.execute("CREATE INDEX IF NOT EXISTS timeout_idx ON session (timeout)")
+
+    def set(self, key, value):
+        timeout = datetime.now() + timedelta(seconds=self.timeout)
+
+        with closing(self.conn.cursor()) as cur:
+            cur.execute(
+                "REPLACE INTO session VALUES (?, ?, unixepoch(?, 'subsec'))",
+                [key, value, timeout],
+            )
+            self.conn.commit()
+
+    def get(self, key):
+        with closing(self.conn.cursor()) as cur:
+            cur.execute("DELETE FROM session WHERE timeout < unixepoch('subsec')")
+            cur.execute("SELECT password FROM session WHERE user = ?", [key])
+            row = cur.fetchone()
+
+            if row is None:
+                self.conn.commit()
+                return None
+            else:
+                timeout = datetime.now() + timedelta(seconds=self.timeout)
+                cur.execute(
+                    "UPDATE session SET timeout = unixepoch(?, 'subsec') WHERE user = ?",
+                    [timeout, key],
+                )
+                self.conn.commit()
+                return row[0]
+
+    def close(self):
+        self.conn.close()
+
 
 def select_timeout_session():
     session_type = current_app.config["JWEBMAIL"]["READ_MAILS"]["SESSION_TYPE"]
 
+    user = "jwebmail"
+    passwd = current_app.config["JWEBMAIL"]["READ_MAILS"]["SESSION_STORE_PASSWD"]
+    args = dict()
+    db_name = current_app.config["JWEBMAIL"]["READ_MAILS"].get("SESSION_STORE_DB_NAME")
+    if db_name:
+        args["database"] = db_name
+
     if session_type == "REDIS":
-        return RedisTimeoutSession
+        return RedisTimeoutSession(user, passwd, EXPIRATION_SEC)
     elif session_type == "MYSQL":
-        return MysqlTimeoutSession
+        return MysqlTimeoutSession(user, passwd, EXPIRATION_SEC, **args)
+    elif session_type == "SQLITE":
+        args.setdefault("database", "/var/local/lib/jwebmail/jwebmail.sqlite3")
+        return SqliteTimeoutSession(user, passwd, EXPIRATION_SEC, **args)
     else:
         raise ValueError(f"unknown session_type {session_type!r}")
 
@@ -104,17 +165,15 @@ def login(username, password):
 
 
 def add_user(user: JWebmailUser):
-    passwd = current_app.config["JWEBMAIL"]["READ_MAILS"]["SESSION_STORE_PASSWD"]
-
-    r = select_timeout_session()("jwebmail", passwd, EXPIRATION_SEC)
+    r = select_timeout_session()
     r.set(user.get_id(), user.password)
+    r.close()
 
 
 def load_user(username: str) -> JWebmailUser:
-    ss_password = current_app.config["JWEBMAIL"]["READ_MAILS"]["SESSION_STORE_PASSWD"]
-
-    r = select_timeout_session()("jwebmail", ss_password, EXPIRATION_SEC)
+    r = select_timeout_session()
     passwd = r.get(username)
+    r.close()
     if passwd is None:
         return None
     return JWebmailUser(username, passwd)