Other Topics

SQL Performance

Во оваа фаза беше направена анализа на перформансите на SQL пребарувања кои се користат во BiblioPremium. Анализата беше направена со EXPLAIN ANALYZE пред и после креирање на предложените индекси.

1. Анализа на продажба по книги

Анализирано е комплексно пребарување кое ги прикажува вкупно продадените примероци и вкупниот приход по книга за завршени нарачки.

EXPLAIN ANALYZE
SELECT
    k.kniga_id,
    k.naslov,
    SUM(s.kolicina) AS vkupno_prodadeni,
    SUM(s.kolicina * s.edinechna_cena) AS vkupen_prihod
FROM project.knigi k
JOIN project.sodrzi s
    ON k.kniga_id = s.kniga_id
JOIN project.naracki n
    ON s.naracka_id = n.naracka_id
WHERE n.status = 'ZAVRSENA'
GROUP BY k.kniga_id, k.naslov
ORDER BY vkupen_prihod DESC;

Пред креирање на индексот, execution plan покажа Sequential Scan (Seq Scan) на табелата naracki при филтрирањето според статусот ZAVRSENA.

За оптимизација беше предложен и креиран следниот индекс:

CREATE INDEX idx_naracki_status
ON project.naracki(status);

По креирањето на индексот повторно беше извршен EXPLAIN ANALYZE.

PostgreSQL и понатаму избра Sequential Scan на табелата naracki и индексот idx_naracki_status не беше употребен.

Причината е малиот број записи во табелата. За мала табела PostgreSQL проценува дека директното секвенцијално читање е поевтино од пристап преку индекс. Индексот може да стане покорисен со значително зголемување на бројот на нарачки.

Заклучок: индексот е соодветен кандидат за поголема количина на податоци, но кај моменталната мала тест база оптимизаторот правилно избира Sequential Scan.

2. Препораки според расположение

Беше анализирано пребарувањето за пронаоѓање достапни книги поврзани со избрано расположение.

EXPLAIN ANALYZE
SELECT
    k.kniga_id,
    k.naslov,
    k.cena,
    k.kolicina_na_zaliha
FROM project.knigi k
JOIN project.povrzana_so ps
    ON k.kniga_id = ps.kniga_id
WHERE ps.raspolozenie_id = 1
  AND k.kolicina_na_zaliha > 0
ORDER BY k.naslov;

Execution plan покажа дека PostgreSQL веќе користи постоечки индекс:

Bitmap Index Scan on povrzana_so_pkey
Index Cond: (raspolozenie_id = 1)

Измереното време на извршување беше приближно 0.132 ms.

Поради тоа не беше креиран дополнителен индекс, бидејќи постоечкиот индекс веќе се користи при пребарувањето. Додавање непотребен индекс би создало дополнителен трошок при INSERT/UPDATE операции без значителна корист за моменталниот обем на податоци.

3. Пребарување книги по наслов

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

EXPLAIN ANALYZE
SELECT
    kniga_id,
    naslov,
    cena,
    kolicina_na_zaliha
FROM project.knigi
WHERE LOWER(naslov) LIKE '%work%'
ORDER BY naslov;

Пред креирањето на индексот беше добиен:

Seq Scan on knigi
Execution Time: 0.075 ms

За поддршка на пребарување со шаблон од типот '%text%' беше активирана PostgreSQL екстензијата pg_trgm:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Потоа беше креиран GIN trigram индекс:

CREATE INDEX idx_knigi_naslov_trgm
ON project.knigi
USING gin (LOWER(naslov) gin_trgm_ops);

По креирањето на индексот повторно беше извршен истиот EXPLAIN ANALYZE.

Резултат:

Seq Scan on knigi
Execution Time: 0.063 ms

Индексот не беше употребен во execution plan. Табелата моментално содржи многу мал број книги, поради што PostgreSQL проценува дека Sequential Scan е поевтин.

