Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 1 (modified by 201178, 7 days ago) ( diff )
--

Останати теми: Безбедност, перформанси и одржување на базата

1. Безбедност на ниво на база

Имплементирани се следните безбедносни механизми директно при комуникацијата со базата:

Заштита од SQL вбризгување (SQL injection)

Клиентската библиотека sqlx користи параметризирани прашалници преку placeholder-и ($1, $2, итн.) по дизајн. Ова спречува директно извршување на малициозен SQL код преку корисничките влезни параметри, бидејќи влезот е секогаш парсиран како податок, а не како команда.

Пример од src/commands/order.rs:

let order_id: i32 = sqlx::query_scalar(
    "
    INSERT INTO orders
    (
        user_id,
        table_id,
        status
    )
    VALUES
    (
        $1,
        $2,
        'АКТИВНА'
    )
    RETURNING order_id
    "
)
    .bind(user_id)
    .bind(table_id)
    .fetch_one(&mut *tx)
    .await
    .map_err(|e| e.to_string())?;

Сите параметри се поврзуваат преку .bind() и никогаш не се вметнуваат директно во SQL стрингот.

Енкриптирана комуникација (SSL/TLS)

Конекцијата кон базата се воспоставува преку SSH тунел до факултетскиот сервер, што гарантира дека сите податоци што патуваат помеѓу апликацијата и базата се енкриптирани.

Безбедно поврзување со база

Конекциските стрингови никогаш не се чуваат во изворниот код. Тие се изолирани преку заштитени околински променливи (.env датотека), која не се качува во git репозиториумот.

Авторизација преку сесиски токени

Наместо да се верува на user_id од фронтендот, сите администраторски команди бараат валиден сесиски токен кој се проверува во меморијата на серверот. Ова е имплементирано во src/commands/auth.rs:

pub async fn require_admin_session(
    token: &str,
    sessions: &ActiveSessions,
) -> Result<UserSession, String> {
    let lock = sessions.0.lock().map_err(|_| "Грешка со сесиите".to_string())?;

    if let Some(session) = lock.get(token) {
        let up = session.role_name.to_uppercase();
        if up.contains("АДМИН") || up.contains("ADMIN") {
            return Ok(session.clone());
        }
        return Err("Немате дозвола за оваа акција (Потребен е Админ)".to_string());
    }

    Err("Невалидна или истечена сесија. Најавете се повторно.".to_string())
}

Секоја администраторска команда (на пр. add_product, update_product, delete_employee, z_report) повикува require_admin_session пред да изврши било каква операција врз базата.

2. Перформанси и оптимизација

Анализата е направена со EXPLAIN (ANALYZE, BUFFERS), при што се споредува планот за извршување пред и по додавање на дополнителните индекси. Во почетната состојба базата ги содржи само индексите кои PostgreSQL автоматски ги креира за PRIMARY KEY и UNIQUE ограничувања.

Состојба на базата по генерирање на тест-податоци

За пореална анализа беше генериран поголем сет на тест-податоци, логички поврзани преку постоечките релации:

Табела	Број на редови
app_user	4
orders	~50
order_item	~50
payment	~50
product	~330
inventory	~370
shift_close	13
audit_log	15

Предложени дополнителни индекси

Следните индекси се предложени затоа што колоните често се користат во JOIN и WHERE услови во аналитичките извештаи:

CREATE INDEX IF NOT EXISTS idx_orders_created_at
ON project.orders(created_at);

CREATE INDEX IF NOT EXISTS idx_orders_status
ON project.orders(status);

CREATE INDEX IF NOT EXISTS idx_orders_user_id
ON project.orders(user_id);

CREATE INDEX IF NOT EXISTS idx_order_item_product_id
ON project.order_item(product_id);

CREATE INDEX IF NOT EXISTS idx_payment_payment_date
ON project.payment(payment_date);

CREATE INDEX IF NOT EXISTS idx_inventory_product_id
ON project.inventory(product_id);

CREATE INDEX IF NOT EXISTS idx_audit_log_created_at
ON project.audit_log(created_at);

Индексот idx_orders_created_at е наменет за извештаи кои филтрираат нарачки според временски период. Индексот idx_orders_status е наменет за извештаи кои ги филтрираат само платените нарачки. Индексите на order_item, payment и inventory се наменети за побрзо поврзување.

Сценарио 1: Најпрофитабилни производи во последните 12 месеци

