Changes between Initial Version and Version 1 of OtherTopics

Timestamp:

09/23/26 13:54:28 (5 days ago)

Author:

233149

Comment:

--

OtherTopics

@@ +1 @@
+== Други теми - перформанси и безбедност
+
+== Мерна поставеност
+
+Анализата на перформанси не може да се направи врз официјалните тест податоци од
+data_load.sql, бидејќи тие се премногу мали - 8 корисници и 20 предмети. Врз
+табела со 20 редови PostgreSQL речиси секогаш избира секвенцијално читање дури и
+кога постои совршен индекс, затоа што читањето на 20 редови е поевтино од
+пребарување низ индекс. Мерењето врз такви податоци не би покажало ништо.
+
+Затоа е креирана посебна шема '''perf''' со иста структура како project, но со
+генерирани податоци од реален обем:
+
+|| '''Табела''' || '''Редови''' ||
+|| users || 4.120 (4.000 студенти, 120 професори) ||
+|| subjects || 300 ||
+|| active_semesters || 20 ||
+|| major_subjects || 600 ||
+|| professour_subjects || 6.000 ||
+|| enrolled_semesters || 16.000 ||
+|| semesters_subjects || 80.000 ||
+|| passed_subjects || 56.000 ||
+|| payment || 16.000 ||
+
+Упатството за Фаза 2 дозволува креирање дополнителни шеми за експерименти и
+тестови, па официјалната шема project останува недопрена.
+
+Сите мерења се направени со {{{EXPLAIN (ANALYZE, BUFFERS)}}} и со {{{ANALYZE}}}
+извршен пред секое мерење, за статистиките на планерот да бидат свежи.
+
+'''Забелешка за околината:''' мерењата се извршени врз локална инстанца
+PostgreSQL 18, а не врз доделената проектна база (PostgreSQL 17.11). Причината е
+што проектната база е достапна само преку SSH тунел кој во текот на работата
+постојано се прекинуваше, а мерење на времиња низ тунел и онака би ја мерело
+мрежата наместо базата. Плановите за извршување се споредливи, бидејќи истата
+шема, истите барања и истите податоци се употребени во двата случаја.
+
+== SQL перформанси
+
+=== Предложени индекси
+
+PostgreSQL автоматски креира индекс за примарен клуч и за секое UNIQUE
+ограничување, но '''никогаш''' не креира индекс за колоната која е надворешен
+клуч. Освен тоа, сложен индекс помага само на барања кои ја користат неговата
+'''прва''' колона.
+
+Според тоа, во шемата недостасуваат индекси на следните места:
+
+{{{#!sql
+CREATE INDEX ix_semesters_subjects_subject   ON semesters_subjects (subjects_id);
+CREATE INDEX ix_semesters_subjects_professor ON semesters_subjects (professor_id);
+CREATE INDEX ix_enrolled_semesters_semester  ON enrolled_semesters (semester_id);
+CREATE INDEX ix_enrolled_semesters_major     ON enrolled_semesters (major_id);
+CREATE INDEX ix_payment_enrollment           ON payment (enrollment_id);
+CREATE INDEX ix_major_subjects_subject       ON major_subjects (subject_id);
+CREATE INDEX ix_professour_subjects_semester ON professour_subjects (active_semester_id, subject_id);
+CREATE INDEX ix_user_documents_document      ON user_documents (document_id);
+CREATE INDEX ix_token_user                   ON token (user_id);
+CREATE INDEX ix_passed_subjects_enrolled_grade ON passed_subjects (enrolled_id, grade);
+}}}
+
+Колоните semesters_subjects.enrolled_semesters_id, enrolled_semesters.user_id,
+passed_subjects.enrolled_id, high_school.user_id и contact.user_id '''не''' се на
+списокот, бидејќи веќе се покриени - тие се првата колона на постоечко UNIQUE
+ограничување.
+
+=== Резултати пред и по индексите
+
+|| '''Барање''' || '''Пред''' || '''По''' || '''Промена''' ||
+|| Извештај 1 - проодност по предмет || 146,0 ms || 156,0 ms || нема ||
+|| Извештај 2 - досие на сите студенти || 188,5 ms || 179,4 ms || нема ||
+|| Извештај 3 - најуспешен студент по програма || 107,3 ms || 94,0 ms || нема ||
+|| Извештај 4 - најоптоварен професор || 121,9 ms || 124,6 ms || нема ||
+|| Извештај 5 - промена на активноста || 124,0 ms || 138,1 ms || нема ||
+|| Досие на '''еден''' студент || 2,19 ms || '''1,07 ms''' || '''2,0 пати побрзо''' ||
+|| Дневник на професор за еден професор || 16,3 ms || '''7,6 ms''' || '''2,1 пати побрзо''' ||
+
+=== Зошто петте извештаи не станаа побрзи
+
+Ова не е неуспех на индексите, туку очекувано однесување. Петте извештаи од Фаза
+6 се агрегатни - тие читаат '''сè''' од semesters_subjects, passed_subjects и
+enrolled_semesters, за да пресметаат проодност, просек или ранг. Кога барањето и
+онака мора да ги прочита сите редови, секвенцијалното читање е побрзо од
+пребарување низ индекс, бидејќи чита последователни блокови од дискот наместо да
+скока по индексот и потоа по табелата.
+
+Дека разликите во табелата се шум, а не забавување, се гледа од повторените
+мерења на истото барање со исти индекси:
+
+{{{
+run 1: 164,0 ms
+run 2: 151,5 ms
+run 3: 162,7 ms
+run 4: 179,9 ms
+}}}
+
+Распонот од околу 28 ms меѓу четири последователни извршувања е поголем од сите
+разлики „пред/по“ во табелата. Значи индексите за овие барања не менуваат ништо -
+ниту на добро, ниту на лошо.
+
+=== Каде индексите навистина помогнаа
+
+Кај двете '''селективни''' барања - оние кои бараат податоци за еден студент или
+за еден професор - разликата е јасна и се гледа во самиот план.
+
+'''Досие на еден студент''' (2,19 ms → 1,07 ms). Планот пред индексите содржеше:
+
+{{{
+Seq Scan on payment
+}}}
+
+а по индексите:
+
+{{{
+Index Scan using ix_payment_enrollment
+}}}
+
+'''Дневник на професор''' (16,3 ms → 7,6 ms). Пред индексот:
+
+{{{
+Seq Scan on semesters_subjects   (80.000 реда прочитани, филтрирани на ~660)
+}}}
+
+По индексот:
+
+{{{
+Bitmap Heap Scan on semesters_subjects
+  -> Bitmap Index Scan using ix_semesters_subjects_professor
+}}}
+
+Наместо да ги прочита сите 80.000 реда и да ги отфрли 99%, базата сега оди
+директно до редовите на тој професор.
+
+=== Дали индексите навистина се употребени
+
+Бројачите од pg_stat_user_indexes по сите мерења:
+
+|| '''Индекс''' || '''Големина''' || '''Пати употребен''' ||
+|| ix_passed_subjects_enrolled_grade || 1248 kB || 1410 ||
+|| ix_payment_enrollment || 368 kB || 8 ||
+|| ix_semesters_subjects_subject || 584 kB || 8 ||
+|| ix_semesters_subjects_professor || 576 kB || 1 ||
+|| ix_enrolled_semesters_major || 128 kB || '''0''' ||
+|| ix_enrolled_semesters_semester || 136 kB || '''0''' ||
+|| ix_major_subjects_subject || 32 kB || '''0''' ||
+|| ix_professour_subjects_semester || 152 kB || '''0''' ||
+|| ix_token_user || 8 kB || '''0''' ||
+|| ix_user_documents_document || 48 kB || '''0''' ||
+
+Од десет предложени индекси, '''четири''' се навистина употребени, а '''шест'''
+ниту еднаш. Тоа е важен резултат: индекс кој не се употребува не е бесплатен -
+зазема простор и го забавува секој INSERT, UPDATE и DELETE врз таа табела,
+бидејќи и индексот мора да се ажурира.
+
+=== Заклучок
+
+ * Индексите врз надворешни клучеви '''не ги забрзуваат''' агрегатните извештаи од Фаза 6, бидејќи тие и онака ја читаат целата табела. Секвенцијалното читање таму е правилниот избор на планерот.
+ * Истите индекси ги забрзуваат '''селективните''' барања - оние што ги користи апликацијата - околу '''два пати''', со видлива промена на планот од Seq Scan во Index Scan.
+ * Бидејќи апликацијата работи со селективни барања (еден студент, еден професор), а извештаите се пуштаат ретко, индексите се исплатливи.
+ * Се задржуваат само четирите употребени индекси. Останатите шест се отстрануваат, со една резерва: индекс врз надворешен клуч помага и при бришење во родителската табела, па ix_enrolled_semesters_semester и ix_major_subjects_subject може да се задржат ако се предвидуваат такви операции.
+ * Мерењето врз 20 реда не докажува ништо. Секое тврдење за перформанси мора да се прави врз податоци со реален обем.
+
+== Безбедносни мерки
+
+=== Во апликацијата
+
+'''Спречување на SQL injection.''' Апликацијата '''воопшто не составува SQL од
+текст'''. Проверено врз целиот изворен код - нема ниту еден повик на !FromSqlRaw,
+!ExecuteSqlRaw, !NpgsqlCommand ниту рачно поставен !CommandText. Целиот пристап оди
+преку Entity Framework Core, кој секоја вредност ја испраќа како параметар. Единственото
+место со напишан SQL е проверката на врската:
+
+{{{#!sql
+.SqlQuery<string>($"SELECT version() AS \"Value\"")
+}}}
+
+Тоа е интерполиран стринг во C#, но EF Core таквите изрази ги претвора во
+параметризирани наредби, а не во спојување на текст - вредностите никогаш не
+влегуваат во телото на барањето.
+
+'''Лозинки.''' Не се чуваат во читлива форма. При регистрација се хешираат со
+BCrypt, а при најава се споредуваат со Verify:
+
+{{{
+PasswordHash = BCrypt.Net.BCrypt.HashPassword(registerDto.Password)
+BCrypt.Net.BCrypt.Verify(loginDto.Password, user.PasswordHash)
+}}}
+
+BCrypt носи сопствена сол и намерно е бавен, што го отежнува пробувањето лозинки.
+
+'''Автентикација и авторизација.''' Пристапот е со JWT токен со краток рок;
+подолгата сесија се одржува со refresh токен кој се чува во табелата token и се
+поништува при одјава (is_valid = FALSE) наместо да се брише, за да остане трага.
+Улогата се чита '''од потпишаниот токен''', не од барањето, па повикувачот не
+може да си ја додели.
+
+'''Авторизацијата е во самото барање, не покрај него.''' Наместо прво да се
+провери правото, па потоа да се изврши барањето, условот е дел од наредбата:
+
+{{{#!sql
+INSERT INTO passed_subjects (enrolled_id, grade, date_passed)
+SELECT ss.id, '9', now()
+FROM semesters_subjects ss
+JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
+WHERE ss.professor_id = 4 AND ss.subjects_id = 2 AND es.user_id = 1;
+}}}
+
+Ако професорот не го предава тој предмет на тој студент, наредбата не менува
+ниту еден ред. Нема временски прозорец меѓу проверката и дејството.
+
+Проверено со барања од туѓа улога: студент кон професорски краен точки враќа
+'''403''', професор кон административни '''403''', барање без токен '''401''', а
+професор кој се обидува да оцени предмет што не го предава добива одбивање без
+никаква измена во базата.
+
+=== Во базата
+
+'''Правила кои важат и при директен пристап.''' Проверките од Фаза 7 се тригери
+во базата, а не само во апликацијата: предметот мора да е во студиската програма,
+професорот мора да го предава тој семестар, предусловите мора да се положени,
+оценка не смее да има датум во иднина. Кој и да пишува во базата - апликацијата,
+скрипта или човек преку DBeaver - правилата важат.
+
+'''Домени.''' Форматите се дел од типот на колоната, па невалидна е-пошта или ЕМБГ
+не може да влезе ниту преку директен INSERT.
+
+'''Изолација на податоци меѓу факултети.''' Со политиките за безбедност на ниво
+на редица (Фаза 7), барањата автоматски се ограничуваат на тековниот факултет.
+
+'''Отворено прашање.''' Апликацијата се поврзува со корисникот кој е сопственик
+на шемата, а сопственикот ги '''заобиколува''' политиките за безбедност на ниво
+на редица, освен ако не се употреби FORCE ROW LEVEL SECURITY. Значи политиките
+во моментов се напишани и точни, но не се активни за апликацијата. Правилното
+решение е посебна улога со ограничени права:
+
+{{{#!sql
+CREATE ROLE iknow_app LOGIN PASSWORD '...';
+GRANT USAGE ON SCHEMA project TO iknow_app;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA project TO iknow_app;
+-- без DROP, без ALTER, без права врз други шеми
+}}}
+
+Ова не е спроведено, бидејќи доделениот кориснички налог на проектната база нема
+право да креира нови улоги.
+
+'''Лозинки во конфигурација.''' Лозинката за базата не е во складиштето на кодот
+- таа е во appsettings.Development.json, кој е во .gitignore, а во складиштето
+постои само appsettings.Development.json.example со празно место.
+
+== Други развојни теми
+
+'''Материјализиран преглед.''' Статистиката по предмети (Фаза 7) е пресметка врз
+целата база - точно оној вид агрегатно барање кое, како што покажа мерењето
+погоре, не може да се забрза со индекс. Затоа е материјализирана и се освежува
+еднаш дневно, со уникатен индекс кој овозможува освежување со CONCURRENTLY,
+односно без заклучување за читање.
+
+'''Pool на конекции.''' Документиран во Фаза 8; тука е релевантен затоа што
+базата е зад SSH тунел, па секоја нова конекција е нов канал низ тунелот и
+отворањето е поскапо од вообичаеното.
+
+== Користење на вештачка интелигенција
+
+ [wiki:OtherTopicsAIUsage OtherTopicsAIUsage]
+
+== Историјат
+
+ '''Верзија 1''' - Прва верзија: мерење на перформансите на петте извештаи од Фаза
+ 6 пред и по воведување индекси врз шема со 80.000 запишани предмети, анализа на
+ плановите за извршување, проверка кои индекси навистина се употребуваат, и
+ документирање на безбедносните мерки во апликацијата и во базата.
+
+== Статус
+
+ ''' [[span(style=color: #FF8000, Во тек )]] '''