Changes between Version 3 and Version 4 of AdvancedDatabaseDevelopment


Ignore:
Timestamp:
08/24/26 19:33:33 (3 days ago)
Author:
231118
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedDatabaseDevelopment

    v3 v4  
    11= Advanced Database Development =
    22
    3 Забелешка: наменето за PostgreSQL '''project''' шемата (v04). Во v04:
    4 security_alerts.id е INTEGER IDENTITY (не се внесува рачно), computer_id е INTEGER,
    5 timestamp е TIMESTAMP, resolved е BOOLEAN. Затоа INSERT-ите не внесуваат id,
    6 користат NOW() (не текст) и false (не 'false').
     3Забелешка: наменето за 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 (само оние што важат за концептот).
    74
    85== Автоматска детекција и пријавување на безбедносни аномалии ==
    96
    10 Системот треба автоматски да детектира невообичаено однесување на компјутерите и да генерира безбедносни предупредувања без рачна интервенција. Ова вклучува ситуации кога одреден компјутер генерира голем број Sysmon настани во краток период, или кога ресурсната потрошувачка (CPU, RAM) ги надминува критичните прагови.
    11 
    12 === Implementation ===
     7Системот треба автоматски да детектира невообичаено однесување на компјутерите и да генерира безбедносни предупредувања без рачна интервенција — кога ресурсната потрошувачка (CPU/RAM/Disk) ги надминува критичните прагови, кога сервис станува недостапен, или кога компјутер генерира голем број Sysmon настани во краток период.
     8
     9'''Implementation:'''
    1310
    1411==== Triggers ====
    1512
    16 Тригер за автоматско предупредување при висока искористеност на процесорот.
    17 Кога во `computer_history` се вметнува нов запис со CPU > 90%, автоматски се креира запис во `security_alerts`:
     13Тригер за предупредување при висока искористеност на процесорот (CPU > 90%):
    1814
    1915{{{
    … …  
    2319BEGIN
    2420  IF NEW.cpu_usage > 90 THEN
    25     INSERT INTO security_alerts(
    26       computer_id, alert_type, severity,
    27       description, timestamp, resolved
    28     )
    29     VALUES (
    30       NEW.computer_id,
    31       'High CPU Usage',
    32       'HIGH',
    33       'CPU usage exceeded 90%',
    34       NOW(),
    35       false
    36     );
     21    INSERT INTO security_alerts(computer_id, alert_type, severity, description, timestamp, resolved)
     22    VALUES (NEW.computer_id, 'High CPU Usage', 'HIGH', 'CPU usage exceeded 90%', NOW(), false);
    3723  END IF;
    3824  RETURN NEW;
    … …  
    4329CREATE TRIGGER cpu_alert_trigger
    4430AFTER INSERT ON computer_history
    45 FOR EACH ROW
    46 EXECUTE FUNCTION create_cpu_alert();
    47 }}}
    48 
    49 Тригер за автоматско предупредување при висока искористеност на RAM меморијата.
    50 Кога во `computer_history` се вметнува нов запис со RAM > 90%, автоматски се креира запис во `security_alerts`:
     31FOR EACH ROW EXECUTE FUNCTION create_cpu_alert();
     32}}}
     33
     34Тригер за предупредување при висока искористеност на RAM меморијата (RAM > 90%):
    5135
    5236{{{
    … …  
    5640BEGIN
    5741  IF NEW.ram_usage > 90 THEN
    58     INSERT INTO security_alerts(
    59       computer_id, alert_type, severity,
    60       description, timestamp, resolved
    61     )
    62     VALUES (
    63       NEW.computer_id,
    64       'High RAM Usage',
    65       'HIGH',
    66       'RAM usage exceeded 90%',
    67       NOW(),
    68       false
    69     );
     42    INSERT INTO security_alerts(computer_id, alert_type, severity, description, timestamp, resolved)
     43    VALUES (NEW.computer_id, 'High RAM Usage', 'HIGH', 'RAM usage exceeded 90%', NOW(), false);
    7044  END IF;
    7145  RETURN NEW;
    … …  
    7650CREATE TRIGGER ram_alert_trigger
    7751AFTER INSERT ON computer_history
    78 FOR EACH ROW
    79 EXECUTE FUNCTION create_ram_alert();
    80 }}}
    81 
    82 Тригер за автоматско предупредување при висока искористеност на дискот (> 90%):
     52FOR EACH ROW EXECUTE FUNCTION create_ram_alert();
     53}}}
     54
     55Тригер за предупредување при висока искористеност на дискот (Disk > 90%):
    8356
    8457{{{
    … …  
    8861BEGIN
    8962  IF NEW.disk_usage > 90 THEN
    90     INSERT INTO security_alerts(
    91       computer_id, alert_type, severity, description, timestamp, resolved
    92     )
    93     VALUES (
    94       NEW.computer_id, 'High Disk Usage', 'MEDIUM',
    95       'Disk usage exceeded 90%', NOW(), false
    96     );
     63    INSERT INTO security_alerts(computer_id, alert_type, severity, description, timestamp, resolved)
     64    VALUES (NEW.computer_id, 'High Disk Usage', 'MEDIUM', 'Disk usage exceeded 90%', NOW(), false);
    9765  END IF;
    9866  RETURN NEW;
    … …  
    10371CREATE TRIGGER disk_alert_trigger
    10472AFTER INSERT ON computer_history
    105 FOR EACH ROW
    106 EXECUTE FUNCTION create_disk_alert();
    107 }}}
    108 
    109 Тригер (BEFORE UPDATE) кој автоматски го ажурира `updated_at` во `env_settings`
    110 при секоја промена на поставките:
    111 
    112 {{{
    113 CREATE OR REPLACE FUNCTION set_env_settings_updated_at()
    114 RETURNS TRIGGER AS
    115 $$
    116 BEGIN
    117   NEW.updated_at := NOW();
    118   RETURN NEW;
    119 END;
    120 $$ LANGUAGE plpgsql;
    121 
    122 DROP TRIGGER IF EXISTS env_settings_updated_trigger ON env_settings;
    123 CREATE TRIGGER env_settings_updated_trigger
    124 BEFORE UPDATE ON env_settings
    125 FOR EACH ROW
    126 EXECUTE FUNCTION set_env_settings_updated_at();
    127 }}}
    128 
    129 Тригер (напреден) кој при нова проверка на достапност со недостапен сервис
    130 (`service_availability.is_available = false`) автоматски генерира безбедносен
    131 аларм, поврзувајќи го сервисот со неговиот компјутер:
     73FOR EACH ROW EXECUTE FUNCTION create_disk_alert();
     74}}}
     75
     76Тригер кој при нова проверка на достапност со недостапен сервис автоматски генерира аларм, поврзувајќи го сервисот со неговиот компјутер:
    13277
    13378{{{
    … …  
    14085BEGIN
    14186  IF NEW.is_available = false THEN
    142     SELECT computer_id, service_name
    143       INTO v_computer_id, v_service
    144       FROM network_services
    145      WHERE id = NEW.service_id;
    146 
    147     INSERT INTO security_alerts(
    148       computer_id, alert_type, severity, description, timestamp, resolved
    149     )
    150     VALUES (
    151       v_computer_id, 'Service Unavailable', 'MEDIUM',
    152       'Network service ' || COALESCE(v_service, '?') || ' is unavailable',
    153       NOW(), false
    154     );
     87    SELECT computer_id, service_name INTO v_computer_id, v_service
     88      FROM network_services WHERE id = NEW.service_id;
     89    INSERT INTO security_alerts(computer_id, alert_type, severity, description, timestamp, resolved)
     90    VALUES (v_computer_id, 'Service Unavailable', 'MEDIUM',
     91            'Network service ' || COALESCE(v_service, '?') || ' is unavailable', NOW(), false);
    15592  END IF;
    15693  RETURN NEW;
    … …  
    16198CREATE TRIGGER service_down_trigger
    16299AFTER INSERT ON service_availability
    163 FOR EACH ROW
    164 EXECUTE FUNCTION create_service_down_alert();
    165 }}}
    166 
    167 Тригер кој при нов перформансен запис го освежува `last_seen` на компјутерот
    168 (машината се смета за активна штом испраќа податоци):
    169 
    170 {{{
    171 CREATE OR REPLACE FUNCTION touch_computer_last_seen()
    172 RETURNS TRIGGER AS
    173 $$
    174 BEGIN
    175   UPDATE computers
    176      SET last_seen = COALESCE(NEW.timestamp, NOW())
    177    WHERE id = NEW.computer_id;
    178   RETURN NEW;
    179 END;
    180 $$ LANGUAGE plpgsql;
    181 
    182 DROP TRIGGER IF EXISTS touch_last_seen_trigger ON computer_history;
    183 CREATE TRIGGER touch_last_seen_trigger
    184 AFTER INSERT ON computer_history
    185 FOR EACH ROW
    186 EXECUTE FUNCTION touch_computer_last_seen();
    187 }}}
    188 
    189 ==== Stored Procedures/Functions ====
    190 
    191 Процедура за детекција на сомнителна активност врз основа на Sysmon настани.
    192 Ако еден компјутер генерирал повеќе од 50 Sysmon настани во последниот час, автоматски се внесува безбедносно предупредување. Може да се извршува периодично (на пр. преку `pg_cron`):
     100FOR EACH ROW EXECUTE FUNCTION create_service_down_alert();
     101}}}
     102
     103==== Stored procedures/functions ====
     104
     105Процедура за детекција на сомнителна активност: ако компјутер генерирал повеќе од 50 Sysmon настани во последниот час, се внесува безбедносно предупредување:
    193106
    194107{{{
    … …  
    197110$$
    198111BEGIN
    199   INSERT INTO security_alerts(
    200     computer_id, alert_type, severity,
    201     description, timestamp, resolved
    202   )
    203   SELECT
    204     computer_id,
    205     'Suspicious Activity',
    206     'HIGH',
    207     'More than 50 Sysmon events in the last hour',
    208     NOW(),
    209     false
     112  INSERT INTO security_alerts(computer_id, alert_type, severity, description, timestamp, resolved)
     113  SELECT computer_id, 'Suspicious Activity', 'HIGH',
     114         'More than 50 Sysmon events in the last hour', NOW(), false
    210115  FROM sysmon_events
    211116  WHERE timestamp > NOW() - INTERVAL '1 hour'
    … …  
    218123==== Views ====
    219124
    220 Поглед за безбедносен преглед на системот.
    221 Обезбедува брз преглед на вкупниот број Sysmon настани по компјутер:
     125Поглед за безбедносен преглед — вкупен број Sysmon настани по компјутер:
    222126
    223127{{{
    224128CREATE OR REPLACE VIEW security_summary_view AS
    225 SELECT
    226   c.name,
    227   COUNT(s.id) AS total_events
     129SELECT c.name, COUNT(s.id) AS total_events
    228130FROM computers c
    229131LEFT JOIN sysmon_events s ON c.id = s.computer_id
    … …  
    231133}}}
    232134
    233 ----
    234 
    235 == Автоматско одржување и чистење на историски податоци ==
    236 
    237 Со текот на времето, историските табели (`sysmon_events`, `computer_history`, `computer_processes_history`, `network_connections_history`) акумулираат голем број записи кои ги успоруваат пребарувањата и го зголемуваат дисковниот простор. Потребен е механизам за автоматско бришење застарени записи.
    238 
    239 === Implementation ===
    240 
    241 ==== Stored Procedures/Functions ====
     135==== Custom domains ====
     136
     137Домен за тежината на алармите (ограничен сет вредности) и домен за процентот на искористеност (0..100), кои се користат за severity во security_alerts и за метриките во computer_history:
     138
     139{{{
     140CREATE DOMAIN severity_level AS TEXT
     141  CHECK (lower(VALUE) IN ('low','medium','high','critical'));
     142
     143CREATE DOMAIN usage_percent AS DOUBLE PRECISION
     144  CHECK (VALUE >= 0 AND VALUE <= 100);
     145}}}
     146
     147----
     148
     149== Автоматско одржување на конзистентност на податоците ==
     150
     151Одредени изведени/контролни полиња треба автоматски да се одржуваат конзистентни без апликацискиот слој да мора експлицитно да ги ажурира: полето updated_at во env_settings мора да се освежи при секоја промена на поставките, а last_seen на компјутер мора да се освежи штом машината испрати нови податоци.
     152
     153'''Implementation:'''
     154
     155==== Triggers ====
     156
     157Тригер (BEFORE UPDATE) кој автоматски го ажурира updated_at во env_settings:
     158
     159{{{
     160CREATE OR REPLACE FUNCTION set_env_settings_updated_at()
     161RETURNS TRIGGER AS
     162$$
     163BEGIN
     164  NEW.updated_at := NOW();
     165  RETURN NEW;
     166END;
     167$$ LANGUAGE plpgsql;
     168
     169DROP TRIGGER IF EXISTS env_settings_updated_trigger ON env_settings;
     170CREATE TRIGGER env_settings_updated_trigger
     171BEFORE UPDATE ON env_settings
     172FOR EACH ROW EXECUTE FUNCTION set_env_settings_updated_at();
     173}}}
     174
     175Тригер кој при нов перформансен запис го освежува last_seen на компјутерот:
     176
     177{{{
     178CREATE OR REPLACE FUNCTION touch_computer_last_seen()
     179RETURNS TRIGGER AS
     180$$
     181BEGIN
     182  UPDATE computers SET last_seen = COALESCE(NEW.timestamp, NOW())
     183   WHERE id = NEW.computer_id;
     184  RETURN NEW;
     185END;
     186$$ LANGUAGE plpgsql;
     187
     188DROP TRIGGER IF EXISTS touch_last_seen_trigger ON computer_history;
     189CREATE TRIGGER touch_last_seen_trigger
     190AFTER INSERT ON computer_history
     191FOR EACH ROW EXECUTE FUNCTION touch_computer_last_seen();
     192}}}
     193
     194----
     195
     196== Автоматско чистење на историски податоци (background jobs) ==
     197
     198Историските табели (sysmon_events, computer_history, computer_processes_history, network_connections_history) акумулираат голем број записи кои ги успоруваат пребарувањата и го зголемуваат дисковниот простор. Потребен е механизам за автоматско бришење застарени записи преку закажани позадински задачи.
     199
     200'''Implementation:'''
     201
     202==== Stored procedures/functions ====
    242203
    243204Процедура за отстранување стари Sysmon логови (постари од 90 дена):
    … …  
    248209$$
    249210BEGIN
    250   DELETE FROM sysmon_events
    251   WHERE timestamp < NOW() - INTERVAL '90 days';
    252 END;
    253 $$;
    254 }}}
    255 
    256 Процедура за отстранување стари записи од `computer_history` (постари од 180 дена):
     211  DELETE FROM sysmon_events WHERE timestamp < NOW() - INTERVAL '90 days';
     212END;
     213$$;
     214}}}
     215
     216Процедура за отстранување стари записи од computer_history (постари од 180 дена):
    257217
    258218{{{
    … …  
    261221$$
    262222BEGIN
    263   DELETE FROM computer_history
    264   WHERE timestamp < NOW() - INTERVAL '180 days';
    265 END;
    266 $$;
    267 }}}
    268 
    269 Процедура за чистење на историските табели за процеси и мрежни конекции (v04), постари од 90 дена:
     223  DELETE FROM computer_history WHERE timestamp < NOW() - INTERVAL '180 days';
     224END;
     225$$;
     226}}}
     227
     228Процедура за чистење на историските табели за процеси и мрежни конекции (постари од 90 дена):
    270229
    271230{{{
    … …  
    274233$$
    275234BEGIN
    276   DELETE FROM computer_processes_history
    277   WHERE timestamp < NOW() - INTERVAL '90 days';
    278 
    279   DELETE FROM network_connections_history
    280   WHERE timestamp < NOW() - INTERVAL '90 days';
    281 END;
    282 $$;
    283 }}}
    284 
    285 Процедурите може да се закажат за периодично извршување преку `pg_cron`:
    286 
    287 {{{
    288 -- Извршување секој ден во раните утрински часови
     235  DELETE FROM computer_processes_history WHERE timestamp < NOW() - INTERVAL '90 days';
     236  DELETE FROM network_connections_history WHERE timestamp < NOW() - INTERVAL '90 days';
     237END;
     238$$;
     239}}}
     240
     241Background jobs: процедурите се закажуваат за периодично извршување преку pg_cron:
     242
     243{{{
    289244SELECT cron.schedule('0 0 * * *', $$CALL cleanup_old_sysmon_events()$$);
    290245SELECT cron.schedule('0 1 * * *', $$CALL cleanup_old_computer_history()$$);
    … …  
    296251== Аналитички погледи за брз пристап до системски информации ==
    297252
    298 Администраторите и апликацијата честопати бараат консолидирани информации за состојбата на компјутерите. Директното пресметување при секое барање е бавно кога базата содржи голем број записи. Погледите и материјализираните погледи овозможуваат побрз пристап.
    299 
    300 === Implementation ===
     253Администраторите и апликацијата честопати бараат консолидирани информации за состојбата на компјутерите. Директното пресметување при секое барање е бавно кога базата содржи голем број записи; погледите и материјализираните погледи овозможуваат побрз пристап.
     254
     255'''Implementation:'''
    301256
    302257==== Views ====
    303258
    304 Детален поглед на компјутерите.
    305 Консолидиран приказ на основните информации за сите компјутери:
     259Детален поглед на компјутерите — консолидиран приказ на основните информации:
    306260
    307261{{{
    308262CREATE OR REPLACE VIEW computer_details_view AS
    309 SELECT
    310   name,
    311   "user",
    312   ip,
    313   os,
    314   env_name,
    315   last_seen
     263SELECT name, "user", ip, os, env_name, last_seen
    316264FROM computers;
    317265}}}
    318266
    319 Поглед за активни (online) компјутери.
    320 Прикажува само компјутери активни во последните 5 минути, корисен за real-time dashboard:
     267Поглед за активни (online) компјутери — активни во последните 5 минути:
    321268
    322269{{{
    323270CREATE OR REPLACE VIEW active_computers_view AS
    324 SELECT
    325   c.id,
    326   c.name,
    327   c.ip,
    328   c.env_name,
    329   c.last_seen
     271SELECT c.id, c.name, c.ip, c.env_name, c.last_seen
    330272FROM computers c
    331273WHERE c.last_seen >= NOW() - INTERVAL '5 minutes';
    332274}}}
    333275
    334 ==== Materialized Views ====
    335 
    336 Материјализиран поглед за најактивни компјутери.
    337 Резултатите се физички зачувани, што овозможува побрзо извршување на аналитички пребарувања врз голем број историски записи:
     276Материјализиран поглед за најактивни компјутери — резултатите се физички зачувани за побрзи аналитички пребарувања:
    338277
    339278{{{
    340279CREATE MATERIALIZED VIEW IF NOT EXISTS most_active_computers AS
    341 SELECT
    342   c.id,
    343   c.name,
    344   COUNT(h.id) AS total_logs
     280SELECT c.id, c.name, COUNT(h.id) AS total_logs
    345281FROM computers c
    346282JOIN computer_history h ON c.id = h.computer_id
    … …  
    348284}}}
    349285
    350 Рачно (или закажано) освежување на материјализираниот поглед:
     286Освежување (рачно или закажано):
    351287
    352288{{{
    … …  
    360296Администраторите треба увид во тоа колку компјутери се регистрирани во секоја околина, за следење на растот и планирање капацитети.
    361297
    362 === Implementation ===
    363 
    364 ==== Stored Procedures/Functions ====
     298'''Implementation:'''
     299
     300==== Stored procedures/functions ====
    365301
    366302Процедура за генерирање статистика по околини (RAISE NOTICE):
    … …  
    373309  r RECORD;
    374310BEGIN
    375   FOR r IN (
    376     SELECT env_name, COUNT(*) AS total
    377     FROM computers
    378     GROUP BY env_name
    379   )
     311  FOR r IN (SELECT env_name, COUNT(*) AS total FROM computers GROUP BY env_name)
    380312  LOOP
    381313    RAISE NOTICE 'Environment: %, Computers: %', r.env_name, r.total;
    … …  
    385317}}}
    386318
    387 Функција која враќа статистика по околини во форма на табела (погодна за апликацијата):
     319Функција која враќа статистика по околини во форма на табела:
    388320
    389321{{{
    … …  
    394326BEGIN
    395327  RETURN QUERY
    396   SELECT
    397     c.env_name,
    398     COUNT(*)::BIGINT AS total_computers
     328  SELECT c.env_name, COUNT(*)::BIGINT AS total_computers
    399329  FROM computers c
    400330  GROUP BY c.env_name
    … …  
    409339SELECT * FROM get_environment_statistics();
    410340}}}
     341
     342----
     343
     344== Валидација на влезни податоци преку сопствени домени ==
     345
     346Одреден дел од колоните бараат построги ограничувања на вредностите кои се повторуваат низ повеќе табели: портите мора да се во опсег 1..65535, мрежниот протокол е TCP/UDP, а е-поштата мора да има валиден формат. Наместо истите CHECK ограничувања да се повторуваат на секоја колона, тие се централизирани во сопствени домени и повторно се употребуваат насекаде во шемата.
     347
     348'''Implementation:'''
     349
     350==== Custom domains ====
     351
     352{{{
     353-- Порта: дозволен опсег 1..65535
     354CREATE DOMAIN port_number AS INTEGER
     355  CHECK (VALUE BETWEEN 1 AND 65535);
     356
     357-- Мрежен протокол
     358CREATE DOMAIN protocol_type AS TEXT
     359  CHECK (upper(VALUE) IN ('TCP','UDP'));
     360
     361-- Е-пошта (основна проверка на формат)
     362CREATE DOMAIN email_address AS TEXT
     363  CHECK (VALUE ~ '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$');
     364}}}
     365
     366Примена на домените врз колоните во шемата:
     367
     368{{{
     369-- tenants.owner_email, users.email  -> email_address
     370-- network_services.port             -> port_number
     371-- network_services.protocol         -> protocol_type
     372}}}
     373
     374Пример на употреба при креирање табела (доменот се пишува наместо базниот тип):
     375
     376{{{
     377CREATE TABLE network_services (
     378  id           INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     379  computer_id  INTEGER NOT NULL,
     380  service_name TEXT,
     381  port         port_number   NOT NULL,
     382  protocol     protocol_type NOT NULL,
     383  status       TEXT,
     384  last_checked TIMESTAMP,
     385  CONSTRAINT fk_network_services_computer FOREIGN KEY (computer_id) REFERENCES computers(id) ON DELETE CASCADE
     386);
     387}}}
     388
     389Примена на веќе постоечка колона (важи ако податоците го задоволуваат ограничувањето):
     390
     391{{{
     392ALTER TABLE network_services ALTER COLUMN port TYPE port_number;
     393ALTER TABLE network_services ALTER COLUMN protocol TYPE protocol_type;
     394}}}