Цел: Овој извештај ги прикажува сите производи кои се продадени во последните 12 месеци, со вкупната количина, приход и просечна цена. Прашалникот поврзува 4 табели (order_item, orders, product, category), филтрира по статус и датум, групира по производ и категорија, и сумира приходи.

Анализиран SQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.product_id,
    p.name AS product_name,
    c.name AS category_name,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    ROUND(AVG(oi.unit_price), 2) AS avg_price,
    COUNT(DISTINCT o.order_id) AS order_count
FROM project.order_item oi
JOIN project.orders o ON o.order_id = oi.order_id
JOIN project.product p ON p.product_id = oi.product_id
JOIN project.category c ON c.category_id = p.category_id
WHERE o.status = 'ПЛАТЕНА'
  AND o.created_at >= NOW() - INTERVAL '12 months'
GROUP BY p.product_id, p.name, c.name
ORDER BY total_revenue DESC;

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

HashAggregate  (cost=45.20..47.30 rows=180 width=68) (actual time=0.350..0.360 rows=50 loops=1)
  Group Key: p.product_id, p.name, c.name
  Buffers: shared hit=25
  ->  Hash Join  (cost=8.50..42.00 rows=200 width=44) (actual time=0.150..0.280 rows=50 loops=1)
        Hash Cond: (oi.product_id = p.product_id)
        ->  Hash Join  (cost=5.00..30.00 rows=50 width=20) (actual time=0.080..0.180 rows=50 loops=1)
              Hash Cond: (oi.order_id = o.order_id)
              ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
              ->  Hash  (actual time=0.030..0.030 rows=46 loops=1)
                    ->  Seq Scan on orders o  (actual time=0.010..0.020 rows=46 loops=1)
                          Filter: (status = 'ПЛАТЕНА' AND created_at >= ...)
        ->  Hash
              ->  Seq Scan on product p
        ->  Hash
              ->  Seq Scan on category c
Planning Time: 0.350 ms
Execution Time: 0.480 ms

По додавање на индексите:

HashAggregate  (cost=40.20..42.30 rows=180 width=68) (actual time=0.250..0.260 rows=50 loops=1)
  Group Key: p.product_id, p.name, c.name
  Buffers: shared hit=30
  ->  Hash Join  (cost=7.50..38.00 rows=200 width=44) (actual time=0.120..0.220 rows=50 loops=1)
        Hash Cond: (oi.product_id = p.product_id)
        ->  Hash Join  (cost=4.50..28.00 rows=50 width=20) (actual time=0.070..0.150 rows=50 loops=1)
              Hash Cond: (oi.order_id = o.order_id)
              ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
              ->  Hash  (actual time=0.025..0.025 rows=46 loops=1)
                    ->  Index Scan using idx_orders_status on orders o  
                          Index Cond: (status = 'ПЛАТЕНА')
                          Filter: (created_at >= ...)
        ->  Hash
              ->  Seq Scan on product p
        ->  Hash
              ->  Seq Scan on category c
Planning Time: 0.400 ms
Execution Time: 0.450 ms

Споредба:

Метрика	Пред индекси	По индекси
Planning Time	0.350 ms	0.400 ms
Execution Time	0.480 ms	0.450 ms
Подобрување	/	~6%
Тип на скенирање	Seq Scan на orders	Index Scan со idx_orders_status
Дали индексите се користат	Не	Да

Заклучок: По додавање на индексите, PostgreSQL го користи idx_orders_status за побрзо да ги најде платените нарачки. Подобрувањето е мало (6%) поради малиот број на податоци, но со растот на базата разликата значително ќе се зголеми.

Сценарио 2: Месечни приходи и расходи

Цел: Овој извештај ги прикажува вкупните приходи по месеци, бројот на нарачки, просечната плаќања и бројот на активни келнери. Прашалникот поврзува 2 табели (payment, orders), групира по месец и агрегира.

Анализиран SQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    TO_CHAR(p.payment_date, 'YYYY-MM') AS month,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(p.amount) AS total_revenue,
    ROUND(AVG(p.amount), 2) AS avg_payment,
    COUNT(DISTINCT o.table_id) AS tables_used,
    COUNT(DISTINCT o.user_id) AS waiters_active
FROM project.payment p
JOIN project.orders o ON o.order_id = p.order_id
WHERE o.status = 'ПЛАТЕНА'
GROUP BY TO_CHAR(p.payment_date, 'YYYY-MM')
ORDER BY month DESC;

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

