| Version 2 (modified by , 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 | 10.088 |
| order_item | 30.048 |
| payment | 5.046 |
| 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;
Резултат од EXPLAIN (ANALYZE, BUFFERS):
Sort (cost=916.67..916.77 rows=40 width=329) (actual time=110.670..110.675 rows=24 loops=1)
Sort Key: (sum(((oi.quantity)::numeric * oi.unit_price))) DESC
Sort Method: quicksort Memory: 27kB
Buffers: shared hit=42572
-> GroupAggregate (cost=914.00..915.60 rows=40 width=329) (actual time=98.576..110.651 rows=24 loops=1)
Group Key: p.product_id, c.name
Buffers: shared hit=42572
-> Sort (cost=914.00..914.10 rows=40 width=273) (actual time=98.557..99.821 rows=20940 loops=1)
Sort Key: p.product_id, c.name, o.order_id
Sort Method: quicksort Memory: 2078kB
Buffers: shared hit=42572
-> Nested Loop (cost=97.89..912.94 rows=40 width=273) (actual time=5.056..56.400 rows=20940 loops=1)
Buffers: shared hit=42572
-> Nested Loop (cost=97.74..908.87 rows=40 width=59) (actual time=5.043..46.378 rows=20940 loops=1)
Buffers: shared hit=42562
-> Hash Join (cost=97.60..902.23 rows=40 width=28) (actual time=5.030..17.485 rows=20940 loops=1)
Hash Cond: (oi.order_id = o.order_id)
Buffers: shared hit=682
-> Seq Scan on order_item oi (cost=0.00..741.48 rows=24048 width=28) (actual time=0.019..4.361 rows=30048 loops=1)
Buffers: shared hit=501
-> Hash (cost=97.43..97.43 rows=13 width=4) (actual time=4.999..5.000 rows=7010 loops=1)
Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 311kB
Buffers: shared hit=181
-> Bitmap Heap Scan on orders o (cost=4.58..97.43 rows=13 width=4) (actual time=0.485..3.863 rows=7010 loops=1)
Recheck Cond: ((status)::text = 'ПЛАТЕНА'::text)
Filter: (created_at >= (now() - '1 year'::interval))
Heap Blocks: exact=168
Buffers: shared hit=181
-> Bitmap Index Scan on idx_orders_status (cost=0.00..4.58 rows=39 width=0) (actual time=0.443..0.443 rows=13992 loops=1)
Index Cond: ((status)::text = 'ПЛАТЕНА'::text)
Buffers: shared hit=13
-> Index Scan using product_pkey on product p (cost=0.15..0.17 rows=1 width=35) (actual time=0.001..0.001 rows=1 loops=20940)
Index Cond: (product_id = oi.product_id)
Buffers: shared hit=41880
-> Memoize (cost=0.15..0.20 rows=1 width=222) (actual time=0.000..0.000 rows=1 loops=20940)
Cache Key: p.category_id
Cache Mode: logical
Hits: 20935 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=10
-> Index Scan using category_pkey on category c (cost=0.14..0.19 rows=1 width=222) (actual time=0.002..0.002 rows=1 loops=5)
Index Cond: (category_id = p.category_id)
Buffers: shared hit=10
Planning:
Buffers: shared hit=11
Planning Time: 0.559 ms
Execution Time: 110.770 ms
Споредба:
| Метрика | Вредност |
| Planning Time | 0.559 ms |
| Execution Time | 110.770 ms |
| Вкупно вратени редови | 24 |
| Тип на скенирање на orders | Bitmap Index Scan (idx_orders_status) |
| Индекси искористени | idx_orders_status, product_pkey, category_pkey |
| Memoize оптимизација | Да (20.935 hits, 5 misses) |
Заклучок: Со растот на базата од 46 на 10.000+ нарачки, PostgreSQL почна да го користи индексот idx_orders_status преку Bitmap Index Scan за филтрирање на платените нарачки, наместо Seq Scan. Дополнително, PostgreSQL користи Memoize оптимизација за категориите, намалувајќи 20.935 повторени читања на само 5.
Сценарио 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;
Резултат од EXPLAIN (ANALYZE, BUFFERS):
GroupAggregate (cost=681.74..804.98 rows=3521 width=120) (actual time=15.977..18.246 rows=13 loops=1)
Group Key: (to_char(p.payment_date, 'YYYY-MM'::text))
Buffers: shared hit=208
-> Sort (cost=681.74..690.55 rows=3521 width=50) (actual time=15.791..16.167 rows=5046 loops=1)
Sort Key: (to_char(p.payment_date, 'YYYY-MM'::text)) DESC, o.order_id
Sort Method: quicksort Memory: 429kB
Buffers: shared hit=208
-> Hash Join (cost=153.54..474.32 rows=3521 width=50) (actual time=1.766..8.294 rows=5046 loops=1)
Hash Cond: (o.order_id = p.order_id)
Buffers: shared hit=208
-> Seq Scan on orders o (cost=0.00..293.57 rows=7010 width=12) (actual time=0.018..2.539 rows=7010 loops=1)
Filter: ((status)::text = 'ПЛАТЕНА'::text)
Rows Removed by Filter: 3036
Buffers: shared hit=168
-> Hash (cost=90.46..90.46 rows=5046 width=18) (actual time=1.719..1.720 rows=5046 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 321kB
Buffers: shared hit=40
-> Seq Scan on payment p (cost=0.00..90.46 rows=5046 width=18) (actual time=0.007..0.750 rows=5046 loops=1)
Buffers: shared hit=40
Planning:
Buffers: shared hit=93
Planning Time: 0.793 ms
Execution Time: 18.309 ms
Споредба:
| Метрика | Вредност |
| Planning Time | 0.793 ms |
| Execution Time | 18.309 ms |
| Вкупно вратени редови | 13 (месеци) |
| Тип на скенирање на orders | Seq Scan (со Filter на status) |
| Индекси искористени | (не се користат поради мал број на вратени редови) |
Заклучок: Овој извештај враќа само 13 редови (месеци) и PostgreSQL избира Seq Scan на двете табели, бидејќи агрегацијата бара читање на сите 5.046 плаќања. Индексот idx_payment_payment_date не се користи бидејќи нема филтер по датум (сите плаќања се во опсегот).
Сценарио 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;
Резултат од EXPLAIN (ANALYZE, BUFFERS):
Sort (cost=3738.84..3756.37 rows=7010 width=80) (actual time=61.237..61.241 rows=24 loops=1)
Sort Key: (sum(((oi.quantity)::numeric * oi.unit_price))) DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=669
-> GroupAggregate (cost=2819.00..3291.07 rows=7010 width=80) (actual time=50.305..61.219 rows=24 loops=1)
Group Key: (EXTRACT(hour FROM o.created_at))
Buffers: shared hit=669
-> Sort (cost=2819.00..2871.42 rows=20967 width=45) (actual time=49.774..51.274 rows=20940 loops=1)
Sort Key: (EXTRACT(hour FROM o.created_at)), o.order_id
Sort Method: quicksort Memory: 1586kB
Buffers: shared hit=669
-> Hash Join (cost=381.20..1314.01 rows=20967 width=45) (actual time=3.765..20.296 rows=20940 loops=1)
Hash Cond: (oi.order_id = o.order_id)
Buffers: shared hit=669
-> Seq Scan on order_item oi (cost=0.00..801.48 rows=30048 width=13) (actual time=0.014..2.618 rows=30048 loops=1)
Buffers: shared hit=501
-> Hash (cost=293.57..293.57 rows=7010 width=12) (actual time=3.724..3.725 rows=7010 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 366kB
Buffers: shared hit=168
-> Seq Scan on orders o (cost=0.00..293.57 rows=7010 width=12) (actual time=0.011..2.333 rows=7010 loops=1)
Filter: ((status)::text = 'ПЛАТЕНА'::text)
Rows Removed by Filter: 3036
Buffers: shared hit=168
Planning:
Buffers: shared hit=46
Planning Time: 0.582 ms
Execution Time: 61.307 ms
Споредба:
| Метрика | Вредност |
| Planning Time | 0.582 ms |
| Execution Time | 61.307 ms |
| Вкупно вратени редови | 24 (часови) |
| Тип на скенирање на orders | Seq Scan (со Filter на status) |
| Тип на скенирање на order_item | Seq Scan |
| Индекси искористени | (не се користат поради голем опсег на податоци) |
Заклучок: Овој извештај враќа само 24 редови (часови), но мора да ги прочита сите 30.048 ставки и 7.010 платени нарачки. PostgreSQL избира Seq Scan бидејќи индексот не помага при агрегација на сите податоци.
Финален заклучок од анализата
| Сценарио | Execution Time | Вратени редови | Индекси искористени |
| Најпрофитабилни производи | 110.770 ms | 24 | idx_orders_status |
| Месечни приходи | 18.309 ms | 13 | (Seq Scan) |
| Најпрометни часови | 61.307 ms | 24 | (Seq Scan) |
Клучни наоди:
idx_orders_statusсе користи во Сценарио 1 прекуBitmap Index Scan, што покажува дека индексот е ефективен кога има филтер по статус.- Memoize оптимизација се користи за категориите во Сценарио 1, намалувајќи 20.935 повторени читања на само 5.
- Во Сценарија 2 и 3, PostgreSQL избира Seq Scan бидејќи агрегацијата бара читање на сите податоци (нема селективен филтер).
- Со растот на базата, индексите ќе имаат сè поголемо влијание, особено
idx_orders_statusиidx_orders_created_at.
3. Интегритет и конзистентност
Со цел самата база да биде отпорна на грешки, имплементирани се стриктни ограничувања (CONSTRAINTS) и тригери кои ја заштитуваат финансиската историја.
Уникатност
ALTER TABLE project.app_user ADD CONSTRAINT app_user_username_key UNIQUE (username);
Корисничките имиња се заштитени со UNIQUE ограничување, со што се гарантира дека во системот не можат да постојат два кориснички профили со исто корисничко име.
Надворешни клучеви
Релациите помеѓу табелите се реализирани преку надворешни клучеви (FOREIGN KEY). Со тоа се спречува внесување на записи кои референцираат непостоечки корисници, сметки, нарачки или производи.
Примери:
orders.user_id→app_user.user_idorders.table_id→restaurant_table.table_idorder_item.order_id→orders.order_idorder_item.product_id→product.product_idpayment.order_id→orders.order_idinvoice.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
Ова овозможува враќање на состојбата во случај на грешка или несреќа.
