Advanced Database Development

Автоматска детекција и пријавување на безбедносни аномалии

Системот треба автоматски да детектира невообичаено однесување на компјутерите и да генерира безбедносни предупредувања без рачна интервенција. Ова вклучува ситуации кога одреден компјутер генерира голем број Sysmon настани во краток период, или кога ресурсната потрошувачка (CPU, RAM) ги надминува критичните прагови. Без автоматска детекција, администраторите мора рачно да ги прегледуваат логовите, што е неефикасно и бавно.

Implementation

Triggers

Тригер за автоматско предупредување при висока искористеност на процесорот. Кога во computer_history се вметнува нов запис со CPU > 90%, автоматски се креира запис во security_alerts:

CREATE OR REPLACE FUNCTION create_cpu_alert()
RETURNS TRIGGER AS
$$
BEGIN
  IF NEW.cpu_usage > 90 THEN
    INSERT INTO security_alerts(
      id, computer_id, alert_type, severity,
      description, timestamp, resolved
    )
    VALUES (
      gen_random_uuid()::text,
      NEW.computer_id::text,
      'High CPU Usage',
      'HIGH',
      'CPU usage exceeded 90%',
      NOW()::text,
      'false'
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cpu_alert_trigger
AFTER INSERT ON computer_history
FOR EACH ROW
EXECUTE FUNCTION create_cpu_alert();

Тригер за автоматско предупредување при висока искористеност на RAM меморијата. Кога во computer_history се вметнува нов запис со RAM > 90%, автоматски се креира запис во security_alerts:

CREATE OR REPLACE FUNCTION create_ram_alert()
RETURNS TRIGGER AS
$$
BEGIN
  IF NEW.ram_usage > 90 THEN
    INSERT INTO security_alerts(
      id, computer_id, alert_type, severity,
      description, timestamp, resolved
    )
    VALUES (
      gen_random_uuid()::text,
      NEW.computer_id::text,
      'High RAM Usage',
      'HIGH',
      'RAM usage exceeded 90%',
      NOW()::text,
      'false'
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ram_alert_trigger
AFTER INSERT ON computer_history
FOR EACH ROW
EXECUTE FUNCTION create_ram_alert();

Stored Procedures/Functions

Процедура за детекција на сомнителна активност врз основа на Sysmon настани. Ако еден компјутер генерирал повеќе од 50 Sysmon настани во последниот час, автоматски се внесува безбедносно предупредување. Оваа процедура може да се извршува периодично (на пр. преку pg_cron):

CREATE OR REPLACE PROCEDURE detect_security_anomalies()
LANGUAGE plpgsql AS
$$
BEGIN
  INSERT INTO security_alerts(
    id, computer_id, alert_type, severity,
    description, timestamp, resolved
  )
  SELECT
    gen_random_uuid()::text,
    computer_id::text,
    'Suspicious Activity',
    'HIGH',
    'More than 50 Sysmon events in the last hour',
    NOW()::text,
    'false'
  FROM sysmon_events
  WHERE CAST(timestamp AS timestamp) > NOW() - INTERVAL '1 hour'
  GROUP BY computer_id
  HAVING COUNT(*) > 50;
END;
$$;

Views

Поглед за безбедносен преглед на системот. Обезбедува брз преглед на вкупниот број Sysmon настани по компјутер, корисен за администраторите при секојдневен мониторинг:

CREATE VIEW security_summary_view AS
SELECT
  c.name,
  COUNT(s.id) AS total_events
FROM computers c
LEFT JOIN sysmon_events s ON c.id = s.computer_id
GROUP BY c.name;

---

Автоматско одржување и чистење на историски податоци

Со текот на времето, табелите со историски податоци (sysmon_events, computer_history) акумулираат голем број записи кои ги успоруваат пребарувањата и ја зголемуваат потрошувачката на дисковен простор. Потребен е механизам за автоматско бришење на застарени записи без рачна интервенција, со цел одржување на перформансите на системот на долг рок.

Implementation

Stored Procedures/Functions

Процедура за автоматско отстранување на стари Sysmon логови (постари од 90 дена):

CREATE OR REPLACE PROCEDURE cleanup_old_sysmon_events()
LANGUAGE plpgsql AS
$$
BEGIN
  DELETE FROM sysmon_events
  WHERE CAST(timestamp AS timestamp) < NOW() - INTERVAL '90 days';
END;
$$;

Процедура за автоматско отстранување на стари записи од computer_history (постари од 180 дена):

CREATE OR REPLACE PROCEDURE cleanup_old_computer_history()
LANGUAGE plpgsql AS
$$
BEGIN
  DELETE FROM computer_history
  WHERE CAST(timestamp AS timestamp) < NOW() - INTERVAL '180 days';
END;
$$;

Двете процедури може да се закажат за периодично извршување преку pg_cron:

-- Извршување секој ден во полноќ
SELECT cron.schedule('0 0 * * *', $$CALL cleanup_old_sysmon_events()$$);
SELECT cron.schedule('0 1 * * *', $$CALL cleanup_old_computer_history()$$);

---

Аналитички погледи за брз пристап до системски информации

Администраторите и апликацијата честопати бараат консолидирани информации за состојбата на компјутерите и нивната активност. Директното пресметување на овие информации при секое барање е бавно кога базата содржи голем број записи. Погледите и материјализираните погледи овозможуваат побрз пристап до вакви статистики.

Implementation

Views

Детален поглед на компјутерите. Консолидиран приказ на основните информации за сите компјутери во системот, без потреба од повторување на JOIN логиката во апликацијата:

CREATE VIEW computer_details_view AS
SELECT
  name,
  "user",
  ip,
  os,
  env_name,
  last_seen
FROM computers;

Поглед за активни (online) компјутери. Прикажува само компјутери кои биле активни во последните 5 минути, корисен за real-time dashboard:

CREATE VIEW active_computers_view AS
SELECT
  c.id,
  c.name,
  c.ip,
  c.env_name,
  c.last_seen
FROM computers c
WHERE CAST(c.last_seen AS timestamp) >= NOW() - INTERVAL '5 minutes';

Materialized Views

Материјализиран поглед за најактивни компјутери. За разлика од обичните погледи, резултатите се физички зачувани, што овозможува значително побрзо извршување на аналитички пребарувања врз голем број историски записи:

CREATE MATERIALIZED VIEW most_active_computers AS
SELECT
  c.id,
  c.name,
  COUNT(h.id) AS total_logs
FROM computers c
JOIN computer_history h ON c.id = h.computer_id
GROUP BY c.id, c.name;

Рачно освежување на материјализираниот поглед (може да се закаже периодично):

REFRESH MATERIALIZED VIEW most_active_computers;

---

Статистички извештаи по околини (environments)

Администраторите треба да имаат увид во тоа колку компјутери се регистрирани во секоја околина, со цел следење на растот на системот и планирање на капацитети. Оваа информација е корисна за квартални извештаи и при носење одлуки за скалирање.

Implementation

Stored Procedures/Functions

Процедура за генерирање статистика по околини. Ја пресметува и ја испечатува (RAISE NOTICE) распределбата на компјутери по секоја околина:

CREATE OR REPLACE PROCEDURE environment_statistics()
LANGUAGE plpgsql AS
$$
DECLARE
  r RECORD;
BEGIN
  FOR r IN (
    SELECT env_name, COUNT(*) AS total
    FROM computers
    GROUP BY env_name
  )
  LOOP
    RAISE NOTICE 'Environment: %, Computers: %', r.env_name, r.total;
  END LOOP;
END;
$$;

Функција која враќа статистика по околини во форма на табела (погодна за употреба во апликацијата):

CREATE OR REPLACE FUNCTION get_environment_statistics()
RETURNS TABLE(env_name TEXT, total_computers BIGINT)
LANGUAGE plpgsql AS
$$
BEGIN
  RETURN QUERY
  SELECT
    c.env_name,
    COUNT(*)::BIGINT AS total_computers
  FROM computers c
  GROUP BY c.env_name
  ORDER BY total_computers DESC;
END;
$$;

Повикување на функцијата:

SELECT * FROM get_environment_statistics();