nixify repo

5 files changed, 224 insertions, 78 deletions

diff --git a/flake.lock b/flake.lock
new file mode 100644
index 0000000..31150c6
--- /dev/null
+++ b/flake.lock
@@ -0,0 +1,46 @@
+{
+  "nodes": {
+    "nixpkgs": {
+      "locked": {
+        "lastModified": 1781607440,
+        "narHash": "sha256-rxO+uc/KFbSJp+pgyXRuAX6QlG9hJdnt0BXpEQRXY+U=",
+        "owner": "NixOS",
+        "repo": "nixpkgs",
+        "rev": "3e41b24abd260e8f71dbe2f5737d24122f972158",
+        "type": "github"
+      },
+      "original": {
+        "id": "nixpkgs",
+        "type": "indirect"
+      }
+    },
+    "pyproject-nix": {
+      "inputs": {
+        "nixpkgs": [
+          "nixpkgs"
+        ]
+      },
+      "locked": {
+        "lastModified": 1781585874,
+        "narHash": "sha256-T4e8kqZ/fsuqYWbcXDcr0EBLClh3VRKVV1XcL/ibf3A=",
+        "owner": "nix-community",
+        "repo": "pyproject.nix",
+        "rev": "112aebcc00ecf20d48a5c08e80660a6852a4707a",
+        "type": "github"
+      },
+      "original": {
+        "owner": "nix-community",
+        "repo": "pyproject.nix",
+        "type": "github"
+      }
+    },
+    "root": {
+      "inputs": {
+        "nixpkgs": "nixpkgs",
+        "pyproject-nix": "pyproject-nix"
+      }
+    }
+  },
+  "root": "root",
+  "version": 7
+}
diff --git a/flake.nix b/flake.nix
new file mode 100644
index 0000000..6044998
--- /dev/null
+++ b/flake.nix
@@ -0,0 +1,52 @@
+{
+  description = "A basic flake using pyproject.toml project metadata";
+
+  inputs = {
+    pyproject-nix = {
+      url = "github:nix-community/pyproject.nix";
+      inputs.nixpkgs.follows = "nixpkgs";
+    };
+  };
+
+  outputs = { nixpkgs, pyproject-nix, ... }:
+    let
+      inherit (nixpkgs) lib;
+
+      project = pyproject-nix.lib.project.loadPyproject {
+        # Read & unmarshal pyproject.toml relative to this project root.
+        # projectRoot is also used to set `src` for renderers such as buildPythonPackage.
+        projectRoot = ./.;
+      };
+
+      # This example is only using x86_64-linux
+      pkgs = nixpkgs.legacyPackages.x86_64-linux;
+
+      python = pkgs.python3;
+
+      # Returns an attribute set that can be passed to `buildPythonPackage`.
+      attrs = project.renderers.buildPythonPackage { inherit python; };
+
+      pkg = python.pkgs.buildPythonPackage (attrs // {
+          meta = {
+            description = "Webinterface for ssh.node5.net";
+            homepage = "https://ssh.node5.net/";
+            changelog = "https://git.node5.net/ssh_log/ssh_log_web_interface/log/";
+            mainProgram = "node5-ssh";
+          };
+          propagatedBuildInputs = (attrs.propagatedBuildInputs or []) ++ [ pkgs.git ];  # Make git binary available
+          postInstall = ''
+          echo $src
+          echo $out
+          cp -r $src/src/static/ $out/${python.sitePackages}
+          cp -r $src/src/templates/ $out/${python.sitePackages}
+          '';
+        });
+
+      # Add missing templates and static content, that wasn't auto discovered by the python build process
+    in
+    {
+      packages.x86_64-linux.default = pkg;
+      pythonPath = "${python.pkgs.makePythonPath attrs.dependencies}:${pkg}/${python.sitePackages}";
+  };
+}
+
diff --git a/pyproject.toml b/pyproject.toml
new file mode 100644
index 0000000..ef976b0
--- /dev/null
+++ b/pyproject.toml
@@ -0,0 +1,17 @@
+[project]
+name = "ssh.node5.net"
+version = "0.1.0"
+description = "flask project to host ssh.node5.net, ssh password login attempt visualizer"
+
+# define any python dependencies
+dependencies = [
+  "flask~=3.0",
+  "pyyaml~=6.0",
+  "psycopg~=3.1",
+]
+
+# define the cli executable
+# here, we define the entry point to be the 'main()' function in the module 'app/main.py'
+[project.scripts]
+node5-ssh = "ssh_node5_net:main"
+
diff --git a/src/db_handler.py b/src/db_handler.py
index 9556d6c..e97c8ce 100644
--- a/src/db_handler.py
+++ b/src/db_handler.py
@@ -1,87 +1,92 @@
+import logging
 import os
 
 import psycopg
-import yaml
 
-with open(os.path.join('configs', 'database.yml'), 'r') as file:
-    db_con_params = yaml.safe_load(file.read())
 
+logger = logging.getLogger(__name__) # Set the logger name, to the name of the module
 
