Other Topics

SQL Performance

Documented performance analysis for complex analytical queries executed in PostgreSQL. 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 mapped inside a time-bounded window via CTE expressions to detect process-level traffic volume.
• Proposed Indexes:

  CREATE INDEX idx_nc_timestamp_comp ON network_connections (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 nc (The engine is forced to perform an expansive sequential evaluation across log records).
  • Execution Time: 18.383 ms

• After Index Creation (Optimized Time-Window Filter):
  • Plan Output: Bitmap Index Scan using idx_nc_timestamp_comp on network_connections nc.
  • Execution Time: 3.271 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 82%, 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 (Forced sequential full table scan table reading).
  • Execution Time: 5.521 ms

• After Index Creation (Optimized Time-Window Filter):
  • Plan Output: Bitmap Index Scan using idx_sa_timestamp_comp on security_alerts sa.
  • Execution Time: 2.853 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 48% for real-time operation filters, successfully validating index utilization under optimal predicate selectivity conditions.

---

Report 3: Resource Hotspots (CPU/RAM Overload)

• Query Description: Evaluates structural system telemetry logs 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 causing an expensive down-stream HashAggregate step across historical partitions.
  • Execution Time: 6.559 ms

• After Index Creation:
  • Plan Output: Bitmap Index Scan using idx_ch_timestamp_comp on computer_history ch.
  • Execution Time: 1.626 ms

• Index Usage Verification: Yes, successfully achieved an Index path, eliminating the need to parse raw heap blocks.
• Conclusion: Performance scaled up by over 75%, 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: Multi-pass sequential loops across table sub-trees to calculate environmental averages.
  • Execution Time: 53.382 ms

• After Index Creation:
  • Plan Output: Evaluated via discrete index conditions (Index Searches: 2) implemented natively across evaluation scopes of the CTE.
  • Execution Time: 32.772 ms

• Index Usage Verification: Yes, the index successfully injected directly inside the localized lookup nodes.
• Conclusion: Execution times dropped by over 38%, 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): Introduction of parameterized queries and ORM abstractions in Flask backend layer to strictly split code and parameters. String concats inside query definitions are structurally banned.
• Prevention of Un-authorized Access: Implementation of custom decorators @require_user() and @require_tenant_admin() to intercept endpoints and force strict validation.

Database-Level Security

The specific architectural database configurations applied directly within PostgreSQL include:

• Prevention of SQL Injection in Dynamic Queries: Avoiding the manual execution of raw string formatting (f-strings or %s concatenation). 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 constraints queries via a verified tenant_id.

---

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