= Advanced Database Development =

Забелешка: наменето за PostgreSQL project шемата (v04). Во v04: security_alerts.id е INTEGER IDENTITY (не се внесува рачно), computer_id е INTEGER, timestamp е TIMESTAMP, resolved е BOOLEAN. Затоа INSERT-ите не внесуваат id, користат NOW() (не текст) и false (не 'false'). Редоследот на имплементацијата во секој концепт е: Triggers, Stored procedures/functions, Views, Custom domains (само оние што важат за концептот).

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

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

'''Implementation:'''

==== Triggers ====

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

{{{
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 меморијата (RAM > 90%):

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

Тригер за предупредување при висока искористеност на дискот (Disk > 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();
}}}

Тригер кој при нова проверка на достапност со недостапен сервис автоматски генерира аларм, поврзувајќи го сервисот со неговиот компјутер:

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

==== Stored procedures/functions ====

Процедура за детекција на сомнителна активност: ако компјутер генерирал повеќе од 50 Sysmon настани во последниот час, се внесува безбедносно предупредување:

{{{
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;
}}}

==== Custom domains ====

Домен за тежината на алармите (ограничен сет вредности) и домен за процентот на искористеност (0..100), кои се користат за severity во security_alerts и за метриките во computer_history:

{{{
CREATE DOMAIN severity_level AS TEXT
  CHECK (lower(VALUE) IN ('low','medium','high','critical'));

CREATE DOMAIN usage_percent AS DOUBLE PRECISION
  CHECK (VALUE >= 0 AND VALUE <= 100);
}}}

----

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

Одредени изведени/контролни полиња треба автоматски да се одржуваат конзистентни без апликацискиот слој да мора експлицитно да ги ажурира: полето updated_at во env_settings мора да се освежи при секоја промена на поставките, а last_seen на компјутер мора да се освежи штом машината испрати нови податоци.

'''Implementation:'''

==== Triggers ====

Тригер (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();
}}}

Тригер кој при нов перформансен запис го освежува 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();
}}}

----

== Автоматско чистење на историски податоци (background jobs) ==

Историските табели (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;
$$;
}}}

Процедура за чистење на историските табели за процеси и мрежни конекции (постари од 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;
$$;
}}}

Background jobs: процедурите се закажуваат за периодично извршување преку 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 минути:

{{{
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';
}}}

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

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

----

== Валидација на влезни податоци преку сопствени домени ==

Одреден дел од колоните бараат построги ограничувања на вредностите кои се повторуваат низ повеќе табели: портите мора да се во опсег 1..65535, мрежниот протокол е TCP/UDP, а е-поштата мора да има валиден формат. Наместо истите CHECK ограничувања да се повторуваат на секоја колона, тие се централизирани во сопствени домени и повторно се употребуваат насекаде во шемата.

'''Implementation:'''

==== Custom domains ====

{{{
-- Порта: дозволен опсег 1..65535
CREATE DOMAIN port_number AS INTEGER
  CHECK (VALUE BETWEEN 1 AND 65535);

-- Мрежен протокол
CREATE DOMAIN protocol_type AS TEXT
  CHECK (upper(VALUE) IN ('TCP','UDP'));

-- Е-пошта (основна проверка на формат)
CREATE DOMAIN email_address AS TEXT
  CHECK (VALUE ~ '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$');
}}}

Примена на домените врз колоните во шемата:

{{{
-- tenants.owner_email, users.email  -> email_address
-- network_services.port             -> port_number
-- network_services.protocol         -> protocol_type
}}}

Пример на употреба при креирање табела (доменот се пишува наместо базниот тип):

{{{
CREATE TABLE network_services (
  id           INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  computer_id  INTEGER NOT NULL,
  service_name TEXT,
  port         port_number   NOT NULL,
  protocol     protocol_type NOT NULL,
  status       TEXT,
  last_checked TIMESTAMP,
  CONSTRAINT fk_network_services_computer FOREIGN KEY (computer_id) REFERENCES computers(id) ON DELETE CASCADE
);
}}}

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

{{{
ALTER TABLE network_services ALTER COLUMN port TYPE port_number;
ALTER TABLE network_services ALTER COLUMN protocol TYPE protocol_type;
}}}