-def get_latest_login_attempts() -> (list[dict], list[str]):
-    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
-        with conn.cursor() as cur:
-            cur.execute("""
-                SELECT login_attempt.id, username, password, ip, login_attempt.timestamp 
-                FROM login_attempt 
-                JOIN connection on connection.id = login_attempt.connection 
-                ORDER BY login_attempt.id desc limit 20;
-                """)
 
-            login_attempts = cur.fetchall()
-            col_names = [desc[0] for desc in cur.description]
-            return login_attempts, col_names
+class DBHandler:
+    def __init__(self, conninfo: str):
+        self.conninfo = conninfo
+        assert bool(self.get_latest_login_attempts()), "No data from database"
 
+    def get_latest_login_attempts(self) -> (list[dict], list[str]):
+        with psycopg.connect(conninfo=self.conninfo, row_factory=psycopg.rows.dict_row) as conn:
+            with conn.cursor() as cur:
+                cur.execute("""
+                    SELECT login_attempt.id, username, password, ip, login_attempt.timestamp 
+                    FROM login_attempt 
+                    JOIN connection on connection.id = login_attempt.connection 
+                    ORDER BY login_attempt.id desc limit 20;
+                    """)
 
-def get_top(column: str) -> (list[dict], list[str]):
-    if column not in ['username', 'password']:
-        raise ValueError(f'{column} is not allowed')
-    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
-        with conn.cursor() as cur:
-            cur.execute(psycopg.sql.SQL("""
-                SELECT {column}, COUNT({column}) 
-                FROM login_attempt 
-                GROUP BY {column}
-                ORDER BY COUNT({column}) DESC 
-                LIMIT 20;
-                """).format(column=psycopg.sql.Identifier(column), ))
+                login_attempts = cur.fetchall()
+                col_names = [desc[0] for desc in cur.description]
+                return login_attempts, col_names
 
-            top_usernames = cur.fetchall()
-            col_names = [desc[0] for desc in cur.description]
-            return top_usernames, col_names
 
+    def get_top(self, column: str) -> (list[dict], list[str]):
+        if column not in ['username', 'password']:
+            raise ValueError(f'{column} is not allowed')
+        with psycopg.connect(conninfo=self.conninfo, row_factory=psycopg.rows.dict_row) as conn:
+            with conn.cursor() as cur:
+                cur.execute(psycopg.sql.SQL("""
+                    SELECT {column}, COUNT({column}) 
+                    FROM login_attempt 
+                    GROUP BY {column}
+                    ORDER BY COUNT({column}) DESC 
+                    LIMIT 20;
+                    """).format(column=psycopg.sql.Identifier(column), ))
 
-def get_password_of_the_month() -> str:
-    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
-        with conn.cursor() as cur:
-            cur.execute("""
-SELECT password
-FROM login_attempt
-WHERE timestamp BETWEEN current_timestamp - interval '1 month' AND timestamp
-GROUP BY password
-ORDER BY COUNT(password) DESC
-LIMIT 1;
-                """)
+                top_usernames = cur.fetchall()
+                col_names = [desc[0] for desc in cur.description]
+                return top_usernames, col_names
 
-            password = cur.fetchone()['password']
-            return password
 
+    def get_password_of_the_month(self) -> str:
+        with psycopg.connect(conninfo=self.conninfo, row_factory=psycopg.rows.dict_row) as conn:
+            with conn.cursor() as cur:
+                cur.execute("""
+    SELECT password
+    FROM login_attempt
+    WHERE timestamp BETWEEN current_timestamp - interval '1 month' AND timestamp
+    GROUP BY password
+    ORDER BY COUNT(password) DESC
+    LIMIT 1;
+                    """)
 
