安全性與權限

資料庫安全是系統安全的關鍵。本篇介紹 PostgreSQL 的角色系統、權限管理與安全設定。

一、角色 (Role)

PostgreSQL 統一使用「角色」概念，角色可以是使用者或群組。

建立角色

sql

-- 建立可登入的使用者
CREATE ROLE alice WITH LOGIN PASSWORD 'secure_password';

-- 建立群組角色（不能登入）
CREATE ROLE developers;

-- 常用選項
CREATE ROLE bob WITH
    LOGIN
    PASSWORD 'password'
    CREATEDB
    CREATEROLE
    VALID UNTIL '2025-01-01';

角色選項

選項	說明
LOGIN	可以登入
SUPERUSER	超級使用者
CREATEDB	可建立資料庫
CREATEROLE	可建立角色
REPLICATION	可進行複寫
INHERIT	繼承父角色的權限

角色繼承

sql

-- alice 加入 developers 群組
GRANT developers TO alice;

-- alice 繼承 developers 的所有權限

二、權限管理

權限類型

權限	說明	物件
SELECT	讀取	表、視圖、欄位
INSERT	插入	表
UPDATE	更新	表、欄位
DELETE	刪除	表
TRUNCATE	清空	表
REFERENCES	建立外鍵	表
TRIGGER	建立觸發器	表
CREATE	建立物件	Schema、資料庫
CONNECT	連線	資料庫
USAGE	使用	Schema、Sequence
EXECUTE	執行	函數

GRANT 授權

sql

-- 授權表的存取
GRANT SELECT ON users TO alice;
GRANT SELECT, INSERT, UPDATE ON orders TO developers;
GRANT ALL PRIVILEGES ON products TO admin;

-- 授權所有表
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

-- 授權欄位
GRANT SELECT (id, name, email) ON users TO limited_role;
GRANT UPDATE (status) ON orders TO support_role;

-- 授權 Schema
GRANT USAGE ON SCHEMA api TO app_role;
GRANT CREATE ON SCHEMA public TO developers;

-- 授權函數
GRANT EXECUTE ON FUNCTION calculate_total TO app_role;

REVOKE 撤銷

sql

REVOKE INSERT ON orders FROM alice;
REVOKE ALL PRIVILEGES ON users FROM developers;

預設權限

設定未來建立的物件自動授權：

sql

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_role;

三、 Row Level Security (RLS)

行級安全策略，每個使用者只能看到自己的資料。

啟用 RLS

sql

-- 建立表
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    owner TEXT NOT NULL,
    title TEXT,
    content TEXT
);

-- 啟用 RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 建立策略：使用者只能看到自己的文件
CREATE POLICY user_documents ON documents
    FOR ALL
    TO PUBLIC
    USING (owner = current_user)
    WITH CHECK (owner = current_user);

策略類型

選項	說明
`FOR ALL`	所有操作
`FOR SELECT`	只限讀取
`FOR INSERT`	只限插入
`FOR UPDATE`	只限更新
`FOR DELETE`	只限刪除

多租戶範例

sql

-- 假設每個請求帶有 tenant_id
CREATE POLICY tenant_isolation ON records
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

-- 應用程式設定 tenant_id
SET app.tenant_id = '123';
SELECT * FROM records;  -- 只會看到 tenant_id = 123 的資料

管理員繞過 RLS

sql

-- 允許特定角色繞過 RLS
ALTER TABLE documents FORCE ROW LEVEL SECURITY;  -- 連表擁有者也要遵守

-- 或創建繞過策略
CREATE POLICY admin_bypass ON documents
    FOR ALL
    TO admin_role
    USING (true);

四、 pg_hba.conf 連線認證

控制誰可以從哪裡連線。

# TYPE  DATABASE  USER        ADDRESS         METHOD

# 本機連線（Unix Socket）
local   all       postgres                    peer
local   all       all                         scram-sha-256

# IPv4 本機
host    all       all         127.0.0.1/32    scram-sha-256

# 內網
host    all       all         10.0.0.0/8      scram-sha-256

# 複寫連線
host    replication replicator 10.0.0.0/8     scram-sha-256

# 拒絕所有其他連線（預設）
host    all       all         0.0.0.0/0       reject

認證方法

方法	說明
trust	無需密碼（危險）
reject	拒絕
scram-sha-256	安全密碼認證（推薦）
md5	舊式密碼認證
peer	使用作業系統使用者
cert	SSL 憑證
ldap	LDAP 認證

五、 SSL/TLS 加密

產生憑證

bash

# 產生自簽憑證
openssl req -new -x509 -days 365 -nodes -text \
    -out server.crt \
    -keyout server.key \
    -subj "/CN=db.example.com"

