PG数据库安全加固脚本

-- 设置数据库会话参数以提高安全性并减少潜在风险
ALTER SYSTEM SET ssl = 'on';
ALTER SYSTEM SET ssl_ca_file = '/path/to/ca.crt';
ALTER SYSTEM SET ssl_cert_file = '/path/to/server.crt';
ALTER SYSTEM SET ssl_key_file = '/path/to/server.key';
ALTER SYSTEM SET password_encryption_mode = 'md5';
ALTER SYSTEM SET fsync = 'on';
ALTER SYSTEM SET synchronous_commit = 'on';
ALTER SYSTEM SET full_page_writes = 'on';
ALTER SYSTEM SET wal_buffers = 16384;
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET shared_buffers = 512MB;
ALTER SYSTEM SET work_mem = 1MB;
ALTER SYSTEM SET maintenance_work_mem = 256MB;
ALTER SYSTEM SET effective_cache_size = 2GB;
 
-- 创建用户并设置密码策略
CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';
ALTER ROLE myuser WITH PASSWORD 'newpassword' VALID UNTIL '2025-01-01';
 
-- 分配权限和角色
GRANT myrole TO myuser;
REVOKE ALL PRIVILEGES ON mytable FROM PUBLIC;
GRANT SELECT, INSERT ON mytable TO myuser;
 
-- 设置连接参数，强制使用SSL
host    all             all             0.0.0.0/0               ssl
host    all             all             ::/0                    ssl

这个简化的代码示例展示了如何设置PostgreSQL数据库的安全相关配置，包括启用SSL、设置证书、密码加密、调整性能参数、创建用户、设置密码过期时间、授权和回收权限等。这些步骤有助于增强数据库的安全性，并减少潜在的安全风险。