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();

Тригер за автоматско предупредување при висока искористеност на дискот (> 90%):

CREATE OR REPLACE FUNCTION create_disk_alert()
RETURNS TRIGGER AS
$$
BEGIN
  IF NEW.disk_usage > 90 THEN
    INSERT INTO security_alerts(
      computer_id, alert_type, severity, description, timestamp, resolved
    )
    VALUES (
      NEW.computer_id, 'High Disk Usage', 'MEDIUM',
      'Disk usage exceeded 90%', NOW(), false
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS disk_alert_trigger ON computer_history;
CREATE TRIGGER disk_alert_trigger
AFTER INSERT ON computer_history
FOR EACH ROW
EXECUTE FUNCTION create_disk_alert();

Тригер (BEFORE UPDATE) кој автоматски го ажурира updated_at во env_settings при секоја промена на поставките:

CREATE OR REPLACE FUNCTION set_env_settings_updated_at()
RETURNS TRIGGER AS
$$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS env_settings_updated_trigger ON env_settings;
CREATE TRIGGER env_settings_updated_trigger
BEFORE UPDATE ON env_settings
FOR EACH ROW
EXECUTE FUNCTION set_env_settings_updated_at();

Тригер (напреден) кој при нова проверка на достапност со недостапен сервис (service_availability.is_available = false) автоматски генерира безбедносен аларм, поврзувајќи го сервисот со неговиот компјутер:

CREATE OR REPLACE FUNCTION create_service_down_alert()
RETURNS TRIGGER AS
$$
DECLARE
  v_computer_id INTEGER;
  v_service     TEXT;
BEGIN
  IF NEW.is_available = false THEN
    SELECT computer_id, service_name
      INTO v_computer_id, v_service
      FROM network_services
     WHERE id = NEW.service_id;

    INSERT INTO security_alerts(
      computer_id, alert_type, severity, description, timestamp, resolved
    )
    VALUES (
      v_computer_id, 'Service Unavailable', 'MEDIUM',
      'Network service ' || COALESCE(v_service, '?') || ' is unavailable',
      NOW(), false
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS service_down_trigger ON service_availability;
CREATE TRIGGER service_down_trigger
AFTER INSERT ON service_availability
FOR EACH ROW
EXECUTE FUNCTION create_service_down_alert();

Тригер кој при нов перформансен запис го освежува last_seen на компјутерот (машината се смета за активна штом испраќа податоци):

CREATE OR REPLACE FUNCTION touch_computer_last_seen()
RETURNS TRIGGER AS
$$
BEGIN
  UPDATE computers
     SET last_seen = COALESCE(NEW.timestamp, NOW())
   WHERE id = NEW.computer_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS touch_last_seen_trigger ON computer_history;
CREATE TRIGGER touch_last_seen_trigger
AFTER INSERT ON computer_history
FOR EACH ROW
EXECUTE FUNCTION touch_computer_last_seen();

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();