chmod 600 server.key
chown postgres:postgres server.crt server.key
mv server.crt server.key /var/lib/postgresql/16/main/

設定 PostgreSQL

conf

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

強制 SSL 連線

# pg_hba.conf
hostssl all all 0.0.0.0/0 scram-sha-256

客戶端連線

bash

psql "host=db.example.com dbname=mydb user=alice sslmode=require"

sslmode	說明
disable	不使用 SSL
allow	可以不用
prefer	優先使用（預設）
require	必須使用
verify-ca	驗證 CA
verify-full	驗證 CA + 主機名

六、密碼管理

密碼加密方式

conf

# postgresql.conf
password_encryption = scram-sha-256  # 推薦

修改密碼

sql

ALTER ROLE alice WITH PASSWORD 'new_password';

密碼有效期限

sql

-- 設定密碼過期
ALTER ROLE alice VALID UNTIL '2025-01-01';

-- 強制下次登入改密碼（需應用程式配合）

七、審計日誌

開啟日誌

conf

# postgresql.conf
log_connections = on
log_disconnections = on
log_statement = 'all'  # none, ddl, mod, all
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

pgAudit 擴充

更細緻的審計控制：

sql

CREATE EXTENSION pgaudit;

-- 設定審計
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
ALTER SYSTEM SET pgaudit.log_catalog = off;

SELECT pg_reload_conf();

八、最佳實踐

1. 最小權限原則

sql

-- 建立應用程式專用角色
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure';

-- 只授予必要權限
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- 不要使用 superuser 連線應用程式

2. 分離角色

sql

-- 唯讀角色
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 讀寫角色
CREATE ROLE readwrite;
GRANT readonly TO readwrite;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;

3. 避免使用 PUBLIC

sql

-- 撤銷所有人的預設權限
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- 明確授權
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

4. 定期檢查

sql

-- 查看角色權限
SELECT * FROM information_schema.table_privileges
WHERE grantee = 'alice';

-- 查看角色成員
SELECT r.rolname, m.rolname AS member
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid;

總結

概念	說明
Role	統一的使用者/群組概念
GRANT/REVOKE	權限授予與撤銷
RLS	行級安全策略
pg_hba.conf	連線認證設定
SSL	加密連線
pgAudit	審計日誌

安全原則：

最小權限
加密傳輸
審計日誌
定期檢查

進階挑戰

設計一個多租戶資料庫，使用 RLS 確保每個租戶只能看到自己的資料
設定 SSL 連線，並測試 sslmode=verify-full 的驗證流程
使用 pgAudit 擴充記錄所有 DDL 和敏感查詢操作

延伸閱讀與資源

← 上一章：複寫與高可用 | 返回專題首頁 | 下一章：常用擴充套件 →

安全性與權限 ​

一、 角色 (Role) ​

建立角色 ​

角色選項 ​

角色繼承 ​

二、 權限管理 ​

權限類型 ​

GRANT 授權 ​

REVOKE 撤銷 ​

預設權限 ​

三、 Row Level Security (RLS) ​

啟用 RLS ​

策略類型 ​

多租戶範例 ​

管理員繞過 RLS ​

四、 pg_hba.conf 連線認證 ​

認證方法 ​

五、 SSL/TLS 加密 ​

產生憑證 ​

設定 PostgreSQL ​

強制 SSL 連線 ​

客戶端連線 ​

六、 密碼管理 ​

密碼加密方式 ​

修改密碼 ​

密碼有效期限 ​

七、 審計日誌 ​

開啟日誌 ​

pgAudit 擴充 ​

八、 最佳實踐 ​

1. 最小權限原則 ​

2. 分離角色 ​

3. 避免使用 PUBLIC ​

4. 定期檢查 ​

總結 ​

進階挑戰 ​

延伸閱讀與資源 ​

安全性與權限

一、角色 (Role)

建立角色

角色選項

角色繼承

二、權限管理

權限類型

GRANT 授權

REVOKE 撤銷

預設權限

三、 Row Level Security (RLS)

啟用 RLS

策略類型

多租戶範例

管理員繞過 RLS

四、 pg_hba.conf 連線認證

認證方法

五、 SSL/TLS 加密

產生憑證

設定 PostgreSQL

強制 SSL 連線

客戶端連線

六、密碼管理

密碼加密方式

修改密碼

密碼有效期限

七、審計日誌

開啟日誌

pgAudit 擴充

八、最佳實踐

1. 最小權限原則

2. 分離角色

3. 避免使用 PUBLIC

4. 定期檢查

總結

進階挑戰

延伸閱讀與資源