Други теми - перформанси и безбедност

Мерна поставеност

Анализата на перформанси не може да се направи врз официјалните тест податоци од 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 ограничување, но никогаш не креира индекс за колоната која е надворешен клуч. Освен тоа, сложен индекс помага само на барања кои ја користат неговата прва колона.

Според тоа, во шемата недостасуваат индекси на следните места:

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 е проверката на врската:

.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) наместо да се брише, за да остане трага. Улогата се чита од потпишаниот токен, не од барањето, па повикувачот не може да си ја додели.

Авторизацијата е во самото барање, не покрај него. Наместо прво да се провери правото, па потоа да се изврши барањето, условот е дел од наредбата:

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. Значи политиките во моментов се напишани и точни, но не се активни за апликацијата. Правилното решение е посебна улога со ограничени права:

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 тунел, па секоја нова конекција е нов канал низ тунелот и отворањето е поскапо од вообичаеното.

Користење на вештачка интелигенција

OtherTopicsAIUsage

Историјат

Верзија 1 - Прва верзија: мерење на перформансите на петте извештаи од Фаза 6 пред и по воведување индекси врз шема со 80.000 запишани предмети, анализа на плановите за извршување, проверка кои индекси навистина се употребуваат, и документирање на безбедносните мерки во апликацијата и во базата.

Статус

Во тек