# TYPE  DATABASE        USER            ADDRESS                 METHOD

# 本地 Unix socket（信任）
local   all             postgres                            trust
local   all             all                                 peer

# 本地 TCP/IP（scram-sha-256，推荐）
host    all             all         127.0.0.1/32            scram-sha-256
host    all             all         ::1/128                 scram-sha-256

# 内网（md5 或 scram-sha-256）
host    all             all         192.168.1.0/24          scram-sha-256

# 外网（scram-sha-256 + IP 白名单）
host    all             admin       10.0.0.0/8              scram-sha-256
host    all             all         0.0.0.0/0               reject

# 复制连接
host    replication     replicator   192.168.1.0/24          scram-sha-256

-- 创建登录角色
CREATE ROLE readonly;
CREATE ROLE readwrite WITH LOGIN PASSWORD 'strong_password';

-- 创建超级用户
CREATE ROLE dba WITH SUPERUSER CREATEDB CREATEROLE BYPASSRLS;

-- 创建带有效期的角色
CREATE ROLE temp_user WITH LOGIN PASSWORD 'temp123' VALID UNTIL '2024-12-31';

-- 创建带连接数限制的角色
CREATE ROLE app_user WITH LOGIN PASSWORD 'app_password' CONNECTION LIMIT 10;

-- 创建角色组
CREATE ROLE analyst;
CREATE ROLE developer;

-- 创建权限组
CREATE ROLE read_only;
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- 添加成员
GRANT read_only TO analyst;
GRANT read_only TO developer;

-- 添加带继承的角色
GRANT developer TO analyst WITH ADMIN TRUE;

-- 查看角色成员
SELECT 
    r.rolname AS role_name,
    m.rolname AS member_name
FROM pg_auth_members rm
JOIN pg_roles r ON rm.roleid = r.oid
JOIN pg_roles m ON rm.member = m.oid;

-- 修改密码
ALTER USER username WITH PASSWORD 'new_strong_password';

-- 设置密码有效期
ALTER USER username VALID UNTIL '2025-01-01';

-- 移除密码
ALTER USER username WITH PASSWORD NULL;

-- 密码策略（通过配置文件）
# password_encryption = scram-sha-256
# password_min_length = 12
# password_complexity = on

-- 数据库权限
GRANT CONNECT, TEMPORARY ON DATABASE mydb TO readwrite;
GRANT USAGE ON DATABASE mydb TO readonly;

-- 模式权限
GRANT USAGE ON SCHEMA public TO readonly;
GRANT CREATE ON SCHEMA public TO developer;

-- 表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 序列权限（需要授予才能使用自增）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite;

-- 函数权限
GRANT EXECUTE ON FUNCTION calculate_total(INTEGER, INTEGER) TO developer;

-- 默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

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

-- 启用 RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 创建策略
CREATE POLICY user_orders_policy ON orders
    FOR ALL
    TO authenticated
    USING (user_id = current_user_id());

CREATE POLICY sensitive_data_policy ON sensitive_data
    FOR SELECT
    TO public
    USING (
        -- 用户只能看到自己的数据
        user_id = current_setting('app.current_user_id')::INTEGER
        -- 或基于角色
        OR current_user IN ('admin', 'manager')
    );

-- 测试策略
SET app.current_user_id = '1';
SELECT * FROM sensitive_data;

-- 查看策略
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies;

-- 撤销表权限
REVOKE DELETE ON orders FROM readwrite;

-- 撤销所有权限
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;

-- 撤销默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    REVOKE ALL ON TABLES FROM public;

# postgresql.conf

# 启用 SSL
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'

# SSL 模式
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'

# 生成自签名证书
openssl req -new -x509 -days 365 -nodes \
  -text -out server.crt \
  -keyout server.key \
  -subj "/CN=postgres"

# 设置权限
chmod 600 server.key
chown postgres:postgres server.crt server.key

-- pgcrypto 扩展提供加密函数
CREATE EXTENSION pgcrypto;

-- 加密数据
INSERT INTO passwords (username, password_hash)
VALUES ('user1', crypt('user_password', gen_salt('bf')));

-- 验证密码
SELECT * FROM passwords 
WHERE username = 'user1' 
  AND password_hash = crypt('input_password', password_hash);

-- 加密字段（对称加密）
INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('sensitive data', 'encryption_key'));

-- 解密字段
SELECT pgp_sym_decrypt(data, 'encryption_key')
FROM secrets;

-- 哈希函数
SELECT md5('password');
SELECT sha256('password');
SELECT digest('password', 'sha256');  -- 需要 pgcrypto

# Ubuntu/Debian
sudo ufw allow from 192.168.1.0/24 to any port 5432

# CentOS/RHEL
sudo firewall-cmd --permanent --add-source=192.168.1.0/24 --zone=trusted
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

# postgresql.conf

# 最大连接数
max_connections = 200

# 预留连接给超级用户
reserved_connections = 3

# 空闲会话超时
idle_session_timeout = 60min

# 语句超时（毫秒）
statement_timeout = 30000  # 30 秒

# 监听地址
listen_addresses = 'localhost,192.168.1.100'

# 允许特定 IP
host    all             all         192.168.1.0/24          scram-sha-256

# 拒绝所有外部访问
host    all             all         0.0.0.0/0               reject

# postgresql.conf

# 启用日志
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# 记录内容
log_statement = 'ddl'           # 记录 DDL
log_min_duration_statement = 1000  # 记录超过 1 秒的查询
log_connections = on
log_disconnections = on
log_hostname = on

# 记录用户
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'

-- 安装 pgaudit
CREATE EXTENSION pgaudit;

-- 配置审计
ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';

-- 审计会话
ALTER DATABASE mydb SET pgaudit.log = 'READ, WRITE, DDL';
ALTER DATABASE mydb SET pgaudit.log_catalog = off;

-- 审计特定表
ALTER TABLE orders SET pgaudit.log = 'ALL';

-- 查看最近的 DDL 操作
SELECT 
    current_timestamp,
    session_start,
    usename,
    datname,
    query
FROM pg_stat_activity
WHERE state = 'active'
    AND query ILIKE '%CREATE%'
    AND query ILIKE '%TABLE%';

-- 查看连接历史
SELECT 
    event_time,
    user_name,
    database_name,
    connection_from,
    session_role,
    command_tag
FROM pg_logicalcapabilities;

┌─────────────────────────────────────────────────────────────┐
│                    安全检查清单                              │
├─────────────────────────────────────────────────────────────┤
│  □ 使用 scram-sha-256 认证                                  │
│  □ 启用 SSL 加密连接                                         │
│  □ 配置强密码策略                                            │
│  □ 最小权限原则                                              │
│  □ 启用 Row-Level Security                                  │
│  □ 配置审计日志                                              │
│  □ 定期更新 PostgreSQL                                      │
│  □ 网络隔离和防火墙                                          │
│  □ 定期备份                                                  │
└─────────────────────────────────────────────────────────────┘