Changes between Initial Version and Version 1 of OtherTopics

Timestamp:

08/24/26 18:55:19 (6 weeks ago)

Author:

231118

Comment:

--

OtherTopics

@@ +1 @@
+= Other Topics =
+
+== SQL Performance ==
+Documented performance analysis for complex analytical queries executed in PostgreSQL (project schema, v04). We utilized the `EXPLAIN (ANALYZE, BUFFERS)` paradigm to isolate structural bottlenecks before and after implementing precise indexes, as well as optimizing time-window filters for high selectivity.
+
+=== Report 1: Top Outbound Connections by Process ===
+* '''Query Description:''' Evaluates massive volumes of raw outbound network connection logs (`network_connections_history`) mapped inside a time-bounded window via CTE expressions to detect process-level traffic volume.
+* '''Proposed Indexes:'''
+{{{
+#!sql
+CREATE INDEX idx_nch_timestamp_comp ON network_connections_history (timestamp, computer_id);
+CREATE INDEX idx_computers_tenant_env ON computers (tenant_id, env_name);
+}}}
+
+==== Execution Plan Analysis ====
+* '''Before Index Creation:'''
+  * Plan Output: `Seq Scan on network_connections_history nc` (Rows Removed by Filter: ~96,647 — the engine is forced to perform an expansive sequential evaluation across log records).
+  * Execution Time: 73.836 ms
+[[Image(ss1_before.png)]]
+
+* '''After Index Creation (Optimized Time-Window Filter):'''
+  * Plan Output: `Bitmap Index Scan using idx_nch_timestamp_comp` feeding a `Bitmap Heap Scan on network_connections_history nc`.
+  * Execution Time: 14.924 ms
+[[Image(ss1_after.2.png)]]
+
+* '''Index Usage Verification:''' Yes, the PostgreSQL engine bypassed raw relational sequential evaluation and bound query execution directly via index nodes inside the initial windowed CTE materialization.
+* '''Conclusion:''' Performance scaled by over '''79%''' (73.8 ms → 14.9 ms), successfully shifting execution path to targeted bitmap index scans.
+
+---
+
+=== Report 2: Unresolved Security Alerts by Severity ===
+* '''Query Description:''' Groups and breaks down security events across custom temporal partitions using aggregations to gauge environmental vulnerability baselines.
+* '''Proposed Index:'''
+{{{
+#!sql
+CREATE INDEX idx_sa_timestamp_comp ON security_alerts (timestamp, computer_id, resolved);
+}}}
+
+==== Execution Plan Analysis ====
+* '''Before Index Creation:'''
+  * Plan Output: `Seq Scan on security_alerts sa` (Rows Removed by Filter: ~96,604 — forced sequential full table scan).
+  * Execution Time: 59.983 ms
+[[Image(ss2_before.png)]]
+
+* '''After Index Creation (Optimized Time-Window Filter):'''
+  * Plan Output: `Bitmap Index Scan using idx_sa_timestamp_comp` feeding a `Bitmap Heap Scan on security_alerts sa`.
+  * Execution Time: 8.799 ms
+[[Image(ss2_after.png)]]
+
+* '''Index Usage Verification:''' Yes, explicitly verified. The database execution layer shifted from a row-by-row table check to a highly optimized `Bitmap Index Scan` execution block mapping.
+* '''Conclusion:''' Performance scaled by over '''85%''' (59.9 ms → 8.8 ms), successfully validating index utilization under optimal predicate selectivity conditions.
+
+---
+
+=== Report 3: Resource Hotspots (CPU/RAM Overload) ===
+* '''Query Description:''' Evaluates structural system telemetry logs (`computer_history`) over a time range to flag target hosts with utilization breaches.
+* '''Proposed Index:'''
+{{{
+#!sql
+CREATE INDEX idx_ch_timestamp_comp ON computer_history (timestamp, computer_id);
+}}}
+
+==== Execution Plan Analysis ====
+* '''Before Index Creation:'''
+  * Plan Output: `Seq Scan on computer_history ch` (Rows Removed by Filter: ~96,671) causing an expensive down-stream HashAggregate step across historical partitions.
+  * Execution Time: 61.481 ms
+[[Image(ss3_before.png)]]
+
+* '''After Index Creation:'''
+  * Plan Output: `Bitmap Index Scan using idx_ch_timestamp_comp` feeding a `Bitmap Heap Scan on computer_history ch`.
+  * Execution Time: 3.664 ms
+[[Image(ss3_after.png)]]
+
+* '''Index Usage Verification:''' Yes, successfully achieved an Index path, eliminating the need to parse raw heap blocks.
+* '''Conclusion:''' Performance scaled up by over '''94%''' (61.5 ms → 3.7 ms), preventing telemetry logging pipelines from bottlenecking.
+
+---
+
+=== Report 6: Sysmon Event Anomaly Detection (Complex CTE) ===
+* '''Query Description:''' Deep analytical CTE query calculating overall infrastructural averages to isolate anomalous logging events 1.5x above baseline values using `CROSS JOIN` evaluations.
+* '''Proposed Index:'''
+{{{
+#!sql
+CREATE INDEX idx_se_timestamp_comp ON sysmon_events (timestamp, computer_id);
+}}}
+
+==== Execution Plan Analysis ====
+* '''Before Index Creation:'''
+  * Plan Output: `Seq Scan on sysmon_events se` (Rows Removed by Filter: ~96,619) across the CTE sub-trees to calculate environmental averages.
+  * Execution Time: 240.350 ms
+[[Image(ss6_before.png)]]
+
+* '''After Index Creation:'''
+  * Plan Output: `Index Only Scan using idx_se_timestamp_comp on sysmon_events se` (Heap Fetches: 0 — served entirely from the index).
+  * Execution Time: 1.403 ms
+[[Image(ss6_after.png)]]
+
+* '''Index Usage Verification:''' Yes — achieved an `Index Only Scan` (Heap Fetches: 0), the optimal access path.
+* '''Conclusion:''' Execution times dropped by over '''99%''' (240.4 ms → 1.4 ms), allowing heavy statistical parsing to complete efficiently.
+
+---
+== Security Measures ==
+
+=== Application-Level Security ===
+To secure database interactions within the application stack, the following measures have been programmatically enforced:
+ * '''Prevention of SQL Injection (SQLi):''' All queries use parameterized statements (sqlite3 placeholders `?`) in the Flask backend, strictly separating SQL code from parameters. User-supplied values are never concatenated into SQL strings. (The planned PostgreSQL migration adds the SQLAlchemy layer.)
+ * '''Prevention of Un-authorized Access:''' Implementation of custom decorators `@require_user()` and `@require_tenant_admin()` to intercept endpoints and force strict JWT validation before any data access.
+
+=== Database-Level Security ===
+The database-side protections include:
+ * '''Prevention of SQL Injection in Dynamic Queries:''' Avoiding manual string formatting (f-strings or `%s` concatenation) with user input. All dynamically evaluated filtering explicitly passes parameter tuples.
+ * '''Prevention of Un-authorized Access to Data:''' Multi-tenant structural architecture where every row manipulation isolates and constrains queries via a verified `tenant_id` (and, for agents, a valid `X-Env-Token`).
+
+---
+
+== Other Developments ==
+
+=== JWT автентикација и авторизација ===
+Системот користи JWT (JSON Web Token) за автентикација на корисниците по успешна Google најава.
+
+Процесот се состои од следните чекори:
+ 1. Корисникот се најавува преку Google OAuth.
+ 2. Серверот го верификува Google токенот.
+ 3. Доколку најавата е успешна, серверот креира JWT токен кој содржи: `user_id`, `email`, `role`, `tenant_id`.
+ 4. JWT токенот се зачувува во HttpOnly cookie со име `session`.
+ 5. При секое наредно барање прелистувачот автоматски го испраќа cookie-то.
+ 6. Серверот го верификува JWT токенот и ги чита корисничките информации.
+
+'''Креирање на JWT токен:'''
+{{{
+#!python
+def make_jwt(payload: dict, minutes=60 * 24):
+    exp = datetime.utcnow() + timedelta(minutes=minutes)
+    data = {**payload, "iss": JWT_ISSUER, "exp": exp}
+    return jwt.encode(data, JWT_SECRET, algorithm="HS256")
+}}}
+
+'''Проверка на JWT токен:'''
+{{{
+#!python
+def read_jwt(token: str):
+    return jwt.decode(
+        token,
+        JWT_SECRET,
+        algorithms=["HS256"],
+        issuer=JWT_ISSUER
+    )
+}}}
+
+Пристапот до заштитените API рути е овозможен преку декораторите `@require_user` и `@require_tenant_admin`.
+{{{
+#!python
+@require_user()
+def api_me():
+    ...
+}}}
+
+=== CORS конфигурација ===
+Бидејќи frontend апликацијата и Flask серверот работат на различни адреси, потребно е овозможување на Cross-Origin Resource Sharing (CORS).
+
+Во системот е конфигурирана листа на дозволени домени:
+{{{
+#!python
+DEFAULT_ORIGINS = [
+    "http://localhost:5173",
+    "http://127.0.0.1:5173",
+]
+}}}
+
+Конфигурацијата се извршува преку Flask-CORS:
+{{{
+#!python
+CORS(
+    app,
+    supports_credentials=True,
+    origins=ALLOWED_ORIGINS,
+    allow_headers=[
+        "Content-Type",
+        "X-Admin-Session",
+        "X-Env",
+        "X-Env-Token",
+    ],
+    methods=["GET", "POST", "OPTIONS"],
+)
+}}}
+ * Се дозволуваат барања само од доверливи frontend адреси.
+ * Се дозволува испраќање на JWT cookie преку `supports_credentials=True`.
+ * Се ограничуваат HTTP методите на GET, POST и OPTIONS.
+ * Се контролира кои HTTP заглавија може да се испраќаат кон серверот.
+
+=== Безбедносен модел ===
+Системот користи повеќеслојна безбедност:
+ * Google OAuth за верификација на идентитетот.
+ * JWT токени за одржување на корисничка сесија.
+ * HttpOnly cookies за заштита од JavaScript пристап до токените.
+ * CORS политика за ограничување на дозволените клиентски апликации.
+ * Tenant изолација преку `tenant_id`.
+ * Посебни environment токени (`X-Env-Token`) за комуникација помеѓу агентите и серверот.