## 1. 网络安全
– **防火墙：** 配置防火墙，只允许应用服务器和管理终端访问数据库
– **VPN：** 要求管理员通过VPN访问数据库
– **SSL/TLS：** 启用强制SSL连接，使用企业CA证书
– **网络分段：** 将数据库放在专用网络段，与其他网络隔离

## 2. 访问控制
– **角色分离：** 分离管理员、开发人员、分析师等角色
– **最小权限：** 为每个角色授予最小必要的权限
– **密码策略：** 实施强密码策略，定期更换密码
– **多因素认证：** 为管理员启用多因素认证

## 3. 数据安全
– **数据加密：** 加密敏感列，如用户密码、支付信息
– **备份加密：** 加密备份数据，异地存储
– **数据脱敏：** 为非生产环境提供脱敏数据
– **审计日志：** 记录所有数据库操作，定期分析审计日志

## 4. 系统安全
– **定期更新：** 及时更新PostgreSQL和操作系统
– **漏洞扫描：** 定期进行安全漏洞扫描
– **入侵检测：** 部署入侵检测系统，监控异常活动
– **安全配置：** 优化PostgreSQL安全配置参数

## 5. 具体实施
“`bash
# 1. 配置防火墙
sudo ufw allow from 10.0.0.0/8 to any port 5432 proto tcp
sudo ufw deny from 0.0.0.0/0 to any port 5432

# 2. 配置pg_hba.conf
sudo echo “fgedu.net.cnssl all all 10.0.0.0/8 scram-sha-256” >> /etc/postgresql/14/main/pg_hba.conf
sudo echo “fgedu.net.cnssl all all 127.0.0.1/32 scram-sha-256” >> /etc/postgresql/14/main/pg_hba.conf
sudo echo “fgedu.net.cnssl all all ::1/128 scram-sha-256” >> /etc/postgresql/14/main/pg_hba.conf
sudo echo “fgedu.net.cn all all 0.0.0.0/0 reject” >> /etc/postgresql/14/main/pg_hba.conf

# 3. 配置postgresql.conf
sudo echo “ssl = on” >> /etc/postgresql/14/main/postgresql.conf
sudo echo “ssl_cert_file = ‘server.crt'” >> /etc/postgresql/14/main/postgresql.conf
sudo echo “ssl_key_file = ‘server.key'” >> /etc/postgresql/14/main/postgresql.conf
sudo echo “ssl_ca_file = ‘root.crt'” >> /etc/postgresql/14/main/postgresql.conf
sudo echo “password_encryption = ‘scram-sha-256′” >> /etc/postgresql/14/main/postgresql.conf
sudo echo “log_connections = on” >> /etc/postgresql/14/main/postgresql.conf
sudo echo “log_disconnections = on” >> /etc/postgresql/14/main/postgresql.conf
sudo echo “log_statement = ‘all'” >> /etc/postgresql/14/main/postgresql.conf
sudo echo “listen_addresses = ‘10.0.0.100’” >> /etc/postgresql/14/main/postgresql.conf

# 4. 重启PostgreSQL
sudo systemctl restart postgresql

# 5. 创建角色和权限
psql -U pgsql -d pgsql << EOF CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin_password'; CREATE ROLE fgapp_fgedu WITH LOGIN PASSWORD 'fgapp_password'; CREATE ROLE read_only WITH LOGIN PASSWORD 'read_only_password'; CREATE DATABASE fgedu_db; GRANT CONNECT ON DATABASE fgedu_db TO fgapp_fgedu, read_only; fgedu_db; CREATE TABLE fgedu_fgedus ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, password TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE fgedu_payment ( id SERIAL PRIMARY KEY, fgedu_id INTEGER NOT NULL REFERENCES fgedu_fgedus(id), card_number TEXT NOT NULL, expiry_date TEXT NOT NULL, cvv TEXT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_fgedus TO fgapp_fgedu; GRANT SELECT ON fgedu_fgedus TO read_only; GRANT SELECT, INSERT ON fgedu_payment TO fgapp_fgedu; GRANT SELECT ON fgedu_payment TO read_only; GRANT USAGE, SELECT ON SEQUENCE fgedu_fgedus_id_seq TO fgapp_fgedu; GRANT USAGE, SELECT ON SEQUENCE fgedu_payment_id_seq TO fgapp_fgedu; -- 启用行级安全 ALTER TABLE fgedu_payment ENABLE ROW LEVEL SECURITY; CREATE POLICY payment_access ON fgedu_payment FOR SELECT USING (fgedu_id = current_fgedu::integer); -- 创建脱敏视图 CREATE VIEW fgedu_payment_masked AS SELECT id, fgedu_id, '****-****-****-' || substring(card_number from length(card_number) - 3) as card_number, substring(expiry_date from length(expiry_date) - 3) as expiry_date, '***' as cvv, amount, created_at FROM fgedu_payment; GRANT SELECT ON fgedu_payment_masked TO read_only; EOF # 6. 配置备份加密 pg_dump -h localfgedu.net.cn -U pgsql -d fgedu_db | gpg --symmetric --cipher-algo AES256 -o /backup/fgedu_db_backup_$(date +%Y%m%d).sql.gpg # 7. 配置监控 sudo apt-get install prometheus postgresql-exporter # 8. 定期安全扫描 sudo apt-get install openscap-scanner oscap scan --target system --profile xccdf_org.ssgproject.content_profile_pci-dss --results-arf /tmp/scan-results.xml ``` ## 6. 预期效果 - **网络安全：** 只允许授权网络访问，加密传输 - **访问控制：** 严格的角色和权限管理 - **数据安全：** 加密敏感数据，安全存储备份 - **系统安全：** 及时更新，定期扫描漏洞 - **合规性：** 满足PCI DSS等合规要求