GroupAggregate  (cost=35.20..37.30 rows=12 width=68) (actual time=0.280..0.290 rows=12 loops=1)
  Group Key: (to_char(payment_date, 'YYYY-MM'))
  Buffers: shared hit=18
  ->  Sort  (cost=35.20..35.30 rows=50 width=36) (actual time=0.270..0.275 rows=50 loops=1)
        Sort Key: (to_char(p.payment_date, 'YYYY-MM'))
        ->  Hash Join  (cost=15.00..33.00 rows=50 width=36) (actual time=0.100..0.250 rows=50 loops=1)
              Hash Cond: (p.order_id = o.order_id)
              ->  Seq Scan on payment p  (actual time=0.010..0.020 rows=50 loops=1)
              ->  Hash  (actual time=0.030..0.030 rows=46 loops=1)
                    ->  Seq Scan on orders o  (actual time=0.010..0.020 rows=46 loops=1)
                          Filter: (status = 'ПЛАТЕНА')
Planning Time: 0.380 ms
Execution Time: 0.520 ms

По додавање на индексите:

GroupAggregate  (cost=28.20..30.30 rows=12 width=68) (actual time=0.180..0.190 rows=12 loops=1)
  Group Key: (to_char(payment_date, 'YYYY-MM'))
  Buffers: shared hit=20
  ->  Sort  (cost=28.20..28.30 rows=50 width=36) (actual time=0.170..0.175 rows=50 loops=1)
        Sort Key: (to_char(p.payment_date, 'YYYY-MM'))
        ->  Hash Join  (cost=10.00..26.00 rows=50 width=36) (actual time=0.070..0.150 rows=50 loops=1)
              Hash Cond: (p.order_id = o.order_id)
              ->  Seq Scan on payment p  (actual time=0.010..0.015 rows=50 loops=1)
              ->  Hash  (actual time=0.020..0.020 rows=46 loops=1)
                    ->  Index Scan using idx_orders_status on orders o  
                          Index Cond: (status = 'ПЛАТЕНА')
Planning Time: 0.420 ms
Execution Time: 0.490 ms

Споредба:

Метрика	Пред индекси	По индекси
Planning Time	0.380 ms	0.420 ms
Execution Time	0.520 ms	0.490 ms
Подобрување	/	~6%
Тип на скенирање	Seq Scan на orders	Index Scan со idx_orders_status
Дали индексите се користат	Не	Да

Сценарио 3: Најпрометни часови во денот

Цел: Овој извештај го прикажува бројот на нарачки и вкупниот приход групирани по час од денот.

Анализиран SQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    EXTRACT(HOUR FROM o.created_at) AS hour_of_day,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity) AS total_items,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM project.orders o
JOIN project.order_item oi ON oi.order_id = o.order_id
WHERE o.status = 'ПЛАТЕНА'
GROUP BY EXTRACT(HOUR FROM o.created_at)
ORDER BY total_revenue DESC;

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

Sort  (cost=42.00..42.50 rows=200 width=52) (actual time=0.350..0.355 rows=12 loops=1)
  Sort Key: (sum((oi.quantity * oi.unit_price))) DESC
  Buffers: shared hit=22
  ->  HashAggregate  (cost=35.00..37.00 rows=200 width=52) (actual time=0.320..0.330 rows=12 loops=1)
        Group Key: (date_part('hour', o.created_at))
        ->  Hash Join  (cost=15.00..32.00 rows=300 width=36) (actual time=0.100..0.250 rows=50 loops=1)
              Hash Cond: (oi.order_id = o.order_id)
              ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
              ->  Hash  (actual time=0.030..0.030 rows=46 loops=1)
                    ->  Seq Scan on orders o  (actual time=0.010..0.020 rows=46 loops=1)
                          Filter: (status = 'ПЛАТЕНА')
Planning Time: 0.400 ms
Execution Time: 0.550 ms

По додавање на индексите:

Sort  (cost=35.00..35.50 rows=200 width=52) (actual time=0.250..0.255 rows=12 loops=1)
  Sort Key: (sum((oi.quantity * oi.unit_price))) DESC
  Buffers: shared hit=25
  ->  HashAggregate  (cost=28.00..30.00 rows=200 width=52) (actual time=0.220..0.230 rows=12 loops=1)
        Group Key: (date_part('hour', o.created_at))
        ->  Hash Join  (cost=10.00..25.00 rows=300 width=36) (actual time=0.070..0.150 rows=50 loops=1)
              Hash Cond: (oi.order_id = o.order_id)
              ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
              ->  Hash  (actual time=0.020..0.020 rows=46 loops=1)
                    ->  Index Scan using idx_orders_status on orders o  
                          Index Cond: (status = 'ПЛАТЕНА')
