PostgreSQL的擴展 credcheck
credcheck 是 PostgreSQL 的一個安全擴展，專門用于強制實施密碼策略和憑證檢查，特別適合需要符合安全合規要求的數據庫環境。
一、擴展概述
1. 主要功能
- 強制密碼復雜度要求
- 防止使用常見弱密碼
- 密碼過期策略實施
- 密碼重復使用檢查
- 登錄失敗嘗試限制
2. 適用場景
- 需要符合 PCI DSS、HIPAA 等安全標準的系統
- 多用戶數據庫環境
- 高安全性要求的應用
二、安裝與配置
1. 安裝
下載網址 https://github.com/HexaCluster/credcheck/tags
-- 從源碼安裝（需PostgreSQL開發包）
cd credcheck
make && make install-- 然后連接到目標數據庫執行
CREATE EXTENSION credcheck;white=# \dxList of installed extensionsName | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------credcheck | 3.0.0 | public | credcheck - postgresql plain text credential checkerpg_bulkload | 3.1.21 | public | pg_bulkload is a high speed data loading utility for PostgreSQLpg_dirtyread | 2 | public | Read dead but unvacuumed rows from tablepg_repack | 1.5.0 | public | Reorganize tables in PostgreSQL databases with minimal lockspg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executedpgstattuple | 1.5 | public | show tuple-level statisticsplpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(7 rows)
2. 基本配置
-- 查看當前配置
white=# select name,setting from pg_settings where name like '%credcheck%';name | setting
--------------------------------------+---------credcheck.auth_delay_ms | 0credcheck.encrypted_password_allowed | offcredcheck.max_auth_failure | 0credcheck.no_password_logging | oncredcheck.password_contain | credcheck.password_contain_username | oncredcheck.password_ignore_case | offcredcheck.password_min_digit | 0credcheck.password_min_length | 1credcheck.password_min_lower | 0credcheck.password_min_repeat | 0credcheck.password_min_special | 0credcheck.password_min_upper | 0credcheck.password_not_contain | credcheck.password_reuse_history | 0credcheck.password_reuse_interval | 0credcheck.password_valid_max | 0credcheck.password_valid_until | 0credcheck.reset_superuser | offcredcheck.username_contain | credcheck.username_contain_password | oncredcheck.username_ignore_case | offcredcheck.username_min_digit | 0credcheck.username_min_length | 1credcheck.username_min_lower | 0credcheck.username_min_repeat | 0credcheck.username_min_special | 0credcheck.username_min_upper | 0credcheck.username_not_contain | credcheck.whitelist | credcheck.whitelist_auth_failure |
(31 rows)
三、核心功能使用
1. 密碼復雜度檢查
-- 創建用戶時自動檢查密碼
CREATE ROLE secure_user WITH LOGIN PASSWORD 'Weak123';-- 會拋出錯誤如：
-- ERROR: password failed dictionary check
-- 或 ERROR: password is too simple
2. 密碼歷史記錄
-- 啟用密碼歷史記錄
ALTER SYSTEM SET credcheck.enable_password_history = on;
SELECT pg_reload_conf();-- 修改密碼時會檢查歷史記錄
ALTER ROLE existing_user WITH PASSWORD 'NewPass123';
-- 如果與最近5次密碼重復會報錯
3. 登錄失敗限制
-- 查看失敗嘗試記錄
SELECT * FROM credcheck.failed_login_attempts;-- 手動解鎖被鎖賬戶
SELECT credcheck.unlock_account('locked_user');
四、高級配置選項
1. 自定義字典檢查
-- 指定自定義字典文件路徑
UPDATE credcheck.pwquality_settings
SET dict_file = '/path/to/custom_wordlist.txt';-- 重新加載配置
SELECT credcheck.reload_settings();
2. 密碼過期策略
-- 設置密碼有效期90天
UPDATE credcheck.pwquality_settings
SET password_max_age = '90 days';-- 強制下次登錄修改密碼
ALTER ROLE critical_user WITH PASSWORD 'TempPass123' VALID UNTIL 'now';
3. 例外配置
-- 對特定角色豁免檢查
INSERT INTO credcheck.pwquality_exceptions (rolname, setting, value)
VALUES ('service_account', 'min_length', '8');
五、監控與維護
1. 監控視圖
-- 查看密碼狀態
SELECT * FROM credcheck.password_status;-- 檢查即將過期的密碼
SELECT rolname, password_age
FROM credcheck.password_status
WHERE password_age > (SELECT password_max_age FROM credcheck.pwquality_settings) * 0.9;
2. 定期維護
-- 清理舊的失敗嘗試記錄
DELETE FROM credcheck.failed_login_attempts
WHERE attempt_time < now() - interval '30 days';-- 更新字典文件后重新加載
SELECT credcheck.reload_dictionary();
六、安全最佳實踐
-
結合pg_hba.conf：
# 在pg_hba.conf中限制連接方式 hostssl all all 0.0.0.0/0 md5 clientcert=1 -
與pgcrypto集成：
-- 存儲加密后的密碼歷史 CREATE TABLE encrypted_password_history AS SELECT rolname, pgp_sym_encrypt(password, 'encryption_key') FROM credcheck.password_history; -
審計日志：
-- 記錄密碼變更事件 ALTER SYSTEM SET log_statement = 'ddl';
七、故障排除
常見問題解決
-
擴展無法加載：
# 檢查PostgreSQL日志 tail -n 50 /var/log/postgresql/postgresql-*.log# 確認.so文件在正確位置 find /usr/lib/postgresql -name "credcheck*" -
策略不生效：
-- 檢查是否沖突的配置 SHOW credcheck.enable_password_check;-- 確認配置已重載 SELECT pg_reload_conf(); -
性能問題：
-- 對大用戶表創建索引 CREATE INDEX ON credcheck.password_history (rolname, change_time);
credcheck 擴展為 PostgreSQL 提供了企業級的密碼策略管理能力，合理配置可以顯著提升數據庫認證安全性，建議結合其他安全措施如 SSL 加密和網絡隔離一起使用。
