Flask 創建一個二手書籍線上購物平台-資料庫設計

本系列文章是紀錄研究所軟體工程課程修課實作過程

資料模型設計

我們的資料庫包含8個實體，這些實體是根據使用者案例圖的設計而來。首先，我們從左上角開始進行說明。每位會員擁有一個「購物車」，而每個「購物車」可以包含多個「購物車品項」，這些「購物車品項」來自於「品項」實體，形成一對一的關係。

在二手平台中，我們從學長姐那裡收集書籍，將這些書籍的資訊登錄到「品項」實體中。由於這些品項有可能是同一本書，所以「品項」與「書籍」之間形成多對一的關係，這麼做的原因是為了區分提供者的身份。

回到前述，會員可以透過平台下單，因此每位會員可能擁有多張「訂單」，而每張訂單則包含多個「訂單商品」。「訂單商品」、「品項」、「書籍」這三個實體之間的關聯形成了訂單明細。

由於在會員模組中有一個「忘記密碼」的功能，我們計劃以系統分發token的方式來驗證會員身份，以便在後續進行密碼更改。因此，我們另外設計了一個實體，稱為「忘記密碼令牌」，以「會員的電子郵件地址」的屬性作為外部鍵，連接「會員」實體。由於我們限制系統中不允許相同的信箱重複註冊，這使得「忘記密碼令牌」和「會員」之間的實體關係保持為一對一。否則，在會員更改密碼時可能會影響到其他會員的密碼。

ERD

ERD（雞爪圖）

資料庫權限設計

PostgreSQL 為本專案選用關聯式資料庫管理系統

RBAC 模型（Role-Based Access Control：基於角色的訪問控制

在談論專案資料庫權限設計之前，我們不得不提及RBAC模型（基於角色的訪問控制）。RBAC模型是一種權限存取機制，由三個基本組成部分組成，即使用者、角色和許可權。這個模型強調誰（WHO）以什麼方式（HOW）可以對什麼（WHAT）進行存取。

以銀行貸款服務為例，承辦櫃員可以查詢顧客聯徵信用紀錄和初步審查貸款資料，最終由主管進行審核和撥款。因此，每個角色都擁有相對應的權責。我們的目標是使角色的權限最小化，以避免權限開放得太大。

同樣地，這種機制也可以應用在資料庫中，具有以下好處：

1. 降低了潛在的安全風險： 如果受到SQL注入攻擊，這種機制可以防止系統使用的帳號擁有過大的權限，從而防止查詢到機敏資訊或竄改重要的資料，攻擊者僅能獲取到相應角色的權限。
2. 細緻權限控制： 對於廠商或一般使用者，可以建立僅能讀取資料的帳號，而只有少數管理者能夠擁有寫入資料的權限，從而確保嚴格的讀寫權責的實施。
3. 簡化權限管理：每個角色擁有特定的權限，無需針對每個使用者進行單獨的權限分配，只要分配對應的角色給使用者即可。

實作

我們將 8 張資料表創建在同一個 schema 底下，建立 rl_sel、rl_del、rl_upd、rl_ins，分別是可以對這 8 張資料表篩選、刪除、更新、新增的權限角色，再來是建立 app_001、it_001 這兩個可登入角色，app_001 是給系統使用的帳號，it_001 是給開發人員使用的帳號，此外這組帳號可以在這個 schema 新增、刪除資料庫物件。

如果後台系統資料表在另一個 schema 存放，會因為這套機制，可以有效地保護資料，不讓角色享有過大的權限，設計上，就可以成立 app_002 角色，只能對後台系統資料表操作，it_001 則可以跨 schema 對資料表操作。

-- 建立資料庫
CREATE DATABASE bookstore;
-- 建立 bookmazon schema
CREATE SCHEMA bookmazon;

-- 可以選資料表內容的角色
CREATE ROLE rl_sel;
-- 可以刪除資料表內容的角色
CREATE ROLE rl_del;
-- 可以更新資料表內容的角色
CREATE ROLE rl_upd;
-- 可以新增資料表內容的角色
CREATE ROLE rl_ins;

