Advanced Database Development

Забелешка: наменето за PostgreSQL project шемата (v04). Во v04: security_alerts.id е INTEGER IDENTITY (не се внесува рачно), computer_id е INTEGER, timestamp е TIMESTAMP, resolved е BOOLEAN. Затоа INSERT-ите не внесуваат id, користат NOW() (не текст) и false (не 'false').

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

Системот треба автоматски да детектира невообичаено однесување на компјутерите и да генерира безбедносни предупредувања без рачна интервенција. Ова вклучува ситуации кога одреден компјутер генерира голем број 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(
      computer_id, alert_type, severity,
      description, timestamp, resolved
    )
    VALUES (
      NEW.computer_id,
      'High CPU Usage',
      'HIGH',
      'CPU usage exceeded 90%',
      NOW(),
      false
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS cpu_alert_trigger ON computer_history;
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(
      computer_id, alert_type, severity,
      description, timestamp, resolved
    )
    VALUES (
      NEW.computer_id,
      'High RAM Usage',
      'HIGH',
      'RAM usage exceeded 90%',
      NOW(),
      false
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS ram_alert_trigger ON computer_history;
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(
    computer_id, alert_type, severity,
    description, timestamp, resolved
  )
  SELECT
    computer_id,
    'Suspicious Activity',
    'HIGH',
    'More than 50 Sysmon events in the last hour',
    NOW(),
    false
  FROM sysmon_events
  WHERE timestamp > NOW() - INTERVAL '1 hour'
  GROUP BY computer_id
  HAVING COUNT(*) > 50;
END;
$$;

Views

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

CREATE OR REPLACE 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, computer_processes_history, network_connections_history) акумулираат голем број записи кои ги успоруваат пребарувањата и го зголемуваат дисковниот простор. Потребен е механизам за автоматско бришење застарени записи.

Implementation

Stored Procedures/Functions

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

CREATE OR REPLACE PROCEDURE cleanup_old_sysmon_events()
LANGUAGE plpgsql AS
$$
BEGIN
  DELETE FROM sysmon_events
  WHERE 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 timestamp < NOW() - INTERVAL '180 days';
END;
$$;

Процедура за чистење на историските табели за процеси и мрежни конекции (v04), постари од 90 дена:

CREATE OR REPLACE PROCEDURE cleanup_old_history()
LANGUAGE plpgsql AS
$$
BEGIN
  DELETE FROM computer_processes_history
  WHERE timestamp < NOW() - INTERVAL '90 days';

  DELETE FROM network_connections_history
  WHERE timestamp < NOW() - INTERVAL '90 days';
END;
$$;

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

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

---

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

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

Implementation

Views

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

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

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

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

Materialized Views

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

CREATE MATERIALIZED VIEW IF NOT EXISTS 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();