## 1. 云服务安全配置
– **网络安全组：** 配置安全组，限制网络访问
– **VPC：** 使用VPC隔离数据库网络
– **加密存储：** 使用云服务的加密存储功能
– **IAM：** 使用云服务的身份和访问管理

## 2. PostgreSQL配置
– **SSL/TLS：** 启用强制SSL连接
– **参数调优：** 优化PostgreSQL安全参数
– **审计日志：** 启用详细的审计日志
– **自动更新：** 配置自动更新

## 3. 监控与告警
– **云监控：** 使用云服务的监控功能
– **安全告警：** 配置安全事件告警
– **日志分析：** 集中管理和分析日志
– **漏洞扫描：** 使用云服务的漏洞扫描功能

## 4. 具体实施（以AWS RDS为例）
“`bash
# 1. 创建VPC和安全组
aws ec2 create-vpc –cidr-block 10.0.0.0/16
aws ec2 create-security-group –group-name postgres-sg –description “PostgreSQL Security Group” –vpc-id vpc-12345678
aws ec2 authorize-security-group-ingress –group-id sg-12345678 –protocol tcp –port 5432 –cidr 10.0.0.0/8

# 2. 创建RDS实例
aws rds create-db-instance \
–db-instance-identifier fgedu-pgsql \
–allocated-storage 100 \
–db-instance-class db.t3.large \
–engine pgsql \
–engine-version 14.5 \
–master-fgeduname pgsql \
–master-fgedu-password your_password \
–vpc-security-group-ids sg-12345678 \
–storage-encrypted \
–enable-performance-insights \
–backup-retention-period 7 \
–multi-az \
–region us-east-1

# 3. 配置参数组
aws rds create-db-parameter-group \
–db-parameter-group-name fgedu-postgres-params \
–db-parameter-group-family postgres14 \
–description “PostgreSQL Security Parameters”

aws rds modify-db-parameter-group \
–db-parameter-group-name fgedu-postgres-params \
–parameters “ParameterName=ssl,ParameterValue=1” “ParameterName=log_connections,ParameterValue=1” “ParameterName=log_disconnections,ParameterValue=1” “ParameterName=log_statement,ParameterValue=all” “ParameterName=password_encryption,ParameterValue=scram-sha-256”

aws rds modify-db-instance \
–db-instance-identifier fgedu-pgsql \
–db-parameter-group-name fgedu-postgres-params \
–fgapply-immediately

# 4. 配置IAM角色
aws iam create-role –role-name rds-monitoring-role –assume-role-policy-document file://trust-policy.json
aws iam attach-role-policy –role-name rds-monitoring-role –policy-arn arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole

# 5. 配置CloudWatch告警
aws cloudwatch put-metric-alarm \
–alarm-name postgres-connection-alarm \
–alarm-description “Alarm when connection count exceeds threshold” \
–metric-name DatabaseConnections \
–namespace AWS/RDS \
–statistic Sum \
–period 60 \
–threshold 100 \
–comparison-operator GreaterThanThreshold \
–evaluation-periods 1 \
–alarm-actions arn:aws:sns:us-east-1:123456789012:SecurityAlerts \
–dimensions Name=DBInstanceIdentifier,Value=fgedu-pgsql # 6. 配置AWS Backup
aws backup create-backup-vault –backup-vault-name fgedu-postgres-backup
aws backup create-backup-plan –backup-plan-name fgedu-postgres-backup-plan –backup-plan file://backup-plan.json
aws backup associate-backup-plan –backup-plan-id 12345678-1234-1234-1234-123456789012 –resource-arn arn:aws:rds:us-east-1:123456789012:db:fgedu-pgsql # 7. 配置AWS Secrets Manager
aws secretsmanager create-secret \
–name fgedu-postgres-credentials \
–secret-string ‘{“fgeduname”:”postgres”,”password”:”your_password”}’

# 8. 配置AWS WAF
aws wafv2 create-web-acl –name postgres-waf –scope REGIONAL –default-action Allow={} –rules file://waf-rules.json –visibility-config SampledRequestsEnabled=true,CloudWatchMetricsEnabled=true,MetricName=postgres-waf
“`