-- 為新增、刪除、修改、查詢賦予角色
GRANT SELECT ON TABLE bookmazon.book TO rl_sel;
GRANT DELETE ON TABLE bookmazon.book TO rl_del;
GRANT UPDATE ON TABLE bookmazon.book TO rl_upd;
GRANT INSERT ON TABLE bookmazon.book TO rl_ins;

-- 程式開發人員操作 db 的角色
CREATE ROLE user_001 WITH LOGIN CREATEDB CREATEROLE PASSWORD 'user_001';
GRANT rl_sel, rl_del, rl_upd, rl_ins to user_001;
-- 賦予 bookmazon schema 的使用權限
GRANT USAGE ON SCHEMA bookmazon to user_001;
-- 賦予可以 bookmazon schema 建立物件（trigger、function、procedure）的權限
GRANT CREATE ON SCHEMA bookmazon to user_001;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA bookmazon TO user_001;

-- 後端系統操作 db 的角色
CREATE ROLE app_001 WITH LOGIN PASSWORD 'app_001';
GRANT rl_sel, rl_del, rl_upd, rl_ins to app_001;
-- 賦予 bookmazon schema 的使用權限
GRANT USAGE ON SCHEMA bookmazon to app_001;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA bookmazon TO app_001;

資料庫表空間管理

什麼是資料庫表空間？

在 PostgreSQL，表空間意思是指資料庫的管理者可以自行定義一段檔案系統的路經，讓資料庫的物件都儲存在這個資料夾底下，爾後凡有資料庫的物件，例如：資料表、索引、視圖要找地方儲存的時候，那就可以引用資料庫的管理者所定義好的路徑進行資料儲存。

為什麼要做資料庫表空間管理？

1. 如果要資料表的資料要擴張到別的地方（新增硬碟），可以在不同的分割區上建立資料表空間。
2. 資料庫管理者可以控制 PostgreSQL 安裝的磁碟規畫，假設資料不斷地增長，原有的檔案系統空間不足時，可以在新掛載的檔案系統建立新的表空間。
3. 允許資料庫管理者依資料庫物件特性的知識來優化效能，比較常查詢的資料可以放在固態硬碟上，相對於不常查詢的資料，就可以放在傳統硬碟上。

實務上，如何進行？

在 PostgreSQL，資料表會以檔案方式落地在預設目錄 $PGDATA/base/，檔案以資料庫物件 OID 命名，資料庫的大小取決於磁碟分割的大小。

為了做到資料庫表空間管理，我們選擇將資料改放在 /var/pgdata/bookmazon/，在下 PostgreSQL TABLESPACE 指令之前，我們先建立 /var/pgdata/bookmazon/ 資料夾出來，讓資料夾擁有者賦予給 postgres 角色（作業系統的角色），權限設為 700。

以上操作必須做，否則在下 PostgreSQL TABLESPACE 指令，會因為作業系統的檔案權限不足而失敗。

sudo mkdir /var/pgdata
sudo mkdir /var/pgdata/bookmazon/
sudo chown -R postgres:postgres /var/pgdata
sudo chmod -R 700 /var/pgdata

我們用了 PostgreSQL TABLESPACE 指令在 /var/pgdata/bookmazon/ 建立了表空間。

CREATE TABLESPACE OWNER postgres bookmazontbspace LOCATION '/var/pgdata/bookmazon/';

以建立 password_reset_tokens 資料表為例，在下 CREATE TABLE 指令後面會加上 TABLESPACE [表空間名稱]，來表示建立的資料表的資料要落地在哪一個磁區。

CREATE TABLE bookmazon.password_reset_tokens (
    id              SERIAL       NOT NULL
  , user_email      VARCHAR(255) NOT NULL
  , token           VARCHAR(255) NOT NULL
  , token_status    VARCHAR(1)
  , update_datetime TIMESTAMP
  , create_datetime TIMESTAMP
  , CONSTRAINT pk_password_reset_tokens PRIMARY KEY (id)
) TABLESPACE bookmazontbspace;

參考來源

EnterproseDB Quickstart — 快速入門筆記

PostgreSQL 繁體中文手冊 - 23.6. Tablespaces

PostgreSQL 表空間使用詳解