Transaction និង Safe SQL

មាតិកា

1. Checkout ដែលធ្វើមិនទាន់ចប់
2. ACID ជាភាសាសាមញ្ញ
3. BEGIN, COMMIT, ROLLBACK
4. Checkout ដែលមានសុវត្ថិភាព
5. នៅពេលមានអ្វីបរាជ័យ៖ Rollback ក្នុងសកម្មភាព
6. Race Condition និង FOR UPDATE
7. សង្ខេបអំពី Isolation Level
8. SQL Injection
9. Parameterized Query ជាមួយ Node.js
10. Script Checkout ជាមួយ Transaction
11. Least Privilege៖ Role និង GRANT
12. Backup ជាមួយ pg_dump
13. សេចក្តីសង្ខេប៖ អ្វីដែលអ្នកបានសាងសង់
14. សាកល្បងដោយខ្លួនឯង

1. Checkout ដែលធ្វើមិនទាន់ចប់

អតិថិជនម្នាក់ចុច "Place order"។ នៅពីក្រោយការចុចនោះ កូដរបស់អ្នកត្រូវធ្វើបីរឿង៖

1. បង្កើត row ក្នុង orders។
2. Insert row ក្នុង order_items សម្រាប់ផលិតផលនីមួយៗដែលបានទិញ។
3. ដកចំនួនពី products.stock។

នីមួយៗជា SQL statement ដាច់ដោយឡែក។ ឥឡូវស្រមៃថា server ដាច់ភ្លើងបន្ទាប់ពីជំហានទី 2 ហើយមុនជំហានទី 3។ order មានហើយ អតិថិជនត្រូវបានគិតលុយ ប៉ុន្តែស្តុកមិនត្រូវបានកាត់បន្ថយទេ។ អ្នកនឹងលក់ក្តារចុចចុងក្រោយដដែលពីរដង។ ឬផ្ទុយមកវិញ៖ ស្តុកថយចុះ ប៉ុន្តែ order មិនត្រូវបានរក្សាទុកទេ ហើយអតិថិជនមិនបានអ្វីទាល់តែសោះ។

រាល់ពេលដែលសកម្មភាពតក្កវិជ្ជាមួយត្រូវការ statement ច្រើន នឹងមានចន្លោះពេលដែលទិន្នន័យផ្លាស់ប្តូរពាក់កណ្តាល។ Transaction បិទចន្លោះនោះ។ វាដាក់ statement ជាក្រុម ដើម្បីឱ្យវា កើតឡើងទាំងអស់ ឬមិនកើតឡើងសោះ។

2. ACID ជាភាសាសាមញ្ញ

មូលដ្ឋានទិន្នន័យ relational សន្យាលក្ខណៈបួនសម្រាប់ transaction ដែលចងចាំជា ACID៖

constraint ដែលអ្នកបានសរសេរក្នុងជំពូកទី 3 និងទី 6 គឺជា "C"។ ជំពូកនេះភាគច្រើនអំពី "A" និង "I"។

3. BEGIN, COMMIT, ROLLBACK

រហូតមកដល់ពេលនេះ គ្រប់ statement ដែលអ្នកបានវាយក្នុង psql ដំណើរការក្នុង transaction ស្វ័យប្រវត្តិតូចមួយរបស់វា (ហៅថា autocommit)។ ដើម្បីដាក់ជាក្រុមច្រើន អ្នកបើកមួយដោយខ្លួនឯង៖

BEGIN;
-- ... statement ជាច្រើន ...
COMMIT;      -- ធ្វើឱ្យអ្វីៗទាំងអស់អចិន្ត្រៃយ៍

ឬដើម្បីបោះអ្វីៗចោលទាំងអស់៖

BEGIN;
-- ... statement ជាច្រើន ...
ROLLBACK;    -- មិនធ្វើវិញនូវអ្វីៗទាំងអស់តាំងពី BEGIN

សាកល្បងលើ shopdb។ វាមានសុវត្ថិភាព ព្រោះយើង rollback៖

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;
SELECT id, name, stock FROM products WHERE id = 1;
ROLLBACK;
SELECT id, name, stock FROM products WHERE id = 1;

 id |      name      | stock
----+----------------+-------
  1 | Wireless Mouse |    39
(1 row)

 id |      name      | stock
----+----------------+-------
  1 | Wireless Mouse |    40
(1 row)