## 5. 预期效果
– **网络安全：** 安全组和VPC隔离
– **数据安全：** 加密存储和备份
– **访问控制：** IAM和密钥管理
– **监控与告警：** CloudWatch监控和告警
– **合规性：** 满足云服务的安全合规要求

## 1. PCI DSS要求
– **要求2.2：** 不使用默认密码和安全参数
– **要求3.4：** 加密存储的持卡人数据
– **要求6.6：** 保护Web应用程序免受常见攻击
– **要求8.1：** 实施强访问控制措施
– **要求10.2：** 记录所有访问系统组件的活动
– **要求11.2：** 定期扫描漏洞

## 2. 实施措施
– **网络安全：** 配置防火墙，限制网络访问
– **数据加密：** 加密存储的支付卡数据
– **访问控制：** 实施最小权限原则
– **审计日志：** 记录所有数据库活动
– **漏洞扫描：** 定期扫描漏洞
– **安全测试：** 定期进行安全测试

## 3. 具体实施
“`sql
— 1. 创建加密函数
CREATE EXTENSION pgcrypto;

— 2. 创建符合PCI DSS的表结构
CREATE TABLE fgedu_payment_cards (
id SERIAL PRIMARY KEY,
fgedu_id INTEGER NOT NULL,
card_number TEXT NOT NULL, — 加密存储
cvv TEXT NOT NULL, — 加密存储
expiry_date TEXT NOT NULL, — 加密存储
cardholder_name TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
last_used_at TIMESTAMP
);

— 3. 插入加密数据
INSERT INTO fgedu_payment_cards (fgedu_id, card_number, cvv, expiry_date, cardholder_name)
VALUES (1,
encrypt(‘1234567890123456’, ‘encryption_key’, ‘aes-cbc/pad:pkcs’),
encrypt(‘123’, ‘encryption_key’, ‘aes-cbc/pad:pkcs’),
encrypt(’12/25′, ‘encryption_key’, ‘aes-cbc/pad:pkcs’),
‘John Doe’);

— 4. 创建视图进行脱敏
CREATE VIEW fgedu_payment_cards_masked AS
SELECT id, fgedu_id,
‘****-****-****-‘ || substring(decrypt(card_number, ‘encryption_key’, ‘aes-cbc/pad:pkcs’)::text from length(decrypt(card_number, ‘encryption_key’, ‘aes-cbc/pad:pkcs’)::text) – 3) as card_number,
‘***’ as cvv,
substring(decrypt(expiry_date, ‘encryption_key’, ‘aes-cbc/pad:pkcs’)::text from length(decrypt(expiry_date, ‘encryption_key’, ‘aes-cbc/pad:pkcs’)::text) – 3) as expiry_date,
cardholder_name,
created_at,
last_used_at
FROM fgedu_payment_cards;

— 5. 配置审计日志
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_statement = ‘all’;
ALTER SYSTEM SET log_line_prefix = ‘%t [%p]: [%l-1] fgedu=%u,db=%d,fgapp=%a,client=%h ‘;
ALTER SYSTEM SET log_rotation_age = ‘1d’;
ALTER SYSTEM SET log_rotation_size = ‘100MB’;

— 6. 配置安全参数
ALTER SYSTEM SET password_encryption = ‘scram-sha-256’;
ALTER SYSTEM SET authentication_timeout = ‘1min’;
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = ‘server.crt’;
ALTER SYSTEM SET ssl_key_file = ‘server.key’;
ALTER SYSTEM SET ssl_ca_file = ‘root.crt’;

— 7. 重载配置
SELECT pg_reload_conf();

— 8. 创建角色和权限
CREATE ROLE pci_admin WITH SUPERUSER LOGIN PASSWORD ‘pci_admin_password’;
CREATE ROLE pci_fgedu WITH LOGIN PASSWORD ‘pci_fgedu_password’;
CREATE ROLE pci_readonly WITH LOGIN PASSWORD ‘pci_readonly_password’;

GRANT CONNECT ON DATABASE fgedu_db TO pci_fgedu, pci_readonly;
GRANT USAGE ON SCHEMA public TO pci_fgedu, pci_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_payment_cards TO pci_fgedu;
GRANT SELECT ON fgedu_payment_cards_masked TO pci_readonly;
GRANT USAGE, SELECT ON SEQUENCE fgedu_payment_cards_id_seq TO pci_fgedu;

— 9. 启用行级安全
ALTER TABLE fgedu_payment_cards ENABLE ROW LEVEL SECURITY;
CREATE POLICY card_access ON fgedu_payment_cards
FOR SELECT USING (fgedu_id = current_fgedu::integer);

— 10. 配置定期安全扫描
CREATE OR REPLACE FUNCTION security_scan()
RETURNS void AS $$
BEGIN
— 检查用户密码强度
— 检查权限配置
— 检查加密状态
— 记录扫描结果
END;
$$ LANGUAGE plpgsql;

— 调度安全扫描
SELECT cron.schedule(‘0 0 * * 0’, ‘SELECT security_scan();’);
“`

## 4. 合规性验证
– **内部审计：** 定期进行内部安全审计
– **外部审计：** 聘请第三方进行PCI DSS合规审计
– **渗透测试：** 定期进行渗透测试
– **漏洞扫描：** 定期进行漏洞扫描
– **文档记录：** 记录所有安全措施和审计结果

## 5. 预期效果
– **合规性：** 满足PCI DSS合规要求
– **数据安全：** 保护支付卡数据安全
– **审计跟踪：** 完整的审计日志和跟踪
– **风险降低：** 降低安全风险和合规风险