Иако измереното време после креирањето на индексот беше 0.063 ms наспроти 0.075 ms пред индексот, оваа разлика не се припишува на индексот бидејќи execution plan покажува дека индексот не бил употребен.

Со значително поголем каталог на книги, trigram индексот е наменет да овозможи поефикасно пребарување со делумно совпаѓање на текст.

Security measures

Application security

Во апликацијата се применети повеќе мерки за заштита на пристапот до базата и податоците.

1. Заштита од SQL Injection

SQL пребарувањата користат параметризирани вредности преку psycopg2 placeholders (%s), наместо директно спојување на корисничкиот внес со SQL текстот.

Пример:

cursor.execute("""
    SELECT kniga_id, naslov, cena, opis, godina_izdavanje
    FROM project.knigi
    WHERE naslov ILIKE %s
    ORDER BY kniga_id;
""", (f"%{search}%",))

На овој начин внесот од корисникот се испраќа како SQL параметар, а не како дел од SQL командата.

2. Автентикација и кориснички сесии

По успешна најава во Flask session се зачувуваат идентификаторот и улогата на корисникот. Заштитените функционалности проверуваат дали постои најавен корисник.

3. Авторизација според улога

Административните страници и операции се достапни само за корисници со улога ADMIN.

Пример:

if "korisnik_id" not in session:
    return redirect(url_for("login"))

if session.get("uloga") != "ADMIN":
    return redirect(url_for("home"))

Оваа проверка се користи кај административниот панел и административните операции за книги, жанрови, категории, расположенија, нарачки и плаќања.

4. Ограничување на пристап до кориснички податоци

При прикажување на нарачките, податоците се филтрираат според korisnik_id од активната сесија.

WHERE n.korisnik_id = %s

И при прикажување на успешно купување се проверуваат и naracka_id и korisnik_id, со што најавениот корисник не може преку промена на ID во URL да пристапи до нарачка на друг корисник.

5. Трансакциска заштита

Кај операции кои менуваат повеќе табели, како купување книга и зачувување преференции, се користат commit и rollback.

При успешна операција:

conn.commit()

При грешка:

conn.rollback()

Со ова се спречува делумно зачувување на податоци при неуспешна операција.

6. Заштита на параметрите за поврзување

Податоците за поврзување со PostgreSQL и SSH (корисничко име, лозинка и други параметри) се земаат од environment variables преку .env датотека, наместо да бидат директно запишани во изворниот код.

Database security

Апликацијата пристапува до проектната PostgreSQL база преку автентициран database account.

Со:

SELECT current_user;

беше потврдено дека конекцијата се извршува преку проектниот database owner account.

Пристапот до PostgreSQL се реализира преку SSH tunnel, а credentials се вчитуваат од environment variables.

SQL командите кои содржат вредности добиени од апликацијата користат параметризирани SQL изрази. Во апликацијата не се користи динамичко составување SQL команди преку директно додавање на кориснички внес.

Дополнително, интегритетот на податоците се поддржува со примарни и надворешни клучеви, ограничувања и релации дефинирани во базата.

Ограничувања и можни идни подобрувања

Во моменталниот прототип корисничките лозинки не се хешираат пред зачувување. Во продукциска верзија треба да се користи сигурен password hashing механизам.

Flask secret key моментално е дефиниран во апликацискиот код. Во продукциска околина треба да се премести во environment variable и да се користи силна случајно генерирана вредност.

Исто така, апликацијата моментално се поврзува преку проектниот database owner account. Дополнително подобрување би било креирање посебна database улога за апликацијата со минималните потребни привилегии.

Other developments

Во рамките на претходните фази се имплементирани дополнителни механизми кои придонесуваат за перформанси, интегритет и сигурност на системот:

• connection pooling за повторна употреба на PostgreSQL конекции;
• трансакции со commit/rollback;
• trigger за проверка и намалување на залиха;
• database views за продажба и следење на ниска залиха;
• database function за препорака на книги според расположение;
• административна авторизација според улога;
• проверки пред бришење на податоци кои се поврзани со други записи.