នៅក្នុង transaction អ្នកបានឃើញ 39។ បន្ទាប់ពី ROLLBACK វាត្រឡប់ទៅ 40 ដូចជាគ្មានអ្វីកើតឡើង។ session ផ្សេងទៀតមិនដែលឃើញ 39 ទាល់តែសោះ។

ពេល transaction បើក psql បង្ហាញ * ក្នុង prompt (shopdb=*#)។ បន្ទាប់ពីមានកំហុសក្នុង transaction prompt បង្ហាញ ! (shopdb=!#) មានន័យថា transaction ត្រូវបាន abort ហើយផ្លូវតែមួយគត់ទៅមុខគឺ ROLLBACK។

4. Checkout ដែលមានសុវត្ថិភាព

តោះសរសេរ checkout ពិតប្រាកដ។ Vuthy (អតិថិជន 4) ចង់បាន Coffee Mug 2 (ផលិតផល 7 តម្លៃ 6.00) និង Notebook Pack 1 (ផលិតផល 8 តម្លៃ 9.50)។ រក្សាទុកនេះជា shopdb/queries/checkout.sql៖

BEGIN;

-- 1. បង្កើត order
INSERT INTO orders (customer_id, status)
VALUES (4, 'paid');

-- 2. បន្ថែមធាតុ ដោយចម្លងតម្លៃបច្ចុប្បន្នជា snapshot (ជំ. 10)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT currval(pg_get_serial_sequence('orders', 'id')),
       p.id, v.quantity, p.price
FROM (VALUES (7, 2), (8, 1)) AS v(product_id, quantity)
JOIN products p ON p.id = v.product_id;

-- 3. កាត់បន្ថយស្តុក
UPDATE products SET stock = stock - 2 WHERE id = 7;
UPDATE products SET stock = stock - 1 WHERE id = 8;

COMMIT;

កត់សម្គាល់ពីរចំណុច។ currval(pg_get_serial_sequence('orders', 'id')) ត្រឡប់ id ដែល INSERT ទើបតែបង្កើតក្នុង session នេះ ដូច្នេះអ្នកមិនដែល hard-code លេខ order ទេ។ ហើយបញ្ជី VALUES ដែល JOIN ទៅ products អនុញ្ញាតឱ្យយើងទាញតម្លៃនីមួយៗពី table ដូច្នេះ snapshot មិនអាចវាយខុសបានទេ។

ដំណើរការវា ហើយពិនិត្យលទ្ធផល៖

docker exec -i shopdb-pg psql -U postgres -d shopdb \
  < queries/checkout.sql

SELECT id, name, stock FROM products WHERE id IN (7, 8);
SELECT * FROM order_items WHERE order_id = 6;

 id |     name     | stock
----+--------------+-------
  7 | Coffee Mug   |    78
  8 | Notebook Pack|    59
(2 rows)

 order_id | product_id | quantity | unit_price
----------+------------+----------+------------
        6 |          7 |        2 |       6.00
        6 |          8 |        1 |       9.50
(2 rows)

(លទ្ធផលធម្មតា ដោយសន្មតថាអ្នកចាប់ផ្តើមពី seed data ដូច្នេះនេះជា order ទីប្រាំមួយ។) ដោយសារអ្វីៗទាំងអស់ស្ថិតក្នុង transaction តែមួយ គ្មានអ្នកសង្កេតណាអាចឃើញ order ដោយគ្មានធាតុរបស់វា ឬធាតុដោយគ្មានការផ្លាស់ប្តូរស្តុករបស់វាបានទេ។

5. នៅពេលមានអ្វីបរាជ័យ៖ Rollback ក្នុងសកម្មភាព

ឥឡូវផ្នែកសំខាន់។ Maly (អតិថិជន 5) ចង់បាន Desk Lamp ជាផលិតផល 6។ ស្តុករបស់វាគឺ 0។ CHECK (stock >= 0) constraint របស់យើងគួរតែរារាំងវា៖

BEGIN;

INSERT INTO orders (customer_id, status) VALUES (5, 'paid');

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (currval(pg_get_serial_sequence('orders', 'id')), 6, 1, 22.00);

UPDATE products SET stock = stock - 1 WHERE id = 6;

BEGIN
INSERT 0 1
INSERT 0 1
ERROR:  new row for relation "products" violates check constraint "products_stock_check"
DETAIL:  Failing row contains (6, Desk Lamp, 22.00, -1, 3).

insert ពីរដំបូងដំណើរការ។ statement ទីបីបរាជ័យ។ ឥឡូវ transaction ត្រូវបាន abort ហើយ psql បដិសេធអ្វីៗទាំងអស់រហូតដល់អ្នកបញ្ចប់វា៖

SELECT count(*) FROM orders;

ERROR:  current transaction is aborted, commands ignored until end of transaction block

ROLLBACK;
SELECT count(*) FROM orders;

ROLLBACK
 count
-------
     6
(1 row)

Order ប្រាំមួយ៖ ប្រាំពី seed បូករួមទាំងរបស់ Vuthy។ order ដែលបង្កើតពាក់កណ្តាលរបស់ Maly បាត់ទៅ រួមជាមួយធាតុរបស់វា។ មូលដ្ឋានទិន្នន័យនៅដូចដែលវាជាមុន BEGIN ទាំងស្រុង។ នោះគឺជា atomicity ដែល CHECK constraint ដើរតួជាអ្នកយាម៖ វាបដិសេធមិនឱ្យលក់លើសស្តុក ហើយ transaction មិនធ្វើវិញនូវអ្វីៗផ្សេងទៀតទាំងអស់។

ចំណុចចម្លែកតូចមួយ៖ ការព្យាយាមដែលបរាជ័យនៅតែបានប្រើអស់ order id មួយ។ order ជោគជ័យបន្ទាប់នឹងជា 7 មិនមែន 6 ទេ។ Identity sequence មិនដែល rollback ទេ ហើយគម្លាតក្នុង id គឺធម្មតា និងគ្មានគ្រោះថ្នាក់។

ប្រសិនបើអ្នកដំណើរការ script ដោយ psql -f ហើយចង់ឱ្យវាឈប់នៅកំហុសដំបូង ជំនួសឱ្យការបន្តទៅមុខ សូមបន្ថែម -v ON_ERROR_STOP=1៖

docker exec -i shopdb-pg psql -U postgres -d shopdb \
  -v ON_ERROR_STOP=1 < queries/checkout.sql

6. Race Condition និង FOR UPDATE

Transaction ការពារអ្នកពីការបរាជ័យ។ ដោយខ្លួនឯង វាមិនការពារអ្នកពីអតិថិជនពីរនាក់ដែលធ្វើសកម្មភាព ក្នុងពេលតែមួយ ទេ។ ពិចារណាក្តារចុច Mechanical Keyboard ចុងក្រោយ (ឧបមាថាស្តុករបស់វាគឺ 1)។ អតិថិជនពីរនាក់ចុចទិញក្នុងវិនាទីតែមួយ។ កម្មវិធីឆោតល្ងង់ធ្វើដូចនេះ៖

1. អានស្តុក៖ SELECT stock FROM products WHERE id = 3។
2. ប្រសិនបើស្តុកយ៉ាងហោចណាស់ 1 UPDATE products SET stock = stock - 1។

ទាំង session ពីរអាន 1 នៅជំហានទី 1 ទាំងពីរសម្រេចថាមិនអីទេ ទាំងពីរបន្តទៅមុខ។ ដោយគ្មាន CHECK constraint អ្នកនឹងលក់ក្តារចុចពីរដែលអ្នកមិនមាន។ នេះគឺជា race condition៖ លទ្ធផលអាស្រ័យលើពេលវេលា។

(ក្នុងករណីជាក់លាក់នេះ stock = stock - 1 បូក CHECK របស់យើងពិតជាជួយសង្គ្រោះអ្នក ព្រោះ update ទីពីរបរាជ័យ។ ប៉ុន្តែលំនាំនៃ "អានតម្លៃ សម្រេចក្នុងកូដរបស់អ្នក បន្ទាប់មកសរសេរ" គឺគ្រោះថ្នាក់សម្រាប់អ្វីៗដែលគ្មានអ្នកយាមបែបនោះ។)

ដំណោះស្រាយគឺ ចាក់សោរ row ខណៈអ្នកសម្រេច ដោយប្រើ SELECT ... FOR UPDATE៖

BEGIN;

SELECT stock FROM products WHERE id = 3 FOR UPDATE;
-- Row 3 ឥឡូវត្រូវបានចាក់សោរ។ transaction ផ្សេងទៀតដែលព្យាយាម
-- UPDATE វា ឬ SELECT ... FOR UPDATE វា នឹងរង់ចាំនៅទីនេះ។

-- កូដរបស់អ្នកពិនិត្យស្តុក ហើយសម្រេច។
UPDATE products SET stock = stock - 1 WHERE id = 3;

COMMIT;   -- ដោះសោរ

អ្នកអាចមើលវាដោយប្រើបង្អួច psql ពីរ។ នៅបង្អួច A ដំណើរការ BEGIN; បន្ទាប់មក SELECT ... FOR UPDATE។ នៅបង្អួច B ដំណើរការគូដូចគ្នា។ បង្អួច B គាំង នៅ SELECT របស់វា កំពុងរង់ចាំ។ នៅពេលអ្នក COMMIT ក្នុងបង្អួច A បង្អួច B បន្តភ្លាមៗ ហើយឃើញតម្លៃស្តុក ថ្មី។ checkout ទាំងពីរឥឡូវត្រូវបានបង្ខំឱ្យកើតឡើងមួយបន្ទាប់ពីមួយទៀត។

រក្សាទម្លាប់ចាក់សោរទាំងនេះ៖

• រក្សា transaction ឱ្យ ខ្លី។ សោរត្រូវបានកាន់រហូតដល់ COMMIT ឬ ROLLBACK។
• កុំរង់ចាំអ្នកប្រើ (ទំព័របង់ប្រាក់ ការសួរបញ្ជាក់) ខណៈ transaction បើក។
• ចាក់សោរ row តាមលំដាប់ស្របគ្នា (ឧទាហរណ៍តាម product id) ដើម្បីកុំឱ្យ transaction ពីររង់ចាំគ្នាជារៀងរហូត ស្ថានភាពនេះហៅថា deadlock។ PostgreSQL រកឃើញវា ហើយលុបចោលមួយក្នុងចំណោមនោះដោយមានកំហុស ហើយកម្មវិធីរបស់អ្នកគួរត្រៀមខ្លួនសាកល្បងម្តងទៀត (retry)។

7. សង្ខេបអំពី Isolation Level

តើ transaction មួយគួរឃើញការងាររបស់មួយទៀតប៉ុណ្ណា? នោះគឺជា isolation level។ PostgreSQL ផ្តល់បីដែលសំខាន់៖

(PostgreSQL ក៏ទទួល READ UNCOMMITTED ដែរ ប៉ុន្តែចាត់ទុកវាជា READ COMMITTED៖ វាមិនដែលបង្ហាញទិន្នន័យដែលមិនទាន់ commit ដល់អ្នកទេ។)

អ្នកជ្រើសរើសក្នុងមួយ transaction៖

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- របាយការណ៍ដែលត្រូវការទិដ្ឋភាពស្របគ្នានៃទិន្នន័យ
COMMIT;

សម្រាប់កូដកម្មវិធីភាគច្រើន លំនាំដើមបូកការប្រើ FOR UPDATE ដោយប្រុងប្រយ័ត្នគឺត្រឹមត្រូវ។ ងាកទៅរក level ខ្ពស់ជាងនៅពេលអ្នកបង្កើតរបាយការណ៍លើ table ច្រើន ហើយត្រូវការឱ្យគ្រប់ query ឃើញពេលវេលាតែមួយ ឬនៅពេលតក្កវិជ្ជាស្មុគស្មាញពេកក្នុងការចាក់សោរដោយដៃ ហើយចងចាំថាកូដដែលប្រើវាត្រូវតែត្រៀមខ្លួន សាកល្បងម្តងទៀត នៅពេលមាន serialization failure។

8. SQL Injection

ឥឡូវផ្នែកម្ខាងទៀតនៃ "safe SQL"៖ ការការពារមូលដ្ឋានទិន្នន័យពីមនុស្សដែលប្រើកម្មវិធីរបស់អ្នក។

ស្រមៃថា server Node.js របស់អ្នករកអតិថិជនតាមអ៊ីមែល។ វិធីធម្មជាតិក្នុងការសរសេរវា និងជាវិធីដ៏អាក្រក់៖

// កុំធ្វើបែបនេះ
const email = req.query.email;   // មកពីអ្នកប្រើ
const sql = "SELECT * FROM customers WHERE email = '" + email + "'";
const result = await db.query(sql);

ជាមួយ input ដែលរួសរាយ dara@example.com string ក្លាយជា៖

SELECT * FROM customers WHERE email = 'dara@example.com'

ល្អ។ ឥឡូវអ្នកប្រើវាយនេះជាអ៊ីមែល៖

' OR '1'='1

ហើយ SQL ក្លាយជា៖

SELECT * FROM customers WHERE email = '' OR '1'='1'

'1'='1' តែងតែពិត ដូច្នេះ query ត្រឡប់ អតិថិជនគ្រប់គ្នា។ អ្នកវាយប្រហារបានផ្លាស់ប្តូរ អត្ថន័យ នៃ query របស់អ្នក ព្រោះអត្ថបទរបស់ពួកគេត្រូវបានបិទភ្ជាប់ត្រង់ទៅក្នុង SQL។ នេះគឺជា SQL injection ដែលជាភាពងាយរងគ្រោះគេហទំព័រចាស់ជាងគេមួយ និងនៅតែបំផ្លាញខ្លាំងបំផុត។

វាអាក្រក់ជាងនេះទៅទៀត។ input ដូចជា '; DROP TABLE customers; -- អាចព្យាយាមបញ្ចប់ statement របស់អ្នក ហើយចាប់ផ្តើមមួយថ្មី ហើយ -- បិទ comment នូវ SQL ដែលនៅសល់របស់អ្នក។ ថាតើការវាយប្រហារពិតប្រាកដនោះដំណើរការឬអត់ អាស្រ័យលើ driver ប៉ុន្តែមេរៀនគឺដដែល៖ ប្រសិនបើអត្ថបទអ្នកប្រើជាផ្នែកនៃ SQL string នោះអ្នកប្រើកំពុងសរសេរ SQL របស់អ្នក។

កុំសាងសង់ SQL ដោយភ្ជាប់ (concatenate) ឬបញ្ចូល (${email}) input របស់អ្នកប្រើ។ មិនមែនសម្រាប់អ៊ីមែល មិនមែនសម្រាប់ id មិនមែនសម្រាប់ column តម្រៀបទេ។ មិនមានបរិមាណណាមួយដែលមានសុវត្ថិភាពទេ។

9. Parameterized Query ជាមួយ Node.js

ដំណោះស្រាយគឺ parameterized query (ហៅម្យ៉ាងទៀតថា prepared statement ឬ bind variable)។ អ្នកផ្ញើអត្ថបទ SQL និងតម្លៃ ដោយឡែកពីគ្នា។ មូលដ្ឋានទិន្នន័យញែក SQL មុនជាមួយ placeholder ($1, $2) បន្ទាប់មកដោតតម្លៃចូលជាទិន្នន័យសុទ្ធ ដូច្នេះតម្លៃមិនដែលអាចក្លាយជា SQL បានទេ។

រៀបចំវាក្នុងថតគម្រោង។ កញ្ចប់ pg គឺជា PostgreSQL client ស្តង់ដារសម្រាប់ Node.js៖

cd shopdb
npm init -y
npm install pg

កំណែមានសុវត្ថិភាពនៃការរកមើល រក្សាទុកជា shopdb/find-customer.mjs៖

import pg from "pg";

const pool = new pg.Pool({
  host: "localhost",
  port: 5432,
  user: "postgres",
  password: "postgres",
  database: "shopdb",
});

const email = process.argv[2];   // input ដែលមិនគួរទុកចិត្ត

const result = await pool.query(
  "SELECT id, name, email FROM customers WHERE email = $1",
  [email]
);

console.log(result.rows);
await pool.end();

node find-customer.mjs dara@example.com

[ { id: 2, name: 'Dara Kim', email: 'dara@example.com' } ]

ឥឡូវសាកល្បងការវាយប្រហារ៖

node find-customer.mjs "' OR '1'='1"

[]

array ទទេ។ string វាយប្រហារទាំងមូលត្រូវបានចាត់ទុកជាអ៊ីមែលតែមួយ (literal) ដែលគ្មាននរណាមាន ព្រោះ driver បានផ្ញើវាជាតម្លៃ មិនមែនជា SQL ទេ។

ច្បាប់សម្រាប់ parameter៖

នេះគឺជាទម្លាប់សំខាន់បំផុតតែមួយសម្រាប់អ្នកអភិវឌ្ឍន៍ណាដែលនិយាយជាមួយមូលដ្ឋានទិន្នន័យ។ ស៊េរី security ដែលបានគ្រោងទុកនៅលើគេហទំព័រនេះគ្របដណ្តប់ injection និង OWASP Top 10 ដែលនៅសល់ឱ្យទូលំទូលាយជាងនេះ។

10. Script Checkout ជាមួយ Transaction

ឥឡូវផ្សំគំនិតទាំងពីរ៖ transaction និង parameter ក្នុង Node.js។ ដោយសារ Pool ចែក connection ផ្សេងគ្នាក្នុងមួយ query transaction ត្រូវការ client ឯកទេសមួយ។ រក្សាទុកជា shopdb/checkout.mjs៖

import pg from "pg";

const pool = new pg.Pool({
  host: "localhost",
  port: 5432,
  user: "postgres",
  password: "postgres",
  database: "shopdb",
});

async function checkout(customerId, items) {
  const client = await pool.connect();   // connection មួយសម្រាប់ txn ទាំងមូល
  try {
    await client.query("BEGIN");

    const order = await client.query(
      "INSERT INTO orders (customer_id, status) VALUES ($1, 'paid') RETURNING id",
      [customerId]
    );
    const orderId = order.rows[0].id;

    for (const { productId, quantity } of items) {
      // ចាក់សោរ row ផលិតផល ដើម្បីកុំឱ្យ checkout ពីរ race គ្នា
      const product = await client.query(
        "SELECT price, stock FROM products WHERE id = $1 FOR UPDATE",
        [productId]
      );
      if (product.rows.length === 0) {
        throw new Error("Unknown product " + productId);
      }
      const { price, stock } = product.rows[0];
      if (stock < quantity) {
        throw new Error("Not enough stock for product " + productId);
      }

      await client.query(
        `INSERT INTO order_items (order_id, product_id, quantity, unit_price)
         VALUES ($1, $2, $3, $4)`,
        [orderId, productId, quantity, price]
      );
      await client.query(
        "UPDATE products SET stock = stock - $1 WHERE id = $2",
        [quantity, productId]
      );
    }

    await client.query("COMMIT");
    return orderId;
  } catch (err) {
    await client.query("ROLLBACK");      // មិនធ្វើវិញអ្វីៗទាំងអស់
    throw err;
  } finally {
    client.release();                    // តែងតែប្រគល់ connection ត្រឡប់វិញ
  }
}

try {
  const id = await checkout(3, [
    { productId: 1, quantity: 1 },
    { productId: 6, quantity: 1 },       // Desk Lamp ស្តុកគឺ 0
  ]);
  console.log("Created order", id);
} catch (err) {
  console.error("Checkout failed:", err.message);
} finally {
  await pool.end();
}

ដំណើរការវា៖

node checkout.mjs

Checkout failed: Not enough stock for product 6

mouse ត្រូវបានបន្ថែមទៅ order មុនពេល lamp បរាជ័យ ប៉ុន្តែ ROLLBACK ក្នុងប្លុក catch បានលុបវាចេញ ហើយស្តុកនៃផលិតផល 1 មិនត្រូវបានប៉ះពាល់ទេ។ ប្តូរ lamp ទៅជាផលិតផល 5 (Clean Code) ហើយដំណើរការម្តងទៀតដើម្បីមើល order ត្រូវបានបង្កើត។ លំនាំគឺដូចគ្នាជានិច្ច៖ BEGIN ធ្វើការ COMMIT ជាមួយ ROLLBACK នៅពេលមានកំហុសណាមួយ និង release() ក្នុង finally។

FOR UPDATE នៅទីនេះធ្វើឱ្យតក្កវិជ្ជា "ពិនិត្យស្តុក បន្ទាប់មកដក" មានសុវត្ថិភាពប្រឆាំងនឹង race ពីផ្នែកទី 6 ហើយគ្រប់តម្លៃធ្វើដំណើរជា parameter។

11. Least Privilege៖ Role និង GRANT

រហូតមកដល់ពេលនេះអ្វីៗទាំងអស់ភ្ជាប់ជា postgres ដែលជា superuser មានអំណាចទាំងអស់។ ប្រសិនបើកម្មវិធីគេហទំព័ររបស់អ្នកភ្ជាប់ជា postgres នោះ bug injection តែមួយអាចឱ្យអ្នកវាយប្រហារលុបគ្រប់ table។ គោលការណ៍ least privilege និយាយថា៖ ផ្តល់ឱ្យកម្មវិធីនីមួយៗតែសិទ្ធិដែលវាត្រូវការប៉ុណ្ណោះ។

បង្កើត role សម្រាប់កម្មវិធីដែលមាន login និងសិទ្ធិមានកំណត់៖

CREATE ROLE app_user LOGIN PASSWORD 'change_me_locally';

GRANT CONNECT ON DATABASE shopdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- អានតែប៉ុណ្ណោះលើទិន្នន័យយោង
GRANT SELECT ON categories, products, customers TO app_user;

-- checkout ត្រូវការទាំងនេះ
GRANT INSERT ON orders, order_items TO app_user;
GRANT UPDATE (stock) ON products TO app_user;
GRANT SELECT ON orders, order_items TO app_user;

-- Identity column ប្រើ sequence
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

កត់សម្គាល់ GRANT UPDATE (stock) ON products៖ កម្មវិធីអាចផ្លាស់ប្តូរតែ column stock មិនមែនតម្លៃទេ។ ពាក្យសម្ងាត់ខាងលើគឺសម្រាប់ប្រើបណ្តោះអាសន្នលើ container ក្នុងម៉ាស៊ីនរបស់អ្នក។ ក្នុងការដាក់ពង្រាយពិត សូមរក្សាទុក secret ក្នុង environment variable ឬ secrets manager មិនមែនក្នុងកូដ ឬ repository របស់អ្នកទេ។

សាកល្បងវាដោយភ្ជាប់ជា role នោះ៖

docker exec -it shopdb-pg psql -U app_user -d shopdb

shopdb=> DROP TABLE customers;
ERROR:  must be owner of table customers

shopdb=> DELETE FROM orders;
ERROR:  permission denied for table orders

bug ដូចគ្នាដែលនឹងក្លាយជាមហន្តរាយនៅពេលជា postgres ឥឡូវត្រូវបានរារាំងដោយមូលដ្ឋានទិន្នន័យខ្លួនឯង។ ឱ្យ script Node.js របស់អ្នកភ្ជាប់ជា app_user (វាត្រូវការ SELECT លើ products បូកសិទ្ធិខាងលើ ដែលយើងបានផ្តល់ហើយ)។ ការងារ admin ដូចជា migration គួរប្រើ role ដាច់ដោយឡែកដែលមានអំណាចជាង។

ដើម្បីដកសិទ្ធិនៅពេលក្រោយ REVOKE ដំណើរការផ្ទុយពី GRANT។

12. Backup ជាមួយ pg_dump

Transaction ការពារអ្នកពីការបរាជ័យ នៅក្នុង មូលដ្ឋានទិន្នន័យ។ ពួកវាមិនការពារអ្នកពីថាសដែលត្រូវបានលុប DROP TABLE ដែលបាន commit ឬ migration អាក្រក់ទេ។ សម្រាប់រឿងនោះអ្នកត្រូវការ backup។

pg_dump សរសេរមូលដ្ឋានទិន្នន័យទាំងមូលជា SQL script៖

docker exec shopdb-pg pg_dump -U postgres shopdb > shopdb-backup.sql

ឯកសារលទ្ធផលមាន statement ប្រភេទ CREATE TABLE និង INSERT។ ដើម្បី restore ទៅមូលដ្ឋានទិន្នន័យថ្មី៖

docker exec shopdb-pg psql -U postgres -c "CREATE DATABASE shopdb_restored;"
docker exec -i shopdb-pg psql -U postgres -d shopdb_restored \
  < shopdb-backup.sql

ទម្លាប់ backup ល្អ៖

• ធ្វើវាដោយស្វ័យប្រវត្តិ (job តាមកាលវិភាគ) ហើយរក្សាច្បាប់ចម្លង ក្រៅ ម៉ាស៊ីនដែលដំណើរការមូលដ្ឋានទិន្នន័យ។
• backup ដែលអ្នកមិនដែល restore គឺជាក្តីសង្ឃឹម មិនមែន backup ទេ។ សាកល្បងការ restore។
• backup មុន migration ណាដែលមានហានិភ័យ។
• សេវាមូលដ្ឋានទិន្នន័យ managed ផ្តល់ backup ស្វ័យប្រវត្តិ និង point-in-time recovery។ នោះជាមូលហេតុធំមួយដែលមនុស្សបង់ប្រាក់សម្រាប់វា។

13. សេចក្តីសង្ខេប៖ អ្វីដែលអ្នកបានសាងសង់

មើលអ្វីដែលអ្នកមានបន្ទាប់ពីដប់ពីរជំពូក៖

• មូលដ្ឋានទិន្នន័យ PostgreSQL ក្នុង Docker ដែលអ្នកអាចសាងសង់ឡើងវិញពី schema.sql និង seed.sql ដោយពាក្យបញ្ជាតែមួយ។
• schema ដែលរចនាបានល្អ៖ table ប្រាំ key constraint និង index លើ foreign key។
• បណ្ណាល័យ query៖ filter, JOIN, aggregate, CTE, window function, view។
• checkout ដែលមានសុវត្ថិភាព៖ transaction atomic ជាមួយការចាក់សោរ row ដែលហៅពី Node.js ដោយ parameterized query ដំណើរការក្រោម role មានកំណត់។
• Backup និងវាក្យសព្ទដើម្បីវែកញែកអំពី normalization, index និង concurrency។

ការសង្ខេបខ្លីអំពីទម្លាប់ដែលសំខាន់បំផុត៖

14. សាកល្បងដោយខ្លួនឯង

1. បង្ហាញ atomicity។ ក្នុង psql BEGIN insert អតិថិជនថ្មី បន្ទាប់មក ROLLBACK។ ពិនិត្យ SELECT count(*) FROM customers។ ធ្វើម្តងទៀតជាមួយ COMMIT ហើយប្រៀបធៀប។ តើមានអ្វីកើតឡើងចំពោះ identity counter ក្នុងករណីនីមួយៗ?
2. ធ្វើឱ្យវាខូចដោយចេតនា។ សរសេរ transaction ដែល insert order ហើយបន្ទាប់មក row order_items ជាមួយ product_id ដែលមិនមាន។ តើអ្នកទទួលបានកំហុសអ្វី ហើយមាន order ប៉ុន្មានបន្ទាប់ពី ROLLBACK?
3. បង្អួចពីរ។ បើក session psql ពីរ។ ក្នុងទាំងពីរ ដំណើរការ BEGIN និង SELECT ... FOR UPDATE លើផលិតផលតែមួយ។ ពិពណ៌នាឱ្យច្បាស់ថាបង្អួច B ធ្វើអ្វី និងពេលណា។ បន្ទាប់មកជំនួស FOR UPDATE ដោយ SELECT ធម្មតា ហើយពន្យល់ថាមានអ្វីផ្លាស់ប្តូរ។
4. បន្ថែមផលបូក។ ពង្រីក checkout.sql ដើម្បីឱ្យបន្ទាប់ពី insert ធាតុ វាបោះពុម្ពផលបូករបស់ order (SELECT sum(quantity * unit_price) សម្រាប់ order ថ្មី)។ ដំណើរការវាពីរដង ហើយបញ្ជាក់ថាស្តុកថយចុះរាល់ពេល។
5. វាយប្រហារកូដរបស់អ្នកផ្ទាល់។ សរសេរកំណែមិនមានសុវត្ថិភាពដែលភ្ជាប់ string នៃ find-customer.mjs ហើយដំណើរការវាជាមួយ input ' OR '1'='1។ រាប់ row ដែលត្រឡប់មក។ បន្ទាប់មកប្តូរទៅ $1 ហើយដំណើរការម្តងទៀត។
6. Least privilege។ ភ្ជាប់ជា app_user ហើយព្យាយាម UPDATE products SET price = 1។ តើមានអ្វីកើតឡើង? បន្ទាប់មកសាកល្បង UPDATE products SET stock = stock។ ហេតុអ្វីទីពីរត្រូវបានអនុញ្ញាត?

ទៅទីណាបន្ត។ អ្នកបានបញ្ចប់ស៊េរីនេះហើយ៖ អ្នកអាចរចនា schema relational query វា ធ្វើឱ្យវាលឿន និងផ្លាស់ប្តូរវាដោយសុវត្ថិភាព។ ជំហានបន្ទាប់ដោយធម្មជាតិគឺប្រើមូលដ្ឋានទិន្នន័យពី backend ពិត។ NestJS Crash Course សាងសង់ API framework ដែលអ្នកអាចភ្ជាប់ទៅ PostgreSQL ដោយយកគំនិតគ្រប់យ៉ាងពីស៊េរីនេះ (parameterized query, transaction, migration) មកប្រើ ហើយ Backend track នៅលើ Start Here រៀបរាប់ពីផ្លូវដែលនៅសល់។ រក្សា shopdb ទុកជាកន្លែងលេង ហើយបន្តសួរសំណួរទៅទិន្នន័យរបស់អ្នកជា SQL។