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:

  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

• 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

• 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:

  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

• 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

• 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:

  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

• 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

• 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:

  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

• 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

• 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 токен:

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 токен:

def read_jwt(token: str):
    return jwt.decode(
        token,
        JWT_SECRET,
        algorithms=["HS256"],
        issuer=JWT_ISSUER
    )

Пристапот до заштитените API рути е овозможен преку декораторите @require_user и @require_tenant_admin.

@require_user()
def api_me():
    ...

CORS конфигурација

Бидејќи frontend апликацијата и Flask серверот работат на различни адреси, потребно е овозможување на Cross-Origin Resource Sharing (CORS).

Во системот е конфигурирана листа на дозволени домени:

DEFAULT_ORIGINS = [
    "http://localhost:5173",
    "http://127.0.0.1:5173",
]

Конфигурацијата се извршува преку Flask-CORS:

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) за комуникација помеѓу агентите и серверот.