設定安全性時常見的錯誤與預防方法

「資料庫安全就像一組好密碼：你可以想一個超難的密碼，但如果你把它寫在便利貼上貼在螢幕上——一點用都沒有。」所以我們的目標不只是學會怎麼設安全機制，還要避開那些會讓一切努力白費的常見錯誤。

1. 使用權限太多的角色

很多開發者怕限制太多會有問題，就直接給角色超大權限，比如 SUPERUSER 或 ALL PRIVILEGES。他們會說：「啊，說不定以後會用到啦！」但這種權限太多的角色就是安全的大漏洞。

權限太多的例子：

GRANT ALL PRIVILEGES ON DATABASE university TO student_role;

這樣 student_role 就能完全存取資料庫所有資料。即使這角色本來只該讀資料，現在也能刪表、改結構，甚至把管理員踢掉。

怎麼避免？

建立權限最小化的角色。這叫最小權限原則。像只要讀資料的角色應該這樣設：

GRANT CONNECT ON DATABASE university TO student_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO student_role;

這樣就很明確 student_role 只能連線跟查資料，不能亂搞。

2. 沒有加密機密資料

想像一下 users 表，密碼直接明文存：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    password TEXT NOT NULL
);

如果駭客拿到這表，他就拿到所有人的密碼。這就像把家裡鑰匙放在門口地墊下。

避免這種事，請用 pgcrypto 加密密碼。例如：

CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO users (username, password)
VALUES ('johndoe', pgp_sym_encrypt('secure_password', 'encryption_key'));

要驗證密碼時可以解密：

SELECT username
FROM users
WHERE pgp_sym_decrypt(password::BYTEA, 'encryption_key') = 'secure_password';

千萬不要明文存放機密資訊！

3. 忽略 SQL 注入

SQL 注入還是最常見的攻擊手法之一，因為開發者還是會用字串拼接來組 SQL。像這樣：

DO $$
DECLARE
    username TEXT := 'John';
    query TEXT;
BEGIN
    query := 'SELECT * FROM users WHERE username = ''' || username || ''';';
    EXECUTE query;
END $$;

如果駭客把 username 換成 John' OR '1'='1，就能把 users 表所有資料都撈出來。

怎麼避免？ 用參數化查詢：

PREPARE user_query (TEXT) AS
SELECT * FROM users WHERE username = $1;

EXECUTE user_query('John');

這樣變數會安全帶入，不會被注入。

4. pg_hba.conf 設錯

pg_hba.conf 是用來控管 IP 存取的主要工具。設錯會讓大家都能連進來。

錯誤設定的例子：

host    all     all     0.0.0.0/0       trust

這行讓任何人、任何 IP、任何資料庫都能無密碼連進來。

怎麼避免？ 只開放特定 IP，並用 md5 或 scram-sha-256 認證：

host    university    student_role    192.168.1.0/24    md5

這樣 student_role 只能從內網用密碼連。

改完 pg_hba.conf 記得 reload：

pg_ctl reload

5. ROW LEVEL SECURITY 用錯

RLS 很強大，但沒設好或忘了開根本沒用。像這樣，寫了 policy 但沒開 RLS：

CREATE POLICY my_policy ON users
USING (username = current_user);

-- 但 RLS 沒開！
SELECT * FROM users; -- 全部都看得到！

怎麼避免？ 記得開 RLS：

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

然後測試 policy 有沒有生效：

SET ROLE student_role;

SELECT * FROM users; -- 只看得到符合 policy 的資料。

6. 忽略管理員的行為

有時資料庫管理員有全部資料的權限，其實他們工作不一定需要。這樣如果管理員帳號被盜，風險超大。

怎麼避免？ 分開角色。管理工作用一個沒資料權限的角色：

CREATE ROLE admin_role WITH LOGIN CREATEDB CREATEROLE;

資料存取再用另一個權限最小的角色：

CREATE ROLE data_analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_analyst_role;

根據工作分配角色給使用者：

GRANT admin_role TO some_user;
GRANT data_analyst_role TO another_user;

7. 日誌不夠

沒設好日誌，你根本不會知道有可疑行為，等發現就太晚了。

沒開日誌的例子：

-- postgresql.conf 裡沒設
log_statement = 'none';

怎麼避免？ 至少開基本日誌：

log_statement = 'all'
log_connections = on
log_disconnections = on

這樣你就能看到所有查詢、連線、斷線。

還可以用 pgAudit 擴充功能做更細的稽核：

CREATE EXTENSION pgaudit;

8. 用舊的認證方式

用像 password 這種舊認證方式，根本不夠安全。

怎麼避免？ 換成更安全的 scram-sha-256：

ALTER SYSTEM SET password_encryption = 'scram-sha-256';

然後更新使用者密碼：

ALTER USER student_role WITH PASSWORD 'new_secure_password';

這些問題看起來都很小，但每一個都可能變成大漏洞。你的任務就是把資料庫當成每個想連進來的人都很可疑。俗話說，「信任，但要驗證」。現在你有工具不只可以設安全，還能避開最常見的錯誤。祝你好運，讓你的資料都安全無虞！