-def get_histogram_detailed() -> str:
-    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
-        with conn.cursor() as cur:
-            cur.execute("""
-SELECT count(la.id) as total_count, date_trunc('hour', la.timestamp) as date, cn.ip
-FROM login_attempt la
-JOIN connection cn on cn.id = la.connection
-WHERE la.timestamp BETWEEN (select max(timestamp) from login_attempt) - interval '3 days' AND (select max(timestamp) from login_attempt)
-GROUP BY date_trunc('hour', la.timestamp), cn.ip
-ORDER BY COUNT(la.id) DESC
-;
-                """)
-            histogram = cur.fetchall()
-            return histogram
+                password = cur.fetchone()['password']
+                return password
 
 
-def get_histogram_simple() -> str:
-    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
-        with conn.cursor() as cur:
-            cur.execute("""
-SELECT count(id) as total_count, date_trunc('hour', timestamp) as date
-FROM login_attempt
-GROUP BY date_trunc('hour', timestamp)
-ORDER BY date_trunc('hour', timestamp)
-LIMIT 48
-;
-                """)
-            histogram = cur.fetchall()
-            return histogram
+    def get_histogram_detailed(self) -> str:
+        with psycopg.connect(conninfo=self.conninfo, row_factory=psycopg.rows.dict_row) as conn:
+            with conn.cursor() as cur:
+                cur.execute("""
+    SELECT count(la.id) as total_count, date_trunc('hour', la.timestamp) as date, cn.ip
+    FROM login_attempt la
+    JOIN connection cn on cn.id = la.connection
+    WHERE la.timestamp BETWEEN (select max(timestamp) from login_attempt) - interval '3 days' AND (select max(timestamp) from login_attempt)
+    GROUP BY date_trunc('hour', la.timestamp), cn.ip
+    ORDER BY COUNT(la.id) DESC
+    ;
+                    """)
+                histogram = cur.fetchall()
+                return histogram
+
+
+    def get_histogram_simple(self) -> str:
+        with psycopg.connect(conninfo=self.conninfo, row_factory=psycopg.rows.dict_row) as conn:
+            with conn.cursor() as cur:
+                cur.execute("""
+    SELECT count(id) as total_count, date_trunc('hour', timestamp) as date
+    FROM login_attempt
+    GROUP BY date_trunc('hour', timestamp)
+    ORDER BY date_trunc('hour', timestamp)
+    LIMIT 48
+    ;
+                    """)
+                histogram = cur.fetchall()
+                return histogram
diff --git a/src/ssh_node5_net.py b/src/ssh_node5_net.py
index afed9f6..35e9700 100644
--- a/src/ssh_node5_net.py
+++ b/src/ssh_node5_net.py
@@ -1,13 +1,19 @@
 import json
 import logging
 import datetime
+import os
 import random
+import time
 
 import flask
 
 import db_handler
 
 
+db_conninfo = os.environ["DB_CONN"]
+db = db_handler.DBHandler(conninfo=db_conninfo)
+
+
 class CustomFormatter(logging.Formatter):
     grey = "\x1b[90;20m"
     blue = "\x1b[34;20m"
@@ -32,7 +38,12 @@ class CustomFormatter(logging.Formatter):
 
 
 logger = logging.getLogger(__name__)
-logger.root.setLevel(logging.INFO)
+
+
+if os.environ.get('FLASK_DEBUG') == '1':  # Local development
+    logger.root.setLevel(logging.DEBUG)
+else:
+    logger.root.setLevel(logging.INFO)
 
 stream_handler = logging.StreamHandler()
 stream_handler.setFormatter(CustomFormatter())
@@ -43,7 +54,7 @@ app = flask.Flask(__name__, template_folder='templates', static_folder='static',
 
 @app.route("/potm")
 def password_of_the_month():
-    password = db_handler.get_password_of_the_month()
+    password = db.get_password_of_the_month()
     return password
 
 
@@ -52,14 +63,16 @@ def format_time(input: datetime.datetime) -> str:
 
 
 '''
-    histogram = db_handler.get_histogram()
+    histogram = db.get_histogram()
     histogram_data = [a['total_count'] for a in histogram]
     histogram_labels = [format_time(a['date']) for a in histogram]
 '''
 
 min_value = 20
 def get_chart():
-    histogram = db_handler.get_histogram_detailed()
+    start_time = time.time()
+    histogram = db.get_histogram_detailed()
+    db_time = time.time()
     all_dates = sorted(list({d['date'] for d in histogram}))
     by_ip = {}
     for data in histogram:
@@ -71,14 +84,20 @@ def get_chart():
             by_ip[ip] = [0] * len(all_dates)
         by_ip[ip][all_dates.index(data['date'])] += data['total_count']
 
+    histogram_labels = [format_time(a) for a in all_dates]
+
+    processing_time = time.time()
     histogram_chartjs = json.dumps([{
         'label': ip,
         'data': data,
         'backgroundColor': f'hsl({random.randrange(0, 360)}, 50%, 50%)',
         'fill': 'start'
     } for index, (ip, data) in enumerate(by_ip.items())])
+    json_time = time.time()
 
-    histogram_labels = [format_time(a) for a in all_dates]
+    print(f'DB time:         {db_time - start_time}\n'
+          f'Processing time: {processing_time - db_time}\n'
+          f'Json time:       {json_time - processing_time}')
     return histogram_chartjs, histogram_labels
 
 
@@ -94,12 +113,12 @@ def chart_page():
 
 @app.route("/")
 def index():
-    latest_loging_attempts = db_handler.get_latest_login_attempts()
+    latest_loging_attempts = db.get_latest_login_attempts()
     for login_attempt in latest_loging_attempts[0]:
         login_attempt['timestamp'] = format_time(login_attempt['timestamp'])
 
-    top_usernames = db_handler.get_top('username')
-    top_passwords = db_handler.get_top('password')
+    top_usernames = db.get_top('username')
+    top_passwords = db.get_top('password')
 
     histogram_chartjs, histogram_labels = get_chart()
 
@@ -110,3 +129,10 @@ def index():
         top_passwords=top_passwords,
         histogram_data=histogram_chartjs,
         histogram_labels=histogram_labels)
+
+def main():
+    global app
+    app.run()
+
+if __name__ == '__main__':
+    main()