PostgreSQL作为世界上最先进的开源关系型数据库，广泛应用于各类关键业务系统。然而，默认配置往往无法满足生产环境的安全需求。本文将从网络配置、身份认证、权限管理、数据加密、审计日志等多个维度，详细介绍PostgreSQL的安全加固最佳实践，帮助您构建一个安全可靠的数据库环境。
一、网络层安全配置
1.1 限制监听地址
问题描述
：PostgreSQL默认监听所有网络接口（
*
），这可能导致数据库暴露在不安全的网络环境中。
解决方案
：通过修改
postgresql.conf
文件中的
listen_addresses
参数，限制数据库只监听特定的可信IP地址。
# 查看当前配置
SHOW listen_addresses;
# 修改配置（仅监听本地回环和内网地址）
listen_addresses = ‘localhost,192.168.1.100’
# 重启服务生效
sudo systemctl restart postgresql
最佳实践
：
生产环境应避免使用
0.0.0.0
或
*
如果数据库仅需本地访问，设置为
localhost
如果需远程访问，指定具体内网IP，避免暴露公网
1.2 配置防火墙规则
问题描述
：即使数据库限制了监听地址，仍需在网络层面限制访问。
解决方案
：使用系统防火墙（如iptables或firewalld）限制访问来源。
# 使用firewalld（CentOS/RHEL）
sudo firewall-cmd –permanent –add-port=5432/tcp
sudo firewall-cmd –reload
# 使用iptables
sudo iptables -A INPUT -p tcp –dport 5432 -s 192.168.1.0/24 -j ACCEPT
sudo iptables -A INPUT -p tcp –dport 5432 -j DROP
1.3 修改默认端口
问题描述
：使用默认端口5432容易被扫描和攻击。
解决方案
：修改
postgresql.conf
中的
port
参数。
# 修改端口为5433
port = 5433
# 重启服务
sudo systemctl restart postgresql
注意
：修改端口后，所有客户端连接都需要更新端口配置。
二、身份认证安全
2.1 强化pg_hba.conf配置
问题描述
：
pg_hba.conf
文件控制客户端认证方式，不当配置可能导致未授权访问。
解决方案
：遵循最小权限原则，精确配置每条规则。
# 查看当前配置
cat /var/lib/pgsql/12/data/pg_hba.conf
# 推荐配置示例
# TYPE DATABASE USER ADDRESS METHOD
# 本地连接必须使用peer或md5
local all all peer
# IPv4本地回环必须使用md5
host all all 127.0.0.1/32 md5
# 仅允许特定网段访问生产数据库
host production app_user 192.168.1.0/24 md5
# 拒绝所有其他连接
host all all 0.0.0.0/0 reject
认证方法选择
：
peer
：适用于本地Unix socket连接，安全但仅限本地
md5
：密码认证，需配合强密码策略
scram-sha-256
：PostgreSQL 10+推荐，比md5更安全
reject
：明确拒绝某些连接
2.2 强密码策略
问题描述
：弱密码是数据库被入侵的主要原因之一。
解决方案
：创建用户时强制使用复杂密码。
— 创建用户并设置强密码
CREATE USER secure_user WITH
PASSWORD ‘ComplexPass123!@#’
VALID UNTIL ‘2024-12-31’;
— 修改密码复杂度要求（需要安装pgcrypto扩展）
CREATE EXTENSION IF NOT EXISTS pgcrypto;
— 创建密码验证函数
CREATE OR REPLACE FUNCTION check_password_strength(password TEXT)
RETURNS BOOLEAN AS $$
BEGIN
— 检查长度至少12位
IF LENGTH(password) < 12 THEN
RETURN FALSE;
END IF;
-- 检查包含大小写字母、数字、特殊字符
IF password !~ '[A-Z]' OR
password !~ '[a-z]' OR
password !~ '[0-9]' OR
password !~ '[!@#$%^&*]' THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT check_password_strength('WeakPass'); -- 返回false
SELECT check_password_strength('StrongPass123!'); -- 返回true
2.3 禁用或重命名默认用户
问题问题
：默认的
postgres
用户权限过高，存在安全风险。
解决方案
：
为日常管理创建专用管理员账户
限制
postgres
用户仅用于维护
-- 创建专用管理员账户
CREATE USER dba_admin WITH
SUPERUSER
LOGIN
PASSWORD 'AdminPass123!@#';
-- 查看当前用户
SELECT current_user;
-- 限制postgres用户（需要在pg_hba.conf中限制）
# 在pg_hba.conf中添加
local all postgres reject
host all postgres 127.0.0.1/32 reject
三、权限管理最佳实践
3.1 角色基础访问控制（RBAC）
问题描述
：直接给用户赋权难以管理，容易产生权限混乱。
解决方案
：采用RBAC模型，通过角色管理权限。
-- 1. 创建角色（不登录）
CREATE ROLE read_only_role;
CREATE ROLE read_write_role;
CREATE ROLE admin_role;
-- 2. 授予角色权限
-- 只读角色
GRANT CONNECT ON DATABASE production TO read_only_role;
GRANT USAGE ON SCHEMA public TO read_only_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_role;
-- 读写角色
GRANT CONNECT ON DATABASE production TO read_write_role;
GRANT USAGE ON SCHEMA public TO read_write_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write_role;
-- 管理员角色
GRANT ALL PRIVILEGES ON DATABASE production TO admin_role;
GRANT ALL PRIVILEGES ON SCHEMA public TO admin_role;
-- 3. 创建用户并分配角色
CREATE USER app_user WITH PASSWORD 'AppPass123!';
GRANT read_write_role TO app_user;
CREATE USER analyst_user WITH PASSWORD 'AnalystPass123!';
GRANT read_only_role TO analyst_user;
-- 4. 验证权限
SELECT * FROM information_schema.table_privileges
WHERE grantee = 'app_user';
3.2 最小权限原则
问题描述
：过度授权是常见安全漏洞。
解决方案
：只为用户分配完成工作所需的最小权限。
-- 错误示例：直接授予所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
-- 正确示例：精确授权
-- 仅授予特定表的特定权限
GRANT SELECT, INSERT ON orders TO app_user;
GRANT SELECT ON customers TO app_user;
GRANT UPDATE (status, updated_at) ON orders TO app_user;
-- 限制用户只能访问特定列
CREATE VIEW user_order_summary AS
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;
GRANT SELECT ON user_order_summary TO analyst_user;
3.3 行级安全（RLS）
问题描述
：需要限制用户只能访问自己数据行的场景。
解决方案
：使用行级安全策略。
-- 启用行级安全
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- 创建策略：用户只能看到自己的订单
CREATE POLICY user_own_orders ON orders
FOR ALL
USING (user_id = current_setting('app.current_user_id')::INTEGER);
-- 创建策略：管理员可以看到所有订单
CREATE POLICY admin_all_orders ON orders
FOR ALL
USING (EXISTS (
SELECT 1 FROM pg_roles
WHERE rolname = current_user AND rolsuper = true
));
-- 测试策略
SET app.current_user_id = '100';
SELECT * FROM orders; -- 只能看到user_id=100的订单
-- 为管理员设置
SET app.current_user_id = '0'; -- 或使用超级用户
SELECT * FROM orders; -- 可以看到所有订单
3.4 视图和存储过程封装
问题描述
：直接暴露表结构存在安全风险。
解决方案
：通过视图和存储过程封装数据访问。
-- 创建安全视图
CREATE VIEW secure_user_data AS
SELECT
user_id,
username,
email,
created_at,
last_login
FROM users
WHERE active = true;
-- 授予视图访问权限
GRANT SELECT ON secure_user_data TO read_only_role;
-- 创建存储过程封装复杂操作
CREATE OR REPLACE FUNCTION transfer_funds(
from_account INTEGER,
to_account INTEGER,
amount NUMERIC
) RETURNS BOOLEAN AS $$
DECLARE
from_balance NUMERIC;
BEGIN
-- 检查余额
SELECT balance INTO from_balance
FROM accounts WHERE account_id = from_account;
IF from_balance < amount THEN
RAISE EXCEPTION 'Insufficient funds';
END IF;
-- 执行转账
UPDATE accounts SET balance = balance - amount
WHERE account_id = from_account;
UPDATE accounts SET balance = balance + amount
WHERE account_id = to_account;
-- 记录日志
INSERT INTO transaction_log (from_account, to_account, amount, timestamp)
VALUES (from_account, to_account, amount, NOW());
RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- 授予执行权限
GRANT EXECUTE ON FUNCTION transfer_funds TO read_write_role;
四、数据加密保护
4.1 传输层加密（SSL/TLS）
问题描述
：明文传输数据可能被中间人攻击窃取。
解决方案
：配置SSL加密连接。
# 1. 生成SSL证书（自签名用于测试）
sudo openssl req -new -x509 -days 365 -nodes -text \
-out /var/lib/pgsql/12/data/server.crt \
-keyout /var/lib/pgsql/12/data/server.key \
-subj "/CN=postgres-server"
# 2. 设置权限
sudo chmod 0600 /var/lib/pgsql/12/data/server.key
sudo chown postgres:postgres /var/lib/pgsql/12/data/server.*
# 3. 修改postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = '' # 如果有CA证书
ssl_ciphers = 'HIGH:!aNULL:!MD5' # 强加密套件
ssl_prefer_server_ciphers = on
# 4. 强制SSL连接（修改pg_hba.conf）
hostssl all all 0.0.0.0/0 md5
# 5. 重启服务
sudo systemctl restart postgresql
# 6. 测试SSL连接
psql "host=192.168.1.100 dbname=production user=app_user sslmode=require"
SSL模式说明
：
disable
：不使用SSL
allow
：如果服务器支持则使用
prefer
：优先使用SSL
require
：必须使用SSL
verify-ca
：验证服务器证书
verify-full
：完整验证（推荐生产环境）
4.2 存储加密
问题描述
：磁盘上的数据文件可能被非法读取。
解决方案
：使用文件系统加密或PostgreSQL扩展。
方案A：文件系统级加密（推荐）
# 使用LUKS加密数据目录
sudo cryptsetup luksFormat /dev/sdb1
sudo cryptsetup luksOpen /dev/sdb1 postgres_encrypted
sudo mkfs.ext4 /dev/mapper/postgres_encrypted
sudo mount /dev/mapper/postgres_encrypted /var/lib/pgsql/12/data
# 自动挂载配置（/etc/crypttab）
postgres_encrypted /dev/sdb1 none luks
# 自动挂载配置（/etc/fstab）
/dev/mapper/postgres_encrypted /var/lib/pgsql/12/data ext4 defaults 0 2
方案B：pgcrypto扩展加密
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- 创建加密表
CREATE TABLE sensitive_data (
id SERIAL PRIMARY KEY,
user_id INTEGER,
ssn_encrypted BYTEA, -- 社会保障号（加密存储）
credit_card_encrypted BYTEA,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 插入加密数据
INSERT INTO sensitive_data (user_id, ssn_encrypted, credit_card_encrypted)
VALUES (
100,
pgp_sym_encrypt('123-45-6789', 'my_secret_key'),
pgp_sym_encrypt('4111-1111-1111-1111', 'my_secret_key')
);
-- 查询加密数据
SELECT
user_id,
pgp_sym_decrypt(ssn_encrypted, 'my_secret_key') as ssn,
pgp_sym_decrypt(credit_card_encrypted, 'my_secret_key') as credit_card
FROM sensitive_data
WHERE user_id = 100;
-- 使用公钥加密（更安全）
-- 生成密钥对
SELECT pgp_key_gen('RSA', 2048, 'user100@company.com', 'passphrase');
-- 使用公钥加密
INSERT INTO sensitive_data (user_id, ssn_encrypted)
VALUES (
101,
pgp_pub_encrypt('987-65-4321', dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----...'))
);
4.3 备份加密
问题描述
：备份文件通常包含完整数据，容易成为攻击目标。
解决方案
：备份时加密。
# 使用openssl加密备份
pg_dump production | openssl enc -aes-256-cbc -salt -out production_backup.sql.enc \
-kfile /path/to/encryption_key
# 解密备份
openssl enc -d -aes-256-cbc -in production_backup.sql.enc \
-kfile /path/to/encryption_key | psql production
# 使用gpg加密
pg_dump production | gpg --cipher-algo AES256 --compress-algo 1 \
--symmetric --output production_backup.sql.gpg
# 解密
gpg -d production_backup.sql.gpg | psql production
五、审计与监控
5.1 启用详细日志
问题描述
：无法追踪数据库操作历史，难以发现安全事件。
解决方案
：配置PostgreSQL日志记录关键操作。
# 修改postgresql.conf
logging_collector = on
log_destination = 'stderr'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all' # 记录所有SQL语句
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_lock_waits = on
log_temp_files = 0 # 记录所有临时文件
# 重启服务
sudo systemctl restart postgresql
5.2 使用pgAudit扩展
问题描述
：标准日志无法满足合规性要求（如SOX、PCI-DSS）。
解决方案
：安装pgAudit扩展进行细粒度审计。
# 安装pgAudit（需要root权限）
# Ubuntu/Debian
sudo apt-get install postgresql-12-pgaudit
# CentOS/RHEL
sudo yum install postgresql12-pgaudit
# 修改postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all, -misc'
pgaudit.log_catalog = on
pgaudit.log_parameter = on
pgaudit.log_statement_once = off
pgaudit.log_level = log
# 重启服务
sudo systemctl restart postgresql
-- 在数据库中启用审计
CREATE EXTENSION IF NOT EXISTS pgaudit;
-- 设置审计角色
ALTER SYSTEM SET pgaudit.role = 'auditor';
SELECT pg_reload_conf();
-- 创建审计角色
CREATE ROLE auditor;
GRANT auditor TO dba_admin;
-- 审计特定表的操作
ALTER TABLE orders SET (pgaudit.log = 'all');
-- 查看审计日志（在postgresql日志中）
-- 示例输出：
-- LOG: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.orders,
-- "SELECT * FROM orders WHERE user_id = 100",
5.3 实时监控与告警
问题描述
：被动查看日志无法及时发现攻击。
解决方案
：使用工具进行实时监控。
# 使用pg_stat_statements监控慢查询
# 修改postgresql.conf
shared_preload_libraries = ‘pg_stat_statements’
pg_stat_statements.max = 1000
pg_stat_statements.track = all
# 重启服务
sudo systemctl restart postgresql
— 在数据库中启用
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
— 查看最慢查询
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
— 监控异常登录
SELECT
datname,
usename,
client_addr,
backend_start,
state
FROM pg_stat_activity
WHERE state = ‘idle in transaction’
AND backend_start < NOW() - INTERVAL '1 hour';
使用外部监控工具
：
Prometheus + postgres_exporter
：指标监控
WAL-G + pgBackRest
：备份与恢复监控
ELK Stack
：日志分析
Pganalyze
：性能与安全分析
六、常见漏洞与攻击防御
6.1 SQL注入防御
问题描述
：SQL注入是最常见的Web攻击方式。
解决方案
：使用参数化查询，避免字符串拼接。
# 错误示例：SQL注入风险
user_input = "admin' OR '1'='1"
query = f"SELECT * FROM users WHERE username = '{user_input}' AND password = 'xxx'"
# 执行后：SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = 'xxx'
# 正确示例：使用参数化查询（Python psycopg2）
import psycopg2
conn = psycopg2.connect("dbname=production user=app_user")
cur = conn.cursor()
# 使用占位符
user_input = "admin' OR '1'='1"
cur.execute("SELECT * FROM users WHERE username = %s AND password = %s",
(user_input, password_hash))
# 批量插入
data = [('user1', 'pass1'), ('user2', 'pass2')]
cur.executemany("INSERT INTO users (username, password) VALUES (%s, %s)", data)
conn.commit()
cur.close()
conn.close()
存储过程中的SQL注入防御
：
-- 错误：动态SQL拼接
CREATE OR REPLACE FUNCTION insecure_search(search_term TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
RETURN QUERY EXECUTE 'SELECT id, name FROM users WHERE name = ''' || search_term || '''';
END;
$$ LANGUAGE plpgsql;
-- 正确：使用format()函数和USING子句
CREATE OR REPLACE FUNCTION secure_search(search_term TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
RETURN QUERY EXECUTE format('SELECT id, name FROM users WHERE name = %L', search_term);
END;
$$ LANGUAGE plpgsql;
-- 或使用参数化
CREATE OR REPLACE FUNCTION secure_search_v2(search_term TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
RETURN QUERY SELECT id, name FROM users WHERE name = search_term;
END;
$$ LANGUAGE plpgsql;
6.2 权限提升防御
问题描述
：攻击者可能利用漏洞获取更高权限。
解决方案
：
禁用危险函数
限制超级用户
使用SECURITY DEFINER时谨慎
-- 禁用危险函数（需要超级用户权限）
REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_write_file(text, text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM PUBLIC;
-- 限制超级用户数量
SELECT rolname, rolsuper FROM pg_roles WHERE rolsuper = true;
-- 创建受限管理员
CREATE ROLE limited_admin WITH
CREATEDB
CREATEROLE
REPLICATION
BYPASSRLS
LOGIN
PASSWORD 'LimitedAdmin123!';
-- 审查SECURITY DEFINER函数
SELECT
proname,
prosecdef,
rolname as owner
FROM pg_proc p
JOIN pg_roles r ON p.proowner = r.oid
WHERE prosecdef = true;
-- 重新定义函数时避免不必要的权限
CREATE OR REPLACE FUNCTION safe_function()
RETURNS void AS $$
BEGIN
-- 函数内部操作
INSERT INTO logs (action) VALUES ('safe_action');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- 更好的做法：使用调用者权限
CREATE OR REPLACE FUNCTION safer_function()
RETURNS void AS $$
BEGIN
-- 函数内部操作
INSERT INTO logs (action) VALUES ('safe_action');
END;
$$ LANGUAGE plpgsql SECURITY INVOKER; -- 使用调用者权限
6.3 暴力破解防御
问题描述
：攻击者尝试猜测密码。
解决方案
：
使用强密码
限制登录尝试
使用外部认证
# 使用fail2ban防止暴力破解
# 安装fail2ban
sudo apt-get install fail2ban
# 创建PostgreSQL jail配置
sudo tee /etc/fail2ban/jail.d/postgresql.conf <
ignoreregex =
EOF
# 重启fail2ban
sudo systemctl restart fail2ban
6.4 数据泄露防御
问题描述
：敏感数据被非法导出。
解决方案
：
限制导出权限
使用数据脱敏
监控导出操作
— 限制导出权限
REVOKE EXECUTE ON FUNCTION pg_dump(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_dumpall() FROM PUBLIC;
— 创建数据脱敏视图
CREATE VIEW masked_users AS
SELECT
user_id,
username,
— 脱敏邮箱：显示前缀和域名，隐藏中间
CONCAT(
LEFT(email, 3),
‘***@’,
RIGHT(email, LENGTH(email) – POSITION(‘@’ IN email))
) as masked_email,
— 脱敏电话：显示后4位
CONCAT(‘***-***-‘, RIGHT(phone, 4)) as masked_phone,
created_at
FROM users;
GRANT SELECT ON masked_users TO read_only_role;
— 监控大查询
CREATE OR REPLACE FUNCTION log_large_export()
RETURNS event_trigger AS $$
BEGIN
IF current_setting(‘application_name’) = ‘pg_dump’ THEN
RAISE LOG ‘Large export detected by user: %’, current_user;
— 发送告警（需要配置外部系统）
PERFORM pg_notify(‘security_alerts’,
format(‘Export detected: user=%s, time=%s’,
current_user, NOW()));
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER large_export_trigger
ON sql_drop
WHEN TAG IN (‘COPY’, ‘SELECT INTO’)
EXECUTE FUNCTION log_large_export();
七、备份与恢复安全
7.1 安全备份策略
问题描述
：备份文件未加密或存储不当。
解决方案
：自动化加密备份。
#!/bin/bash
# 安全备份脚本：/usr/local/bin/secure_backup.sh
BACKUP_DIR=”/backup/postgresql”
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_NAME=”production_${DATE}.sql.gz”
ENCRYPTED_NAME=”${BACKUP_NAME}.gpg”
GPG_KEY=”security-team@company.com”
# 创建备份目录
mkdir -p $BACKUP_DIR
# 执行备份并压缩
pg_dump -h localhost -U dba_admin -d production | \
gzip > ${BACKUP_DIR}/${BACKUP_NAME}
# 使用GPG加密（对称加密）
gpg –cipher-algo AES256 –compress-algo 1 \
–symmetric –batch –passphrase-file /path/to/backup_passphrase \
–output ${BACKUP_DIR}/${ENCRYPTED_NAME} \
${BACKUP_DIR}/${BACKUP_NAME}
# 删除临时未加密文件
shred -u ${BACKUP_DIR}/${BACKUP_NAME}
# 传输到安全存储（如S3）
aws s3 cp ${BACKUP_DIR}/${ENCRYPTED_NAME} \
s3://company-backups/postgresql/ \
–sse aws:kms –sse-kms-key-id alias/postgres-backup
# 保留最近7天的备份
find $BACKUP_DIR -name “*.gpg” -mtime +7 -delete
# 记录日志
echo “[$(date)] Backup completed: ${ENCRYPTED_NAME}” >> /var/log/postgresql_backup.log
7.2 恢复安全
问题描述
：恢复过程可能暴露数据或破坏现有数据。
解决方案
：在隔离环境恢复验证。
# 1. 创建隔离恢复环境
sudo -u postgres createdb recovery_test
# 2. 解密并恢复到测试环境
gpg -d /backup/postgresql/production_20240101.sql.gz.gpg | \
gunzip | psql -U postgres -d recovery_test
# 3. 验证数据完整性
psql -U postgres -d recovery_test -c ”
SELECT
COUNT(*) as total_records,
MIN(created_at) as earliest,
MAX(created_at) as latest
FROM orders;
”
# 4. 检查是否有异常数据
psql -U postgres -d recovery_test -c ”
SELECT * FROM users
WHERE username LIKE ‘%admin%’ OR email LIKE ‘%@evil.com%’;
”
八、定期安全审查
8.1 安全检查清单
— 1. 检查未加密连接
SELECT
count(*) as total_connections,
count(*) FILTER (WHERE ssl = ‘t’) as ssl_connections,
count(*) FILTER (WHERE ssl = ‘f’) as non_ssl_connections
FROM pg_stat_ssl;
— 2. 检查密码复杂度
SELECT
usename,
passwd_age_days,
CASE
WHEN passwd_age_days > 90 THEN ‘EXPIRED’
WHEN passwd_age_days > 70 THEN ‘NEAR_EXPIRED’
ELSE ‘OK’
END as status
FROM (
SELECT
usename,
EXTRACT(DAYS FROM (NOW() – passwd_changed)) as passwd_age_days
FROM pg_shadow
) AS ages;
— 3. 检查权限过大用户
SELECT
grantee,
string_agg(privilege_type, ‘, ‘) as privileges
FROM information_schema.role_table_grants
WHERE table_schema = ‘public’
GROUP BY grantee
HAVING COUNT(*) > 10;
— 4. 检查未使用的索引（可能为残留对象）
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;
— 5. 检查未使用的角色
SELECT
rolname,
rolvaliduntil,
rolcanlogin,
rolconnlimit
FROM pg_roles
WHERE rolcanlogin = true
AND rolname NOT IN (‘postgres’, ‘dba_admin’)
AND NOT EXISTS (
SELECT 1 FROM pg_stat_activity
WHERE usename = rolname
);
— 6. 检查未加密的敏感列
SELECT
table_name,
column_name,
data_type
FROM information_schema.columns
WHERE table_schema = ‘public’
AND (
column_name LIKE ‘%ssn%’ OR
column_name LIKE ‘%password%’ OR
column_name LIKE ‘%credit%’ OR
column_name LIKE ‘%card%’
)
AND table_name NOT IN (
SELECT table_name FROM information_schema.views
WHERE table_schema = ‘public’
);
8.2 自动化安全扫描
#!/bin/bash
# PostgreSQL安全扫描脚本
DB_HOST=”localhost”
DB_USER=”dba_admin”
DB_NAME=”postgres”
# 执行安全检查
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -f security_audit.sql > audit_report.txt
# 发送报告
mail -s “PostgreSQL Security Audit Report” security-team@company.com < audit_report.txt
# 检查错误日志中的异常
grep -i "FATAL\|ERROR\|WARNING" /var/log/postgresql/postgresql-12-main.log | \
grep -v "password authentication failed" | \
tail -20 > error_summary.txt
九、总结与最佳实践清单
9.1 核心安全原则
最小权限原则
：只授予完成工作所需的最小权限
纵深防御
：多层安全措施（网络、认证、应用、数据）
默认拒绝
：默认情况下拒绝所有访问，明确授权允许
持续监控
：实时监控异常行为，及时响应
定期审查
：定期审查权限和配置
9.2 生产环境配置清单
# ✅ 网络层
– listen_addresses = ‘localhost,10.0.0.100’ # 限制监听
– port = 5433 # 修改默认端口
– 配置防火墙限制访问源IP
# ✅ 认证层
– pg_hba.conf使用md5或scram-sha-256
– 强制SSL连接（hostssl）
– 强密码策略（12位+，复杂字符）
– 禁用或重命名postgres用户
# ✅ 权限层
– 使用RBAC模型
– 实施最小权限原则
– 启用行级安全（RLS）
– 使用视图封装敏感数据
# ✅ 加密层
– SSL加密传输
– 文件系统或应用层加密存储
– 备份文件加密
# ✅ 审计层
– 启用详细日志
– 安装pgAudit扩展
– 实时监控告警
– 定期安全扫描
# ✅ 维护层
– 自动化加密备份
– 定期恢复测试
– 及时更新补丁
– 定期审查权限
9.3 常见攻击防御速查表
攻击类型
防御措施
SQL注入
参数化查询、输入验证、最小权限
暴力破解
强密码、fail2ban、限制登录尝试
权限提升
禁用危险函数、限制超级用户、审查SECURITY DEFINER
数据泄露
数据脱敏、限制导出权限、监控大查询
中间人攻击
强制SSL、证书验证
数据窃取
传输加密、存储加密、备份加密
通过实施上述安全加固措施，可以显著提升PostgreSQL数据库的安全性，有效防御常见攻击手段。安全是一个持续的过程，建议定期审查和更新安全策略，以应对新的威胁和挑战。
# PostgreSQL安全加固最佳实践 从配置到权限管理全方位指南 如何避免常见漏洞与攻击
PostgreSQL作为世界上最先进的开源关系型数据库，广泛应用于各类关键业务系统。然而，默认配置往往无法满足生产环境的安全需求。本文将从网络配置、身份认证、权限管理、数据加密、审计日志等多个维度，详细介绍PostgreSQL的安全加固最佳实践，帮助您构建一个安全可靠的数据库环境。
一、网络层安全配置
1.1 限制监听地址
问题描述
：PostgreSQL默认监听所有网络接口（
*
），这可能导致数据库暴露在不安全的网络环境中。
解决方案
：通过修改
postgresql.conf
文件中的
listen_addresses
参数，限制数据库只监听特定的可信IP地址。
# 查看当前配置
SHOW listen_addresses;
# 修改配置（仅监听本地回环和内网地址）
listen_addresses = ‘localhost,192.168.1.100’
# 重启服务生效
sudo systemctl restart postgresql
最佳实践
：
生产环境应避免使用
0.0.0.0
或
*
如果数据库仅需本地访问，设置为
localhost
如果需远程访问，指定具体内网IP，避免暴露公网
1.2 配置防火墙规则
问题描述
：即使数据库限制了监听地址，仍需在网络层面限制访问。
解决方案
：使用系统防火墙（如iptables或firewalld）限制访问来源。
# 使用firewalld（CentOS/RHEL）
sudo firewall-cmd –permanent –add-port=5432/tcp
sudo firewall-cmd –reload
# 使用iptables
sudo iptables -A INPUT -p tcp –dport 5432 -s 192.168.1.0/24 -j ACCEPT
sudo iptables -A INPUT -p tcp –dport 5432 -j DROP
1.3 修改默认端口
问题描述
：使用默认端口5432容易被扫描和攻击。
解决方案
：修改
postgresql.conf
中的
port
参数。
# 修改端口为5433
port = 5433
# 重启服务
sudo systemctl restart postgresql
注意
：修改端口后，所有客户端连接都需要更新端口配置。
二、身份认证安全
2.1 强化pg_hba.conf配置
问题描述
：
pg_hba.conf
文件控制客户端认证方式，不当配置可能导致未授权访问。
解决方案
：遵循最小权限原则，精确配置每条规则。
# 查看当前配置
cat /var/lib/pgsql/12/data/pg_hba.conf
# 推荐配置示例
# TYPE DATABASE USER ADDRESS METHOD
# 本地连接必须使用peer或md5
local all all peer
# IPv4本地回环必须使用md5
host all all 127.0.0.1/32 md5
# 仅允许特定网段访问生产数据库
host production app_user 192.168.1.0/24 md5
# 拒绝所有其他连接
host all all 0.0.0.0/0 reject
认证方法选择
：
peer
：适用于本地Unix socket连接，安全但仅限本地
md5
：密码认证，需配合强密码策略
scram-sha-256
：PostgreSQL 10+推荐，比md5更安全
reject
：明确拒绝某些连接
2.2 强密码策略
问题描述
：弱密码是数据库被入侵的主要原因之一。
解决方案
：创建用户时强制使用复杂密码。
— 创建用户并设置强密码
CREATE USER secure_user WITH
PASSWORD ‘ComplexPass123!@#’
VALID UNTIL ‘2024-12-31’;
— 修改密码复杂度要求（需要安装pgcrypto扩展）
CREATE EXTENSION IF NOT EXISTS pgcrypto;
— 创建密码验证函数
CREATE OR REPLACE FUNCTION check_password_strength(password TEXT)
RETURNS BOOLEAN AS $$
BEGIN
— 检查长度至少12位
IF LENGTH(password) < 12 THEN
RETURN FALSE;
END IF;
-- 检查包含大小写字母、数字、特殊字符
IF password !~ '[A-Z]' OR
password !~ '[a-z]' OR
password !~ '[0-9]' OR
password !~ '[!@#$%^&*]' THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT check_password_strength('WeakPass'); -- 返回false
SELECT check_password_strength('StrongPass123!'); -- 返回true
2.3 禁用或重命名默认用户
问题问题
：默认的
postgres
用户权限过高，存在安全风险。
解决方案
：
为日常管理创建专用管理员账户
限制
postgres
用户仅用于维护
-- 创建专用管理员账户
CREATE USER dba_admin WITH
SUPERUSER
LOGIN
PASSWORD 'AdminPass123!@#';
-- 查看当前用户
SELECT current_user;
-- 限制postgres用户（需要在pg_hba.conf中限制）
# 在pg_hba.conf中添加
local all postgres reject
host all postgres 127.0.0.1/32 reject
三、权限管理最佳实践
3.1 角色基础访问控制（RBAC）
问题描述
：直接给用户赋权难以管理，容易产生权限混乱。
解决方案
：采用RBAC模型，通过角色管理权限。
-- 1. 创建角色（不登录）
CREATE ROLE read_only_role;
CREATE ROLE read_write_role;
CREATE ROLE admin_role;
-- 2. 授予角色权限
-- 只读角色
GRANT CONNECT ON DATABASE production TO read_only_role;
GRANT USAGE ON SCHEMA public TO read_only_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_role;
-- 读写角色
GRANT CONNECT ON DATABASE production TO read_write_role;
GRANT USAGE ON SCHEMA public TO read_write_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write_role;
-- 管理员角色
GRANT ALL PRIVILEGES ON DATABASE production TO admin_role;
GRANT ALL PRIVILEGES ON SCHEMA public TO admin_role;
-- 3. 创建用户并分配角色
CREATE USER app_user WITH PASSWORD 'AppPass123!';
GRANT read_write_role TO app_user;
CREATE USER analyst_user WITH PASSWORD 'AnalystPass123!';
GRANT read_only_role TO analyst_user;
-- 4. 验证权限
SELECT * FROM information_schema.table_privileges
WHERE grantee = 'app_user';
3.2 最小权限原则
问题描述
：过度授权是常见安全漏洞。
解决方案
：只为用户分配完成工作所需的最小权限。
-- 错误示例：直接授予所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
-- 正确示例：精确授权
-- 仅授予特定表的特定权限
GRANT SELECT, INSERT ON orders TO app_user;
GRANT SELECT ON customers TO app_user;
GRANT UPDATE (status, updated_at) ON orders TO app_user;
-- 限制用户只能访问特定列
CREATE VIEW user_order_summary AS
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;
GRANT SELECT ON user_order_summary TO analyst_user;
3.3 行级安全（RLS）
问题描述
：需要限制用户只能访问自己数据行的场景。
解决方案
：使用行级安全策略。
-- 启用行级安全
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- 创建策略：用户只能看到自己的订单
CREATE POLICY user_own_orders ON orders
FOR ALL
USING (user_id = current_setting('app.current_user_id')::INTEGER);
-- 创建策略：管理员可以看到所有订单
CREATE POLICY admin_all_orders ON orders
FOR ALL
USING (EXISTS (
SELECT 1 FROM pg_roles
WHERE rolname = current_user AND rolsuper = true
));
-- 测试策略
SET app.current_user_id = '100';
SELECT * FROM orders; -- 只能看到user_id=100的订单
-- 为管理员设置
SET app.current_user_id = '0'; -- 或使用超级用户
SELECT * FROM orders; -- 可以看到所有订单
3.4 视图和存储过程封装
问题描述
：直接暴露表结构存在安全风险。
解决方案
：通过视图和存储过程封装数据访问。
-- 创建安全视图
CREATE VIEW secure_user_data AS
SELECT
user_id,
username,
email,
created_at,
last_login
FROM users
WHERE active = true;
-- 授予视图访问权限
GRANT SELECT ON secure_user_data TO read_only_role;
-- 创建存储过程封装复杂操作
CREATE OR REPLACE FUNCTION transfer_funds(
from_account INTEGER,
to_account INTEGER,
amount NUMERIC
) RETURNS BOOLEAN AS $$
DECLARE
from_balance NUMERIC;
BEGIN
-- 检查余额
SELECT balance INTO from_balance
FROM accounts WHERE account_id = from_account;
IF from_balance < amount THEN
RAISE EXCEPTION 'Insufficient funds';
END IF;
-- 执行转账
UPDATE accounts SET balance = balance - amount
WHERE account_id = from_account;
UPDATE accounts SET balance = balance + amount
WHERE account_id = to_account;
-- 记录日志
INSERT INTO transaction_log (from_account, to_account, amount, timestamp)
VALUES (from_account, to_account, amount, NOW());
RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- 授予执行权限
GRANT EXECUTE ON FUNCTION transfer_funds TO read_write_role;
四、数据加密保护
4.1 传输层加密（SSL/TLS）
问题描述
：明文传输数据可能被中间人攻击窃取。
解决方案
：配置SSL加密连接。
# 1. 生成SSL证书（自签名用于测试）
sudo openssl req -new -x509 -days 365 -nodes -text \
-out /var/lib/pgsql/12/data/server.crt \
-keyout /var/lib/pgsql/12/data/server.key \
-subj "/CN=postgres-server"
# 2. 设置权限
sudo chmod 0600 /var/lib/pgsql/12/data/server.key
sudo chown postgres:postgres /var/lib/pgsql/12/data/server.*
# 3. 修改postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = '' # 如果有CA证书
ssl_ciphers = 'HIGH:!aNULL:!MD5' # 强加密套件
ssl_prefer_server_ciphers = on
# 4. 强制SSL连接（修改pg_hba.conf）
hostssl all all 0.0.0.0/0 md5
# 5. 重启服务
sudo systemctl restart postgresql
# 6. 测试SSL连接
psql "host=192.168.1.100 dbname=production user=app_user sslmode=require"
SSL模式说明
：
disable
：不使用SSL
allow
：如果服务器支持则使用
prefer
：优先使用SSL
require
：必须使用SSL
verify-ca
：验证服务器证书
verify-full
：完整验证（推荐生产环境）
4.2 存储加密
问题描述
：磁盘上的数据文件可能被非法读取。
解决方案
：使用文件系统加密或PostgreSQL扩展。
方案A：文件系统级加密（推荐）
# 使用LUKS加密数据目录
sudo cryptsetup luksFormat /dev/sdb1
sudo cryptsetup luksOpen /dev/sdb1 postgres_encrypted
sudo mkfs.ext4 /dev/mapper/postgres_encrypted
sudo mount /dev/mapper/postgres_encrypted /var/lib/pgsql/12/data
# 自动挂载配置（/etc/crypttab）
postgres_encrypted /dev/sdb1 none luks
# 自动挂载配置（/etc/fstab）
/dev/mapper/postgres_encrypted /var/lib/pgsql/12/data ext4 defaults 0 2
方案B：pgcrypto扩展加密
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- 创建加密表
CREATE TABLE sensitive_data (
id SERIAL PRIMARY KEY,
user_id INTEGER,
ssn_encrypted BYTEA, -- 社会保障号（加密存储）
credit_card_encrypted BYTEA,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 插入加密数据
INSERT INTO sensitive_data (user_id, ssn_encrypted, credit_card_encrypted)
VALUES (
100,
pgp_sym_encrypt('123-45-6789', 'my_secret_key'),
pgp_sym_encrypt('4111-1111-1111-1111', 'my_secret_key')
);
-- 查询加密数据
SELECT
user_id,
pgp_sym_decrypt(ssn_encrypted, 'my_secret_key') as ssn,
pgp_sym_decrypt(credit_card_encrypted, 'my_secret_key') as credit_card
FROM sensitive_data
WHERE user_id = 100;
-- 使用公钥加密（更安全）
-- 生成密钥对
SELECT pgp_key_gen('RSA', 2048, 'user100@company.com', 'passphrase');
-- 使用公钥加密
INSERT INTO sensitive_data (user_id, ssn_encrypted)
VALUES (
101,
pgp_pub_encrypt('987-65-4321', dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----...'))
);
4.3 备份加密
问题问题
：备份文件通常包含完整数据，容易成为攻击目标。
解决方案
：备份时加密。
# 使用openssl加密备份
pg_dump production | openssl enc -aes-256-cbc -salt -out production_backup.sql.enc \
-kfile /path/to/encryption_key
# 解密备份
openssl enc -d -aes-256-cbc -in production_backup.sql.enc \
-kfile /path/to/encryption_key | psql production
# 使用gpg加密
pg_dump production | gpg --cipher-algo AES256 --compress-algo 1 \
--symmetric --output production_backup.sql.gpg
# 解密
gpg -d production_backup.sql.gpg | psql production
五、审计与监控
5.1 启用详细日志
问题描述
：无法追踪数据库操作历史，难以发现安全事件。
解决方案
：配置PostgreSQL日志记录关键操作。
# 修改postgresql.conf
logging_collector = on
log_destination = 'stderr'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all' # 记录所有SQL语句
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_lock_waits = on
log_temp_files = 0 # 记录所有临时文件
# 重启服务
sudo systemctl restart postgresql
5.2 使用pgAudit扩展
问题描述
：标准日志无法满足合规性要求（如SOX、PCI-DSS）。
解决方案
：安装pgAudit扩展进行细粒度审计。
# 安装pgAudit（需要root权限）
# Ubuntu/Debian
sudo apt-get install postgresql-12-pgaudit
# CentOS/RHEL
sudo yum install postgresql12-pgaudit
# 修改postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all, -misc'
pgaudit.log_catalog = on
pgaudit.log_parameter = on
pgaudit.log_statement_once = off
pgaudit.log_level = log
# 重启服务
sudo systemctl restart postgresql
-- 在数据库中启用审计
CREATE EXTENSION IF NOT EXISTS pgaudit;
-- 设置审计角色
ALTER SYSTEM SET pgaudit.role = 'auditor';
SELECT pg_reload_conf();
-- 创建审计角色
CREATE ROLE auditor;
GRANT auditor TO dba_admin;
-- 审计特定表的操作
ALTER TABLE orders SET (pgaudit.log = 'all');
-- 查看审计日志（在postgresql日志中）
-- 示例输出：
-- LOG: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.orders,
-- "SELECT * FROM orders WHERE user_id = 100",
5.3 实时监控与告警
问题描述
：被动查看日志无法及时发现攻击。
解决方案
：使用工具进行实时监控。
# 使用pg_stat_statements监控慢查询
# 修改postgresql.conf
shared_preload_libraries = ‘pg_stat_statements’
pg_stat_statements.max = 1000
pg_stat_statements.track = all
# 重启服务
sudo systemctl restart postgresql
— 在数据库中启用
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
— 查看最慢查询
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
— 监控异常登录
SELECT
datname,
usename,
client_addr,
backend_start,
state
FROM pg_stat_activity
WHERE state = ‘idle in transaction’
AND backend_start < NOW() - INTERVAL '1 hour';
使用外部监控工具
：
Prometheus + postgres_exporter
：指标监控
WAL-G + pgBackRest
：备份与恢复监控
ELK Stack
：日志分析
Pganalyze
：性能与安全分析
六、常见漏洞与攻击防御
6.1 SQL注入防御
问题描述
：SQL注入是最常见的Web攻击方式。
解决方案
：使用参数化查询，避免字符串拼接。
# 错误示例：SQL注入风险
user_input = "admin' OR '1'='1"
query = f"SELECT * FROM users WHERE username = '{user_input}' AND password = 'xxx'"
# 执行后：SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = 'xxx'
# 正确示例：使用参数化查询（Python psycopg2）
import psycopg2
conn = psycopg2.connect("dbname=production user=app_user")
cur = conn.cursor()
# 使用占位符
user_input = "admin' OR '1'='1"
cur.execute("SELECT * FROM users WHERE username = %s AND password = %s",
(user_input, password_hash))
# 批量插入
data = [('user1', 'pass1'), ('user2', 'pass2')]
cur.executemany("INSERT INTO users (username, password) VALUES (%s, %s)", data)
conn.commit()
cur.close()
conn.close()
存储过程中的SQL注入防御
：
-- 错误：动态SQL拼接
CREATE OR REPLACE FUNCTION insecure_search(search_term TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
RETURN QUERY EXECUTE 'SELECT id, name FROM users WHERE name = ''' || search_term || '''';
END;
$$ LANGUAGE plpgsql;
-- 正确：使用format()函数和USING子句
CREATE OR REPLACE FUNCTION secure_search(search_term TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
RETURN QUERY EXECUTE format('SELECT id, name FROM users WHERE name = %L', search_term);
END;
$$ LANGUAGE plpgsql;
-- 或使用参数化
CREATE OR REPLACE FUNCTION secure_search_v2(search_term TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
RETURN QUERY SELECT id, name FROM users WHERE name = search_term;
END;
$$ LANGUAGE plpgsql;
6.2 权限提升防御
问题描述
：攻击者可能利用漏洞获取更高权限。
解决方案
：
禁用危险函数
限制超级用户
使用SECURITY DEFINER时谨慎
-- 禁用危险函数（需要超级用户权限）
REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_write_file(text, text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM PUBLIC;
-- 限制超级用户数量
SELECT rolname, rolsuper FROM pg_roles WHERE rolsuper = true;
-- 创建受限管理员
CREATE ROLE limited_admin WITH
CREATEDB
CREATEROLE
REPLICATION
BYPASSRLS
LOGIN
PASSWORD 'LimitedAdmin123!';
-- 审查SECURITY DEFINER函数
SELECT
proname,
prosecdef,
rolname as owner
FROM pg_proc p
JOIN pg_roles r ON p.proowner = r.oid
WHERE prosecdef = true;
-- 重新定义函数时避免不必要的权限
CREATE OR REPLACE FUNCTION safe_function()
RETURNS void AS $$
BEGIN
-- 函数内部操作
INSERT INTO logs (action) VALUES ('safe_action');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- 更好的做法：使用调用者权限
CREATE OR REPLACE FUNCTION safer_function()
RETURNS void AS $$
BEGIN
-- 函数内部操作
INSERT INTO logs (action) VALUES ('safe_action');
END;
$$ LANGUAGE plpgsql SECURITY INVOKER; -- 使用调用者权限
6.3 暴力破解防御
问题描述
：攻击者尝试猜测密码。
解决方案
：
使用强密码
限制登录尝试
使用外部认证
# 使用fail2ban防止暴力破解
# 安装fail2ban
sudo apt-get install fail2ban
# 创建PostgreSQL jail配置
sudo tee /etc/fail2ban/jail.d/postgresql.conf <
ignoreregex =
EOF
# 重启fail2ban
sudo systemctl restart fail2ban
6.4 数据泄露防御
问题描述
：敏感数据被非法导出。
解决方案
：
限制导出权限
使用数据脱敏
监控导出操作
— 限制导出权限
REVOKE EXECUTE ON FUNCTION pg_dump(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_dumpall() FROM PUBLIC;
— 创建数据脱敏视图
CREATE VIEW masked_users AS
SELECT
user_id,
username,
— 脱敏邮箱：显示前缀和域名，隐藏中间
CONCAT(
LEFT(email, 3),
‘***@’,
RIGHT(email, LENGTH(email) – POSITION(‘@’ IN email))
) as masked_email,
— 脱敏电话：显示后4位
CONCAT(‘***-***-‘, RIGHT(phone, 4)) as masked_phone,
created_at
FROM users;
GRANT SELECT ON masked_users TO read_only_role;
— 监控大查询
CREATE OR REPLACE FUNCTION log_large_export()
RETURNS event_trigger AS $$
BEGIN
IF current_setting(‘application_name’) = ‘pg_dump’ THEN
RAISE LOG ‘Large export detected by user: %’, current_user;
— 发送告警（需要配置外部系统）
PERFORM pg_notify(‘security_alerts’,
format(‘Export detected: user=%s, time=%s’,
current_user, NOW()));
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER large_export_trigger
ON sql_drop
WHEN TAG IN (‘COPY’, ‘SELECT INTO’)
EXECUTE FUNCTION log_large_export();
七、备份与恢复安全
7.1 安全备份策略
问题描述
：备份文件未加密或存储不当。
解决方案
：自动化加密备份。
#!/bin/bash
# 安全备份脚本：/usr/local/bin/secure_backup.sh
BACKUP_DIR=”/backup/postgresql”
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_NAME=”production_${DATE}.sql.gz”
ENCRYPTED_NAME=”${BACKUP_NAME}.gpg”
GPG_KEY=”security-team@company.com”
# 创建备份目录
mkdir -p $BACKUP_DIR
# 执行备份并压缩
pg_dump -h localhost -U dba_admin -d production | \
gzip > ${BACKUP_DIR}/${BACKUP_NAME}
# 使用GPG加密（对称加密）
gpg –cipher-algo AES256 –compress-algo 1 \
–symmetric –batch –passphrase-file /path/to/backup_passphrase \
–output ${BACKUP_DIR}/${ENCRYPTED_NAME} \
${BACKUP_DIR}/${BACKUP_NAME}
# 删除临时未加密文件
shred -u ${BACKUP_DIR}/${BACKUP_NAME}
# 传输到安全存储（如S3）
aws s3 cp ${BACKUP_DIR}/${ENCRYPTED_NAME} \
s3://company-backups/postgresql/ \
–sse aws:kms –sse-kms-key-id alias/postgres-backup
# 保留最近7天的备份
find $BACKUP_DIR -name “*.gpg” -mtime +7 -delete
# 记录日志
echo “[$(date)] Backup completed: ${ENCRYPTED_NAME}” >> /var/log/postgresql_backup.log
7.2 恢复安全
问题描述
：恢复过程可能暴露数据或破坏现有数据。
解决方案
：在隔离环境恢复验证。
# 1. 创建隔离恢复环境
sudo -u postgres createdb recovery_test
# 2. 解密并恢复到测试环境
gpg -d /backup/postgresql/production_20240101.sql.gz.gpg | \
gunzip | psql -U postgres -d recovery_test
# 3. 验证数据完整性
psql -U postgres -d recovery_test -c ”
SELECT
COUNT(*) as total_records,
MIN(created_at) as earliest,
MAX(created_at) as latest
FROM orders;
”
# 4. 检查是否有异常数据
psql -U postgres -d recovery_test -c ”
SELECT * FROM users
WHERE username LIKE ‘%admin%’ OR email LIKE ‘%@evil.com%’;
”
八、定期安全审查
8.1 安全检查清单
— 1. 检查未加密连接
SELECT
count(*) as total_connections,
count(*) FILTER (WHERE ssl = ‘t’) as ssl_connections,
count(*) FILTER (WHERE ssl = ‘f’) as non_ssl_connections
FROM pg_stat_ssl;
— 2. 检查密码复杂度
SELECT
usename,
passwd_age_days,
CASE
WHEN passwd_age_days > 90 THEN ‘EXPIRED’
WHEN passwd_age_days > 70 THEN ‘NEAR_EXPIRED’
ELSE ‘OK’
END as status
FROM (
SELECT
usename,
EXTRACT(DAYS FROM (NOW() – passwd_changed)) as passwd_age_days
FROM pg_shadow
) AS ages;
— 3. 检查权限过大用户
SELECT
grantee,
string_agg(privilege_type, ‘, ‘) as privileges
FROM information_schema.role_table_grants
WHERE table_schema = ‘public’
GROUP BY grantee
HAVING COUNT(*) > 10;
— 4. 检查未使用的索引（可能为残留对象）
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;
— 5. 检查未使用的角色
SELECT
rolname,
rolvaliduntil,
rolcanlogin,
rolconnlimit
FROM pg_roles
WHERE rolcanlogin = true
AND rolname NOT IN (‘postgres’, ‘dba_admin’)
AND NOT EXISTS (
SELECT 1 FROM pg_stat_activity
WHERE usename = rolname
);
— 6. 检查未加密的敏感列
SELECT
table_name,
column_name,
data_type
FROM information_schema.columns
WHERE table_schema = ‘public’
AND (
column_name LIKE ‘%ssn%’ OR
column_name LIKE ‘%password%’ OR
column_name LIKE ‘%credit%’ OR
column_name LIKE ‘%card%’
)
AND table_name NOT IN (
SELECT table_name FROM information_schema.views
WHERE table_schema = ‘public’
);
8.2 自动化安全扫描
#!/bin/bash
# PostgreSQL安全扫描脚本
DB_HOST=”localhost”
DB_USER=”dba_admin”
DB_NAME=”postgres”
# 执行安全检查
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -f security_audit.sql > audit_report.txt
# 发送报告
mail -s “PostgreSQL Security Audit Report” security-team@company.com < audit_report.txt
# 检查错误日志中的异常
grep -i "FATAL\|ERROR\|WARNING" /var/log/postgresql/postgresql-12-main.log | \
grep -v "password authentication failed" | \
tail -20 > error_summary.txt
九、总结与最佳实践清单
9.1 核心安全原则
最小权限原则
：只授予完成工作所需的最小权限
纵深防御
：多层安全措施（网络、认证、应用、数据）
默认拒绝
：默认情况下拒绝所有访问，明确授权允许
持续监控
：实时监控异常行为，及时响应
定期审查
：定期审查权限和配置
9.2 生产环境配置清单
# ✅ 网络层
– listen_addresses = ‘localhost,10.0.0.100’ # 限制监听
– port = 5433 # 修改默认端口
– 配置防火墙限制访问源IP
# ✅ 认证层
– pg_hba.conf使用md5或scram-sha-256
– 强制SSL连接（hostssl）
– 强密码策略（12位+，复杂字符）
– 禁用或重命名postgres用户
# ✅ 权限层
– 使用RBAC模型
– 实施最小权限原则
– 启用行级安全（RLS）
– 使用视图封装敏感数据
# ✅ 加密层
– SSL加密传输
– 文件系统或应用层加密存储
– 备份文件加密
# ✅ 审计层
– 启用详细日志
– 安装pgAudit扩展
– 实时监控告警
– 定期安全扫描
# ✅ 维护层
– 自动化加密备份
– 定期恢复测试
– 及时更新补丁
– 定期审查权限
9.3 常见攻击防御速查表
攻击类型
防御措施
SQL注入
参数化查询、输入验证、最小权限
暴力破解
强密码、fail2ban、限制登录尝试
权限提升
禁用危险函数、限制超级用户、审查SECURITY DEFINER
数据泄露
数据脱敏、限制导出权限、监控大查询
中间人攻击
强制SSL、证书验证
数据窃取
传输加密、存储加密、备份加密
通过实施上述安全加固措施，可以显著提升PostgreSQL数据库的安全性，有效防御常见攻击手段。安全是一个持续的过程，建议定期审查和更新安全策略，以应对新的威胁和挑战。