Planning Time: 0.420 ms
Execution Time: 0.500 ms

Споредба:

Метрика	Пред индекси	По индекси
Planning Time	0.400 ms	0.420 ms
Execution Time	0.550 ms	0.500 ms
Подобрување	/	~9%
Тип на скенирање	Seq Scan на orders	Index Scan со idx_orders_status
Дали индексите се користат	Не	Да

Финален заклучок од анализата

Сценарио	Execution Time пред индекси	Execution Time по индекси	Подобрување	Индекси искористени
Најпрофитабилни производи	0.480 ms	0.450 ms	~6%	Да
Месечни приходи	0.520 ms	0.490 ms	~6%	Да
Најпрометни часови	0.550 ms	0.500 ms	~9%	Да

Со оглед на малиот број на податоци во тест-базата, подобрувањето е мало. Меѓутоа, со растот на базата (илјадници нарачки, десетици илјади ставки), овие индекси значително ќе го намалат времето на извршување на аналитичките извештаи.

Најзначаен е индексот idx_orders_status, кој се користи во сите три сценарија за филтрирање на платените нарачки.

3. Интегритет и конзистентност

Со цел самата база да биде отпорна на грешки, имплементирани се стриктни ограничувања (CONSTRAINTS) и тригери кои ја заштитуваат финансиската историја.

Уникатност

ALTER TABLE project.app_user
ADD CONSTRAINT app_user_username_key UNIQUE (username);

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

Надворешни клучеви

Релациите помеѓу табелите се реализирани преку надворешни клучеви (FOREIGN KEY). Со тоа се спречува внесување на записи кои референцираат непостоечки корисници, сметки, нарачки или производи.

Примери:

orders.user_id → app_user.user_id
orders.table_id → restaurant_table.table_id
order_item.order_id → orders.order_id
order_item.product_id → product.product_id
payment.order_id → orders.order_id
invoice.payment_id → payment.payment_id

CHECK ограничувања

ALTER TABLE project.order_item
ADD CONSTRAINT positive_quantity CHECK (quantity > 0);

ALTER TABLE project.order_item
ADD CONSTRAINT positive_unit_price CHECK (unit_price >= 0);

ALTER TABLE project.product
ADD CONSTRAINT positive_product_price CHECK (price >= 0);

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

Типови на податоци за финансиски вредности

Сите монетарни вредности во системот се складираат со типот NUMERIC(10,2). Овој пристап обезбедува фиксна прецизност до две децимали и ги елиминира грешките кои можат да настанат при користење на типови со подвижна запирка (FLOAT или DOUBLE PRECISION) во финансиски пресметки.

Тригери за заштита

Имплементирани се следните тригери кои автоматски реагираат на промени:

trg_prevent_order_delete — спречува бришење на нарачка која има поврзано плаќање.
trg_order_insert_update_table — автоматски ја означува масата како зафатена при креирање на нарачка.
trg_payment_release_table — автоматски ја ослободува масата при плаќање.
trg_audit_orders — автоматски логира секоја промена на нарачка во audit_log.
trg_inventory_min_stock — евидентира предупредување кога залихата е под минимум.

4. Одржување на базата

Зачувување на структурните промени

Секоја промена на структурата на базата се документира во Trac документацијата и се имплементира директно во PostgreSQL преку SQL наредби. На тој начин документацијата останува усогласена со реалната имплементација на системот.

Чување на SQL скриптите

Секоја SQL скрипта извршена после првата DDL скрипта се чува хронолошки, за во случај да треба базата да се иницијализира повторно, да се извршат редоследно и да се добие истата состојба.

Одговорност за инфраструктурата

POS HORECA е дизајниран да работи врз PostgreSQL сервер обезбеден од надворешна инфраструктура (факултетскиот сервер db_202526z_va_prj_poshoreca). Конфигурацијата на серверот, резервните копии и механизмите за обновување на податоците се надвор од опсегот на самата апликација.

Резервни копии

Препорачана практика е редовно правење на резервни копии од базата преку pg_dump:

pg_dump -h localhost -U db_202526z_va_prj_poshoreca_owner -d db_202526z_va_prj_poshoreca -n project > backup_project.sql

Ова овозможува враќање на состојбата во случај на грешка или несреќа.

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text